Why Compile error on Myarr()?

J

JMay

Sub Foo()
Dim Myarr(2) As Variant
Myarr() = ActiveSheet.Range("A1:A3").Value
End Sub

Just trying to work through excel logic...

Tks in Advance
 
P

paul.robinson

Hi
You can do

Sub Foo()
Dim Myarr As Variant
Myarr = ActiveSheet.Range("A1:A3").Value
msgbox MyArr(1,1)
msgbox MyArr(2,1)
msgbox MyArr(3,1)
End Sub

Note that the Variant myArr has its elements accessed like Cell(i, j).
A quirk - you cannot create a "1 by 1" array for a single cell value.

You could also do

Sub Foo2()
Dim Myarr(1 to 3) As Variant
For i = 1 to 3
Myarr(i) = ActiveSheet.Range("A1:A3").Cell(i,1).Value
next i
msgbox MyArr(1)
msgbox MyArr(2)
msgbox MyArr(3)
End Sub

The first syntax is useful if you want to lift values off a range then
put them back (maybe in the same place) after processing

e.g.

Sub Foo3()
Dim Myarr As Variant
Myarr = ActiveSheet.Range("A1:A3").Value
For i = 1 to 3
MyArr(i,1) = Myarr(i,1)+3
next i
Range("B1:B3").Value = MyArr
End Sub

regards
Paul
 
D

Dave Peterson

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
 
J

Jim Cone

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.
Click to expand...
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
 
J

JMay

Thanks Dave. I thought I remembered seeing this capability sometime back (in
tIme), but must not have.. This is a complicated area to me. Thanks for
your help
 
J

JMay

Thanks Jim -- I seem to STAY confused...

Jim Cone said:
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
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
Click to expand...
 
D

Dave Peterson

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.
 
J

Jim Cone

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.
 
Top