Wednesday, 28 August 2013



Custom Trip Distance Function (VBA & Google Directions API)


Introduction



The previous week I published a custom VBA function that uses the Google Elevation API to calculate the elevation of a location based on its latitude and longitude. After finishing that code I continued to read about the capabilities of the available APIs in Google Developer’s page. So, after reading about the Google Directions API I decided to write a custom VBA function using the particular API.

The custom Trip Distance function returns the estimated trip distance (in km) between the selected origin and destination using the Google Directions API. The function has two main parameters (origin and destination), which can be of the following type:
  • Street address
  • City name
  • Latitude and longitude
  • Country
  • Place of interest
  • Postal code
Trip Distance has also another optional parameter (Driving Mode), which can take the following values:
  • Driving
  • Walking*
  • Bicycling*
Of course, the most usual case here is “Driving”, which is also the default one. Bear in mind that in many cases the “Bicycling” and “Walking” might not be available, so the function will return an error. At Google’s page there is another driving mode option, which is the "transit" mode (route with public means of transport).  However, here it was omitted because it would require additional parameters (departure time or arrival time) that would make the function much more complex. Another option in the third argument of the function is to leave it empty/blank. In that case the default “Driving” mode is used.



VBA code



Below you will find the VBA code of the custom Trip Distance function. To tell you the truth, the code is more XML than VBA.

Option Explicit

Function TripDistance(Origin As String, Destination As String, Optional Travel_Mode As String) As Variant
    
    '-----------------------------------------------------------------------------------------
    'The function returns the estimated trip distance (in km) between the selected origin and
    'destination using the Google Directions API.
    'NOTE: As Google points out, the use of the Google Directions API is subject to a limit
    'of 2500 requests per day, so be careful not to exceed this limit.
    
    'In order to use this function you must enable the XML, v3.0 library from VBA editor:
    'Go to Tools -> References -> check the Microsoft XML, v3.0.
    
    'Written by:    Christos Samaras
    'Date:          28/08/2013
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '-----------------------------------------------------------------------------------------
    
    'Declaring the necessary variables. Using 30 at the first two variables because it
    'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
    Dim Request         As New XMLHTTP30
    Dim Results         As New DOMDocument30
    Dim StatusNode      As IXMLDOMNode
    Dim DistanceNode    As IXMLDOMNode
    Dim Mode            As String
    
    'Checking if the Travel Mode value is correct (according to defined API's values).
    'Here the "transit" mode was omitted because it would require additional parameters
    '(departure time or arrival time) that would make the function quite complex.
    Select Case LCase(Travel_Mode)
        'Default, indicates standard driving directions using the road network.
        Case "driving": Mode = "driving"
        'Walking directions via pedestrian paths & sidewalks (where available).
        Case "walking": Mode = "walking"
        'Bicycling directions via bicycle paths & preferred streets (where available).
        Case "bicycling": Mode = "bicycling"
        'If driving mode was omitted, the default driving mode is used.
        Case Else: Mode = "driving"
    End Select
            
    'Check if origin is not empty.
    If Origin = "" Then
        TripDistance = "Origin is empty"
        Exit Function
    End If
    
    'Check if destination is not empty.
    If Destination = "" Then
        TripDistance = "Destination is empty"
        Exit Function
    End If
                
    'Removing possble spaces from the input variables.
    Origin = WorksheetFunction.Substitute(Origin, " ", "%20")
    Destination = WorksheetFunction.Substitute(Destination, " ", "%20")
    
    On Error GoTo errorHandler
    
    'Create the request based on Google Directions API. Parameters (from Google page):
    '- Origin:      The address or textual latitude/longitude value from which you wish to calculate directions.
                    'If you pass an address as a string, the Directions service will geocode the string and convert it to a
                    'latitude/longitude coordinate to calculate directions.
                    'If you pass coordinates, ensure that no space exists between the latitude and longitude values.
                    
    '- Destination: The address or textual latitude/longitude value from which you wish to calculate directions.
                    'If you pass an address as a string, the Directions service will geocode the string and convert it to a
                    'latitude/longitude coordinate to calculate directions.
                    'If you pass coordinates, ensure that no space exists between the latitude and longitude values.
                    
    '- Ssensor:     Indicates whether or not the directions request comes from a device with a location sensor.
                    'This value must be either true or false.
                    
    '- Mode:        Optional parameter - default value is "driving". Specifies the mode of transport to use when calculating directions.

    Request.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?" _
    & "origin= " & Origin & "&destination=" & Destination & "&sensor=false" & "&mode=" & Mode, False
            
    'Send the request to the Google server.
    Request.send
    
    'Read the results from the request.
    Results.LoadXML Request.responseText
    
    'Get the status node value.
    Set StatusNode = Results.SelectSingleNode("//status")
    
    'Based on the status node result, proceed accordingly.
    Select Case StatusNode.Text
            
        Case "OK"   'The response contains a valid result.
            'Get the distnance node value and calculate trip distance in km.
            Set DistanceNode = Results.SelectSingleNode("//leg/distance/value")
            TripDistance = CDbl(DistanceNode.Text) / 1000
    
        Case "INVALID_REQUEST"  'The provided request was invalid.
                                'Common causes of this status include an invalid parameter or parameter value.
            TripDistance = "Invalid request"
        
        Case "NOT_FOUND"    'At least one of the locations specified in the requests's origin,
                            'destination, or waypoints could not be geocoded.
            TripDistance = "Origin/destination could not be geocoded"
                    
        Case "ZERO_RESULTS" 'No route could be found between the origin and destination.
            TripDistance = "Could not find route"
                                
        Case "MAX_WAYPOINTS_EXCEEDED"   'Too many waypoints were provided in the request The maximum allowed waypoints is 8, plus the origin, and destination.
                                        '(Google Maps API for Business customers may contain requests with up to 23 waypoints.)
            TripDistance = "Too many waypoints"
            
        Case "OVER_QUERY_LIMIT" 'The requestor has exceeded limit.
            TripDistance = "Requestor has exceeded limit"
            
        Case "REQUEST_DENIED"   'The service denied use of the directions service.
            TripDistance = "Invalid sensor parameter"
        
        Case "UNKNOWN_ERROR"    'The request could not be processed due to a server error.
            TripDistance = "Server error"
        
        Case Else   'Just in case...
            TripDistance = "Error"
        
    End Select
        
    'In case of error, release the objects.
errorHandler:
    Set StatusNode = Nothing
    Set DistanceNode = Nothing
    Set Results = Nothing
    Set Request = Nothing
    
End Function



Limitations



If you use this function have in mind three things:
  1. The use of the Google Directions API is subject to a limit of 2500 requests per day, so be careful not to exceed this limit.
  2. The Directions API may only be used in conjunction with displaying results on a Google map. Using Directions data without displaying a map for which directions data was requested is prohibited from Google. This is the reason why I put the map in the beginning of this post. Additionally, calculation of directions generates copyrights and warnings which must be displayed to the user in some fashion.
  3. The accuracy of the Trip Distance function is strongly related with the accuracy of the Google Maps algorithm. This means that for a particular trip Google Maps might not return the most appropriate route.
So, use the function but do not forget the above limitations. In the sample Excel file I have used this technique in order to add description to the above function.



Download it from here




The file can be opened with Excel 2007 or newer. Please enable macros before using it.



Read also




Did you like this post? If yes, then share it with your friends. Thank you!



Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: tel. +30-6973513308, e-mail , Facebook , Twitter , Google+ and Linkedin . Full CV