Help with function and 2 dimensional array

[Certified]

Hi,

I have an function that converts foreign currency to US dollars. However, I have to enter the exchange rate data manually into the function. I would like to learn how to populate an array from a worksheet and use that array in my function.

Here is the code I currently have.

 Function

]ConvertTOUSD(foreignCurrencySymbol As String ,amount As Double ) As Double

Dim ExchangeRates(3, 2) As Variant ,i As Integer 

ExchangeRates(0,0) = "Canada"
ExchangeRates(0,1) = "CAD"
ExchangeRates(0,2) = 1.05

ExchangeRates(1,0) = "Euro Zone"
ExchangeRates(1,1) = "EUR"
ExchangeRates(1,2) = 1.2

ExchangeRates(2,0) = "Japan"
ExchangeRates(2,1) = "JPY"
ExchangeRates(2,2) = 0.012

ExchangeRates(3,0) = "Mexico"
ExchangeRates(3,1) = "Mxn"
ExchangeRates(3,2) = 0.07


For i = 0 To UBound(ExchangeRates,1)
         If foreignCurrencySymbol= ExchangeRates(i, 1) Then 
            ConvertTOUSD = amount * ExchangeRates(i, 2)
         End If 
Next i

End Function 

The worksheet has two columns: FX symbol and FX rate.

 

[mole999]

could you create a spare sheet called rates

then

A1 = CANADA
B1 = 1.05

your function could then
ExchangeRates(0,2) = 1.05

become

ExchangeRates(0,2) = Sheets("rates").range("B1")

you wouldn't need open the VBA

 

[veyselemre]

Function ConvertTOUSD(foreignCurrencySymbol As String, amount As Double) As Double    Dim ExchangeRates(3, 2) As Variant, i As Integer


    With Sheets("rates")    'change sheet name
        ExchangeRates(0, 0) = "Canada"
        ExchangeRates(0, 1) = "CAD"
        ExchangeRates(0, 2) = .Range("A3")


        ExchangeRates(1, 0) = "Euro Zone"
        ExchangeRates(1, 1) = "EUR"
        ExchangeRates(1, 2) = .Range("B3")


        ExchangeRates(2, 0) = "Japan"
        ExchangeRates(2, 1) = "JPY"
        ExchangeRates(2, 2) = .Range("C3")


        ExchangeRates(3, 0) = "Mexico"
        ExchangeRates(3, 1) = "Mxn"
        ExchangeRates(3, 2) = .Range("D3")
    End With
    For i = 0 To UBound(ExchangeRates, 1)
        If UCase(foreignCurrencySymbol) = UCase(ExchangeRates(i, 1)) Then
            ConvertTOUSD = amount * ExchangeRates(i, 2)
        End If
    Next i
End Function

example
https://yadi.sk/i/Ti1Yi6Yvh7xCi

 

[Certified]

I came up with this code. To me logically it should work, but I keep getting a #value! error.

 Function Ech1q15(FCC As String, Amount As Double) As Double


Dim Myarray() As Variant
Dim i As Integer

Sheet6.Activate


Myarray = Range("b2", Range("b1").End(xlDown).End(xlToRight))


For i = 1 To UBound(Myarray, 1)
    If FCC = Myarray(i, 1) Then
        work2 = Amount * Myarray(i, 3)
    End If
    
Next i

End Function