Assigning range to variant
 

Hi all,

I thought (apparently wrongly), that when we assign a range to a
variant variable then this variable becomes an array. Motivation comes
from this snippet:

Dim v
v = Range("A1:A10")
Range("B1:B10") = v

This effectively copies the values of A1:A10 to B1:B10, something that
cannot be done with
Range("B1:B10") = Range("A1:A10"). Now, the following code:

Option Base 0
Sub test()
Dim v
v = Range("A1:A10")
Debug.Print LBound(v), UBound(v)
For i = 1 To 10
Debug.Print v(i)
Next i
End Sub

1. It does not complain about LBound(v) or UBound(v). Hence, in this
respect, it handles v as an array.
2. Even though Option Base is 0, LBound = 1 and Ubound = 10 (instead
of the 0 and 9 I expected). In this respect it handles it as an array
but does not honor Option Base.
3. v(1) producess the Subscript out of range error. In this respect it
refuses to see it as an array.

So what exactly happens when we assign a range array to a variant?

TIA
Kostis Vezerides

Assigning range to variant
 

RAnge("B1:B10").Value = Range("A1:A10").Value
works fine for me.

if you do

v = Range("A1:A10").Value

then you have to do

for i = 1 to ubound(v)
debug.print v(i,1)
Next

since v is v(1 to 10, 1 to 1)

--
Regards,
Tom Ogilvy


"vezerid" wrote:

Hi all,

I thought (apparently wrongly), that when we assign a range to a
variant variable then this variable becomes an array. Motivation comes
from this snippet:

Dim v
v = Range("A1:A10")
Range("B1:B10") = v

This effectively copies the values of A1:A10 to B1:B10, something that
cannot be done with
Range("B1:B10") = Range("A1:A10"). Now, the following code:

Option Base 0
Sub test()
Dim v
v = Range("A1:A10")
Debug.Print LBound(v), UBound(v)
For i = 1 To 10
Debug.Print v(i)
Next i
End Sub

1. It does not complain about LBound(v) or UBound(v). Hence, in this
respect, it handles v as an array.
2. Even though Option Base is 0, LBound = 1 and Ubound = 10 (instead
of the 0 and 9 I expected). In this respect it handles it as an array
but does not honor Option Base.
3. v(1) producess the Subscript out of range error. In this respect it
refuses to see it as an array.

So what exactly happens when we assign a range array to a variant?

TIA
Kostis Vezerides

Assigning range to variant
 

Thank you Tom

Kostis

On Mar 12, 7:39 pm, Tom Ogilvy
wrote:
RAnge("B1:B10").Value = Range("A1:A10").Value
works fine for me.

if you do

v = Range("A1:A10").Value

then you have to do

for i = 1 to ubound(v)
debug.print v(i,1)
Next

since v is v(1 to 10, 1 to 1)

--
Regards,
Tom Ogilvy

"vezerid" wrote:
Hi all,

I thought (apparently wrongly), that when we assign a range to a
variant variable then this variable becomes an array. Motivation comes
from this snippet:

Dim v
v = Range("A1:A10")
Range("B1:B10") = v

This effectively copies the values of A1:A10 to B1:B10, something that
cannot be done with
Range("B1:B10") = Range("A1:A10"). Now, the following code:

Option Base 0
Sub test()
Dim v
v = Range("A1:A10")
Debug.Print LBound(v), UBound(v)
For i = 1 To 10
Debug.Print v(i)
Next i
End Sub

1. It does not complain about LBound(v) or UBound(v). Hence, in this
respect, it handles v as an array.
2. Even though Option Base is 0, LBound = 1 and Ubound = 10 (instead
of the 0 and 9 I expected). In this respect it handles it as an array
but does not honor Option Base.
3. v(1) producess the Subscript out of range error. In this respect it
refuses to see it as an array.

So what exactly happens when we assign a range array to a variant?

TIA
Kostis Vezerides