This post is a nifty tool for tracking the distance travelled by building materials from source to site. I’ve been writing a few bits of code lately which have required using Google tools, so I thought I’d write up some notes here. This is for my information than anything else but may come in handy for others too. Used in conjunction with the carbon factors per tonne km from Defra, this allows a decent estimate for the transport stage of a lifecycle carbon footprint assessment.
The custom function for Excel works by entering two place names – or postcodes – and finding the shortest route between them using Gmaps route planner. I’ve called the UDF G_DISTANCE and the syntax for using it is =G_DISTANCE(Origin, Destination). It returns a value of the distance between the two locations in miles kilometres. If any kind of error occurs then the function returns 0.

Using the function

This table shows the various ways you can use the function. You can also mix and match the types of input parameter you use. For example you may the site location precisely – either by latitude and longitude or postcode, but only know the city or even the country of origin of the materials. That’s fine – the function should still give you the right answer, or at worst return zero.

Two note of warning:
1) The Gmaps terms of service state that you are only allowed to use the API when displaying a map, so using this without doing so is at your own risk (hence the map above…).
2) Gmaps will generally give you a road-based route which may not be the most appropriate. For example cork from Portugal would probably be shipped rather than delivered all the way by HGV. If anyone has a better function for these cases please get in touch.

The code

As ever, you need to install this as a code module in the workbook you want to use it in. It also requires that you set a reference to Microsoft XML, v6.0 (Tools > References > Microsoft XML, v6.0 ).

Jamie Bull | jamiebull1@gmail.com

Related Posts

WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]

Just a quick post to point out a couple of really useful tools.The first is a web-based tool for finding weather files for a location of interest. It’s similar to the Excel EPW finder tool we created a few years back, but much more modern looking. It is however missing a few of the useful […]

Eppy is a really useful library which I’ve written about several times, since before I really had anything to offer in terms of contributing code. Over the past year or so though, I’ve started to contribute back some of the changes and additions I’ve made while using eppy on academic and commercial projects.This post is […]

