Noob - Create a UDF for currency conversion with VBA only - no external connections

[ilya49]

I am a beginner in VBA</SPAN>

The main objective is to create a, UDF with 2 parameters (Currency, Date) in order to display currency exchange rate based on the currency and date. Let’s say I want to find an exchange rate between USD/ EUR, then I would have a formula something like “=FX(EUR, 12/31/2011)”. All currencies will be against USD.</SPAN>

The trick is that it will have to be created as Add-On so that it can be used in any Excel spreadsheets. The second trick is that I cannot have VBA to make any internal connections to other workbooks or applications. </SPAN>

So the only thing that I can come up with would be to write a VBA code that would include all values and currency rates in it. The structure that I have in mind would be something like:</SPAN>

Function Fx(Vl As String, Dte As Date)
   Dim Rte As String
    
If Vl = "EUR" And Dte = "12/31/2011" Then
           Rte = 0.7723
    ElseIf Vl = "EUR" And Dte = "12/31/2010" Then
           Rte = 0.7546
           
           
ElseIf Vl = "GBP" And Dte = "12/31/2011" Then
            Rte = 0.6471
        ElseIf Vl = "GBP" And Dte = "12/31/2010" Then
           Rte = 0.6465
        End If
Fx = Rte
End Function

The above code does not work!!!

There will be at least twenty different currencies and at least 15 periods. I don't mind the manual work, but would appreciate if somebody an recommend a more efficient way.

Thank you

 

[E_DPSG]

you know I hate to say it, but wouldnt it be easier to maintain a second sheet with your conversion needs and just perform an indexed lookup to the values?

 

[ilya49]

This is what I have originally. But I will be creating several new excel spreadsheets every day, and adding a second sheet to each one of them is simply not an option.

I searched online and found a PULL script which can basically connect to a closed workbook and pull data from it. But due to my work (IT policy) limitations, the VBA script cannot connect to other workbooks or applicaitons.