Force numeric/language format to document

Question

Where work is very common we have problems with excel macros, which generate tables and charts, 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 independent of the user’s language?

PS.: I know it is possible to use .NumberFormat in each cell, but I’m looking for a global setting that solves all the numbers within the document.

Answer 1

If you want to use the dot instead of the comma in all fields of the worksheet you can access the menu EXCEL > OPTIONS > ADVANCED > 'Use system separator' and define 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 Applying and not only the active file, so if it is interesting to return the Excel decimal separator to the default, use the function below:

Application.UseSystemSeparators = True

I hope I’ve helped!