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”)

Leave a Reply

Your email address will not be published. Required fields are marked *