Format cell as arbitrary currency regardless of language using VBA
This really annoys me as it seems rather counterintuitive how it works.
I have a macro to format a cell as currency using a bit of code to get the currency symbol.
Here is the code:
Dim sym As String
sym = reportConstants(ISOcode)
'Just use the ISO code if there isn't a symbol available
If sym = "" Then
sym = ISOcode
End If
With range(.Offset(0, 3), .Offset(3, 3))
.NumberFormat = sym & "#,##0;(" & sym & "#,##0)"
Debug.Print sym & "#,##0;(" & sym & "#,##0)"
End With
reportConstants
- a dictionary object with currency symbols defined as strings. For example. reportConstants("USD") = "$"
... This is defined earlier in the macro.
When the macro runs, it gets the ISO code and then formats the cell with the appropriate currency symbol.
When I run it in one instance, the ISO code is "USD" - so it sym
is defined as "$"
- but it still formats the cell with the pound (£) sign. When I debug.print
string the format cell shows $#,##0;($#,##0)
, so as long as I understand my syntax correctly it should use the dollar sign in the cell. But instead he uses a sign. (I am using UK version of excel, so it might not be valid for £ -sign, but why?)
Any help is greatly appreciated.
source to share
Btw think I read your question a bit after posting;) Excel is highly dependent on your computer's regional settings for currency, language, dates ... Using numberformat can force it to keep the required sign. if it's a rounding issue you can try: In Excel 2010 go to File Options - Advanced and scroll down to When Calculating This Workbook and click Set Precision As Shown and OK.
Try this if your values are numbers / integers / decimals ....
Range("a2").Style = "Currency"
Or you can use the format:
Format(value, "Currency")
Format(Range(a2).value, "Currency")
Literature:
http://www.mrexcel.com/forum/excel-questions/439331-displaying-currency-based-regional-settings.html
http://www.addictivetips.com/microsoft-office/excel-2010-currency-values/
(PS: I'm on mobile, you can try these two links)
source to share