Excel Google Maps Distance and Travel Time Calculator with Directions API

You can use the Google Maps Directions API to calculate travel time and distance using a number of travel modes. You can used VBA-JSON to create an Excel function that provides this data in Microsoft Excel! In this video, we create both travel time and travel distance functions in Excel VBA.

Links:

[Androo]

This tutorial was very helpful. I had a 1000+ addresses I had to evaluate for a school project, and as much of a learning curve as this was, it was still infinitely easier than trying to do it manually. As others have suggested, changing Dim Seconds and Dim meters from "Integer" to "Long" solved the problem of longer distances not calculating. Thank you so much for the guide!

[miked]

2 important points: 1 the difference you are getting in the applications verses the website appear to be driven by time of day. Try on web saying you are leaving at 3AM. 2 - I was doing a larger distance, and kept having an error. The Dim for meters needs to be long as my result was more than 64K.

Oh and thank you VERY much this was a fun Sunday learning, I really appreciated it.

[matthewwallace]

This works like a charm. I wanted to suggest changing "Integer" to "Long" as returning meters and seconds may end up exceeding the character limit for Integer. Alternatively you can write a conversion in the VBA.

Additionally, this seems to now violate Google's ToS for API as it is considered data scraping.

[Wiznaz]

Excellent video on how to do this! Some tips that helped me complete my worksheet were:
-For TravelDistance showing VALUE, remove "Dim meters As Integer" entirely
-Replace Integer with Long if you have far distances
-To convert seconds to an hour/minute format, add to the end of your TRAVELTIME code /86400 and then change the cells from General to Time. For example, this should make a route go from 9000 seconds to "2:30" (2hr, 30min)

[MojtabaGhanidel]

THIS WAS Excellent job! Thank you. I changed the ("duration")("value") to ("duration")("text") and it gives me distance in Miles!

[AnnaJacksonbanana]

This was super! I was able to create a sheet that tells me how much drive time I need to allot in order to get to my next facepainting gig on time! YOU ARE AWESOME!!! thank you

[rodrigoruiz]

Hi, I did it the exact way as the video, but when I use the formula It gives me a #VALUE! for the time and distance, I also wanted to know if this works for coordinates (I also tried using the same direction as the video but I have the same result)

[anditote]

Hi, I am getting a compile error - User-defined type not defined. And the text "parsed as dictionary is getting highlighted. Can you please help?

[kats]

This was SO helpful! How would you modify the code to display only the shortest travel distance? I also need to figure out a way to display the associated map for each record. Appreciate the guidance.

[GeoffWieczynski]

Thanks a lot. I got it working in an hour. Saving me some real time. Well done.

[AliPaul-pbci]

Thanks alot mate, really helped me in simplifying one of my analysis containing 22K rows of data, which I had to otherwise do manually

[primafitra]

Hi, i tried your code and get #value when tried the traveldistance and traveltime. I think its because the json format is different now, when i tried to open the link in browser there is no "leg" in there. can you help?

[thefurniturepeople]

hi there want to say thank you very much for this video. it was tremendously helpful for what i am trying to do. I will also tell you that i know not much about coding and vba etc but i just followed us instructions and got the result i needed. thanks for such a detailed video, thanks for your time, and thanks to the people below for commenting on some of the issues they had, it also helped me fix some issues.

[mgification]

Thank you so much for this incredible helpful video!

[michaelthgersen]

Hi again. I'm not getting the ferry thing to work. Do you have any advice on altering the code to work? I've tried this without any results:

Dim parsed As Dictionary
Set parsed =
Dim ferry As Long

Dim leg As Dictionary

For Each leg In
ferry = ferry + leg("maneuver")("text")

ferry = ferry
Next Item

GETFERRY = ferry

[ernestina]

Thank you for the explanation! Super clear and useful!

[toprelaxingtime]

It is like a magic ... Super usefull & Super thanks !!!

[heathercrawford]

Good day, Thank you for your video. I have general computer skills and was able to follow the steps and get it working. I was wondering if there was a way to modify the code so that it pulls the public transportation time and distance instead of driving for a project I am working on?

[juanpirueta]

Hey quick question, do I have to pay for it to keep working? I am currently trying to implement the macro, however on the Google Maps Developer page a lot of subscriptions start popping up, and it says I'm on a free trial. Is this on the "free trial" limit?

[bbghazal]

This is awesome! Can you advise if I can add the "Depart at" value which includes the time and date? I need to know for example how long will it take me from PointA to PointB if I left PointA on March 15th 2022 at 3pm