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

When you assign a range to a variant like that it always returns a *2-
dimensional* array - even if it is only 1 column:

Sub test()

Dim v, i
v = Range("A1:A10")
Debug.Print LBound(v, 1), UBound(v, 1), LBound(v, 2), UBound(v, 2)
For i = 1 To 10
Debug.Print v(i, 1)
Next i

Hth

-John Coleman

On Mar 12, 11:24 am, "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

Thank you John.

Kostis

On Mar 12, 6:47 pm, "John Coleman" wrote:
When you assign a range to a variant like that it always returns a *2-
dimensional* array - even if it is only 1 column:

Sub test()

Dim v, i
v = Range("A1:A10")
Debug.Print LBound(v, 1), UBound(v, 1), LBound(v, 2), UBound(v, 2)
For i = 1 To 10
Debug.Print v(i, 1)
Next i

Hth

-John Coleman

On Mar 12, 11:24 am, "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