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
1 comment
  1. Ian said:

    It is very good and clear. THX~~~~

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: