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

Does Not Work

and using Sheets in place of Worksheets also all give the same error.

I have made sure it is the same sheet as the active referenced sheet by using Range("A1:A10").Worksheet.Name Following the working code and it indeed says Sheet1 in the output.

No other workbooks are open so it can't be referencing another workbook either.

Now this last bit of code only adds to my confusion as it totally works!

Dim arr() As Variant
Dim SampleRange As Range

Set SampleRange = Worksheets("Sheet1").Range("A1:A10")
arr = SampleRange

So using the SAME RANGE defined the same way on the same sheet now works when I assign it to a Range Variable. and use that! And as expected this works with both the WorkSheets and Sheets function regardless of how I define the sheet (I can use the index or the Name of the worksheet and all work fine)

If it helps anyone, I am testing this with Excel 2007 on a Windows XP machine. I have not yet tested it on any other machines but I plan to test on 2003 and 2010 on Windows 7 and 8, just haven't had the chance yet.

UPDATE: Not 100% sure if this is the same exact issue as with the array but from a shallow view it seems to be:

 Range("B1:B3") = Range("A1:A3") 

The above code will not work, even if A1:A3 is populated, dates, numeric values, strings, formula anything, it will write blanks into B1:B3

But

Range("B1:B3").Value = Range("A1:A3").Value

And

Range("B1") = Range("A1")

does work!

Also working is:

Range("B1:B3") = Application.Transpose(Application.Transpose(Range("A1:A3")))

Answer 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

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

Answer 3

I would say an Array of Something is not the same as a Something, since this Something can be an Array of some other things. If you define something as an Array, what you assign to it has to be an Array, be it an Array of Number, Text, Range, Chart objects, etc.

When things work that we don't expect, I believe it's the built-in data type conversion that makes things easy for us most of the time. This conversion may have to be a direct object, not properties of an object.

For example, Rows and Cols are of type Long, but you can throw the Byte/Double type at it:

Cells(1,1.5) gives value of Cells(1,2)

You don't have to convert 1.5 to Long; Excel does it all in background for you.

When you define an array of something and assign things to it, Excel does type matching behind the scene and set values when possible.

Check with these in the Immediate window:

?typename(Range("A1:A10").Value) gives you Variant() <-- this is why it works on Dim arr() As Variant without any issues

?typename(Range("A1:A10")) gives you Range. But when you assign it to arr where Dim arr() As Variant, Excel converts the Range to an Array using the values of that Range.

However, Excel seems to fail converting if it doesn't have direct access to the object, unless you created memory for it. For example:

Dim arr() As Variant, oRng As Range
Set oRng = Range("A1:A10")
arr = oRng
Set oRng = Worksheets("Sheet1").Range("A1:A10")
arr = oRng

The above code is all fine, but it cannot convert and assign arr = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10") in one go, unless you throw an array to it (ThisWorkbook.Worksheets("Sheet1").Range("A1:A10").Value is of type Variant()).