How do I set up a “jagged array” in VBA?

by

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

More Related Contents:

  1. Excel-VBA – Insert new first column in datafield array without loops or API calls
  2. Expanding column cells for each column cell
  3. How to find if an array contains a string [duplicate]
  4. How do I slice an array in Excel VBA?
  5. Check if a value is in an array or not with Excel VBA
  6. How to search for string in an array
  7. Sorting a multidimensionnal array in VBA
  8. Matching values in string array
  9. Declare and Initialize String Array in VBA
  10. Deleting Elements in an Array if Element is a Certain value VBA
  11. Is it possible to fill an array with row numbers which match a certain criteria without looping?
  12. Return Index of an Element in an Array Excel VBA
  13. Writing an array to a range. Only getting first value of array
  14. Excel VBA: Range to String Array in 1 step
  15. Faster way of using Sumifs()
  16. Turn Excel range into VBA string
  17. Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
  18. Why am I having issues assigning a Range to an Array of Variants
  19. Subscript out of range error with an array – no idea why?
  20. VBA Excel large data manipulation taking forever
  21. Performance difference between looping range vs looping array
  22. how to combine duplicate rows and sum the values 3 column in excel
  23. Populating VBA dynamic arrays
  24. Dynamically Dimensioning A VBA Array?
  25. Create Chart from Array data and not range
  26. VBA – Returning array from Property Get
  27. How do I Merge two Arrays in VBA?
  28. Excel VBA Quickest way to sort an array of numbers in descending order?
  29. Simple VBA array join not working
  30. ReDim Preserve “Subscript Out of Range”

Leave a Comment