(Solved) – #value error with excel distance calculation by using Google Maps API key


I’m trying to develop a function in excel that returns the traveltime between two locations. I used this code i found online, yet always get a #value error. When I plug in the strUrl in my browser with my apikey the request works.

Help is very much appreciated.

' 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/distancematrix/json?units=imperial&origins=" & origin & "&destinations=" & destination & "&key=" & apikey

    Set httpReq = CreateObject("MSXML2.XMLHTTP")
    With httpReq
         .Open "GET", strUrl, False
    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

Leave a Reply

Your email address will not be published. Required fields are marked *