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

Good
  • 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
Bad
  • 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.

Example

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.

Jagged Arrays

Good
  • Flexible
Bad
  • 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:

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:

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:

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:

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

Good
  • 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
Bad
  • 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.