DYNAMIC ARRAYS

In case you have no idea how large an array needs to be, then you may create dynamic arrays that does not have a preset number of elements.

They are declared with a blank set of parentheses.

For example, 

Dim MyArray( ) As Integer
Before you can use the array, you must use the ReDim (for re-dimension) statement to tell VBA how many elements the array has.

Usually the number of elements in the array is determined while your code is running, from the result of a calculation or based on user input.

The ReDim statement can be used any number of times to dynamically change the array's size as needed. For example, if the variable NumElements is a variable that contains a value, then you can use the following statement to redimension the array: 
ReDim MyArray(NumElements)
When you redimension an array by using ReDim, you wipe out all values currently stored in the array.

This can be avoided by using the Preserve keyword. For example,

ReDim Preserve MyArray(NumElements)
So supposed MyArray currently has a size of 10 and contains 10 values. After executing the preceding statement with NumElements greater than 10, the first 10 elements remain intact and the array has room for additional elements up to the number specified by NumElements.