Using VBA to detect which decimal sign the computer is using

I didn't actually know the Formulas in FormatConditions accept localized formulas. In other places you have a choice between Formula and FormulaLocal.


Please note:
This part turned out to be oversimplified to the point of being wrong. Please refer to the other answer (which should really have been the accepted one) for how Application.DecimalSeparator and Application.International(xlDecimalSeparator) actually behave.

To simply answer the question, you can use Application.International(xlDecimalSeparator) or simply Application.DecimalSeparator to know the separator.


But for non-trivial formulas it might be easier to assign the invariant English-locale based formula to the Formula property of a hidden cell and then read FormulaLocal from that cell and use that for FormatConditions. Excel will do all the conversions for you.


Regarding the answer above, it is important to know that Application.DecimalSeparator and Application.International(xlDecimalSeparator) do not behave the same way:

  • Application.DecimalSeparator will ALWAYS output the decimal separator chosen in Excel options even when Excel is told to use System Separators (from Windows regional settings)
  • Application.International(xlDecimalSeparator) will output whatever is the actual decimal separator used by Excel whether it comes from Windows settings (when Application.UseSystemSeparators = True) or from Excel options (when Application.UseSystemSeparators = False)

I therefore strongly recommend to always use Application.International(xlDecimalSeparator).

Tags:

Excel

Vba