How can I find user's time zone offset in exce

Question

I am using an excel macro to generate an RSS feed. The user's timezone offset needs to go in the field of the RSS feed. How can I do this programatically in the excel macro function?

Answer 1

Paste the following code into a module in Excel:

Private Declare Function GetTimeZoneInformationAny Lib "kernel32" Alias _
  "GetTimeZoneInformation" (buffer As Any) As Long

Function GetTimeZone() As Single
  Dim retval As Long
  Dim buffer(0 To 42) As Long

  Const TIME_ZONE_ID_INVALID = &HFFFFFFFF
  Const TIME_ZONE_ID_UNKNOWN = 0
  Const TIME_ZONE_ID_STANDARD = 1
  Const TIME_ZONE_ID_DAYLIGHT = 2

  retval = GetTimeZoneInformationAny(buffer(0))

  Select Case retval
    Case TIME_ZONE_ID_INVALID
      GetTimeZone = 0
    Case TIME_ZONE_ID_STANDARD, TIME_ZONE_ID_UNKNOWN
      GetTimeZone = (buffer(0) + buffer(21)) / -60
    Case TIME_ZONE_ID_DAYLIGHT
      GetTimeZone = (buffer(0) + buffer(42)) / -60
    Case Else
      GetTimeZone = 0
  End Select

End Function

(From http://binaryworld.net/Main/CodeDetail.aspx?CodeId=152)