Jagged Arrays in VBA

Tags

advanced vba topics, coding techniques, complex data structures, for each loop, for loops, jagged arrays, multi-dimensional arrays, vba arrays, vba data structures, vba data types

Jagged arrays are one of the lesser known data structures in VBA. In some cases, it may be a viable an alternative to a traditional 2D multi-dimensional array. Today’s post will present three different approaches to implementing jagged arrays in your VBA project.

Overview

In its most basic form, a jagged array is an array of arrays. Each index of a one-dimensional array will hold another one-dimension array. These arrays are joined together in a single data structure effectively making a 2D array. Our previous post on multi-dimension arrays will provide you with a good overview of the conceptual structure of a 2D array. For this post, it’s important to visualize a 2D array as an array holding data in a table format. When printed, a 2D array’s first index represents rows, while the second index represents columns. We can think of jagged arrays as being very similar to traditional 2D arrays.

Example File

The example file provides a working version of all procedures discussed in this post. Macros must be enabled upon opening. [wpdm_package id=’9622′]

Jagged Array Vs. 2D Multidimensional Array

While a jagged array is structured similarly to a 2D array, there are a few key differences.

Syntax

First, some array terminology:

• Subscript: the values in brackets that refer to the index of an array.
• Index: the numeric value referring to a specific spot in an array that does (or can) contain an element.
• Element: the value returned from a specific index of an array.

If you’re reading this, you’re likely familiar with the syntax of 2D arrays, shown here: arr(3, 4) = 100 This will assign the value 100 to index 3, 4 of 2D array arr. If we were to think of this as a table (for simplicity, let’s assume—just this once—that the index is one-based), the value 100 will exist in the 3rd row and 4th column. Notice the the subscript value “3, 4” exists within a single set of brackets. For a jagged array, the syntax for the same index location would be: arr(3)(4) = 100 The only real difference in syntax is within the subscript. Instead of the indices contained within a single set of brackets, they are contained in two separate sets of brackets. If we go back to the general premise of a jagged array, this makes sense: a jagged array is an array of arrays. Each element of the top level (parent) one-dimensional array will contain another (child) one-dimensional array. The index value “3” points to the 3rd element of the top level (parent) array, in which the index value “4” points to the 4th element of the bottom level (child) array.

Size Constraints

While a traditional 2D array must have the same width for all elements, the width of a jagged array can vary. The following figure exemplifies this, showing how a 2D array is rigid, requiring the same number of elements for each row (2nd dimension), while a jagged array is flexible, permitting different number of elements in each row. Most would consider this an advantage. In a traditional 2D array, you would need to populate missing values with either a blank string or zero value as a work-around to “empty” values. This is not required in a jagged array.

Looping Flexibility

To loop through both levels of a traditional 2D array, you would need to use a basic For loop (or in some cases, a Do loop). This would require you to find the array’s upper-bound by using the UBound function. With a jagged array, a For Each loop can be used to loop through both dimensions.

Ease of Creation

In most situations, jagged arrays will require a bit more code to be constructed. While a traditional 2D array can be created as a single data structure, jagged arrays require at least three one-dimensional arrays to be built. We’ll discuss a few methods of building jagged arrays later in this post.

Speed

Unfortunately speed is not a benefit for jagged arrays. Even when working with modestly sized jagged arrays, there will be a noticeable lag when printing.

Methods for Creating and Printing Jagged Arrays

This post will go over three different methods of populating and printing jagged arrays. While the general concept and structure of a jagged array is similar to it’s 2D array counterpart, building jagged arrays is quite different. For the purpose of this post, we’ll refer to the first dimension of a jagged array as a “parent array”, and the second dimension of a jagged array as a “child array.” Child arrays will exist within the indices of its parent array.

Explicit Populate and Print

We can explicitly populate a jagged array by creating multiple one-dimensional arrays to assign to the indices of a single one-dimensional parent array. The procedure below creates a jagged array containing a total of five elements: the first parent element hosts a child array of two elements, while the second hosts a child array of three elements. In all, this data structure will be built with three arrays. Before we get started assigning values, we must first declare the parent and child arrays. Given that the parent array has a known size, we will declare its type and bounds at the top of the procedure: Dim parentArray(0 To 1) As Variant. The parent array must always be declared as a Variant. We’ll declare a child array as an unsized String array: Dim childArray() As String. Within the procedure, we will re-use the same child array for the second parent element. A ReDim statement on line 13 clears the previous child array values and sets a new size. A possible alternative would be to declare two unique child arrays at the top of the procedure. Referring back to the table structure, we can think of constructing the jagged array left to right, one row at a time. First, we build a one-dimensional child array to assign to the first index (row) of the parent array. This is done in lines 9 and 10 below, containing two elements. In line 11, this child array is then assigned to the first element (index zero) of the parent array. The second row is then constructed similarly in rows 13 through 17 below. To explicitly print these, both array indexes must be referenced as shown in lines 20-24 below. Unlike a traditional 2D array, which would reference a combined index in a single set of brackets, e.g. arr(2,1), a jagged array will use two separate sets of brackets to access the indices of the parent and child arrays, e.g. arr(2)(1). In this procedure, we explicitly assign each array element to its own unique cell on a spreadsheet.

