英文:
How do add multiple VBA WorksheetFunctions in the same line
问题
运行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之外运行代码没有问题。
英文:
Running into some issues with VBA and long formulas to calculate which launch point is closest to destination.
Run-Time Error 438
Object doesn't support this propert or method.
I have tried a few different ways of doing this and since I am still new to this I was hoping someone could help out.
Code below:
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
I am hoping to get the launch point that would be closest to the destination.
Running the code outside of VBA works fine.
答案1
得分: 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
注意：这个翻译中已经去掉了 HTML 实体编码，以便在代码中正确使用。
英文:
Untested, since I don't have any test data, but something like this should work:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论