237 Comments on “Google Maps distance function for Excel”

  • Nickos Dimoglou says:

    Hi Jamie,
    thanks for sharing.
    any chance you could send me an excel with this macro working :))
    Thx in advance

  • Nickos Dimoglou says:

    Thx for hepling first of all
    The link however doesn’t seem to work . I get “Internet Explorer cannot display the webpage” or “The address was not found…”

  • Nickos Dimoglou says:

    not the link you have just sent but the link in page
    http://oco-carbon.com/2012/03/29/google-maps-and-excel-download/

    thx

  • Nickos Dimoglou says:

    Hi again,
    It works fine now. Really cool 🙂
    Have you got any similar excel for distance calculation which is what I am basically looking for?
    I am trying using the code you have provided above… but seems my programming is really limited…
    Thx

  • Gabriel says:

    Good Afternoon!
    Your code is helping me a lot!
    I’m sorry for the english using google translator

    Would like to know how to check the distances Rapidas fastest route

    Thank you and congratulations

    [Edited to improve on Google’s translation.]

    • Jamie Bull says:

      This post has a Gmaps journey duration function for Excel written in answer to this comment.

      • Liam says:

        Hi that function looks great!

        However i am unable to get it working on my machine. Excel comes up with error “Compile Error: user defined type not defined”….is this something to do with
        the 2nd line of your code ‘ Requires a reference to Microsoft XML, v6.0….I’m not sure i have done this?

        I am looking for something that will enable me to write origin postcode in column A and destination postcodes in column B. Then have the distance or travel time in column C

        Many thanks

        Liam

  • Adam says:

    Great function! How would I adjust this function to display distance in miles, rather than kilometers?

    • Jamie Bull says:

      Thanks for that question. It made me notice a mistake in the post where I said it returns the result in miles where I should have said it returns in kilometres.

      To make it give you a result in miles you need to divide the result by 1.61, either in the cell where you’re doing the calculation or in the VBA code itself. To do it in the code, change the last line before the exitRoute section from “If Not distanceNode Is Nothing Then G_DISTANCE = distanceNode.Text / 1000” to “If Not distanceNode Is Nothing Then G_DISTANCE = distanceNode.Text / 1000 / 1.61”

      Hope that helps.

      • Kirill says:

        There is an original Google parameter for unit system

        units=metric (default) returns distances in kilometers and meters.
        units=imperial returns distances in miles and feet.

        I tried to add it to your code, but it didn’t work. I am not an expert in VB, so I think it was my mistake:

        myRequest.Open “GET”, “http://maps.googleapis.com/maps/api/directions/xml?origin=” _
        & Origin & “&destination=” & Destination & “&units=imperial&sensor=false”, False

        It works fine in a browser, but in Excel it still brings kilometers only. For now I’ll use your simple remedy by dividing to 1.61, but it doesn’t seem right.

        Anyway, thanks a lot for sharing your wisdom – it is very helpful!

      • Tony says:

        Hi Jamie,

        I’m using your example workbook and there seems to be a problem somehow. I modified the cells as you described to report miles instead of kilometers and the distances are quite a bit off still. For instance, if I plug two addresses into Google Maps, it comes up with 43.5 miles distance from one to the other. For the same two addresses, yours comes up with 33… I can understand a small change when using an alternate route but, 10.5 miles isn’t likely. All of the addresses I used are coming up with the same discrepancy. Is the data returned on yours “as the crow flies” or should it be the same as Google Maps results?

  • Gabriel Lawrence says:

    Hi Jamie,

    Thanks for this tutorial, great feature.

    I have filled out a long list of from and to addresses, it works fine and pops up with the KM distance. However, if I save the list and go back into it, the distance cells do not update to the correct amount, it just stays 0. Is there a way to have the whole sheet refresh? Currently, I have to press F2 and enter repeatedly throughout all the distance cells to get them to update.

    Thanks,

    Gabe

  • JY says:

    Hi, i have been using this method to get the distance between location from Google as well. My target is to have a matrix that display the distance between 11 cities. This will give excel 121 distance to extract from google in total.

    everything goes well except that whenever i refresh the data, i always have cells that appear as 0 instead of the distance. i have to press F2 individually on these cells before the correct distance appear. Do you know why? Thanks!

    • Jamie Bull says:

      It could be a few things. The function returns 0 if any error occurs, which could be if the location doesn’t exist, Google can’t find a route, you’re over the limit of queries for the day, etc. But the most likely reason is that you’re querying too fast. There are two ways to fix this. The easy way is to put a delay in between calls using something like this Sleep() function. Put the line Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) at the top of your module, add in the line Sleep(200) somewhere in the G_DISTANCE function and the code will pause for 0.2 seconds between queries, enough to stop Google thinking you’re a robot.

      The alternative is to cache the results somewhere as a temp file but that’s a bit more involved.

      • Eli says:

        Hi Jamie, thanks for the post, works perfectly.
        I think i need to use the pause function to get an accurate result. would you be able to post the whole vba with the pause function in it (i’m a reall beginner and have no clue what to put where).

        thnx

        • Jamie Bull says:

          You need the Sleep function if I understand correctly. Description of how to use it here. You need to add it so it pauses once per loop, and then tune the value until you stop getting the OVER_QUERY_LIMIT response from Google.

  • JY says:

    I think that solves the problem! Thank you!

    • JY says:

      Hi, I got another query. Should i want excel to only extract and display the data from google maps only after I press a button, How should I do it? I do not want it to automatically refresh itself everytime. It is taking too much time.

      • Jamie Bull says:

        Either set Excel’s calculation mode to manual then use f9 when you want to recalculate, or alternatively wrap the G_DISTANCE() function up in a macro that writes to your output cells rather than using it as an in-cell function. You could also take a look at caching the results. It’s much faster than querying Google after the first time.

  • Bart says:

    Wonderfull!

    I guess I can use the same code for the time to travel (in stead of the kilometers).
    How can I get this info ?

  • Chris says:

    Hey, Ive been using this script successfully – however – is it possible to get it to return the shortest distance from point A to B rather than the quickest?

    • Jamie Bull says:

      You are limited to what Google provides so there’s no way to specifically request shortest distance. You just get the response from Google “best” route algorithm. It’s a bit of a hack but you could ask for the walking directions rather than driving which is generally shorter but may not actually be a driveable route.

  • Thomas says:

    Hey !

    I came up with pretty much the same code, though I like yours better. However… It worked fine yesterday but I did not save the data it returned. I queried above a thousand distances (postal code to postal code).

    When I started it again this morning, it will only send back 0’s. Which I assume is because I reached the limit of queries for a day. Hopefully, it’s a limit per 24h and I’ll be able to run it again tomorrow or later today. And I already included a pause of 1 sec between each request, so… It’s pretty frustrating. Just wanted to let everybody know it might happen if you query too many distances at once…

    • Jamie Bull says:

      One suggestion that I’ve used in a pinch is to hide behind a VPN. In my case it was as easy as logging on to a client’s VPN but you could use something like Hotspot Shield – or even a friendly neighbour’s WiFi. The important thing is to present a different IP.

      For a better solution I’d recommend caching results. Have a look at some of the other posts on this site for instructions on doing that.

      • Thomas says:

        Not too sure what you mean by caching the results ? I assume it will only help getting the results faster ?…

        • Jamie Bull says:

          I mean saving the XML file to your temporary files as something like Environ("temp") & "\" & Origin & "_" & Destination & "_dist.xml". Then before you query Google, check if you already have that file in the cache. In part it helps with speed of results, but it also means that you aren’t adding to your total number of queries by making the same request over and over.

          • Thomas says:

            I had no idea you could do that !… Thanks for the tip.

          • Xander says:

            Your script works great, however I got about 1000 postal codes. Using the pausing script it takes ages for the document to load again so I’m searching for a caching script of some sort. I’m really not into vba script so I haven’t got a clue where to start. Could you write something if it’s not too much trouble?

  • Thomas says:

    FYI, it’s working again. I guess there must be a limit per 24h.

  • Jorge says:

    Hi Jamie, I’m trying to measure between 2 waypoints

    S25 04 42.9 W57 16 17.0
    S25 05 12.6 W57 17 20.4

    the excel aplication returns 2,387 meters, but in google maps gives an exact 2 Kms

    Please help me solve this issue…

    Thanks in advance.

    • Jamie Bull says:

      I’m not entirely sure. It seems to be some sort of rounding error. Are you sure your starting points are exactly on the road? All I can think of is that the two methods may use a slightly different algorithm in deciding where to start the journey from if the start or end point is not exactly on a road. One thing I noticed while looking in to this is that the Google location parameters only got to 5 places after the decimal point. Perhaps that’s introducing the rounding error. Your best bet is probably to ask Google though. I just provide a link to their API and don’t have any insight into what goes on at their end.

      A few figures to check on:

      Your start point
      S25 04 42.9, W57 16 17.0
      Start point when entered in Google Maps
      S25 04 43.1, W57 16 16.7
      Start point via G_DISTANCE
      S25 04 37.7, W57 16 5.7

      Your end point
      S25 05 12.6, W57 17 20.4
      End point when entered in Google Maps
      S25 05 12.8, W57 17 20.2
      End point via G_DISTANCE
      S25 05 13.4, W57 17 21.3

  • Jorge says:

    Jamie, thanks for your prompt answer. I already open a issue in

    https://developers.google.com/maps/documentation/webservices/forum?hl=es.

    But they say they will check it before open it for discussions.

    Regarding the waypoints. Yes, they starts and ends on the road.

    I don’t quite understand the “A few figures to check on”. Are you refering at the rounding issue???.

    I’ve try them all and they all gave the same result… 2.387…

    Jorge.

    • Jamie Bull says:

      Those figures are what your waypoint addresses come back as (after converting from the rounded decimal latitude/longitude) from either entering directly onto Google Maps, or when it’s entered into the Google Directions API. The fact that the Directions figure is not the same as the other two makes me think that Google is rounding off the precision when you use the API. I’ll be interested to see the response to your issue.

  • Jorge says:

    Jamie, once again, thanks for your prompt answer!. I think I know what is going on, and I hope you help me correct it.

    If you put these values in the G_LATLNG,

    WP1 S25 07 25.9 W57 20 22.1
    WP2 S25 07 02.4 W57 19 15.1

    returns this:

    WP1 -25.1238745,-57.3396070
    WP2 -25.1162286,-57.3175897

    If you put this in maps.google.com, it returns the same distance as the Excel_Gmaps .

    • Jamie Bull says:

      This is a link to the raw XML and you can see the latitude and longitude are rounded in there. It’s definitely a problem at Google’s end to do with the level of precision of their results via the API.

  • Jorge says:

    Jamie, I’ll wait for the forum response!.

    Thanks

    Jorge

  • Jorge says:

    as I told you before… when y open the issue i got a this message:

    ” they will check it before open it for discussions”.

    I just opened another one in the geocoding api forum… With the following text

    Hi, when I try to calculate distance between 2 GPS waipoints the geodecoding api translates this coordinates

    WP1: S25 07 25.9 W57 20 22.1 to -25.1238745,-57.3396070
    WP2: S25 07 02.4 W57 19 15.1 to -25.1162286,-57.3175897

    using this coordinates in the distance api brings a distance of 2374 mts.

    the right result should be

    WP1: S25 07 25.9 W57 20 22.1 to -25.123861, 57.339472
    WP2: S25 07 02.4 W57 19 15.1 to -25.117333, 57.320861

    • Jamie Bull says:

      I see. Well it might be worth asking on Stack Overflow too if no luck on those Google forums. There’s as good a chance of a response on there as anywhere.

  • Jorge says:

    and the distance is 2010 mts.

  • Jorge says:

    Ok… I will open it there too…

    Thanks!

  • Jas says:

    This is a great help! I only have one issue. When I am attempting to use the G_Distance formula, it does not work to find the distance from the address I input to a list of ZIP codes that begin with the number “0”. Is there a way to make it work? All other ZIP distances are located from the address I input if they do not begin with a “0”. Help?

    • Jamie Bull says:

      From a little bit of testing it seems to work if you force the ZIP code to be read as text. To do that in Excel you just enter a single quote before the number. For example if you enter 01002 (Amhurst, MA) in a cell then normally Excel will see that as a number and convert it to just 1002. If you enter it as ‘01002 then Excel displays it as 01002 and G_DISTANCE reads it just fine.

  • Fernando Soares says:

    Hey! Congratulations for your job, it is amazing! How can I see the map with the route instead of just the city map when I’m working with Excel_GMaps?

    Best Regards,

    Fernando

  • Vic says:

    Hello Jamie!

    I need to create a simple formula in Excel that returns the distance between 2 Zip Codes:

    Column 1 Zip Code 1
    Column 2 Zip Code 2
    Column 3 Distance (Km or miles)

    I think this coul be possible but I dont know how, I dont have Long and Lat data and I dont have an Addresses… only Zip Codes!

    Can you help me with this?

    • Jamie Bull says:

      Hi Vic. The G_DISTANCE() function should work with zip codes. If it doesn’t you could try making sure it gets read as a location by appending “USA”, so instead of passing in say “90210”, have “90210, USA”.

  • JL says:

    Great to share this! Thanks!

  • Hamish Brown says:

    Modified your code with a quick hack that made it find the distance between two postcodes in Australia (just switch “UK” with “Australia” to make it work in the UK). Thanks for providing the code as, with the modifications below, it has saved me a lot of time at work!

    Function G_DISTANCE(Origin As Long, Destination As Long) As Double
    ‘ Requires a reference to Microsoft XML, v6.0
    ‘ Draws on the stackoverflow answer at bit.ly/parseXML
    Dim myRequest As XMLHTTP60
    Dim myDomDoc As DOMDocument60
    Dim distanceNode As IXMLDOMNode
    G_DISTANCE = 0
    On Error GoTo exitRoute
    ‘ Read the XML data from the Google Maps API
    Set myRequest = New XMLHTTP60
    myRequest.Open “GET”, “http://maps.googleapis.com/maps/api/directions/xml?origin=VIC,%20” _
    & Origin & “%20Australia&destination=VIC,%20” & Destination & “%20Australia&sensor=false”, False
    myRequest.send
    ‘ Make the XML readable usign XPath
    Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText
    ‘ Get the distance node value
    Set distanceNode = myDomDoc.SelectSingleNode(“//leg/distance/value”)
    If Not distanceNode Is Nothing Then G_DISTANCE = distanceNode.Text / 1000
    exitRoute:
    ‘ Tidy up
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
    End Function

    • Jamie Bull says:

      Great to see that this is coming in so handy for people. I’m surprised it doesn’t work for Australian postcodes anyway though. It should do, and if not you can pass in the extra argument in your formula if you use "=G_DISTANCE(A1&", Australia",B1&", Australia") – assuming your origin and destination are in A1 and B1 respectively.

      By the way, if you’re using this a lot it might be worth checking out the updated download in this thread which has improved code including caching of results to avoid going over Google’s daily limit.

  • Brandon M. says:

    Jamie – your tools have been more than helpful with some of the projects I work on to make operations more efficient. I am curious though – when using this particular tool how do I display the route map that you have shown above?

  • Ethan says:

    Hello Jamie,

    Just wanted to say what a great tool this is and to ask a few questions.

    I downloaded the Excel Workbook that contains the Sheets G_LATLNG, G_DISTANCE etc.

    I am interested specifically in the G_DISTANCE Sheet, I had wondered if in some way
    it was possible to export this somehow into Microsoft Access, in a way that if I entered
    one postcode into one cell and another postcode into another cell it would return the distance
    between them (as it does in the Spreadsheet).

    Or if this isn’t possible, whether you know of such a code module for Access that will allow me to do this.

    Thanks a lot.

    • Jamie Bull says:

      Hi Ethan,

      I’m not much of an Access person but you may still be in luck. A post came up on my RSS feeds a month or so ago and sounds like it might be exactly what you need for distance in Access – and a different implementation for Excel too. It’s not actual road distances though, just Great Circle so perhaps not quite what you need. If you’re looking for actual road distances you can link to an Excel workbook from Access but that’s not something I’ve done before.

      Hope this helps a bit at least,

      Jamie

  • Владислав says:

    Good day, I am infinitely grateful to you for an indispensable tool.

    I have studied all the restrictions with regards to the number of Google queries, namely 2,500 requests per day for one client, and 100 000 000 for organizations.
    Distance matrix, which I process consists of more than one million hits, and I understand that the solution to my problem need to use multiple options or use a dynamic IP, or connecting to another service in which there is no restriction.
    Maybe you have to adapt their tools for such services?

    I produce only a measurement of the distance between the geographical coordinates of points, but I have them, as you know, very much.

    Thanks in advance for your help.

    P.S in Russia prefer to use all yandex me, many said that it is free, or Visicom, without limitation requests.

    http://api.yandex.ru/maps/doc/jsapi/1.x/dg/concepts/router.xml
    http://api.visicom.ua/docs/tools/console

  • Rowel says:

    Hi Jamie, this is a wonderful tool.

  • Paul Corbett says:

    I need to get the road distance from postcode to postcode for a ton of date (50,000+)

    I have used your G_Distance function, which is great, but with only being able to get 2500 in a 24 hour period, it will take ages.

    My company doesn’t mind paying, is there a corporate licence I could get that would open it up?

    Many thanks

    • Jamie Bull says:

      You can get business licences for about £7,000 from memory but contact them to check. I’ve sent you an email with your options but for anyone else looking here the options are just slogging away at 2,500 per day, getting a business licence, using the distance matrix API which is also rate limited, or spreading the job out over a network, with each IP address doing 2,500 queries.

  • Julio says:

    OMG, I love you Jamie!
    Thank you very much.

  • Angelina says:

    Hi Jamie,

    Thank you so much for putting this up it’s very useful. I was able to use it the first time but when i reopen the file and drag the formula (to apply to other cells) it’s giving me this #NAME? error. Could you please let me know how i can fix this? thanks. -angelina

    • Jamie Bull says:

      Having seen your worksheet in the email you sent, I think the problem may be working with a Mac which doesn’t play nicely with some of the system calls in the macro.

  • Steve Wheeler says:

    Jamie,
    A simple and very useful feature, well described.

    Nice work…

  • Simon says:

    Thank you very much, great macro! Is there anyway to have distance by train instead of driving distance. Thanks again!

    • Jamie Bull says:

      Hi Simon, Not as far as I’m aware, although you can specify public transit generally by specifying mode=transit in the URL, and optionally a time of day. At least in the UK that also includes buses and underground as well as trains though, and it only works within cities and not between them. I had a commenter from Singapore testing out a worksheet back in December but he went quiet on me. I’ll try and get a post finished and out there in the near future.

  • Paco Garcia says:

    Hola
    Existe una funcion que devuelva verdadero o falso, si al dar las coordenadas de un punto nos diga si esta dentro o fuera de un perimetro?
    Algo asi:

    pero en Excel

    Un saludo
    Gracias

  • Doug says:

    Jamie – incredibly useful functions, if I could only get them to work 🙂

    I’m particularly interested in the G_DISTANCE function.

    The problem here is definitely between the chair and the keyboard. I’m new to enabling custom functions in Excel (I’m using 2010 on a PC) and can’t seem to get the workbook I’m working in to recognize the function.

    Your sample spreadsheet works for me, but if I copy the G_DISTANCE tab into a new spreadsheet it doesn’t. I think I’ve copied the function code into the new workbook correctly and set the reference to MSXML 6.0 properly, but I continue to get the #NAME? error.

    Appreciate your help,

    Doug

  • Marcel Espinoza says:

    Great Work man! it’s very useful..! but i need your help regarding this topic, when i’ve used windows 7 the excel worked extremely fine but now i use OS Mac and i need that this macro works with this system, so can you help me to fix the problem please?

    thanks in advance!

    • Jamie Bull says:

      Sorry, Marcel – you’re out of luck. This macro relies on Microsoft XML which means it can’t be used on Mac.

  • Hussain Alzayer says:

    Hi Jamie,

    Thanks a lot for your work. I want just to ask you whether I can que function calls or not? I have 2-way (2D) table and it has many calls for the same function. So some of the values are returned ZERO …. I have been searching for a que mechanism or built-in function in excel with no success.

    Thanks again

  • CHristian says:

    Hi Jamie,

    I’m not an Excel-expert by a longshot, but use it for some basic calculations regarding my music gigs.

    is there an easy way to adapt/simplify your script, to just provide the distance in kilometers between 2 adresses inputted into cells? (must work for different countries as well).

    • Jamie Bull says:

      You can use the function as described in the post. Just add the code into your workbook, add the reference to MS XML, and you’re good to go.

  • Andy says:

    Hi, this function is great! However I seem to face some problems when trying to use this function on multiple cells. I am trying to find the combined distance travelled in a football league over the last year- so I have

    1) Put all the team’s postcodes from a league into a table
    2) Run a lookup whenever two teams play each other
    3) Used G_Distance on the two postcodes

    The distances seem to show correctly for the first round of fixtures, but all the subsequent games have them displayed as 0. The function then works again for the return fixture.

    I’m scratching my head as to why it would do this- any help appreciated!

  • Thomas says:

    Hello Jamie,

    brilliant code. It has proven to be verry usefull in our (little) organisation.

    1 question though.
    Is it possible to get the distance of an alternative route in the code?
    I guess it has to do something with “Set distanceNode = myDomDoc.SelectSingleNode(“//leg/distance/value”)” and putting “&alternatives=true” in the request. I suspected an array but bumped into a wall, not being an excell/vba expert.

    It’s nescesarry for us to get the shortest route between two points, so I figured you could get the distance of each route and set the shortest one as the G_Distance variable.

    Can you give it a try?

    • Tibert says:

      Hi !

      I take the point : I tried “&provideRouteAlternatives=True” but i didn’t succeeded in getting more than 2 nodes.
      Without any google.maps.DirectionsRequest object specification dedicated to identify the shortest or the fastest way, I tried to get all the possible ays in order to find the shortest.

      Does somebody have an Idea ?

      bye.

  • sal says:

    Hi!
    Sorry for my bad english.

    I need a code to calculate the Route distance (not direct distance) fro postcode to postcode. in vb6 classic?
    Possible?
    many tkx.

  • Robert Sharpe says:

    Thanks for a great tool. However, we use Open Office and are not proficient programmers. Does anyone know of someone that has, or wants to, supply a similar Open Office version?

  • Fabio says:

    Jamie, thanks for the code. That’s incredible!
    The code is very simple to use, with a lot of potential for applications. Thansk for help me.
    Fabio (from Brazil)

  • Alexandre says:

    Jamie, your code is just helping me so much. But since I’ve choosed to use the New Google Maps, it seems not work anymore. Do you know something about this? Thank you!

  • Duncan says:

    Thanks for this code. It works great. Is there a way to return the Lat and Long of the Origin and Destination?

    Thanks in advance.

  • Alexandre Adler says:

    Jamie,

    Your function is very helpful however it seems calculate the car route and than provide its distance and I would like to get the walking distance. Do you think it is possible to adapt the function and get the “walking distance”?

    Thanks,
    Alexandre

  • Ali Beheshti says:

    Jamie, thanks a lot. Your code is very helpful for me. But it seems not work in the mixed addresses of two languages such as ‘ Bazar e Zytoon, بلوار امام موسی صدر, Bandar Abbas, Hormozgan, Iran ‘. Do you know something about this?

    I really appreciate you.

  • James says:

    Hi Jamie,

    My question may not be quite relevant to this article but I’ll be very thankful if you could help me on this case:
    I’m currently a PhD student at UWaterloo and I’m looking into whether I can somehow access Google traffic data for a limited section such as 2 or 3 intersections. Google does provide access to its traffic layer API but I’m not sure if its possible to some how obtain the backend info. For example sending requests every 15 minutes and obtaining the length at which the traffic is slow (red line).
    Travel times, considering traffic, from point a to b may also be useful for me but I’m not sure how to obtain that either.
    Any help will be great!
    Thanks

    • Jamie Bull says:

      This is a great question. Unfortunately it looks like the maps are processed on the back end and then delivered as tiles with the traffic layer already included like this one. You could find the tile that covers your target area then do some image processing on that using something like Python Imaging Library. Alternatively (and I’ve not tested this so can’t be certain it includes traffic) you can find the journey duration from one point in your target area to another using the code in this post as a starting point.

  • Geoffrey says:

    Hello,

    Thank you for these functions! They are very useful!
    I copied the two functions (G_Distance and G_Time). They work fine on my pc (Windows 8 and Office 2013).

    I proceeded in the same way on my office PC (Windows WP and Office 2007), and only the G_Time function works. The G_Distance function always returns the same value “0”.
    I installed the MS XML 6.0 Reference. I haven’t done much request with this IP. I don’t see where the problem is.

    Do you have any idea?
    Thank you.

    • Jamie Bull says:

      Hi Geoffrey, I’m afraid I can’t shed any light on that. I’ve not developed for Office 2007 for quite some time. You could try setting a breakpoint in the VBA code and stepping through to see where it fails.

      • Geoffrey says:

        Thank you for your help.
        The solution is to replace :
             Origin = WorksheetFunction.EncodeURL (Origin)
             WorksheetFunction.EncodeURL destination = (Destination)

        by:
            Origin = Replace (Origin, “”, “% 20”)
             Destination = Replace (Destination, “”, “% 20”)

        And it works on Office 2013 !

        Keep up the good work !

        • Rafael says:

          Hey , I’m trying to do the same thing, but it isn’t working, could you detail the explanation?

          • Jamie Bull says:

            Looks like a mistake in Geoffrey’s comment – possibly WordPress’s fault. Try the code below. What it does is replace any spaces in the Origin or Destination parameters with %20. EncodeURL does the same (and more) but isn’t available on older versions of Excel.

            Replace this:
                 Origin = WorksheetFunction.EncodeURL (Origin)
                 WorksheetFunction.EncodeURL destination = (Destination)

            with this:
                Origin = Replace (Origin, " ", "%20")
                Destination = Replace (Destination, " ", "%20")

  • Peter Wilford says:

    Hi Jamie

    I am trying to work our the distances between say 100 post codes and an office location. Very few are returned with the vast majority returned as #NA. However, when i access google maps directly a distance is returned.

    Any ideas

    Peter.

    • Jamie Bull says:

      Hi Peter,

      Most likely you are hitting the soft rate limit. This is just an example piece of code – for production use you would also want to cache results locally and to add a wait/retry function for when the API soft limits are hit.

      Jamie

  • Justin says:

    Hello Jamie. Your codes are helping me a lot.

    However, when I tried to load the excel file it gave me an infinite “Complie error: End of statement expected” over and over.

    Another question is, can I return the direction from A to B? for example, I want to return W when calculated from New York to Los Angeles and return E when calculated from Los Angeles to New York.

    Thanks!

    • Jamie Bull says:

      Hi Justin. On the first issue, what version of Excel are you using? And are you on a 32 bit or 64 bit version of Windows?

      On the second point, yes you can do that using the functions you can find in this post to look up the longitude.

      • Justin says:

        It is 32 bit. I just installed Excel 2013 and still same problem.

        I remembered your loongitude and realized I could use that function to get directionality.

        For the first part again, my computer in office is 64 bit and my laptop is 32 bit. Your function works in the office but not on my laptop so I believe it is 32/64 bit issue.

        Any idea how I can fix it please?

        • Justin says:

          **This is the function that shows up as the problem**
          Function ConvertAccent(ByVal inputString As String) As String
          ‘ Code originally from Rick Rothstein, posted on
          http://www.jpsoftwaretech.com/remove-and-replace-special-characters-in-vba/
          ‘ Handling of German characters contributed by Gabor
          Dim X As Long, Position As Long
          Const AccChars As String = _
          “?럻옝읒쩠컵픽盖誥國喫戇倆描白丕飡宸偃⑵隅膺狀穽增彩充犀亨涍?”
          Const RegChars As String = _
          “sSZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaacceeeeiiiidnooooosuuuuyyc”
          Const DeAccChars As String = _
          “켯俸琢”
          For X = 1 To Len(inputString)
          Position = InStr(AccChars, Mid(inputString, X, 1))
          If Position Then
          If InStr(“?, Mid(inputString, X, 1)) Then
          inputString = Left(inputString, X) & “s” & Right(inputString, Len(inputString) – X)
          End If
          If InStr(DeAccChars, Mid(inputString, X, 1)) Then
          inputString = Left(inputString, X) & “e” & Right(inputString, Len(inputString) – X)
          End If
          Mid(inputString, X) = Mid(RegChars, Position, 1)
          Debug.Print Mid(inputString, X)
          End If
          Next
          ConvertAccent = inputString
          End Function

          • Jamie Bull says:

            Odd, that’s not where I’d normally expect to see a 32/64 bit error cropping up. I suggest commenting out where that function is called and see if that avoids the issue.

  • jan says:

    Hi Jamie,

    Your code is really amazing. Thanks so far. But I have one question.

    If I am looking for both, distance and driving time between to locations in Germany, Excel shows me curious results that are not matching the results of the Google Maps website.

    For example: Distance from 10117 Berlin to 14467 Potsdam

    Results, using your Code: Distance approx. 34km, driving time: approx.: 32 min
    Results Google Website: Distance approx.: 34km, driving time: approx.: 37 min (considering traffic), 33 min (without traffic)

    Even if it looks pretty petty, it is not, as the differences in driving time increase with the distance between the two locations.

    Do you have any idea of what might be the reason for this? Or: Is there a way to get the driving time without traffic, using your code?

    Thanks in advance,
    jan

    • Jamie Bull says:

      I’m afraid you’d have to ask Google why they are returning different values. I don’t have access to their code behind the scenes.

      • jan says:

        Recently I’ve found out that your code (ok, more precisely the google maps api) seems to work fine with google maps classic, but no longer with the modern gui of google maps, where only driving time with and without traffic is displayed.

        By the way, the driving time, returning via google maps api, seems to be a kind average driving time.

        greetings from Germany

        • Jamie Bull says:

          Thanks Jan, that’s really useful. To be honest I’d been expecting more trouble when the Maps API changed but since this isn’t top of my list of projects to maintain at the moment I’ve not looked into it properly.

          A quick refresher check of the Directions API docs tells me that the time returned there (and presumably in the old version of Maps) is without traffic. To get time allowing for traffic you need to be a business customer and include the client and signature parameters.

          https://developers.google.com/maps/documentation/directions/

          • jan says:

            OK. Thanks for this information.

            But it’s quite hard to understand, why there are apparently three different driving times for one route. To carry this to extremes, falk routenplaner has lifted its gui and claims to use the newest data from google maps. But actually the just return the data from google maps classic, which is different from the modern google maps 🙂

            And: Corresponding to the route, there are up to 20 minutes difference between gm modern (without traffic) and google maps classic (whatever this specific driving time means).

            Nevertheless your code has already helped me a lot!

  • Ruch says:

    Thanks so much for this code – it’s great and so, so helpful! I have a couple of questions:

    – How could we make sure that the code “knows” I’m talking about UK specific addresses? (e.g. Birmingham is assumed to be within USA as opposed to UK)

    – Is there a way to check whether an address is within a certain country? In this case, the UK (e.g. Input is an address in Poland -> I need to flag this entry as being outside the UK)

    Thanks so much again for your amazing help!

    Cheers,

  • Ben says:

    I am so excited that I found your code because it should be able to save me hours of work. That being said I have zero history of coding.

    I have used other excel plugins that do the same function through Mappoint but the results are inaccurate. The fact that this uses Google maps makes it perfect for my needs.

    After quite a good amount of time searching to no avail, I thought I would ask you if you have any resources that I can read up on in order to get this to work. My lack of knowledge goes so far that I can follow steps online to get it copied but then can’t figure out how to run it.

    Obviously this may not be the best place to ask for beginner instruction but literally any help or references you could give me would be greatly appreciated.

    • Jamie Bull says:

      Sorry for the very late reply. You use it just as any Excel function in the worksheet. For example =G_DISTANCE("Here","There")

  • DavidW says:

    Hi Jamie,

    I 100% doff my hat to you Sir.

    I downloaded your Excel_GMaps_v1_02.xlsm file and have used the coding to sit behind a piece I’m working on. Would you be able to send me a tweeked copy of the file that calculates the distance/time based on avoiding “Highways” as I want to specifically avoid include Motorways in the route.

    I really cannot thank you enough for your time in sharing your skills so openly.

    Many thanks once again.

    David

  • Kapil says:

    Hi Jamie,
    Somehow this does not seem to be working on my computer but is working fine on my friends computer.Is this because I’m using excel 2007 instead of 2010. Is there a plugin available for 2007?
    Error message displayed is: ‘Compile Error. Expected: End of statement’
    Thanks!
    Kapil

  • mimi says:

    Dear Jamie,
    The G_elevation function retrieve me this error message, ‘Compile Error. Expected: End of statement’ How can i fix it?

    Thanks!

    • Jamie Bull says:

      When does the error occur? What version of Excel? What are you searching for?

      • mimi says:

        Hi, I insert the coordinates and when I use G_ELEVATION function downloaded from your web it retrieves me that error. I’ve tested it in excel 2010 and excel 2013. Some months ago it was working properly in the same machines that i’m using now.

        Thks!

  • Benjamin says:

    Hi Jamie,

    great work. Your’s was the only script which worked out of several I tried and it was the shortest. You saved me a lot of time. Thanks a million.

    Ben

  • Sarah says:

    Hi Jamie,

    Thank you so much-this is a great function!

    I am trying to use it to find the distance between almost 800 addresses. It seems to be working at random-half the cell are populated with the distances, the other half are filled in with #NA. (I saw in an earlier post, you mentioned soft rate limits but I’m unfamilar with what that is…) Do you kow what might be causing this/how to fix it?

    Thanks!

    • Jamie Bull says:

      Hi Sarah,

      Soft rate limits are where the script is asking for answers from Google at a rate faster than 10 requests per second. It can be fixed by adding a short delay somewhere in the code module, or better by caching the results locally. Please feel free to get in touch at mail@oco-carbon.com if you’d like assistance in developing anything.

      Jamie

  • Christiaan says:

    I can not get the alternative routes to work. Any advise?

  • Christiaan says:

    Is it possible to get the longest distance between two points?

    • Jamie Bull says:

      There’s a great article about exactly that here. Might not be quite what you meant but I think it’s well worth a quick read.

      I can’t answer what you’re actually asking without a few more constraints. Do you mean of the routes returned by Google for a simple query?

  • Christiaan says:

    Pardon the vague question but I’m new to this.

    When enter coordinates for 2 points is it possible that google returns the longest(furthest KM) route?

  • Jonathan says:

    Hi Jamie, the article you have written is awesome and really helpful for my school project. However is there anyway I can utilize your codes to calculate the most logical path to take for multiple location. Given the alphabetical order denotes the distance between each other, the algo should avoid travelling from point A to Z then to B, instead it should travel from point A to B then Z.

    I’ve been working on a calculation table by permuting the different locations but I cant seem to get it right. Could you probably shed some light?

    • Jamie Bull says:

      Hi Jonathan,

      That sounds like a interesting extension of the code. Your permutations approach also sounds like it’s on the right track so long as you don’t have too many points (since the combinatorials will stack up quickly). If you’re still having problems feel free to send me your spreadsheet (mail@oco-carbon.com) and I’m happy to take a look.

      Jamie

  • Phill H says:

    Hi Jamie,

    I am using the G_distance function built into one of my spread sheets to calculate the cost between certain routes.

    is it possible to have something for toll routes. so if a route included a toll to display YES or anything really? just so I could apply the toll charge to the costs?

    Thanks,

    • Jamie Bull says:

      Hi Phill,

      Google lets you specify a route without tolls, so if you check the distance both with and without tolls then you can see if there’s a toll present as they’ll (probably) be different distances. The change to the URL in this snippet is to change myRequest.Open "GET","http://maps.googleapis.com/maps/api/directions/xml?origin=" & Origin & "&destination=" & Destination & "&sensor=false", False
      to add the avoid=tolls URL parameter like so:

      myRequest.Open "GET","http://maps.googleapis.com/maps/api/directions/xml?origin=" & Origin & "&destination=" & Destination & "&avoid=tolls&sensor=false", False

      You should be able to figure it out from there, but if not let me know.

  • Phill H says:

    Hi Jamie,

    thanks for the quick response.

    ok so I have created a new function g_distancetoll (which has the URL with avoid toll)

    its giving the same distance to a route with a toll?

    • Jamie Bull says:

      Are you sure that Google would choose the toll road under normal circumstances? It could be that both routes avoid the toll road.

      • Phill H says:

        OK for some reason when I create the new function it does then give me the avoided toll distance so in theory that would work for me fine.

        but it also then changes the G_distance function (without the Avoid tolls) to the avoided toll distance.

        I’m now confused?

  • Phill H says:

    What I did was copy all the code for G_Distance, pasted it into a new module but called the function G_DistanceToll and changed the URL to avoid tolls and change the relevant bits from G_Distance to G_DistanceToll.

    Tested it again, as soon as I create the new function, the original G_Distance function also works with avoided toll routes.

    sorry but I am no code expert at all and am just tinkering.

    thanks,

  • Sam says:

    Hi, this code seems to be exactly what I am looking for so thank you for your sharing this, however I only seem to get a result of 0 in the cell. I am a bit of a novice in Excel and have been googling lots of things to try and figure it out, but can’t quite get there.

    I have opened a new workbook, and Inserted a Module, pasted the code into there, added the reference and then back in the spreadsheet used this

    =G_DISTANCE(“Manchester”,”Liverpool”)

    It returns 0. I have tried adding , UK on the end of the origin/destinations but it still outputs 0. Even when I use the insert function wizard and select G_DISTANCE through that way. Any idea what I’m doing wrong?

    • Jamie Bull says:

      Hi Sam. Have you set the reference to MS XML v6.0 as described at the end of the post?

      • Sam says:

        Yeah, I ticked the box next to Microsoft XML, v6.0 on the Tools References when on the module screen. If that is the right place?

        • Jamie Bull says:

          Yes, that’s right. What version of Excel are you using? The EncodeURL function is only available since Excel 2013.

          • Sam says:

            Oh, I am using 2010. Do you know if there is a way of making this work in Excel 2010?

            Many thanks for your quick responses.

          • Jamie Bull says:

            If your queries are simple and don´t have any special characters you can just comment out those lines in the VBA. Otherwise I there´s a backwards-compatible URLEncode function in this post.

  • Sam says:

    That’s awesome. That you so much for all you help!

  • Soffia says:

    Hi Jamie,

    This post is really useful! I’ve uploaded the macro and your downloadable workbook works really well. I’m now trying to incorporate the same table that’s in your post but I’m having problems getting it to work. Could you explain a bit more exactly what you did to get the table to work. (I’m a bit of a nube).

    Thanks – Soffia

  • Soffia says:

    Also I’m using the sheet in your workbook called G_DISTANCE and the only UDF available is G_DURATION which seems to results that are not close to milage, kilometres or even time. Could you explain what result it’s actually giving me please?

  • Alex says:

    The best in the net
    If you have to calculate many distances
    Thank you very very much for this effort
    kind regards Alex

  • Marc says:

    Hi Jamie,
    Great work, and patience to answer all these questions. Besides, I myself added, the function always gives me 0 … I have checked the XML version 6, I am in Excel 2010, the problem may be coming from there.
    You’re a bit of a super hero to excel, and I just need to calculate the distance in km between two cities with Google maps.
    Please do help me !!
      Sorry I could not read all the comments, if the answer is in I plunge myself!
    Have a nice day !!
    Marc

    • Jamie Bull says:

      Hi Marc,
      Thanks for the praise, always good to hear that this is useful to people. And yes, the problem is probably that you’re using Excel 2010. This answer should solve the problem for you.
      Jamie

      • Marc says:

        Jamie thank you for the answer, but it does not work .. There is an order in Virtual basic?
        Maybe I need to change the googlmaps Link?
        Thank you for your availability.

  • Phil D says:

    Hi Jamie,

    If I want to use your work to return a simple mileage calculation from two columns A &B in column C do I simply use the formula =G_Duration(A1,B1) and copy it down as far as I need?

    Sorry to ask such a basic question – this is not my area of work

    Thanks

    • Jamie Bull says:

      G_Duration gives you time. You want G_Distance for distance. Otherwise, yes you just copy the formula down.

      • Thomas Bass says:

        Is G_Distance driving distance or straight line distance?

        Thank you so much for this tool Jamie!

        • Jamie Bull says:

          It’s driving distance. You can find a VBA function for great circle distance (as-the-crow-flies) in one of the downloadable files on this site, or elsewhere on the web.

  • Marc says:

    I try to copie the URLEncode function and the function gives me “0” as result. I have one city in “A1” an other city in “B1”, I want the road distance in km between A1 and B1 in an other cell, c1 for exemple. i copied/ pasted your “g_distance” function et add the URLEncode Function but the result is always “0” in C1.
    sorry if I wasn’t clear,
    Thanks you
    Marc

    • Phil D says:

      I get a value but it doesn’t match the value that is returned when I put the same postcodes into Google. For example YO42 to WF13 gives me 59KM or 37 miles. The same query direct to Google gives me 49.7 miles?

    • Jamie Bull says:

      I can’t see why that wouldn’t work then. All I can suggest is you place a breakpoint in the VBE and step through the code (maybe add a few debug.print statements) to see what’s going on.

      • Phil D says:

        Jamie thanks for coming back.

        Can you post your workbook so I can take a look at the difference?

        References to VBE are a bit beyond me.

        Many thanks

        • Phil D says:

          Jamie,

          I have it working perfectly thanks. The only issue I have now is the query limit. I have 12.5k lines! Need to find some new IP friends.

          I know its been said before, but this is fantastic piece of work. Many thanks.

          Phil

          • Jamie Bull says:

            Glad to hear it. Sorry, I missed your last reply. What was the problem in the end? By the way, if you have a large batch you need processing on a one-off basis we offer that as a service. Feel free to get in touch at mail@oco-carbon.com

  • Thomas Bass says:

    Thanks so much for this tool!

    Is there any way I have have it display Miles instead of Kilometers?

    Thanks again!

  • Shefali says:

    Jamie this is really useful. Do you have any code that can generate the nearest tube, train stations and bus routes. I hav 2000 post codes and need to get these three pieces of information for each. Thanks

  • Jonathan says:

    Brilliant, thank you.

  • Alvi says:

    Dear Jamie,
    many thanks for your code. I’ve read all the comments and tried to solve my problem by myself but unfortunately my result is always Zero ! can you help me? can you send a sample of your workbook pls?

  • Kim says:

    Hi, Jamie! This is my second year to use your spreadsheet for our mileage purposes. Thank you! It’s been great!

    However, I need a delay, sometimes significant. How may I achieve 500-750 msec between “calls” for each address I’m inquiring of the distance? You would shake your head at what I manually do to achieve the results.

  • Shahir says:

    Absolutely brilliant!! Thank you for this great tool. Saved me hours of coding and days of manual lookups.

  • Daniel says:

    Hi Jamie,

    Thanks so much for this, it really helped me alot in my research. However, as an alternative measure, is there a way to show the direct distance instead. In other words, the shortest distance between point A and B?

  • Sam says:

    Hi
    this is genius
    we are looking to use this for our mileage claim sheets for work,
    im trying to use English addresses but the data given back is not correct. will either be using English postcodes or road names and numbers will it work.
    thanks

  • bart says:

    I was using thsi in an excel file to calculate prices for customers.
    but it stopped working recently. Anybody having the same problem?

  • Aline says:

    Hi Jamie!!

    I’ve tried the code, but sometimes the function returns the right answer (if compared to google maps), but sometimes the function returns wrong values or zeros. I’m working only with brazilian zipcodes.

    Could I send you my spreadsheet to see if you see what’s the problem please?

    Thanks a lot!!!

    • Jamie Bull says:

      Hi Aline, I know I already responded to you by email but thought I’d also add the answers here in case anyone else has similar issues.
      1) To make Google know your search value is a Brazilian postcode, add ” brazil” to it before sending it to the G_DISTANCE function, so the cell should have =G_DISTANCE(A2&" Brazil", B2&" Brazil")
      2) Too many queries meaning that you hit the soft rate limit of 10 calls per second. This can be avoided by adding your own rate limits in combination with local caching
      3) Waaay to many queries meaning you hit the hard rate limit of 25,000 calls per day. If you need to, please get in touch with us at mail@oco-carbon.com to discuss your options.

  • Mike says:

    Jamie, Unless you deliberately deleted my post to you and your reply (music teaching daughter) you may have a problem with this site. Just went to show her our conversation and it is gone.

    She likes the spreadsheet so that’s a relief,

    Best wishes

    Mike

  • Bill says:

    Mine required a few tweaks, but that’s an excellent template to start from. Thank you!

    • Bill says:

      One tweak I should mention specifically: to get miles you need to divide the distance by 1609.5459 (or its close enough) not 1000. Then again, I’m in the US. 🙂

  • JD says:

    Hi Jamie,

    Your file works wonderfully. I have one query with regards to referencing to Microsoft XML, v6.0 (Tools > References > Microsoft XML, v6.0 ).

    Can this step be automated or included as a part of the solution?

    Many Thanks.

    • Jamie Bull says:

      Hi JD. I’ve looked into this before and found it more trouble than it’s worth. However if you distribute a file with the reference already set then the user doesn’t need to do it again. Depending on what the problem is, this might be a solution.

      • JD says:

        Thanks for coming back to me Jamie.

        I have tried it and it works until the time the user remembers to use the corected file. So I created an Add-in, but excel doesnt start with the add-in if XML v6.0 is not set correctly before starting with the add-in.

        Thanks a lot again for this wonderful tool.

  • HY says:

    Hi Jamie, i am trying to calculate about 315 different post codes in Singapore for distribution distance usage, will i be able to use your excel for this purpose?

    Sample
    column A Column B Distance
    132800 467380 25.6

    • Jamie Bull says:

      Yes, that’s exactly the sort of thing it does well. You may need to specify the country code in the query so you have =G_DISTANCE(A2&" SGP",B2&" SGP") so that Google knows where the postcodes are located.

      • HY says:

        hi jamie, your file rock.
        in google map it has multiple route and in your excel the default route was taken, any possibility to take the shortest route instead?

  • Mohammad Ashraf says:

    Thank you so much for the file, Jamie.

  • Toby says:

    Thank you very much for this piece of code. It has saved me a lot of work.

    Because I ran into some issues with a matrix of 60 points (always got out of limit even on a fresh day or IP and after deleting all the cache files) I got myself a Google API Code and changed it a bit to use the code. Now I get an REQUEST_DENIED error. Could you please have a look and tell me if you see the problem.

    Function G_DISTANCE( _
    Origin As String, _
    Destination As String, _
    Optional Requery As Boolean = False _
    ) As Variant

    Dim key As String
    Dim myRequest As XMLHTTP60
    Dim myDomDoc As DOMDocument60
    Dim distanceNode As IXMLDOMNode
    Dim statusNode As IXMLDOMNode
    Dim CachedFile As String
    Dim NoCache As Boolean
    On Error GoTo exitRoute
    G_DISTANCE = CVErr(xlErrNA)

    ‘ Sleep Funktion
    Sleep (200)

    ‘ Check and clean inputs
    If WorksheetFunction.IsNumber(Origin) _
    Or IsEmpty(Origin) _
    Or Origin = “” Then GoTo exitRoute
    If WorksheetFunction.IsNumber(Destination) _
    Or IsEmpty(Destination) _
    Or Destination = “” Then GoTo exitRoute
    Origin = URLEncode(CStr(Origin), True)
    Destination = URLEncode(CStr(Destination), True)
    key = “String of Google Key”

    CachedFile = Environ(“temp”) & “\” & Origin & “_” & Destination & “_Dist.xml”
    NoCache = (Len(Dir(CachedFile)) = 0)

    Set myRequest = New XMLHTTP60

    If NoCache Or Requery Then
    myRequest.Open “GET”, “http://maps.googleapis.com/maps/api/directions/xml?origin=” _
    & Origin & “&destination=” & Destination & “&sensor=false” & “&key=” & key, False
    myRequest.Send
    Else
    myRequest.Open “GET”, CachedFile
    myRequest.Send
    Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText
    ‘ durch folgende Funktion wird der satus der Files ŸberprŸft
    Set statusNode = myDomDoc.SelectSingleNode(“//status”)
    If Not statusNode.Text = “OK” Then
    Call G_DISTANCE(Origin, Destination, True)
    End If
    End If

    Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText

    Set statusNode = myDomDoc.SelectSingleNode(“//status”)
    If statusNode.Text = “OK” Then
    If NoCache Then: Call CreateFile(CachedFile, myRequest.responseText)

    Set distanceNode = myDomDoc.SelectSingleNode(“//leg/distance/value”)
    If Not distanceNode Is Nothing Then G_DISTANCE = val(distanceNode.Text) / 1000

    Else
    G_DISTANCE = statusNode.Text
    End If
    exitRoute:
    Set statusNode = Nothing
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
    End Function

    • Toby says:

      I did some digging and used the URL in browser to have a look at the error file.

      At first I got an error that this feature has to be used with SSL so I added “https://” to the URL.

      This then go me to the following error:
      This API project is not authorized to use this API. Please ensure that this API is activated in the APIs Console: Learn more: https://code.google.com/apis/console

      I checked the console and I generated a browser key that is active for Google Maps Static API and accepts requests from all URLs (* added).

  • LarryG says:

    Your work has served me very well over the past few years. After upgrading to Windows 10 I now get the following error message: Compile error: User-defined type not defined. This is the line that causes the error: Dim IE As MSXML2.XMLHTTP60.

    I used the code successfully in Windows 7 and 8, but it failed on my first attempt in Windows 10. I would appreciate any help to make it work again in Windows 10.

    • Jamie Bull says:

      Hi Larry.

      Glad you’ve found it useful. It sounds like you haven’t added set a reference to Microsoft XML, v6.0 (Tools > References > Microsoft XML, v6.0).

      Hope that helps.

  • Cris says:

    Dear Jamie, is it possible to send me the xls file? My code generates “zero” for all distances.. Thanks in advance!

  • mokhtar says:

    Please helpe me
    I want to load distance for all point on map like.from city A to city B 100km and load all data on Excel or matrix for compute shortest path graph

  • mokhtar says:

    Please helpe me
    I want to load distance for all point on map like.from city A to city B 100km and load all data on Excel or matrix for compute shortest path graph
    what I have to do it

    • Jamie Bull says:

      Hi Mokhtar, How many edges will you have on the graph? That will define whether this is possible or not using the G_Distance function. J

  • Hi Jamie,

    Can you please help changing the macro using api key?

    See here: https://developers.google.com/maps/documentation/distance-matrix/start

    I am trying to calculate distance of a 5000+ routes which I am not able to use without api.

    Looking forward for a positive response.

  • Actually I am looking to use distance matrix api and using a api key.

  • I tried changing the url in the code but it is giving me result in some cases and #NA in lots of cases.

  • Alex says:

    Best macro for calculating distances, GPS Data etc.. in Excel
    tnx for sharing

  • Shamus says:

    Does this macro work for United States zip codes?

  • harneet says:

    Best code…worked on very first try..
    thanks a lot for sharing, Jamie..

  • Zabata says:

    very useful , well described thank you

    great work, however how to enter just coordinates latitude and longitude only is it feasible

    • Jamie Bull says:

      Glad this is useful for you. Yes, entering latitude and longitude is fine. Just concatenate them and pass them in like this: =G_DISTANCE(LatCell&","&LongCell)

  • Zabata says:

    Thank you very much appreciate your valuable help, worked as charm.
    you are the best.

  • Borhan says:

    You’re amazing! Worked like a charm! Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *