Search code examples
excelvbaformulahaversine

VBA haversine formula

I am trying to implement Haversine formula into excel function. Its looks like this:

Public Function Haversine(Lat1 As Variant, Lon1 As Variant, Lat2 As Variant, Lon2 As Variant)
Dim R As Integer, dlon As Variant, dlat As Variant, Rad1 As Variant
Dim a As Variant, c As Variant, d As Variant, Rad2 As Variant

R = 6371
dlon = Excel.WorksheetFunction.Radians(Lon2 - Lon1)
dlat = Excel.WorksheetFunction.Radians(Lat2 - Lat1)
Rad1 = Excel.WorksheetFunction.Radians(Lat1)
Rad2 = Excel.WorksheetFunction.Radians(Lat2)
a = Sin(dlat / 2) * Sin(dlat / 2) + Cos(Rad1) * Cos(Rad2) * Sin(dlon / 2) * Sin(dlon / 2)
c = 2 * Excel.WorksheetFunction.Atan2(Sqr(a), Sqr(1 - a))
d = R * c
Haversine = d
End Function

But when im testing it I am getting wrong distance... I dont understand why. For coordinates used in this topic : Function to calculate distance between two coordinates shows wrong I am getting 20013,44 as output. Anyone knows what is wrong here? Cant find my mistake...

Solution

  • Atan2 is defined back to front in Excel compared to JavaScript i.e. Atan2(x,y) rather than Atan2(y,x).

    You need to reverse the order of the two arguments:-

    c = 2 * Excel.WorksheetFunction.Atan2(Sqr(1 - a), Sqr(a))

    See this

    So

    =haversine(59.3293371,13.4877472,59.3225525,13.4619422)

    gives

    1.65 km

    which is the correct distance as the crow flies.