VBA Snippet – GetTimezoneOffset()

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();