How to Build in a Simple Distance Checker Between Two UK Postcodes Int…

This method will only calculate the distance in Kilometres (divide the consequence by 1.609344 for miles) “as the crow files” but it is extremely useful when, for example, you are marketing everyone within a fixed radius.

So how do we do it? The method uses the “Haversine” formula which assumes that the Earth is a sphere. OK we know that the Earth is not a perfect sphere, but when you are checking between two map co-ordinates it is more than accurate enough for these purposes.

OK so without going into the technicalities of how the formula works, we shall get straight on with the coding requirements for VBA.

You will need a table in your application containing all the UK postcode variations and the X & Y axis co-ordinates. We would be very happy to provide a copy.

The following VBA Code attached to the “Calculate Distance” click event is used in a simple form where the user enters the start and end postcodes and the distance is calculated.

txtPostCodeStart and txtPostCodeEnd are blank text boxes for entering your criteria.

txtStartLat, txtEndLat, txtStartLong and txtEndLong are text boxes that are automatically populated by the”after update” event on the text boxes “txtPostCodeStart” and “txtPostCodeEnd” using simple RecordSet vba programming.

Private Sub caldistance_Click()

On Error GoTo Err_caldistance_Click

If Me.TxtPostCodeStart = “” Then

MsgBox (“Please go into a Start Post Code”)

Exit Sub

End If

If Me.TxtPostCodeEnd = “” Then

MsgBox (“Please go into an End Post Code”)

Exit Sub

End If

Distance = (Sin((Me.TxtEndLat * 3.14159265358979) / 180)) * (Sin((Me.TxtStartLat * _

3.14159265358979) / 180)) + (Cos((Me.TxtEndLat * 3.14159265358979) / 180)) *  _

((Cos((Me.TxtStartLat * 3.14159265358979) / 180))) * _

(Cos((Me.TxtStartLong – Me.TxtEndLong) * (3.14159265358979 / 180)))

Distance = 6371 * (Atn(-Distance / Sqr(-Distance * Distance + 1)) + 2 * Atn(1))

Me.TxTDistance = Distance

Exit_caldistance_Click:

Exit Sub

Err_caldistance_Click:

MsgBox Err.Description

begin again Exit_caldistance_Click

End Sub

Whilst we have demonstrated a simple form, the basic formula is there to be used in a large number of different ways. We hope you will find this as useful as we have!

we have!