Translate function names between English and the local language
2021-09-26 functions 0 167
Sometimes you need to know what a function name is in the local language.
E.g. when adding FormatConditions with VBA code the formulas in the FormatCondition must be added using formula names and list separators in the localized language.
The function below can be used to translate an English function name into the local function name or from the local function name to an English function name.
Function TranslateFunctionName(strFormula As String, Optional blnFormulaIsDefaultEnglish As Boolean = True, Optional lngReferenceStyle As XlReferenceStyle = xlA1) As String ' updated 2021-09-26 by OPE ' returns the translated name for the first function in strFormula, either in English or the local language ' the function name must be located immediately after the = char in the formula ' strFormula must be a valid formula written in default English format (blnFormulaIsDefaultEnglish = True) or the local language (blnFormulaIsDefaultEnglish = False) ' if blnFormulaIsDefaultEnglish = True then the result will be the local formula name ' if blnFormulaIsDefaultEnglish = False then the result will be the English formula name ' set lngReferenceStyle to alA1 or xlR1C1 depending on the reference style used in strFormula ' NOTE! this function translates around 100+ functions per second depending on the computer hardware, avoid using this function multiple times, e.g. in a loop with more than 100 items ' example: strFunction = TranslateFunctionName("=if(true,false,true)") ' translate from english to local ' example: strFunction = TranslateFunctionName("=mid(""abcdef"",3,2)") ' translate from english to local ' example: strFunction = TranslateFunctionName("=sum(A1:A10)") ' translate from english to local ' example: strFunction = TranslateFunctionName("=true") ' translate from english to local ' example: strFunction = TranslateFunctionName("=hvis(sann;usann;sann)", False) ' translate from local to english ' example: strFunction = TranslateFunctionName("=deltekst(""abcdef"";3;2)", False) ' translate from local to english ' example: strFunction = TranslateFunctionName("=summer(A1:A10)", False) ' translate from local to english ' example: strFunction = TranslateFunctionName("=sann", False) ' translate from local to english strFormula = Trim(strFormula) If Len(strFormula) = 0 Then Exit Function Const cstrName As String = "TempTranslateFunction" Dim strNameTemp As String, blnSaved As Boolean, objName As Name, strResult As String If Left(strFormula, 1) <> "=" Then strFormula = "=" & strFormula With ThisWorkbook blnSaved = .Saved On Error Resume Next .Names(cstrName).Delete If blnFormulaIsDefaultEnglish Then If lngReferenceStyle = xlA1 Then Set objName = .Names.Add(cstrName, strFormula, False) Else Set objName = .Names.Add(cstrName, , False, , , , , , , strFormula) End If strResult = objName.RefersToLocal Else ' formula in the local language If lngReferenceStyle = xlA1 Then Set objName = .Names.Add(cstrName, , False, , , , , strFormula) Else Set objName = .Names.Add(cstrName, , False, , , , , , , , strFormula) End If strResult = objName.RefersTo End If If Not objName Is Nothing Then objName.Delete On Error GoTo 0 .Saved = blnSaved End With If Len(strResult) = 0 Then Exit Function Dim i As Long i = InStr(1, strResult, "(", vbBinaryCompare) If i > 0 Then strResult = Left$(strResult, i - 1) If Left$(strResult, 1) = "=" Then strResult = Mid$(strResult, 2) TranslateFunctionName = strResult End Function