In Excel 97, you cannot assign to an array.
In Excel 2000 and later array assignments are allowed,
It appears that the job to accomplish that was given to the summer intern. <g
Thanks for taking a look at it Dave.
--
Jim Cone
Portland, Oregon USA




"Dave Peterson"
wrote in message
Option Explicit
Sub TestFoo()

'I wouldn't use Arr() here. It can cause errors with some versions of excel.
'Dim Arr() As Variant
'I'd use
Dim Arr as Variant

Dim rng As Range

'this line is essentially a waste.
'VBA will use the assignment to the .value to do what it wants to use for
'the lower and upper bound when you assign the .value.
'(and arr() will be 1 based--not 0 based when it "redims" that arr variable
'ReDim Arr(0 To 2, 1 To 1)

Set rng = ActiveSheet.Range("A1:A3")

'I'd drop the ()'s from this line:
'Arr() = rng.Value
'I'd use
Arr = rng.value

MsgBox LBound(Arr, 1) & vbCr & UBound(Arr, 1)

End Sub

====
If you step through your original code (adding a watch to Arr), you'll see that
as soon as you hit:
arr()=rng.value
that the array is 1 based and the array is essentially recreated--existing
values are lost.

Option Explicit
Sub TestFoo()
Dim Arr() As Variant
Dim rng As Range
Dim i As Long
Dim j As Long
ReDim Arr(0 To 2, 1 To 1)
For i = 0 To 2
For j = 1 To 1
Arr(i, j) = i * j
Next j
Next i
Set rng = ActiveSheet.Range("A1:A3")
Arr() = rng.Value
MsgBox LBound(Arr, 1) & vbCr & UBound(Arr, 1)
End Sub

I have no idea what really happens, but I see it as
Erase Arr
redim arr(myrng.rows.count, myrng.columns.count)
and then it assigns the value.





Jim Cone wrote:
I get confused sometimes (most of the time?) ...
'--
Sub TestFoo()
Dim Arr() As Variant
Dim rng As Range
ReDim Arr(0 To 2, 1 To 1)
Set rng = ActiveSheet.Range("A1:A3")
Arr() = rng.Value
MsgBox LBound(Arr, 1) & vbCr & UBound(Arr, 1)
End Sub
'--
Returns 1 and 3 not 0 and 2
--
Jim Cone
Portland, Oregon USA





"Dave Peterson"
wrote in message
A quirk - you cannot create a "1 by 1" array for a single cell value.
You can't if you just assign the value of the cell to the variant, but you can
if you do more work.

Sub Foo()
Dim Myarr As Variant
dim myRng as range

set myrng = ActiveSheet.Range("A1:A3")
myarr = myrng.value '3 row x 1 column array

set myrng = activesheet.range("a1")
myarr = array(myrng.value) '1 element array
'or
redim myarr(1 to 1, 1 to 1)
myarr(1,1) = myrng.value '1 row x 1 column array
End Sub
ve Peterson

--

Dave Peterson