No it is not a bug.
The point is that Value is the default property of the Range Object, so why isn’t it implicitly used? Did you have a look at the question I linked? (FROM CHAT)
The experts posting previous answers have already explained very well in details. I will keep the explanation to minimal and hence let me know if you still have any questions.
Let’s understand our objects first. I created this small table which clearly shows what are we handling so that there is no confusion.
You could also add a Watch
to see the Type
for a particular object as shown in the pic below.
So when you say
arr = Range("A1:A10")
Excel knows that the default property is .Value
. However in other case, it doesn’t know because Excel is not a mind reader or let’s say intelligent enough to understand whether you want to use Worksheets("Sheet1").Range("A1:A10")
as a Range
or a Variant
Once you explicitly specify your object as a Range
then Excel knows what you want. For example this works.
Dim arr() As Variant
Dim Rng As Range
Set Rng = Worksheets("Sheet1").Range("A1:A10")
arr = Rng