How to Create and Read Jagged Arrays in VBA
Arrays of Arrays(Jagged Arrays) are not the same as Multidimensional Arrays if you think about them visually Multidimensional Arrays would look like Matrices (Rectangular) with defined number of elements on their dimensions(inside arrays), while Jagged array would be like a yearly calendar with the inside arrays having different number of elements, like days in on different months.
Although Jagged Arrays are quite messy and tricky to use due to their nested levels and don't have much type safety, but they are very flexible, allow you to manipulate different types of data quite easily, and don't need to contain unused or empty elements.
Creating a Jagged Array
In the below example we will initialise a jagged array containing two arrays one for Names and another for Numbers, and then accessing one element of each
Dim OuterArray() As Variant Dim Names() As Variant Dim Numbers() As Variant 'arrays are declared variant so we can access attribute any data type to its elements Names = Array("Person1", "Person2", "Person3") Numbers = Array("001", "002", "003") OuterArray = Array(Names, Numbers) 'Directly giving OuterArray an array containing both Names and Numbers arrays inside Debug.Print OuterArray(0)(1) Debug.Print OuterArray(1)(1) 'accessing elements inside the jagged by giving the coordenades of the element
Dynamically Creating and Reading Jagged Arrays
We can as well be more dynamic in our approx to construct the arrays, imagine that we have a customer data sheet in excel and we want to construct an array to output the customer details.
Name - Phone - Email - Customer Number Person1 - 153486231 - 1@STACK - 001 Person2 - 153486242 - 2@STACK - 002 Person3 - 153486253 - 3@STACK - 003 Person4 - 153486264 - 4@STACK - 004 Person5 - 153486275 - 5@STACK - 005
We will Dynamically construct a Header array and a Customers array, the Header will contain the column titles and the Customers array will contain the information of each customer/row as arrays.
Dim Headers As Variant ' headers array with the top section of the customer data sheet For c = 1 To 4 If IsEmpty(Headers) Then ReDim Headers(0) Headers(0) = Cells(1, c).Value Else ReDim Preserve Headers(0 To UBound(Headers) + 1) Headers(UBound(Headers)) = Cells(1, c).Value End If Next Dim Customers As Variant 'Customers array will contain arrays of customer values Dim Customer_Values As Variant 'Customer_Values will be an array of the customer in its elements (Name-Phone-Email-CustNum) For r = 2 To 6 'iterate through the customers/rows For c = 1 To 4 'iterate through the values/columns 'build array containing customer values If IsEmpty(Customer_Values) Then ReDim Customer_Values(0) Customer_Values(0) = Cells(r, c).Value ElseIf Customer_Values(0) = "" Then Customer_Values(0) = Cells(r, c).Value Else ReDim Preserve Customer_Values(0 To UBound(Customer_Values) + 1) Customer_Values(UBound(Customer_Values)) = Cells(r, c).Value End If Next 'add customer_values array to Customers Array If IsEmpty(Customers) Then ReDim Customers(0) Customers(0) = Customer_Values Else ReDim Preserve Customers(0 To UBound(Customers) + 1) Customers(UBound(Customers)) = Customer_Values End If 'reset Custumer_Values to rebuild a new array if needed ReDim Customer_Values(0) Next Dim Main_Array(0 To 1) As Variant 'main array will contain both the Headers and Customers Main_Array(0) = Headers Main_Array(1) = Customers
To better understand the way to Dynamically construct a one dimensional array please check Dynamic Arrays (Array Resizing and Dynamic Handling) on the Arrays documentation.
The Result of the above snippet is an Jagged Array with two arrays one of those arrays with 4 elements, 2 indention levels, and the other being itself another Jagged Array containing 5 arrays of 4 elements each and 3 indention levels, see below the structure:
Main_Array(0) - Headers - Array("Name","Phone","Email","Customer Number") (1) - Customers(0) - Array("Person1",153486231,"1@STACK",001) Customers(1) - Array("Person2",153486242,"2@STACK",002) ... Customers(4) - Array("Person5",153486275,"5@STACK",005)
To access the information you'll have to bear in mind the structure of the Jagged Array you create, in the above example you can see that the Main Array contains an Array of Headers and an Array of Arrays (Customers) hence with different ways of accessing the elements.
Now we'll read the information of the Main Array and print out each of the Customers information as Info Type: Info.
For n = 0 To UBound(Main_Array(1)) 'n to iterate from fisrt to last array in Main_Array(1) For j = 0 To UBound(Main_Array(1)(n)) 'j will iterate from first to last element in each array of Main_Array(1) Debug.Print Main_Array(0)(j) & ": " & Main_Array(1)(n)(j) 'print Main_Array(0)(j) which is the header and Main_Array(0)(n)(j) which is the element in the customer array 'we can call the header with j as the header array has the same structure as the customer array Next Next
REMEMBER to keep track of the structure of your Jagged Array, in the example above to access the Name of a customer is by accessing Main_Array -> Customers -> CustomerNumber -> Name which is three levels, to return "Person4" you'll need the location of Customers in the Main_Array, then the Location of customer four on the Customers Jagged array and lastly the location of the element you need, in this case Main_Array(1)(3)(0) which is Main_Array(Customers)(CustomerNumber)(Name).