CODEWITHSUNDEEP
excelvba
Orbit
Orbit

Reputation: 212

How do Excel VBA Number format options work for users with different regional settings?

I would like a number to show only 2 decimal places behind the comma (or point). I found that i can do that by specifying someRange.NumberFormat = "#.##", but how will this work if a user has set a comma as a decimal separator?

Upvotes: 3

Views: 1837

Answers (1)

CLR
CLR

Reputation: 12279

When setting NumberFormat, the US English format string should be entered - i.e. "#.##". That is to say, regardless of the .DecimalSeparator set by the user/environment, the format string requires a period (.) to denote the decimal separator.

someRange.NumberFormat = "#.##"

The above would be safe to run regardless of the users locale settings or .DecimalSeparator set by the user.

If you'd prefer to set it using an alternative language/locale format string e.g. German, then you need to tell Excel you're giving it the locale appropriate format - however, you'd need to test the locale settings beforehand to ensure it was going to work - in case the program is not being run with that locale setting:

someRange.NumberFormatLocal = "#,##"

I can't think of a good reason why you'd want to do it that way, unless you were only sure of the e.g. German format string. If that's the case, then you could check what it is in US English by reading the non-local property:

someRange.NumberFormatLocal = "#,##"
debug.print someRange.NumberFormat

Output:

#.##

Upvotes: 4

Related Questions