vba 电子表格函数中的返回数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18494156/
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
Return array in Spreadsheet Function
提问by cdelsola
The code below returns an array. I would like to use it in a spread sheet as an excel formula to return the array. However, when I do, it only returns the first value to the cell. Is there anyway to return the array in a range of equal size as the array?
下面的代码返回一个数组。我想在电子表格中使用它作为 excel 公式来返回数组。但是，当我这样做时，它只将第一个值返回到单元格。无论如何，是否可以在与数组相同大小的范围内返回数组？
Function LoadNumbers(Low As Long, High As Long) As Long()
'''''''''''''''''''''''''''''''''''''''
' Returns an array of Longs, containing
' the numbers from Low to High. The
' number of elements in the returned
' array will vary depending on the
' values of Low and High.
''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''
' Declare ResultArray as a dynamic array
' to be resized based on the values of
' Low and High.
'''''''''''''''''''''''''''''''''''''''''
Dim ResultArray() As Long
Dim Ndx As Long
Dim Val As Long
'''''''''''''''''''''''''''''''''''''''''
' Ensure Low <= High
'''''''''''''''''''''''''''''''''''''''''
If Low > High Then
Exit Function
End If
'''''''''''''''''''''''''''''''''''''''''
' Resize the array
'''''''''''''''''''''''''''''''''''''''''
ReDim ResultArray(1 To (High - Low + 1))
''''''''''''''''''''''''''''''''''''''''
' Fill the array with values.
''''''''''''''''''''''''''''''''''''''''
Val = Low
For Ndx = LBound(ResultArray) To UBound(ResultArray)
ResultArray(Ndx) = Val
Val = Val + 1
Next Ndx
''''''''''''''''''''''''''''''''''''''''
' Return the array.
''''''''''''''''''''''''''''''''''''''''
LoadNumbers = ResultArray()
End Function
采纳答案by tigeravatar
A worksheet formula can only output a value to the same cell the formula was written in. As it stands, the code already produces an array. If you want the values to be shown as you copy the formula down, use a formula like this (in any cell you want) and then copy down:
工作表公式只能向编写公式的同一单元格输出一个值。就目前而言，代码已经生成了一个数组。如果您希望在向下复制公式时显示值，请使用这样的公式（在您想要的任何单元格中），然后向下复制：
=INDEX(LoadNumbers(1,10),ROWS($A:$A1))
If you copy down too far, you'll get a #REF! error because the LoadNumbers ran out of numbers.
如果你复制得太远，你会得到一个#REF！错误，因为 LoadNumbers 用完了数字。
回答by Chel
A UDF can certainly return an array, and your function works fine. Just select, e.g., range B2:D2, put =LoadNumbers(1, 3)into the formula bar, and hit Ctrl+Shift+Enter to tell Excel it's an array function.
UDF 当然可以返回一个数组，并且您的函数可以正常工作。只需选择，例如范围 B2:D2，放入=LoadNumbers(1, 3)公式栏，然后按 Ctrl+Shift+Enter 告诉 Excel 这是一个数组函数。
Now, you can't have the UDF auto-resize the range it was called fromaccording to its inputs (at least not without some ugly Application.OnTimehack), but you don't need to do that anyways. Just put the function in a 1000-cell-wide range to begin with, and have the UDF fill in the unused space with blank cells, like this:
现在，你不能让 UDF根据它的输入自动调整它被调用的范围（至少不是没有一些丑陋的Application.OnTime黑客），但无论如何你都不需要这样做。只需将函数放在 1000 个单元格范围内，然后让 UDF 用空白单元格填充未使用的空间，如下所示：
Function LoadNumbers(ByVal Low As Long, ByVal High As Long) As Variant()
Dim ResultArray() As Variant
Dim Ndx As Long
Dim Val As Long
Dim SourceCols As Long
SourceCols = Application.Caller.Columns.Count
If Low > High Then
Exit Function
End If
If High - Low + 1 > SourceCols Then High = Low + SourceCols - 1
ReDim ResultArray(1 To SourceCols)
Val = Low
For Ndx = LBound(ResultArray) To (High - Low + 1)
ResultArray(Ndx) = Val
Val = Val + 1
Next Ndx
For Ndx = (High - Low + 2) To UBound(ResultArray)
ResultArray(Ndx) = vbNullString
Next Ndx
LoadNumbers = ResultArray()
End Function
回答by cdelsola
=INDEX(LoadNumbers(1,10),ROWS($A:$A1),COLUMNS($B,B))
