2

Quick Question on Jagged Arrays. I have a static container array that will not change in size:

Dim StaticArray(1 to 3, 1 to 4, 1 to 12) as variant

I am assigning array values to each index in the static array as follows:

Dim ArrayInput() as Variant
ArrayInput = Array(1,2,3,4,5)
StaticArray(1,1,1) = ArrayInput

After assigning the array of values into StaticArray, I want the flexibility to add one more value to the ArrayInput Variable.

Is there any way to redim preserve the Variant contained in StaticArray(1,1,1)? Something like:

Redim Preserve StaticArray(1 to 3, 1 to 4, 1 to 12)(1 to ubound(?)+1)

Or is the only option to modify the ArrayInput variable and re-read?

Thanks!

JSS
  • 203
  • 1
  • 2
  • 11
  • See Joshua's answer here: http://stackoverflow.com/questions/19633937/can-you-declare-jagged-arrays-in-excel-vba-directly – Tim Williams Mar 05 '15 at 00:34

1 Answers1

1

I was wondering the same thing!

As you mention, I can only achieve this by creating a temporary array, redimensioning and then reassigning to the original jagged array. Here is my code:

Dim StaticArray(1 To 3, 1 To 4, 1 To 12) As Variant
Dim ArrayInput() As Variant
Dim TempArray() As Variant

ArrayInput = Array(1, 2, 3, 4, 5)
StaticArray(1, 1, 1) = ArrayInput

'Instead of redim, store array temporarily
TempArray = StaticArray(1, 1, 1)

'Redim the temporary
ReDim Preserve TempArray(UBound(TempArray) + 1)

'Asign value
TempArray(UBound(TempArray)) = 6

'Then store again on statick array
StaticArray(1, 1, 1) = TempArray
Ernesto
  • 605
  • 1
  • 13
  • 30