In article ,
says...
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

Hi Alan,

I guess I haven't run into that problem because I never program without
Option Explicit.

The following works just fine:

Option Explicit
Sub testIt()
ReDim arr(1 To 5)
MsgBox TypeName(arr) & ", " & nbrDim(arr) _
& ", " & LBound(arr) & ", " & UBound(arr)
arr = Range("A1:A3")
MsgBox TypeName(arr) & ", " & nbrDim(arr) _
& ", " & LBound(arr) & ", " & UBound(arr)
arr = Application.WorksheetFunction.Transpose(Range("A1: A3"))
MsgBox TypeName(arr) & ", " & nbrDim(arr) _
& ", " & LBound(arr) & ", " & UBound(arr)
End Sub


nbrDim is a function that returns the number of dimensions in an array:

Function nbrDim(x) As Integer
Dim i As Integer, rslt As Integer
i = 1
On Error GoTo XIT
Do While True
rslt = LBound(x, i)
i = i + 1
Loop
XIT:
nbrDim = i - 1
End Function


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions