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

3 answers

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 in sheets(n)

  • Sheets(n).range(Sheets(n).cells(y,x))

    is also valid and refers to the range in sheets(n)

  • Range(Sheets(n).cells(y,x))

    is also valid and refers to the range still in sheets(n)

    - here the parent is Range

    not activesheet

    , but object !! application

  • Range(cells(y,x))

    is valid and belongs to the range in activesheet

  • Sheets(k).range(Sheets(n).cells(y,x))

    invalid since parent Sheets(k)

    , which contains no cell insheets(n)



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.



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




All Articles