Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I assign range to variant and use | Excel Discussion (Misc queries) | |||
Assigning range to variant | Excel Programming | |||
How to find the Dimensions of a Variant Range | Excel Programming | |||
How to define a Range with variant? | Excel Programming | |||
Best way to paste a variant array into a range? | Excel Programming |