Range object - why musn't I use a sheet sometimes

Question

In this thread:

Excel VBA find maximum value in range on specific sheet

I found a strange behaviour from the range object. I can't really put my finger on the problem, it is more like a feeling. Sometimes, you shouldn't preceed the range object with a sheet, sometimes you can. For me, it feels obvious, but I can't really explain it. Can anyone clarify it maybe?

This works:

Range(Cells(1, 1), Cells(2, 2)).Value = "X"

This feels really bad, but it works:

Sheets(2).Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(2, 2)).Value = "X"

These don't work:

Sheets(1).Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(2, 2)).Value = "X"
Sheets(1).Range("Sheet2!A1:B2").Value = "X"

I could change all the Cells(1,1) objects to Range("a1"), the results would be the same.

My best guess is that whenever the parameter passed to the Range object defines cells on a specific worksheet, you can't define a worksheet before the mentioned range. But when this is not the case, you are allowed to preceed the Range with a sheet object.

Answer 1

It seems VBA is making assumptions about unqualified objects which may not be what you assume. In general, an unqualified Range object will refer to the active sheet. But if the range object is unqualified, and the included Cells arguments are qualified, then the range object will be qualified by the Cells.Worksheet object.

Always best to fully qualify. For example:

With Sheets(2)
 .Range(.Cells(1, 1), .Cells(2, 2)).Value = "X"
End With

although the following also works:

With Sheets(2)
 Range(.Cells(1, 1), .Cells(2, 2)).Value = "X"
End With

Answer 2

The key here I think is that Range object can be child of both application and worksheet object.

As John Coleman wrote in his answer:

Microsoft documentation clearly states that "When used without an object qualifier, this property [Range] is a shortcut for ActiveSheet.Range"

It's actually not really wrong if you consider that "object qualifier" is not necessary to be parent of the Range object, but it can also be within it ( sheets(n) is an object qualifier in Range(Sheets(n).cells(y,x))), so the possible scenarios:

• Sheets(n).range(cells(y,x)) is valid and refers to a range in Sheets(n)
• Sheets(n).range(Sheets(n).cells(y,x)) is also valid and refers to a range in Sheets(n)
• Range(Sheets(n).cells(y,x)) is also valid and refers to a range still in Sheets(n) - here the parent of Range is not the activesheet, but the application object!!
• Range(cells(y,x)) is valid and refers to a range in ActiveSheet
• Sheets(k).range(Sheets(n).cells(y,x)) is not valid as parent is Sheets(k) which doesn't contain cells in Sheets(n)

Answer 3

This seems a bit backwards. You can always put an explicit Sheet before a Range but sometimes you don't need to. Range is a child of the WorkSheet object in the Excel object model. VBA has a notion of default objects which allow you to sometimes keep parent objects implicit. In the case of Range the default parent object is ActiveSheet. If you are specifying a range on the active sheet then you can just refer to the range directly, but if you are trying to refer to a range on another sheet then you need to provide an explicit sheet reference. Your two examples don't really make sense since you are trying to construct a range on one sheet by referring to ranges on another sheet. That is sort of like saying you want the part of Germany which is between New York and Boston.

On edit: vacip has discovered an apparent anomaly. Microsoft documentation clearly states that "When used without an object qualifier, this property [Range] is a shortcut for ActiveSheet.Range" Thus the in the following block of code, if run when Sheet 1 is active, both lines should be equivalent since the first is a "shortcut" for the second:

Sub test()
    Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(2, 2)).Value = 1
    ActiveSheet.Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(2, 2)).Value = 1
End Sub

But what actually happens is that the first line succeeds in altering a range on an inactive sheet but the second line fails. Odd.