Language independent formulas

Question

I'd like to prepare an overview tab with some formulas, though not all colleagues use an en-us version of Excel - I currently use de-at languages pack. Therefore I started using .FormulaLocal but it doesn't quite work in my case

In this example I try to prepare a formula that sums up all "light" errors from various tabs. I have two versions

Version 1 (does work with de-at Excel)

Sub count_light_errors(tabName)
Dim element As Variant
Dim formula As String
Dim temp As Variant

For Each element In tabName
    temp = temp & "zählenwenn(" & element & "!E:E;""light"");"
Next element

formula = "=summe(" & Left(temp, Len(temp) - 1) & ")"
Worksheets("Overview").Range("C8").FormulaLocal = formula

End Sub

Version 2 (does not work with de-at Excel)

Sub count_light_errors(tabName)
Dim element As Variant
Dim formula As String
Dim temp As Variant

For Each element In tabName
    temp = temp & "countif(" & element & "!E:E,""light""),"
Next element

formula = "=sum(" & Left(temp, Len(temp) - 1) & ")"
Worksheets("Overview").Range("C8").FormulaLocal = formula

End Sub

The difference between those two is just using the German words for

• SUM() -> SUMME()
• COUNTIF() -> ZÄHLENWENN()

and ; instead of , as seperator

How do I prepare formulas correctly, so they are independent from the user language pack?

Thanks a million!
-Claus

Answer 1

VBA is very EN-US-centric. VBA's .Formula and .FormulaR1C1 expect the ROW function. To use regional language function 'flavors' like SUMME then the Range.FormulaLocal property or Range.FormulaR1C1Local property should be employed instead.

So instead of use .FormulaLocal just use .Formula with EN language, your code will be interpreted in every language.