NEW Use VBA to Retrieve distances between multiple addresses in Excel

It's about time!!!

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

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

Leave a Reply

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