I used a tutorial to automatically calculate travel distance using Google Directions API, based on addresses I enter in Excel. I succesfully created an API key and I used a tutorial page that showed two functions in VBA, one for Time and one for Distance:

    'Returns the number of seconds it would take to get from one place to another
Function TRAVELTIME(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 seconds As Integer
     
    Dim leg As Dictionary
     
    For Each leg In parsed("routes")(1)("legs")
        seconds = seconds + leg("duration")("value")
    Next leg
     
     
     
    TRAVELTIME = seconds
 
End Function
 
 
' Returns the distance
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

When I try to use the distance function, it fails most of the time. I can get it to work if I enter a single dimension of a location, e.g. "Kerkstraat 10", but if I add the city name, e.g. "Kerkstraat 10, Amsterdam", I get a #Value error in Excel.

Whether I use spaces, commas, semicolons, %20 or whatever in VBA doesn't seem to matter and interestingly, the traveltime function works perfectly with a "full" address.

Does anyone know what might be wrong, or if there is a better/different way to get what I need?

Thanks in advance!