copy

Wednesday, 28 August 2013



Last updated: 09/08/2018, 2 min read (without the code)

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, hence 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 for the custom Trip Distance function. To tell you the truth, the code is more XML than VBA.

2018 Update: the function has been updated to reflect the changes in Google API. To use this VBA function you will need a valid API key. Check this link that presents a step-by-step guide on how to acquire one for free.

2018 Update 2 (August): The EncodeURL function was added to avoid problems with special characters. This is a common problem with addresses that are from Greece, Serbia, Germany and other countries.

Option Explicit

Function GetTripDistance(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 40,000 requests per month, so be careful not to exceed this limit. For more info check:
    'https://developers.google.com/maps/documentation/directions/usage-limits
        
    '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.
    
    '2018 Update: In order to use this function you will now need a valid API key.
    'Check the next link that guides you on how to acquire a free API key:
    'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html
    
    'Written By:    Christos Samaras
    'Date:          28/08/2013
    'Last Updated:  09/08/2018
    'E-mail:        xristos.samaras@gmail.com
    'Site:          https://www.myengineeringworld.net
    '--------------------------------------------------------------------------------------------
    
    'Declaring the necessary variables. Using 30 at the first two variables because of it
    'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
    Dim ApiKey          As String
    Dim Request         As New XMLHTTP30
    Dim Results         As New DOMDocument30
    Dim StatusNode      As IXMLDOMNode
    Dim DistanceNode    As IXMLDOMNode
    Dim Mode            As String
    
    'Set your API key in this variable. Check this link for more info:
    'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html
    ApiKey = "Your API Key goes here!"
    
    'Check that an API key has been provided.
    If ApiKey = vbNullString Or ApiKey = "Your API Key goes here!" Then
        GetTripDistance = "Invalid API Key"
        Exit Function
    End If
    
    '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 the travel mode is omitted, the default driving mode is used.
        Case Else: Mode = "driving"
    End Select
            
    'Check if the origin is not empty.
    If Origin = vbNullString Then
        GetTripDistance = "Origin is empty"
        Exit Function
    End If
    
    'Check if the destination is not empty.
    If Destination = vbNullString Then
        GetTripDistance = "Destination is empty"
        Exit Function
    End If
                    
    'Note: The EncodeURL function was added to allow users from Greece, Poland, Germany, France and other
    'countries use the addresses from their home countries without a problem. The particular function,
    'returns a URL-encoded string without the special characters.
    Origin = Application.EncodeURL(Origin)
    Destination = Application.EncodeURL(Destination)
    
    'Generic error handling.
    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.

    '- Mode:        Optional parameter - default value is "driving". Specifies the mode of transport to use when calculating directions.

    Request.Open "GET", "https://maps.googleapis.com/maps/api/directions/xml?" _
    & "origin= " & Origin & "&destination=" & Destination & "&mode=" & Mode & "&key=" & ApiKey, 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 distance node value and calculate trip distance in km.
            Set DistanceNode = Results.SelectSingleNode("//leg/distance/value")
            GetTripDistance = CDbl(DistanceNode.Text) / 1000
        
        Case "NOT_FOUND"    'At least one of the locations specified in the request's origin,
                            'destination or waypoints could not be geocoded.
            GetTripDistance = "Origin/destination could not be geocoded"
                    
        Case "ZERO_RESULTS" 'No route could be found between the origin and destination.
            GetTripDistance = "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.
            GetTripDistance = "Too many waypoints"
        
        
        Case "MAX_ROUTE_LENGTH_EXCEEDED" 'The requested route is too long and cannot be processed. This error occurs when more complex directions are returned.
                                         'Try reducing the number of waypoints, turns, or instructions.
            GetTripDistance = "Route is too long"
        
        Case "INVALID_REQUEST"  'The provided request was invalid.
                                'Common causes of this status include an invalid parameter or parameter value.
            GetTripDistance = "Invalid request"
            
        Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit.
            GetTripDistance = "Requestor has exceeded limit"
            
        Case "REQUEST_DENIED"   'The service denied use of the directions service.
            GetTripDistance = "Request was denied"
        
        Case "UNKNOWN_ERROR"    'The request could not be processed due to a server error.
            GetTripDistance = "Server error"
        
        Case Else   'Just in case...
            GetTripDistance = "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 40,000 requests per month, 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 at 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 to 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.



Downloads



Download

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: e-mail, Facebook, Twitter, Google+ and Linkedin. More info