在电子表格功能中返回数组 [英] Return array in Spreadsheet Function

问题描述

下面的代码返回一个数组。我想在一个电子表格中使用它作为excel公式返回数组。但是，当我这样做，它只返回第一个值到单元格。有没有可以返回与数组相同大小的数组？

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?

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

推荐答案

p>工作表公式只能将值输出到公式写入的同一单元格中。就此而言，代码已经生成了一个数组。如果您希望在复制公式时显示这些值，请使用如下所示的公式（在所需的任何单元格中），然后复制：

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$1:$A1))

如果你复制太多，你会得到一个#REF！因为LoadNumbers用完了数字。

If you copy down too far, you'll get a #REF! error because the LoadNumbers ran out of numbers.

这篇关于在电子表格功能中返回数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！