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…):
[sourcecode language=”vb”]Dim MyArray() As String[/sourcecode]

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.
[sourcecode language=”vb”]
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:
[sourcecode language=”vb”]
ReDim [Preserve] ArrayName(Subscript) As DataType

Show Comments