Redim Question

[th081]

Hi

I am trying to Redim a multi dimension array.

I have declared the array at the top of the module as

Dim DataStor as variant

Then in the sub i have a loop

For i = 1 to XYZ

r = r +1

Redim Preserve DataStor(r,16)

DataStor(r,1) = Unit
DataStor(r,2) = Time

etc



Next i

it picks up the values when r = 1 but on the next loop it says subscipt out of range. I am trying to get it to hold x rows and 16 columns of data to eventually transfer into a sheet

 

[StephenCrump]

You can only Preserve data when you change the size of the last (in this case second) dimension.

But you don't need to resize on every iteration of the loop. As soon as you have quantified XYZ you can:

ReDim DataStor(1 To XYZ, 1 To 16)

 

[Kenneth Hobson]

Redim Preserve takes extra time and only works on the last dimension. Why not size or extra size (Redim) it first?
e.g. Redim DataStore(1 to XYZ, 1 to 16)

There are some tricks to Redim the rows and columns after it is filled. This shows how for rows. http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.10

 

[th081]

thanks guys,

that explains it.