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

ilya49

New Member
Joined
Nov 24, 2011
Messages
9
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>


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

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

E_DPSG

Board Regular
Joined
Jul 17, 2008
Messages
82
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

New Member
Joined
Nov 24, 2011
Messages
9
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,473
Messages
5,596,356
Members
414,061
Latest member
JJSB

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top