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

Question

I have a classroom full of kids, each of whom have to list their favorite toys for an assignment. Some kids only list 1 toy whereas others list more.

How do I create a jagged array such that Kids(x)(y)...where x is the number of kids in my class, and y is the list of toys that they list as their favorites?

Answer 1

"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 Long
Dim iStudent As Long
Dim toys() As Variant
Dim nToys As Long
Dim thisStudentsToys() As Variant

nStudents = 5 ' or whatever

ReDim toys(1 To nStudents) ' this will be your jagged array

For 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) = thisStudentsToys
Next 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.}

Answer 2

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

Answer 3

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 Collection
Dim a(1 to 5) as Variant

c.Add "a","a"
c.Add "b","b"
a(1) = 5
a(2) = Array(2,3,4)
set a(3) = c
a(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.

Answer 4

You could also concatenate the list of toys into eg a pipe-separated string, then use Split to turn the string into an array when needed:

Sub UntangleTheString()

Dim sToys As String
Dim aToys() As String
Dim x As Long

sToys = "baseball|doll|yoyo"

aToys = Split(sToys, "|")

For x = LBound(aToys) To UBound(aToys)
    Debug.Print aToys(x)
Next

End Sub

Answer 5

You don't necessarily need a jagged array to handle your scenario as a 2D array (r, c) will also work. One row for each child and one column for each present. The array dimensions will be (# of children, MAX # of presents) and it will just mean that some of the slots will be empty or 0 (depending on your data type). But at least this way you won't need to redim the array each time you add a present for a child.