VBA Haversine公式 [英] VBA haversine formula

问题描述

我正在尝试将Haversine公式实现为excel函数.它看起来像这样:

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

但是当我进行即时测试时，我得到了错误的距离...我不明白为什么.对于本主题中使用的坐标:用于计算两个坐标之间的距离的函数显示错误 我得到20013,44作为输出.有人知道这是怎么回事吗?不能发现我的错误...

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

推荐答案

与JavaScript相比，Atan2是在Excel中重新定义的，即Atan2(x，y)而不是Atan2(y，x).

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

请参见此

所以

=haversine(59.3293371,13.4877472,59.3225525,13.4619422)

给予

1.65 km

这是乌鸦飞行的正确距离.

which is the correct distance as the crow flies.

这篇关于VBA Haversine公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！