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

Private Declare Sub GetSystemTime Lib "kernel32" _
    (lpSystemTime As SYSTEMTIME)

Private 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

Private Type TIME_ZONE_INFORMATION
    Bias As Long
    StandardName( 31 ) As Integer
    StandardDate As SYSTEMTIME
    StandardBias As Long
    DaylightName( 31 ) As Integer
    DaylightDate As SYSTEMTIME
    DaylightBias As Long
End Type

 'Also, you'll want to include the following variables.  Excel 97 and earlier versions do not support the Enum data type.
 
 'For Excel 97, see the code in the downloadable file.
 


Public Enum cstDSTType
    cstDSTUnknown =  0 
    cstDSTStandard =  1 
    cstDSTDaylight =  2 
End Enum

Private SysTime As SYSTEMTIME
Private TZInfo As TIME_ZONE_INFORMATION
Private TZType As cstDSTType

These are how we 'll access the time zone information from Windows.
 


 '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.
 


 'Bias
 
 'This is the number of minutes added to the local time to get GMT.  Therefore, if Bias is 360, this indicates
 
 'that we are 6 hours (360 minutes) behind GMT.  
 

 'StandardName
 
 'This is the name of the standard time zone for your location.  
 

 'DaylightName
 
 'This is the name of the daylight savings time zone for your location.
 

 '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

Dim Ndx As Long
Dim C As String
Do Until IntArray(Ndx) =  0 
    C = C & Chr(IntArray(Ndx))
    Ndx = Ndx +  1 
Loop
StringFromIntArray = C

End Function


 '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
Set TInfo = New CTime
Debug.Print TInfo.TimeZoneName