TimezoneDb API returns time zone information

TimezoneDb has an API which returns time zone information about a geographical point (latitude/longitude).

It requires an API key, which you can sign up for using the above link. I have an API key but have removed it for this example. In this article we'll review how to access the API using VBA. I've created a function which can be used on the worksheet (as a UDF) and also in VBA.

Given a latitude and longitude pair, the API returns

1. Status of the request (
   OK

   or
   FAIL

   )

2. Error Message (if any error occurred)
3. ISO country code
4. Time zone name
5. Time zone abbreviation
6. GMT offset (in seconds)
7. Daylight Savings indicator (1 if DST is in effect, 0 otherwise)
8. Timestamp

Note that you can also specify a time zone name instead of latitude/longitude. For this exercise, I chose to use latitude and longitude. Also, I did not bother to check if the status was
OK

or
FAIL

.

First, paste the following at the top of a standard module:

Public Const API_KEY As String = "your API key here"
Public Const BASE_URL As String = "http://api.timezonedb.com/"

The following function uses the MSMXL class module to simplify access to the API. You'll need to import that file into your project in order for this function to work.

Function GetTimeZoneInfo(lat As String, lon As String, _
                         Optional getValue As Variant, _
                         Optional byCols As Variant, _
                         Optional forceRequery As Boolean) As String()

  Dim tempFolder As String
  Dim tempFile As String
  Dim msxml As clsMSXML
  Dim xml As Object  ' MSXML2.XMLHTTP60
  Dim result As String
  Dim xmlDoc As Object  ' MSXML2.DOMDocument
  Dim xmlDocRoot As Object  ' MSXML2.IXMLDOMNode
  Dim tempString() As String
  Dim i As Long

  Const XML_FILE_EXTENSION As String = ".xml"

  ' short circuit
  If Len(lat) = 0 Or _
     Len(lon) = 0 Then
    Exit Function
  End If

  tempFolder = Environ("temp") & "\"
  tempFile = tempFolder & lat & lon & XML_FILE_EXTENSION

  Set msxml = New clsMSXML

  If Len(Dir(tempFile)) = 0 Or forceRequery Then
    Set xml = msxml.GetMSXML

    result = msxml.GetResponse(xml, _
                               HTTP_GET, BASE_URL & _
                                         "?format=xml&lat=" & _
                                         lat & "&lng=" & lon & _
                                         "&key=" & API_KEY, False)
    ' write API results to temp file
    msxml.CreateFile tempFile, result
  End If

  ' at this point we either have an existing xml file in temp folder,
  ' or we queried the API and created a new xml file in temp folder
  ' either way, load xml file into new XML doc
  Set xmlDoc = msxml.GetDomDoc

  With xmlDoc
    .async = False
    .validateonparse = False
    .Load tempFile
  End With

  ' check that the doc was loaded
  If msxml.LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = msxml.GetRootNode(xmlDoc)

  If IsMissing(getValue) Then  ' return all values
    If IsMissing(byCols) Then  ' formula can be filled _
                               down to return all values
      ReDim tempString(1 To xmlDocRoot.childnodes.length, 1 To 1)

      For i = 1 To xmlDocRoot.childnodes.length
        tempString(i, 1) = msxml.GetNode(xmlDocRoot, i).nodeTypedValue
      Next i

    Else  ' formula can be filled across to return all values
      ReDim tempString(1 To 1, 1 To xmlDocRoot.childnodes.length)

      For i = 1 To xmlDocRoot.childnodes.length
        tempString(1, i) = msxml.GetNode(xmlDocRoot, i).nodeTypedValue
      Next i
    End If
  Else  ' we want specific values only
    On Error Resume Next
    If IsError(UBound(getValue)) Then ' single value
    On Error GoTo 0
    ReDim tempString(1 To 1, 1 To 1)

    tempString(1, 1) = msxml.GetNode(xmlDocRoot, _
                                  CLng(getValue)).nodeTypedValue
    
    Else ' multiple values
    On Error GoTo 0
      If IsMissing(byCols) Then ' fill down
        ReDim tempString(1 To UBound(getValue), 1 To 1)
          For i = 1 To UBound(getValue)
            tempString(i, 1) = _
              msxml.GetNode(xmlDocRoot, CLng(getValue(i))).nodeTypedValue
          Next i
      Else ' fill across
        ReDim tempString(1 To 1, 1 To UBound(getValue))
          For i = 1 To UBound(getValue)
            tempString(1, i) = _
              msxml.GetNode(xmlDocRoot, CLng(getValue(i))).nodeTypedValue
          Next i
      End If
    End If
  End If

  GetTimeZoneInfo = tempString

End Function

To limit the number of queries, the response is cached to a file in the local temp folder. The fifth parameter must be true in order to force the function to query the API again. By default it is false. This is recommended to avoid getting blacklisted. It's also faster, and takes advantage of the fact that time zone information doesn't change very often. If the latitude and longitude hasn't changed, there's simply no need to query the API every single time. I recommend you keep this behavior and never specify a value for the fifth parameter to let the function handle caching.

Now is when things get complicated. To make a more flexible UDF, I added a few options:

• Return a single value, a specific set of values or all values
• Transpose the array

In order to instruct the function to return only a single value from the API response, the third parameter is declared as Variant. If any value is specified, we create a one-element array, and assign the corresponding node value to that array. If we specify a specific subset of values, the array is declared as n-element and the corresponding values are placed into the array.

If the third parameter is left out (IsMissing(getValue) = True), then we return the entire API response as an array. However, we can have two different kinds of arrays:

• One column with multiple rows (1 Row * n Columns)
• One row with multiple columns (n Rows * 1 Column)

The one we choose (using the fourth parameter) will change the way we use the UDF, as we'll see shortly.

Return Results to VBA

If we want to use this function in our VBA programs, we need to do something like this. Let's say I want to return all values as a column-based array:

Sub TestGet1()

  Dim result() As String
  Dim i As Long, j As Long
  result = GetTimeZoneInfo("53.7833", "-1.75")

  For i = LBound(result) To UBound(result)
    For j = LBound(result, 2) To UBound(result, 2)
      Debug.Print result(i, j)
    Next j
  Next i

End Sub

If I wanted the same information as a row-based array, simply specify any value for the fourth parameter.

Sub TestGet2()

  Dim result() As String
  Dim i As Long, j As Long
  result = GetTimeZoneInfo("53.7833", "-1.75", , 1)

  For i = LBound(result) To UBound(result)
    For j = LBound(result, 2) To UBound(result, 2)
      Debug.Print result(i, j)
    Next j
  Next i

End Sub

Step through both functions and you can see how the loop changes depending on whether the values are column-based or row-based.

If you want to return only a specific value, the code would look like this:

Sub TestGet3()

  Dim result() As String
  Dim i As Long, j As Long
  result = GetTimeZoneInfo("53.7833", "-1.75", 4)

  For i = LBound(result) To UBound(result)
    For j = LBound(result, 2) To UBound(result, 2)
      Debug.Print result(i, j)
    Next j
  Next i

End Sub

However, if you only wanted the third and fourth values from the API response, you would need to do this:

Sub TestGet4()

  Dim result() As String
  Dim i As Long, j As Long
  Dim values(1 To 2) As Variant
  
  values(1) = 3
  values(2) = 4
  
  result = GetTimeZoneInfo("53.7833", "-1.75", values)

  For i = LBound(result) To UBound(result)
    For j = LBound(result, 2) To UBound(result, 2)
      Debug.Print result(i, j)
    Next j
  Next i

End Sub

This will return the ISO country code and time zone name for the latitude and longitude pair. No validation is performed inside the function to ensure that a valid value is selected before trying to retrieve that value.

Return Results to Worksheet as UDF

On a sample worksheet, I placed the latitude and longitude I've been working with.

There are five possible ways I can return values from the API to the worksheet:

1. All values, listed in a single column
2. All values, listed in a single row
3. A specific single value only
4. A specific set of values, listed in a single column
5. A specific set of values, listed in a single row

All values, listed in a single column

Since the API returns eight values, I highlight a block of eight cells and enter this formula as an array formula (press Ctrl+Shift+Enter):

{=GetTimeZoneInfo($C$2,$D$2)}

All values, listed in a single row

Now I want all the values, but listed in a single row. I select a group of eight cells in a single row and use the following formula, also entered as an array:

{=GetTimeZoneInfo($C$2,$D$2,,1)}

I used
1

as the fourth parameter, to tell the function that I want the array transposed, but I could have used anything. i.e.

{=GetTimeZoneInfo($C$2,$D$2,,"bollocks")}

A specific single value only

Returning only a single value requires the third parameter of the function. This does not need to be entered as an array formula:

=GetTimeZoneInfo($C$2,$D$2,4)

In this formula, I only want the fourth value (time zone name).

A specific set of values, listed in a single column

Suppose I want to return just the third and fourth values in the API response (ISO country code and time zone name). I don't want all the values, but I don't want only one value. I select two cells in the same column and enter this formula:

{=GetTimeZoneInfo($C$2,$D$2,{3,4})}

A specific set of values, listed in a single row

Now I want specific values, but across columns instead of rows. All we need to do is add the fourth parameter to tell the function to return a row-based array. As with the other array formulas, this must be committed using Ctrl+Shift+Enter. I select two cells in the same row and enter this formula:

{=GetTimeZoneInfo($C$2,$D$2,{3,4},1)}

I realize some of this is an exercise, but hopefully you can apply this technique to your UDFs to make them more flexible.

Download sample workbook