Can VBA Code detect time zone?

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Hi,
I have been thinking that there could be a way to find out what time zone is the user in....
I came across the post in cpearson's site....but couldn't make it work..
I want cell 1 to show show what time zone is the user in. EST or other time zone etc.
Any suggestion and ideas would be great.
Thanks in advance.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Peter...Yes, exactly.
Code was really large and didnt know what to do with it where to paste etc...lol!
Please help Peter if you have time..
 
Upvote 0
Did you bump ME?! :LOL:

Dump this lot into a general code module and run sub AAA():-
Code:
    Option Explicit
    
    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
    
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' NOTE: If you are using the Windows WinAPI Viewer Add-In to get
    ' function declarations, not that there is an error in the
    ' TIME_ZONE_INFORMATION structure. It defines StandardName and
    ' DaylightName As 32. This is fine if you have an Option Base
    ' directive to set the lower bound of arrays to 1. However, if
    ' your Option Base directive is set to 0 or you have no
    ' Option Base diretive, the code won't work. Instead,
    ' change the (32) to (0 To 31).
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    Private Type TIME_ZONE_INFORMATION
        Bias As Long
        StandardName(0 To 31) As Integer
        StandardDate As SYSTEMTIME
        StandardBias As Long
        DaylightName(0 To 31) As Integer
        DaylightDate As SYSTEMTIME
        DaylightBias As Long
    End Type
    
    
    ''''''''''''''''''''''''''''''''''''''''''''''
    ' These give symbolic names to the time zone
    ' values returned by GetTimeZoneInformation .
    ''''''''''''''''''''''''''''''''''''''''''''''
    
    Private Enum TIME_ZONE

        TIME_ZONE_ID_INVALID = 0        ' Cannot determine DST
        TIME_ZONE_STANDARD = 1          ' Standard Time, not Daylight
        TIME_ZONE_DAYLIGHT = 2          ' Daylight Time, not Standard

    End Enum
    
    Private Declare Function GetTimeZoneInformation Lib "kernel32" _
        (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
    
    Private Declare Sub GetSystemTime Lib "kernel32" _
        (lpSystemTime As SYSTEMTIME)
 
    Function IntArrayToString(V As Variant) As String

        Dim N As Long
        Dim S As String
        For N = LBound(V) To UBound(V)
            S = S & Chr(V(N))
        Next N
        IntArrayToString = S

    End Function

    Sub AAA()

        Dim TZI As TIME_ZONE_INFORMATION
        Dim DST As TIME_ZONE
        Dim StandardName As String
        
        DST = GetTimeZoneInformation(TZI)
        StandardName = IntArrayToString(TZI.StandardName)
        Debug.Print StandardName

    End Sub
 
Upvote 0
Hey Rud, what is this code supose to show???:biggrin:
I inserted this in standard module and run AAA thing and then I was waiting for something to happen then nothing happned....:biggrin:I think I need to b.u.m.p you again...
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top