VBA Function Collection for converting Eastings and Northings to Latitude and Longitude

Some years back we hired a young lad by the name of Iain Brodie on a temporary contract – The week before I had been at an ESRI conference which had extensively discussed Web Mapping and  a speaker had demonstrated showing points in Google Maps. It was clear to me that the Google Maps url would accept and zoom to coordinates if those coordinates passed to it were Longitude and Latitude. Where I work there are significant numbers of datasets that use old Ordnance Survey UK specific Eastings and Northings coordinate system. Ordnance Survey actually set out the mathematics of conversion to Lat and Long on this page even detailing coded functions albeit in Javascript.

http://www.movable-type.co.uk/scripts/latlong-gridref.html

I specifically wanted to dynamically convert using Visual Basic for applications (specifically from MS Access). When Iain arrived it was clear that he was useful with computers and so I tasked him with finding VBA code from the internet. Between us we managed to get it working and I still regularly use the function set today to give users of applications a map in Google Maps. It really is a very nice quick tool that gives users quick access to maps for – you bet zero cost. My favourite price. We originally had it working with Google Earth but I only use it with Google Maps now.

```Function PHId(North1, N0, aFo, PHI0, n, bFo) PHI1 = ((North1 - N0) / aFo) + PHI0 M = Marc(bFo, n, PHI0, PHI1) PHI2 = ((North1 - N0 - M) / aFo) + PHI1 Do While Abs(North1 - N0 - M) > 0.00001 PHI2 = ((North1 - N0 - M) / aFo) + PHI1 M = Marc(bFo, n, PHI0, PHI2) PHI1 = PHI2 Loop PHId = PHI2 End Function```

``` Function Marc(bFo, n, P1, P2) Marc = bFo * (((1 + n + ((5 / 4) * (n ^ 2)) + ((5 / 4) * (n ^ 3))) * (P2 - P1)) - (((3 * n) + (3 * (n ^ 2)) + ((21 / 8) * (n ^ 3))) * (Sin(P2 - P1)) * (Cos(P2 + P1))) + ((((15 / 8) * (n ^ 2)) + ((15 / 8) * (n ^ 3))) * (Sin(2 * (P2 - P1))) * (Cos(2 * (P2 + P1)))) - (((35 / 24) * (n ^ 3)) * (Sin(3 * (P2 - P1))) * (Cos(3 * (P2 + P1))))) End Function Function lon(East1, North1) a = 6377563.396 b = 6356256.91 F0 = 0.9996012717 E0 = 400000 N0 = -100000 PHI0 = 0.855211333 LAM0 = -0.034906585 aFo = a * F0 bFo = b * F0 e2 = (aFo ^ 2 - bFo ^ 2) / aFo ^ 2 n = (aFo - bFo) / (aFo + bFo) InitPHI = PHId(North1, N0, aFo, PHI0, n, bFo) nuPL = aFo / ((1 - (e2 * (Sin(InitPHI)) ^ 2)) ^ 0.5) rhoPL = (nuPL * (1 - e2)) / (1 - (e2 * (Sin(InitPHI)) ^ 2)) eta2PL = (nuPL / rhoPL) - 1 M = Marc(bFo, n, PHI0, InitPHI) Et = East1 - E0 X = ((Cos(InitPHI)) ^ -1) / nuPL XI = (((Cos(InitPHI)) ^ -1) / (6 * nuPL ^ 3)) * ((nuPL / rhoPL) + (2 * ((Tan(InitPHI)) ^ 2))) XII = (((Cos(InitPHI)) ^ -1) / (120 * nuPL ^ 5)) * (5 + (28 * ((Tan(InitPHI)) ^ 2)) + (24 * ((Tan(InitPHI)) ^ 4))) XIIA = (((Cos(InitPHI)) ^ -1) / (5040 * nuPL ^ 7)) * (61 + (662 * ((Tan(InitPHI)) ^ 2)) + (1320 * ((Tan(InitPHI)) ^ 4)) + (720 * ((Tan(InitPHI)) ^ 6))) lon = (LAM0 + (Et * X) - ((Et ^ 3) * XI) + ((Et ^ 5) * XII) - ((Et ^ 7) * XIIA)) End Function Function lat(East1, North1) a = 6377563.396 b = 6356256.91 F0 = 0.9996012717 E0 = 400000 N0 = -100000 PHI0 = 0.855211333 LAM0 = -0.034906585 aFo = a * F0 bFo = b * F0 e2 = (aFo ^ 2 - bFo ^ 2) / aFo ^ 2 n = (aFo - bFo) / (aFo + bFo) InitPHI = PHId(North1, N0, aFo, PHI0, n, bFo) nuPL = aFo / ((1 - (e2 * (Sin(InitPHI)) ^ 2)) ^ 0.5) rhoPL = (nuPL * (1 - e2)) / (1 - (e2 * (Sin(InitPHI)) ^ 2)) eta2PL = (nuPL / rhoPL) - 1 M = Marc(bFo, n, PHI0, InitPHI) Et = East1 - E0 VII = (Tan(InitPHI)) / (2 * nuPL * rhoPL) VIII = ((Tan(InitPHI)) / (24 * rhoPL * nuPL ^ 3)) * (5 + (3 * ((Tan(InitPHI)) ^ 2)) + eta2PL - (9 * ((Tan(InitPHI)) ^ 2) * eta2PL)) IX = ((Tan(InitPHI)) / (720 * rhoPL * nuPL ^ 5)) * (61 + (90 * ((Tan(InitPHI)) ^ 2)) + (45 * ((Tan(InitPHI)) ^ 4))) lat = (InitPHI - ((Et ^ 2) * VII) + ((Et ^ 4) * VIII) - ((Et ^ 6) * IX)) End Function Function degrees(radians) degrees = 180 * radians / 3.14159265358979 End Function ```

```Function trunc(value) If value > 0 Then trunc = Int(value) Else trunc = Int(value + 1) End If End Function```

And here is the code the onclick function of a button called Command01 and it pulls from a screen that has an eastings and northings field on it and which has a Sitename field.

```Dim Llatitude As Double Dim Llongitude As Double Dim strSitename As String```

``` Llatitude = degrees(lat([Eastings], [Northings])) Llongitude = degrees(lon([Eastings], [Northings])) - 0.0015 strSitename = Me.Sitename Dim strlatlong As String strlatlong = Llatitude & ",+" & Llongitude 'Here I have two options - the first places a marker on the map - as far as I can tell - the marker is only available within google with the side panel displayed as well. The second shows the map centered on the requested location but without any markers. Choose one Command01.HyperlinkAddress = "https://maps.google.com/maps?q=" & strlatlong & "+(" & strSitename & ")&z=18&iwloc=near&hl=en&ll=" & strlatlong ```

`Command01.HyperlinkAddress ="https://www.google.com/maps/@" & Llatitude & "," & Llongitude & ",18z?hl=en"`

And for Developers wanting to get into more detail here is the url for more information on passing parameters to the google maps url.