Zip Codes

Distance Between Zip Codes

Geocoder offers us the ability to get distances between two zip codes.

Like the GetLatLongDist function, the following function will cache each query in the local temp folder. You cannot make more than one request from the web every 15 seconds. Specifying True as the last parameter will force a re-query of the web service. As before, I strongly discourage this because distances are unlikely to change, so forcing a re-query of the same coordinates is pointless.

Function ZipToZipDistance(startingZip As Long, endingZip As Long, _
                          Optional forceRequery As Boolean = False) As String
Dim URL As String
Dim xml As Object ' MSXML2.XMLHTTP
Dim result As String
Dim tempFolder As String
Dim tempFile As String

Const baseURL As String = "http://geocoder.us/service/distance?zip1=95472&zip2=94305"
Const TXT_FILE_EXTENSION As String = ".txt"

  ' build URL
  URL = Replace(baseURL, "95472", startingZip)
  URL = Replace(URL, "94305", endingZip)

  ' store result in temp folder file
  tempFolder = environ("temp") & "\"
  tempFile = tempFolder & "ziptozipdist" & startingZip & endingZip & TXT_FILE_EXTENSION

  ' requery if cache file is missing or forceRequery set to True
  If Len(Dir(tempFile)) = 0 Or forceRequery Then
    Set xml = GetMSXML

    With xml
      .Open "GET", URL, False
      .send
    End With

    result = xml.responseText

    CreateFile tempFile, ConvertAccent(result)

  End If

  result = GetText(tempFile)

  ZipToZipDistance = result

End Function

Sample Usage

Sub tst()
  Debug.Print ZipToZipDistance(11101, 11103)
End Sub

Helper Functions

Copy the GetText function from ExcelUser, as well as the MSXML helper functions.