Get the current time zone, UTC offset and daylight saving state

This post is one of a series providing implementation examples of Windows API Functions, Types, Enums and Consts using VBA. The code in this post can be used as-is, however, if you regularly (or even just occasionally) work with Windows API declarations in VBA, you may want to see the posts Automatically add Windows API declaration(s) and Using 'F1' to view Windows API web pages which explain some of the functionality that can be added to the VBE by VBE_Extras.

VBA can tell you the current date and time, but not which time zone you're in, how far that is from UTC, or whether daylight saving is in effect right now. Windows knows all three, and a single call of the GetDynamicTimeZoneInformation Windows API function gets them for you.

A few things to know before the code:

• GetDynamicTimeZoneInformation fills a DYNAMIC_TIME_ZONE_INFORMATION Type and returns a code telling you whether standard or daylight time is in effect right now (TIME_ZONE_ID_DAYLIGHT)

• The offsets are all 'bias' values in minutes, and the sign runs the opposite way to how you might expect ... Windows defines UTC as local + bias, and so the offset from UTC is the negative of the bias

• The bias in effect now is Bias plus either StandardBias or DaylightBias, depending on the return code

• The time-zone name fields are fixed-length Unicode (WCHAR) buffers held as Bytes inside the structure, so you decode them with CopyMemory and StrPtr, not a plain string assignment

The code

Add a standard Module and paste in all of the following.

First the declarations ... the two Types (SYSTEMTIME nests inside DYNAMIC_TIME_ZONE_INFORMATION), the four return-code Consts, and the declarations for GetDynamicTimeZoneInformation and CopyMemory:

Next, the Sub you run ... it calls the helper and prints the results to the Immediate window:

The helper does the real work ... it calls the API functions, picks the daylight or standard name and bias according to the return code, then negates the bias to get the offset from UTC:

And two small helpers ... one decodes a fixed WCHAR buffer into a String, the other formats the offset as +HH:MM:

Two things to note ...

The bias sign. Bias values are minutes, and a positive bias means you're behind UTC. Here in the UK the standard Bias is 0, so the winter offset comes out as +00:00. In summer the return code is TIME_ZONE_ID_DAYLIGHT and DaylightBias is -60, giving an effective bias of -60 and so an offset of +01:00 (BST). Negating the bias is the trick ... without that, every offset comes out backwards.

The Unicode names. A WCHAR is a 'Wide CHARacter': a single UTF-16 character that always takes up two bytes (the 'W' here means the same as the 'W' appended to some Function names ... it means 'wide', as opposed to the one-byte ANSI characters the 'A' versions use). Obviously, VBA has no WCHAR type, so the StandardName, DaylightName and TimeZoneKeyName fields are declared as Byte arrays and we use two bytes per character. WideFieldToString then makes a String of half the byte length, copies the raw bytes straight into it with CopyMemory (via StrPtr, because a VBA String's buffer is already UTF-16), and trims at the first null.

Finally ...

One call, a structure to unpack, and two details (a sign and an encoding) to respect ... and you have the time zone, its UTC offset and the live daylight-saving state, in any VBA host.

Oneof the fiddliest parts of a job like this is getting the VBA versions of the declarations (the Function, two Types and four Consts) right. VBE_Extras can insert all of them into your code for you, correctly formed for both 32- and 64-bit ... see Automatically add Windows API declarations.