Calculate distance between two places in Excel [With Fun]

.

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

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

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

Download Now

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

10 Comments

  1. Krishnaprasad Menon

    Very Nice. i have not studied it in detail, but downloaded the file. It is giving a run time error ‘-2147024891 (80070005)’; Access denied.

    Reply
    • Vishwamitra Mishra

      Hi,
      Can you run it in debug mode and tell me where are you getting the error? Which version of excel are you using?

      Reply
  2. Craig

    Hi Thanks for this,

    I’m trying to convert this to use on my own workbook but getting an error “object varioable with block not set” at response(0) = xmlNodesList(0).Text

    any idea what might eb going on here or what I might be missing?

    Reply
    • Abhishek

      Same error here too!

      Reply
      • Lee

        I am getting the same error!!

        Reply
  3. Benjamin

    This is just amazing. Really solid work I am curious is their any limitation to the amount of search that can be done. Can you suggest any way to remove the counter of the number so it just shows the number right away and is there anyway to show an actual map?

    Reply
  4. Anthony Short

    Doesn’t seem to be correct driving distance…possibly direct from 1 point to the other

    Reply
  5. Robert

    Hi, i am using excel 2010 version and after clicking on the “Get Distance” button it is giving error message VBA: Run time error ’91’ ( Object variable not set ).

    When i click on the debug button, then following statement is displayed with yellow color : response(0) = xmlNodesList(0).Text

    ( response(0) = Empty )

    Reply
  6. Robert Meleg

    Hi, i am using excel 2010 version and after clicking on the “Get Distance” button it is giving error message VBA: Run time error ’91’ ( Object variable not set ).

    When i click on the debug button, then following statement is displayed with yellow color : response(0) = xmlNodesList(0).Text

    ( response(0) = Empty )

    Reply
  7. Johan Peters

    Sheet does not work.. when i whant to calculate i receive a error.. project or library not found..

    Reply

Trackbacks/Pingbacks

  1. Distance Calculator in Excel VBA [REVISED] - Welcome to LearnExcelMacro.com - […] had written an article long back about how to calculate distance and time taken between two places in Excel…
  2. Distance Calculator in Excel VBA [REVISED] - Let's excel in Excel - […] Calculate distance between two places in Excel [With Fun] VBA to Add Static Google Map in Excel Sheet Invoice…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest