Dynamic multidimensional arrays

[ispy99]

All,

I am trying to write some VBA code for an excel application. I need to read
in a CSV file with a set number of fields but variable number of rows. I
have been able to open the file and read the contents, however I am having a
problem loading the data into an array. I believe I am simply not
understanding how to work with multidimensional dynamic arrays. In this case
I have two fields with a variable number of rows. I know in other languages
like Java I would instantiate a Vector (for example) and then add as many
elements as I needed. VBA seems to require knowing the size of the array
when it is created with Dim. Any suggestions?

 

[ispy99]

Tom,

Thanks, that got me going. I ended up creating an array for each column,
counting how many rows were in the file and then ReDim both arrays to that
size.

BTW, why is VBA so strict about dimensioning arrays? Most other languages I
am aware of (admittingly few) aren't that strict. Seems overly restrictive.

Thanks for your help!

 

[Jim Thomlinson]

It has to do with how arrays are stored in memory. They are stored as Y
blocks of X for the array

dim ary(X, Y) as integer

There is no difficulty appending another block of of X's to the end of the
memory, but it is another thing all together to try to insert one more item
into each of the existing blocks and slide everything down. I am not sure if
that is a great expanation but...

Ultimately you can work around this in any language but is is a bunch of
work...