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"
[/sourcecode]
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
[/sourcecode]

One response to “Dynamic array in VBA

Leave a Reply

%d bloggers like this: