将数组值分配给 VBA 中的变量会导致我的 UDF 退出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11237567/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Assigning an array value to a variable in VBA causes my UDF to exit
提问by nightTrevors
I can't seem to figure out why this UDF is exiting on the currentInput = inputArray(i). Here is the relevant code:
我似乎无法弄清楚为什么这个 UDF 在currentInput = inputArray(i). 这是相关的代码：
Function OrderRange(inputRange As Range) As Variant
Dim length As Integer
inputHeight = inputRange.Count
Dim inputArray As Variant
inputArray = inputRange
Dim strippedArray() As Variant
ReDim strippedArray(0 To (inputHeight - 1))
Dim currentInput As String
Dim i As Integer
For i = 0 To (inputHeight - 1)
currentInput = inputArray(i)
'...computations on currentInput...'
strippedArray(i) = currentInput
Next i
OrderRange = strippedArray
End Function
The debugger reaches currentInput = inputArray(i)but once I move to the next line, the function terminates and a #VALUE!error is entered into the cell I call the function from. I know this is a specific question, but I'm sure it's a general issue and I'll edit this original post to reflect what the general problem is.
调试器到达，currentInput = inputArray(i)但是一旦我移到下一行，函数就会终止，并且#VALUE!错误会输入到我从中调用函数的单元格中。我知道这是一个具体问题，但我确定这是一个普遍问题，我将编辑这篇原始帖子以反映普遍问题是什么。
Edit:This is an issue regarding assignment of a range to a variant array.
编辑：这是一个关于将范围分配给变体数组的问题。
回答by Doug Glancy
Variant arrays created by setting them equal to ranges have two dimensions even if they are only one column, or row, wide. So if you call the function with A1:A10 you'll get a 10 * 1 array. Also the lower bound of the dimensions will be one, not zero. So you'd have to do something like:
通过将它们设置为等于范围来创建的变体数组具有二维，即使它们只有一列或一行宽。因此，如果您使用 A1:A10 调用该函数，您将得到一个 10 * 1 的数组。维度的下限也是一，而不是零。因此，您必须执行以下操作：
For i = 1 To (inputHeight)
currentInput = inputArray(i, 1)
Also you should use Option Explicit so that you're reminded to declare all variables. InputHeight is never declared.
此外，您应该使用 Option Explicit 以便提醒您声明所有变量。InputHeight 从未声明。
