excel - Does Range.Formula property translate to other locale

Question

When a user manually enters formula in a sheet in the English version of Excel and sends this document to another user who has Excel in another version, the formula is automatically translated to the language in which the document is opened.

'Formula entered in Sheet1!B1 in the English Excel version
=VLOOKUP(A1,Sheet2!$A$1:$C$150,3,FALSE)
'Formula as shown in a Dutch Excel version
=VERT.ZOEKEN(A1;Sheet2!$A$1:$C$150;3;ONWAAR)

When I programmatically add a formula, in my English version, it would look like this:

Sub AddFormula()
    ThisWorkbook.Worksheets("Sheet1").Cells(1, 2).Formula = "=VLOOKUP(A1,Sheet2!$A$1:$C$150,3,FALSE)"
End Sub

If I were to run this procedure on my English Version, then save the document and send it over to a user with another language, it would be fine as the formula is in the cell and thus translated to whatever language and locale the 2nd user uses.

My question is what happens when the 2nd user runs the procedure, in the Dutch (or other language for that matter) Excel with the formula in the code written in the EN-US format. Will it be translated or will it throw an error and in case of the latter, how would I solve that and make the formula insertion language proof?

question from:https://stackoverflow.com/questions/66048804/insert-a-formula-into-a-specific-cell-using-vba

Answer 1

In Excel vba the .Formula requires that the formula to be inserted be in EN-USA format. This is regardless of the local settings.

Excel will then translate it into the local vernacular when putting it in the sheet.

So no matter what the local settings it will work cross languages.

VBA also has the .FormulaLocal which allows the entry of the formula in the native language. But (as far as I know) it will not work cross different local settings.