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.