ReDim Preserve error

Question

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?

Answer 1

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.