Couldn't Stand The Weather

storm cloud

World Weather Online has an API for requesting local and marine weather.

I've covered weather APIs extensively on this blog, see

for more examples. So why not one more?

This API is dead simple to use (unlike others), all you need to do is register for an API key. I have an API key, but in the examples below it has been removed. Register for yours to get this code working.

Get Your Local Weather

I define an Enum and two public constants to make querying the API easier:

Public Enum numDays
  two = 2
  three = 3
  four = 4
  five = 5
End Enum

Public Const base_URL As String = ""
Public Const apiKey As String = "your api key here"

The API returns data between two and five days ahead, so the Enum is used so I don't have to do any validation on the number of days passed to the function.

The GetLocalWeather Function

This function accepts four parameters:

  • your API key
  • the location for which you want the weather report
  • the number of days forecast
  • whether you want to requery the API (as opposed to using the cached results)

The location must be one of these types:

  • City,State combination (ex: New York, NY)
  • IP Address
  • Zip/Post Code
  • Latitude,Longitude (ex: 33.156,-50.143)

The query caching is based on the location you choose and the number of days' results requested.

Function GetLocalWeather(apiKey As String, location As String, _
                         Optional NumberOfDays As numDays = five, _
                         Optional forceRequery As Boolean = False) As String()

Dim requestURL As String
Dim xml As Object  ' MSXML2.XMLHTTP
Dim xmlDoc As Object  ' MSXML2.DOMDocument
Dim xmlDocRoot As Object  ' MSXML2.IXMLDOMNode
Dim weatherNodes As Object  ' MSXML2.IXMLDOMNodeList
Dim weatherNode As Object  ' MSXML2.IXMLDOMNode
Dim result As String
Dim tempFile As String
Dim tempString() As String
Dim i As Long, j As Long

Const XML_FILE_EXTENSION As String = ".xml"

  ' build request URL
  requestURL = base_URL & "key=" & apiKey & "&q=" & location & _
               "&format=xml&num_of_days=" & NumberOfDays

  tempFile = environ("temp") & "\" & location & "_weather_" & _
             NumberOfDays & Format(Date, "mm.dd.yyyy") & XML_FILE_EXTENSION

  ' cache response
  If (Len(Dir(tempFile)) = 0 Or forceRequery) Then

    Set xml = GetMSXML

    With xml
      .Open "GET", requestURL, False
    End With

    result = xml.responseText

    ' save result as temp XML document
    tempFile = CreateFile(tempFile, result)

  End If

  ' load XML file into new XML document
  Set xmlDoc = CreateObject("MSXML2.DOMDocument")

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

  ' check that the XML doc loaded
  If LoadError(xmlDoc) Then
    Exit Function
  End If

  ' get root node
  Set xmlDocRoot = GetRootNode(xmlDoc)
  Set weatherNodes = GetChildNodes(xmlDocRoot)

  ' resize array
  ' 14 data points in each weather forecast node
  ReDim tempString(1 To (weatherNodes.Length - 2), 1 To 14)

  ' start at node 3
  For i = 3 To weatherNodes.Length
    Set weatherNode = GetNode(weatherNodes, i)

    For j = 1 To UBound(tempString, 2)
      tempString(i - 2, j) = GetNode(weatherNode, j).nodeTypedValue
    Next j
  Next i

  GetLocalWeather = tempString

End Function

Helper Functions

The function above requires these helper functions. Paste them into a standard module in the same project.

Sample UserForm

The best part is we have a sample application using this API!

Enter the location, choose the number of days out and select the date you want to check and click Fetch. The weather prediction will be displayed in the textbox at the bottom of the form, and the web browser control will show a graphical image of the forecast.

form screenshot

To force a requery of the API, click the Requery Weather API checkbox. Although I recommend you leave this unchecked and only check it once a day to update the forecast. The API will thank you.

This form demonstrates a few useful techniques, including

  • dependent comboboxes
  • using URLs found in XML results to display a web image in a Web Browser control

My favorite part of the form is the web browser control. One of the API results is a URL pointing to an image file representing the weather forecast for a given day. So all we need to do is use the Navigate Method to point the browser at the URL. Poof! The image appears.

The validation on the form is minimal, so you may see some errors from time to time. And you'll need to register for an API key for it to work at all.

Download sample workbook (Excel 2003)

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Related Articles:

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel