Internationalise Dates in VBA

VBA does Date Internationalisation! Here find VBA code to generate and interpret dates in foreign languages. In this post I give new code to interpret dates in foreign languages as well as signpost existing code to format dates in foreign languages written by Stack Overflow user GSerg.

A while back I blogged about how VBA can interpret foreign currencies by calling into the COM runtime like other Windows API calls. Dates are another internationalisation problem and the same trick works, we call into oleaut32.dll the COM runtime.

First, let's introduce GSerg's VBA code to write dates in foreign languages. We need this first to generate test data for my code which interprets foreign language dates.

I won't replicate the code to respect intellectual property rights but I give here some test code that calls in to GSerg's FormatForLocale function...

Public Function TestFormatForLocale() As String
    
    '*
    '* for list of locale ids (LCID) see this
    '* https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oe376/6c085406-a698-4e12-9d4d-c3b0ee3dbc4a
    '*
    Const EN_US As Long = 1033
    Const DE_DE As Long = 1031
    
    '*
    '* FormatForLocale written by GSerg
    '* https://stackoverflow.com/users/11683/gserg
    '*
    '* Find source code at https://stackoverflow.com/questions/8523017/excel-format-value-mask/8523219#8523219
    '*
    Debug.Print FormatForLocale(CDate("12/May/2020"), "dd/mmm/yyyy", , , EN_US, DE_DE)
    Debug.Print FormatForLocale(CDate("12/Oct/2020"), "dd/mmm/yyyy", , , EN_US, DE_DE)
    Debug.Print FormatForLocale(CDate("12/Mar/2020"), "dd/mmm/yyyy", , , EN_US, DE_DE)
    
End Function

The code aboves prints out three dates in German...

12.Mai.2020
12.Okt.2020
12.Mrz.2020

GSerg's code cleverly calls into VarFormatFromTokens and VarTokenizeFormatString to format a date in a foreign language. You can read GSerg's explanation at his StackOverflow answer. For the moment I am content he has generated some good test input for my foreign language date interpretation code.

Next is code to interpret foreign dates, it's far fewer lines because I don't have to build a string buffer instead I supply a string and get a date back. There is some test code at the bottom demonstrating the German dates are being interpreted correctly (I have thrown in a French date as well).

Option Explicit

'* https://docs.microsoft.com/en-us/previous-versions/windows/embedded/aa519031(v=msdn.10)
Private Declare Function VarDateFromStr Lib "oleaut32" (ByVal strIn As Long, ByVal lcid As Long, _
            ByVal dwFlags As Long, ByRef pdateOut As Date) As Long

Public Function VarDateFromStr2(ByVal sInDate As String, ByVal lLCID As Long) As Date
    Dim hres As Long
    Dim pdateOut As Date
    hres = VarDateFromStr(StrPtr(sInDate), lLCID, 0, pdateOut)
    
    If hres = 0 Then
        VarDateFromStr2 = pdateOut
    Else
        Debug.Print "warning error: " & hres
    End If
End Function

Sub TestVarDateFromStr2()
    '*
    '* for list of locale ids (LCID) see this
    '* https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oe376/6c085406-a698-4e12-9d4d-c3b0ee3dbc4a
    '*
    Const EN_US As Long = 1033
    Const DE_DE As Long = 1031
    Const FR_FR As Long = 1036

    Debug.Print VarDateFromStr2("12.Mai.2020", DE_DE) = CDate("12-May-2020")
    Debug.Print VarDateFromStr2("12.Okt.2020", DE_DE) = CDate("12-Oct-2020")
    Debug.Print VarDateFromStr2("12.Mrz.2020", DE_DE) = CDate("12-Mar-2020")
    Debug.Print VarDateFromStr2("12/mars/2020", FR_FR) = CDate("12-Mar-2020")
    
End Sub

I was inspired to write this post because of a StackOverflow question which uses the MonthName function which will be tied to the VBA installation language. With the couple of programs demonstrated it is possible to break away from VBA's installation language and truly go international.