Time Zones In VBA
|All of the date and time functions in
Excel and VBA use local time, that is, the current time of day in your
location. They do not take into account time zones or Daylight
Savings Time. For most purposes, this is adequate. However, if
your application needs to accommodate multiple time zones, you'll need to
be able to determine the absolute time, or Greenwich Mean
Greenwich Mean Time (GMT), also called Universal Coordinated Time (UTC) or Zulu Time, is the time of day in Greenwich, England, which is at 0 degrees longitude. GMT is not adjusted for Daylight Savings Time (DST) and all time zones around the world are computed as offsets from GMT. For example, Central Standard Time in the United States is GMT -6, that is, 6 hours earlier than GMT.
VBA doesn't give you any built-in functions for determining either GMT or your offset from it, or what time zone your are located in. Therefore, you'll have to use two of the Windows API (or Application Programmatic Interface) calls, to get this information directly from Windows. This page describes the techniques for doing this.
The Windows API procedures, GetTimeZoneInformation and GetSystemTime are a bit tricky to use for novice programmers, so I've wrapped these procedures into a Class Module, so that your VBA code can simply read a few simple properties of this class without having to worry about the dirty details.
You can download a workbook which contains all the code we'll be using.
The class module declares the following API functions and Types.
Private Declare Function GetTimeZoneInformation Lib "kernel32" _
NOTE: If you use the WinAPI Viewer add-in for VBA to copy these declaration into your code, the Type declaration for TIME_ZONE_INFORMATION is incorrect. You must change the size of both StandardName and DaylightName from 32 to 31. Otherwise, the procedure calls will not work.
To get the current time zone information, we call GetTimeZoneInformation as follows.
TZType = GetTimeZoneInformation(TZInfo)
This will fill the TZInfo structure with the information we need, and return a value indicating whether we are currently in Daylight Savings Time. There are three elements of the TZInfo structure we're interested in.
Both StandardName and DaylightName are returned from Windows as an array of integers, not strings. Therefore, it is necessary to convert this array to a string. My class module provides a procedure to do this, shown below.
Private Function StringFromIntArray(IntArray() As Integer) As String
As I said, all of this can get a little tricky. However, if you use the CTime class provided in the workbook, you can avoid all this code yourself, and just call upon the properties of the class. For example, to return the current time zone (for your location, and taking into account daylight savings time), you would simple code the following.
Dim TInfo As CTime
All of the messy details are handled for you inside the class. When you create an instance of the class, it reads the current time. To update the time used by the class, call its Refresh method.
The GMT property will return the Greenwich Mean Time. For example,
Msgbox "GMT: " & Format(TInfo.GMT,"hh:mm:ss")
This will display the current GMT.
You can also determine whether your are currently in Daylight Savings Time.
If TInfo.DSTInEffect = True Then