Currency Conversion Update Macro

Can you email this file to me? I am having problem accessing it from EE

ssaqibh,

Certainly!  What's your email address (not on your profile)?

B.

It is definitely there. You probably did not read it thoroughly. Anyway never mind I managed to download it from home and am looking at it now. Will come back if I have any questions.

Is this what you want?

Sub CurrencyUpdate()
Dim cel As Range, cn As Range
Sheets("Currency").QueryTables(1).Refresh
inputs").Range("F2").Value, , xlValues)
For Each cel In Sheets("Currency").Range("G4:G10000")
If cel.Value <> "" Then

    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).Value = cel.Offset(, 2).Value
    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).NumberFormat = cel.Offset(, 2).NumberFormat
End If
Next cel
End Sub

Is this what you meant?

Sub CurrencyUpdate()
Dim cel As Range, cn As Range
Sheets("Currency").QueryTables (1)
.Refresh "Customer-inputs"
.Range("F2").Value , , xlValues
For Each cel In Sheets("Currency").Range("G4:G10000")
If cel.Value <> "" Then

    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).Value = cel.Offset(, 2).Value
    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).NumberFormat = cel.Offset(, 2).NumberFormat
End If
Next cel
End Sub

This is what I meant

Sub CurrencyUpdate()
Dim cel As Range, cn As Range
Sheets("Currency").QueryTables(1).Refresh
For Each cel In Sheets("Currency").Range("G4:G10000")
If cel.Value <> "" Then
    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).Value = cel.Offset(, 2).Value
    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).NumberFormat = cel.Offset(, 2).NumberFormat
End If
Next cel
End Sub

Select allOpen in new window

Ssaqibh,

I copied the code above into the Currency WS and it doesn't work (newly attached file).

In the Currency worksheet I actually have all the different versions (named differently and unassigned) for you to look at.

Here is what I'm trying to have it do;

1.) When you select a currency on the Customer - Inputs WS, it changes not only the format (which it does now), but reflects the new value (based on the currency selected) in the Currency WS (column I).  This new value is reflected in both the Customer Input WS as well as the Pricing WS.  

2.) When you press "UPDATE!" it does a background query to MS Money to update the currencies and then reflects those new currencies.  

Make sense?
Copy-of-Currency-Update-and-Disp.xlsm

Ssaqibh,

Are you there?  The copies of the macro code are within the WB for you to see.  But the latest code does not work.

Appreciate your help.

B.

Ssaqibh,

Hey...WELCOME BACK!  In my last post, I copied the code you sent and put it in the WB.  I have a number of versions in the code "commented out" but you can see it for reference.

Here is what I'm trying to have it do;

1.) When you select a currency on the Customer - Inputs WS, it should changeboth the format (which it does now), and the new CURRENCY value (based on the currency selected) in the Currency WS (column I).  This new value is reflected in both the Customer Input WS as well as the Pricing WS.  

2.) THEN, periodically, someone will want to update the Currency conversion table.  When you press "UPDATE!" it should do a background query to MS Money to update the currencies and then reflect those new currencies in the model.

Make sense?

Lets do step 1 first. Please set up an example.

Tell me what you want to change the currency to

and when you do that change what would you expect to see

and what are you currently seeing.

Ssaqibh,

