Got weather?

umbrella

Hot on the heels of my update to the Latitude/Longitude functions page, I've written some more VBA code that uses web APIs to retrieve weather information for any zip code in the U.S.A. If you visit that page and then look at the code below, you may notice the similarity.

It's pretty flexible, if I do say so myself. You can look at weather data for up to the next five days (including the current date) for any U.S. zip code, and return the high or low temp in Celsius or Fahrenheit! I smell a weather app on the horizon.

First place the following at the top of a standard module:

Const XML_FILE_EXTENSION As String = ".xml"

Public Enum MaxOrMinTemp
  HighC
  LowC
  HighF
  LowF
End Enum

The constant isn't really required, feel free to remove it and hard code the .xml file extension. The enum section delimits what types of temperatures we can select. Respectively, they represent

Celsius daily high temp
Celsius daily low temp
Fahrenheit daily high temp
Fahrenheit daily low temp

The Main Function

Now on to the good stuff. Note that this is just one of many possible applications of the weather API. See below for code walkthrough.

Function ZipCodeWeather(ZipCode As String, daytoCheck As Date, _
    Optional temperature As MaxOrMinTemp = HighF) As Long
' http://www.webservicex.net/WCF/ServiceDetails.aspx?SID=44
' returns given weather for given U.S. zip code on given day

Dim xml As Object
Dim result As String
Dim nextFileNum As Long
Dim tempFile As String
Dim doc As Object
Dim objRoot As Object
Dim Children As Object
Dim Child As Object
Dim subChildren As Object
Dim subChild As Object
Dim subsubChildren As Object
Dim subsubChild As Object
Dim dayWasFound As Boolean
Dim whichTemp As String

  ' check if valid day selected
  ' (must be 5 days or less from today, per API)
  If ((daytoCheck - Now > 5) Or (daytoCheck - Now < 1)) Then
    ' MsgBox "Date to check must be 5 or less days from today."
    ' return -999 instead of zero, because weather might actually be zero!
    ZipCodeWeather = -999
    Exit Function
  End If

  ' check valid zip
  If Len(ZipCode) <> 5 Then
    ZipCodeWeather = -999
    Exit Function
  End If

  Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

  xml.Open "GET", _
"http://www.webservicex.net/WeatherForecast.asmx/GetWeatherByZipCode?ZipCode=" & ZipCode, False
  xml.Send

  result = xml.responsetext

 ' put "?result" into Immediate Window
 ' if you want to see the return result

  ' write to temp xml file
  nextFileNum = FreeFile

  tempFile = Environ("temp") & Application.PathSeparator & ZipCode & XML_FILE_EXTENSION

  Open tempFile For Output As #nextFileNum
  Print #nextFileNum, result
  Close #nextFileNum

  Set doc = CreateObject("MSXML2.DOMDocument")
  doc.validateOnParse = False
  doc.Load tempFile

  Set objRoot = doc.documentElement

  Set Children = objRoot.childnodes

  ' do we want high or low temp, celsius or fahrenheit?
  Select Case temperature
    Case 0 ' high celsius
      whichTemp = "MaxTemperatureC"
    Case 1 ' low celsius
      whichTemp = "MinTemperatureC"
    Case 2 ' high fahrenheit
      whichTemp = "MaxTemperatureF"
    Case 3 ' low fahrenheit
      whichTemp = "MinTemperatureF"
  End Select

  For Each Child In Children
    If Child.nodename = "Details" Then
      Set subChildren = Child.childnodes
      For Each subChild In subChildren
        Set subsubChildren = subChild.childnodes
        For Each subsubChild In subsubChildren
          If subsubChild.Text = Format(daytoCheck, "dddd, mmmm dd, yyyy") Then
            ' current iteration is the correct date
            dayWasFound = True
          End If

          If dayWasFound Then

            If subsubChild.nodename = whichTemp Then
              ZipCodeWeather = subsubChild.Text
              Kill tempFile
              Exit Function
            End If
          End If
        Next subsubChild
      Next subChild
    End If
  Next Child

End Function

Since the XML response from the web will only contain five days' worth of weather information, the first thing we do is check if the date is valid. We also check if the zip code is valid; unfortunately other than checking for five digits (or possibly that it's a number), there's little validation possible (I'd be glad to hear otherwise).

If either of these conditions fails, the function returns a value of -999. This is necessary because by default, it will return zero. But the temperature could be zero, so you wouldn't know the function had failed. The -999 result should be checked by a wrapper procedure to make sure the result is legit.

A temporary XML file is created, then loaded into a MSXML Document Object. The temperature is checked (Fahrenheit high temp is assumed) and the appropriate string is applied. The XML is parsed until the correct day is found. Then the appropriate temperature from that day is parsed and returned by the function, but not before the temporary XML file is deleted.

Sample usage

The following sample procedure defines a zip code (which happens to be mine) and then displays the high temperature prediction in Celsius for Wednesday.

Sub TestZipCodeWeather()

Dim ZipCode As String
Dim filePath As String

  ZipCode = "11103"

  MsgBox ZipCodeWeather(ZipCode, #3/17/2010#, HighC)

End Sub

Download sample workbook — Excel 2003
Download sample workbook — Excel 2007