Is there a way to write a local language - independent formula within a format condition in VBA in excel?

user_4482350 0
Viewed 201

I am writing a excel macro with VBA that contains a simple formula:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AND($L1>360;$K1<45))"

However, this does only work, if the language in excel is set to English. If the applicants language is for instance German, it looks like that:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NICHT(UND($L1>360;$K1<45))"

Is there a way to use a generic one that works for all applicants?

Some requirements regarding the solution:

  • It should not be specific (no translation from english into german)
  • Also a select case statement with the language ID is not applicable
  • There is an option with a function translator: This cannot be applied due to admin restrictions (and I am curious, if there is another way :) )
  • Changing the formula to use it within a cell is also not applicable, since I need the format condition there

Any help is appreciated

regional-settings vba excel
2 Answers

Actually that is not possible, but there is a workaround.

First note that english versions use , instead of ; as separator, so your english formula needs to be =NOT(AND($L1>360,$K1<45)).

The workaround is to write that formula to any unused cell.

Range("unused_cell").Formula = "=NOT(AND($L1>360,$K1<45))"

Then read the localization of that

Dim FormulaLocal As String
FormulaLocal = Range("unused_cell").FormulaLocal

and use that as condition

Selection.FormatConditions.Add Type:=xlExpression, Formula1:=FormulaLocal

That will translate the condition to the correct localisation of your Excel.

user_32196130

The formula for conditional formatting is region specific and there is no option - neither in VBA nor in the GUI - to handle this except you omit all language specific keywords (or do a "translation" as in the previous answer suggested)

This will work:

=($K1<45)*($L1>360)=0
user_165784240
Related