Format a cell as arbitrary currency regardless of locale, using VBA
excel, number-formatting, vba
Solution
I just recorded a macro to set the format to $xx.xx and it created this: `[$$-409]#,##0.00`. Looks like the -409 localises the currency to a particular country; it works without it - try changing yours to `.NumberFormat = "[$" & sym & "]#,##0.00"`
Problem
This is really bugging me as it seems pretty illogical the way it's working. I have a macro to format a cell as a currency using a bit of code to obtain the currency symbol. Here is the code involved: ``` 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` is a dictionary object with currency symbols defined as strings. E.g. `reportConstants("USD") = "$"`. This is defined earlier in the macro. When the macro runs it gets the ISO code and should then format the cell with the corresponding currency symbol. When I run it in one instance the ISO code is "USD" - so `sym` is defined as `"$"` - but it still formats the cell with a pound sign (£). When I `debug.print` the format cell string it shows `$#,##0;($#,##0)` so, as long as I got my syntax correct, it should use a dollar sign in the cell. But it uses a £ sign instead. (I am running a UK version of excel so it may be defaulting to £-sign, but why?) Any help greatly appreciated.