Alan Beban wrote:
Tushar Mehta wrote:

There's probably some performance benefit because the compiler/OS
doesn't have to worry about potentially having to resize the array.
In the old mainframe days, there was a way to convince the compiler to
stick the entire array into the stack. Helped speed up a program at
the cost of locking down that much memory.

With modern computers, it probably saves a whole microsecond or two
over the lifetime of a program. {grin}

And, it requires fewer keystrokes, something else that seems to
impress many people.

Finally, an oddity. The Dim part is not necessary. While the
documentation says it is, it is not. Until someone pointed it out,
I've been using Redim without a preceeding Dim blissfully unaware of
the requirement.

Thanks, Tushar.

I have a dim recollection (no pun intended) that I had the same view
about no Dim being required in xl5, but that something changed in xl97;
that under some circumstances a ReDim would be rejected without a Dim.
I'll try to recreate the problem, and if I do I'll report back.

Thanks again,
Alan Beban

Aha!

I have a 1993-94 Microsoft Excel Visual Basic User's Guide. It states
"When you create a dynamic array that is local to a procedure, declaring
the array with a *Dim* or *Static* statement is recommended but not
required."

In xl2000 the following works; but with the Dim statement commented out,
it throws a compile error "Invalid ReDim".

Alzheimer's hasn't caught me yet :-)

Sub foo()
Dim rng As Range
Dim arr
Set rng = Range("A1:C3")
arr = rng
ReDim arr(4, 4)
Debug.Print UBound(arr); UBound(arr, 2)
End Sub

Alan Beban