Convert Easting and Northing Coordinates to Latitude and Longitude using VBA

Many Government bodies and agencies  in the UK still publish postcode data using Easting and Northing co-ordinates.  Although this may be useful for some, many software systems, such as Tableau, don’t natively support this and prefer the more universally used Latitude and Longitude coordinate system.

Converting to and from these coordinate systems is not a perfect science.  The process is an approximation as it attempts to convert 2-dimensional mappings onto a 3-dimensional object, along with taking account of some of the obscurities of the OSGB36 grid system.  You can read more about the UK’s grid system at the Ordinance Survey.

There are a few online converters out there, but nothing seemed particularly relevant for batch converting stuff in Excel or Access.

I did, however, find a SQL UDF written by Sandy Mottram at Carra Lucia that, itself, had been converted from a javascript file hosted on a caving club’s website.

Basically, other than the time taken to convert and error check, I can’t really take credit for this.

You can either refer to the function as part of a VBA routine or use it in a worksheet cell.  Call in the usual way:

=NEtoLL(521034,169028,”Lat”) for latitude
=NEtoLL(521034,169028,”Lng”) for longitude

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Leave a reply by filling in your details below, or connect automatically with social media:

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