Redimensioning a multidimensional array 2

[colin81]

Hi All

Im relatively new to VBA and need help redimensioning a multidimensional array in vba. I receive the following error when I attempt to redimension:

"Array already dimensioned"

Can anyone please provide an example of how to redimension a multidimensional array? Or is it possible to initialise the array with a non constant value?

Many Thanks
Colin Daly

 

[GlennUK]

Well, seeing as you haven't posted your code, can't do much other than quoting the help file

If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

ReDim X(10, 10, 10)
. . .
ReDim Preserve X(10, 10, 15)

Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound; changing the lower bound causes an error.

If you make an array smaller than it was, data in the eliminated elements will be lost. If you pass an array to a procedure by reference, you can't redimension the array within the procedure.

Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.

 

[xlbo]

That's if using preserve - if you do not need to preserve values you can simply use

Dim myArray as variant

redim myArray(12,12)

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

True Geoff,

must point out, the thing that beginners usually don't get is that when doing a REDIM and using PRESERVE, it will only work when you redimension the last dimension.



Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.

 

[xlbo]

yeh - fair point Glen

I always wondered why that was though. I know it just is but do you have any idea why ??

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[colin81]

Thanks for your quick reply guys, just to clarify does this mean I declare a 1 dimensional array first then redim it as a multidimensional array ? After that I can just redim preserve as much as I like ?

 

[xlbo]

no - you declare with empty parentheses - Any dimensioning within the declaration will cause the error you have reported

You then use a 1st redim to set the initial number of dimensions and the issues with redim preserve come after that

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Hi Geoff,

as to why only the last dimension can be REDIMmed with Preserve, yes, I do know why, ( as I went and found out after a superb design I had, had to be redisgned because of this "feature" ), and here it is ... a pseudo-memory diagram of what a multi-dimensional array is stored like.

Dim Array(3,2,4) =3 * 2 * 4 memory locations	
	      Dim 1 Dim 2 Dim 3
         = 3    = 2    = 4
mem loc 1	 1	1	1
mem loc 2	 2	1	1
mem loc 3	 3	1	1
mem loc 4	 1	2	1
mem loc 5	 2	2	1
mem loc 6	 3	2	1
mem loc 7	 1	1	2
mem loc 8	 2	1    2
mem loc 9	 3	1	2
mem loc 10	1	2	2
mem loc 11	2	2	2
mem loc 12	3	2	2
mem loc 13	1	1	3
mem loc 14	2	1	3
mem loc 15	3	1	3
mem loc 16	1	2	3
mem loc 17	2	2	3
mem loc 18	3	2	3
mem loc 19	1	1	4
mem loc 20	2	1	4
mem loc 21	3	1	4
mem loc 22	1	2	4
mem loc 23	2	2	4
mem loc 24	3	2	4

As you can see, the left-most ( or first ) dimension is the one that is indexed quickest through the memory storage, so increasing the size of the last dimension will not disturb the storage of those memory locations already used.

That's what I seem to remember anyway.


Cheers, Glenn.

My grandfather was ill, and my grandmother smeared goose-grease on his back. He went downhill very quickly after that.

 

[xlbo]

ah right - so it is really a case of not being detrimental to performance

Fair 'nuff and cheers for the explanation

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[colin81]

Many Thanks for your help guys, its worked a treat. Ive given a star to both of you for your troubles!