Why is my constructed formula giving different results than the same formula as UDF in Excel?

Question

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.3132689135060 km

Long formula method: 254.3132689135250 km

Answer 1

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.

• Acos - Double
• Cos - Double
• Sin - Double
• Radians - Double

Answer 2

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