Defining array size when it is dynamic

[Mark Stephens]

Hi,

At the beginning when you define an array do you have to specify a size or
can you just leave the () empty and then it will hold however many items it
holds (or can you redim it when you know). A bit rusty on arrays, help
appreciatyed, regards, Mark

 

[Dave Peterson]

You can use:

dim myArr() as long 'variant/string...

and redim it when you the dimensions.

redim myArr(1 to 5, 7 to 9, -1 to 1)
(as a weird example)

 

[XP]

Hi,

In this example, the array (saItems --- sa=string array in my shorthand) is
redimensioned dynamically and only adds data from the cells in column three
that are not blank :

Dim saItems() as String
Dim rCell as Range
Dim lX as Long
For Each rCell in ActiveSheet.UsedRange.Columns(3).Rows
If rCell.FormulaR1C1 <> "" Then
lX = lX + 1
Redim Preserve saItems(lX)
saItems(lX) = rCell.FormulaR1C1
End If
Next

"Preserve" causes the array to hold all items previously loaded into the
array; without this keyword, the array would only hold one item, i.e. the
last one loaded.

HTH

 

[Chip Pearson]

lX = lX + 1
Redim Preserve saItems(lX)
saItems(lX) = rCell.FormulaR1C1

A ReDim Preserve operation is expensive. A better way is to ReDim the array
to the largest possible size before doing anything with the array, fill the
array with the apporpriate values, and then do a single ReDim Preserve at
the end to reduce the size of the array to the actual used size.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[XP]

Thanks for the tip. I didn't know about the cost...

Relatively speaking, how costly is it?

Going further and given the example, is it less costly to dim the array to
65536 right up front, or to redim on the fly (as I did), or to count the
number of blanks first, then dim the array once (even though it would require
a little more code)?

 

[Mark Stephens]

Dear Chip, Dave and HTH,

Thank you all for your help, Iit has all come folooding back and I now feel
competent with arrays again. FYI I am going to adopt a combination of all
your suggestions and count the number of cells in the column (which is the
number of items in my single dimension array) and then do one redim of the
array, thank you all once more, kind regards, Mark