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

=ACOS(COS(RADIANS(90-LAT1)) *COS(RADIANS(90-LAT2)) +SIN(RADIANS(90-LAT1)) *SIN(RADIANS(90-LAT2)) *COS(RADIANS(Long1-Long2))) *6371

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.313268913**5060** km

**Long formula method**: 254.313268913**5250** km