25

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?

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
grovel
  • 643
  • 1
  • 6
  • 11
  • 1
    [Different languages issue when inserting formula from VBA](https://stackoverflow.com/q/35724156/995714) – phuclv Oct 19 '19 at 13:11

7 Answers7

16

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.

mheim
  • 366
  • 1
  • 12
grovel
  • 643
  • 1
  • 6
  • 11
  • btw. this temporarily destroys the value of A1, but then writes it back again. No issue in my implementation, but you might want to change the temporary cell if you use this yourself. – grovel Nov 06 '12 at 15:47
  • you can also create a temporary worksheet for this, so you never interfere with any existing worksheet functionality you have (that relates to A1) – K_B Nov 06 '12 at 15:53
  • Automatic translation of formulas didn't work on validation functions either, but this solution saved my day. – Kaniu Apr 22 '13 at 12:51
  • 2
    This workarount is also limited to be used in sub procedures, as functions are not allowed to change any cell... – DrMarbuse Jun 22 '15 at 15:06
  • 1
    Just a VBA tip: don't give yourself the false impression that variables aren't declared until the `Dim` line. In VBA, variables are *hoisted*, which means that putting your `Dim`s right before using or in the beginning of the function are the same, but the former will eventually fool you into doing something wrong. – André Chalella Jul 15 '15 at 16:24
10

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)).

Marcus Mangelsdorf
  • 2,852
  • 1
  • 30
  • 40
  • 2
    This is a great idea, but it adds the sheet name to the range references ("$B$9" becomes "MySheet!$B$9" and FormatConditions doesn't like a Range with a sheet name. – Julien N Nov 06 '15 at 15:46
  • 2
    That would be really neat if it worked. Unfortunately, it doesn't. (On my computer, RefersTo also contains the local formula.) – z32a7ul Mar 30 '17 at 09:37
  • @MarcusMangelsdorf - For me the formula translation via adding names works in both directions without any issues +1 (local language German :-) – T.M. Aug 28 '18 at 18:19
  • It works very well for some cases, but it depends on the selected cell in some cases... "$B2" becomes "MySheet!$B19" if the cell selected is on the 18th row, "MySheet!$B$12" if it's on the 11th row... – Rafiki Dec 17 '19 at 10:52
  • It's not working I am getting an error like if I can't use formula's in RefersTo field. – JustGreat Jun 30 '20 at 22:25
5

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.

K_B
  • 3,668
  • 1
  • 19
  • 29
2

Maybe try this (untested as I only have English version insatlled)

Write your international version of the formula to an out of the way cell using Range.Formula . Then read it back from Range.FormulaLocal, and write that string to the FormatConditions

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
0

I know this thread is ages old, and someone may have found an elegant solution, but I just had the same problem where I needed to apply conditional formatting without modifying the sheet, creating temporary cell contents or named ranges. All users use English language versions of Excel, so the functions used in the formulas are the same, but the regional settings vary by location, and therefore also the parameter separater; In Norwegian, it's ";" instead of ",", much like the rest of Europe, I guess.

For example, I needed to automatically create conditional formatting, using Excel formula for the following criterion:

.FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(B" & I & "),B" & I & ">=" & Ul1 & ")"

Where "Ul1" is a value defined in a previous step, and it's not important for the solution.

However, I needed to be able to run this on computers with both Norwegian and English settings

I and found a very short and simple solution from Andrew Pulsom here: https://www.mrexcel.com/board/threads/french-vba-vs-english-vba.729570/. He just made the parameter separator into a variable:

If Application.International(xlDecimalSeparator) = "," Then
    Sep = ";"
Else
    Sep = ","
End If

Cl1 = "=AND(ISNUMBER(B" & I & ")" & Sep & "B" & I & "<" & Ul1 & ")"

Worked like a charm for me :)

I know that this only solves part of the problem, but I assume that this could apply to many international companies which use English Office installations with local regional settings.

LarsS
  • 163
  • 1
  • 15
-1

Please refer to the link for more explanation: https://bettersolutions.com/csharp/excel-interop/locale-culture.htm

CultureInfo baseCulture = System.Threading.Thread.CurrentThread.CurrentCulture; 
Thread.CurrentThread.CurrentCulture = new CultureInfo(xlapp.LanguageSettings.LanguageID(Office.MsoAppLanguageID.msoLanguageIDUI)); 
// do something 
System.Threading.Thread.CurrentThread.CurrentCulture = baseCulture;
Karthik Venkatraman
  • 1,619
  • 4
  • 25
  • 55
user9479152
  • 1
-2

Thanks everyone! I found the post very useful.

My solution is a combination of others, I add it in case somebody finds it useful.

Dim tempform As String
Dim strlocalform1 As String
Dim strlocalform2 As String

' Get formula stored in WorksheetA Cell O1 =IFERROR(a,b)
tempform = Worksheets("Sheet").Range("O1").Formula

' Extract from the formula IFERROR statement in local language.
strlocalform1 = Mid(tempform, 2, InStr(1, tempform, "(") - 1)

' Extract from the formula separator , (comma) in local settings.
strlocalform2 = Mid(tempform, InStr(1, tempform, "a") + 1, 1)

' Add formula in local language to desired field.
pvt.CalculatedFields.Add Name:="NewField", Formula:="=" & strlocalform1 & "FORMULA" & strlocalform2 & ")"

Hope this helps!

Diego
  • 1