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

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

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

## 2 Answers

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
``````

• The Dim/set to avoid writing worksheetfunction several times is a nice touch. Jun 10, 2015 at 14:09
• @P.Obertelli Pure laziness on my part! Jun 10, 2015 at 14:19
• For maximum laziness - superuser.com/a/708388/116196 Jun 10, 2015 at 16:16
• @Raystafarian WOW!! ...................Thank you!! Jun 10, 2015 at 16:20
• @Raystafarian There are a few quirks with `Evaluate`, though... Jun 10, 2015 at 17:37