There are many kinds of arrays in Visual Basic for Applications. Specifically, there are three fundamental attributes of each array, determined at compile time by how the array is declared:

  • Element data type
  • Number of dimensions
  • Fixed or Dynamic Length

Each of these attributes will be discussed in detail in this article.

Element data type

Just as scalar (non-array) variables have a data type, arrays have a data type that restricts what can go in the elements of the array. As we will see in a later post all about array assignments, the declared data type of an array is more restrictive that for a scalar variable: Only arrays declared with exactly the same data type can be assigned to each other. In other words, inheritance and Variant semantics are not considered when assigning arrays in VBA.

Number of dimensions

Most arrays have only one dimension, but in VBA arrays can have up to 60 dimensions. Additional dimensions are added to an array by declaring multiple sets of bounds separated by commas (example below). Note that an array with multiple dimensions is not the same as a jagged array (an array of arrays). Both jagged arrays and multidimensional arrays are available in VBA.

Fixed or Dynamic Length

The length of an array can be fixed at declaration by explicitly setting bounds, or can be dynamic if no bounds are specified. If an array has explicit (fixed) bounds it can be used right away, but dynamic arrays must be resized at least once before they can be used. Dynamic arrays are resized using the Redim statement. If the Preserve keyword is supplied, any existing elements already in the array being resized will remain intact. If it is omitted all elements will be erased.

Multidimensional Dynamic Arrays

Dynamic arrays can have more than one dimension. Multidimensional dynamic arrays are created by specifying multiple dimension bounds when using the Redim statement. When a dynamic array has more than one dimension, the Preseve keyword will only be respected if all dimensions retain the same bounds except for the last dimension. For a 2-dimensional dynamic array this is like a table, where the “columns” (the first dimension) must be fixed but “rows” (the second dimension) can be added or removed. See below for a full illustration: