Passing an array that is to be re dimensioned to a subroutine

I have several arrays I need to populate from data in an Excel worksheet.  I don't know in advance how many there will be or whether the entries will be numeric or text.

Rather than code reading in each one separately, I have a subroutine that looks up the cells in a designated column and each time it finds a non blank one it does a ReDim on the array and sets the latest entry in the array to the value of the cell.  Everything is fine inside the subroutine but only the last value remains in the array when I examine it back in the calling routine.

I've replicated the behavior in this code.  While the Filler routine is operating, I can see by stepping through (using f8) that each successive entry is set to the value of the index i, just to give me something to check.  Afterwards, back in the Master routine, all the entries are empty apart from the tenth one so the string displayed in the message box is vVariant{,,,,,,,,,10}.

Code is as follows

Public vVariant() As Variant

Sub Master()

    Dim strS As String, i As Integer

    Call Filler(vVariant())
    strS = "vVariant{"
    For i = 1 To 10
        strS = strS & ", " & vVariant(i)
    Next i
    strS = strS & " }"
    MsgBox strS
End Sub

Sub Filler(vV() As Variant)

    Dim i As Integer
    For i = 1 To 10
        ReDim vV(i)
        vV(i) = i
    Next i

End Sub

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Change this line:

        ReDim vV(i)


        ReDim Preserve vV(i)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sjgreyAuthor Commented:
Perfect thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.