Driving Distance

Driving Distance MapQuest UDF

Here is a UDF for getting driving distance from MapQuest, based on cells in your worksheet.

Let's say you have a starting address in cells A1 (street), B1 (city), C1 (state), D1 (zip), and the destination address is E1 (street), F1 (city), G1 (state), H1 (zip). This function pulls the address from the cells in Excel, creates an instance of Internet Explorer, passes the addresses to MapQuest and returns the driving distance to the target cell. If there is an error with MapQuest, it shows a friendly error message.

Update 10/31/08: MapQuest reorged their page and broke the UDF. I've updated the URL string, and also added a call to the Refresh Method. This is required to make the driving distance appear programmatically.

'
' based on http://www.vbaexpress.com/kb/getarticle.php?kb_id=386
Public Function GetDistance(startAddr As String, startCity As String, _
  startState As String, startZip As String, endAddr As String, _
  endCity As String, endState As String, endZip As String) As String

Dim sURL As String
Dim appIE As Object ' InternetExplorer
Dim regex As Object ' RegExp
Dim Regmatch As Object ' MatchCollection
Dim BodyTxt As String
Dim GetFirstPos As Long

sURL = "http://www.mapquest.com/maps?1c=" & Replace(startCity, " ", "+")
sURL = sURL & "&1s=" & startState & "&1a=" & Replace(startAddr, " ", "+")
sURL = sURL & "&1z=" & startZip & "&2c=" & endCity & "&2s=" & endState
sURL = sURL & "&2a=" & Replace(endAddr, " ", "+") & "&2z=" & endZip

Set appIE = GetIE

appIE.navigate sURL
appIE.Visible = True

    Do
        DoEvents
    Loop Until appIE.readyState = READYSTATE_COMPLETE

appIE.Refresh

Set regex = CreateObject("VBScript.RegExp")
With regex
    .Pattern = "Total Estimated Distance"
    .MultiLine = False
End With

BodyTxt = appIE.document.Body.innerText
Set Regmatch = regex.Execute(BodyTxt)

If Regmatch.Count > 0 Then
    GetFirstPos = WorksheetFunction.Find("Total Estimated Distance", BodyTxt, 1)

    GetDistance = Mid$(BodyTxt, GetFirstPos, 30)

    Else
    GetDistance = "Address Error, fix and try again"
End If

appIE.Quit
Set appIE = Nothing
Set regex = Nothing
Set Regmatch = Nothing

End Function

This function should be used in a worksheet as follows:

=GetDistance(A1,B1,C1,D1,E1,F1,G1,H1)

The GetIE function may be found at Automate Internet Explorer.

Now note that it will return an error if you include suite or apartment numbers in the house number. Also if you include hash marks "#" it will fail. Also keep in mind you are opening Internet Explorer in the background and waiting for MapQuest to load, so there will be an approx 6 second delay between you entering the function and it actually returning a result. Also I recommend that after entering the function and getting the result, you either paste in the value or delete it and re-enter it if needed — the function is volatile and you will end up re-running the code inadvertently, which will slow down your computer to a crawl.