Calculate Travel Time and Distance using Google Maps API

[Falko26]

Hey Team,

Has anyone used the Google Maps API with VBA to get Travel Distance and Time? For some reason I am getting it to work with Travel Time but I can only get some of my Travel distances to report correctly. My VBA Function is giving me an #VALUE error.

For some reason my formula will not work for Travel Distance. When using the Test HTTP It works and I get Travel Time and Distance but in Excel only Travel Time works 100% of the time while Travel Distance only works 50% of the Time.

Google Maps API:

Excel Function to Calculate Travel Time and Distance using Google Maps API with VBA - Syntax Byte

In this article, we will create an Excel function to calculate the distance between two addresses using the Google Maps directions API. This will allow you to get the travel time between the two locations. The format of the function will be as follows: =TRAVELTIME(origin, destination, api_key)...

syntaxbytetutorials.com

Code:

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
   
    For Each leg In parsed("routes")(1)("legs")
        seconds = seconds + leg("duration")("value")
    Next leg
   
    TRAVELTIME = seconds
   
End Function

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
   
    For Each leg In parsed("routes")(1)("legs")
        meters = meters + leg("distance")("value")
    Next leg
   
    TRAVELDISTANCE = meters
End Function

 

[John_w]

I can only get some of my Travel distances to report correctly. My VBA Function is giving me an #VALUE error.

Because Dim meters As Integer has a maximum value of 32,767. Change it to Dim meters As Long and the TRAVELDISTANCE function should work for all distances.

I would also change Dim seconds As Integer to Dim seconds As Long in the TRAVELTIME function.

 

[Falko26]

Because Dim meters As Integer has a maximum value of 32,767. Change it to Dim meters As Long and the TRAVELDISTANCE function should work for all distances.

I would also change Dim seconds As Integer to Dim seconds As Long in the TRAVELTIME function.

Thanks John!
I did not know the difference there between Integer and Long but that seems to have worked!!

 

[Jon Peltier]

Thanks John!
I did not know the difference there between Integer and Long but that seems to have worked!!

VBA best practice is to use Long rather than Integer and Double rather than Single. Behind the scenes, VBA converts the smaller variable type to the larger, does any calcs, then converts back. This invalidates the conventional wisdom that smaller variables use less memory and calculate faster.