EXCEL VBA - Function that retrun array

Question

I want to build function in VBA that build an array and return it to array that's in another sub so I can use this function in multiple places. For example:

Sub getArrayData ()
 Dim myItemsArr() As String ' dynamic array
 myItemsArr = functionToFillArray
End sub

And let's assume this is the function

Function functionToFillArray(arrayIWantToFill as Variant) 
 arrayToFill = ("A","B","C")
 arrayIWantToFill  = ???
 functionToFillArray = ?? ' what should i do here to return the array i want to fill
End function

At other language suck as Java its more clear to me how to do it, but in VBA I just don't understand how its work yet. I try to find some information online, but without success

It's not really clear what you want your function to do. Your two bocks of code are not in agreement. Your sub passes no argument to the function, but the function expects one. — Tim Williams
– Tim Williams, Commented Dec 16, 2020 at 7:41

Gustav · Accepted Answer · 2020-12-16 07:42:07Z

1

Like this:

Function getArrayData() As Variant

    Dim myItemsArr As Variant

    ' Fill array.
    functionToFillArray myItemsArr

    getArrayData = myItemsArr

End Function


Sub functionToFillArray(arrayIWantToFill As Variant) 

    arrayIWantToFill = Array("A","B","C")
 
End Sub

answered Dec 16, 2020 at 7:42

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

EXCEL VBA - Function that retrun array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related