One of the things I think is amazing about VBA is the fact that it is so incomplete in some ways. For instance, did you know that there is no native way to get the local timezone offset. Yesterday I was working on a VBA SOAP plugin for Excel and realized this horrible fact. Of course, my first instinct was to Google the answer. Unfortunately, not even Google could provide a satisfactory (meaning short) solution. Therefore, I came up with the following VBA function:
Public Function GetTimezoneOffset() As Integer
Dim ie As New InternetExplorer
ie.Visible = False
ie.Navigate "about:blank"
While ie.readyState <> READYSTATE_COMPLETE And Not ie.Busy
Wend
ie.Document.parentWindow.execScript "n = (new Date).getTimezoneOffset()"
GetTimezoneOffset = ie.Document.parentWindow.n
ie.Quit
End Function
Of course, there are downsides to this solution. The first (being minor) is the fact that you have to include a reference by going to Tools > References… and then selecting “Microsoft Internet Controls”. The second is the fact that every time this function is called, an instance of Internet Explorer is created and then quickly (speed depending on the computer) destroyed. Other than that, the function will work. Its purpose is to return the amount of minutes behind GMT (or UTC) time the computer is. This is the equivalent of doing the following in JavaScript:
var offset = (new Date).getTimezoneOffset();
2 Comments
Naveen · August 7, 2014 at 11:43 AM
you can use this function instead!
Public Function TimeZone_Offset()
Dim objWMIService
Dim colTimeZone
Dim objTimeZone
Set objWMIService = GetObject(“winmgmts:” & “{impersonationLevel=impersonate}!\\.\root\cimv2”)
Set colTimeZone = objWMIService.ExecQuery(“Select * from Win32_TimeZone”)
For Each objTimeZone In colTimeZone
TimeZone_Offset = objTimeZone.Bias
MsgBox TimeZone_Offset
Next
End Function
ettore · January 3, 2018 at 4:40 AM
error .
Code red on line
Set objWMIService = GetObject(“winmgmts:” & “{impersonationLevel=impersonate}!\\.\root\cimv2”)
Set colTimeZone = objWMIService.ExecQuery(“Select * from Win32_TimeZone”)