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")))
Steps to Add a Range into an Array in VBA First, you need to declare a dynamic array using the variant data type. Next, you need to declare one more variable to store the count of the cells from the range and use that counter for the loop as well. After that, assign the range where you have value to the array.
A variant array is set up so you can store anything in the individual elements (intersect of row and column). In the code you show they are creating a two dimensional array, but there is only a single column and the same number of rows as found in the listobject DataBodyRange.
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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With