I was asked for a downloadable file with the G_LATLNG function by Filipe who was having trouble getting the function working. Since I posted that, I’ve done a bit of further work to improve it. The workbook you can download caches results from G_LATLNG and G_DISTANCE queries to reduce the amount of time spent polling Google. This also helps to avoid hitting the buffers in terms of how many queries they allow you – 2,500 per day, but they also seem to have soft limits over shorter time periods.
Download updated example file – updated to v1.02 on 24/03/2014 to correct a bug in the duration calculation.

There are a couple of other new features. G_LATLNG now takes an optional second argument allowing you to return just the latitude or longitude. The syntax is =G_LATLNG(Location, optionNum). If you enter optionNum as 0 you get both (this is the default if you don’t specify), 1 you get just the latitude, 2 just gives you the longitude.

Since the update, the G_geocode() code module now allows you to specify G_LATLNG(), G_LAT(), G_LONG() or G_ADDRESS()

These give you options to return either latitude or longitude singly as well as as a pair. G_ADDRESS() can be used for reverse geocoding – finding a full address from a latitude and longitude, or from a partial address. It is also useful for cleaning up and standardising addresses so that they can be compared with others, useful where there are spelling mistakes or inconsistencies in the way they have been entered in your data source.

The WebBrowser worksheet shows you the difference between a regular Google Map and a Google Static Map. Click on locations in the list and it will update the map automatically. You can play around with the parameters and see what they do. You can also replace the places in the Locations table with your own ones

Tags: , ,

Jamie Bull | mail@oco-carbon.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 […]

