Edit: See an updated version of this geocoding function.

As with the previous post, I’ve been writing some code lately using Google tools. This post is a rewrite of some heavier code for geocoding location data. The aim is to get latitude and longitude which is then used to display points on a Google Map (more on that in a later post). It’s used as part of a dashboard which can show data about various buildings to give an obvious cue to the user as to which one they are currently viewing.

The function for Excel takes a single argument, a place name or postcode. It then grabs the latitude and longitude from Google and returns them to 4 places separated by a comma and a space as follows:

I’ve named this UDF G_LATLNG and the syntax for using it is =G_LATLNG(Input).

Note that the Google Maps API terms of service state that you can only use the API when displaying a Google Map, so using this code without doing so is at your own risk. Look out for a future post here on how to do that.

The table shows the same ways of inputting data are available as with G_DISTANCE.

You’ll need to install this as a code module in your workbook, and to 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 […]

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 […]

Leave a Reply

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