I am trying to redim preserve a two dimensional array. at the very top of my code I have:
Dim BayQuestionArray() As Variant Dim numberofbay As Double
I have two buttons, the first button is for initializing the array size:
numberofbay = 1 ReDim Preserve BayQuestionArray(numberofbay, 37)
and the second button is for upsizing the array
ReDim Preserve BayQuestionArray(numberofbay + 1, 37)
The second button doesn't work, it keeps giving me an error saying Run time error 9 Subscript out of range. Why?
When using Preserve to resize an array variable (declared as such, not as Variant), you may only alter the upper boundary of the last dimension. You would need to transpose your array, or use a jagged array or other structure like a Dictionary/Collection.
The "redim preserve" command was altered recently due to a security patch. This patch effects ALL scripts using "redim preserve" written before the patch. Previous to the patch your code would have worked as written.
All "redim preserve" commands that changed the lower bound of the array no longer work. It will appear as if the code just ignores the line which can make diagnosis of effected scripts/code difficult because the first warning that the "redim preserve" is not working as expected will occur with the overflow error, potentially far from the "redim preserve" command.
For more info on the vulnerability that prompted the security patch (and to prove I am not making this up) you can reference: