How to create and use Dynamic Arrays in VBA

Arrays are important elements of any programming language. An array can be one, two or multi dimensional. An Array declared without a specific size (re-sizable) is called a Dynamic Array. Dynamic Array can be declared as shown below.

Dim NewArray () as Integer

However, to use the Array in application, We must define its size. To change size of an Array we use ReDim keyword.

ReDim NewArray (4) 


Now, NewArray can store up to 5 values (i.e. 0 to 4). Let us assign 5 values to the Array.

NewArray = Array (5,10,15,20,25)

ReDim can be used multiple times in our application to change the size of an Array dynamically. Suppose we want to change the size of NewArray again, so that it can store 10 values, then use ReDim again as below.

ReDim NewArray (9)

But changing the dimension of Array using ReDim statement clears the previously stored values. If we want to retain the previously stored values, then we use Preserve keyword as shown below. By doing this old values stored in an array remain unchanged.

ReDim Preseve NewArray (9)