Dear readers,

Last week, I had placed an article on how to create a static google map in excel using Google Static Map API. Over this weekend, I thought of creating some fun for you in Excel using Google Maps Distance Matrix API. This article is going to be a learning with fun or you can say download some fun in excel with some learning.

Distance between two places

How above excel workbook is made?

Above excel has the following two main parts:
1. How to calculate Distance and time between two places using Google Maps API
2. How to make a shape/picture move on excel

Calculate distance and duration between two places

Using Google Distance Matrix API, you can calculate the distance between two places. By Google Maps Matrix API, you can also calculate travel time or duration between two places. As you know Distance and duration between two places might differ for different types of transport modes like Bicycling, Car, Walking, etc., Google API provides this feature to pass your mode of transport as an input parameter in the API.

Google Maps Distance Matrix API uses a URL which should be in this format: (copied from the above google documentation page)

https://maps.googleapis.com/maps/api/distancematrix/parameters

Parameters

There are many possibilities of parameters which can be provided in order to generate your map.. again more details you can find it on above page

You can read more about this API by going through the Google API documentation page.

Following is the function which will return distance and duration between two places.

Function getDistanceAndTimeBetweenTwoPlaces() As Variant
' This function will return an array holding
' distance in meters and duration in seconds
    Dim googleAPIRequest As XMLHTTP60
    Dim domDoc As DOMDocument60
    Dim xmlNodesList As IXMLDOMNodeList
    
    Dim response(1) As Variant 'array to hold distance & duration
    
' API URL is formed in excel sheet config using excel formula.
' refer excel workbook for the API URL format
    urlForDistance = Range("urlForDistance").Value
    
    Set googleAPIRequest = New XMLHTTP60
' invoke the API to get the Distance Matrxi in XML format
    googleAPIRequest.Open "GET", urlForDistance, False
    googleAPIRequest.Send
    
' Get the response XML
    Set domDoc = New DOMDocument60
    domDoc.LoadXML googleAPIRequest.ResponseText
    
' Using xPath get the distance
    Set xmlNodesList = domDoc.SelectNodes("//distance[1]/*")
    response(0) = xmlNodesList(0).Text
    
' Using xPath get the duration
    Set xmlNodesList = domDoc.SelectNodes("//duration[1]/*")
    response(1) = xmlNodesList(0).Text
    
' Return response with distance and duration in array
    getDistanceAndTimeBetweenTwoPlaces = response
    
' release memory
    
    Set xmlNodesList = Nothing
    Set domDoc = Nothing
    Set googleAPIRequest = Nothing
End Function

Using the above code, I got the distance and duration between two places.

Fun Part: Animation – Moving vehicle

moving car – in Excel

This is made in two parts:

1. Change Image based on selected value in drop down

I will explain in detail in my next article.

2. Move vehicle or image from Left to Right

It is simple. All you need to do is get a fix increment value and start incrementing the “Left” amount for that shape. This way it seems that object is moving from Left to right.
To understand more, you can go through the comments line for each of the statement in below code.

Sub StartVehicleFromSourceToDestination()
    
    Dim distanceAndDuration As Variant
    Dim distance As Long
    Dim Duration As Long
    
    Application.ScreenUpdating = True
'get the distance and duration from the above function
    distanceAndDuration = getDistanceAndTimeBetweenTwoPlaces
    distance = distanceAndDuration(0)
    Duration = distanceAndDuration(1) / 60
    
' divide distance and duration with an equal interval
' for a smooth moving shape (vehicle)
    iduration = Duration / 160
    idistance = distance / 160
    
' reset distance, duration, starting place of shape (vehicle)
    resetData
    
' Now loop through 1 to total no of intervals
' 160 - same no as duration and distance are
' divided by
    For i = 1 To 160
        
        With ActiveSheet
' increment left with a fixed number in order to show vehicle
' moving from Left to right direction with same speed.
            .Shapes("truck").IncrementLeft 2.18
            
' keep increasing the distance and duration with the same intervals
' calculated above to give an animated calculation effect.
            .Range("distance").Value = Range("distance").Value + idistance
            .Range("duration") = Range("duration") + iduration
' most importantly !! do not forget to put below
' statement. this will keep refreshing the screen
' and hence you would be able to see the vehicle
' moving with a constant speed
            DoEvents
        End With
    Next
End Sub

Now you know how to calculate distance and time taken (duration) in excel by using Google distance matrix API

DO NOT Forget to download this fun excel and play around with it.

Download

Before downloading this version – I would recommend you to read this revised article.

Download this, use it and do not forget to provide me your feedback by typing your comment here or sending en email or you can twit me You can also share it with your friends, colleagues or whomsoever you want to!!