Timezone in VBA?

[Ian]

Using Word 97.

I want to add the timezone to a timestamp. E.g. "11:30 EST". How can I
obtain the local timezone using VBA?

 

[Lars-Eric Gisslén]

Ian,

You can allways calculate the time zone by using a Win API call.

You can use the following example as a starting point. But you must care for
daylight saving time. The structure is populated with all information you
need.

--------------------------------------------
Private Const TIME_ZONE_ID_STANDARD = 1
Private Const TIME_ZONE_ID_UNKNOWN = 0
Private Const TIME_ZONE_ID_INVALID = &HFFFFFFFF
Private Const TIME_ZONE_ID_DAYLIGHT = 2

Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Type TIME_ZONE_INFORMATION
Bias As Long
StandardName(64) As Byte
StandardDate As SYSTEMTIME
StandardBias As Long
DaylightName(64) As Byte
DaylightDate As SYSTEMTIME
DaylightBias As Long
End Type

Declare Function GetTimeZoneInformation Lib "kernel32" _
(lpZoneInfo As TIME_ZONE_INFORMATION) As Long

Public Sub GetUTCDiff()
Dim pZoneInfo As TIME_ZONE_INFORMATION
Dim nRetVal As Long
Dim sDayLight As String

nRetVal = GetTimeZoneInformation(pZoneInfo)

If nRetVal = TIME_ZONE_ID_INVALID Then
MsgBox "Function GetTimeZoneInformation failed."
ElseIf nRetVal = TIME_ZONE_ID_UNKNOWN Then
MsgBox "Could not get the Bias value."
Else
If nRetVal = TIME_ZONE_ID_DAYLIGHT Then
sDayLight = "(Daylight saving time)"
Else
sDayLight = "(Normal time)"
End If
MsgBox "The difference between UTC time and local time is " & _
Str(pZoneInfo.Bias) & " minutes" & vbCr & sDayLight
End If

End Sub

 

[Ian]

Many thanks Lars-Eric. This looks like a very good starting point.

 

[Lars-Eric Gisslén]

Ian,

Sorry, but it seems I've got the structure TIME_ZONE_INFORMATION
missaligned.

Change the StandardName and DaylightName arrays to a size of 62 bytes
instead of 64. To convert from the Unicode string in those arrays you can
use a code like CStr(pZoneInfo.DaylightName)