how to best create language settings independent excel formula in VBA?

Question

I have a macro which creates an excel formula range("A2").formula="=TEXT(A1,""YYYYMMDD"")" this works well with my US-EN version but gives problem when I try to run it on FR-FR version I need to adapt the formula to range("A2").formula="=TEXT(A1,""AAAAMMJJ"")" to have it work correctly on a -FR-FR version but this no longer works on my US-En version.

Any idea about how to manage this?

I have the same kind of issue with the CELL and INFO functions which use a string parameter that is language-dependent but not managed correctly when the formula is created programmatically thru the formula property of the range function.

Is there a property that I can test on and adapt the formula accordingly or better is there a way to have the string parameter automatically translated?

Answer 1

You can access the local regional settings via the Application.International method. This can then be used to translate your US_EN code to Local settings code.

Something like this

Function MakeDateCodeLocal(ByVal Code As String)
    Dim Y As String
    Dim M As String
    Dim D As String
    
    Y = Application.International(xlYearCode)
    M = Application.International(xlMonthCode)
    D = Application.International(xlDayCode)
    ' Add more codes if you need them
    
    Code = Replace$(Code, "Y", Y, , , vbTextCompare)
    Code = Replace$(Code, "M", M, , , vbTextCompare)
    Code = Replace$(Code, "D", D, , , vbTextCompare)
    MakeDateCodeLocal = Code
End Function

Then use it like this

range("FormulaRange").formula="=TEXT(A1, " & MakeDateCodeLocal("YYYYMMDD") & ")"

Answer 2

Can't test it properly because I have just one Excel version with spanish language.

As far as I know, you can't have Excel autotranslate the string parameter.

But the Application object holds the Language Settings.

MsgBox "Install Language - " & _
 Application.LanguageSettings.LanguageID(msoLanguageIDInstall) & vbNewLine & _
 "User Interface Language - " & _
 Application.LanguageSettings.LanguageID(msoLanguageIDUI) & vbNewLine & _
 "Help Language - " & _
 Application.LanguageSettings.LanguageID(msoLanguageIDHelp)

So in your VBA code, check the msoLanguageIDUI and depending on the value, use one string parameter or another.

All msoLanguageIDUI are listed here: MsoLanguageID enumeration (Office)

So you could combine this with your needs to choose right formula depending on FR or US-EN:

Sub TEST()
Dim LanguageID As Integer
LanguageID = Application.LanguageSettings.LanguageID(msoLanguageIDUI)

Range("A1").Formula = CHOOSE_FORMULA(LanguageID)

End Sub


Function CHOOSE_FORMULA(ByVal vLanguage As Integer) As String

Select Case vLanguage
    Case 1033 'The English US language
        CHOOSE_FORMULA = "=TEXT(A1,""YYYYMMDD"")"
    Case 1036 'The French language
        CHOOSE_FORMULA = "=TEXT(A1,""AAAAMMJJ"")"
End Select
End Function