从函数VBA返回动态数组

Question

我正在尝试创建一个输出数组的函数。但是，我在左侧接听了函数调用，必须返回变体或目的。如何从此功能返回动态数组？

Public Function Fibonacci_Array(max As Integer) As Integer

    Dim result()   As Variant
    ReDim result(0 To max)
    '' Array indices.
    Dim i1         As Integer
    Dim i2         As Integer
    Dim i          As Integer
    i1 = 0
    i2 = 1

    '' Array values.
    Dim newVal     As Long
    Dim prev2      As Long
    Dim prev       As Long
    prev2 = 0
    prev = 1
    '' Loop through
    While prev <= max
            result(i1) = prev2
            result(i2) = prev

            newVal = prev + prev2
            ''Debug.Print newVal
            prev2 = prev
            prev = newVal
            i1 = i1 + 1
            i2 = i2 + 1
    Wend
    '' Problem here.
    Fibonacci_Array() = result
End Function

Answer 1

Variant是传递函数或从功能传递数组时最灵活的类型。

替换

Public Function Fibonacci_Array(max As Integer) As Integer

Public Function Fibonacci_Array(max As Integer) As Variant

替换

Dim result() As Variant

Dim result As Variant

并更换

Fibonacci_Array() = result

Fibonacci_Array = result

这会使它编译，但是您似乎需要一些调试，因为当我键入

时

?Join(Fibonacci_Array(10),", ")

在直接窗口中，我得到：

0, 1, 1, 2, 3, 5, 8, , , , 

（如果您想要小于max的斐波那契号，这可能是您想要的，但是然后您可能需要使用 ReDim Preserve将数组降低到大小，然后再返回大小。如果您的意图是要获得第一个 max fibonacci编号，罪魁祸首是行While prev <= max-不是您要比较max的prev）。

在编辑上，我认为编写一个VBA函数很有趣，该功能返回所有大小为＆lt; = a给定最大值的fibonacci数字的数组。由于斐波那契数迅速增长，因此我决定使用Long而不是Integer，并且还使用Binet的公式在填充阵列之前计算数组的大小（可能是 1），因此我们不分配一个数组太大了：

Function FibNums(max As Long) As Variant
    'returns array consisting of all Fibonacci numbers <= max
    'max is assumed to be >= 1
    Dim i As Long, n As Long, F As Long
    Dim Fibs As Variant
    'come up with an upper bound on size of array:
    n = 1 + Int(Log(Sqr(5) * max) / Log((1 + Sqr(5)) / 2))
    ReDim Fibs(1 To n)
    Fibs(1) = 1
    Fibs(2) = 1
    i = 2
    Do While Fibs(i) <= max
        F = Fibs(i - 1) + Fibs(i)
        If F <= max Then
            i = i + 1
            Fibs(i) = F
        Else
            Exit Do 'loop is finished
        End If
    Loop
    'at this stage, Fibs contains i numbers
    If i < n Then ReDim Preserve Fibs(1 To i)
    FibNums = Fibs
End Function

例如：

?Join(Fibnums(100000),", ")
1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, 1597, 2584, 4181, 6765, 10946, 17711, 28657, 46368, 75025

Answer 2

您的返回类型应该相同，分配函数值时不需要括号：

Public Function Fibonacci_Array(max As Integer) As Long()

    Dim result()   As Long
    ReDim result(0 To max)
    '' Array indices.
    Dim i1         As Integer
    Dim i2         As Integer
    Dim i          As Integer
    i1 = 0
    i2 = 1

    '' Array values.
    Dim newVal     As Long
    Dim prev2      As Long
    Dim prev       As Long
    prev2 = 0
    prev = 1
    '' Loop through
    While prev <= max
            result(i1) = prev2
            result(i2) = prev

            newVal = prev + prev2
            ''Debug.Print newVal
            prev2 = prev
            prev = newVal
            i1 = i1 + 1
            i2 = i2 + 1
    Wend
    '' Problem here.
    Fibonacci_Array = result
End Function
Sub a()
Dim b() As Long
b() = Fibonacci_Array(100)
End Sub