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 suppose 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.

If the original array is dimensioned as a Variant type, then one can use the ReDim statement to change the array to another type by using the As Type clause, unless you are using the Preserve keyword, in which case no changes in the data type are permitted.

This is an example of the use of the ReDim statement: 
Dim I, MyArray() As Integer   ' Declare variable and array variable.
ReDim MyArray(5) ' Allocate 6 elements.
For I = 0 To UBound(MyArray)
   MyArray(I) = I   ' Initialize array.
Next I
The next statement resizes the array without saving the contents of the elements.
ReDim MyArray(10) ' Resize to 11 elements.
For I = 0 To UBound(MyArray)
   MyArray(I) = I   ' Initialize array.
Next I
The following statement resizes the array but saves the contents of the elements.
ReDim Preserve MyArray(15) ' Resize to 16 elements.