Last updated: 09/08/2018, 2 min read (without the code)
.jpg "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://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: [email protected]
'Site: https://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://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:
- 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.
- 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.
- 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
The file can be opened with Excel 2007 or newer. Please enable macros before using it.
Hi Ricky,
Thank you for suggestions and your code.
The custom function above worked without any problem to all the test cases that I run.
I wrote it based on the guidelines that are provided on Google’s API page. The function can be also used to acquire trip duration. For example down from this line:
TripDistance = CDbl(DistanceNode.Text) / 1000
You can add these lines:
Dim DurationNode As IXMLDOMNode
Dim Duration As Double
Set DurationNode = Results.SelectSingleNode(“//leg/duration/value”)
Duration = CDbl(DurationNode.Text) / 60
Debug.Print Round(Duration, 0) & ” min”
And now you will have trip duration (in minutes) printed in the immediate window of the VBA editor.
Best Regards,
Christos
Great write up! I have been working with this same code but wow the options i did not know i even had. Also, it is good to know now that i am limited to 23 waypoints and finally know why my large mapping code always errors after 23. My next task is hourly weather for each waypoint.
Do you ever get errors with this?
TripDistance = CDbl(DistanceNode.Text) / 1000
I quit using if because even know it seemed to do what it wanted it to do. You cannot or i cannot seem to add to the string. I use:
Set oXH = CreateObject(“msxml2.xmlhttp”)
With oXH
.Open “get”, sURL, False
.send
bodytxt = .responseText
End With
bodytxt = Right(bodytxt, Len(bodytxt) – InStr(1, bodytxt, ““) – 5)
tim_e = Left(bodytxt, InStr(1, bodytxt, ““) – 1)
bodytxt = Right(bodytxt, Len(bodytxt) – InStr(1, bodytxt, ““) – 5)
distanc_e = StrConv(Left(bodytxt, InStr(1, bodytxt, ““) – 1), 1)
dis = Replace(distanc_e, “mi”, “”)
tim_e = StrConv(tim_e, 1)
This code seems to do the trick and you can pull way more data off the XML file. I am no developer. .I use what I can find and alter it. The code above also makes it easier to convert to Miles. I live in the US. By adding:
& mode “&sensor=false&units=imperial” to the end or your URL. and your good to go.
You do not have to convert and seems accurate. The code, below, is what i was using before i changed to the code above. It worked well but i could not figure out how to add the duration time to it.
Round(DistanceNode.Text / 1609.344, 2)
I thank you again for your write up. I added all your code into mine and it works perfectly.