Clever Geek Handbook

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.

+3


source to share


2 answers


I just recorded a macro to change the format in the $ xx.xx, and he created it: [$$-409]#,##0.00

. It looks like -409 localizes the currency in a specific country; it works without it - try changing your to.NumberFormat = "[$" & sym & "]#,##0.00"



+6


source


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)

0


source






More articles:


All Articles