Redim Array of Arrays (Jagged Array)

[AMAS]

Hi,

First attempt to creating an array of arrays and could you use guidance. I have declared the array:

Dim arrTemp     As Variant

Then I tried to redimension it (failed)

ReDim Preserve arrTemp (1)(1 to 500)
ReDim Preserve arrTemp (2)(1 to 500)
ReDim Preserve arrTemp (3)(1 to 500)

Finally will be filling the arrays:

arrTemp(1)(1) = "First array, first element"
arrTemp(2)(2) = "Second array, second element"
arrTemp(3)(3) = "Third array, third element"

How do I get this working?

AMAS

 

[Rick Rothstein]

See if this helps...

Dim X As Long, Y As Long, arrTemp As Variant
[COLOR=seagreen]'[/COLOR]
[COLOR=seagreen]' You can dim and assign everything all at once[/COLOR]
[COLOR=seagreen]'[/COLOR]
arrTemp = Array(Array("First array", "first element"), _
                Array("Second array", "second element"), _
                Array("Third array", "third element"))
[COLOR=seagreen]'[/COLOR]
[COLOR=seagreen]' Watch it work[/COLOR]
[COLOR=seagreen]'[/COLOR]
For X = 0 To 2
  For Y = 0 To 1
    Debug.Print arrTemp(X)(Y)
  Next
Next

 

[AMAS]

Hi Rick,

Thanks for looking at this for me. I need to redim the arrays during the progress of the running of the macro as I don't know the upper limit before the run. Is Redim possible with a jagged array or is there another way?

Thanks.

AMAS

 

[Rick Rothstein]

'
' Watch it work
'
For X = 0 To 2
For Y = 0 To 1
[/CODE]

Just to follow up, the upper and lower bounds are determined by the Option Base setting. Also, since each array element does not necessarily have to have the same number of elements, hard-coding it the way I show will not always work. Here is the generalize set of For statements to handle whatever the Option Base is and for no matter how many elements are in each separate array...

For X = LBound(arrTemp) To UBound(arrTemp)
  For Y = LBound(arrTemp(X)) To UBound(arrTemp(X))

 

[pgc01]

Hi

This is an example of dynamic dimensioning of the jagged array:

Sub Test()
Dim arrTemp As Variant
Dim vTemp As Variant
 
ReDim arrTemp(1 To 3)
 
ReDim vTemp(1 To 100)
arrTemp(1) = vTemp
 
ReDim vTemp(1 To 200)
arrTemp(2) = vTemp
 
ReDim vTemp(1 To 500)
arrTemp(3) = vTemp
 
arrTemp(1)(1) = "First array, first element"
arrTemp(2)(20) = "Second array, second element"
arrTemp(3)(300) = "Third array, third element"

MsgBox _
    "arrTemp(1)(1) = " & arrTemp(1)(1) & vbNewLine & _
    "arrTemp(2)(20) = " & arrTemp(2)(20) & vbNewLine & _
    "arrTemp(3)(300) = " & arrTemp(3)(300)
End Sub

 

[AMAS]

Thanks pgc. From this I am starting to see that I have to redim each element seperately. That's fine with me. Just for confirmation, I can't redim all the arrays in one line of code. Correct?

Also is it possible to use Redim Preserve the same way as we normally would with an array?

Thanks again.

AMAS

 

[pgc01]

Yes, In a jagged array each array is independent an so I believe you have to dimension each of the arrays.

If you want to redim preserve an array you can use an auxilliary array.

Check this example. Notice that now each array of the second level has not only a different type but also a different size.

Sub Test()
Dim arrTemp As Variant
Dim vTemp As Variant
 
ReDim arrTemp(1 To 3)
 
ReDim vTemp(1 To 100) As String
arrTemp(1) = vTemp
 
ReDim vTemp(1 To 200) As Double
arrTemp(2) = vTemp
 
ReDim vTemp(1 To 500) As Boolean
arrTemp(3) = vTemp
 
arrTemp(1)(1) = "First array, first element"
arrTemp(2)(1) = 1
arrTemp(3)(300) = True
 
' redim Preserve the string array to (1 to 1000)
vTemp = arrTemp(1)
ReDim Preserve vTemp(1 To 1000)
arrTemp(1) = vTemp
 
MsgBox _
    "arrTemp(1)(1) = " & arrTemp(1)(1) & vbNewLine & _
    "arrTemp(2)(1) = " & arrTemp(2)(1) & vbNewLine & _
    "arrTemp(3)(300) = " & arrTemp(3)(300)
 
End Sub

 

[AMAS]

Excellent. This is good stuff. I can see some real potential for using Jagged arrays.

Thanks again.

AMAS

 

[pgc01]

You're welcome. Thanks for the feedback.

Also is it possible to use Redim Preserve the same way as we normally would with an array?

Remark: Redim Preserve is a heavy operation, use it as little as possible.

 

[Rick Rothstein]

See if this helps...

Dim X As Long, Y As Long, arrTemp As Variant
[COLOR=seagreen]'[/COLOR]
[COLOR=seagreen]' You can dim and assign everything all at once[/COLOR]
[COLOR=seagreen]'[/COLOR]
[COLOR=red][B]arrTemp = Array(Array("First array", "first element"), _[/B][/COLOR]
[COLOR=red][B]               Array("Second array", "second element"), _[/B][/COLOR]
[COLOR=red][B]               Array("Third array", "third element"))[/B][/COLOR]
[COLOR=seagreen]'[/COLOR]
[COLOR=seagreen]' Watch it work[/COLOR]
[COLOR=seagreen]'[/COLOR]
For X = 0 To 2
  For Y = 0 To 1
    Debug.Print arrTemp(X)(Y)
  Next
Next

I know you are looking for something different than what I have presented, but I just wanted to remark that you can make the initial assignment of array elements more dynamic (you could probably use this technique with any new array elements you add to the main array as well)... here I do it with string arguments for each array element rather than repeated Array function calls (so the string can be composed dynamically before being assigned to the main array element)...

Dim X As Long, Y As Long, arrTemp As Variant
[COLOR=seagreen]'[/COLOR]
[COLOR=seagreen]' You can dim and assign everything all at once[/COLOR]
[COLOR=seagreen]'[/COLOR]
arrTemp = Array(Split("First array,first element", ","), _
                Split("Second array,second element", ","), _
                Split("Third array,third element", ","))
[COLOR=seagreen]'[/COLOR]
[COLOR=seagreen]' Watch it work[/COLOR]
[COLOR=seagreen]'[/COLOR]
For X = LBound(arrTemp) To UBound(arrTemp)
  For Y = 0 To UBound(arrTemp(X))
    Debug.Print arrTemp(X)(Y)
  Next
Next

Note that since Split always produces a zero-based array no matter what the Option Base setting is, I started the inner For..Next loop with 0 rather than an LBound call, but remember the main array's lower and upper bound will still be dependent on the Option Base setting.