VBA Types of Arrays in Excel

Types of Arrays in Excel VBA. There are two types of VBA arrays in Excel. The first one is Static Array and the second one is Dynamic Array.

Static Array:

A Static array is an array of fixed size. It stores pre-defined number of elements. We can’t change the size and data type of static array. Static array is good when we know the array exact size. Static array never empty.

Syntax:

Let us see the syntax of the static array.

Dim aArrayName(N) As DataType

Where ‘N’ represents an array size. An array named ‘aArrayName’ stores ‘N’ elements.
Example on Static Array:

Dynamic Array:

A dynamic array is an array can be resized during run time. We can resize an array using ‘ReDim’ statement. A dynamic array does not have a pre-defined number of elements. We can change the size and cannot change the data type of dynamic array.  When you don’t know the size of an array, you can use dynamic array.

Syntax:

Let us see the syntax of the dynamic array.

Dim aArrayName() As DataType
ReDim aArrayName(N)

Where ‘N’ represents an array size. An array named ‘aArrayName’ stores ‘N’ elements.
Example on Dynamic Array:

Tips to Remember:

1. The dynamic arrays are created with empty parentheses.
2. Set the size of an array using ‘ReDim’ statement.
3. The ‘ReDim’ creates a new static array and allocates memory.
4. You can use as many times ‘ReDim’ statement for Dynamic Array.
5. We cannot use ‘ReDim’ statement for static arrays.
6. It is a basic one-dimensional array. One-dimensional array has one index(N).
7. We can resize of an array by modifying upper bound of an array.
8. You cannot resize the lower bound of an array.

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Arrays in Excel VBA Tutorial VBA Functions List Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers