CODEWITHSUNDEEP
arraysvbaexcelvariant
Christian Contreras
Christian Contreras

Reputation: 1

How to return an array, of varying length, from a VBA Function

I'm trying to write a function within a bigger macro to return a list of elements. The array is generated from cells within the spreadsheet:

    Function Elem_Array() As Variant
    Dim Elements() As Variant
    i = 1
    Cells(i, 38).Select
    element1 = Cells(i, 38).Value
    element2 = ""

    Do While element2 <> element1
    i = i + 1
    Cells(i, 38).Select
    element2 = Cells(i, 38).Value
    ReDim Preserve Elements(1 To i)
    Elements(i) = element2
    Loop

    Elements(1) = element1
    Elem_Array= Elements
    End Function

When trying to set Elem_Array equal to the Elements array it ends the function without Elem_Array equaling anything. When I call on the function I end up with errors because I'm trying to call on an empty array or string. I've tried using a for loop to populate Elem_Array one element at a time, but the function ends without adding anything. Whether Elements() is Dim'ed as a Variant or string doesn't change anything for me. Suggestions to similar questions involve using a collection for Elements() and then using a loop to have Elem_Array equal Elements, but if possible I'd like to minimize changing the code too much. Any help is appreciated!

Upvotes: 0

Views: 1265

Answers (1)

paul bica
paul bica

Reputation: 10715

Change this:

Dim Elements() As Variant

To this:

Dim Elements As Variant

.

The first statement generates an array of variants

Your function return type is "variant variable" (singular) not an array of variants

Upvotes: 1

Related Questions