Get currency conversion rates with web services
March 12, 2010 • JP • 4 Comments • Rate This Article
Did you ever want to check currency exchange rates but thought it was too difficult? Here's one method of doing so using web services.
The Currency Convertor from WebserviceX.NET lets you look up exchange rates for 151 different currency types. All you need to do is formulate the URLs to request the two currencies and the web service returns the exchange rate. Let's take a look at just how to do that.
First let's start with the main function, then we'll review the ancillary functions.
Return the exchange rate for two currency types
The following function takes two custom currency types (which we'll see below), parses the web response for the conversion rate and returns the exchange rate as a double type.
Function CurrencyConversionRate(fromCurr As CurrencyType, toCurr As CurrencyType) _
As Double
' http://www.webservicex.net/WCF/ServiceDetails.aspx?SID=18
Dim xml As Object
Dim result As String
Dim lFirstChar As Long
Dim lLastChar As Long
Dim fromCurrency As String
Dim toCurrency As String
fromCurrency = GetCurrencyName(fromCurr)
toCurrency = GetCurrencyName(toCurr)
Set xml = CreateObject("MSXML2.XMLHTTP")
xml.Open "GET", _
"http://www.webservicex.net/CurrencyConvertor.asmx/ConversionRate?FromCurrency=" & fromCurrency & "&ToCurrency=" & toCurrency, False
xml.Send
result = xml.responsetext
' parse result for response
lFirstChar = InStr(result, "webserviceX.NET")
lFirstChar = InStr(lFirstChar, result, ">") + 1
lLastChar = InStr(lFirstChar, result, "<")
CurrencyConversionRate = Mid$(result, lFirstChar, lLastChar - lFirstChar)
End Function
As Double
' http://www.webservicex.net/WCF/ServiceDetails.aspx?SID=18
Dim xml As Object
Dim result As String
Dim lFirstChar As Long
Dim lLastChar As Long
Dim fromCurrency As String
Dim toCurrency As String
fromCurrency = GetCurrencyName(fromCurr)
toCurrency = GetCurrencyName(toCurr)
Set xml = CreateObject("MSXML2.XMLHTTP")
xml.Open "GET", _
"http://www.webservicex.net/CurrencyConvertor.asmx/ConversionRate?FromCurrency=" & fromCurrency & "&ToCurrency=" & toCurrency, False
xml.Send
result = xml.responsetext
' parse result for response
lFirstChar = InStr(result, "webserviceX.NET")
lFirstChar = InStr(lFirstChar, result, ">") + 1
lLastChar = InStr(lFirstChar, result, "<")
CurrencyConversionRate = Mid$(result, lFirstChar, lLastChar - lFirstChar)
End Function
Get currency name from custom currency type
This function takes the custom currency type (see below) and returns the currency code.
Function GetCurrencyName(cType As CurrencyType) As String
Select Case cType
Case 0: GetCurrencyName = "AFA" ' -Afghanistan Afghani
Case 1: GetCurrencyName = "ALL" ' -Albanian Lek
Case 2: GetCurrencyName = "DZD" ' -Algerian Dinar
Case 3: GetCurrencyName = "ARS" ' -Argentine Peso
Case 4: GetCurrencyName = "AWG" ' -Aruba Florin
Case 5: GetCurrencyName = "AUD" ' -Australian Dollar
Case 6: GetCurrencyName = "BSD" ' -Bahamian Dollar
Case 7: GetCurrencyName = "BHD" ' -Bahraini Dinar
Case 8: GetCurrencyName = "BDT" ' -Bangladesh Taka
Case 9: GetCurrencyName = "BBD" ' -Barbados Dollar
Case 10: GetCurrencyName = "BZD" ' -Belize Dollar
Case 11: GetCurrencyName = "BMD" ' -Bermuda Dollar
Case 12: GetCurrencyName = "BTN" ' -Bhutan Ngultrum
Case 13: GetCurrencyName = "BOB" ' -Bolivian Boliviano
Case 14: GetCurrencyName = "BWP" ' -Botswana Pula
Case 15: GetCurrencyName = "BRL" ' -Brazilian Real
Case 16: GetCurrencyName = "GBP" ' -British Pound
Case 17: GetCurrencyName = "BND" ' -Brunei Dollar
Case 18: GetCurrencyName = "BIF" ' -Burundi Franc
Case 19: GetCurrencyName = "XOF" ' -CFA Franc (BCEAO)
Case 20: GetCurrencyName = "XAF" ' -CFA Franc (BEAC)
Case 21: GetCurrencyName = "KHR" ' -Cambodia Riel
Case 22: GetCurrencyName = "CAD" ' -Canadian Dollar
Case 23: GetCurrencyName = "CVE" ' -Cape Verde Escudo
Case 24: GetCurrencyName = "KYD" ' -Cayman Islands Dollar
Case 25: GetCurrencyName = "CLP" ' -Chilean Peso
Case 26: GetCurrencyName = "CNY" ' -Chinese Yuan
Case 27: GetCurrencyName = "COP" ' -Colombian Peso
Case 28: GetCurrencyName = "KMF" ' -Comoros Franc
Case 29: GetCurrencyName = "CRC" ' -Costa Rica Colon
Case 30: GetCurrencyName = "HRK" ' -Croatian Kuna
Case 31: GetCurrencyName = "CUP" ' -Cuban Peso
Case 32: GetCurrencyName = "CYP" ' -Cyprus Pound
Case 33: GetCurrencyName = "CZK" ' -Czech Koruna
Case 34: GetCurrencyName = "DKK" ' -Danish Krone
Case 35: GetCurrencyName = "DJF" ' -Dijibouti Franc
Case 36: GetCurrencyName = "DOP" ' -Dominican Peso
Case 37: GetCurrencyName = "XCD" ' -East Caribbean Dollar
Case 38: GetCurrencyName = "EGP" ' -Egyptian Pound
Case 39: GetCurrencyName = "SVC" ' -El Salvador Colon
Case 40: GetCurrencyName = "EEK" ' -Estonian Kroon
Case 41: GetCurrencyName = "ETB" ' -Ethiopian Birr
Case 42: GetCurrencyName = "EUR" '-Euro
Case 43: GetCurrencyName = "FKP" ' -Falkland Islands Pound
Case 44: GetCurrencyName = "GMD" ' -Gambian Dalasi
Case 45: GetCurrencyName = "GHC" ' -Ghanian Cedi
Case 46: GetCurrencyName = "GIP" ' -Gibraltar Pound
Case 47: GetCurrencyName = "XAU" ' -Gold Ounces
Case 48: GetCurrencyName = "GTQ" ' -Guatemala Quetzal
Case 49: GetCurrencyName = "GNF" ' -Guinea Franc
Case 50: GetCurrencyName = "GYD" '-Guyana Dollar
Case 51: GetCurrencyName = "HTG" '-Haiti Gourde
Case 52: GetCurrencyName = "HNL" '-Honduras Lempira
Case 53: GetCurrencyName = "HKD" '-Hong Kong Dollar
Case 54: GetCurrencyName = "HUF" '-Hungarian Forint
Case 55: GetCurrencyName = "ISK" '-Iceland Krona
Case 56: GetCurrencyName = "INR" '-Indian Rupee
Case 57: GetCurrencyName = "IDR" '-Indonesian Rupiah
Case 58: GetCurrencyName = "IQD" '-Iraqi Dinar
Case 59: GetCurrencyName = "ILS" '-Israeli Shekel
Case 60: GetCurrencyName = "JMD" '-Jamaican Dollar
Case 61: GetCurrencyName = "JPY" '-Japanese Yen
Case 62: GetCurrencyName = "JOD" '-Jordanian Dinar
Case 63: GetCurrencyName = "KZT" '-Kazakhstan Tenge
Case 64: GetCurrencyName = "KES" '-Kenyan Shilling
Case 65: GetCurrencyName = "KRW" '-Korean Won
Case 66: GetCurrencyName = "KWD" '-Kuwaiti Dinar
Case 67: GetCurrencyName = "LAK" '-Lao Kip
Case 68: GetCurrencyName = "LVL" '-Latvian Lat
Case 69: GetCurrencyName = "LBP" '-Lebanese Pound
Case 70: GetCurrencyName = "LSL" '-Lesotho Loti
Case 71: GetCurrencyName = "LRD" '-Liberian Dollar
Case 72: GetCurrencyName = "LYD" '-Libyan Dinar
Case 73: GetCurrencyName = "LTL" '-Lithuanian Lita
Case 74: GetCurrencyName = "MOP" '-Macau Pataca
Case 75: GetCurrencyName = "MKD" '-Macedonian Denar
Case 76: GetCurrencyName = "MGF" '-Malagasy Franc
Case 77: GetCurrencyName = "MWK" '-Malawi Kwacha
Case 78: GetCurrencyName = "MYR" '-Malaysian Ringgit
Case 79: GetCurrencyName = "MVR" '-Maldives Rufiyaa
Case 80: GetCurrencyName = "MTL" '-Maltese Lira
Case 81: GetCurrencyName = "MRO" '-Mauritania Ougulya
Case 82: GetCurrencyName = "MUR" '-Mauritius Rupee
Case 83: GetCurrencyName = "MXN" '-Mexican Peso
Case 84: GetCurrencyName = "MDL" '-Moldovan Leu
Case 85: GetCurrencyName = "MNT" '-Mongolian Tugrik
Case 86: GetCurrencyName = "MAD" '-Moroccan Dirham
Case 87: GetCurrencyName = "MZM" '-Mozambique Metical
Case 88: GetCurrencyName = "MMK" '-Myanmar Kyat
Case 89: GetCurrencyName = "NAD" '-Namibian Dollar
Case 90: GetCurrencyName = "NPR" '-Nepalese Rupee
Case 91: GetCurrencyName = "ANG" '-Neth Antilles Guilder
Case 92: GetCurrencyName = "NZD" '-New Zealand Dollar
Case 93: GetCurrencyName = "NIO" '-Nicaragua Cordoba
Case 94: GetCurrencyName = "NGN" '-Nigerian Naira
Case 95: GetCurrencyName = "KPW" '-North Korean Won
Case 96: GetCurrencyName = "NOK" '-Norwegian Krone
Case 97: GetCurrencyName = "OMR" '-Omani Rial
Case 98: GetCurrencyName = "XPF" '-Pacific Franc
Case 99: GetCurrencyName = "PKR" '-Pakistani Rupee
Case 100: GetCurrencyName = "XPD" '-Palladium Ounces
Case 101: GetCurrencyName = "PAB" '-Panama Balboa
Case 102: GetCurrencyName = "PGK" '-Papua New Guinea Kina
Case 103: GetCurrencyName = "PYG" '-Paraguayan Guarani
Case 104: GetCurrencyName = "PEN" '-Peruvian Nuevo Sol
Case 105: GetCurrencyName = "PHP" '-Philippine Peso
Case 106: GetCurrencyName = "XPT" '-Platinum Ounces
Case 107: GetCurrencyName = "PLN" '-Polish Zloty
Case 108: GetCurrencyName = "QAR" '-Qatar Rial
Case 109: GetCurrencyName = "ROL" '-Romanian Leu
Case 110: GetCurrencyName = "RUB" '-Russian Rouble
Case 111: GetCurrencyName = "WST" '-Samoa Tala
Case 112: GetCurrencyName = "STD" '-Sao Tome Dobra
Case 113: GetCurrencyName = "SAR" '-Saudi Arabian Riyal
Case 114: GetCurrencyName = "SCR" '-Seychelles Rupee
Case 115: GetCurrencyName = "SLL" ' -Sierra Leone Leone
Case 116: GetCurrencyName = "XAG" ' -Silver Ounces
Case 117: GetCurrencyName = "SGD" '-Singapore Dollar
Case 118: GetCurrencyName = "SKK" '-Slovak Koruna
Case 119: GetCurrencyName = "SIT" '-Slovenian Tolar
Case 120: GetCurrencyName = "SBD" '-Solomon Islands Dollar
Case 121: GetCurrencyName = "SOS" '-Somali Shilling
Case 122: GetCurrencyName = "ZAR" '-South African Rand
Case 123: GetCurrencyName = "LKR" '-Sri Lanka Rupee
Case 124: GetCurrencyName = "SHP" '-St Helena Pound
Case 125: GetCurrencyName = "SDD" '-Sudanese Dinar
Case 126: GetCurrencyName = "SRG" '-Surinam Guilder
Case 127: GetCurrencyName = "SZL" '-Swaziland Lilageni
Case 128: GetCurrencyName = "SEK" '-Swedish Krona
Case 129: GetCurrencyName = "TRY" '-Turkey Lira
Case 130: GetCurrencyName = "CHF" '-Swiss Franc
Case 131: GetCurrencyName = "SYP" '-Syrian Pound
Case 132: GetCurrencyName = "TWD" '-Taiwan Dollar
Case 133: GetCurrencyName = "TZS" '-Tanzanian Shilling
Case 134: GetCurrencyName = "THB" '-Thai Baht
Case 135: GetCurrencyName = "TOP" '-Tonga Pa'anga
Case 136: GetCurrencyName = "TTD" '-Trinidad&Tobago Dollar
Case 137: GetCurrencyName = "TND" '-Tunisian Dinar
Case 138: GetCurrencyName = "TRL" '-Turkish Lira
Case 139: GetCurrencyName = "USD" '-U.S.Dollar
Case 140: GetCurrencyName = "AED" '-UAE Dirham
Case 141: GetCurrencyName = "UGX" '-Ugandan Shilling
Case 142: GetCurrencyName = "UAH" '-Ukraine Hryvnia
Case 143: GetCurrencyName = "UYU" '-Uruguayan New Peso
Case 144: GetCurrencyName = "VUV" '-Vanuatu Vatu
Case 145: GetCurrencyName = "VEB" '-Venezuelan Bolivar
Case 146: GetCurrencyName = "VND" '-Vietnam Dong
Case 147: GetCurrencyName = "YER" '-Yemen Riyal
Case 148: GetCurrencyName = "YUM" '-Yugoslav Dinar
Case 149: GetCurrencyName = "ZMK" '-Zambian Kwacha
Case 150: GetCurrencyName = "ZWD" '-Zimbabwe Dollar
End Select
End Function
Select Case cType
Case 0: GetCurrencyName = "AFA" ' -Afghanistan Afghani
Case 1: GetCurrencyName = "ALL" ' -Albanian Lek
Case 2: GetCurrencyName = "DZD" ' -Algerian Dinar
Case 3: GetCurrencyName = "ARS" ' -Argentine Peso
Case 4: GetCurrencyName = "AWG" ' -Aruba Florin
Case 5: GetCurrencyName = "AUD" ' -Australian Dollar
Case 6: GetCurrencyName = "BSD" ' -Bahamian Dollar
Case 7: GetCurrencyName = "BHD" ' -Bahraini Dinar
Case 8: GetCurrencyName = "BDT" ' -Bangladesh Taka
Case 9: GetCurrencyName = "BBD" ' -Barbados Dollar
Case 10: GetCurrencyName = "BZD" ' -Belize Dollar
Case 11: GetCurrencyName = "BMD" ' -Bermuda Dollar
Case 12: GetCurrencyName = "BTN" ' -Bhutan Ngultrum
Case 13: GetCurrencyName = "BOB" ' -Bolivian Boliviano
Case 14: GetCurrencyName = "BWP" ' -Botswana Pula
Case 15: GetCurrencyName = "BRL" ' -Brazilian Real
Case 16: GetCurrencyName = "GBP" ' -British Pound
Case 17: GetCurrencyName = "BND" ' -Brunei Dollar
Case 18: GetCurrencyName = "BIF" ' -Burundi Franc
Case 19: GetCurrencyName = "XOF" ' -CFA Franc (BCEAO)
Case 20: GetCurrencyName = "XAF" ' -CFA Franc (BEAC)
Case 21: GetCurrencyName = "KHR" ' -Cambodia Riel
Case 22: GetCurrencyName = "CAD" ' -Canadian Dollar
Case 23: GetCurrencyName = "CVE" ' -Cape Verde Escudo
Case 24: GetCurrencyName = "KYD" ' -Cayman Islands Dollar
Case 25: GetCurrencyName = "CLP" ' -Chilean Peso
Case 26: GetCurrencyName = "CNY" ' -Chinese Yuan
Case 27: GetCurrencyName = "COP" ' -Colombian Peso
Case 28: GetCurrencyName = "KMF" ' -Comoros Franc
Case 29: GetCurrencyName = "CRC" ' -Costa Rica Colon
Case 30: GetCurrencyName = "HRK" ' -Croatian Kuna
Case 31: GetCurrencyName = "CUP" ' -Cuban Peso
Case 32: GetCurrencyName = "CYP" ' -Cyprus Pound
Case 33: GetCurrencyName = "CZK" ' -Czech Koruna
Case 34: GetCurrencyName = "DKK" ' -Danish Krone
Case 35: GetCurrencyName = "DJF" ' -Dijibouti Franc
Case 36: GetCurrencyName = "DOP" ' -Dominican Peso
Case 37: GetCurrencyName = "XCD" ' -East Caribbean Dollar
Case 38: GetCurrencyName = "EGP" ' -Egyptian Pound
Case 39: GetCurrencyName = "SVC" ' -El Salvador Colon
Case 40: GetCurrencyName = "EEK" ' -Estonian Kroon
Case 41: GetCurrencyName = "ETB" ' -Ethiopian Birr
Case 42: GetCurrencyName = "EUR" '-Euro
Case 43: GetCurrencyName = "FKP" ' -Falkland Islands Pound
Case 44: GetCurrencyName = "GMD" ' -Gambian Dalasi
Case 45: GetCurrencyName = "GHC" ' -Ghanian Cedi
Case 46: GetCurrencyName = "GIP" ' -Gibraltar Pound
Case 47: GetCurrencyName = "XAU" ' -Gold Ounces
Case 48: GetCurrencyName = "GTQ" ' -Guatemala Quetzal
Case 49: GetCurrencyName = "GNF" ' -Guinea Franc
Case 50: GetCurrencyName = "GYD" '-Guyana Dollar
Case 51: GetCurrencyName = "HTG" '-Haiti Gourde
Case 52: GetCurrencyName = "HNL" '-Honduras Lempira
Case 53: GetCurrencyName = "HKD" '-Hong Kong Dollar
Case 54: GetCurrencyName = "HUF" '-Hungarian Forint
Case 55: GetCurrencyName = "ISK" '-Iceland Krona
Case 56: GetCurrencyName = "INR" '-Indian Rupee
Case 57: GetCurrencyName = "IDR" '-Indonesian Rupiah
Case 58: GetCurrencyName = "IQD" '-Iraqi Dinar
Case 59: GetCurrencyName = "ILS" '-Israeli Shekel
Case 60: GetCurrencyName = "JMD" '-Jamaican Dollar
Case 61: GetCurrencyName = "JPY" '-Japanese Yen
Case 62: GetCurrencyName = "JOD" '-Jordanian Dinar
Case 63: GetCurrencyName = "KZT" '-Kazakhstan Tenge
Case 64: GetCurrencyName = "KES" '-Kenyan Shilling
Case 65: GetCurrencyName = "KRW" '-Korean Won
Case 66: GetCurrencyName = "KWD" '-Kuwaiti Dinar
Case 67: GetCurrencyName = "LAK" '-Lao Kip
Case 68: GetCurrencyName = "LVL" '-Latvian Lat
Case 69: GetCurrencyName = "LBP" '-Lebanese Pound
Case 70: GetCurrencyName = "LSL" '-Lesotho Loti
Case 71: GetCurrencyName = "LRD" '-Liberian Dollar
Case 72: GetCurrencyName = "LYD" '-Libyan Dinar
Case 73: GetCurrencyName = "LTL" '-Lithuanian Lita
Case 74: GetCurrencyName = "MOP" '-Macau Pataca
Case 75: GetCurrencyName = "MKD" '-Macedonian Denar
Case 76: GetCurrencyName = "MGF" '-Malagasy Franc
Case 77: GetCurrencyName = "MWK" '-Malawi Kwacha
Case 78: GetCurrencyName = "MYR" '-Malaysian Ringgit
Case 79: GetCurrencyName = "MVR" '-Maldives Rufiyaa
Case 80: GetCurrencyName = "MTL" '-Maltese Lira
Case 81: GetCurrencyName = "MRO" '-Mauritania Ougulya
Case 82: GetCurrencyName = "MUR" '-Mauritius Rupee
Case 83: GetCurrencyName = "MXN" '-Mexican Peso
Case 84: GetCurrencyName = "MDL" '-Moldovan Leu
Case 85: GetCurrencyName = "MNT" '-Mongolian Tugrik
Case 86: GetCurrencyName = "MAD" '-Moroccan Dirham
Case 87: GetCurrencyName = "MZM" '-Mozambique Metical
Case 88: GetCurrencyName = "MMK" '-Myanmar Kyat
Case 89: GetCurrencyName = "NAD" '-Namibian Dollar
Case 90: GetCurrencyName = "NPR" '-Nepalese Rupee
Case 91: GetCurrencyName = "ANG" '-Neth Antilles Guilder
Case 92: GetCurrencyName = "NZD" '-New Zealand Dollar
Case 93: GetCurrencyName = "NIO" '-Nicaragua Cordoba
Case 94: GetCurrencyName = "NGN" '-Nigerian Naira
Case 95: GetCurrencyName = "KPW" '-North Korean Won
Case 96: GetCurrencyName = "NOK" '-Norwegian Krone
Case 97: GetCurrencyName = "OMR" '-Omani Rial
Case 98: GetCurrencyName = "XPF" '-Pacific Franc
Case 99: GetCurrencyName = "PKR" '-Pakistani Rupee
Case 100: GetCurrencyName = "XPD" '-Palladium Ounces
Case 101: GetCurrencyName = "PAB" '-Panama Balboa
Case 102: GetCurrencyName = "PGK" '-Papua New Guinea Kina
Case 103: GetCurrencyName = "PYG" '-Paraguayan Guarani
Case 104: GetCurrencyName = "PEN" '-Peruvian Nuevo Sol
Case 105: GetCurrencyName = "PHP" '-Philippine Peso
Case 106: GetCurrencyName = "XPT" '-Platinum Ounces
Case 107: GetCurrencyName = "PLN" '-Polish Zloty
Case 108: GetCurrencyName = "QAR" '-Qatar Rial
Case 109: GetCurrencyName = "ROL" '-Romanian Leu
Case 110: GetCurrencyName = "RUB" '-Russian Rouble
Case 111: GetCurrencyName = "WST" '-Samoa Tala
Case 112: GetCurrencyName = "STD" '-Sao Tome Dobra
Case 113: GetCurrencyName = "SAR" '-Saudi Arabian Riyal
Case 114: GetCurrencyName = "SCR" '-Seychelles Rupee
Case 115: GetCurrencyName = "SLL" ' -Sierra Leone Leone
Case 116: GetCurrencyName = "XAG" ' -Silver Ounces
Case 117: GetCurrencyName = "SGD" '-Singapore Dollar
Case 118: GetCurrencyName = "SKK" '-Slovak Koruna
Case 119: GetCurrencyName = "SIT" '-Slovenian Tolar
Case 120: GetCurrencyName = "SBD" '-Solomon Islands Dollar
Case 121: GetCurrencyName = "SOS" '-Somali Shilling
Case 122: GetCurrencyName = "ZAR" '-South African Rand
Case 123: GetCurrencyName = "LKR" '-Sri Lanka Rupee
Case 124: GetCurrencyName = "SHP" '-St Helena Pound
Case 125: GetCurrencyName = "SDD" '-Sudanese Dinar
Case 126: GetCurrencyName = "SRG" '-Surinam Guilder
Case 127: GetCurrencyName = "SZL" '-Swaziland Lilageni
Case 128: GetCurrencyName = "SEK" '-Swedish Krona
Case 129: GetCurrencyName = "TRY" '-Turkey Lira
Case 130: GetCurrencyName = "CHF" '-Swiss Franc
Case 131: GetCurrencyName = "SYP" '-Syrian Pound
Case 132: GetCurrencyName = "TWD" '-Taiwan Dollar
Case 133: GetCurrencyName = "TZS" '-Tanzanian Shilling
Case 134: GetCurrencyName = "THB" '-Thai Baht
Case 135: GetCurrencyName = "TOP" '-Tonga Pa'anga
Case 136: GetCurrencyName = "TTD" '-Trinidad&Tobago Dollar
Case 137: GetCurrencyName = "TND" '-Tunisian Dinar
Case 138: GetCurrencyName = "TRL" '-Turkish Lira
Case 139: GetCurrencyName = "USD" '-U.S.Dollar
Case 140: GetCurrencyName = "AED" '-UAE Dirham
Case 141: GetCurrencyName = "UGX" '-Ugandan Shilling
Case 142: GetCurrencyName = "UAH" '-Ukraine Hryvnia
Case 143: GetCurrencyName = "UYU" '-Uruguayan New Peso
Case 144: GetCurrencyName = "VUV" '-Vanuatu Vatu
Case 145: GetCurrencyName = "VEB" '-Venezuelan Bolivar
Case 146: GetCurrencyName = "VND" '-Vietnam Dong
Case 147: GetCurrencyName = "YER" '-Yemen Riyal
Case 148: GetCurrencyName = "YUM" '-Yugoslav Dinar
Case 149: GetCurrencyName = "ZMK" '-Zambian Kwacha
Case 150: GetCurrencyName = "ZWD" '-Zimbabwe Dollar
End Select
End Function
Enum Section
Here is the enumerated section that all of the procedures above are counting on. Remember that Enums must be placed at the top of a standard module. Usually I put all my Enums into their own module (and declare them Public) to avoid placement confusion.
Public Enum CurrencyType
AFA ' -Afghanistan Afghani
ALL ' -Albanian Lek
DZD ' -Algerian Dinar
ARS ' -Argentine Peso
AWG ' -Aruba Florin
AUD ' -Australian Dollar
BSD ' -Bahamian Dollar
BHD ' -Bahraini Dinar
BDT ' -Bangladesh Taka
BBD ' -Barbados Dollar
BZD ' -Belize Dollar
BMD ' -Bermuda Dollar
BTN ' -Bhutan Ngultrum
BOB ' -Bolivian Boliviano
BWP ' -Botswana Pula
BRL ' -Brazilian Real
GBP ' -British Pound
BND ' -Brunei Dollar
BIF ' -Burundi Franc
XOF ' -CFA Franc (BCEAO)
XAF ' -CFA Franc (BEAC)
KHR ' -Cambodia Riel
CAD ' -Canadian Dollar
CVE ' -Cape Verde Escudo
KYD ' -Cayman Islands Dollar
CLP ' -Chilean Peso
CNY ' -Chinese Yuan
COP ' -Colombian Peso
KMF ' -Comoros Franc
CRC ' -Costa Rica Colon
HRK ' -Croatian Kuna
CUP ' -Cuban Peso
CYP ' -Cyprus Pound
CZK ' -Czech Koruna
DKK ' -Danish Krone
DJF ' -Dijibouti Franc
DOP ' -Dominican Peso
XCD ' -East Caribbean Dollar
EGP ' -Egyptian Pound
SVC ' -El Salvador Colon
EEK ' -Estonian Kroon
ETB ' -Ethiopian Birr
EUR '-Euro
FKP ' -Falkland Islands Pound
GMD ' -Gambian Dalasi
GHC ' -Ghanian Cedi
GIP ' -Gibraltar Pound
XAU ' -Gold Ounces
GTQ ' -Guatemala Quetzal
GNF ' -Guinea Franc
GYD '-Guyana Dollar
HTG '-Haiti Gourde
HNL '-Honduras Lempira
HKD '-Hong Kong Dollar
HUF '-Hungarian Forint
ISK '-Iceland Krona
INR '-Indian Rupee
IDR '-Indonesian Rupiah
IQD '-Iraqi Dinar
ILS '-Israeli Shekel
JMD '-Jamaican Dollar
JPY '-Japanese Yen
JOD '-Jordanian Dinar
KZT '-Kazakhstan Tenge
KES '-Kenyan Shilling
KRW '-Korean Won
KWD '-Kuwaiti Dinar
LAK '-Lao Kip
LVL '-Latvian Lat
LBP '-Lebanese Pound
LSL '-Lesotho Loti
LRD '-Liberian Dollar
LYD '-Libyan Dinar
LTL '-Lithuanian Lita
MOP '-Macau Pataca
MKD '-Macedonian Denar
MGF '-Malagasy Franc
MWK '-Malawi Kwacha
MYR '-Malaysian Ringgit
MVR '-Maldives Rufiyaa
MTL '-Maltese Lira
MRO '-Mauritania Ougulya
MUR '-Mauritius Rupee
MXN '-Mexican Peso
MDL '-Moldovan Leu
MNT '-Mongolian Tugrik
MAD '-Moroccan Dirham
MZM '-Mozambique Metical
MMK '-Myanmar Kyat
NAD '-Namibian Dollar
NPR '-Nepalese Rupee
ANG '-Neth Antilles Guilder
NZD '-New Zealand Dollar
NIO '-Nicaragua Cordoba
NGN '-Nigerian Naira
KPW '-North Korean Won
NOK '-Norwegian Krone
OMR '-Omani Rial
XPF '-Pacific Franc
PKR '-Pakistani Rupee
XPD '-Palladium Ounces
PAB '-Panama Balboa
PGK '-Papua New Guinea Kina
PYG '-Paraguayan Guarani
PEN '-Peruvian Nuevo Sol
PHP '-Philippine Peso
XPT '-Platinum Ounces
PLN '-Polish Zloty
QAR '-Qatar Rial
ROL '-Romanian Leu
RUB '-Russian Rouble
WST '-Samoa Tala
STD '-Sao Tome Dobra
SAR '-Saudi Arabian Riyal
SCR '-Seychelles Rupee
SLL ' -Sierra Leone Leone
XAG ' -Silver Ounces
SGD '-Singapore Dollar
SKK '-Slovak Koruna
SIT '-Slovenian Tolar
SBD '-Solomon Islands Dollar
SOS '-Somali Shilling
ZAR '-South African Rand
LKR '-Sri Lanka Rupee
shp '-St Helena Pound
SDD '-Sudanese Dinar
SRG '-Surinam Guilder
SZL '-Swaziland Lilageni
SEK '-Swedish Krona
TRY '-Turkey Lira
CHF '-Swiss Franc
SYP '-Syrian Pound
TWD '-Taiwan Dollar
TZS '-Tanzanian Shilling
THB '-Thai Baht
Top '-Tonga Pa'anga
TTD '-Trinidad&Tobago Dollar
TND '-Tunisian Dinar
TRL '-Turkish Lira
USD '-U.S.Dollar
AED '-UAE Dirham
UGX '-Ugandan Shilling
UAH '-Ukraine Hryvnia
UYU '-Uruguayan New Peso
VUV '-Vanuatu Vatu
VEB '-Venezuelan Bolivar
VND '-Vietnam Dong
YER '-Yemen Riyal
YUM '-Yugoslav Dinar
ZMK '-Zambian Kwacha
ZWD '-Zimbabwe Dollar
End Enum
AFA ' -Afghanistan Afghani
ALL ' -Albanian Lek
DZD ' -Algerian Dinar
ARS ' -Argentine Peso
AWG ' -Aruba Florin
AUD ' -Australian Dollar
BSD ' -Bahamian Dollar
BHD ' -Bahraini Dinar
BDT ' -Bangladesh Taka
BBD ' -Barbados Dollar
BZD ' -Belize Dollar
BMD ' -Bermuda Dollar
BTN ' -Bhutan Ngultrum
BOB ' -Bolivian Boliviano
BWP ' -Botswana Pula
BRL ' -Brazilian Real
GBP ' -British Pound
BND ' -Brunei Dollar
BIF ' -Burundi Franc
XOF ' -CFA Franc (BCEAO)
XAF ' -CFA Franc (BEAC)
KHR ' -Cambodia Riel
CAD ' -Canadian Dollar
CVE ' -Cape Verde Escudo
KYD ' -Cayman Islands Dollar
CLP ' -Chilean Peso
CNY ' -Chinese Yuan
COP ' -Colombian Peso
KMF ' -Comoros Franc
CRC ' -Costa Rica Colon
HRK ' -Croatian Kuna
CUP ' -Cuban Peso
CYP ' -Cyprus Pound
CZK ' -Czech Koruna
DKK ' -Danish Krone
DJF ' -Dijibouti Franc
DOP ' -Dominican Peso
XCD ' -East Caribbean Dollar
EGP ' -Egyptian Pound
SVC ' -El Salvador Colon
EEK ' -Estonian Kroon
ETB ' -Ethiopian Birr
EUR '-Euro
FKP ' -Falkland Islands Pound
GMD ' -Gambian Dalasi
GHC ' -Ghanian Cedi
GIP ' -Gibraltar Pound
XAU ' -Gold Ounces
GTQ ' -Guatemala Quetzal
GNF ' -Guinea Franc
GYD '-Guyana Dollar
HTG '-Haiti Gourde
HNL '-Honduras Lempira
HKD '-Hong Kong Dollar
HUF '-Hungarian Forint
ISK '-Iceland Krona
INR '-Indian Rupee
IDR '-Indonesian Rupiah
IQD '-Iraqi Dinar
ILS '-Israeli Shekel
JMD '-Jamaican Dollar
JPY '-Japanese Yen
JOD '-Jordanian Dinar
KZT '-Kazakhstan Tenge
KES '-Kenyan Shilling
KRW '-Korean Won
KWD '-Kuwaiti Dinar
LAK '-Lao Kip
LVL '-Latvian Lat
LBP '-Lebanese Pound
LSL '-Lesotho Loti
LRD '-Liberian Dollar
LYD '-Libyan Dinar
LTL '-Lithuanian Lita
MOP '-Macau Pataca
MKD '-Macedonian Denar
MGF '-Malagasy Franc
MWK '-Malawi Kwacha
MYR '-Malaysian Ringgit
MVR '-Maldives Rufiyaa
MTL '-Maltese Lira
MRO '-Mauritania Ougulya
MUR '-Mauritius Rupee
MXN '-Mexican Peso
MDL '-Moldovan Leu
MNT '-Mongolian Tugrik
MAD '-Moroccan Dirham
MZM '-Mozambique Metical
MMK '-Myanmar Kyat
NAD '-Namibian Dollar
NPR '-Nepalese Rupee
ANG '-Neth Antilles Guilder
NZD '-New Zealand Dollar
NIO '-Nicaragua Cordoba
NGN '-Nigerian Naira
KPW '-North Korean Won
NOK '-Norwegian Krone
OMR '-Omani Rial
XPF '-Pacific Franc
PKR '-Pakistani Rupee
XPD '-Palladium Ounces
PAB '-Panama Balboa
PGK '-Papua New Guinea Kina
PYG '-Paraguayan Guarani
PEN '-Peruvian Nuevo Sol
PHP '-Philippine Peso
XPT '-Platinum Ounces
PLN '-Polish Zloty
QAR '-Qatar Rial
ROL '-Romanian Leu
RUB '-Russian Rouble
WST '-Samoa Tala
STD '-Sao Tome Dobra
SAR '-Saudi Arabian Riyal
SCR '-Seychelles Rupee
SLL ' -Sierra Leone Leone
XAG ' -Silver Ounces
SGD '-Singapore Dollar
SKK '-Slovak Koruna
SIT '-Slovenian Tolar
SBD '-Solomon Islands Dollar
SOS '-Somali Shilling
ZAR '-South African Rand
LKR '-Sri Lanka Rupee
shp '-St Helena Pound
SDD '-Sudanese Dinar
SRG '-Surinam Guilder
SZL '-Swaziland Lilageni
SEK '-Swedish Krona
TRY '-Turkey Lira
CHF '-Swiss Franc
SYP '-Syrian Pound
TWD '-Taiwan Dollar
TZS '-Tanzanian Shilling
THB '-Thai Baht
Top '-Tonga Pa'anga
TTD '-Trinidad&Tobago Dollar
TND '-Tunisian Dinar
TRL '-Turkish Lira
USD '-U.S.Dollar
AED '-UAE Dirham
UGX '-Ugandan Shilling
UAH '-Ukraine Hryvnia
UYU '-Uruguayan New Peso
VUV '-Vanuatu Vatu
VEB '-Venezuelan Bolivar
VND '-Vietnam Dong
YER '-Yemen Riyal
YUM '-Yugoslav Dinar
ZMK '-Zambian Kwacha
ZWD '-Zimbabwe Dollar
End Enum
Sample usage
The following procedure takes two currencies (Euro and U.S. Dollar) and gets the conversion rate from EUR to USD. If the exchange rate is favorable (i.e. EUR is lower than USD), the result will be >= 1. Either way, an appropriate message is printed to the Immediate Window.
The currencies are then swapped and the conversion is done again.
Sub TestCurrencyCheck()
Dim fromCurrency As CurrencyType
Dim toCurrency As CurrencyType
Dim fromCurrencyName As String
Dim toCurrencyName As String
Dim tempCurrencyName As CurrencyType
Dim result As Double
Dim relation As String
fromCurrency = EUR
toCurrency = USD
result = CurrencyConversionRate(fromCurrency, toCurrency)
' Debug.Print result
If result < 1 Then
relation = "less"
Else
relation = "more"
End If
fromCurrencyName = GetCurrencyName(fromCurrency)
toCurrencyName = GetCurrencyName(toCurrency)
Debug.Print fromCurrencyName & " vs. " & toCurrencyName
Debug.Print "---"
Debug.Print fromCurrencyName _
& " is valued " & relation & " than " & toCurrencyName & ":"
Debug.Print result & " " & toCurrencyName & " for each 1 " & fromCurrencyName
' swap currencies to show the reverse calculation
tempCurrencyName = fromCurrency
fromCurrency = toCurrency
toCurrency = tempCurrencyName
result = CurrencyConversionRate(fromCurrency, toCurrency)
' Debug.Print result
If result < 1 Then
relation = "less"
Else
relation = "more"
End If
fromCurrencyName = GetCurrencyName(fromCurrency)
toCurrencyName = GetCurrencyName(toCurrency)
Debug.Print fromCurrencyName _
& " is valued " & relation & " than " & toCurrencyName & ":"
Debug.Print result & " " & toCurrencyName & " for each 1 " & fromCurrencyName
End Sub
Dim fromCurrency As CurrencyType
Dim toCurrency As CurrencyType
Dim fromCurrencyName As String
Dim toCurrencyName As String
Dim tempCurrencyName As CurrencyType
Dim result As Double
Dim relation As String
fromCurrency = EUR
toCurrency = USD
result = CurrencyConversionRate(fromCurrency, toCurrency)
' Debug.Print result
If result < 1 Then
relation = "less"
Else
relation = "more"
End If
fromCurrencyName = GetCurrencyName(fromCurrency)
toCurrencyName = GetCurrencyName(toCurrency)
Debug.Print fromCurrencyName & " vs. " & toCurrencyName
Debug.Print "---"
Debug.Print fromCurrencyName _
& " is valued " & relation & " than " & toCurrencyName & ":"
Debug.Print result & " " & toCurrencyName & " for each 1 " & fromCurrencyName
' swap currencies to show the reverse calculation
tempCurrencyName = fromCurrency
fromCurrency = toCurrency
toCurrency = tempCurrencyName
result = CurrencyConversionRate(fromCurrency, toCurrency)
' Debug.Print result
If result < 1 Then
relation = "less"
Else
relation = "more"
End If
fromCurrencyName = GetCurrencyName(fromCurrency)
toCurrencyName = GetCurrencyName(toCurrency)
Debug.Print fromCurrencyName _
& " is valued " & relation & " than " & toCurrencyName & ":"
Debug.Print result & " " & toCurrencyName & " for each 1 " & fromCurrencyName
End Sub
Download sample workbook with macros – Excel 2003
Download sample workbook with macros – Excel 2007
There are lots more web services to explore, so stay tuned!
VBA ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Check a file's last modification time
Next Post: Got weather?
I am trying to convert from one currency to another.
A B D E
Amount Convert From Convert To Result
100 USD NZD
How can I utilise your VBa to get latest rate can convert and give me correct answer?
Kind Regards
Bhavik
Just call CurrencyConversionRate like this:
Dim toCurrency As CurrencyType
Dim result As Double
fromCurrency = USD
toCurrency = NZD
result = CurrencyConversionRate(fromCurrency, toCurrency) * 100
Hi JP,
Sorry I can't make this work. Could you please e-mail me file at bhavik_khatri@hotmail.com
Kind Regards,
Bhavik
Putting the functions into a file for you isn't going to help if you don't know how to use them.
Did you copy and paste the CurrencyConversionRate and GetCurrencyName functions into a standard module?
Did you copy and paste the Enum Section into a separate standard module, or at the top of an existing standard module?
If so, did you copy and paste the TestCurrencyCheck function into a standard module and try to run it? Did you get an error message? If so, what was the message?
You'll need to be more specific.