redim preserve matrix doesnt work (the preserve part)

[Pascalvrolijk]

Hi dearest helping hand,

Been trying but cant find out the solution..
I have a dynamic matrix that gets an extra row within a forloop (time and time again until there are between 3,000,000 and 4,000,000 rows). I want to keep the previous lines as well but redim preserve doesnt work for reason i dont understand. Could someone please help me? The matrix is defined as Variant.

part of the code within a loop is:

k = k + 1
ReDim VBA_matrix(1 To k, 1 To 5)


in second line it gives error 9 during running, subscribt falls out of reach.

Thanks for any and all help!

Greetings,
Pascal.

 

[diddi]

you are missing the Preserve keyword

 

[Pascalvrolijk]

thx diddi, sorry i didnt write the preserve keyword because i set it back to a working version. Its the following code that doesnt work:

k = k + 1
Redim Preserve VBA_matrix((1 To k, 1 To 5)

 

[MickG]

Your array should be like this:-

Redim Preserve VBA_matrix((1 To 5, 1 To k)

You can only "Redim" the last dimension on multi dimensional arrays!!!

 

[Pascalvrolijk]

thx MickG! but then after the loop is finished how do i transpose it? Sorry for my little VBA skills..

 

[MickG]

There is a limit on the Transpose Function that may preclude you from using it with all those rows.
Normally you would build the array on its side and then transpose when placing Back to a sheet.

 

[Pascalvrolijk]

ok i was exactly afraid for that. Do you think i could use the following (thinking of the big size and that i want to use Correl function on rows and not on columns)?:
correlationmatrix(i, j) = Application.WorksheetFunction.Correl(Application.Index(VBA_matrix, 0, i), Application.Index(VBA_matrix, 0, j))

Thanks in advance, been a great help already!
Greetings,
Pascal.