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

Question

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!

paul bica · Accepted Answer · 2015-07-02 21:23:29Z

1

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

answered Jul 2, 2015 at 21:23

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

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

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