Force numerical format / language for the document

Question

Force numerical format / language for the document

Navigation

#1 by (3 votes)

3

Where I work, it is very common to have problems with excel macros, which generate tables and graphs, depending on the language and version of Excel the client is using.

How to force a language (for example English) and consequent numerical formats? That way a thirty percent would appear, for example, always as 30.00% and a thousand always as 1,000.00 regardless of the language of the user?

PS: I know you can use .NumberFormat in each cell, but I'm looking for a global setting that resolves all numbers within the document.

excel vba

asked by anonymous 06.04.2015 / 17:00

1 answer

How to retrieve a string-array item within the layout of my activity, in the "xml" case? How to allow a DecimalField field with the currency format BR in Django?

score 3 · Accepted Answer

If you want to use the dot instead of the comma throughout the fields in the worksheet you can access the EXCEL > OPTIONS > ADVANCED > 'Use System tab' and set what you want.

To do this via code in VBA use the following function:

Function setSeparadorDecimal()
  With Application
    .DecimalSeparator = "."
    .ThousandsSeparator = ","
    .UseSystemSeparators = False
  End With 
End Function

As warned by our friend @mathiasfk, the above function changes the Application and not just the active file, so if you want to return the decimal separator from Excel to the default, use the function below :

Application.UseSystemSeparators = True

I hope I have helped!