Sub Jagged_Array_Explicit()

    Dim parentArray(0 To 1) As Variant
    Dim childArray() As String
    
    Sheet1.Range("B2:G7").ClearContents ' Clear prior data
    
    ReDim childArray(0 To 1) As String ' Proper size of 2nd dimension.
    childArray(0) = "ZERO"
    childArray(1) = "ONE"
    parentArray(0) = childArray ' Assign 2nd dimension (child) to first element of 1st dimension (parent).

    ReDim childArray(0 To 2) As String ' Proper size of 2nd dimension.
    childArray(0) = "ZERO"
    childArray(1) = "ONE"
    childArray(2) = "TWO"
    parentArray(1) = childArray ' Assign 2nd dimension (child) to second element of 1st dimension (parent).
    
    ' Explicilty print all values directly on sheet
    Sheet1.Cells(2, 2).Value = parentArray(0)(0)
    Sheet1.Cells(2, 3).Value = parentArray(0)(1)
    Sheet1.Cells(3, 2).Value = parentArray(1)(0)
    Sheet1.Cells(3, 3).Value = parentArray(1)(1)
    Sheet1.Cells(3, 4).Value = parentArray(1)(2)
    
End Sub

Multiple Data Types and For Loop Printing

This procedure is conceptually similar to the previous example, but contains two key differences: the bottom level (child arrays) now consist of three different data types, and a nested For loop is used to print the array data on a spreadsheet. It’s important to realize that child arrays are independent of one another. A child array’s structure has no impact on any other child arrays (let’s call them siblings) in the jagged array. Additionally, the parent array (as a Variant) doesn’t care about the data type of its child arrays. In this example, the first three rows of the jagged array are built (lines 12 through 38) using the same child array, childArrayStr, which is set up to handle a one-dimensional array of the String data type. The fourth row of the jagged array is built using a new child array, childArrayInt, which is set up to handle a one-dimensional array of the Integer data type. Finally, the fifth and last row of the jagged array is built using another new child array, childArrayLng, which is set up to handle a one-dimensional array of the Long data type. As a last step for each row of data, the child array is assigned to a unique index of the parent array in lines 17, 26, 33, 38, 46, and 52. Note that the ReDim statement can only be used to change the size of an array, not change its data type. This explains why we need three different child arrays in this example — one for each data type. If we wanted, we would not need to ReDim arrays which are only used once, like the final two — they could be declared as their actual size at the top of the procedure, but I retained ReDim in this example for consistency. This procedure uses a nested For loop to print the array data on the spreadsheet. This technique is similar to obtaining output from a traditional 2D array. The nested loop works by cycling through each element of the parent array array in the outer loop, then cycling through each element of the child array in the inner loop. Within the loop, two counter variables, i and j cycle through the jagged array using the syntax summarize earlier in this post: parentArray(i)(j). It’s also important to note that to obtain the upper bound of each child array, you must pass the parent array element as an argument in the UBound function. In this example, counter variable i is used to cycle through the parent array, so the upper-bound in the inner loop will be specified as UBound(parentArray(i)). This may look unusual, as you typically do not pass an argument containing a subscript through the UBound function, but it is necessary in this case.

