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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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 Next Next |
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:
|
1 2 3 4 5 6 |
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:
|
1 |
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:
|
1 |
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:
|
1 2 3 4 |
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
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 Next ' 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 Next Next |