# 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

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

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

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.

``````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 • @Raystafarian There are a few quirks with `Evaluate`, though... Jun 10, 2015 at 17:37