gpt4 book ai didi

excel - 将 UBound 用于 ParamArray 时出现值错误

转载 作者:行者123 更新时间:2023-12-04 21:30:24 26 4
gpt4 key购买 nike

我正在尝试编写一个函数来计算一个邮政编码到另一个邮政编码的最小距离。该函数应获取一个邮政编码的经度和纬度,然后是一个包含所有邮政编码的经度和纬度信息的二维数组。这是我写的函数:

Public Function PassArray(Longitude As Double, Latitude As Double, ParamArray varValues() As Variant) As Double
Dim arr() As Variant
Dim x As Long
For x = 1 To UBound(varValues(0), 1)
ReDim Preserve arr(x)
arr(UBound(arr)) = Sqr((Longitude - varValues(0)(x, 1)) ^ 2 + (Latitude - varValues(0)(x, 2)) ^ 2)
Next x
PassArray = WorksheetFunction.Min(arr)

我得到了 #Value!尝试使用此功能时出错。我检查了每一步,似乎 UBound(varValues(0), 1)导致问题。当我尝试 UBound(varValues)它返回 0 ,我猜它是参数数组的第一维上限?

我不明白为什么 UBound(varValues(0), 1)行不通。我认为它应该返回我的经度和纬度数组的最后一行编号。

最佳答案

考虑@Mathieu Guindon 的评论并按照这些思路进行:

Option Explicit

'ASSUMPTION: coordinatesArray is a 2D array with rows in dimension 1 and columns in dimension 2.
Public Function PassArray(longitude As Double, latitude As Double, coordinatesArray As Variant) As Double
Dim rowLowerBound As Long
Dim rowUpperBound As Long
Dim x As Long

'We're looking at coordinatesArray's first dimension (rows).
'Let's consider both the lower and upper bounds, so as to adapt to the
'configuration of coordinatesArray.
rowLowerBound = LBound(coordinatesArray, 1)
rowUpperBound = UBound(coordinatesArray, 1)

'Dim arr upfront; this will be way faster than redimming within the loop.
ReDim arr(rowLowerBound To rowUpperBound) As Double

For x = rowLowerBound To rowUpperBound
'Your calculations go here.
'You can access coordinatesArray elements like so:
'coordinatesArray(x, 1) for row x, column 1, and
'coordinatesArray(x, 2) for row x, column 2.
arr(x) = Sqr((longitude - coordinatesArray(x, 1)) ^ 2 + (latitude - coordinatesArray(x, 2)) ^ 2)
Next x

'Note that Application.WorksheetFunction.Min doesn't seem to care
'whether arr is zero, one or n-based.
PassArray = Application.WorksheetFunction.Min(arr)
End Function

请注意,我不能保证您的距离计算;可能适用于笛卡尔坐标,但不适用于经度/纬度。

关于excel - 将 UBound 用于 ParamArray 时出现值错误,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54029489/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com