Sounds like a plan!  OK…. When you select the Currency in WS Customer-Inputs, the Currency Format is changed in both the Customer-Inputs WS and the Price Quote WS (this works correctly).  But in addition to that, in the Currency WS (Cells I) should change to the selected Currency (from the Currency List) and then the new numeric value (associated with that currency should be updated to both of the other WSs.   The Notes in the WB should help.

Thank you in advance.  Hope this makes sense.

B.
Currency-Update-and-Displayv8.xlsm

When I change pounds to yen what happens and what should happen. Please give me numbers for the given file.

When selecting pounds to yen in the Customer-Input WS, then the format changes to pounds and the numbers should change.  

AND,

In Column I in Currency WS, will change from 102,300,000 Yen to 613,000 Pounds (and the other cells in COLUMN I will also change to Pounds and the Pound values), and then be reflected in Pounds in the reference cells in G.  The reason that I'm using the reference for moving the data to a particular WS and cell, is I need to scale it and as I do, I'll be adding both numbers and references.

Make sense?

B.

Sorry, Ssaqibh,  

I gave you the wrong answer to your question.  When you change from POUNDS to YEN, it should change (in the first row), from 613,000 POUNDS to 102,300,000 YEN and reflect the change in the reference cells.  This should be a simple look up and post.  The reason for the reference cells is so I can scale the macro to other cells.  

If you look at the mocked up WB here, you can see where you start and what I'm trying to have happen.

Hope this helps.

B.
Currency-Update-and-Displayv9.xlsm

Ssaqibh,

I have revised the spreadsheet to make it much simpler to understand.  This is simply a selection (Type of Currency on the Customer Tab), and then a formula to take the dollar value and convert it to the selected Currency value.  Then place that value in a cell that is referenced in the Currency Sheet.

That's it.

See if that makes it easier to build out the macro.

Thanks and I hope you have had a wonderful holiday season.

B.
Copy-of-Currency-Update-and-Disp.xlsm

Hi Bright.

1.  I realise you have put some effort into this already but feel there needs to be a better match between the currency you select on <Inputs> and the list of names returned from MSN Money in order to do the lookup properly.

eg China Yuan not Yuan (China)  
United Kingdom Pound  not Pounds ...  South Africa Rand; what about Canadian Dollars, Australian Dollar

It would be best to construct a drop down list of currencies available.

2.  Here is the process as I understand it. please confirm.
Sub DoUpdate()

    '1.  determine output currency. from Input sheet
    '2.  get the exchange rate from MSN Money query
    '3.  for each of the references on Currency sheet, Do CurrencyUpdate macro
    '3.1    convert from Dollars to SelectedCurrency
    '3.2    format the new value as text (eg INR xx.yy)  [macro done... sort of]
    '3.3    copy formatted text to referenced location. [macro done]
   
End Sub

my attempt.

'ee robberbaron  4.Jan.14
'v 11
Sub DoUpdate()

    '1.  determine output currency. from Input sheet
    Dim currcy_idx As Integer, currcy_name As String, currcy_val As Single
    currcy_name = Range("Start_of_CurrencyList").Offset(0, 2).Value   'the cell right of tag
    
    '2.  get the exchange rate from MSN Money query
        currcy_val = Range("Start_of_CurrencyList").Offset(0, 3).Value   'the cell right of tag

    '3.  for each of the references on Currency sheet, Do CurrencyUpdate macro
    '3.1    convert from Dollars to SelectedCurrency
    '3.2    format the new value as text (eg INR xx.yy)  [done... sort of]
    '3.3    copy formatted text to referenced location. [done]
    CopyData currcy_name, currcy_val
    
End Sub
Function GetFormating(currcy_name As String) As String
    Set cWS = Sheets("Currency")
    
    Set rFind = cWS.Range("CurrencyFormats").Find(What:=currcy_name, LookAt:=xlWhole)
    If rFind Is Nothing Then
        'other
        Set rFind = cWS.Range("CurrencyFormats").Find(What:="Other", LookAt:=xlWhole)
    End If
    GetFormating = rFind.Offset(0, 1).NumberFormat
  
End Function
Sub CopyData(curc_name As String, curc_rate As Single)
    Dim cel As Range, cn As Range, dollarval As Double
    Sheets("Currency").QueryTables(1).Refresh
    For Each cel In Sheets("Currency").Range("G4:G100")
        If cel.Value <> "" Then
            shtname = Replace(Split(cel.Value, "!")(0), "'", "")
            cellid = Split(cel.Value, "!")(1)
            dollarval = cel.Offset(0, 1)
            cel.Offset(, 2).Value = dollarval / curc_rate
            Sheets(shtname).Range(cellid).Value = cel.Offset(, 2).Value
            Sheets(shtname).Range(cellid).NumberFormat = GetFormating(curc_name)
            
        End If
    Next cel
End Sub

Sub UpdateCurrencyDropDown()
    Sheets("Currency").QueryTables(1).Refresh
    Waite 2  'for refresh
    Dim rng As Range, currcy As Range
    Set rng = Range("Start_of_CurrencyList").Offset(1, 0) 'the row below tag
    Set currcy = rng.Offset(0, -9)
    Do While Not IsEmpty(currcy)
        x = InStr(currcy.Value, " - ")
        If x = 0 Then x = Len(currcy.Value)
        rng.Value = Left(currcy.Value, x - 1)
        rng.Offset(0, 1).Value = currcy.Offset(0, 1).Value
        
        Set rng = rng.Offset(1, 0) 'next row
        Set currcy = rng.Offset(0, -9)
    Loop
    
    'now reset the range of names
    ActiveWorkbook.Names("CurrencyConversions").RefersTo = Range(Range("Start_of_CurrencyList").Offset(1, 0), rng.Offset(-1, 1))
    
End Sub
Sub Waite(secs As Integer)
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + secs
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
End Sub

Select allOpen in new window

Currency-Update-and-Disp11.xlsm

Robberbaron  (Rob?),

Thank you for jumping in on this.  YES; you are correct!  And your code on v11 is amost perfect.  Three minor "tweeks");

