VBA, Excel, use Range to populate a 2D array

Question

I do not understand this behavior:

Sub tuEs() Dim A() As Variant A = Range("A1:A10") ' works Dim B() As Variant B = ActiveSheet.Range("A1:A10") ' Type mismatch End Sub 

In the first version, the second version works. What for? What is the difference?

Answer 1

The way to do this is to add a ".value" at the end of the range. It’s usually a good idea to make things very explicit (the reason you can omit this is because the value is the default property for the range object)

I added all the values ​​in the clock to see what was happening, and apparently the Excel problem was not able to efficiently (and implicitly) drop the object on the fly. Notice how the expression that fails "ActiveSheet.Range (" A1: A10 ") is of type: Variant / Object / Range; switching from a variant to an object is most likely causing a problem.

The way to force the correct display will be to divide the process into two parts, which the first selects for the range, and the second into the variant array. Look at my example

Also note that if you declare a variable only as an option, and not an array of options (dim E, not dim E ()), it will receive it because it will adapt to what is needed.

 Sub tuEs() 'Works Dim A() As Variant A = Range("A1:A10") ' Type missmatch Dim B() As Variant B = ActiveSheet.Range("A1:A10") ' Fix to make it cast properly Dim C() As Variant Dim r As Range Set r = ActiveSheet.Range("A1:A10") C = r ' Best of all options Dim d As Variant d = ActiveSheet.Range("A1:A10").Value End Sub 

Hope this is somewhat clear.

Answer 2

This is really a mystery! But it works (there is no need to declare an array of object variants, only an option). As for why this doesn't work in your code, as indicated, I'm afraid I can't answer.

 Dim B As Variant ' instead of Dim B() as Variant B = ActiveSheet.Range("A1:A10") 

Answer 3

At first, I thought it was a syntax problem - some hidden ambiguity that made the interpreter react differently to different statements. But, unfortunately, the following code works flawlessly:

 Dim B() as Variant B = Application.Range("A1:A10") 

Since it is syntactically identical to the failure line in the question, the only possible conclusion AFAIK is that Range implementations in Worksheet and Application classes return different types of objects, even if they contain essentially the same information. Most prizes will give the same results, but for some quirk implementations, only the application version can be ported to an array of options. This conclusion is confirmed by the fact that checking the results of the expressions Range("A1:A10") and ActiveSheet.Range("A1:A10") in debug mode leads to various types of information - "Object / range" in the first case and "Variant / Object / Range "in the second.

If this is true, the exact cause of the difference (if not an accident at all) is known only to programmers or other people in MS. I am curious if this is agreed across different versions of Office ..