Imagine you have built a business case for a client that wants to easily see the revenue-cost implications in both local and foreign currencies. The following macro – attached to a form control (either button or option button) can automate this task, and free both you and your client from resorting to calculators and currency converters during this process.
The currency toggle macro will accomplish 2 things:
- multiply all the impacted values by the indicated currency factor
- change the cell format to reflect the selected currency
There are 2 approaches to getting the first task done – either by:
- creating a macro that multiplies all the values in the relevant cells by the currency factor, or
- entering the currency factor in an Assumptions cell ($C$1), and ensuring all the relevant cells are connected to it throughout the process of building the model.
The first method will require more Excel memory to execute as you are creating a macro that will interact with the sheet data very frequently, and will result in overwriting of other formulas existing in the impacted cells. The second method requires more effort during the model build process, but I believe is more flexible to operate and update. Thus this post will focus on how to execute the second method.
In the second approach, a toggle value cell ($AB$1) and a formula creating a toggle function for the currency conversion is used. The toggle value cell can take 2 or more modes, and the formula is an IF() formula checking to see which value is in the toggle cell and recalculating the cell as appropriate.
In the example below, toggle state one (1) represents USD currency, while state two (2) represents NGN currency. The formula in every cell will thus look like the one below:
=IF($AB$1=1,A2+B2, IF($AB$1=2,(A2+B2)*$C$1,0))
(the italicized values will be replaced with the appropriate formula or value based on your model)
Thus the macro to change currency to USD (dollar) will be this simple code in a module (use shortcut Alt+F11 to open Visual Basic Editor, select Insert > Module):
Sub DollarCurr()
Range(“AB1”).value = 1
End Sub
And the one to change currency to NGN (naira) will be this:
Sub NairaCurr()
Range(“AB1”).value = 2
End Sub
To change the cell format to reflect the selected currency, first program the desired cell formats using Cell Styles (Cell Style > New Cell Style).
By using cell styles, you can decide the appropriate currency cell format, change font color, font style, cell color and other variables as you deem fit. In my example above, the final cell styles look like this:
After this is done, the macros above will be updated to reflect a command to change the cell styles in all the impacted cells (Range $A$3 to $J$10 in my example) as well:
Sub DollarCurr()
Range(” AB1″).Value = 1
Range(“A3:J10”).Style = “Dollar currency”
End Sub
And the one to change currency to NGN (naira) will be this:
Sub NairaCurr()
Range(“AB1”).Value = 2
Range(“A3:J10”).Style = “Naira currency”
End Sub
To interact with these macros in the main sheet, you can either use option buttons or simple buttons (Developer > Insert > Form Control > Button, then right-click the created form control to Assign Macro to the appropriate code).
