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