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.

Google Map URLs

VB Function for Zooming to location in Google Maps at 23 March 2014

mapGoogle recently slightly altered their URL to link to Google Maps. This code passes eastings and northings from fields into a function that calculates Longitude and Latitude and then passes those calculated coordinates to the google maps url for display

Note you will need to have implemented the functions that calculate lat and long for this to be useful.

Private Sub Command01_Click()

On Error GoTo Err_Command01_Click

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

'From 24 March Google changed their hyperlink address this is the new one

Command01.HyperlinkAddress = "https://maps.google.com/maps?q=" & strlatlong & "+(" & strSitename & ")&z=18&iwloc=near&hl=en&ll=" & strlatlong

Exit_Command01_Click:

Exit Sub

Err_Command01_Click:

MsgBox Err.Description

Resume Exit_Command01_Click

End Sub

 

Nubuilder Pro 3 – Example Web Application Deletion and Creation – Race Number system

Objective :

Create a simple online web application that can be used to type in race numbers. Once a number is typed in the time will automatically posted to a field. This could be used to manually record numbers of competitors coming across a finish line on mobile phones provided all phones have access to a web browser and they have 4G connection or wifi connection.

First go to the administrator url and sign in with the Global Administrator username and password to get into the design form.

The format of this is

If you wish to delete a project go to the delete tab within the site select the site you wish to delete then hit Delete

It will then present you with a sanity check window

Pressing enter again you get a site administration check box again

Adding a New Site in Nubuilder Pro
Go back to the opening screen and this time go to the Add Tab.

You should be presented with an Add Dialog window.
Type in the name you wish your new project to be here I call it racenumber – note the name has to be all lower case.
Hit create after which there will be a short delay while the site is created.

There will be a short delay while the site is created

You will then be presented with the Administration panel with your new site created something like the following

Now click on the site you are interested in this case race number which will take you to the login panel

Now enter the global username and password that you were originally handed out.

Once logged in you should be presented with the following screen.

Hitting the database button will take you to the php-myadmin control panel

The First step is to create a table to hold all the data

This can be done buy using the create table button on the bottom left.

Here I am creating a table called T002Time to which I add three columns. I set the AI which stands for Auto Increment to yes and I set this field as an index I also add a time field and set the default to current_time – note that in phpmyadmin fields will not necessarily accept default functions but Time variable type accepts current_time. Current_time function returns date and time.

Here I have forgotten the racenumber field so I create some SQL to add this in.

Now we want to go back to the Nubuilder web application panel

Hit the form wizard – now select the T002Time drop down in the table name and use the add to Browse column and add to Form Objects button to transfer everything across to the respective columns as follows.

This will run and in the background create a form which you can see by going through the User Home screen as follows

Now lets remove some fields we no longer need
Go to objects we can see that three columns relate to to the T002Time(form) Lets do several things
1. Remove DTime from the form
2. Remove the Race FKID from the form
3. Change the label of the home Form button T002Timebtn to Race Numbers

Firstly in the objects button go into the two lines you wish to remove from the form

and when the object details appear delete them.

After a warning message you should be presented with the new list of object without the object recently deleted.

The new form should look something like this.

That’s 1 and 2 done now lets try and get that button bigger.

Now in here I am going to remove the RACEFKID and make the DTIME much wider

Now we see

Now lets see if we can move this field to the centre of the screen this can be done while in global administration mode by clicking on the following button

Reposition then add users and that should be you.