Return dynamic array from function VBA

Question

I am trying to create a function that outputs an array. However, I got the Function call on left-hand side must return Variant or Object. How can I return a dynamic array from this function?

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

John Coleman · Accepted Answer · 2017-03-06 17:54:48Z

Variant is the most flexible type when it comes to passing arrays to or from functions.

Replace

Public Function Fibonacci_Array(max As Integer) As Integer

by

Public Function Fibonacci_Array(max As Integer) As Variant

Replace

Dim result() As Variant

by

Dim result As Variant

and replace

Fibonacci_Array() = result

by

Fibonacci_Array = result

That will make it compile, but you seem to need a bit of debugging, since when I then type

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

in the Immediate Window, I get:

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

(This might be what you want if you want the Fibonacci numbers which are less than max, but then you might want to use a ReDim Preserve to pare the array down to size before returning it. If your intention was to get the first max Fibonacci numbers, the culprit is the line While prev <= max -- it isn't prev that you would want to compare to max).

On Edit I thought it would be fun to write a VBA function which returns the array of all Fibonacci numbers whose size is <= a given max. Since Fibonacci numbers grow rapidly, I decided to use Long rather than Integer, and also to use Binet's formula to calculate the size of the array (possibly +1 for safety) before filling the array, so we don't allocate an array which is much too large:

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

For example:

?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

I wish I can give this reponse 3 upvotes! One for answering my question correctly, one for giving an alternative way of outputting Fibonacci sequence , and one for mentioning Binet's formula.

Absinthe · Accepted Answer · 2017-03-05 23:39:13Z

Your return type should be the same and you don't need the parenthesis when you assign the value of the function:

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

Collectives™ on Stack Overflow

Return dynamic array from function VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related