Types of Arrays

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.

Excel VBA Arrays:

Please click on below link to see other VBA Array concepts.

VBA Arrays in Excel

Excel VBA Tutorial:

Please click on below link to go back to see Excel VBA Tutorial table of content.

Excel VBA Tutorial

Leave a Reply

Your email address will not be published. Required fields are marked *