{"id":4896,"date":"2016-12-19T06:41:57","date_gmt":"2016-12-19T06:41:57","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/wp\/?p=4896"},"modified":"2022-08-02T21:18:49","modified_gmt":"2022-08-02T21:18:49","slug":"google-maps-distance-matrix-in-excel-vba","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/","title":{"rendered":"Calculate distance between two places in Excel [With Fun]"},"content":{"rendered":"

Dear readers,<\/p>\n

Last week, I had placed an article on how to create a static google map in excel using Google Static Map API<\/a>. Over this weekend, I thought of creating some fun for you in Excel using Google Maps Distance Matrix API<\/a>. This article is going to be a learning with fun or you can say download some fun in excel with some learning.<\/p>\n

\"Distance<\/a>

Distance between two places<\/p><\/div>\n

How above excel workbook is made?<\/h3>\n

Above excel has the following two main parts:
\n1. How to calculate Distance and time between two places using Google Maps API
\n2. How to make a shape\/picture move on excel<\/p>\n

Calculate distance and duration between two places<\/h2>\n

Using Google Distance Matrix API, you can calculate the distance between two places. By Google Maps Matrix API<\/a>, you can also calculate travel time or duration between two places. As you know Distance and duration between two places might differ for different types of transport modes like Bicycling, Car, Walking, etc., Google API provides this feature to pass your mode of transport as an input parameter in the API.<\/p>\n

Google Maps Distance Matrix API uses a URL which should be in this format: (copied from the above google documentation page)<\/p>\n

https:\/\/maps.googleapis.com\/maps\/api\/distancematrix\/parameters<\/i><\/strong><\/p><\/blockquote>\n

Parameters<\/h3>\n

There are many possibilities of parameters which can be provided in order to generate your map.. again more details you can find it on above page<\/p>\n

You can read more about this API by going through the Google API documentation page.<\/a><\/p>\n

Following is the function which will return distance and duration between two places.<\/p>\n

\r\nFunction getDistanceAndTimeBetweenTwoPlaces() As Variant\r\n' This function will return an array holding\r\n' distance in meters and duration in seconds\r\n    Dim googleAPIRequest As XMLHTTP60\r\n    Dim domDoc As DOMDocument60\r\n    Dim xmlNodesList As IXMLDOMNodeList\r\n    \r\n    Dim response(1) As Variant 'array to hold distance &amp; duration\r\n    \r\n' API URL is formed in excel sheet config using excel formula.\r\n' refer excel workbook for the API URL format\r\n    urlForDistance = Range("urlForDistance").Value\r\n    \r\n    Set googleAPIRequest = New XMLHTTP60\r\n' invoke the API to get the Distance Matrxi in XML format\r\n    googleAPIRequest.Open "GET", urlForDistance, False\r\n    googleAPIRequest.Send\r\n    \r\n' Get the response XML\r\n    Set domDoc = New DOMDocument60\r\n    domDoc.LoadXML googleAPIRequest.ResponseText\r\n    \r\n' Using xPath get the distance\r\n    Set xmlNodesList = domDoc.SelectNodes("\/\/distance[1]\/*")\r\n    response(0) = xmlNodesList(0).Text\r\n    \r\n' Using xPath get the duration\r\n    Set xmlNodesList = domDoc.SelectNodes("\/\/duration[1]\/*")\r\n    response(1) = xmlNodesList(0).Text\r\n    \r\n' Return response with distance and duration in array\r\n    getDistanceAndTimeBetweenTwoPlaces = response\r\n    \r\n' release memory\r\n    \r\n    Set xmlNodesList = Nothing\r\n    Set domDoc = Nothing\r\n    Set googleAPIRequest = Nothing\r\nEnd Function\r\n<\/code><\/pre>\n
Using the above code, I got the distance and duration between two places.<\/p>\n
Fun Part: Animation – Moving vehicle<\/h2>\n
\"moving<\/a>
moving car – in Excel<\/p><\/div>\n
This is made in two parts:<\/p>\n
1. Change Image based on selected value in drop down<\/h3>\n
I will explain in detail in my next article.<\/p>\n
2. Move vehicle or image from Left to Right<\/h3>\n
It is simple. All you need to do is get a fix increment value and start incrementing the “Left” amount for that shape. This way it seems that object is moving from Left to right.
\nTo understand more, you can go through the comments line for each of the statement in below code.<\/p>\n
\r\nSub StartVehicleFromSourceToDestination()\r\n    \r\n    Dim distanceAndDuration As Variant\r\n    Dim distance As Long\r\n    Dim Duration As Long\r\n    \r\n    Application.ScreenUpdating = True\r\n'get the distance and duration from the above function\r\n    distanceAndDuration = getDistanceAndTimeBetweenTwoPlaces\r\n    distance = distanceAndDuration(0)\r\n    Duration = distanceAndDuration(1) \/ 60\r\n    \r\n' divide distance and duration with an equal interval\r\n' for a smooth moving shape (vehicle)\r\n    iduration = Duration \/ 160\r\n    idistance = distance \/ 160\r\n    \r\n' reset distance, duration, starting place of shape (vehicle)\r\n    resetData\r\n    \r\n' Now loop through 1 to total no of intervals\r\n' 160 - same no as duration and distance are\r\n' divided by\r\n    For i = 1 To 160\r\n        \r\n        With ActiveSheet\r\n' increment left with a fixed number in order to show vehicle\r\n' moving from Left to right direction with same speed.\r\n            .Shapes("truck").IncrementLeft 2.18\r\n            \r\n' keep increasing the distance and duration with the same intervals\r\n' calculated above to give an animated calculation effect.\r\n            .Range("distance").Value = Range("distance").Value + idistance\r\n            .Range("duration") = Range("duration") + iduration\r\n' most importantly !! do not forget to put below\r\n' statement. this will keep refreshing the screen\r\n' and hence you would be able to see the vehicle\r\n' moving with a constant speed\r\n            DoEvents\r\n        End With\r\n    Next\r\nEnd Sub\r\n<\/code><\/pre>\n
Now you know how to calculate distance and time taken (duration) in excel by using Google distance matrix API<\/p>\n
DO NOT Forget to download<\/strong> this fun excel and play around with it.<\/p><\/blockquote>\n
Download<\/h2>\n
Before downloading this version – I would recommend you to read this revised article.<\/a><\/span><\/p>\n
Download this, use it and do not forget to provide me your feedback by typing your comment here or sending en email or you can twit me <\/a> You can also share it with your friends, colleagues or whomsoever you want to!!
\n
\n\"Download
\n<\/a><\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"
Dear readers, Last week, I had placed an article on how to create a static google map in excel using Google Static Map API. Over this weekend, I thought of creating some fun for you in Excel using Google Maps Distance Matrix API. This article is going to be a learning with fun or you […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1246,1675,1678,1682],"tags":[],"yoast_head":"\nExcel VBA : Distance and duration between two places<\/title>\n<meta name=\"description\" content=\"Excel macro to calculate distance between two places. How to use Google Maps distance matrix API in excel VBA. Download FREE Excel - Distance Calculator\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Calculate distance between two places in Excel [With Fun]\" \/>\n<meta property=\"og:description\" content=\"Excel macro to calculate distance between two places. How to use Google Maps distance matrix API in excel VBA. Download FREE Excel - Distance Calculator\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2016-12-19T06:41:57+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-08-02T21:18:49+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2017\/01\/Distance.gif\" \/>\n\t<meta property=\"og:image:width\" content=\"920\" \/>\n\t<meta property=\"og:image:height\" content=\"466\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/gif\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Calculate distance between two places in Excel [With Fun]\",\"datePublished\":\"2016-12-19T06:41:57+00:00\",\"dateModified\":\"2022-08-02T21:18:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/\"},\"wordCount\":485,\"commentCount\":12,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Macro\",\"Excel Macro Tutorial\",\"Interesting VBA Functions\",\"Popular Articles\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/\",\"name\":\"Excel VBA : Distance and duration between two places\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2016-12-19T06:41:57+00:00\",\"dateModified\":\"2022-08-02T21:18:49+00:00\",\"description\":\"Excel macro to calculate distance between two places. How to use Google Maps distance matrix API in excel VBA. Download FREE Excel - Distance Calculator\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Macro\",\"item\":\"https:\/\/vmlogger.com\/excel\/macro\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Calculate distance between two places in Excel [With Fun]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Excel VBA : Distance and duration between two places","description":"Excel macro to calculate distance between two places. How to use Google Maps distance matrix API in excel VBA. Download FREE Excel - Distance Calculator","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/","og_locale":"en_US","og_type":"article","og_title":"Calculate distance between two places in Excel [With Fun]","og_description":"Excel macro to calculate distance between two places. How to use Google Maps distance matrix API in excel VBA. Download FREE Excel - Distance Calculator","og_url":"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2016-12-19T06:41:57+00:00","article_modified_time":"2022-08-02T21:18:49+00:00","og_image":[{"width":920,"height":466,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2017\/01\/Distance.gif","type":"image\/gif"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Calculate distance between two places in Excel [With Fun]","datePublished":"2016-12-19T06:41:57+00:00","dateModified":"2022-08-02T21:18:49+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/"},"wordCount":485,"commentCount":12,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Macro","Excel Macro Tutorial","Interesting VBA Functions","Popular Articles"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/","url":"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/","name":"Excel VBA : Distance and duration between two places","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2016-12-19T06:41:57+00:00","dateModified":"2022-08-02T21:18:49+00:00","description":"Excel macro to calculate distance between two places. How to use Google Maps distance matrix API in excel VBA. Download FREE Excel - Distance Calculator","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2016\/12\/google-maps-distance-matrix-in-excel-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Macro","item":"https:\/\/vmlogger.com\/excel\/macro\/"},{"@type":"ListItem","position":3,"name":"Calculate distance between two places in Excel [With Fun]"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/4896"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=4896"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/4896\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=4896"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=4896"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=4896"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}