Excel VBA - Create a jagged two dimensional array

[Harry Flashman]

I can create a two dimensional array, but in this case I think I really need a jagged two dimensional array.

Cell A1 contains the following:

A,1,2,3;B,1,2,3,4,5,6;C,1,2

<tbody>
</tbody>

The semicolons split the data into rows, and the commas split the data in to columns.

I can take this value and put it into a two dimensional array with the following routine:

Sub PutCellContentsIntoTwoDimensionaArray()

    Dim rng As Range
    Set rng = Range("A1")
    
    Dim arrRow As Variant
    arrRow = Split(rng, ";")
    
    Dim i As Integer, j As Integer, cnt As Integer
    For i = LBound(arrRow) To UBound(arrRow)
        
        cnt = UBound(Split(arrRow(i), ","))
        If cnt > j Then
            j = cnt
        End If
        
    Next i
    
    Dim r As Integer, c As Integer
    Dim arrCol As Variant, arr() As Variant
    
    For r = LBound(arrRow) To UBound(arrRow)
        arrCol = Split(arrRow(r), ",")
        For c = LBound(arrCol) To UBound(arrCol)
            ReDim Preserve arr(0 To i, 0 To j)
            'Debug.Print c & " - " & arrCol(c)
            arr(r, c) = arrCol(c)
        Next c
    Next r
    
    ' print the first value in each row
    Debug.Print arr(0, 0)
    Debug.Print arr(1, 0)
    Debug.Print arr(2, 0)

End Sub

The trouble is it is a ordinary rectangular two dimensional array. Is there some way I can modify this code to create a two dimensional jagged array instead (assuming this is possible).

My routine can print the first value in each row of the array, but I am not sure how to print the last value in each row of the array, as the number of columns containing values changes with each row.

Any advice would be greatly appreciated. Cheers

 

[Harry Flashman]

Btw my terminology might be wrong. I see that there are such a thing as Jagged Arrays, which apparently are one-dimensional, array of arrays.

Collection of VBA Code Snippets and Useful Excel Knowledge - Working with Arrays in VBA

I don't know enough about them to know for sure if this what I want.

At any rate I need to be able to know how many items are each row, and to be able to return the last (non blank) item in each row.

 

[Harry Flashman]

I have added comments to the routine to make it a bit easier to understand.

Sub GetValuesAndPutIntoTwoDimensionaArray()

    Dim rng As Range
    Set rng = Range("A1")
    
    Dim arrRow As Variant
    arrRow = Split(rng, ";")
    
    ' figure out how many columsn are in the two-dim array
    Dim i As Integer, j As Integer, cnt As Integer
    For i = LBound(arrRow) To UBound(arrRow)
        
        cnt = UBound(Split(arrRow(i), ","))
        If cnt > j Then
            j = cnt
        End If
        
    Next i
    
    i = i - 1
    
    ' i + 1 is the number of rows
    ' j + 1 is the number of columns
    
    Dim r As Integer, c As Integer
    Dim arrCol As Variant, arr() As Variant
    
    ' loop through each row of the array
    For r = LBound(arrRow) To UBound(arrRow)
    ' and split the comma separated values from each row into separate columns
        arrCol = Split(arrRow(r), ",")
        For c = LBound(arrCol) To UBound(arrCol)
            ReDim Preserve arr(0 To i, 0 To j)
            'Debug.Print c & " - " & arrCol(c)
            ' populate our two-dim array
            arr(r, c) = arrCol(c)
        Next c
    Next r
    
    ' print the column headers
    Debug.Print arr(0, 0)
    Debug.Print arr(1, 0)
    Debug.Print arr(2, 0)

End Sub

 

[Rick Rothstein]

If you don't mind having the arrays be zero-based (that is, the first element for each dimension is 0, not 1) and if you don't mind using an odd syntax to address the array elements, then give this code a try...

Sub CreateJaggedArray()

  Dim R As Long, C As Long, Jag() As String, Jagged() As Variant
  
  [COLOR="#008000"]' Create the jagged array from the text in cell A1.[/COLOR]
  Jag = Split(Range("A1").Value, ";")
  ReDim Jagged(0 To UBound(Jag))
  For R = 0 To UBound(Jag)
    Jagged(R) = Split(Jag(R), ",")
  Next
  
  [COLOR="#008000"]' Show the data stored in the jagged array starting in cell C3.[/COLOR]
  [COLOR="#008000"]' Note the "odd" syntax for addressing the array's two dimensions.[/COLOR]
  For R = 0 To UBound(Jagged)
    For C = 0 To UBound(Jagged(R))
      Range("C3").Offset(R, C).Value = Jagged(R)(C)
    Next
  Next
  
End Sub

The elements of the array are specified by enclosing each dimension in its own set of parentheses and not as a comma delimited list.

 

[Harry Flashman]

That is brilliant thanks Rick. One more question if you don't mind.

How can I find out the number of elements in each row of the jagged array?

For example the row starting with A has three additional elements, while row B has six additional elements. How can I return the number of elements for a specified row of this jagged array?

 

[Harry Flashman]

Sorry that was a silly question. The answer is right there in your code.
If I want to know the number of elements in the second row....
x = UBound(Jagged(1))

Thanks once again Rick.

 

[Rick Rothstein]

How can I find out the number of elements in each row of the jagged array?

Because the arrays are zero-based, the number of elements in any give row of the jagged array is one plus the UBound of that row's array. See if this example subroutine makes this clear (the print out will be in the Immediate Window)...

Sub CreateJaggedArray()

  Dim R As Long, C As Long, Jag() As String, Jagged() As Variant
  
  [COLOR="#008000"]' Create the jagged array from the text in cell A1.[/COLOR]
  Jag = Split(Range("A1").Value, ";")
  ReDim Jagged(0 To UBound(Jag))
  For R = 0 To UBound(Jag)
    Jagged(R) = Split(Jag(R), ",")
  Next
  
  [COLOR="#008000"]' Display the element count per array.[/COLOR]
  For R = 0 To UBound(Jagged)
    Debug.Print "There are " & [COLOR="#0000FF"]UBound(Jagged(R)) + 1[/COLOR] & _
                " elements on Row " & R & " of the jagged array."
  Next
  
End Sub

 

[Rick Rothstein]

Sorry that was a silly question. The answer is right there in your code.
If I want to know the number of elements in the second row....
x = UBound(Jagged(1))

Correct... the thing to keep in mind here is that you have an array (that is what the first number in parentheses addresses) each of whose elements are an array (which is what the second number in parentheses addresses)... and, of course, you have to remember that all arrays are zero based so that you end up referencing the correct array elements (such as your use of 1 to address the second row).

 

[Harry Flashman]

Thanks once again Rick. Much appreciated.