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

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

Thank you Tom

Kostis

On Mar 12, 7:39 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
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