"Syntax Error" on Redim of sub of jagged array (Excel XP) 1

[dnky]

I need some help and I can't find the answer elsewhere on here or anywhere.

I'm using jagged arrays to store information, I declared it as so:
Dim mainArray() As Variant, subArray() As Variant
...
Redim mainArray(1 To 5)

Redim subArray(1 To 5)
mainArray(1) = subArray
...

and later when I need the subArray to be bigger I can only think to use:
Redim Preserve mainArray(1)(6)

but I get a "Syntax Error" from Excel. How do I ReDim the subArray of mainArray(1) without losing any of the data?

Can anyone help?

 

[SkipVought]

Hi,

dim idx as integer

idx = 6 

Redim Preserve mainArray(idx)
Redim Preserve subArray(idx)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Why not using a Collection or a Scripting.Dictionary object ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dnky]

SkipVought, I appreciate the response but it doesn't seem to have done it.

If I:
ReDim subArray(6)

I still get:
UBound(mainArray(1)) = 5

I need:
UBound(mainArray(1)) = 6

without losing the data in mainArray(1)

PHV, I'm not aware of Collections/Scripting.Dictionary objects so I'll take a look. Thanks

 

[SkipVought]

did you

Redim Preserve mainArray(6)

???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dnky]

Yes. Let me try again because I think you're missing the point.

Dim mainArray() As Variant
Dim subArray() As Variant
ReDim mainArray(1 To 3)
ReDim subArray(1 To 5)
mainArray(1) = subArray
ReDim subArray(1 To 3)
mainArray(2) = subArray
ReDim subArray(1 To 7)
mainArray(3) = subArray

Now:
UBound(mainArray(1)) = 5
UBound(mainArray(2)) = 3
UBound(mainArray(3)) = 7

How do I "ReDim Preserve mainArray(1)" so that I get:
UBound(mainArray(1)) = 6
without losing the data in mainArray(1)?

 

[combo]

You need to assign the subarray again:

Redim Preserve subArray(1 To 10)
mainArray(1) = subArray

combo

 

[dnky]

For now I'm using a temporary array. i.e.:

ReDim tempArray(1 to 6)
For x = 1 To UBound(mainArray(1))
tempArray(x) = mainArray(1)(x)
Next x
mainArray(1) = tempArray

But it requires a lot of code so I would still like to know if there is a way of doing it with ReDim.

 

[SkipVought]

Dim mainArray() As Variant
Dim subArray() As Variant
ReDim mainArray(1 To 3)
ReDim subArray(1 To 5)
mainArray = subArray
ReDim subArray(1 To 3)
mainArray = subArray
ReDim subArray(1 To 7)
mainArray = subArray

You cannot assign a whole array to ONE ELEMENT.

Or redim each array and assign the element you wish for one to the other.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

Instead of the loop:
temparray=MainArray(1)


combo

 

[dnky]

Combo, good point. Didn't think of that.

In the end I wrote a little function:

Private Function ReDimSub(subArray As Variant, newUBound As Integer)
    ReDim Preserve subArray(LBound(subArray) To newUBound)
    ReDimSub = subArray
End Function

To be used like so:
mainArray(1) = ReDimSub(mainArray(1), 6)

Seems to be working for now.

 

[fumei]

I would definitely check into PHV's suggestion.

Gerry