Excel Macro, inserting internationally valid formula during run-time

Question

I've got an Excel spreadsheet, with a Macro, that inserts a conditional formatting, like this:

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=UND($A3=""" & lastName & """; $B3=""" & firstName & """)"

As you can see, I've used the German formula for "AND" (i.e. "UND"), and obviously, this code doesn't work as soon as I use it on a French or English version of Excel. Usually formulas are localized automatically, but how can I insert a formula during run-time that will work on ALL versions?

Answer 1

Ok, thanks for helping me with this, you've helped me crack this one.

It is indeed not possible to just use English. One can use English when operating on a formula, eg. by setting coding Range("A1").formula="AND(TRUE)", but this does not work with FormatConditions.

My solution is a function that writes a formula temporarily to a cell, reads it through the FormulaLocal property, and returns the localized formula, like so:

Function GetLocalizedFormula(formula As String)
' returns the English formula from the parameter in the local format
  Dim temporary As String
  temporary = Range("A1").formula
  Range("A1").formula = formula
  Dim result As String
  result = Range("A1").FormulaLocal
  Range("A1").formula = temporary
  GetLocalizedFormula = result
End Function

The returned formula can be used on FormatConditions, which will be re-localized or un-localized when the document is later opened on a different-language version of Excel.

Answer 2

I just found a very elegant solution to the problem in a German Excel forum. This doesn't write to a dummy cell but rather uses a temporary named range. I used the original idea (credit to bst) to write a translating function for both directions.

Convert localized formula to English formula:

Public Function TranslateFormula_LocalToGeneric(ByVal iFormula As String) As String
    Names.Add "temporaryFormula", RefersToLocal:=iFormula
    TranslateFormula_LocalToGeneric = Names("temporaryFormula").RefersTo
    Names("temporaryFormula").Delete
End Function

Convert English formula to localized formula:

Public Function TranslateFormula_GenericToLocal(ByVal iFormula As String) As String
    Names.Add "temporaryFormula", RefersTo:=iFormula
    TranslateFormula_GenericToLocal = Names("temporaryFormula").RefersToLocal
    Names("temporaryFormula").Delete
End Function

This is very handy if you need to deal with formulas in conditional formatting, since these formulas are always stored as localized formulas (but you could need their generic version, e.g. to use Application.Evaluate(genericFormula)).

Answer 3

Store (a trivial version of) the formula in a (hidden) cell in your workbook.

Then when you open the workbook that formula will be translated automatically by excel for the user.

Now you just have to dissect this formula in your script (find the opening bracket "(" and take the past left of that:

Use something like:

strLocalizedFormula = Mid(strYourFormula, 2, InStr(1, strYourFormula, "(") - 2)

where strYourFormula will be a copy from the formula from your worksheet.

I hope this works as I only use an English environment.

Also from reading this: http://vantedbits.blogspot.nl/2010/10/excel-vba-tip-translate-formulas.html I am thinking you should (only) be able to use the english version of a cell formula from VBA.