Here's a long formula to calculate distance between two coordinates points on Earth


As it is a bit cumbersome to use inside other formulas, I wrote a user defined function for that.

Function DistXY(Lat1 As Single, Lng1 As Single, Lat2 As Single, Lng2 As Single) As Single

DistXY = WorksheetFunction.Acos(Cos(WorksheetFunction.Radians(90 - Lat1)) * Cos(WorksheetFunction.Radians(90 - Lat2)) + Sin(WorksheetFunction.Radians(90 - Lat1)) * Sin(WorksheetFunction.Radians(90 - Lat2)) * Cos(WorksheetFunction.Radians(Lng1 - Lng2))) * 6371

End function

As you can see, they're exactly the same. As there's no Acos ou Radians functions in VBA, it even uses the "worksheetFunction" method.

Why is that when I apply theses formula to the same pair of XY coordinates, I get different results ?

For example:

From ABC 45.4960674,-73.514446 to XYZ 43.5369,-71.8592

DistXY() results: 254.313156128

Long formula method: 254.313268914

Going along with the comment of @raystafarian , changing all the types to double improved the precision. But there's still a difference.

Function DistXY(Lat1 As double, Lng1 As double, Lat2 As double, Lng2 As double) As double ...

New results:

DistXY() : 254.3132689135060 km

Long formula method: 254.3132689135250 km

  • 1
    I assume that this is because of rounding errors in the different stages of the calculation, and Excel and VBA handling them slightly differently.
    – eirikdaude
    Jun 10, 2015 at 13:15

2 Answers 2


You're using (and returning) a data type of single. This will cause functions to evaluate differently if they take a different data type of default.


Based on Raystafarian's answer:

Public Function DistXY(Lat1 As Double, Lng1 As Double, Lat2 As Double, Lng2 As Double) As Double
   Dim i As Double, f As Double
   Dim wf As WorksheetFunction
   Set wf = Application.WorksheetFunction

   i = 90
   f = 6371

   DistXY = wf.Acos(Cos(wf.Radians(i - Lat1)) * Cos(wf.Radians(i - Lat2)) + Sin(wf.Radians(i - Lat1)) * Sin(wf.Radians(i - Lat2)) * Cos(wf.Radians(Lng1 - Lng2))) * f
End Function

enter image description here


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .