Distance between two places

### 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)

### 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

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 domDoc As DOMDocument60
Dim xmlNodesList As IXMLDOMNodeList

Dim response(1) As Variant 'array to hold distance &amp; duration

' API URL is formed in excel sheet config using excel formula.
' refer excel workbook for the API URL format
urlForDistance = Range("urlForDistance").Value

' invoke the API to get the Distance Matrxi in XML format

' Get the response XML
Set domDoc = New DOMDocument60

' 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
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.