# Help with function and 2 dimensional array

#### Certified

##### Board Regular
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.

Rich (BB code):
`` Function``
Rich (BB code):
``]ConvertTOUSD(foreignCurrencySymbol As String ,amount As Double ) As Double``
Rich (BB code):
``````Dim ExchangeRates(3, 2) As Variant ,i As Integer

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.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### mole999

##### Well-known Member
could you create a spare sheet called rates

then

B1 = 1.05

ExchangeRates(0,2) = 1.05

become

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

you wouldn't need open the VBA

#### veyselemre

##### Board Regular
Code:
``````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, 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

#### Certified

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

Code:
`````` 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``````

Replies
3
Views
3K
Replies
2
Views
378
Replies
2
Views
15K

### Forum statistics

1,195,625
Messages
6,010,754
Members
441,568
Latest member
abbyabby ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back