Excel VBA Google Distance Matrix API and Google Static Map Image API

Posted on by

This is a demo of a recent client project. Based on my Google Distance Calculator example (go here to view and download). Below is a video demo and downloadable example of the project.

Technologies Used:

Excel VBA, Google Maps Distance Matrix API, Google Maps Static Map API.

My client needed a list of addresses to generate a matrix that included both the distance and travel time for each address combination.

For each address listed, a column was created, and using the column heading address as the origin, destinations were calculated for each of the remaining addresses.

Conditional formatting was included on a 3 color scale gradient. The colors used can be modified by my client by selecting a background color for cells in a "System" sheet.  Additionally, a departure time can also be added.

Finally, from any of the resulting matrix columns, a form allows my client to select one or more destination addresses and generate a Google map image with markers for the origin and selected destination addresses.

Note: This is a demo. As part of a client project, I am not including the source code. If you have questions about my methodology, please contact me.

Video Demo

File Download

Download

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

6 Comments

  1. I need more locations start – address list page. Can you extend for 15 adress. Macro can do that only 5 locations.

    • You can modify the code to loop through multiple start and multiple destinations. I created a grid solution for a client that does something very similar.

      Given my current projects, it is not likely that I will update this in the near future but all the code is in the downloadable file.

      Thanks.

  2. Hi,

    Thanks for the excel file! I did play around with the tool however it seems that you can only use 5 addresses at the moment and there is no way to edit the script without a password. Are you able to share this password with us in order for us to increase the limit further?

    • Hi Alaina,

      I need to go back and find my original project. I’ve upgraded systems since posting this but believe I have it saved on my backup drive. I’ll send you an email directly when I find it.

      Thanks.

Leave a Reply

Your email address will not be published.