Updated: Microsoft Excel VBA and Google Maps to calculate distances and time from multiple locations

Excel File download with VBA and Google Maps to calculate distance and duration between multiple destination addresses.

Update 12/17/2019: Important! You must have a Google Matrix API Key and IT MUST BE CONNECTED TO AN API BILLING ACCOUNT!
If your billing account is not active or connected to the API account, the tool WILL NOT RUN! 
I apologize for leaving this out of the instructions.


I've been asked to update this and address a few things in the code for a couple years.

Back on October of 2016, I posted this blog - with an Excel download. The file calculated distances between a base address and a list of addresses. As I explained at the time, it was used for a marketing report for one of my clients. They wanted to know how far their customers were driving to get to them and from where.

Since then, I've had a LOT of people download the file. However, changes to the Google Maps API Distance Matrix was causing the code to fail frequently and eventually always. A few reasons for this.

  1. It used an outdated URL
  2. It didn't include the API Key
  3. It didn't include region (needed for some people).

INTRODUCING OUR NEW COURSE

Excel Automation for Non-programmers

A project-based course to help you
use Excel VBA where it matters...
In YOUR work!

Find out more here

All of that has been addressed and a few other items adjusted as well. Below is a quick video example/demo.

I'm not going through the code here. My current workload doesn't allow me to. But you can go into the code when you download it and I have a few items commented in the code.

A few things of note:

  • I parse this as XML and I want to clean that up a bit. There is no error handling.
  • JSON is the "recommended" way to parse this. I've done that in javascript but I haven't played with the script library in VBA to do this effectively. I'll be adding that at a later time.
  • Google lets you have multiple origin (from) addresses and multiple destination (to) addresses. I use that in this code. I process up to 10 destinations at a time. Why 10? Because. You can do up to 100.
  • I use the Microsoft XML 6.0 Library in the references and use early binding. You may want to change that to late binding... or not. I wanted the intellisense - particularly when working with the childnodes as an IXMLDOMNode.
  • I was modifying the previous code, so what's downloaded here is less efficient than I would like but it is effective.
  • I'm aware of the lack of error checking. I'll leave that up to you. 😉

What I would like from you:

I don't ask for much. But, if you find this helpful, please consider doing the following:

And if you can't do any of that, no worries. I hope the code was helpful. I'll be adding to it. I'll have a json parser and also code to have multiple origin and destination distances. And I'll document the code a bit more.

If you are interested in knowing when that happens, send me a message using the form below.

File download

Download

 

Posted in Consulting, Microsoft Access, Tips and Tools and tagged , , .

17 Comments

    • Thank you Marilyn… I received your email. I’m actually working with another school district on a very similar project (student transportation). I will respond to your email.

  1. Getting run-time error ‘438’
    Object doesn’t support this property or method.
    I added my google api key. it freaks out on the first address: 8901 sunset blvd, west hollywood, ca 90069

    • I’ll take a look at it later. But just to verify, did you connect your account to a billing account? There has to be billing information available.

  2. Hey Matthew – was all geeked to use this, but keep getting Not Responding when I run it, even if I just have one address in there. I also tried just a zip code, and with & without my Google API, and still no luck.

    Curious if you have any suggestions?

    Thanks a ton!
    – Tom

    • You need your Google Distance Matrix API key active AND you need it connected to an active billing account. More often than not, that has been the big issue people face.

      Check that and let me know if you get it to work.

      Thanks. Matthew

    • I’m glad it worked out. I’ve added a BOLD and RED notice at the top of the post. This is the question that comes up most often. Thanks again.

  3. Hey Mr. Matthew
    I’ve recently discovered API’s and the power they hold.
    I work for a family business very small time but sometimes tedious.
    if we deliver a car that is 30+ miles away we get paid $20+ on the tow
    i have a small list of addresses that’s growing and an origin address that doesn’t change.
    vba isn’t my strong suit but thanks to your spreadsheet I have something to work with. Managed to so far find the distance for one address at a time. Baby steps. Any and all help would be appreciated!

    • Hi Austin,

      Thanks for reaching out. You should be able to put your source address at the top and a series of addresses to retrieve distances. Is is not doing that for you?

  4. It worked once for me last night, but now it just hangs – Excel Not Responding. Any ideas? My API is connected to an active billing account. 🙂

    • Erich, Thanks for reaching out. I’ll respond to your email – not really sure what is going on. But you could step through the code one line at a time and see exactly where it is hanging.

  5. Hi Matthew – I’m having the same issue as Erich. Stepping through the code, within the gDist function, the strStatus is returning “REQUEST_DENIED” which is causing the code to loop.

    My API is also connected to an active billing account (I use it for other Excel calls). My API has the Google “Directions API”, “Maps JavaScript API”, and “Road API” enabled. Is there perhaps some other Google API that I also need to enable?

    Many thanks!

  6. Doh – I didn’t see your reply to Thomas Leeper above. Need the Google Distance Matrix API enabled as well. I enabled it and fixed the issue!

    Thanks again for the helpful webpage and code!

    • Hi Paul,

      Yep. I was just going to say, Distance Matrix is the key.

      Glad you got it working and thank you! And, of course, if you get the chance, consider sharing my page. And anyone else who happens to find themselves here.

      Thanks.
      Matthew

      Also, other things I do:
      https://MatthewMoranOnline.com

  7. A huge thanks for the code, Matthew. It eased my first foray into the world of Google. Works like a charm. I am still on Excel 2010 so a minor issue with the missing encodeURL worksheet function – quickly resolved with a freebie function ‘URLencode’.
    The application is part of our large inter-town squash competition here in Australia. We have around 50 towns on file and a matrix of the distances between them all. As we add towns, this function makes life so much easier.

    • Kevin,

      Thanks for letting me know how it is being used. I’ve worked in technology for nearly 30 years and routinely have calls or interactions online with people all over the world. But, the idea that my code, written and published here in Los Angeles, was found, downloaded, and is being used on the other side of the world, is both interesting and somehow encouraging.

      I’m glad you found it helpful.

Leave a Reply

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