How to return and use multiple values in functions in VBA

Question

I am would like to write a user defined function (UDF), A that returns multiple values (let's say 3). I would then like to use these values in another UDF, B.

I have been following https://www.geeksengine.com/article/vba-function-multiple-values2.html on what VBA data structures would support this. I was originally using a collection to store the multiple values from UDF A, but in my use case, index 0, 1 and 2 have some value, so would like to use an array instead of a collection so that I can index with base 0 (I am not sure if this is possible with collections - it seems the first element is .Item(1)).

The problem is, I cannot seem to retrieve the values of the array returned by UDF A inside UDF B.

A minimum (not) working example:

Function A() As Variant

    Dim arr(3) As Variant

    arr(0) = "zero"
    arr(1) = "one"
    arr(2) = "two"

    A = arr

    MsgBox "Function A"
    MsgBox arr(0)

End Function


Function B() As Variant

    Dim arry(3) As Variant
    Set arry = A()

    MsgBox "Function B"
    MsgBox arry(0)


    End Function

Sub debugfunc()
    MsgBox B() 
End Sub

I have tried both Set arry = A() and arry = A(). Both produce 'Ca't assign to array' errors when running the debugfunc sub.

Any ideas?

Can't assign to array is because of Dim arry(3). You cannot assign to an array Dimed with dimensions. Dim without dimensions. — GSerg
– GSerg, Commented Aug 7, 2019 at 11:01
Please note that these Functions may be used as UDFs. However, the example code does not do that. A UDF (User Defined Function) is a function that you intend to call in an Excel Formula. — z32a7ul
– z32a7ul, Commented Aug 7, 2019 at 11:06

Rory · Accepted Answer · 2019-08-07 10:58:22Z

4

You don't use Set and you need a dynamic array of the same type to receive the function result:

Dim arry() As Variant
arry = A()

answered Aug 7, 2019 at 10:58

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

z32a7ul · Accepted Answer · 2019-08-07 11:01:32Z

2

You need to declare arry as Variant and get rid of Set. Here is your code corrected:

Function A() As Variant
    Dim arr(3) As Variant
    arr(0) = "zero"
    arr(1) = "one"
    arr(2) = "two"
    A = arr
    MsgBox "Function A"
    MsgBox arr(0)
End Function

Function B() As Variant
    Dim arry As Variant: arry = A()
    MsgBox "Function B"
    MsgBox arry(0)
End Function

Sub debugfunc()
    MsgBox B()
End Sub

You may also remove the parens after A, like Dim arry As Variant: arry = A().

answered Aug 7, 2019 at 11:01

z32a7ul

3,8474 gold badges24 silver badges49 bronze badges

Collectives™ on Stack Overflow

How to return and use multiple values in functions in VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related