I did a distance calculation some time ago for zip codes in the US (the UK database of post code is too large for Excel).

My calculations were based on distance "as the crow flies" not as you drive (as shown in Google).

I have a sheet with zip codes and their corresponding Long and Lat values (approx 33000 of them).

Code:

Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
ByVal lat2 As Double, ByVal lon2 As Double) As Double
' shg 2008-1111
' Returns central angle between two point in RADIANS
' using Vincenty formula
Const pi As Double = 3.14159265358979
Const D2R As Double = pi / 180#
Dim dLon As Double
Dim x As Double
Dim y As Double
' convert angles from degrees to radians
lat1 = D2R * lat1
lon1 = D2R * lon1
lat2 = D2R * lat2
lon2 = D2R * lon2
dLon = lon2 - lon1 ' delta lon
x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
CentralAngle = WorksheetFunction.Atan2(x, y)
End Function

was/is the UDF I used to calculate.

## Like this thread? Share it with others