Select Case User Defined Function

[talt0612]

All-
I'm really jammed up on this one and need urgent help for a project due today. I have a very large workbook that stores all of our currency information. Basically I need to be able to get a currency exchange rate from a table and run some sort of calculation with that rate. So for instance assume that :
A1 = "USD",
B1 = $100, and
C1 = EUR.
Then column "F" contains the currency pair e.g.
F1=EURUSD and
column "G" contains the exchange rate e.g.
G1=1.31, [F2=JPYUSD, G2=0.0123, F3=GBPUSD, G3=1.5926.....]

In D1 I'd like to calculate the value of $100 in EUR by pulling the exchange rate from the table. So D1 would be =B1/G1.

The problem is that the values of A1 and C1 can be any value i.e. EUR & GBP, AUD & JPY, ....
AND
they can appear on many different worksheets i.e. they won't always be in A1 & C1.

So what I think I need is a UDF (maybe a select case?) where the inputs are "currency1" & "currency2" and then the output pulls the currency rate from the table and allows me to run some sort of calculation with it.

I'm begging for help on this. Thank you very much in advance.

 

[AD_Taylor]

Working with the data you've given (and cross-checking with www.xe.com) this should work?

Excel 2010
	A	B	C	D	E	F
1	USD	100	EUR	76.09	EURUSD	1.31
2					JPYUSD	0.0123
3					GBPUSD	1.5926
4					USDEUR	0.7609
Sheet1

Cell Formulas
Range	Formula
D1	=$B1 * VLOOKUP($A1&$C1,$E:$F, 2, 0)

Not sure what you meant by they won't always be in A1 & C1. But you can use this formula and just change the references?

 

[talt0612]

Thanks AD_Taylor. Your formula looks good, but unfortunately it won't work for my purposes. If the data were always on the same sheet and in the same location, your formula would work well. However in my sheet, "USD" and "EUR" could be in many different places (i.e. they won't always be in sheet 1, A1 and C1. They will be on the same sheet, but they could be in A3 & D4 for instance). The table with exchange rates will always be in the same location. I can handle "finding" the currency pairs in my sheet, what I do not know how to do is "return" the exchange rate for a given currency pair. That's why I thought I could use a UDF. something like:

Public Function GetFXRate(currency1 As string, currency2 as string) As integer

Select Case activecell
case is "USD" and activecell.offset(0,2)="EUR"
[get USDEUR exchange rate].....
case is "USD" and activecell.offset(0,2)="GBP"
...
end select

 

[AD_Taylor]

There's obviously something I'm not getting here, because if that formula was copied down or the cell references changed then it would work for wherever the currency pairs are.

If they were in A3 & D4 then just switch the formula to:

=$B1 * VLOOKUP($A3&$D4,$E:$F, 2, 0)

With B1 being wherever your number to convert is.
Another way if you want to hard code the strings is:

=100 * VLOOKUP("USDEUR",$E:$F, 2, 0)

To be honest a UDF won't give you much more than that. It'll still need to know where the two currency pairs are, which is all that is being found here.


PS - ActiveCell won't work in a UDF as far as I'm aware. UDF's like to work with the values passed to them, so in your example you would have to do something with currency1 and currency2 that you have passed in.

 

[talt0612]

Again, thank you for your time AD_Taylor.

The problem is that our currency sheet runs a macro to calculate translated currency values (as well as some other things) and additionally the calculation will not always be the same.
So for instance, first I may need to know the value of USD 100 in EUR, e.g. =100/(EUR/USD exchange rate), but then I may also need to know the change in a given position in another sheet in the same workbook. So for instance, given USD 100 and EUR 60, the "change (in USD)"
= (-60*(EUR/USD exchange rate)-100)

You can see that in both places, the EUR/USD exchange rate is referenced, but it is used in a different fashion. As I said, all of the calculations are done within a macro, and I need the translated values for subsequent calculations to be correct. Therefore I can not update a formula each time the reference cells change.

Any further help you may provide is greatly appreciated.

 

[AD_Taylor]

Ah I think the bit I was missing was that it was being done in a macro not in a cell.

Right so you want to be able to call a function from within your macro that will get the rate from the table. If so try this:

Function GetRate(strCurr1 As String, strCurr2 As String) As Double
    Dim rngLkUp                     As Range
    Dim rngFind                     As Range
    Dim strCurrency                 As String
    
    'Concatentate the 2 strings
    strCurrency = strCurr1 & strCurr2
    
    'Set a reference to the lookup table
    'We only need the first column of it for this to work
    Set rngLkUp = ActiveSheet.Range("E:E")
    
    'Search within the lookup range to see if this particular currency pair exists
    'If it does return the value 1 cell to the right // If it doesn't return 0
    Set rngFind = rngLkUp.Find(strCurrency, LookAt:=xlWhole)
    If Not rngFind Is Nothing Then
        GetRate = rngFind.Offset(0, 1).Value
    Else
        GetRate = 0
    End If
End Function

The code is commented so should be easy to understand, any questions let me know.
FYI the lookup table is hard coded, i.e. it will always be looking in Column E for the match.

Hope this helps!

 

[talt0612]

Absolutely Fantastic. Thank you very much for the help.
Regards,
T

 

[AD_Taylor]

No problem Thanks for the feedback