202 Comments on “Google Maps and Excel download”

  • philippe says:

    Hello Jamie,

    Thanks a lot for posts on google maps in excel !! So great to find helps.

    A small question: in the example above, I don’t find the WebBrowser worksheet neither the modified G_LATLNG with optionNum option. Or I miss something, just eyes wide shut or there is a mismatch …

    Could you check the example to see if it is the last version? The one I get following the link is poscodes.xlsb.

    Anyway, again, your post helped a lot !!!

    Kind regards


    • Jamie Bull says:

      Hi Philippe,

      Thanks for the spot. I’ve corrected the link and that should work for you now.


      • philippe says:

        Ok, I got it… and it works 🙂

        1000x Thanks


      • philippe says:

        just a comment,

        on the “my location” post, the code contains some misprints, I think. Obvious, I know but with copy paste, I miss it at the first test… 😉

        “Function My_Lat()
        My_Country = GeoIP(2, 1)
        My_Lat = GeoIP(2, 1)
        End Function

        Function My_Lng()
        My_Country = GeoIP(3, 1)
        My_Lng = GeoIP(3, 1)
        End Function

        Function My_LatLng()
        My_Country = GeoIP(2, 1) & “, ” & GeoIP(3, 1)
        My_LatLng = GeoIP(2, 1) & “, ” & GeoIP(3, 1)

        End Function

        Function My_IP()
        My_IP GeoIP(4, 1)
        My_IP = GeoIP(4, 1)
        End Function”

        kind regards


  • Guido says:

    Hej Jamie,

    a million & one thx for this fantastic macro !!!

    Since I’ve started my interests in Excel and vba I know what amount of work and knowledge is in that piece of work. 🙂

    It works perfect on the spot!

    However I would like to know if it is possible to get the driving times too?
    I tried some “things” but wasn’t sucsessfull so far..
    The times should be in the format with seperate hours and minutes so I can get it easy to the format of hh:mm.

    BestOf Germany,

    • Jamie Bull says:

      Hi Guido,

      Glad that is helpful for you. There’s another post which gives journey times. The standard option should give you a result in Excel’s hh:mm format but if you want hours and minutes in separate cells you might find the decimal option easier to work with. The formula =ROUNDDOWN(G_TIME(Origin, Destination, "Decimal"),0) should give you the hours and =G_TIME(Origin, Destination, "Decimal") - ROUNDDOWN(G_TIME(Origin, Destination, "Decimal"),0) will give you the minutes.

      Let me know if I’ve misunderstood or if that doesn’t work for you.


      • Guido says:

        THX !!!!
        The link you posted works fantastic!
        Can I get the format now to skip the zero if its less then 10 h?
        I did it so far with
        h”h” mm”‘”
        in the formating cells option but then it’s skipping the 1 by equal or more than 10 hours of course.
        and: can I get instead the “:” as seperator between the hour and the minutes a “h” but in high position (as you do it with the 1st, 2nd, 3rd, 4th etc)

        So far i use the format:
        h”h” mm”‘”
        in the formating cells option.


        • Jamie Bull says:

          =ROUNDDOWN(G_TIME(Origin, Destination, "Decimal"),0)&"h:"&G_TIME(Origin, Destination, "Decimal") - ROUNDDOWN(G_TIME(Origin, Destination, "Decimal"),0) ought to do something like what you want. This post on OzGrid ought to help with the custom formatting.

          • Guido says:

            Somehow I cannot made your code running any more.
            I did not change anything (as far I know 😉 )but it doesnt work any more.
            Any known bugs?
            Can you send me a simple “working” sheet with your code where I put in the 2 cities (i.e. start in “A1” and dest in “B1)?
            It worked perfect last year 🙂

  • Leonard says:

    Hi Jamie,

    In one of you posts re Google Maps and Excel you mention that you should display a map (API rules)
    I’m wanting to do just that – but I want to display a rounte between two places.
    In my 20 minutes of hunting and learning I’ve not seen how.
    Any clues?
    I want a small map with the route between then?
    Regards Leonard.

  • Matt says:

    Is there a way to “slow down” these functions? I am trying to use your VBA to geocode 100s of addresses rather than the handful of rows you have in the attached workbook. The problem I am running into is that after the first few rows google does not return geocoordinates (the G_LATLNG() function returns a 0), and I believe it is because Google limits the speed of geocoding after the first few transactions.

    • Jamie Bull says:

      You’re quite right, Matt. If you’re using the code for lots of geocoding then you do need to throttle it to stop Google from returning REQUEST_DENIED. The simplest way to do this is using the Windows Sleep API. Just add

      at the start of your module, then add the line

      somewhere in the code. You might need to experiment with the 250 (which is in milliseconds, so quarter of a second) to find a sleep duration which still runs fast enough for you but also stops Google from blocking your requests.

      • Jonas says:


        can you please tell me where exactly this has to be added? I’ve put those two lines in the beginning of the G_road_distance but I’m just getting lots of error messages 🙂

  • Pongkorn Limpisut says:

    Hi Jamie. A big big thanks. It is a great function. However, how could I get the shortest distance from this function? I really need the shortest distance, I’m sure that you already have the shortest distance function.

  • JY says:

    Hi Jamie,

    Why does it not work when i input south east asia countries like Singapore, Malaysia, Indonesia and India in the G_Distance tab?

  • S says:

    Hi Jamie,
    I want to use the code used in the example for a project. Do you have copy right over the code. Should I take any permission from you before using the code.

  • Dev says:

    Hi Jamie,

    I am using Excel 2011 for Mac and as soon as I open the file, I first get an ‘automation error’ and then Excel gets stuck with a message showing ‘opening Excel_GMaps.xlsm, Press Command + . to cancel’. Unfortunately, I am not able to cancel and it remains stuck.

    The other thing I tried to do was to create a function G_Distance as you had mentioned in another post, but there too, I get an error. It is a compile error and it takes me to this line – “DIM myRequest As XMLHTTP60” – User type not defined.

    I am not well-versed with the coding and was just trying to use the function for calculating distances for a project. What could I be doing wrong here?

    • Jamie Bull says:

      The second error you’re getting is because the file requires a reference to Microsoft XML 6.0. That’s probably the cause of the automation error too. Unfortunately for you it seems like other people have run up against this problem too and not found an answer. There must be a way of parsing XML on the Mac but I don’t know anything about it. I’ve put some feelers out though and if I get a response I’ll come back to you.

  • Paul says:

    Hi Jamie

    How can I use the g_distance tab to find distances in Australia. I can use the other 2 tabs ok, but not the g_distance tab, it just shows – for the distances.


    • Jamie Bull says:

      Hi Paul,

      Just to check, what distances are you looking for? It works fine for me for distances within Australia. There aren’t any roads out of Australia though so finding road distances to most places might be a little difficult…


  • Paul says:

    I am looking for driving distance between 2 paces. I replace “Munich” with “Sydney” on the G_DISTANCE tab, and values in the distances all change to a -.
    If i put in a full address, ie street “1 George St, Sydney, New South Wales”, it still shows me the same – in the distance.
    While if i put those 2 into the G_LATING tab i get “-33.8674869,151.2069902” and “-33.7042820,151.0996170”.

  • Paul says:

    Try 1 George St, Sydney, New South Wales TO 100 Mascot Drive, Eastlakes, New South Wales

    • Jamie Bull says:

      The G_DISTANCE tab in the download file is a distance matrix. You put up to six locations in column A and the same locations in the headers. Then the intersection of a row and column gives you the distance between the two locations. If I do that with the addresses you give in cells A2 and A3, and B1 and C1, then I get the distance – 34 miles – in cell B3. Dashes mean either that no route exists or that the distance is zero. I’ve also just updated the download file with some instructions – and improved code – so you can download it again if that helps.

  • john peach says:

    These functions work awesome – I think i got a little to carried away and now am getting the “OVER_LIMIT_QUERY”. do you know how long it takes to reset before i can use this again?


  • joelle Schicks says:

    Thanks a lot from Belgium for your wonderful tool…

  • catapulcher says:

    Hi Jamie

    thanks for this wonderful macro!

  • mark says:

    Hi Jamie. come across this site by accident, must say your work has helped me lots. I work in housing, mainly property strategy carrying out option appraisals concerning sustainability, remodelling and investment. We are in the process of developing a sustainability model which amongst other factors looks at concentration of stock, travel time/distance, income, investment and energy performance to arrive at NPV. Your tool is very useful.

    • Jamie Bull says:

      Hi Mark, That sounds very interesting. I’m actually working on something similar for non-domestic stock at the moment – primarily focused on university estates but also some work on schools. I’m looking at the influence of life cycle cost and life cycle carbon footprint on sustainability of refurbishment vs replacement. Might be worth comparing notes sometime.

  • Tom says:

    Absolutely fantastic bit of code. Incredibly useful, thank you so so much!

  • Megan says:

    Hi Jamie,

    I’m trying to figure out if this is something I can use. What I need to do is find out if a potential store is within a certain number of miles from any one of our existing stores. So if I have 100 new stores, and 1000 current stores, I need to enter all the addresses in and see if any new stores are 5 miles or less apart from our current stores.


  • Luiz says:

    Thank you so much for this!!!

  • Tom says:

    I get an error in Microsoft Excel. VBA pops up and says-

    “Compile error: The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.”

    Any way to update? 🙁

    • Jamie Bull says:

      Sorry about the delay on this. In case you haven’t figured it out already, you need to search for Declare in the VBE and replace it with Declare PtrSafe. It runs fine after that. If you’re concerned about running on 32 and 64 bit machines then you can edit any lines where it shows up with the pattern here.

  • Daniel says:

    Thank you very much for both this excelent tool, and the very clean vba code which makes even a real vba newbie able to understand and alter it (as I needed support for Æ and Ø characters).

    Been using it for 6 months now where it has worked great. Today I updated to your more recent changes. And it still works great!
    Google is picking some funny (=long) routes sometimes, but that is not your fault of course 🙂

    Just one question though.
    Both in the former version and the current one, when I have too many queries in a short time, and it returns with the Over query limit message, the formula doesnt seem to ever recaclulate again.
    I can only make it recalculate by clicking the cell, as if I wanted to edit the formula, and press enter. Then it works again – and it actually calculates a distance after doing this, which I guess means I only hit a short time limit on queries, and not the 2500 in 24 hour limit.
    I dont understand why it doesnt recalculate on its own though – nor dose it recaulcate if I force a recalc of the whole worksheet with F9.
    I’ve just bruteforced it to recalculate so far, but when others use the worksheet, they get quite confused by this behaviour.

    Any idea what the issue might be? I do have autocalc turned on in Excel of course.

    • Jamie Bull says:

      Good question. It’s always bugged me a little too. I’ve looked in the past at Application.Volatile but that didn’t work so today I decided that a quick but functional hack was probably the best option. Try adding this code to the ThisWorkbook module. The functions won’t automatically recalculate but the user can use F9 or Shift-F9 to force recalculation.

      If anyone has a less clunky way of achieving the same results, please let me know.

  • Daniel says:

    Not sure what I did wrong but the macro didnt do anything in the test worksheet I made.

    I ended up making a very simple version of the above, where it just replaces “G_Distance” within a given range of cells.
    It seems to work fine, but of course requires that I know exactly where my distance formulae will be.

  • Colin says:

    Hi Jamie – Thank you very much for the code. I am using 64 Bit Excel so have changed the Declare to Declare PtrSafe however I only get #NAME? error.

    Is the code still working?

    Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)

    Declare PtrSafe Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)

    Also it is possible to add fuel costs?

    • Jamie Bull says:

      Hi Colin,

      All I can say is it’s working for me with that fix. If you step through the code, where does it throw the error?

      On the fuel costs, that’s a great idea. I’m quite short of time and the moment, hence very few posts recently, but if someone wanted to have that added on a commercial basis I’d probably be able to squeeze it in. There’s quite a bit to be taken into account – fetching up-to-date fuel prices, vehicle MPG, mileage allowances (e.g. for expenses claims) – but definitely possible and worthwhile for the common use cases of this tool.

  • justin says:

    First off Jamie this is an amazing macro, so Thank you contributing it. When I try to use the WebBrowser tab and put my location the maps never update. then when I touch another location they also don’t update. is there something I can do to correct this?

    • Jamie Bull says:

      Hi Justin. I’m not sure what the problem is – what happens when you click on the My location cell? It should then take the map straight to your location – or at least to the location your IP address suggests you are at.

  • Marcelo says:

    Mate, this is a really good tool…..
    I’m using to help to create services for community members associated to a Not for Profit Organisation…. finding the distance to pick-up old members that doesn’t have public transport…

    Thank you very very very much for this tool.

    Kind Regards,

  • Sam says:

    Hi Jamie

    This is extremely useful. What would be the easiest way of modifying this to calculate distances between an entire list of UK postcodes and a fixed location?

    I am currently carrying out a research project and one of my analysis is the distance travelled by people to their local hospital.


  • Gabor says:

    some time ago I accidentally found this perfect tool, it is really great. Anyways there are some problems in German language, but they can be solved easily. I added some amendments.
    In German it is not enough to replace an umlaut (Ä,ä,Ü,ü,Ö,ö) with (A,a,U,u,O,o) (Function ConvertAccent), the correct transcription would be (Ae, ae, Ue, ue,…).
    There is also a little problem with the German ´ß´, you sould replace this with ´ss´.
    The ß exists in the German word fo street (´Straße´). Thus it appears really often in addresses. A google adjusted in German does not find such addresses.

    I did not find the function available in code ´ConvertAccent´ integrated anywhere, so I integrated those in the functions G_DISTANCE and G_DURATION before URLEncode.

    Origin = ConvertAccent(CStr(Origin))
    Destination = ConvertAccent(CStr(Destination))
    Origin = URLEncode(CStr(Origin), True)
    Destination = URLEncode(CStr(Destination), True)

    I extended this function. I integrated the ´ß´ into the available constants and created a additional constant for the German umauts.

    Const AccChars As String = _
    Const RegChars As String = _
    Const DeAccChars As String = _

    And before the originally function does the transcription, my code quickly adds a ´s´ behind the ´ß´ or a ´e´ behind found German umlauts.

    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

    With those little replacements it perfectly works with Google.

    • Jamie Bull says:

      That’s a great addition. I’ll try and find to add it into the code over the weekend. Thanks!

      • Scot Crain says:

        Jamie, is there an easy way to expand the number of rows and columns on your G_DISTANCE tab, to accommodate the addition of more locations? You’ve created a very helpful tool, but I’d like to be able to view a somewhat larger number of locations. Thanks!

  • Alex says:

    And another one from Germany:
    Awesome Tool Jamie, many thanks it saved me lots of time.

    I am wondering how i can automatically calculate when I have a list with hundreds of distances that have to be calculated without copy-pasting every cell into the search fields.
    Is that possible? Best Regards, Alex.

  • Brendan says:


    This seems like a great tool and I can’t wait to use it.

    However, after downloading the excel file and not changing anything, when running the macro all that is returned is an error message stating “Compiling Error: End of Statement Expected”.

    This is on a loop and can not be exited.

    Do you have any ideas as to why this is happening please?



  • Christian says:

    Dear Jamie
    I’m just using version 1.01 on a Windows Vista 32 bit machine running Excel 2007. Starting up was not easy due to some compilation errors (#VBA7 and some double #END IF), but I found it and commented it out.
    I entered as well a sheet “Input_Addresses” to calculate distances between a start and several destinations based on addresses.
    The last item that bothered was that the webbrowser didn’t work. So I entered a new snippet using code from Chip Pearson – and then it worked. I can send the version to your hands.

  • Jamie says:

    I also get a Compile error:
    Expected: End of Statement

    Using a PC… any thoughts Jamie?

  • Rafaela says:

    Does anybody know why it is not working anymore? I used it on Friday and it was ok. I already tried to download it again and it is not working.

  • Robert says:

    Hello from Germany,

    thx for the great tool. Is that possible to calculate more than 6 cities in the ‘G_Distance’ table and how can I do this? I have to calculate the distances between 75 cities.

    Thx in advance

  • Louis Vorstenbosch says:

    Hi Jamie,

    I copied the Origin and Destination of multiple records but the formula doesn’t give the right value every time.
    When I manually edit the fields Origin or Destination the formula udates and gives the correct answer.
    How can I prevent to manually have to edit one field a record?

    Kind regards,


  • Santosh says:

    Thanks a lot…working amazing…

    just a clarification is there a limit on the number of requests for per day?

    Thanks and regards,


  • Santosh says:

    Hi Jamie,

    Thank you for the response

    I get over_query_limit after running for 500 cases

    how do i over come this particular problem?

  • Eric says:

    I can not get the following link to work correctly:


    It tries to open the associated .xlsm file as a word document, rather than as an excel document.



    • Jamie Bull says:

      Have you tried right clicking and choosing “Open with”? You should then be offered the option to “Choose default program…”

      • Eric says:

        Thanks for your response!

        I finally got it to open. I had to open EXCEL and then open the document within EXCEL. I was unable to open it otherwise.

        However, I must have gotten off of the thread that I thought I was following.

        What I am trying to do is to find the distance and driving time between 2 USA addresses.

        In other words:

        Origin A1=647 Amber Jack Dr (Street Address)
        A2=Ballwin (City)
        A3=MO (State)
        A4=63021 (Zip Code)

        Destination A6=512 S Macomb St (Street Address)
        A7=El Reno (City)
        A8=OK (State)
        A9=73036 (Zip Code)

        I could concatenate them in A5 and A10, if necessary.

        I would like to put the Driving Time ([h]h:mm) in A11 and the Driving Distance (rounded to the nearest mile) in A12.

        I don’t think that is what you are doing in this particular spreadsheet, or is it?

        If you were working on this situation somewhere else, I would appreciate being redirected back to it, or if it is pertinent to this discussion, then any help would definitely be appreciated.

        Thanks in advance for any help that you may be able to provide!


  • Eric says:

    Well first of all, my apologies for the above post.

    I spent some more time studying your spreadsheet and discovered that it does indeed calculate the distances for the USA addresses, using the G_Distance tab. When I wrote the first time, I had put only 1 USA address into the heading and of course, you can imagine what i saw, since there is no way to have driven the points that were listed.

    Once I entered more than one USA address in the column headings and changed the matrix to divide by 1.6, I got what I was looking for. Thanks so much.

    I still need to modify it to fit my format, but let me play with it on my own and see if I can figure it out by myself. I find that I learn much better that way.

    I also found your “A Google Maps journey time function for Excel” link and I’m sure that it will be just as powerful. Thanks for your help. With just a little modification, I think that this will be just what I needed.

    Thanks again for your help!


  • mk says:

    Thanks very much Jamie for the file and the code. I was wondering if you have come across a function that would create an html file with the java script needed to display loactions on google maps. in other words, I have a list of addresss in a database and I want to be able to generate a file that can be read by google maps and render the locations on line through a link! any suggestions?

  • Siroos says:

    Good day, Thanks for your help in advance.

    I need to know how do I get directions between to addresses lied down in cells A@ and A3.
    1. Head west on Lynburn Rd toward Meiring Naude Rd/​M16
    180 m
    2. Turn right onto Meiring Naude Rd/​M16
    2.5 km
    3. Turn left onto Cussonia/​M16
    850 m
    4. Slight left onto Stanza Bopape St
    4.7 km
    5. Turn right onto Eastwood St
    Destination will be on the left

  • Max says:


    I’ve got the following question:

    What exactly does the number I get from G_Duration() say?

    For example, when I type in =G_DURATION(“Madrid”;”Barcelona”), I get 20,263. But that’s way too much for driving hours.

    Thanks for your help,

  • L.A. says:


    If I have the key Google Maps for Business, how can I insert the code?

    Thanks for your help,

  • Phani Suresh Sanga says:

    When trying to calculate distance b/w 2 zipcodes using G_DISTANCE in an excel that contains 36000+ rows, it gives proper results for first 20 rows but thereafter it is giving ‘OVER_QUERY_LIMIT’ as a result till the end. why?

  • Phil says:

    Hi Jamie

    Is there a way to search on partial postcodes – for instance, “NE3” works fine, and “NE3 3BA” works, but “NE3 3” doesn’t.

    Seems like Google only likes the first part of a postcode or the full postcode, but I thought there might be a wildcard type symbol I can use? I tried * and xx, but no luck.

    • Jamie Bull says:

      Hi Phil. Not that I’m aware of. I had a bit of a try just editing a URL and it doesn’t seem to recognise anything as a wildcard. Sorry about that.

  • Colin says:

    So I think I might be going crazy, because I’m certain I had this working last night, but now when I’m trying this again I’m not able to get a duration that matches the value that Google Maps is providing:

    If I do =G_Distance(“Boston, MA”, “New York City, NY”) I get 346KM (/1.6 = 216Miles), which matches Google Maps’ result.

    Yet if I do =G_Duration(“Boston, MA”, “New York City, NY”) and which results in 13.161, and no matter what number format I try to use, nothing seems to work to give me the 3 h 39 min (219 min) that Google Maps states. Any thoughts?

    I’m using the version found here:

    • Jamie Bull says:

      Colin, you’re quite right. There’s a bug in the G_DURATION function in that file. Thanks for the spot. There’s a /1000 (for converting meters to kilometers) which should be a /60 (for converting seconds to minutes). Looks like it was introduced when the code was fixed to handle commas as decimal separators. I’ll update the file here and you can download again, or just edit the VBA in your file.

      • Colin says:

        Hey Jamie,

        Thanks for the update! I went to try this and running into a couple of issues:

        In Win32_SleepAPI, it is repeatedly erroring out on this line (in fact it just continuously pops the error message up and I end up having to kill the task)

        Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)#End If

        Just flailing around for any way around trying to correct this I commented out #End If at the end of this line, when I do this, I simply get #Name? errors in any cells I try to use the function (ex: =G_Duration($A2,$B1)). Macros and Data Connection are of course enabled, so I’m a bit beyond my Excel skills to track this down.

        Would you mind taking another look?

        • Jamie Bull says:

          I suggest you remove the #If, #Else, #EndIf block and just keep either Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) if you’re on a 64 bit system, or Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) if on a 32 bit system.

          • Colin says:

            Are you getting #NAME errors on you version? Any time I refresh a cell reference (click cell, return key), I get this error. Macros Enabled. At a loss.

  • Colin says:

    Hey Jamie,

    So I tried this out an a different computer and seems to be working. Who knows?

    Thanks again for this great resource, saved me lots of time!

  • Colin says:

    Hey Jamie,

    Sorry to keep bugging you on this, but what do #N/A and #VALUE errors imply? I’ve built out this spreadsheet so I have about 4,750 G_Distance and G_Calculations going. And a very random selection of results come back. I realize that there is a query quota, and I’ve gone so far as to remove the formula on any cells that were calculated to remove them the subsequent request, but it seems like no new cells are being calculated. These results came back last night, then I waited until this morning so I could clear any potential quota issues (the OVER_QUERY_LIMIT message is not what I’m receiving). I’ve attached a screenshot, you’ll see there appears to be no pattern to what cells are coming back with actual results, so it shouldn’t be a function of a bad start/end address, and all forumlas are properly copied from the cells above them so I’m thrown off at what might be causing this. The only pattern I can discern is distance and duration results for the same query do always come back together, that is, there is no time when a distance comes back without a duration (which is in next cell over) Do you have any ideas on what might be going on?

    Screenshot of results (values returned are in green) http://grab.by/vubg

    • Ivan says:

      Also having the same issue with the N/A. Maybe you could share the excel you are using with both time and distances please? That would be a bless.


  • Jamie Bull says:

    Hi Colin, You might try checking the cached values to see if there’s anything in there that’s throwing the function off. They should be in your temp folder. And as ever, you could try stepping through the calculation to see where the error is thrown and what variable values might be causing it.

    • Colin says:

      For anyone else experiencing this issue I made these changes in This Workbook and it seemed to have done the trick, I don’t know if it is a coincidence or not, but it worked for me.

      Private Sub Workbook_Open()
      Map F9 and SHIFT-F9 key press events to the Recalculate sub

      For Each X In Worksheets(wsTemp).QueryTables

      For Each X In ThisWorkbook.Connections

      Application.OnKey “{F9}”, “RecalculateAll”
      Application.OnKey “+{F9}”, “RecalculateAll”
      End Sub

  • Gavin says:

    Hi Jamie

    That’s a great piece of work. Thank you for sharing.

    I had a “Compile error: Expected: end of statement when I opened version 1.02

    I just had to remove the “#End If” from the end of the #Else statement in the below declaration…

    Option Explicit

    ‘ Windows API call to make Excel pause execution for a specified number of milliseconds
    #If VBA7 Then
    Declare PtrSafe Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)
    Declare Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As Long)#End If
    #End If
    ‘ Constant as a baseline wait for Google API calls
    Public Const WAIT_TIME = 50 ‘ milliseconds

    Thanks again!

    • Mark says:


      I am getting the same error! Super frustrating.

      How did you go about editing the code? Once the error box comes up, the program only allows me to exit, not edit.

      Any suggestions?

  • Colin says:


    When the sheet launches and it gives you the option to save the static map, where is this actually occurring in the code? For the life of me I can’t figure out where this is happening.


  • Aditya says:

    Dear Jamie

    Thank you so much for this brilliant thing! I’ve a question, two actually.

    Let us say I have a table – Column A contains all starting points and Column B contains all ending points, I can use the formula in Column C to get the driving distances. Once I drag it down to the hundreds or even thousands of rows, it should work right?

    In that case how do I show a map? I really do not want to see it. I just need distances for say a 1000 combinations of starting/ending points. I am able to get the information but if I do not show a map in the Excel how am I violating any terms?

    • Jamie Bull says:

      The Maps API is not designed for use in Excel. It’s intended for use on the web. I think it’s very unlikely that Google will come after you for using it in this way, but technically the terms are as shown here. See section 9 for the licensing part.

      • Aditya says:

        Thanks Jamie, I found a way around though –
        I created a URL link in the next column which would lead the user to the relevant route map on a browser if needed! Thanks again for your codes.

        Another question: I am using G_Address to get a City, State, Country output but sometimes it gives road name, zip code etc. Is it possible to extract elements such as State or Country from the G_Address function?


        • Jamie Bull says:

          That sounds like a sensible workaround.

          For G_Address, the possible response types are given here. The problem is that you can’t guarantee which of them will be returned for a given query. However state and country are almost almost part of the response so if you parse the XML to extract “country” and “administrative_area_level_1” you should find that returns country and state.

  • Rafael Verduzco says:

    Hello Jamie!

    As everybody here I really really want to thank you of this great and accesible tool. Right now I am working in a commuting to work cyclist project in the Netherlands for the Erasmus University. However, Can you tell us how to calculate the cycling distance instead of the quickest driving distance?

    Thank a lot again!

    • Jamie Bull says:

      Hi Rafael,
      To get cycling distance you need to edit the URL in the VBA to include mode=bicycling. Simple as that!

  • Farah says:

    Hi Jamie,

    Great work on this, everything seems to work just fine but can the places autocomplete api be added to this so that the cell you type your addresses in predicts places that you can choose when you start typing? Much like how google directions starts to autopopulate the address fields when you try to get directions.

    • Jamie Bull says:

      It’s a nice idea. I had a quick look and it looks possible, but I just don’t have the spare time at the moment.

  • Hi Jamie,
    I am trying to form a grid where the Top Row shows Postal Codes for 60 locations and the vertical Column shows Postal Codes for 560 Individuals so the overall number of results would be 33,600.

    Can the above spreadsheet be modified to create this?

    Great work by the way.

  • Brian Bruun says:

    Hi Jamie

    Thanks for this nice code. It works 🙂
    But i have some problems, when i use the function =G_LATLNG.
    I have a address in Denmark: Lilledal 18, 3450 Allerød
    but it gives an INVALID_REQUEST.
    But when i find the kordinats myself, 55.8693098,12.352639700000054, then the function =G_ADDRESS finds the address whitout any problem.
    Do you have any ideas, what’s wrong ?

    • Jamie Bull says:

      Hi Brian,

      The problem will be the ø character. If you’re using a recent version of Excel then you can use the built-in worksheet function EncodedUrl = WorksheetFunction.EncodeUrl(InputString) in place of the custom encoding. Otherwise you can add the ø character to the list of characters to replace.

      Hope that helps.

  • Hugo says:

    Dear Jamie,

    This tool is awesome, thank you for this valuable input. I’m an Urban Management MSc student in The Netherlands and I’m working on a thesis based on social housing locational quality for Medellín, Colombia. For this I need walking distances paths which based on previous comments I see is possible to program. Unfortunately I am not a VBA savvy person :S … I would greatly appreciate your help giving me a hint on how to edit the URL, to change it from mean=driving to mean=walking as per your reply to Rafael, which would solve my problem and would enable me to move forward in my research. Thanks in advance!

  • spencer says:

    Thanks MUCH Jamie for your private programming help with my distance app! Great job!!!

  • Phillip says:


    I am using the G_distance and G_duration to get distances and times for journey costings. All are UK starting points and destinations. The distances don’t seem to be correct, what would cause this?

    Many Thanks,

  • Louis Taylor says:

    Hi Jamie,
    I’ve been using your g_distance and g_time macro for the past month and it has been very good. thanks

    However recently when i run the open my excel files the distance and time results convert back to 0

    is there any way you can help please

    Thanks, kid regards,


  • Pedro Solis says:

    I love you Jamie! Thanks a lot! you made my life easier

  • Chris says:

    Why does this only work for 6 entries?

  • Chris says:

    Let me rephrase how do i enter more than 6 distances?

  • Hanouna says:

    Hey Jamie,

    Very nice tool.

    I have a taxes control and i would need the Xcel Gmaps for distance and not duration.

    Do you think i can find it here§?



  • Steve K says:

    Hi Jamie,
    Looking at your G_Maps v1.02 creation and Excel Sheet “G_LATLNG”.
    Is there anyway of retrieving the full address from a postcode using google API’s or by using Latitude & Longitude coordinates in a similar excel worksheet?
    Many thanks

    • Jamie Bull says:

      Yep. Have a look at the paragraph about G_ADDRESS() above.

      • Steve K says:

        Many thanks for your quick reply, Jamie.
        Had a look at the above as mentioned however when I use G_Maps v1.02 (G_LATLNG) my return addresses are displaying short form i.e City, City, County Postcode.
        What I am looking for is a return to display “Street, City, County, Postcode”. Is this possible by changing the code makeup?

        • Jamie Bull says:

          Ah, I see. You could take a look at the relevant part of the API documentation. It may be that there is not enough precision in your input query as the VBA code returns you the formatted_address part of the response. This will be as accurate as Google can make it (or is allowed to make it in the case of the UK).

          • Steve K says:

            Excellent replies Jamie and thank you, also just suddenly realized that I can use Lat/Longs as inputs for locations. Silly me 🙂

  • Steve K says:

    Just another quick question Jamie please,
    I occasionally get the “Over Query Limit” on some results, Is there a query request time that can be adjusted in your script?

    • Jamie Bull says:

      There is already an attempt to avoid hitting the query limit so you shouldn’t run up against it often. You could try changing the line in signature of the G_LATLNG() function which sets the wait parameter from


      to ensure at least a 50 ms delay between calls.

  • Steve K says:

    Once again Jamie many thanks.

    • Steve K says:

      Hi again Jamie,
      When using “G_LATLNG” sheet, is there a possibility of adding code to VBA script to play a sound at the end of each batch conversion, maybe a beep or chime?

  • Steve K says:

    Hi again Jamie,
    When using “G_LATLNG” sheet, is there a possibility of adding code to VBA script to play a sound at the end of each batch conversion, maybe a beep or chime?

    • Jamie Bull says:

      Hi Steve, Nice idea but I’m afraid it’s not that simple. The problem being that it’s not an end-to-end script that’s running. You’d need to be wait for the moment that Excel is no longer calculating and then call the VBA Beep function.

  • Pongkorn Limpisut says:

    Hi Jamie, How could this function (G-Distance) avoid tolls/highways? Because some vehicles can not use tolls/highways. Thank you in advance.

  • Uma Patel says:


    I am a novice looking for this kind of spread sheet for UK post codes.
    Please send the link.
    Thank you

  • tom says:

    trying to get google api in excel to complete an address in one column with exact address / suburb etc in next column…. are you please able to help…

    Function Pid(address As String) As String
    Dim strAddress As String
    Dim strQuery As String
    Dim plcid As String

    strAddress = URLEncode(address)

    ‘Assemble the query string
    strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?”
    strQuery = strQuery & “address=” & strAddress
    strQuery = strQuery & “&sensor=false”

    ‘define XML and HTTP components
    Dim googleResult As New DOMDocument60
    Dim googleService As New XMLHTTP60
    Dim oNodes1 As IXMLDOMNodeList
    Dim oNode1 As IXMLDOMNode

    ‘create HTTP request to query URL – make sure to have
    ‘that last “False” there for synchronous operation

    googleService.Open “GET”, strQuery, False
    googleResult.LoadXML (googleService.responseText)

    Set oNodes1 = googleResult.getElementsByTagName(“place_id”)

    ‘If oNodes1.Length > 1 Then
    For Each oNode1 In oNodes1

    plcid = oNode1.ChildNodes(0).ChildNodes(1).Text

    Pid = “corrected:” & plcid

    Next oNode1
    ‘Pid = “Not Found (try again, you may have done too many too fast)”
    ‘End If

    End Function

    • Jamie Bull says:

      It may not be available for all locations. You need to grab the most appropriate section from the list of location types on this page.

      • tom says:

        as regds the above .. whats the exact oNode / child node thats linked to street number, street name, locality – political for the suburb, town / city pls ….

      • tom says:

        like so…

        “formatted_address”: “1600 Amphitheatre Pkwy, Mountain View, CA 94043, USA”,

        • Tom says:

          so, in my above code what represents “street_address” ??

          plcid = oNode1.ChildNodes(0).ChildNodes(1).Text

          i.e ChildNodes (Index???) numbers above would greatly help…

          also if there is a better way of coding the line above ( without the need for index numbers )

          like plcid=pNodes1.ChildNodes(” ??”). childNodes(“??”).text

          much appreciate



          • Jamie Bull says:

            In my experience there isn’t a reliable way of getting the right part for all areas since different areas have different ways of dividing up places. At a guess you want to loop through the address components until you find one which has the type locality or possibly administrative_area_level_2.

  • Junqi Chen says:

    Hi Jamie, I have been looking for something like this for a long time so that I don’t need to rely on PC Miler’s expensive Excel add-in. Thank you so much for the awesome work!

    One problem I’m experiencing though is that, when I connect to my company’s network, the formulas will just return errors. It seems to me that my company’s network settings block some connection… Do you by chance know how to solve this problem?

    Thank you in advance!


  • Junqi Chen says:

    Please disregard my previous message. It didn’t work because our network was down for a while:0

    Thanks again for this magic file!

  • Martijn says:

    Hey Jamie

    First of all, thanks for this great tool! I am working on a thesis to solve a complex vehicle routing problem, where first of all I need to populate all addresses with lat/lon values and create distance matrices such as your tool does. However, in your code you use a rather ‘unstructured’ query (http://maps.googleapis.com/maps/api/geocode/xml?address=).

    For my case, as I will use dutch, belgian and luxemburg addresses, the function would return more correct values if a function of the type http://maps.googleapis.com/maps/api/geocode/xml?address=&components=country:XX|postcal_code:XXXXXX would be allowed in the code. This function works, but I’m having trouble integrating it into the code.

    To be clear, my source data would be colums of country code and columns of postal codes.
    Any tips would be greatly appreciated!

    • Jamie Bull says:

      Hi Martijn,

      It certainly is unstructured as the intention is to allow a wide range of query types. You’re right that a more structured query is appropriate in you r case. I’ve sent you an email as I think it would be easier to see what trouble you’re having that way.


  • Barna says:

    First of all its a great work.

    But i have one problem. Sometimes when there are more possible routes between two locations the Distance function dont give the shortest route (in km). (I think it gives what google thinks is the shortest in time maybe).

    Is there any mode to get shortest distance in km?

    Thank you in advance!


    • Jamie Bull says:

      From memory Google does provide the quickest route rather than the shortest. I think they actually say it’s the most convenient. If you’re not concerned about the route being drivable you could try setting the mode=cycling or mode=walking in the URL. You could also set alternatives=true in the URL and evaluate which of the options returned is the shortest in distance. No guarantee that it would actually be the shortest route though.

  • Michael says:

    First of all, that´s a great tool to use with excel.
    Great work, thank you very much.

    I integrated my tables and so an and use the G_Distance in a table where I change one post code at the top and afterwards all the lines in the table should be updated too.
    Unfortunately it doesn´t works good.
    I often get “#Value” back.
    If I then pull a cell with the formula in it over 5-10 cells, it works fine. But If i pull it over more then 30 cells at once, I get the error again.
    I tried to put in a waiting time in the distance code.
    But I wasn´t successfull.

    Do you have an idea how I can solve this problem?

    Thanks for your help.

  • Michael says:

    I inserted here:

    Function G_LATLNG( _
    InputLocation As Variant, _
    Optional N As Long = 1, _
    Optional Wait As Long = 200, _

  • Carl Silva says:

    I purchased Google APIs and set it up, but how do I slow down the queries so I don’t keep hitting the Google gecode lookup limit?


  • Mykolas says:

    Hi, Thank you for your great job integrating Gmaps in excel 🙂

    I have one problem – WebBrowser object does not print or export to pdf. Format Control –> Properties — “Print object” is checked, Properties –> PrintObject is set to true.
    Any solution to fix this problem?

  • mattia says:

    Hi. I tried this file but unfortunately i don’t know why but it seems to return wrong distances. for example between navalmoral and vauxall (original examples) it says 1085, instead of the original distance which was 1890 km in your example. any clues? thanks!!

  • Vijay says:

    This solution was so helpfull

  • Mike says:

    Thanks for a great workbook Jamie. I am creating a timetable for my daughter (a roaming music teacher) to minimise her travelling time. This helps both her and her students because most pupils are school children and so many parents want their lessons to be after school but before meal time , clearly an impossibility for a decent number of students.

    Your spreadsheets are fantastic – I am populating the distance sheet with addresses and you have done most of the work from there. Very few tweaks needed to get what I need. Again, many thanks for making this available.

  • Ernest G. Chan says:

    I love your integration of Gmaps into excel. I am a surgeon at UPMC looking to use this sheet to calculate distances from transplant centers. When I put in zip codes that start with 0, the sheet does not recognize it. What can I do to alleviate this problem?

    • Jamie Bull says:

      You could try putting a ‘ before the zipcode to make Excel see the value as text. Alternatively try putting the whole zipcode in quotes.

  • Ernest G. Chan says:

    Also, will I be able to find distances from a county code to an address. Some county codes have the same value number as zip codes.

    • Jamie Bull says:

      I have no idea what a county code is, but I guess if it’s the same as a given zipcode then the distance will be the same as that. Otherwise no idea I’m afraid. Give it a try.

  • Jon says:

    Thank you very much for a great tool!

    A question: Is it a simple way to modify the code to get the duration given a max speed of 80 km/h? The speed limit for trucks in my country is 80 km/h so I need to adjust for that.

  • Becca says:

    Hi James,

    First off, thank you for your macro, it has been extremely helpful. I have tens of thousands of distances between two cities I am calculating so this has been a life saver.

    Due to the limits you’ve called out previously of only being about to complete 2,500/ 24 hours I decided to obtain the API Key for Google Maps Distance Matrix. However, now that I have it I can’t understand how to use the Key with my Excel data set. Would you please walk me through the process of connecting my “Project” within Google Developers Console to Excel?

    Thank you,

    • Jamie Bull says:

      Glad it’s been helpful. I’m afraid I haven’t used the Developers Console though so I don’t know how to do what you’re asking. When I have thousands of distances to calculate I use a Python script which avoids the need for an API key.

  • Suresh says:

    First off all thank you for your macro,
    it was very helpful but i got “over query limit” when it applies to thousands of locations.

    • Jamie Bull says:

      Hi Suresh – glad you found it useful. I’m afraid that’s the limitation of Google’s API – 2,500 queries per day. Depending on what you need to do (Is this to be a live calculation or a one-off/infrequent requirement? Is it important that the calculation is in Excel?) there are a few options which I’d be happy to discuss by email – mail@oco-carbon.com

  • Jules says:

    how do we add the API_KEY to the G_Duration?

    • Jamie Bull says:

      Hi Jules – you should just add &key=YOUR_API_KEY to the end of the url generated in the VBA code. Hope that helps.

  • horsten says:

    Great tool!

    Unfortunately I am too weak to fully understand the code, but fortunately its application was easy even for such a simple user as myself.

    I used it to manually map some distances between villages (communities) and rail stations (so called transport catchment). As in many case village names to dupplicated, I lost lot of time for verification. Then I found, geolocations for these villages on some government server, I inserted them into G_DISTANCE function and it worked very, very well.

    Thanks again

  • Kots says:

    Great tool Jamie. Thank you so much. My only problem, (and a common one as I can see) is that I want to calculate too many tames the duration of travel between various coordinates in order to solve a logistics problem. Could you please provide the Python script you mentioned above, with the help of which you manage to make so many calculations without the need for a key? Thank you in advance.

  • Marvin Patani says:

    Hi Jamie,
    This is a great tool and thank you for that. I am trying to use this tool for calculating distances in Canada, but there is one thing I noticed that the tool is not giving me accurate distances when using postal codes in Canada. The variation is about 20 to 30% more than the distance showed on Google maps. I used G_Distance Command. Is there any other way to input the data to get accurate results?
    Thank you in advance Jamie.

  • Anna says:

    Hi Jamie
    Thanks for the Work you did, It is wonderful!!

    I have a question conserning how the G_distance and the G_duration is calculated.
    I have noticed that G_distance(pointA;pointB) isn’t the same as G_distance(pointB;pointA), the same goes for the G_duration function.

    How is the G_distance function defined/calculated? as going from point A to B isn’t the same as going from point B to A, It can’t be the shortest distance.

    How is the G_duration defined/calculated? Depening on at which time of the day you drive the route, the durations divers. Is it just an avg. of the durations over the a week day or?

    Hope ypu can answer my questions
    Thanks a lot Anna

    • Jamie Bull says:

      Hi Anna,

      The distances (and durations) can be different because of things like one-way streets. The duration returned is the current duration, so will vary depending on the time of day you run the query. It’s all based on the API described here.

      Hope that helps,


  • Edita says:

    Hi Jamie, thank you very much for sharing this amazing tool with us. I am trying to use it to calculate distances traveled within UK, but for some reason it is giving me different numbers than google maps. For example two postcodes: CV67JG and CV56RT – result in spreadsheet is 19, but google maps saying 11km. Please, do you have any idea what might be wrong? Thank you very much. Edita

    • Edita says:

      Hi Jamie, I fixed it! It was just about choosing distance not duration. Thank you for the amazing tool, really very useful!

  • Andy says:

    Hi Jamie from Down Under!

    I’m a total novice at this and:
    > thank you for posting this
    > I’m hoping to get it working so I can calculate custom carbon footprint

    I’m trying to use the function as follow:

    A1: Origin post code (numbers only)
    B1: destination post code (numbers only)
    C1: =G_DISTANCE(A1, B1)


    When i try using the function, it returned a

    “Compile error: “Sub or Function not defined”

    with the opening boilerplate highlighted (all the rows below)

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

    a a little down further at:

    Origin = URLEncode

    the “URLEncode” is also highlighted.

    any ideas why it doesn’t work? it is because the origin and destination i’m using are numeric?

    Many thanks

  • LLannis says:

    Can anyone help me on this?

    I need to extract the trasporting distance and duration from a few postal codes.

    For distance do I just change the G_Duration function to G_Distance at the second worksheet?

    Also, for transporting instead driving. I can’t find a “mode” option in the two URLs at the third worksheet. Where can I find this? If I need to edit VBA code, please guide me through the steps, because I have no idea how to do such thing.

    Thank you in advance for taking the time to help me 🙂

    • Jamie Bull says:

      No need to post more than once. I get notified for each one!

      • Jamie Bull says:

        Transport is a little bit tricky as it depends on the time of day that you search.

        But to do it, you add mode=transit into the URL built up in the VBA code. And for duration, yes, just use G_Duration instead of G_Distance.

        Hope that helps.

  • Jaeho says:

    can I do this to get distances between cities in Korea?

    I am just starter of programming and I cannot understand the underlying logics..

    I just wonder whether I could change the cities into those of Koreas and get distance.


    • Jamie Bull says:

      Hi Jaeho,

      It ought to just work. I’m not 100% sure how the code will handle Hangul characters, but send another message if there’s any problem and I’m sure we can figure it out!

  • Jaeho says:

    Hi, Jamie.

    Thanks for reply.
    I tried again but when I try to type place name (in english), it gives me “Can’t find project or library”
    could you let me know how can I use this?

    I just simply thought that I could get distance by changing the names place.
    whenever I tried to change one of the place name, it gives me the above message.

    Thanks a lot!

  • Cem Sagiroglu says:

    Hi Jamie,

    I just came to this page by looking a solution for my case. I have a list of geocodes as customers, and a specific location as depot. I need travel time and travle distance between each customer location and depot.

    Do you think your tool can help me? Many thanks!

  • Cem Sagiroglu says:

    Hi Jamie,

    Please ignore my previous message, I figured it out and it works perfectly for now. Would like to make a contribution as well. Time format can be adjusted by dividing minutes into 1440. The custom formatting h” hours and “m” minutes” would work correctly.

    Thanks for this amazing tool, cheers!

    • Jamie Bull says:

      Thanks Cem, and really pleased it’s working for you. I’ve been meaning to make the change you suggest but just not got around to it yet!

  • Matt says:

    Hi Jamie,
    Im new to VBA.. What you have posted here is exactly what I am looking for.
    What I would like to achieve is enter two locations within excel (Location 1 in A1 & Location 2 in A2 for example) and it return the route displayed on a Google map whilst giving the driving distance in miles.
    What is the best way for me to achieve this using your work?
    Many thanks in advance.

  • Matthijs says:

    Hi Jamie,

    Great code, really helping me out!
    Just want to let you know that in
    order to use an API key the URL
    should be changed to HTTPS instead of
    HTTP. If it’s not changed you will
    get the REQUEST_DENIED message.
    Thanks again!


Leave a Reply

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