Last week, As I had placed an article on how to create 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.
How above excel workbook is made?
Above excel has 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 moving on excel
Calculate distance and duration between two places
Using Google Distance Matrix API, you can calculate 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 type of transport mode 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)
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/*") response(0) = xmlNodesList(0).Text ' Using xPath get the duration Set xmlNodesList = domDoc.SelectNodes("//duration/*") 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
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 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!!