Dynamically appending an array in VBA
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)) = 1End If
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