ReDim with variable array

[Amy Blankenship]

According to the Help, I should be able to create an array of variable
length by declaring it like this:

MyArray() as String

However, when I try to assign anything to it:

MyArray(0) = "foo"

I get "subscript out of range".

Am I misunderstanding what the Help means by this? It seems to have gaps
just where you need specifics.

TIA;

Amy

 

[Guest]

The problem is you have to tell it how big the array is before you use it,
using the ReDim statement.

MyArray() As String

ReDim MyArray(1)
MyArray(0) = "foo"
MyArray(1) = "bar"

You can continue to ReDim it as much as you want:
ReDim MyArray(5)

The above statement will lose the first two entries though, so if you want
to preserve them, use the "preserve" keyword on the ReDim statement:
ReDim Preserve MyArray(5)

HTH

 

[John Nurick]

Hi Amy,

VBA "dynamic" arrays aren't really dynamic (as in, say, Perl). After
Dim MyArray() As String
you have to explicitly create the number of elements you need, e.g.
ReDim My Array(5)
before you can use
MyArray(0) = "foo"

If you want to add one new element without disturbing the existing ones,
you can use something like
Dim NewElements As Long

NewElements = UBound(MyArray) + 2
Redim Preserve MyArray(NewElements)

 

[Amy Blankenship]

Thanks, guys.

I pretty much found that after digging a bit more in the help. I know it's
impossible to create perfect help, but it seems too often help winds up
leading me on a wild goosechase when it implies (or even says) things that
turn out not to be true. This is a fairly mature product...it would be nice
if they could clean up the help a bit.

-Amy