1

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!

SanderVeeken
  • 11
  • 2
  • Call your function from a Sub instead of from a worksheet cell, and you'll be able to debug it. – Tim Williams Jun 23 '22 at 16:37
  • My bet is on the JSON converter. I've encountered so many JSON strings it can't parse. Follow @TimWilliams advice and see if the problem is in the response or the parsing. – Ambie Jun 23 '22 at 21:06
  • Using the `distancematrix` api, I replace all the spaces in the origin and destinations with `+`. I don't know if that would make a difference as my api is not set up to work with the `directions` api so I really can't test your code. – Ron Rosenfeld Jun 24 '22 at 02:05
  • Sorry for being a VBA noob but how do I debug my custom function? If I open the VBA interface I have a few options in the debug menu but most don't seem to do anything (e.g. step into) and "run to cursor" says "line is not an executable statement". If I try to look in the "run" menu I can only run a macro, not my function. Sorry for being new at this. I'm used to getting plenty of python tracebacks which give more information. Finally, if I enter the string into my browser I get a perfect JSON back and the function seems to look for the right field. – SanderVeeken Jun 24 '22 at 18:28
  • I finally solved this by defining all datatypes in the function (origin as string etc.) and switching from integer to long for the meters variable. That made it work in one cell. If I copied down a cell it didn't work anymore (for a second address, I used a nested index/match to get the address from a name). But rewriting the function for the second (and third) cell worked, yay! – SanderVeeken Jun 24 '22 at 19:00

1 Answers1

0

I had the same issue, and what I did was just replace integer with long for the meters and seconds variables and it worked.

Pranay
  • 1
  • 1