# Phil's Hiking Resources

## OSGB Lat/Long Conversion

Visual Basic Macro Code for converting Ordanance Survey OSGB grid references to OSGB36 latitude / longitude in MS Office 2000 software.
(Note the majority of applications use the WGS84 datum – so you might find Phil Brady’s Excel Spreadsheet…  more useful.) Excel Conversion Spreadsheet (MS Excel 2000) for converting OS grid references to latitude / longitude NB: this spreadsheet contains macros which are virus free. Access Conversion Database (MS Access 2000) for converting OS grid references to latitude / longitude.

The Code used…
``` 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.000000001 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

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

Function texteast(ZN As String)
If ZN = “SV” Or ZN = “NL” Or ZN = “NF” Or ZN = “NA” Then
texteast = 0
ElseIf ZN = “SW” Or ZN = “SR” Or ZN = “SM” Or ZN = “NW” Or ZN = “NR” Or ZN = “NM” Or ZN = “NG” Or ZN = “NB” Or ZN = “HW” Then
texteast = 1
ElseIf ZN = “SX” Or ZN = “SS” Or ZN = “SN” Or ZN = “SH” Or ZN = “SC” Or ZN = “NX” Or ZN = “NS” Or ZN = “NN” Or ZN = “NH” Or ZN = “NC” Or ZN = “HX” Then
texteast = 2
ElseIf ZN = “SY” Or ZN = “ST” Or ZN = “SO” Or ZN = “SJ” Or ZN = “SD” Or ZN = “NY” Or ZN = “NT” Or ZN = “NO” Or ZN = “NJ” Or ZN = “ND” Or ZN = “HY” Or ZN = “HT” Then
texteast = 3
ElseIf ZN = “SZ” Or ZN = “SU” Or ZN = “SP” Or ZN = “SK” Or ZN = “SE” Or ZN = “NZ” Or ZN = “NU” Or ZN = “NR” Or ZN = “HZ” Or ZN = “HU” Or ZN = “HP” Then
texteast = 4
ElseIf ZN = “TV” Or ZN = “TQ” Or ZN = “TL” Or ZN = “TF” Or ZN = “TA” Then
texteast = 5
ElseIf ZN = “TR” Or ZN = “TM” Or ZN = “TG” Then
texteast = 6
Else
texteast = -99999
End If
End Function

Function textNORTH(ZN As String)
If ZN = “SV” Or ZN = “SW” Or ZN = “SX” Or ZN = “SY” Or ZN = “SZ” Or ZN = “TV” Then
textNORTH = 0
ElseIf ZN = “SR” Or ZN = “SS” Or ZN = “ST” Or ZN = “SU” Or ZN = “TQ” Or ZN = “TR” Then
textNORTH = 1
ElseIf ZN = “SH” Or ZN = “SJ” Or ZN = “SK” Or ZN = “TF” Or ZN = “TG” Then
textNORTH = 3
ElseIf ZN = “SC” Or ZN = “SD” Or ZN = “SE” Or ZN = “TA” Then
textNORTH = 4
ElseIf ZN = “NW” Or ZN = “NX” Or ZN = “NY” Or ZN = “NZ” Then
textNORTH = 5
ElseIf ZN = “NR” Or ZN = “NS” Or ZN = “NT” Or ZN = “NU” Then
textNORTH = 6
ElseIf ZN = “NL” Or ZN = “NM” Or ZN = “NN” Or ZN = “NO” Then
textNORTH = 7
ElseIf ZN = “SM” Or ZN = “SN” Or ZN = “SO” Or ZN = “SP” Or ZN = “TL” Or ZN = “TM” Then
textNORTH = 2
ElseIf ZN = “NF” Or ZN = “NG” Or ZN = “NH” Or ZN = “NJ” Or ZN = “NK” Then
textNORTH = 8
ElseIf ZN = “NA” Or ZN = “NB” Or ZN = “NC” Or ZN = “ND” Then
textNORTH = 9
ElseIf ZN = “HW” Or ZN = “HX” Or ZN = “HY” Or ZN = “HZ” Then
textNORTH = 10
ElseIf ZN = “HT” Or ZN = “HU” Then
textNORTH = 11
ElseIf ZN = “HP” Then
textNORTH = 12
Else
textNORTH = -99999
End If
End Function

## SQL code for Database query calculations

``` SELECT [OSGB Grid References].[zn], [OSGB Grid References].[Eastings(5)], [OSGB Grid References].[Northings(5)], texteast([zn])*100000+[eastings(5)] AS East, textnorth([zn])*100000+[northings(5)] AS North, degrees(Lat([East],[North])) AS deglat, degrees(Lon([East],[North])) AS deglon, IIf([deglat]>0,"N","S") & " " & (Abs(TRUNC([deglat]))) & "° " & (TRUNC((Abs([deglat])-Abs(TRUNC([deglat])))*60)) & "' " & (((Abs([deglat]))*3600)-(TRUNC((Abs([deglat])-TRUNC([deglat]))*60)*60)-(TRUNC([deglat])*3600)) & "''" AS [lat in text], IIf([deglon]<0,"W","E") & " " & (Abs(TRUNC([deglon]))) & "° " & (TRUNC((Abs([deglon])-Abs(TRUNC([deglon])))*60)) & "' " & (((Abs([deglon]))*3600)-(TRUNC((Abs([deglon])-TRUNC([deglon]))*60)*60)-(TRUNC([deglon])*3600)) & "''" AS [long in text] FROM [OSGB Grid References]; ```

Author: Phil Newby