How do I set up a "jagged array" in VBA?
arrays, jagged-arrays, vba
Solution
"Jagged array" is slang for array of arrays. VBA's`Variant` 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.
Problem
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?