Identifying The Time Zone in VBA, With Daylight Saving

I've blogged about this elsewhere, but here's a short piece of code to extract the named time zone in VBA, and tell you your Daylight Saving status.

This particular function just returns a descriptive string; but the UTC offset in hours or minutes is available in the code, too.

Note the use of WMI Script: Windows Management Interface is quite chewy, and bloggers like 'Hey Scripting Guy!' who can explain it well are few and far between. I don't generally use it unless I have to because it is effectively undocumented, and therefore best left to a community of experts who are immersed in the 'folklore' of code that you can only learn from mistakes and shared knowledge. That, and it's slow.

Public Function TimeZone() As String
Application.Volatile False
' returns a full descriptive string for the workstation's time zone


Dim objWMIService As Object
Dim objWin32_TimeZone As Object
Dim objWin32_ComputerSystem As Object


Dim strDescription As String
Dim strStandardName As String
Dim strDaylightName As String
Dim lngDaylightMins As Long
Dim lngUToffsetMins As Long

Dim boolDaylightInEffect As Boolean

Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")

For Each objWin32_TimeZone In objWMIService.ExecQuery("Select * from Win32_TimeZone")

    strDescription = objWin32_TimeZone.Description
    strStandardName = objWin32_TimeZone.StandardName
    strDaylightName = objWin32_TimeZone.DaylightName
    TimeZone = ""

    
    lngDaylightMins = objWin32_TimeZone.DaylightBias

    
    For Each objWin32_ComputerSystem In objWMIService.ExecQuery("Select * From Win32_ComputerSystem")
    
        boolDaylightInEffect = objWin32_ComputerSystem.DaylightInEffect
        lngUToffsetMins = objWin32_ComputerSystem.CurrentTimeZone
        
    Next objWin32_ComputerSystem
    
    If boolDaylightInEffect = False Then
        TimeZone = strDescription & " (" & strStandardName & ")"
    ElseIf lngDaylightMins = -60 Then
        TimeZone = strDescription & " with daylight saving: '" & strDaylightName & "' as " & strStandardName & " + 1 hour"
    ElseIf lngDaylightMins < 0 Then
        TimeZone = strDescription & " with daylight saving: '" & strDaylightName & "' as " & strStandardName & " + " & -1 * lngDaylightMins / 60 & " hours"
    ElseIf lngDaylightMins > 0 Then
        TimeZone = strDescription & " with daylight saving: '" & strDaylightName & "' as " & strStandardName & " - " & lngDaylightMins / 60 & " hours"
    ElseIf lngDaylightMins = 60 Then
        TimeZone = strDescription & " with daylight saving: '" & strDaylightName & "' as " & strStandardName & " - 1 hour"
    End If
    

    If boolDaylightInEffect = False Then
        ' no further action: name is UTC
    ElseIf lngUToffsetMins = 60 Then
        TimeZone = "UTC + 1 hour" & ": " & TimeZone & ""
    ElseIf lngUToffsetMins = -60 Then
        TimeZone = "UTC - 1 hour" & ": " & TimeZone & ""
    ElseIf lngUToffsetMins > 0 Then
        TimeZone = "UTC + " & lngUToffsetMins / 60 & "  hours" & ":" & TimeZone & ""
    ElseIf lngUToffsetMins < 0 Then
        TimeZone = "UTC - " & -1 * lngUToffsetMins / 60 & "  hours" & ": " & TimeZone & ""
    End If
    
    
Next


End Function