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.

Inage of Matthew Moran - consultant, author, and automation expert

Hi, I'm Matthew Moran.Β  I am a 25+ year technology consultant, author, and business automation specialist. In my other life, I also write & play music.

I am working to build two YouTube channels: Arrogant Sage Media (for tutorials and related content) & Matthew Moran Music. I would be SUPER grateful if you took a moment and subscribed to those.

Also, my goal is to provide learning across multiple disciplines. If you've found this information helpful, consider buying me a cup or two (or 100) of coffee.

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



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


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


      Also, other things I do:

  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:

  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.

  12. Matt, just wanted to let you know that I came across this tool today and found it very helpful in allowing me to run some analysis for over 150+ locations. We were trying to find the distance between our employees and their office locations and while i made several attempts to use and customize other tools online, i just couldnt get the return values in excel automatically so thank you so much for sharing

  13. I have tried adding billing information but it never works- all I get is that the document “is not responding.” Please help

    • Where are you located? Unfortunately, without looking at your account, it is hard for me to know why it might not run.

  14. Hi Matthew,

    I cannot run the macro by clicking the button. I get an error prompt that reads “Can’t find project or library”
    Any solutions here? I’m using Microsoft 365.

    • You need to go into the code, select “Tools->References” from the menu and see if any of the libraries are listed as missing. Also, if you choose debug, does it stop on a specific line? If so, let me know what it is.


  15. Hi Matthew πŸ™‚

    Thank you for your Excel Template! It is fantastic.

    I was thinking how I can create something to do a similar thing today for a database of clients – to assist in identifying contractors within a 100 km radius of a project location. I’m not a coder at all – but I do somehow workout what I need. The biggest thing is I can only run the search on 10 queries. I have a Database of 5000 Contractors. I looked at the code but can’t identify where I need to change the code to query at least 100 as you mention, or more if possible.

  16. Thank you very much for this! It worked like a charm! I am pretty new to VBA and have a quick question. How do I change it to reference multiple starting points for multiple destinations?

    • Hi Javier,

      I’ll try to respond this weekend with a little more information. I’ve done several modifications but they do take an understanding of VBA. It is definitely doable.

Leave a Reply

Your email address will not be published.