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?
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Change this line:

        ReDim vV(i)


        ReDim Preserve vV(i)

sjgreyAuthor Commented:
Perfect thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.