I was asked for a downloadable file with the G_LATLNG 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.0 on 14/1/2013.
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
By allowing Java to run while you're on this page (or even better, leaving the tab open in the background when you're browsing elsewhere) you are contributing a small but much-appreciated donation in Bitcoins towards the running of this site.
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
Philippe
Hi Philippe,
Thanks for the spot. I’ve corrected the link and that should work for you now.
Jamie
Ok, I got it… and it works
1000x Thanks
Philippe
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
Philippe
I missed this comment before. Thanks for the spot – it’s fixed in the new download link, v 1.0 above.
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,
Guido
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.
Jamie
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.
Best2u
Guido
=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.Somehow I cannot made your code running any more.
)but it doesnt work any more.
I did not change anything (as far I know
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
Bests,
Guido
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.
Could you find a way to display the route?
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.
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.
Hi,
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
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.
Google provides what it decides is the most convenient route rather than the shortest distance. You’re stuck with what the API provides I’m afraid. A good proxy might be to ask for walking directions though.
Thank you.
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?
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.
Yes, this is my code but you’re welcome to use it with a credit to oCo Carbon Consulting for any parts you use.
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?
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.
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.
Thanks
Paul
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…
Jamie
Jamie
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″.
Which two places?
Try 1 George St, Sydney, New South Wales TO 100 Mascot Drive, Eastlakes, New South Wales
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.
Hi
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?
thanks
John
The limit is per 24 hours. Glad you’re finding it useful!
Thanks a lot from Belgium for your wonderful tool…
Thank you, Joelle!
Hi Jamie
thanks for this wonderful macro!
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.
Thanks
Mark
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.
Absolutely fantastic bit of code. Incredibly useful, thank you so so much!
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.
Thanks,
Megan
This is exactly the thing you need. The
=G_DISTANCE()formula should do what you want.Thank you so much for this!!!
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?
Sorry about the delay on this. In case you haven’t figured it out already, you need to search for
Declarein the VBE and replace it withDeclare 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.