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.}