Computing road distances using the Google Maps DistanceMatrix API

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Published:
Updated:
Edited by: Andrew Leniart
Many applications need to compute the road distance between two addresses.  Numerous websites perform this function, but to build this feature into your application, you must use an API to call these features via the internet.  This article discusses how I did so by using Google Maps API and VBA.

I have a potential client which reimburses employ travel based on the distance between their office and homes they visit as part of their job.  They have found that many of the distances included on expense accounts do not reflect the shortest distance between start and end points, and they want a way to use the distances calculated by Google Maps to standardize these mileage values.


There are many websites which provide direction, distance, and time estimates to get from point A to point B while driving.  Google Maps is just one these services, many of which provide APIs that allow developers and organizations to tap into these features in their own applications.  


Google recently modified its policy for using Google Maps and is now charging on a "pay-as-you-go" basis.  On their web site, they indicate that they provide $200 in free usage credits per month, and that "for most of our users, the $200 monthly credit is enough to support their needs."  With this knowledge in hand, I gave them my credit card info, created a new account, and got a license key (which is used in the XML request).


But when I started looking for code example, I found very few VBA examples.  I did find one example of how to use the directions feature so with that code in hand, I created a simple form in a new project file to test this new feature.



I then added a reference in the project to the MS XML, v6.0 library and headed off to the races.


The code behind the 'Distance' button simply calls the function (shown below) which, although it does not include any error handlers,  provides all of the details you need to get your application running.


Public Function GoogleDistance(FromAddress As String, ToAddress As String) As String

    Dim strKey As String
    Dim sXMLURL As String
    Dim objXMLHTTP As MSXML2.ServerXMLHTTP
   
    On Error GoTo ProcError
   
    strKey = "your license key goes here"
    sXMLURL = "https://maps.googleapis.com/maps/api/distancematrix/xml?" _
            & "units=imperial&" _
            & "origins=" & Replace(FromAddress, " ", "") & "&" _
            & "destinations=" & Replace(ToAddress, " ", "") & "&" _
            & "key=" & strKey

'Debug.Print sXMLURL

    Set objXMLHTTP = New MSXML2.ServerXMLHTTP
   
    With objXMLHTTP
        .Open "Get", sXMLURL, False
        .setRequestHeader "content-Type", "application/x-www-form-URLEncoded"
        .send
    End With
   
'Debug.Print objXMLHTTP.responseText

    Dim domResponse As DOMDocument60
    Set domResponse = New DOMDocument60
    domResponse.loadXML objXMLHTTP.responseText
   
    Dim ixnStatus As Variant
    Set ixnStatus = domResponse.selectSingleNode("//status")
   
    If ixnStatus.Text = "OK" Then
        Dim ixnDistance, ixnDuration
        Set ixnDistance = domResponse.selectSingleNode("/DistanceMatrixResponse/row/element/distance/text")
        Set ixnDuration = domResponse.selectSingleNode("/DistanceMatrixResponse/row/element/duration/text")
    End If
   
    Forms("form1").txt_Distance = ixnDistance.Text
    Forms("form1").txt_duration = ixnDuration.Text
   
ProcExit:
    Exit Function
   
ProcError:
    Debug.Print Err.Number, Err.Description
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume ProcExit
    Resume

End Function



I've included a couple of debug.print statements in the code to allow you to see the actual values which are passed to the request and received back as the response.  If I were going to implement this within an application, I would obviously add some error handling code which would provide meaningful error messages back to the user.


Take a look at the attached .accde file and see how simple it is to include this feature in your application.

GoogleMapDistance.accde



1
3,833 Views
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.