There are a variety of ways in VBA to have collections of collections. All of them have benefits and drawbacks.
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.
You declare multidimensional arrays by including multiple bounds separated by commas:
Dim intMatrix(0 to 2, 0 to 4) As Integer
You can dynamically increase the last dimension of a multidimensional array if you first declare the array without any bounds:
Dim intMatrix() As Integer ' Uninitialized dynamic array
ReDim intMatrix(0 to 4, 0 to 2) ' Initialize as a matrix
ReDim Preserve intMatrix(0 to 4, 0 to 3) ' Add another "row" to the matrix, preserving existing data
Jagged Arrays
Good:
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:
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:
Set objWorksheet = outer(2)(3)
Although 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 array 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)
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)
ReDim Preserve tempInts(0 to 5)
outer(2) = tempInts
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
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.
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
