使用经度和纬度的距离(Excel vba 访问 vba)

Question

我有以下VBA函数，我用于MS Excel。但是，我已将整个数据库导入MS Access，我希望使用此功能而无需从MS Excel调用它。可能吗？我必须改变什么？

---

`
'This is to find the GCDm (great circle distance) in miles
Function GCDnm(origin As String, dest As String) As Integer

Dim olat As Double
Dim olong As Double
Dim dlat As Double
Dim dlong As Double
Dim earthradius As Integer
earthradius = 6371
olat = coordlat(origin)
olong = coordlong(origin)
dlat = coordlat(dest)
dlong = coordlong(dest)
GCDnm = Round(WorksheetFunction.Acos(( _
    Sin(WorksheetFunction.Radians(olat)) * _
    Sin(WorksheetFunction.Radians(dlat)) + _
    Cos(WorksheetFunction.Radians(olat)) * _
    Cos(WorksheetFunction.Radians(dlat)) * _
    Cos(WorksheetFunction.Radians(olong - dlong)))) * _
    earthradius, 0) / 1.852
'GDCm = [arccos[sin(origin latitude) * sin(destination latitude) * cos(origin latitude)    * sin(destination latitude) * cos(origin latitude - dest latitude)]*radius=6371]/1.852
 End Function
`

---

Answer 1

虽然你可以像牧田说的那样自动化Excel，但如果你在查询或类似的东西中使用该函数，性能可能会很差。就个人而言，我会用纯VBA重写代码：

Function ArcCos(Number As Double) As Double
  ArcCos = Atn(-Number / Sqr(-Number * Number + 1)) + 2 * Atn(1)
End Function
Function DegreesToRadians(Number As Double) As Double
  DegreesToRadians = Number / 57.2957795130823
End Function
'...
GCDnm = Round(ArcCos(( _
  Sin(DegreesToRadians(olat)) * _
  Sin(DegreesToRadians(dlat)) + _
  Cos(DegreesToRadians(olat)) * _
  Cos(DegreesToRadians(dlat)) * _
  Cos(DegreesToRadians(olong - dlong)))) * _
  earthradius, 0) / 1.852

Answer 2

这篇博文 http://blogannath.blogspot.com.au/2010/05/microsoft-access-tips-tricks-excel.html 解释了如何在访问中使用 excel 函数。基本上，您需要添加对 Excel 对象库的引用。之后，您可以访问几乎所有的 excel 函数。只需确保目标电脑上安装了 Excel。