We help IT Professionals succeed at work.

VBA Excel: How to return function as an array

Hi X-perts,

I need to return function as an array and assign it to another array.

What is wrong with my code?

Please, advise

Public Function qtest(ByVal n As Integer) As Variant
    Dim res(0 To 3) As Variant
    res(0) = 2.12 * n
    res(1) = 3.14 + n
    res(2) = 1.18
    res(3) = 2.12
    qtest = res
End Function

Public Sub aaa()
    Dim fnc(0 To 3) As Variant
    fnc = qtest(3) '==== This line returns error "Cannot assign to array"
    zz = fnc(0)
    zz = fnc(1)
    zz = fnc(2)
End Sub

Open in new window

Watch Question

You already have dimensioned the array. Do it like this....

    Dim fnc As Variant
    fnc = qtest(3)



perfect- thank you!