Using Redim on 2 dimensional array

[RocketRod]

I am having trouble trying to use ReDim on a 2 dimensional array.
I need to increase the size of the array in a For…Next loop as data is
determined as valid - it is a staff list by name with 3 additional columns
of data for each name but the number of staff that will populate the array is
not known at the start, hence the Array needs to start as a 1x4, then change
to 2x4 etc

I have tried using both types of Dim statements as follows based on some of
the forum references
Dim StaffArray()
and
Dim StaffArray() ‘apparently this allows both dimensions of the
array to be modified in the ReDim

with each of the following types of ReDim (obviously only one at a time)

Dim staffindex as Integer
…
For…….…
ReDim Preserve StaffArray(5, 4)
ReDim Preserve StaffArray(staffindex, 4)
ReDim Preserve StaffArray(staffindex To 5, 4)
ReDim Preserve StaffArray(staffindex To 5, 4 To 4)

Next…

Can some give me the right combination of Dim and ReDim please - I keep
getting subscript out of range errors

 

[Nigel]

Using Preserve prevents you from changing any dimension other than the last.

Dim myArray(5,5)

can be resized

ReDim Preserve myArray(5,10)

but not

ReDim Preserve myArray(10,5)

HTH

 

[Jacob Skaria]

You can change the size of only the last dimension.

http://msdn.microsoft.com/en-us/library/w8k3cys2(VS.80).aspx

 

[RocketRod]

Thanks but...
According to Alan Beban in his post of 14th March 2008 as a reply under
subject "array dimensioning" he says the following which suggest that either
dimension can be changed.
Is he right?


he writes:
A bit more needs to be said.

If Arr is a true variant() type array [e.g.,
Dim Arr()
ReDim Arr(1 to 10, 1 to 2)]
then with the Preserve keyword you can change only the upper bound of
the last dimension
[e.g.,
Dim Arr()
Redim Arr(1 to 10, 1 to 2)
ReDim Preserve Arr(1 to 10, 1 to 4)]
In this case, if you try to change either bound of the 1st dimension, or
the lower bound of the 2nd dimension, you will get a Subscript out of
range error.

If, however, Arr is a an array contained within a Variant type variable
[e.g.,
Dim Arr
Redim Arr(1 to 10, 1 to 2)]
then with the Preserve keyword you can change either or both bounds of
the last dimension [e.g.,
Dim Arr
Redim Arr(1 to 10, 1 to 2)
ReDim Preserve Arr(1 to 10, 0 to 3)]

If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, then you
can use the ResizeArray function to preserve the values of the array
that is passed to it and change the lower and/or upper bounds of any or
all of the dimensions of a one-, two- three- or four-dimensional
array, or increase (up to 4) or decrease the number of the array's
dimensions (whether or not the array is contained within a Variant type
variable).

 

[RocketRod]

see my reply to Nigel - I am led to believe otherwise but it may be wrong

 

[Eric G]

I don't think you're reading that response quite right. He is saying that it
is possible to change the UPPER or LOWER bounds of the SECOND dimension in a
specific case. You still can't change the bounds of the first dimension of
the array.

As he points out, there are functions out there that will redimension
multi-dimensional arrays while preserving their contents. It's just that
those functions are not built in to Excel.

HTH,

Eric

Dim Arr()
Redim Arr(1 to 10, 1 to 2)
ReDim Preserve Arr(1 to 10, 1 to 4)] <-- Only upper bound of 2nd dim is changed!

Dim Arr
Redim Arr(1 to 10, 1 to 2)
ReDim Preserve Arr(1 to 10, 0 to 3)] <-- Upper and lower bounds are changed!

If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, then you
can use the ResizeArray function to preserve the values of the array
that is passed to it and change the lower and/or upper bounds of any or
all of the dimensions of a one-, two- three- or four-dimensional
array, or increase (up to 4) or decrease the number of the array's
dimensions (whether or not the array is contained within a Variant type
variable).

 

[Jacob Skaria]

Please read that carefully...."you can change both bounds of the last
dimension"

Again it is the last dimension/////

 

[Jim Thomlinson]

You can only change the last dimension. I has to do with the way that the
data is stored. A 2d array (or multi d for that matter) is stored on the disk
as a long string of memory. Here is an example:

A 2,4 array is actually stored as 4 blocks of 2. So when you redim the array
to 5 to then it just adds another block of 2 to the end (not strictly true as
it actually creates an an empty 2,5 array and then copies the old array over).
To redim the array to 3,4 would mean inserting an extra item into each block
of 4. That would require a whole pile of shuffling of memory to create all of
the required new memory slots. The overhead is just too great so it won't do
it.