Dynamic Array in VBA
We can easy resize the array declared in VBA. The trick is actually relocate the array to a bigger space. Suppose we need an array, we have to declare (dim) it with unknown size. It can be any variant (string, integer, etc…):
Dim MyArray() As String
We we need to add something new to this array, we re-declare (re-dim) it to a bigger size. The
preserve is optional but needed to preserve data for the re-dimmed array.
ReDim Preserve MyArray(0 to 1) As String MyArray(0) = "First One" MyArray(1) = "Second One"
To make it general, we can build a function or class. To conclude, the ReDim syntax is:
ReDim [Preserve] ArrayName(Subscript) As DataType