下面的代码返回一个数组。我想在电子表格中使用它作为 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
UDF 当然可以返回一个数组，并且您的函数可以正常工作。只需选择范围 B2:D2，然后输入=LoadNumbers(1, 3)进入编辑栏，然后按 Ctrl+Shift+Enter 告诉 Excel 这是一个数组函数。
现在，您无法让 UDF 自动调整范围大小它是从根据其输入（至少不是没有一些丑陋的Application.OnTimehack），但无论如何你都不需要这样做。只需将函数放在 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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
