Dynamic multidimensional arrays
 

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?

Dynamic multidimensional arrays
 

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!

"Tom Ogilvy" wrote:

Unfortunately, you can only expand on the last dimension (second dimension in
this case).

So you could treat your rows as columns and columns as rows.

an alternative would be to use a variant one dimensional array and read each
row into another variant one dimensional array (1 to # Columns), then assign
that to each elemement of the original variant and expand that array

Some pseudo code:

Dim v() as Variant
Dim dum(1 to 13) as Variant

redim v(1 to 1)
' code to open file
do while not eof()
' populate Dum
for i = 1 to 13
dum(i) = . . .
next
v(ubound(v)) = dum
redim preserve v(1 to ubound(v) + 1)
Loop
redim preserve v(1 to ubound(v) - 1)

for i = 1 to ubound(v)
debug.print v(i)(1), v(i)(2), v(i)(3), . . . , v(i)(13)
Next


--
Regards,
Tom Ogilvy



"ispy99" wrote:

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?

Dynamic multidimensional arrays
 

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...
--
HTH...

Jim Thomlinson


"ispy99" wrote:

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!

"Tom Ogilvy" wrote:

Unfortunately, you can only expand on the last dimension (second dimension in
this case).

So you could treat your rows as columns and columns as rows.

an alternative would be to use a variant one dimensional array and read each
row into another variant one dimensional array (1 to # Columns), then assign
that to each elemement of the original variant and expand that array

Some pseudo code:

Dim v() as Variant
Dim dum(1 to 13) as Variant

redim v(1 to 1)
' code to open file
do while not eof()
' populate Dum
for i = 1 to 13
dum(i) = . . .
next
v(ubound(v)) = dum
redim preserve v(1 to ubound(v) + 1)
Loop
redim preserve v(1 to ubound(v) - 1)

for i = 1 to ubound(v)
debug.print v(i)(1), v(i)(2), v(i)(3), . . . , v(i)(13)
Next


--
Regards,
Tom Ogilvy



"ispy99" wrote:

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?