5

I do not understand this behaviour:

Sub tuEs()
    Dim A() As Variant
    A = Range("A1:A10") ' works

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

The first version works the 2nd version does not. Why? What is the difference?

Community
  • 1
  • 1
Christian
  • 1,308
  • 3
  • 14
  • 24
  • Is there a chance that no sheet is active? Try using `Sheets(sheet_name)` – Nomeaning25 Jan 15 '16 at 20:33
  • 5
    And they both work if you put `.Value` behind it. – Scott Craner Jan 15 '16 at 20:33
  • 1
    This is weird. This is maybe even weirder: Declare C to be a variant (with no parenthesis after the C) and replace the line `B = ActiveSheet.Range("A1:A10")` by the line `C = ActiveSheet.Range("A1:A10")` followed by the line `B=C` and it will work as intended. @ScottCraner is correct that there are work-arounds, but it still seems somewhat mystifying. – John Coleman Jan 15 '16 at 21:01
  • 2
    Given that Excel isn't open source, this might be one of those cases where the best anyone can do is make an educated guess. There seems to be a strange interaction between how variants handle dynamic typing and how VBA decides on whether or not it needs to use a default property. – John Coleman Jan 15 '16 at 21:12
  • While it is a duplicate, I am not happy with the [accepted answer](http://stackoverflow.com/a/21411922/11683). It does not explain why assigning to scalar `B` works and assigning to array `B()` does not. – GSerg Jan 15 '16 at 22:05
  • 1
    @GSerg If anything -- assigning to `B()` should work (since `B()` *can't* hold a range -- so it must be the value that you are trying to assign) but assigning to `B` should trigger the ambiguity (since it *could* hold a range). I thus agree with you that the accepted answer to the duplicate question (impressive as it is, like most answers by Siddharth Rout) doesn't completely explain what is going on. – John Coleman Jan 15 '16 at 22:26

3 Answers3

3

The way to go with this is by adding the ".value" at the end of the range. This is usually a good idea to make things very explicit (the reason you can omit this is because value is the default property for the range object)

I added all the values to watches to see what was going on and apparently there is a problem of Excel not been able to effectively ( and implicitly ) cast the object on the fly. Note in the picture how the expression that is failing "ActiveSheet.Range("A1:A10") is of type: Variant/Object/Range; the transition from Variant to object is most likely causing the issue.

enter image description here

A way to force it to cast correctly would be to split the process in two parts the first one casts to range and the second one casts to a variant array. Look at my example

Also notice that if you declare the variable as variant alone and not an array of variants (dim E and not dim E()) it will get it because the 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 makes is somewhat clear.

  • 1
    On reflection, +1. The difference between `Variant/Object/Range` and `Variant/Range` is interesting. I think that you are correct to highlight it as being central to what is happening, although in some ways it only deepens the mystery. – John Coleman Jan 15 '16 at 21:37
  • 1
    It's another example of VB sometimes being and sometimes not being able to handle a combination of late binding, default properties and arrays. A way to simulate the `ActiveSheet.Range` behaviour is to do `Dim O as Variant, A() as Variant: Set O = Range("A1:A10") : A = O`. – GSerg Jan 15 '16 at 22:09
0

This is indeed a mystery! But this works (no need to declare an array of variant objects, just a variant). As to why it doesn't work in your code as stated, I am afraid I can't answer.

Dim B As Variant ' instead of Dim B() as Variant
B = ActiveSheet.Range("A1:A10")
smackenzie
  • 2,880
  • 7
  • 46
  • 99
-1

At first I thought it to be a syntax issue - some hidden ambiguity that caused the interpreter to respond differently to the different statements. But to my dismay the following code works flawlessly:

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

As it is syntatically identical to the crashing line in the question, the only possible conclusion, AFAIK is that the implementations of Range in the Worksheet and in the Application classes return different types of objects even if they contain, in essence, the same information. Most casts will render the same results, but for some implementation quirk, only the Application version can be cast to an array of variants. This conclusion is backed by the fact that inspecting the results of expressions Range("A1:A10") and ActiveSheet.Range("A1:A10") in debug mode result in different type informations - "Object/Range" in the first case and "Variant/Object/Range" in the second.

If this is true, the exact reason for the difference (if not an accident at all) is probably known only by the coders or other folks at MS. I am curious if this is consistent thru different versions of Office..

dnep
  • 512
  • 2
  • 7
  • 1
    `Application.Range("A1:A10")` is identical to `Range("A1:A10")`. `ActiveSheet.Range("A1:A10")` calls a different property, `Worksheet.Range`. Apart from the fact that they are properties of different classes, they are also called differently - `Application.Range` uses early binding and `ActiveSheet.Range` uses late biding. – GSerg Jan 15 '16 at 21:54
  • I see that loannis comment pointed to what seems to be the correct answer. But still the downvotes seem a bit rough. Anyway I never stated that 'Application.Range' and 'ActiveSheet.Range' where semantically identical - only syntatically identical. – dnep Jan 15 '16 at 22:09