1.) When you select the Currency on the Input WS, it should automatically convert.  The Update button on "Currency" tab was there to ask a different question which was to auto update the MS Currency Table.

2.) Can you get the format to play out in Cells in "I" on the Currency Tab?  Right now it remains as "Pounds"

3.) Can you get the heading in I to read the right currency?

Again, your logic to my complex explanation was "spot on".  Thank you again.


Here is the process as I understand it. please confirm.
Sub DoUpdate()

    '1.  determine output currency. from Input sheet
    '2.  get the exchange rate from MSN Money query
    '3.  for each of the references on Currency sheet, Do CurrencyUpdate macro
    '3.1    convert from Dollars to SelectedCurrency
    '3.2    format the new value as text (eg INR xx.yy)  [macro done... sort of]
    '3.3    copy formatted text to referenced location. [macro done]
   
End Sub

version 12 attached.

it does an update of the currencies upon Workbook_Open, to populate the dropdown list and get latest rates.

upon change of currency, it does another refresh of rates.  The Update button on Currencies has been changed to be 'Update Rates' only.
Currency-Update-and-Disp-v12.xlsm

@firefytr

1. the list you refer to is attached to the MSN money data query and it is possible that it will shrink or expand each time. (though unlikely to shrink i agree). I make no assumptions about its size so I recreate the CurrencyConversions named range each time to match the number of items in the query.  impact is minor.

2. Totally agree about the number of redundant named ranges in the workbook but this is for the author to fix as i suspect there are more functions other than this sample.

3. I actually find splitting the code into smaller routines easier to debug and test. Likewise I create variables to hold data rather than refer to the range value each time. Just my style & I find the speed overhead with Excel VBA is generally very small.

4. I like the ToggleEvents routine. very easy to reuse !  Is there a reason you use Call ToggleEvents(False) style  rather than ToggleEvents False

Hi there,

I hope you don't mind my critique. I can be a bit forthcoming and straightforward, and have been called pretentious more than once.

1: there's still no need to create the names range every time, not with the dynamic name I posted. ;)

2 & 3: too true. :)

4: thanks! It's just habit to call routines like that. Like using ".Value" to reference a range value. It's the default property, but I try to do it all the same. If you have ever crossed over to VB/.NET or C# you'll understand.  It's a good habit and what I consider best practices. Plus it improves readability, which in turn makes maintenance easier. And we must always keep in mind those who will be using our code once it leaves our hands. :) But the routine certainly does help speed up your code execution, as well as help prevent multiple firings when dealing with events.

Zack

Hey guys,  great commentary and progress.  Thank you so much for working through this.  This WB does have history.  You can ignore the range names and the code that was already in the WB; What I like to do is to have a small, simple macro, that I then incorporate into a master WB for incorporation into the production WB and I think you have a clear understanding of what this macro is suppose to do.  You are also correct in that the MS Money conversion rates are periodically updated.  

I look forward to seeing the approach you guys take on this.... and again.... "thank you".

B.

Sorry... just looked at V12.  It works very well!  Exactly as prescribed.  One quick question; I had originally not required the MS Conversion to "update"... I thought I'd do that with a separate Macro (would put a button on the Currency Tab) to update the table.  That way I wouldn't have an error problem every time someone used the WB but didn't have an internet connection.

Can that be accomodated easily?  Otherwise, this looks very very good!!!!

B.

You need to do a few things in order to accomplish this.

1. Comment out the query refresh

Look in the 'CopyData' routine, put a single apostrophe in front of this line...

Sheets("Currency").QueryTables(1).Refresh

Select allOpen in new window

2. (Optional) Comment out name range update

In the 'UpdateCurrencyDropDown' routine, the last line updates the named range. It is not needed. This is the line of code...

ActiveWorkbook.Names("CurrencyConversions").RefersTo = Range(Range("Start_of_CurrencyList").Offset(1, 0), rng.Offset(-1, 1))

Select allOpen in new window

If you do this you'll need to rename that named range the formula I posted earlier.
Hit CTRL + F3 to open the name manager
Find 'CurrencyConversions'
Click the Edit button on top of the dialog box
Change the RefersTo to formula to this...

=Currency!$AE$7:INDEX(Currency!$AF:$AF,MATCH(9.99E+307,Currency!$AF:$AF))

Select allOpen in new window

HTH

Zack

Great Teamwork!  And Robberbaron, thank you for finishing this up.  I know it wasn't well explained but you guys really did a good job understanding what this complex macro needed to accomplish...... and for this.......I'm very grateful.

Best regards and thanks again,

B.