Using VBA to detect which decimal sign the computer is using
I didn't actually know the Formula
s 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 howApplication.DecimalSeparator
andApplication.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 (whenApplication.UseSystemSeparators = True
) or from Excel options (whenApplication.UseSystemSeparators = False
)
I therefore strongly recommend to always use Application.International(xlDecimalSeparator)
.