Sub Jagged_Array_For()

    Dim parentArray(0 To 5) As Variant
    Dim childArrayStr() As String
    Dim childArrayInt() As Integer
    Dim childArrayLng() As Long
    Dim i As Integer
    Dim j As Integer
    
    Sheet1.Range("B2:G7").ClearContents ' Clear prior data
    
    ReDim childArrayStr(0 To 3) As String ' Proper size of 2nd dimension.
    childArrayStr(0) = "A"
    childArrayStr(1) = "jagged"
    childArrayStr(2) = "array"
    childArrayStr(3) = "permits"
    parentArray(0) = childArrayStr ' Assign 2nd dimension (child) to first element of 1st dimension (parent).
    
    ReDim childArrayStr(0 To 5) As String ' Proper size of 2nd dimension.
    childArrayStr(0) = "the"
    childArrayStr(1) = "second"
    childArrayStr(2) = "dimension"
    childArrayStr(3) = "of"
    childArrayStr(4) = "an"
    childArrayStr(5) = "array"
    parentArray(1) = childArrayStr ' Assign 2nd dimension (child) to second element of 1st dimension (parent).
    
    ReDim childArrayStr(0 To 3) As String ' Proper size of 2nd dimension.
    childArrayStr(0) = "to"
    childArrayStr(1) = "vary"
    childArrayStr(2) = "in"
    childArrayStr(3) = "size"
    parentArray(2) = childArrayStr ' Assign 2nd dimension (child) to third element of 1st dimension (parent).
    
    ReDim childArrayStr(0 To 1) As String ' Proper size of 2nd dimension.
    childArrayStr(0) = "and"
    childArrayStr(1) = "type"
    parentArray(3) = childArrayStr ' Assign 2nd dimension (child) to third element of 1st dimension (parent).
    
    ReDim childArrayInt(0 To 4) As Integer ' Proper size of 2nd dimension.
    childArrayInt(0) = 0
    childArrayInt(1) = 1
    childArrayInt(2) = 2
    childArrayInt(3) = 3
    childArrayInt(4) = 4
    parentArray(4) = childArrayInt ' Assign 2nd dimension (child) to fourth element of 1st dimension (parent).

    ReDim childArrayLng(0 To 2) As Long ' Proper size of 2nd dimension.
    childArrayLng(0) = 100000
    childArrayLng(1) = 200000
    childArrayLng(2) = 300000
    parentArray(5) = childArrayLng ' Assign 2nd dimension (child) to fifth element of 1st dimension (parent).

    For i = 0 To UBound(parentArray)
        For j = 0 To UBound(parentArray(i))
            Sheet1.Cells(i + 2, j + 2).Value = parentArray(i)(j)
        Next j
    Next i

End Sub

Array Building Function and For Each Loop Printing

The final procedure is quite different from the previous two. Instead of explicitly building individual child arrays and subsequently assigning them to indices of the parent array, we’ll assign an array building function to indices of the parent array. We’ll use VBA’s Split function to convert sentences into substring arrays made up of individual words. You can read more about the split function in this post. In summary, the Split function will build an array from a string and delimeter. If a sentence is the string and a space is the delimeter, a one-dimensional array will be returned containing all words as individual elements. We’ll start by declaring the parent array as a Variant array with six indices: Dim parentArray(0 To 5) As Variant. One advantage of this approach is that we do not need to declare (or ReDim) any child arrays as the Split function returns a proper array. In lines 11 through 16, we directly assign the Split function to each index of the parent array, creating six separate child arrays that are instantly added as elements to the parent array. We’re also doing something unique to print this jagged array. As stated above, one of the advantages of jagged arrays over 2D multi-dimensional arrays is that they can be cycled through using nested For Each loops. To do this, we must declare two Variant variables to handle element output from the outter and inner loops. I’ve declared these as loopLevOne and loopLevTwo. The outer loop begins in line 19, where the parent array parentArray is cycled through, extracting its individual elements to the loopLevOne variable. It’s important to realize that the loopLevOne variable now contains the child array, so this variable can now be passed through the inner loop, as is done in line 21. This will subsequently return individual elements from the child array as the loopLevTwo variable. We can then assign loopLevTwo values to a cell for printing. Also note that I’ve included counter variables i and j in lines 18, 20, 23, and 25 to manage printing locations on the spreadsheet.

Sub Jagged_Array_For_Each()

    Dim parentArray(0 To 5) As Variant
    Dim loopLevOne As Variant
    Dim loopLevTwo As Variant
    Dim i As Integer
    Dim j As Integer
    
    Sheet1.Range("B2:G7").ClearContents ' Clear prior data
    
    parentArray(0) = Split("One advantage of", " ")
    parentArray(1) = Split("a jagged array is that", " ")
    parentArray(2) = Split("a For Each loop", " ")
    parentArray(3) = Split("can be used to access", " ")
    parentArray(4) = Split("both dimensions of the", " ")
    parentArray(5) = Split("array.", " ")

    i = 2
    For Each loopLevOne In parentArray
        j = 2
        For Each loopLevTwo In loopLevOne
            Sheet1.Cells(i, j).Value = loopLevTwo
            j = j + 1 ' Include counter for column assignment.
        Next loopLevTwo
        i = i + 1 ' Include counter for row assignment.
    Next loopLevOne

End Sub

Other Notes

• Like multi-dimensional arrays, there is no limit to the number of dimensions that can be added to jagged arrays. All levels above the very bottom level must be declared as Variant data types.
• In the first two examples, child arrays of the same data type were re-used more than once. This is not necessary — you are able to declare multiple unique child arrays at the time of procedures. This may help you avoid the notoriously slow ReDim statement.