Arrays in VBA – Part 1: Types of Arrays

March 29, 2013 in VBA

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.

' Declaring arrays of different data types
Dim vArray1()              ' Implicitly an array of Variant
Dim vArray2()  As Variant  ' The same thing as vArray1
Dim lngArray() As Long     ' An array of Longs
Dim objArray() As Object   ' An array of Objects
Dim udtArray() As MY_UDT   ' An array of a user defined type

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.

' Declaring arrays with different numbers of dimensions
' A normal 1-dimensional array of Long:
Dim lngArray(0 to 3) As Long             

' A 2-dimensional matrix of Double:
Dim my2DMatrix(0 to 2, 0 to 3) As Double 

' A 4-dimensional array of Object:
Dim hyperCube(0 to 1, 0 to 1, 0 to 1, 0 to 1) As Object

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.

' -----------------------------------------
'  Fixed-length arrays
' -----------------------------------------
Dim buffer(255)    As Byte
Dim vItems(6)      As Variant
Dim sysTables(4)   As TableDef

' All elements are initialized to the default value for
' the declared element type
Debug.Print buffer(0)              ' Prints : 0
Debug.Print TypeName(vItems(0))    ' Prints : Empty
Debug.Print TypeName(sysTables(0)) ' Prints : Nothing
' You can also assign values to a fixed-length array right away
buffer(3) = Asc("A")
vItems(2) = "Hello"
Set sysTables(0) = CurrentDb.TableDefs("MSysObjects")

' -----------------------------------------
'  Dynamic-length arrays
' -----------------------------------------
Dim strArray() As String

' You cannot use this array yet. Even trying get the upper bound will 
' cause an error. The following prints 'Error 9 : Subscript out of range'
On Error Resume Next
Debug.Print UBound(strArray)
If Err.Number <> 0 Then Debug.Print "Error"; Err.Number; ": "; Err.Description
On Error GoTo 0

' Intialize with 2 elements (0 to 1)
ReDim strArray(1)   
strArray(0) = "Hello"
strArray(1) = "world!"
Debug.Print Join(strArray)  ' Prints : Hello world!

' Resize to 5 elements, preserving existing data
ReDim Preserve strArray(4)
strArray(2) = "How"
strArray(3) = "are"
strArray(4) = "you?"
Debug.Print Join(strArray)  ' Prints : Hello world! How are you?

' Resize to one element, but still preserve the data
' that exists in that element
ReDim Preserve strArray(0)
Debug.Print Join(strArray)  ' Prints : Hello

' Reinitialized with 3 elements. Existing elements erased
ReDim strArray(2)
Debug.Print Join(strArray)  ' Prints an empty line
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:

Public Enum AnimalCols
    amlName = 0
    amlSpecies = 1
    amlGenus = 2
    amlFamily = 3
    amlClade = 4
    amlConsvStatus = 5
End Enum

Sub MatrixExample()
    ' Declared with no dimensions
    Dim animals() As String
    ' Redimmed to 2D "matrix" with one "row"
    ReDim animals(0 To 4, 0)
    ' Add a "row" to the matrix
    animals(amlName, 0) = "Walrus"
    animals(amlSpecies, 0) = "Odobenus rosmarus"
    animals(amlGenus, 0) = "Odobenus"
    animals(amlFamily, 0) = "Odobenidae"
    animals(amlClade, 0) = "Pinnipedia"
    ' First row prints:
    PrintMatrix animals
    ' Add two more rows. First bound is the same, so Preserve is respected
    ReDim Preserve animals(0 To 4, 2)
    animals(amlName, 1) = "Turkey"
    animals(amlSpecies, 1) = "Meleagris gallopavo"
    animals(amlGenus, 1) = "Meleagris"
    animals(amlFamily, 1) = "Meleagridinae"
    animals(amlClade, 1) = "Galliformes"
    animals(amlName, 2) = "Platypus"
    animals(amlSpecies, 2) = "Ornithorhynchus anatinus"
    animals(amlGenus, 2) = "Ornithorhynchus"
    animals(amlFamily, 2) = "Ornithorhynchidae"
    animals(amlClade, 2) = "Monotremata"
    ' All three rows print:
    PrintMatrix animals
    ' Add a column? Unfortunately this is not possible
    ' This is not allowed and will raise an error at runtime:
    ReDim Preserve animals(0 To 5, 2)
    ' ... and this will erase the array:
    ReDim animals(0 To 5, 2)
End Sub

Sub PrintMatrix(ByRef Matrix() As String)
    Dim ubx As Long, uby As Long, i As Long, j As Long
    Dim row() As String
    ubx = UBound(Matrix, 1)
    uby = UBound(Matrix, 2)
    ReDim row(ubx)
    For j = 0 To uby
        For i = 0 To ubx
            row(i) = Matrix(i, j)
        Debug.Print Join(row, ",")
End Sub