如何在同一行中添加多个VBA工作表函数

Question

在VBA和长公式计算哪个发射点最接近目的地时遇到一些问题。

运行错误438

对象不支持此属性或方法。

我已经尝试了几种不同的方法，因为我还是新手，我希望有人能帮助我。

下面的代码:

Sub GetClosestLocation(dest_lat, dest_long)
Dim sp As Variant
Dim expr, a, b, c, launch_name, launch_lat, launch_long As Range
'Get Names on Launch Points sheet
a = Sheets("Launch Points").Range("F" & Rows.Count).End(xlUp).Row
expr = "F10" + Trim(Str(a))
Set launch_name = Sheets("Launch Points").Range(expr)
'Get Lattitudes on Launch Points sheet
b = Sheets("Launch Points").Range("B" & Rows.Count).End(xlUp).Row
expr = "B10" + Trim(Str(b))
Set launch_lat = Sheets("Launch Points").Range(expr)
'Get Longitudes on Launch Points sheet
c = Sheets("Launch Points").Range("C" & Rows.Count).End(xlUp).Row
expr = "C10" + Trim(Str(c))
Set launch_long = Sheets("Launch Points").Range(expr)
With Application
sp = .Lookup(1, 1 / .Frequency(0, .Sin((.Radians(launch_lat - dest_lat)) / 2) ^ 2 + .Sin((.Radians(launch_long - dest_long)) / 2) ^ 2 * .Cos(.Radians(launch_lat)) * .Cos(.Radians(dest_lat))), launch_name)
End With
Range("F17") = sp

End Sub

我希望能找到离目的地最近的发射点。

在VBA之外运行代码可以正常工作。

Answer 1

未经测试，因为我没有任何测试数据，但是像这样的东西应该可以工作:

Function GetClosestLocation(dest_lat, dest_long)

Dim f As String, lr As Long, ws As Worksheet

f = "=LOOKUP(1, 1 / FREQUENCY(0, SIN((RADIANS(B10:B119 - <dlat>)) / 2) ^ 2 + " & _
"SIN((RADIANS(C10:C<lr> - <dlong>)) / 2) ^ 2 * COS(RADIANS(B10:B<lr>)) * " & _
"COS(RADIANS(<dlat>))), F10:F<lr>)"

Set ws = ThisWorkbook.Worksheets("Launch Points")

lr = ws.Range("F" & Rows.Count).End(xlUp).Row
f = Replace(f, "<lr>", lr)
f = Replace(f, "<dlat>", dest_lat)
f = Replace(f, "<dlong>", dest_long)

GetClosestLocation = ws.Evaluate(f)

End Function