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

 

Then, how do we do it? The method uses the “Haversine” formula which assumes that the Earth is a sphere. Okay, so we know the Earth isn’t a perfect sphere, but when you check between two map coordinates, it’s more than enough for these purposes.

 

Ok, without getting into the technicalities of how the formula works, let’s move on to the coding requirements for VBA.

 

You will need a table in your application containing all the variations of UK Postcodes and the coordinates of the X and Y axes. We will be happy to provide you with a copy.

 

The following VBA code attached to the “Calculate Distance” click event is used in a simple form where the user enters the starting and ending zip codes and the distance is calculated.

 

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

txtStartLat, txtEndLat, txtStartLong and txtEndLong are textboxes that are auto-populated via “after update” event on “txtPostCodeStart” and “txtPostCodeEnd” textboxes by simple RecordSet vba programming.

 

Private sub caldistance_Click()

On error Go to Err_caldistance_Click

 

If Me.TxtPostCodeStart = “” Then

MsgBox(“Enter a starting zip code”)

ExitSub

It will end if

 

If Me.TxtPostCodeEnd = “” Then

MsgBox(“Enter a final zip code”)

ExitSub

It will 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:

ExitSub

 

Err_caldistance_Click:

MsgBox Err.Description

Resume Exit_caldistance_Click

finish sub

 

While we have demonstrated a simple form, the basic formula is there to be used in many different ways. We hope you find this as useful as we do!

have!