Range object - why don't I use sheet sometimes
In this thread:
Excel VBA find the maximum value in a range on a specific sheet
I discovered strange behavior of the range object. I cannot say that this is a problem, it is more like a feeling. Sometimes you don't have to chase a range object with a leaf, sometimes you can. It seems obvious to me, but I cannot explain it. Can someone clarify this maybe?
It works:
Range(Cells(1, 1), Cells(2, 2)).Value = "X"
This is really bad, but it works:
Sheets(2).Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(2, 2)).Value = "X"
They do not 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 Cells (1,1) objects to Range ("a1"), the results are the same.
Your best bet is to assume that whenever the parameter passed to the Range object identifies cells in a specific sheet, you cannot define the worksheet in front of the specified range. But when it doesn't, you can undergo a range using a sheet object.
source to share
The key point here is that an object Range
can be a child object application
and worksheet
.
As John Coleman wrote in his answer:
Microsoft documentation clearly states that "When used without object, this [Range] property is a shortcut to ActiveSheet.Range"
In fact, this is not entirely true if you think that the "object classifier" is not the parent of the object Range
, but it can also be inside it (it sheets(n)
is the object classifier in Range(Sheets(n).cells(y,x))
), so the possible scenarios are:
-
Sheets(n).range(cells(y,x))
is valid and belongs to the range insheets(n)
-
Sheets(n).range(Sheets(n).cells(y,x))
is also valid and refers to the range insheets(n)
-
Range(Sheets(n).cells(y,x))
is also valid and refers to the range still insheets(n)
- here the parent isRange
notactivesheet
, but object !!application
-
Range(cells(y,x))
is valid and belongs to the range inactivesheet
-
Sheets(k).range(Sheets(n).cells(y,x))
invalid since parentSheets(k)
, which contains no cell insheets(n)
source to share
It seems a bit backward. You can always specify an explicit Sheet
before Range
, but sometimes you don't need to. Range
is a child object WorkSheet
in the Excel object model. VBA has a notion of default objects which allows you to sometimes hide parent objects. In case the Range
parent is the default ActiveSheet
. If you are specifying a range on an active sheet, you can simply refer to the range directly, but if you are trying to refer to a range on another sheet, you need to provide a clear reference to the sheets. Your two examples don't really make sense as you are trying to plot a range on one sheet by referencing the ranges on another sheet. It's like you want a part of Germany between New York and Boston.
On edit: vacip encountered a clear anomaly. Microsoft documentation clearly states that "When used without an object qualifier, this property [ Range
] is a shortcut to ActiveSheet.Range" So in the next block of code, if it is run when Sheet 1 is active, both lines should be equivalent, since the first is the "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 row manages to change the range on the inactive sheet, but the second row fails. It's strange.
source to share
VBA seems to be making assumptions about unqualified objects, which may not be what you assume. In general, an unqualified Range object will reference the active sheet. But if the range object is unqualified and the arguments of the included cells are qualified, then the range object will be qualified by the Cells.Worksheet object.
It is always best to 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
source to share