Why am I having issues assigning a Range to an Array of Variants

Solution 1:

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

Solution 2:

Let me clarify my comment.
It can't fit to comment to i post it as answer just to at least clear my point.

Dim arr As Variant '~~> you declare arr as Variant as what Tim said

what does it mean?
It means that arr can take on any form (eg. integer, string, array, object and all the other Variable Type)

Dim arr() as Variant '~~> you declare arr() as array which may contain Varying `Data Type`

what does it mean?
It means that arr() array variable can store different Data types.
That excludes Objects or Collection of Objects.

Now, why the following works:

1. Dim arr() As Variant: arr = Range("A1:A10")
2. Dim arr() As Variant: arr = Sheet1.Range("A1:A10")
3. Dim arr() As Variant: arr = Sheets("Sheet1").Range("A1:A10").Value

This also works:

4. Dim arr() as Variant
   Dim rng as Range

   Set rng = Sheets("Sheet1").Range("A1:A10")
   arr = rng

Above works because you are not trying to assign Collections of Objects into an array.
Instead, you are assigning a specific entity or value.
Range is an object but not a Collection of Objects.
No.1 example is direct without accessing Sheets Collection Object.
Same is true with
No.2 since you work with Sheet1 which is a Sheet Object but not Collection of Sheet Objects.
No.3 is self explanatory, you assign .Value to an arr array.
No.4 works because rng is already a Range object by Set which again is not a Collection of Objects.

So this:

Dim arr() As Variant

arr = Sheets("Sheet1").Range("A1:A10")

doesn't work because Excel will read this as trying to assign Object from Sheets Collection of Objects and thus error occurs.
I hope this makes sense a bit.