VBA Formula to Calculate Distances with Google Maps API

Hello,

I am trying to get a formula I found to work for a project I am working on. The suggested VBA code is;

'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=car&language=pl&sensor=false"
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
ErrorHandl:
GetDistance = -1
End Function

Found on http://www.analystcave.com/excel-cal...een-addresses/

I am using Chrome and I notice that it specifically states several web browsers in the text. Is that the issue? How can I get it to work with Chrome? The error I get is "Compile Error: Invalid Outside Procedure

Regards,

Tony

I removed that line but am still getting an error of #value!. I have included a link to the file below as it is too large to post in the thread. Any help would be appretiated.

https://www.dropbox.com/s/5if8mlri2i...Data.xlsm?dl=0

Thank you! It works perfectly now. You are my hero. Saved me hours of manual searching!

It'll also stop working. Google are pretty stringent on their use of APIs and regularly stop access to those who break the terms and conditions (which this does). Bing has no such stipulations providing you have a licence key, see: http://www.excelforum.com/tips-and-t...rivetimes.html

PS Please use code tags when posting code

How does it break the terms of use?

Originally Posted by Someone at Google;

Use of the Google Maps Distance Matrix API must relate to the display of information on a Google Map; for example, to determine origin-destination pairs that fall within a specific driving time from one another, before requesting and displaying those destinations on a map. Use of the service in an application that doesn't display a Google map is prohibited. For complete details on allowed usage, consult the Maps API Terms of Service License Restrictions.

https://developers.google.com/maps/d...x/usage-limits

Thanks Kyle! I appreciate the information.

Anyone any experience with adding optional parameters such as "arrival_time" "traffic_model"?