Google Maps Distance Matrix API transit mode doesn’t work?

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Solved3.77K views

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 = ""
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?

Thank you.

edited answer

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:,US&destinations=Washington,US&mode=transit&transit_mode=subway&language=pl&sensor=false

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:

commented on answer

Thank you so much AnalystCave for the quick response — that worked (FYI, I also had to change the start of the URL to https). Also, great website. I should purchase something from here in return for you helping me out : )

Tom (AnalystCave)

Hi Stephan was my pleasure to help! In any case any support to maintaining is always appreciated!

Simply the best place to learn Excel VBA