API Key Distance Matrxi

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Solved4.85K views

I copied your VBA for the distance matrix and have been using is for a while. It has just stopped working and it appear to now require an API Key. How do I pass this within the VBA? Below is what I have tried. I am only attempting to retrieve a mileage for one address at a time.

‘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 = “https://maps.googleapis.com/maps/api/distancematrix/json?origins=”
secondVal = “&destinations=”
lastVal = “&mode=car&language=pl&sensor=false&Key=place key here”
Set objHTTP = CreateObject(“MSXML2.ServerXMLHTTP”)
Url = firstVal & Replace(start, ” “, “+”) & secondVal & Replace(dest, ” “, “+”) & lastVal
objHTTP.Open “GET”, Url, False
objHTTP.setRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)”
objHTTP.send (“”)
If InStr(objHTTP.responseText, “””distance”” : {“) = 0 Then GoTo ErrorHandl
Set regex = CreateObject(“VBScript.RegExp”): regex.Pattern = “””value””.*?([0-9]+)”: regex.Global = False
Set matches = regex.Execute(objHTTP.responseText)
tmpVal = Replace(matches(0).SubMatches(0), “.”, Application.International(xlListSeparator))
GetDistance = CDbl(tmpVal)

Exit Function
GetDistance = -1
End Function

Thank you for your help.


I was having the same problem as you and your post actually helped me a bit. Your error I think is just that you have the ‘k’ in key capitalized – use lower case and that worked for me.


Hi, google now requires that you generate and use your own Key. Go to the link on the post webpage and create your own Google Distance Matrix API Key then in the code snippet above replce the part:

Key=place key here

selected as best answer

Thanks David & Mike. Just for clarity for any other visitors here, the changes David & Mike have made that make it work are:
1. line 4 is now https
2. line 6 has &key=yourkeyhere added to it

commented on answer

Thanks! These adjustments worked for me as well.


Thanks David, Mike & David :). I have updated the example and will need to update the page accordingly.


I just bought our solution… the first results after enable macros are -1… why?


Unfortunately it not working for me.
I am new in VBA so maybe I am doing something wrong.
I create mi API key on google (I hope that this free of charge API is enough)
I copied you VBA to my module, i can see formule =getDistance, put as it was before (when works) but now I see only result “-1” ( I mean error)

What am I doing wrong?

Is is possible for you to paste fully worked VBA?

thank you in advance.

Changed status to publish

Simply the best place to learn Excel VBA