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

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

    • Hi Mathew

      I will be happy if get distance between latitude and longitude in same workbook.

      Kindly help me to save our country πŸ™ kinds in this corona pandemic

      • Srikanth,

        I recently built a project for another client that retrieved longitude and latitude – and calculated the approximate distance between those two points. I will try to get that project and upload some of the elements to this blog.

        I’m curious. Where are you? And what will you be using the project for?

        Thanks,
        Matt

        • Thanks for swift response Mathew!!
          Srikanth here from INDIA & part of NGO.

          We do have nearly 20,863 latitudes & longitudes of poor people who cannot afford food & daily needs in this pandemic due to our country lockdown.

          We are struggling to deliver this ontime since we don’t have route metrics here.
          But still serving people by doing some ancient route techniques & seeking help from local people.

          We’ll be happy if you can support us!!

          All We need is distance of all latitudes and longitudes from our base station.

          Thanks in advance

          • Hi Matt
            Expecting a little support on this, since we couldn’t afford any it support provider here.

            We’re sure this can make our manual routing little easier!!

            Awaiting for your reply!!☺️

          • Srikanth: I will respond via email. Right now I am working on several items that require my attention. Hopefully I can get a very clear idea of what you need and may be able to allocate time to help you in an day or two.

  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.

  8. Hi Matthew – Thank you so much for your AMAZING spreadsheet. :). Do you have any idea how much google charges to look up the addresses? Just want to set up a budget and I can’t find much information on the Google Cloud Platform on cost. Thanks in advance for your help! – Shannon

    • Hi Shannon,

      Thanks! I’m glad you found it helpful. I’ve provided the link to the billing below. The price, for the first 100,000 requested items is .005 (half a cent). You get a $200 credit (free) each month. That is enough for 40,000 requested items.

      Each address combination in a request is a requested item. So, if my sheet has 1 origin and 10 destination addresses, that is 10 requested items. 10 requested items is: $0.05.

      If you modify the sheet to calculate multiple origins against multiple destinations, the number of requested items is origin addresses X destination addresses. So 5 origin addresses and 10 destinations is 50 requested items or $0.25.

      Link to the pricing can be found here:
      https://developers.google.com/maps/documentation/distance-matrix/usage-and-billing

  9. Any chance there is a version of this code for Access 2007 VBA? Access needs all variables to be declared before use.
    Thank you

    • I’ve ported this to Access a few times – though I used an Excel object just for the sake of using the built in URL Encode function. Also, almost all the apps I write in Access include Excel as a way to move data in and out of the system.

      Regarding declaring variables: Access only needs variables declared if you tell it to require that. But when I am not being lazy, that is the appropriate way to handle them.

  10. Is there a complete tutorial I am a complete noob and not in the slightest a programmer how do I attach a API? I need this so bad right now its not funny ha

    • The code that “attaches” the API is in the file you download. It is less about attaching anything, in this case, than referencing it via an HTML object. Although, as a noob, using the term “object” may also be challenging. With Google API’s, the most important aspect is signing up for their API’s – as needed. Also, you need to have an active billing account with Google – and connected to the API account.

      Are you brand new to VBA? How might I help you understand? If you are brand new with programming entirely, you will require some time understanding the code in its entirety.

  11. Can you let me know how I can use to get a detour route using the Google API. E.g. Base Location and To Location are just about 100 m apart on the same road. How to find the alternative route between these two points rather than the straight 100 m distance.

    – Second best route.
    – Ignore the straight distance in between.

    • Thanks for leaving a comment. I’d have to revisit this project. My understanding is that, if the optimal route is closed, it uses the next optimal route. I may have time this coming week to look into this. If so, I’ll comment here.

Leave a Reply

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