How do I set up a "jagged array" in VBA?

"Jagged array" is slang for array of arrays. VBA'sVariant data type can contain just about anything*, including an array. So you make an array of type Variant, and assign to each of its elements an array of arbitrary length (i.e. not all of them have to have equal length).

Here's an example:

Dim nStudents As LongDim iStudent As LongDim toys() As VariantDim nToys As LongDim thisStudentsToys() As VariantnStudents = 5 ' or whateverReDim toys(1 To nStudents) ' this will be your jagged arrayFor iStudent = 1 To nStudents    'give a random number of toys to this student (e.g. up to 10)    nToys = Int((10 * Rnd) + 1)    ReDim thisStudentsToys(1 To nToys)    'code goes here to fill thisStudentsToys()    'with their actual toys    toys(iStudent) = thisStudentsToysNext iStudent' toys array is now jagged.' To get student #3's toy #7:MsgBox toys(3)(7)'will throw an error if student #3 has less than 7 toys

_{* A notable exception is user-defined types. Variants cannot contain these.}

You can use a collection of collections

Public Sub Test()    Dim list As New Collection    Dim i As Integer, j As Integer    Dim item As Collection    For i = 1 To 10        Set item = New Collection        For j = 1 To i            item.Add "Kid" & CStr(i) & "Toy" & CStr(j)        Next j        list.Add item    Next i    Debug.Print "Kid 4, Toy 2 = " & list(4)(2)End Sub

Which outputs Kid 4, Toy 2 = Kid4Toy2

Jean-Francois pointed out that each element can be an array of varying length. I would add that each element can also be of other types and need not be arrays. For example:

Dim c as New CollectionDim a(1 to 5) as Variantc.Add "a","a"c.Add "b","b"a(1) = 5a(2) = Array(2,3,4)set a(3) = ca(4) = "abcd"a(5) = Range("A1:A4").Value

The various child elements can then be referenced depending on the implicit type of each:

a(2)(1) = 3

a(3)(1) = "a"

a(5)(2,1) = whatever is in cell A2.