Collections of Collections in VBA

December 2, 2013 in VBA

In many situations using collections of one kind or another is very useful. Once in a while, you even want to go another step and use collections of collections. Just as there are several types of collections in VBA, there are several ways you can use collections of collections. All of them have benefits and drawbacks. I’ll provide a summary of them here.

Nested collection types

The three types of nested collections I’ll cover in this article are as follows:

Multidimensional arrays

  • Simple syntax (only one variable)
  • Type safety. All elements of a matrix of Integer are known and enforced to be Integers.
  • Very fast array access
  • If there are large differences in the size of the inner arrays, a matrix will waste some space because there are unused “cells” in the matrix.
  • You can only change the bounds of the last dimension with ReDim Preserve. So you can’t add “columns” to a matrix without clearing all the data. See Types of Arrays for more detail.

The most direct way to have a nested collection is with a multidimensional array. Arrays in VBA can have up to 60 dimensions. Multidimensional arrays are useful when all the elements are going to be of the same data type, and all but one dimension will have a predictable (constant) size.


You are running some statistics about a multiple choice test. All questions have 5 possible answers. You can keep track of the answers for a test in a 2-dimensional array of Integer. One dimension represents the question number, the other dimension represents the test number (such as 1 for the first student to 25 for the 25th student), and each element stores the choice number (1-5). This is a good fit for a multidimensional array because all elements are of the same type (Integer), and the number of questions on the test is the same for all instances of the test.

Dim arrAnswers() As Integer
Dim numberOfQuestions As Integer = 17
Dim numberOfStudents As Integer = 25
Dim q As Integer, s As Integer
Redim arrAnswers(1 To numberOfQuestions, 1 To numberOfStudents)

For q = 1 to numberOfQuestions
    For s = 1 to numberOfStudents
        arrAnswers(q, s) = ... ' Get the answer from somewhere

Jagged Arrays

  • Flexible
  • You lose compile-time type safety
  • They are a bit tricky / messy because of the nested structure
  • It is awkward and expensive to resize the inner arrays

You can create jagged arrays be declaring an outer array of type Variant(), and assigning other arrays to the elements of the outer array:

Dim outer() As Variant  ' Dynamic, so new inner arrays can be added
Dim inner() As Integer  ' Dynamic, so new elements can be added

ReDim outer(0 to 3)
ReDim inner(0 to 4)
outer(2) = inner
Lost compile-time type information

All the compiler “knows” about the outer array is that it can contain anything. So the following code will compile, though at runtime this will cause an error because the inner array at outer(2) contains Integers, not Worksheet objects:

Set objWorksheet = outer(2)(3)
Awkward to resize

One of the benefits of jagged arrays is that the inner arrays can be of different sizes. However, you cannot directly resize an inner array. VBA just can’t handle the syntax; the following doesn’t compile:

ReDim Preserve outer(2)(0 to 5)   ' This doesn't work

In order resize an inner array you first have to assign the inner array to a separate variable, resize that variable, and then assign it back to the jagged array:

Dim tempInts() As Integer
tempInts = outer(2)             ' 1. Copy FROM the jagged aray
ReDim Preserve tempInts(0 to 5) ' 2. Resize
outer(2) = tempInts             ' 3. Copy back TO the jagged array

The reason you have to reassign tempInts back to the outer array is that arrays use by-value semantics in VBA. That means when you assign an array to a variable (as in tempInts = outer(2)), you copy the entire array. That can be very expensive if your array is long (say a few thousand elements), and even more expensive if your array contains strings, because every single string must also be copied.

Jagged Collections

Jagged Collections

  • Simple syntax for adding and removing elements
  • Just as flexible as jagged arrays
  • Collections use by-reference semantics so assigning is cheap, and you can have multiple references to the same collection object
  • Like jagged arrays, there is no type safety

If you will be adding elements to your inner arrays frequently, it will be a lot easier to use Collection objects instead of arrays. Collections do not enforce the data type of their elements, so this has the same drawbacks of using Variant arrays — but you must do that to use jagged arrays anyways.

Dim cAnimals As New Collection 

' Let's add stats on the Cheetah
Dim cCheetah As New Collection

' Easy to add inner collections to the outer collection.  Also, cCheetah refers
' to the same collection object as cAnimals(1).  
cAnimals.Add cCheetah          

' Easy to add items to inner collection.
' Working directly with the cCheetah collection:
For Each vMeasurment In GetMeasurements("Cheetah")
    cCheetah.Add vMeasurement

' Working on the same collection by indexing into the outer object
For i = 1 To cAnimals.Count
    For j = 1 To cAnimals(i).Count
        cAnimals(i)(j) = cAnimals(i)(j) * dblNormalizingFactor