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

Question

I am having a few problems with some VERY simple lines of code. Let me detail the facts and see if anyone else can replicate this behavior. If any one can replicate I would like to get an explanation of why it is happening. So lets me start with a very simple line of code THAT WORKS: ``` Dim arr() As Variant arr = Range("A1:A10") ``` this does as expected, `arr` is assigned the Values of `A1:A10` now why won't the following line of code work? ``` Dim arr() As Variant arr = WorkSheets("Sheet1").Range("A1:A10") ``` I get a Run-Time Error '13' Type mismatch, even though the same range was successfully assigned to the array, just without the Worksheet value. But ``` Dim arr As Variant arr = Worksheets("Sheet1").Range("A1:A10") ``` And ``` Dim arr() As Variant arr = Application.Transpose(Application.Transpose(Worksheets("Sheet1").Range("A1:A10"))) ``` DOES WORK Now before you answer please let me give you some more facts. ``` Dim arr() As Variant arr = Worksheets(1).Range("A1:A10") ``` Doe…

Accepted Answer

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

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

Solution

Problem

Related problems