Calculating distance between adresses using Google API in Excel VBA

Question

I found code online to calculate the distance between several addresses (~10k pairs). Two different codes from two websites both give me an error.

I created my own Google API, and activated billing (using the URL test works for me) and tried some suggestions from other forums.

1. Approach
Found on: https://analystcave.com/excel-calculate-distances-between-addresses/

'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&key=YOUR_KEY"
    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

Every time I apply the formula, I get a "-1" i.e. error message.

2. Approach
Found on: https://syntaxbytetutorials.com/excel-function-to-calculate-distance-using-google-maps-api-with-vba/

I added VBA-JSON and activated references as suggested by the author.

' Returns the number of seconds it would take to get from one place to another
Function TRAVELDISTANCE(origin, destination, apikey)
 
    Dim strUrl As String
    strUrl = "https://maps.googleapis.com/maps/api/directions/json?origin=" & origin & "&destination=" & destination & "&key=" & apikey
     
    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    With httpReq
        .Open "GET", strUrl, False
        .Send
    End With
     
    Dim response As String
    response = httpReq.ResponseText
     
    Dim parsed As Dictionary
    Set parsed = JsonConverter.ParseJson(response)
    Dim meters As Integer
     
    Dim leg As Dictionary
    
    For Each leg In parsed("routes")(1)("legs")
        meters = meters + leg("distance")("value")
    Next leg
     
    TRAVELDISTANCE = meters
 
End Function 

The function does not compile due to an error in the line

strUrl = "https://maps.googleapis.com/maps/api/directions/json?origin=" & origin & "&destination=" & destination & "&key=" & apikey

It returns an error.

Expected: end of statement

Answer 1

As far as the second approach is concerned, according to this, the URL should look like the following example:

https://maps.googleapis.com/maps/api/directions/json?origin=Disneyland&destination=Universal+Studios+Hollywood&key=YOUR_API_KEY

So in your case, if you have a variable named origin, another one named destination and a third one named apikey, the URL should be built like so:

strUrl ="https://maps.googleapis.com/maps/api/directions/json?origin=" & origin & "&destination=" & destination & "&key=" & apikey

The function would have to be called like so, for example:

call TRAVELDISTANCE("Disneyland", "Universal+Studios+Hollywood", "xxxxx")

I suppose what happened is that in the website where you found the original code, the & character was resolved in its HTML version which is: &. This however will give an error in VBA.

So before copying and pasting something you need to understand how it works.

To combine strings the following applies in general:

finalString= String1 & String2 & "String3" & "String4" & ..... 'depending on whether your strings are stored in variables or not.