Distance Calculator in Excel VBA [REVISED]

.

Hi guys, on June 2016, google made some changesaround pricing and authentication etc. of its FREE APIs.Distance calculator API was also affected by those changes. I had written an article long back about how to calculate distance and time taken between two places in Excel VBA using google matrix api. In addition to this article, I also built a small Excel tool that calculates the distance and time taken between two places you type it in. It also has an option to choose your mode of transport. It looks something like the below:

Distance Calculator

Distance Calculator

Ever since the changes have taken place, I am getting many questions from LEM readers that this tool does not work for them anymore.

Therefore, I thought to write this article to explain to you more in detail.

In the previous article, I have simply shared a simple VBA code to call Distance matrix API – where no authentication (API Key) was required…

After that change, the tool I had created and an article I had published was not working. Although I had replied via comments many times, I keep getting this question again and again. Therefore, I decided to write this article to answer all the questions at once.

Now, In order to invoke Distance matrix API from Google, first, you need to get an API key using your google account. For that matter, in order to access any of its APIs, you need to get an API key to authenticate your API calls.

How to get Google API Key

You can follow the simple steps given here in this article from google – https://developers.google.com/maps/documentation/distance-matrix/get-api-key. Before, you try to call the API from Excel VBA, get this API key for yourself.

As I mentioned previously, it is no longer for free (100%), you will have some limitations by using your FREE account. more details can be found here

How to use the API key – Excel VBA

As you have learned in the previous article about how to create different parameters before your make an API call, that remains exactly the same except for the fact that now you need to append &key=YOUR_API_KEY

For example: Here is how a sample URL looks like using an API key

https://maps.googleapis.com/maps/api/distancematrix/xml?origins=Amsterdam&destinations=Utrecht&mode=driving&key=YOUR_API_KEY

What is fixed in the Tool

There are Two things:

In the code, I have added an exception handling, so that you know what is wrong with the API call. It was my mistake that in my previous article, I did not use any exception handling, therefore it was not clear what exactly the problem is.

Secondly, I have added a placeholder for your API key which you can store in the config sheet and the tool is ready for your own use.

VBA to calculate the distance between two places

Here is the VBA code to call Google Distance Matrix API and get the distance and time taken :


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 status As String
    Dim errorMessage As String
    
    Dim response(1) As Variant 'array to hold distance & duration
    
    On Error GoTo err
'API URL is formed in excel sheet config using exccel formula
    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 first get the status of the API Call
    status = domDoc.SelectSingleNode("//status[1]").nodeTypedValue
    
    errorMessage = domDoc.Text
    If status = "OK" Then
' 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
    Else
        
        MsgBox errorMessage
        
    End If
err:
    Set domDoc = Nothing
    Set googleAPIRequest = Nothing
End Function

 

Distance Calculator Tool

Note:

Before you run this excel sheet, you need to get your API key generated from your google account and put that key in the excel sheet at a specified place and then enjoy using it.

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…

2 Comments

  1. Olaf

    Thanks for updating your VBA code to accommodate changes by Google. At the moment it appears be the only one working example on the whole internet. Congratulations and thanks again.

    Reply
  2. Ericken

    What about using Bing’s API?

    Reply

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