What is the difference between 'Range.Parent' and 'Range.Worksheet' in VBA?

1
votes

The .Parent and .Worksheet properties, when used with a range, seem to reference to the same worksheet object where Range is located.

For example, both of these lines return the same value:

Debug.Print Selection.Parent.Name
Debug.Print Selection.Worksheet.Name

Is there a difference between the two? Are there advantages/disadvantages to each method?

1
vbaexcelpropertiesworksheet
You would have to test all of the methods and properties native to the Worksheet object through .Parent to proof it but I would assume they are interchangeable; i.e. .Parent takes on all properties and methods of whatever it refers to. – user4039065
For a lot of operations (loops) using Selection.Parent will be slower because Excel needs to determine what collection the Selection belongs to: Charts, Names, Sheets, Styles, TableStyles, Windows, Worksheets, XmlNamespaces. With Selection.Worksheet it goes directly to the Worksheets collection – paul bica
The only difference I can think of is when you use With, IntelliSense cannot pick up any object's Parent. All kinds of objects top Parent is the Application, including it self interestingly. – PatricK

1 Answers

2
votes

Unless you can guarantee Selection is always part of a Worksheet, it's not said that Selection.Parent.Name will yield the same result as Selection.Worksheet.Name. If it's "in" other types of objects (charts or graphics, for example) the result could be quite different - you'd need to do some testing.

So, Selection.Worksheet.Name is more exact (and, as someone has pointed out in Comments, potentially faster in execution). But if you can't guarantee Selection is going to reference a Worksheet it can trigger an error or yield an unexpected result.