从 VBA 函数返回数组的正确方法

我的学校使用 Excel 2016（前 {=array} 时代），因此我使用 VBA 来定义一些我需要的缺失函数。我尝试在 VBA 中实现的一个函数是SEQUENCE- 替换，就像这样

Function MYSEQ(n As Integer, m As Integer) As Variant
    ReDim seq(m - n) As Variant

    For i = LBound(seq) To UBound(seq)
        seq(i) = i + n
    Next i

    MYSEQ = seq
End Function

这很有效，因为如果我在牢房里这样做=SUM(MYSEQ(1,3))我正确地得到了6。 然而，奇怪的是，返回值与其他 Excel 运算符不能很好地配合。 例如，如果我这样做=SUM(MYSEQ(1,3)+1)，我得到 2，而不是预期的 9。请注意，常量数组与其他运算符配合得很好，即=SUM({1;2;3}+1)正确输出 9。

有没有办法解决这个问题，以便我返回的数组被视为常量数组？

You return a Variant() and enter the UDF as an array function (with CTRL+SHIFT+ENTER)

将数组中的值加倍的函数示例

Public Function ScaleValues(ByRef r As Range, ByVal factor As Double) As Variant()
    Dim n As Long, m As Long, i As Long, j As Long
    n = r.Rows.Count:   m = r.Columns.Count

    Dim vals() As Variant
    vals = r.Value

    For i = 1 To n
        For j = 1 To m
            vals(i, j) = factor * vals(i, j)
        Next j
    Next i

    ScaleValues = vals
End Function

具体来说，填充序列中的单元格的函数是：

Public Function MySeq(ByVal start_value As Long, ByVal end_value As Long) As Variant()
    Dim n As Long, i As Long
    n = end_value - start_value + 1

    Dim vals() As Variant
    ReDim vals(1 To n, 1 To 1)

    For i = 1 To n
        vals(i, 1) = start_value + (i - 1)
    Next i

    MySeq = vals

End Function

您可以测试一下，效果很好。进入=SUM(MySeq(1,24))在一个单元格中，您会得到 300，这是正确答案。如果您想做线性代数，例如缩放或添加数组，那么您必须为每个步骤逐列分离操作。

请注意，您无法将整个数组存储在单个单元格中。相信我，我尝试了无数种方法（比如将公式设置为={1,2,3,4}例如）。即使成功，也无法以任何标准方式提取值以供使用SUM(), TRANSPOSE() or MMULT().

对于后代，有一个序列函数的版本可以处理非整数值和非单位步长（步长）

Public Function MySeq2(ByVal start_value As Double, ByVal end_value As Double, Optional stride As Double = 1) As Variant()
    Dim n As Long, i As Long
    n = (end_value - start_value + stride) / stride

    Dim vals() As Variant
    ReDim vals(1 To n, 1 To 1)

    For i = 1 To n
        vals(i, 1) = start_value + (i - 1) * stride
    Next i

    MySeq2 = vals

End Function