CODEWITHSUNDEEP
arraysvbaexcel
harryg
harryg

Reputation: 24077

Dynamically appending an array in VBA

I want to append an array with a number depending on the condition of various variables. Here is the code I've come up with: I begin with an empty array.

Sub makeArr()
Dim myArr() As Integer
If box1 = True Then
    ReDim Preserve myArr(LBound(myArr) To UBound(myArr) + 1)
    myArr(UBound(myArr)) = 1
End If

If box2 = True Then
    ReDim Preserve myArr(LBound(myArr) To UBound(myArr) + 1)
    myArr(UBound(myArr)) = 2
End If
End Sub

Obviously this is an example so not the most elegant way of putting it but it doesn't work as I can't seem to reDim the array as it doesn't initially have a ubound or an lbound. When I dim it as myArr(0 to 0) this also fails.

Any ideas?

Upvotes: 9

Views: 30255

Answers (2)

ecoe
ecoe

Reputation: 5322

Olle's solution can be expanded upon with some more checks and balances, if it interests you.

see the InsertElementIntoArray Function here: http://www.cpearson.com/excel/VBAArrays.htm

Upvotes: 1

Olle Sjögren
Olle Sjögren

Reputation: 5385

Before using the myArr array the first time, run this:

ReDim Preserve myArr(0 To 1)

Then when you come to the dynamic ReDim statement, only use ReDim if certain conditions are met, e.g. If UBound(myArr) > 1 then etc.

If box1 = True Then
    If UBound(myArr) > 1 Then
        ReDim Preserve myArr(LBound(myArr) To UBound(myArr) + 1)
    End If
    myArr(UBound(myArr)) = 1
End If

Upvotes: 6

Related Questions