Solved

Currency Conversion Update Macro

Posted on 2013-12-09
30
247 Views
Last Modified: 2014-01-07
SeanStrickland (EE Pro.) wrote a nice little macro for me that updates a Currency Conversion table.  Here's the challenge;

1.) The update to currency within the WS needs to take place when the currency is SELECTED on the first tab; independent of the Update from MS Money.

2.) When the "UPDATE!" Macro fires, it should then update the currency table with the MS Money data and update all values in the table.

That's it!

And ..... "Thank you in advance" for this modification.

B.
Copy-of-Currency-Update-and-Disp.xlsm
0
Comment
Question by:Bright01
  • 13
  • 7
  • 5
  • +1
30 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Can you email this file to me? I am having problem accessing it from EE
0
 

Author Comment

by:Bright01
Comment Utility
ssaqibh,

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

B.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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
0
 

Author Comment

by:Bright01
Comment Utility
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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

Open in new window

0
 

Author Comment

by:Bright01
Comment Utility
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
0
 

Author Comment

by:Bright01
Comment Utility
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.
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 100 total points
Comment Utility
Hi, B,

Many apologies for disappearing.

The problem is that I have made several attempts at trying to understand your requirement but every time I get confused and then I defer it.

Can you help me by spelling out what you want. For the last workbook you uploaded give me two examples of what you want to do and what you expect to see.
0
 

Author Comment

by:Bright01
Comment Utility
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?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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.
0
 

Author Comment

by:Bright01
Comment Utility
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
When I change pounds to yen what happens and what should happen. Please give me numbers for the given file.
0
 

Author Comment

by:Bright01
Comment Utility
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.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Bright01
Comment Utility
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
0
 

Author Comment

by:Bright01
Comment Utility
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
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
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
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
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

Open in new window

Currency-Update-and-Disp11.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
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
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
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
0
 
LVL 14

Assisted Solution

by:Zack Barresse
Zack Barresse earned 100 total points
Comment Utility
Hi there,

Your UpdateCurrencyDropdown routine doesn't need a Do Loop, which should really be avoided unless necessary. You can do it with a For Each loop like so...

Sub UpdateCurrencyDropdown2()
    Dim CurrencySheet           As Worksheet
    Dim RatesData               As Range
    Dim Cell                    As Range
    Set CurrencySheet = ThisWorkbook.Worksheets("Currency")
    Set RatesData = CurrencySheet.Range("V7", CurrencySheet.Range("V6").End(xlDown))
    For Each Cell In RatesData.Cells
        Cell.Offset(0, 9).Value = Split(Cell.Value, " - ")(0)
        Cell.Offset(0, 10).Value = Cell.Offset(0, 1).Value
    Next Cell
End Sub

Open in new window


* This assumes the list will not shrink, as it does not clear previous data.

And no need to keep renaming the named range. Since it's setup in it's own little range off in columns AE:AF, you just need one named range, no updates needed (assuming nothing will go below it)...

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

Open in new window


I would SERIOUSLY think about cleaning up the other named ranges you have in the file and deleting anything you don't need. I lost count of how many #REF errors I saw. Salesforce data can plague a file with uneeded named ranges and styles.

You can shorten all of the other routines into a single one, I'm not sure why they're so spread out, makes it difficult to traverse/maintain them. This is basically the same thing but condensed a bit, with some more explicit referencing...

