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

Tushar,

Hope it's not picky to point out your example would fail in XL97

ReDim arr(1 To 5)
arr = Range("A1:A3")
'compile error, can't assign to array (xl97)

but this is OK in xl97
Dim arr
arr = Range("A1:A3")

recently http://tinyurl.com/4ydfd

That fails for a very different reason. In VB5 one couldn't assign to
an array. In VB6, one can -- well, with some number of restrictions.
For example, the below works in VB6 (XL2000+) but not in VB5 (XL97)

Sub testArrAssignment()
Dim x(1 To 5), y(), i As Integer
For i = 1 To 5
x(i) = i
Next i
y = x
MsgBox y(5)
End Sub


Alan,

Another highly contrived scenario when it is necessary to declare before
Redim

Sub test()
Dim x, i
Dim arr() As Long 'fails if commented
x = 20
For i = 1 To x
If i = 5 Then Exit For
ReDim Preserve arr(1 To i)
arr(i) = i
Next
MsgBox UBound(arr)
End Sub


Apparently, ReDim is treated as:
if notdeclared(x) then dim x()
redim x(...)

but not redim preserve, which in a way makes sense since what are we
asking VB to preserve? I've always thought that the redim code was
implemented as:

allocate new memory
delete old memory

and I guess the VB folks decide to morph it into

allocate new memory
delete old memory if any

whereas redim preserve was implemented as

allocate new memory
copy old memory to new memory
delete old memory

which for some reason was not morphed into
allocate new memory
if old memory exists then
copy old memory to new memory
delete old memory

FWIW, the following (contrived) example works:
Sub test()
Dim x, i
'Dim arr() As Long 'fails if commented
x = 20
For i = 1 To x
If i = 5 Then Exit For
If i = 1 Then ReDim arr(1 To 1) _
Else ReDim Preserve arr(1 To i)
arr(i) = i
Next i
MsgBox UBound(arr)
End Sub


Regards,
Peter




--
Regards,

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