20. Create a function to compute the great circle route (shortest) distance between two geographic locations.
This function has been embedded in the module code for a while. Modules: Misc, Function: GeogDistance The formula is relatively complex, but available on the Internet
Function GeogDistance(lat1 As Variant, lon1 As Variant, lat2 As
Variant, lon2 As Variant) As Single
On Error GoTo ERR_GDistance
' Computes great circle route distance between any two geographic points.
' Latitude and Longitude are passed in degrees (West is negative and South is negative).
' Distance is based on average radius of the earth (about 1/10 % difference).
Dim t1 As Double
Const Pi = 3.14159265358979
Const DEGTORAD = 57.29577951 � 180/PI
Const MILESDEG = 69.04958853 � Average (statutory) miles per degree
' 69.1638889 for equator; 68.93177881 for polar
If (IsNull(lat1) Or IsNull(lon1) Or IsNull(lat2) Or IsNull(lon2)) Then
GeogDistance = 30000 ' missing data, so return a big number
Exit Function
End If
t1 = sIn(lat1 / DEGTORAD) * sIn(lat2 / DEGTORAD) + Cos(lat1 / DEGTORAD) * Cos(lat2 / DEGTORAD) * Cos((lon1 - lon2) / DEGTORAD)
' VBA does not have Acos, so use Atn instead: Acos = Pi/2 - Atn(x / sqrt(1 - x*x) )
' Note that VBA help has the formula wrong!
GeogDistance = MILESDEG * DEGTORAD * (Pi / 2 - Atn(t1 / Sqr(1 - t1 * t1)))
EXIT_GDistance:
Exit Function
ERR_GDistance:
If (t1 < 0) Then
GeogDistance = 180 * MILESDEG * DEGTORAD
Else
GeogDistance = 0
End If
Resume EXIT_GDistance
End Function