Sub CopyData()
    Dim CurrencySheet           As Worksheet
    Dim InputsSheet             As Worksheet
    Dim QuoteSheet              As Worksheet
    Dim CellValue               As Double
    Dim FormatRow               As Long
    Dim InputData               As Range
    Dim Target                  As Range
    Dim Cell                    As Range
    Dim SheetName               As String
    Dim RangeAddress            As String
    Set CurrencySheet = ThisWorkbook.Worksheets("Currency")
    Set InputsSheet = ThisWorkbook.Worksheets("Customer-Inputs")
    Set QuoteSheet = ThisWorkbook.Worksheets("Price Quote")
    Set InputData = CurrencySheet.Range("G4", CurrencySheet.Cells(CurrencySheet.Rows.Count, "G").End(xlUp))
    If InputData(1, 1).Row < 4 Then
        MsgBox "There was no data in column G to process.", vbExclamation, "Whoops!"
        Exit Sub
    End If
    Call TOGGLEEVENTS(False)
    CurrencySheet.Range("I3").Value = "Value in " & CurrencySheet.Range("ConvertName").Value
    For Each Cell In InputData.Cells
        SheetName = Left(Cell.Text, InStrRev(Cell.Text, "!") - 1)
        RangeAddress = Right(Cell.Text, Len(Cell.Text) - Len(SheetName) - 1)
        If WSEXISTS(SheetName, ThisWorkbook) = False Then GoTo SkipCell
        Set Target = Nothing
        Set Target = ThisWorkbook.Worksheets(SheetName).Range(RangeAddress)
        If Target Is Nothing Then GoTo SkipCell
        On Error Resume Next
        FormatRow = 0
        FormatRow = WorksheetFunction.Match(CurrencySheet.Range("ConvertName").Text, CurrencySheet.Range("CurrencyNames"), 0)
        If FormatRow = 0 Then FormatRow = WorksheetFunction.Match("Other", CurrencySheet.Range("CurrencyNames"), 0)
        On Error GoTo 0
        If FormatRow = 0 Then GoTo SkipCell
        CellValue = Cell.Offset(0, 1).Value
        Cell.Offset(0, 2).Value = CellValue / CurrencySheet.Range("ConvertRate").Value
        Cell.Offset(0, 2).NumberFormat = CurrencySheet.Range("CurrencyFormats")(FormatRow, 2).NumberFormat
        Target.Value = Cell.Offset(0, 1).Value
        Target.NumberFormat = Cell.Offset(0, 1).NumberFormat
SkipCell:
    Next Cell
    Call TOGGLEEVENTS(True)
End Sub

Sub TOGGLEEVENTS(blnState As Boolean)
    'Originally written by Zack Barresse
    Application.DisplayAlerts = blnState
    Application.EnableEvents = blnState
    Application.ScreenUpdating = blnState
    If blnState Then Application.CutCopyMode = False
    If blnState Then Application.StatusBar = False
End Sub

Function WSEXISTS(ByVal wksName As String, Optional WKB As Workbook) As Boolean
    If WKB Is Nothing Then
        If ActiveWorkbook Is Nothing Then Exit Function
        Set WKB = ActiveWorkbook
    End If
    On Error Resume Next
    WSEXISTS = CBool(Len(WKB.Worksheets(wksName).Name) <> 0)
    On Error GoTo 0
End Function

Open in new window


This does assume you have some additional named ranges:
Currency sheet AG6 refers to "ConvertName"
Currency sheet AH6 refers to "ConvertRate"
Currency sheet B4:B12 refers to "CurrencyNames"

Also, the combobox would need to point to the routine 'CopyData' instead of what it is.

I remember this workbook from a few months ago. I would caution you now as well, always be mindful of your data structure. Everything depends on it. The way you have this structured is still somewhat confusing.

HTH

Regards,
Zack Barresse
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
@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
0
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
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
0
 

Author Comment

by:Bright01
Comment Utility
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.
0
 

Author Comment

by:Bright01
Comment Utility
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.
0
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
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

Open 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))

Open 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))

Open in new window


HTH

Zack
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 300 total points
Comment Utility
I have made the changes to the workbook.
1. Removed the Rates query update from load and Currency change.
2. The button on Currency page was already there and updated the rates and the currencies available in the DropDown selection.
3. Removed the pauses needed to allow Query refresh and Added Zacks code to turn off noticiations; should be noticably quicker on currency change.
Currency-Update-and-Disp-v13.xlsm
0
 

Author Closing Comment

by:Bright01
Comment Utility
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.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel - Scroll Speed 3 24
Word 2010 mail merge 3 32
Redacting a row in Excel based on a term. 17 31
Excel 2016 Hiding Toolbars 7 18
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now