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.

Excel - VBA - Parsing International Currency Amounts in VBA

VBA Currency Parsing is limited to one's own currency

So parsing currencies in ordinary VBA can work so long as one sticks to one's own currency, so the following, CCur can handle commas and minus signs and on my machine Sterling Pound symbol but trips up on dollars.

Sub Test()

    Debug.Assert VBAParseCCur("1000") = 1000
    Debug.Assert VBAParseCCur("-1000") = -1000
    Debug.Assert VBAParseCCur("-10,000") = -10000
    
    Debug.Assert VBAParseCCur("£-10,000") = -10000 '* works on my (UK) machine
    Debug.Assert VBAParseCCur("$-10,000") = -10000  '* fails on my (UK) machine

End Sub

Function VBAParseCCur(v)
    On Error Resume Next
    VBAParseCCur = CCur(v)
End Function

COM System Parsers

So typically C++ programmers have greater access to the system functions than VBA programmers. A C++ programmer can call VarParseNumFromStr to parse a number out of a string.

HRESULT VarParseNumFromStr(
  LPCOLESTR strIn,
  LCID      lcid,
  ULONG     dwFlags,
  NUMPARSE  *pnumprs,
  BYTE      *rgbDig
);

One can see that one can specify the locale id as second parameter. If you know what you want, in this case I want a currency from a string then you can chose a more specific parsing function, VarCyFromStr

HRESULT VarCyFromStr(
  LPCOLESTR strIn,
  LCID      lcid,
  ULONG     dwFlags,
  CY        *pcyOut
);

With the help of VBFormus.com expert Olaf Schmidt we can call VarCyFromStr from VBA. And now we have the ability to parse multi-currency strings, see this code.

Option Explicit


'* http://www.vbforums.com/showthread.php?762443-VB6-Tabulator-Crosstab-Class
'* https://docs.microsoft.com/en-gb/previous-versions/windows/desktop/api/oleauto/nf-oleauto-varcyfromstr
Private Declare Function VarCyFromStr& Lib "oleaut32" (ByVal sDate&, ByVal LCID&, ByVal Flags&, C As Currency)

Private Enum lcidLocaleId
    lcidEN_US = 1033    '* US
    lcidEN_EN = 2057    '* UK
    lcidFR_FR = 1036    '* Eurozone
    lcidRU = 1049       '* Russia
    lcidJA = 1041       '* Japan
End Enum

Private Enum chrwCurrencies
    chrwEuro = 8364     '* for euros
    chrwRouble = 8381   '* for roubles
    chrwYen = 165       '* for yen
End Enum

Private Function CCurLA(ByVal sAmount As String, ByVal LCID As lcidLocaleId) As Currency
    Dim HRes As Long
    
    HRes = VarCyFromStr(StrPtr(sAmount), LCID, 0, CCurLA)
    If HRes Then Err.Raise HRes
End Function

Sub TestVarCyFromStr_Yen()

    Dim sYen As String
    sYen = ChrW(chrwYen) & "-1000"
    Dim curYen As Currency
    
    curYen = CCurLA(sYen, lcidJA)
    Debug.Assert curYen = -1000
End Sub

Sub TestVarCyFromStr_Roubles()

    Dim sRoubles As String
    sRoubles = ChrW(chrwRouble) & "-1000"
    Dim curRoubles As Currency
    
    curRoubles = CCurLA(sRoubles, lcidRU)
    Debug.Assert curRoubles = -1000
End Sub

Sub TestVarCyFromStr_Euros()

    Dim sEuros As String
    sEuros = ChrW(chrwEuro) & "-1000"
    Dim curEuros As Currency
    
    curEuros = CCurLA(sEuros, lcidFR_FR)
    Debug.Assert curEuros = -1000
End Sub

Sub TestVarCyFromStr_Dollars()

    Dim sDollars As String
    sDollars = "$-10,00"
    Dim curDollars As Currency
    
    curDollars = CCurLA(sDollars, lcidEN_US)
    Debug.Assert curDollars = -1000
End Sub

Sub TestVarCyFromStr_Pounds()

    Dim sPounds As String
    sPounds = "£-10,00"
    Dim curPounds As Currency
    
    curPounds = CCurLA(sPounds, lcidEN_EN)
    Debug.Assert curPounds = -1000
End Sub

C++ Internationalisation - Twelve steps to Unicode-enabling

Some great advice about ensuring Unicode...

From Developing International Applications pages 109-111, Microsoft Press:

1. Modify your code to use generic data types.
   Determine which variables declared as char or char* are text, and not pointers to buffers or binary byte arrays. Change these types to TCHAR and TCHAR*, as defined in the Win32 file windows.h, or to _TCHAR as defined in the VC++ file tchar.h. Replace LPSTR and LPCH with LPTSTR and LPTCH. Make sure to check all local variables and return types. Using generic data types is a good transition strategy because you can compile both ANSI and Unicode versions of your program without sacrificing the readability of the code. Don't use generic data types, however, for data that will always be Unicode or always ANSI. For example, one of the string parameters of MutliByteToWideChar and WideCharToMultiByte should always be in ANSI and the other should always be in Unicode.
