サンプルマクロは、始点の緯度経度および終点の緯度経度の４つの引数に対して、
その２地点間の距離を返すユーザ定義関数です。

Function Sample(StartLatitude As Double, StartLongitude As Double _
    , EndLatitude As Double, EndLongitude As Double) As Double

    Dim myNS As Double, myEW As Double

    With Application.WorksheetFunction
        myNS = (StartLatitude - EndLatitude) / 360 * 40000#
        myEW = (StartLongitude - EndLongitude) / 360 * 40000# _
            * Cos(.Average(StartLatitude, EndLatitude) * .Pi() / 180)
        Sample = (.SumSq(myNS, myEW)) ^ 0.5
    End With

End Function