How to calculate Driving Distance Matrix on Excel using Bing Maps API

This is a small tutorial video on how to generate a distance matrix on excel using Bing Maps API. Everything is explained in detail. This video is extremely useful for a Supply chain professional who is trying to perform Vehicle routing / Network analysis / Logistic cost analysis / Location Planning / Logistics Planning. It provides the road distance along the best routes considering traffic.

This may not work in Mac OS, due to limitations of Apple Mac.

If you need Travel time aswell, post this below code in your vba window and use GetTravelTime formula in the same format as GetDistance as explained in the video.
output will be travel time in minutes

If you are getting an error, it means the formula is not giving you any relevant information.
Here are the general reasons which I know result in such errors. (in the order of the likelihood)

1. If you are using a macbook. The Excel in macbook does not have a web service function, which is essential for Excel to talk to the internet.
3. If you are sure 1&2 are not the reasons, There could be an error in the source coordinates and destination coordinates or the Bing key that is inserted into the GetDistance formula. Request you to kindly revisit the video and verify.
4. You need to save the file in .xlsm format.

About me:
I am a student at NITIE Mumbai, batch of 2019-2021. NITIE (National Institute of Industrial Engineering) is a leading academic institution in generating world class supply chain and operations professionals. It is also popularly known as "The Mecca of Supply chain".

#bingmaps #distancematrix #excel

To Connect with me:

[lucastavares]

You have help me a lot ! I’m making a cost sheet, using Kilometers from every city from state of São Paulo

[SensekariYogi]

Very structured & elaborate explanation. Must watch for all aspiring learners.

[leroyjenkins]

Excellent hemant, just one observation though - Since api keys have limits, every call is precious. Hence filling only the upper half of the diagonal would have been wise. Also from efficient programming perspective it is important

[ssaravananp]

Dear Mr.Hemanth, It was a useful App. We have around 25 rows and columns in our Application. The macro works correctly upto 12 rows and columns. From 13th row and column, there is a difference of at the least 2 Kms. Please guide us.
Regards,

S.Saravanan

[sudheerkumar]

Good work Hemanth Boddapu... expecting more videos from you..

[Michael-zgcx]

Hey
Great work! I used your code for two years now. But with the change to Azure Maps I don't get this think working anymore... can you make a new video where you explain how to use Azure Maps API instead of Bing Maps?

[srinivasannarayanaswami]

Thank you Hemanth. Lucid explanation and a very useful tool. Much appreciated

[blue]

THANK YOU! You did miss that input for Source and Destination coordinates. Also you could have clarified the best way to get the coordinates in the database tab. OTHERWISE, this is an excellent video and I really appreciate it!

[fulyaulutas]

This is exactly what i needed. Thank you for explaining very clearly

[himanshushukla]

Thank you Hemanth for such insightful videos

[Gabriel-Oliveira]

Hi Mr. Boddapu. Firstly, I really appreciate the time you spent recording this video. It was really important for my understanding when it comes to create a distance matrix.

I have a question, if I have 12 locations, I think the total number os distances would be 144 (12*12) instead of 66, am I right?

Cheers from Brazil!

[alexanderbarlse]

Hi there! Thank you for this great piece of code, it helped me a lot. However, i'm having a small issue. The code was running perfectly well yesterday. However, men reopening the xlsm file today, and trying to use the getdistance function, I get the error: #NAME?. Do you have any suggestions?
Thank you.

[karstenhaberkorn]

Hello Hemanth, this is really very helpful and a great tool - thank you for that! Can you also advise, on how to get coordinates (latitude and longitude) back from bing maps for given locations? This would be very helpful, in case you need coordinates for many addresses (comparable to the list of locations you put into the "database sheet"). Many thanks for any advice! Karsten

[aphastus]

Wow I tried not knowing if it’d work because I’ve never coded in VBA and I did a couple mistakes that I was able to detect and understand watching my code and yours (mostly cells placements). I used the time instead of the distance. It is incredibly satisfying watching the code fill all the cells in the matrix. I started in Mac but the getdistance formula didn’t work. I’ll try it later to see if something wasn’t correct. What bugged me is that every version of office uses different separators, you used a, and I had to change it to a ; so probably it’s the same problem with Mac. Anyway, thanks so much for the video, it was so nice!

[vidaan]

Hi Hemanth, this is a wonderful video. It really saved a huge amount of time for me.

[nicolesamson]

This works perfectly, Thank you so much Hemanth. When im working on a larger data set it takes quite a lot of time for it to fetch the distance, is there any work around for that?
TIA.

[spvgggwdeg]

This is amazing and very, very helpful for me. Thanks a lot for the well elaborated explanation.

[widhyawanjuliaris]

thank you very much for the knowledge. you've helped me in many ways.

[ranjitsakhare]

Hemant thanks a lot. Its helping a lot. Like function GetDistance can you help with GetTime as well?

[prafullgoyal]

Awesome...I need to hit like button for tonnes but have to satisfy with 1 only..✌