2. Modify your code to use generic function prototypes.
   For example, use the C run-time call _tclen instead of strlen, and use the Win32 API GetLocaleInfo instead of GetLocaleInfoA. If you are also porting from 16 bits to 32 bits, most Win32 generic function prototypes conveniently have the same name as the corresponding Windows 3.1 API calls (TextOut is one good example). Besides, the Win32 API is documented using generic types. If you plan to use Visual C++ 2.x or higher, become familiar with the available wide-character functions so that you'll know what kind of function calls you need to change. Always use generic data types when using generic function prototypes.
3. Surround any character or string literal with the TEXT macro. The TEXT macro conditionally places an L in front of a character literal or a string literal. The C run-time equivalents are _T and _TEXT. Be careful with escape sequence specifying a 16-bit Unicode double-quote character, not as the beginning of a Unicode string. Visual C++ 2 treats anything within L" " quotes as a multibyte string and translates it to Unicode byte by byte, based on the current locale, using mbtowc. One possible way to create a string with Unicode hex values is to create a regular string and then coerce it to a Unicode string (while paying attention to byte order).
      char foo[4] = 0x40,0x40,0x40,0x41;
      wchar_t *wfoo = (wchar_t *)foo;
4. Create generic versions of your data structures.
   Type definitions for string or character fields in structure should resolve correctly based on the UNICODE compile-time flag. If you write your own string-handling and character-handling functions, or functions that take strings as parameters, create Unicode versions of them and define generic prototypes for them.

5. Change your make process.
   When you want to build a Unicode version of your application, both the Win32 compile-time flag -DUNICODE and the C run-time compile-time flag -D_UNICODE must be defined.

6. Adjust pointer arithmetic.
   Subtracting char* values yields an answer in terms of bytes; subtracting wchar_t values yields an answer in terms of 16-bit chunks. When determining the number of bytes (for example, when allocating memory), multiply by sizeof(TCHAR). When determining the number of characters from the number of bytes, divide by sizeof(TCHAR). You can also create macros for these two operations, if you prefer. C makes sure that the + + and - - operators increment and decrement by the size of the data type.

7. Check for any code that assumes a character is always 1 byte long.
   Code that assumes a character's value is always less than 256 (for example, code that uses a character value as an index into a table of size 256) must be changed. Remember that the ASCII subset of Unicode is fully compatible with 7-bit ASCII, but be smart about where you assume that character will be limited to ASCII. Make sure your definition of NULL is 16 bits long.

8. Add Unicode-specific code if necessary.
   In particular, add code to map data "at the boundary" to and from Unicode using the Win32 functions WideCharToMultiByte and MutliByteToWideChar, or using the C run-time functions mbtowc, mbstowcs, wctomb, and wcstombs. Boundary refers to systems such as Windows 95, to old files, or to output calls, all of which might expect or provide non-Unicode encoded characters.

9. Add code to support special Unicode characters.
   These include Unicode character in the compatibility zone, character in the private-use zone, combining characters, and character with directionality. Other special characters include the private-use-zone non-character U+FFFF, which can be used as a sentinel, and the byte order marks U+FEFF and U+FFFE, which can serve as flags that indicate a file is stored as Unicode. The byte order marks are used to indicate whether a text stream is little-Endian or big-Endian - that is, whether the high-order byte is stored first or last. In plaintext, the line separator U+2028 marks an unconditional end of line. Inserting a paragraph separator, U+2029, between paragraphs makes it easier to lay out text at different line widths.

10. Determine how using Unicode will affect file I/O.
    If your application will exist in both Unicode and non-Unicode variations, you'll need to consider whether you want them to share a file format. Standardizing on an 8-bit character set data file will take away some of the benefits of having a Unicode application. Having different file formats and adding a conversion layer so each version can read the other's files is another alternative. Even if you choose a Unicode file format, you might have to support reading in old non-Unicode files or saving files in non-Unicode format for backward compatibility. Also, make sure to use the correct printf-style format specifiers for Visual C++, shown here:

       Specifier printf Expects wprintf expects
       %s SBCS or MBCS Unicode
       %S Unicode SBCS or MBCS
       %hs SBCS or MBCS SBCS or MBCS
       %ls Unicode Unicode

11. Double-check the way in which you retrieve command line arguments.
    Use the function GetCommandLine rather than the lpCmdLine parameter (an ANSI string) that is passed to WinMain. WinMain cannot accept Unicode input because it is called before a window class is registered.

12. Debug your port by enabling your compiler's type-checking.
    Do this (using W3 on Microsoft compilers) with and without the UNICODE flag defined. Some warnings that you might be able to ignore in the ANSI world will cause problems with Unicode. If your original code compiles cleanly with type-checking turned on, it will be easier to port. The warnings will help you make sure that you are not passing the wrong data type to code that expects wide-character data types. Use the Win32 NLSAPI or equivalent C run-time calls to get character typing and sorting information. Don't try to write your own logic - your application will end up carrying very large tables!

From Microsoft Typography|Developer Information|Character sets