Using VBA sheet code to perform large scale currency conversion

[RoneTyne]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Wondering if you can help. I have a workbook with 6 different worksheets each containing a report in Excel 2007. All values in the worksheets are in $USD.<o></o>
<o></o>
I would like to be able to provide functionality to the reports global users to convert the reports into other currencies, namely €EUR, £GBP, $CAD and ¥YEN. Whether this conversion happens on a per sheet level or on the coversheet and propagates through the whole workbook doesn't really matter.<o></o>
<o></o>
The thought is that I will have a hidden worksheet with all the conversion rates I wish to use (this will be static and due to company policy must be updated by me each time the report is sent out on monthly basis).<o></o>
<o></o>
When a user selects and alternative currency from either a drop down list or some sort of form control, I would like to have all the values on the worksheet (or in the workbook) converted from the $USD amount to the new currency. I would also require that the currency symbol be changed as well.<o></o>
<o></o>
I know I can create hidden columns and use formulas with vlookups to do this.<o></o>
<o></o>
But what I'm really hoping is that you all will be able to provide some guidance on how to use VBA code to perform this. Is that possible, if so, how?<o></o>
<o></o>
Any help you can provide would be greatly appreciated.<o></o>
<o></o>
<o></o>
- RoneTyne<o></o>

 

[GlennUK]

I know I can create hidden columns and use formulas with vlookups to do this.

But what I'm really hoping is that you all will be able to provide some guidance on how to use VBA code to perform this.

Surely you would simply need alternate sheets, with the conversion done with formulas, one sheet per currency. You could hide/unhide sheets for the chosen currency via VBA.

 

[RoneTyne]

Surely you would simply need alternate sheets, with the conversion done with formulas, one sheet per currency. You could hide/unhide sheets for the chosen currency via VBA.

Hi GlennUK,

That is another way to do it which is still better than adding a dozen or more columns per sheet. Do you know what the VBA script to hide and unhide sheets looks like? Thanks for the reply.


- RoneTyne

 

[GlennUK]

To loop through all sheets you'd do something like:

For Each ws In ThisWorkbook.Worksheets

and within the loop test to see whether the sheet is to be displayed or not ( maybe you'd have a currency code in the sheetname ), like:

If Instr(1,ws.Name,"GBP") > 0 Then
   ws.Visible = True
Else
   ws.Visible = xlHidden
End If

That should give you a good starting point.