Hi, excellent article on calculating distances in VBA using the Google Maps Distance Matrix API.
I modified the lastVal string var in your GetDistance function slightly to change the travel mode to transit. Following the Google Maps Distance Maxtrix API documentation, I also supplied a transit_mode=subway parameter and value in the lastVal string.
'Calculate Google Maps distance between two addresses
Public Function GetDistance(start As String, dest As String)
Dim firstVal As String, secondVal As String, lastVal As String
firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
secondVal = "&destinations="
lastVal = "&mode=transit&transit_mode=subway&language=en&sensor=false"
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
The function is now not working, always returns -1. BTW, all the addresses are within New York City, where we have an extensive subway system.
Do you know what I’m doing wrong?
Seems this is a functionality limited to when you provide an API key or Client ID.
Run this link in the browser to see Google error message:
This message is not thrown when using other modes of transport.
Setting up your API Key is free (as long as you don’t exceed the limits, then it is still very cheap – read here) and all you need to do to make the query work is append your API Key to the URL: