by Michael S. Kaplan, published on 2005/03/07 02:02 -05:00, original URI: http://blogs.msdn.com/b/michkap/archive/2005/03/07/386452.aspx
(Recycling some electrons for a fun bug that can still be reproduced in the latest versions and service packs of VB and VBA <= 6.x (despite all the pressure I was able to muster in my position of unimportance!). To see this article in Thai, go to http://blogs.msdn.com/michkap/archive/2005/03/07/386453.aspx)
A bug with the new oleaut32.dll calendar support and VB, VBA, and VBScript
(Originally posted 4/9/2000)
The new version OLEAUT32.DLL (2.40.4512.1) that ships with Windows 2000 has added support for the Thai calendar (previously it had only supported Gregorian and Hijri dates, which is all VB supports). There is an initial problem that although the DLL contains the support and MSDN documents the new capability, the platform SDK does not contain the latest header files, so you can't actually use the feature. Here are those values (which should be there for the next platform SDK in July 2000):#define VARIANT_CALENDAR_THAI 0x20 // SOUTHASIA calendar support
#define VARIANT_CALENDAR_GREGORIAN 0x40 // SOUTHASIA calendar support
Ok, you can use the feature! Now for the bug:
When you have this new oleaut32.dll on either a Thai Win95/Win98/NT4 machine or a US Windows 2000 machine with Thai regional settings, COM will recognize that the Thai calander is the one to use. Unfortunately, VBA and VBScript do not understand the concept of Thai dates, so they will both assume that dates like 9/4/2543 (the Thai equivalent of April 9th, 2000) is simply a very futuristic Gregorian date. Suddenly, any code you have to display dates is going to look like everything is 543 years in the future! And if your users do not type in dates that 543 years ahead, the incorrect date will then be used by your application.
The problem is slightly worse if you use Hijri dates, which are approximately 600 years prior to Gregorian dates. Everything will seem to be way off. I am not sure how VBA is actually calculating Hijri dates, but if they are asking COM to do it, they are not properly identifying the source date as being in Thai, since it seems to think that the date is 5/1/1964 rather than the proper Hijri date, 1/5/1421. There is even more potential for corruption here, since the date does not correspond to any date that a user would understand (as opposed to the Thai date, which presumably a user who has Thai regional settings would understand).
The real fix is obviously for VBA to have a mechanism for its date handling that uses whatever COM has available to it, rather than being hardcoded to accept only two date types when COM will now support three. To workaround this bug, you need to call the VariantChangeTypeEx function to convert a date to a Gregorian date string and a Gregorian date string back into a date. The declaration of that function and relevant constants are:Private Const VARIANT_NOUSEROVERRIDE = &H4
Private Const VARIANT_CALENDAR_HIJRI = &H8
Private Const VARIANT_CALENDAR_THAI = &H20
Private Const VARIANT_CALENDAR_GREGORIAN = &H40
Declare Function VariantChangeTypeEx _
Lib "oleaut32.dll" _
(ByRef pvargDest As Variant, _
ByRef pvarSrc As Variant, _
ByVal lcid As Long, _
ByVal wFlags As Integer, _
ByVal vt As VbVarType) As Long
and you can use the following procedure (if you have the new oleaut32.dll only!) to test this out:Sub TestDateFormats()
Dim vSrc As Variant
Dim vDst As Variant
vSrc = Date
Call VariantChangeTypeEx(vDst, vSrc, 1033, VARIANT_CALENDAR_GREGORIAN, vbString)
Debug.Print "Gregorian date: " & vDst
Call VariantChangeTypeEx(vDst, vSrc, 1025, VARIANT_CALENDAR_HIJRI, vbString)
Debug.Print "Hijri date: " & vDst
Call VariantChangeTypeEx(vDst, vSrc, 1054, VARIANT_CALENDAR_THAI, vbString)
Debug.Print "Thai date: " & vDst
The output will look like this:Gregorian date: 4/9/2000
Hijri date: 05/01/1421
Thai date: 9/4/2543
To convert from a string to a date, simply specify vbDate in that fifth parameter instead of vbString. You can use this same code under more pleasant circumstances to help interpret dates from different locales.
The bug is obviously pretty bad for existing applications running on Thai machines since their behavior will be changed. I hope Microsoft addresses this one quickly.
This post and its localized Thai cousin brought to you by "ภ" (U+0e20, a.k.a. THAI CHARACTER PHO SAMPHAO)
# AC on 7 Mar 2005 12:16 AM:
# Michael Kaplan on 7 Mar 2005 1:13 AM:
Yuhong Bao on 2 May 2010 9:28 PM:
"Recycling some electrons for a fun bug that can still be reproduced in the latest versions and service packs of VB and VBA <= 6.x"
What about VBA 7.0?
Michael S. Kaplan on 2 May 2010 9:36 PM:
You can try it, of course. No such version existed when I wrote this over half a decade ago!
2005/03/07 Successful experiment
go to newer or older post, or back to index or month or day