why range is not part of worksheets properties here?

[lezawang]

Hi
The link below shows all functions and attribution of Worksheets object. However I do not see "range" in the list? Any idea why? Thanks

http://www.excel-spreadsheet.com/vba/objectbrowser.htm

 

[JoeMo]

That link shows the Object Browser with Classes (objects) on the left and methods, properties, events and constants for each object. Range is itself an object, not a property (or method, or event or constant) of the worksheet. It is a child of the worksheet so:

msgbox Activesheet.Range("A1:Z26").Parent.Name

will tell you the name of the sheet a range is on.

 

[lezawang]

worksheets.range("a1").value=1

To me the above statement means that range is a sub object inside worksheets and value is a function/property of the range. Range is not child of worksheets. It is an object inside Worksheets (sub object), am I right?

 

[JoeMo]

Don't think so. If the Parent of a range object is the worksheet the range is on (and it is), then the range object must be the child of the worksheet. Sub object is not a term I am familiar with.

 

[StephenCrump]

lezawang, you're referring to the Worksheets object, which is a collection of Worksheet objects, to which JoeMo is referring.

Worksheets.Range("A1") is not a valid reference. You need to refer to Range("A1") on a specific Worksheet.
(Or if you don't specify the Worksheet, it will default to ActiveSheet).

 

[lezawang]

Yes agree. It should be like this
Worksheets("Sheet1").Range("A1")

But let say Worksheets object has the following memebers

Worksheets {
Member1
Member2
Member3
}

The to access any member to change change the value, then we need to write it this way

Worksheets.member1 = 1

Having said that, if someone told me the following syntax:

Worksheets("Sheet1").Range("a1")

To me that means that "Range()" is a member of that Object. What is how C++ is as far as I remember. Thank you

lezawang, you're referring to the Worksheets object, which is a collection of Worksheet objects, to which JoeMo is referring.

Worksheets.Range("A1") is not a valid reference. You need to refer to Range("A1") on a specific Worksheet.
(Or if you don't specify the Worksheet, it will default to ActiveSheet).

 

[StephenCrump]

In your terminology:

Worksheets("Sheet1").Range("a1") = 1

is equivalent to:

Worksheet.member1 = 1 (not Worksheets.member1 = 1)

That's because Worksheets("Sheet1") is a Worksheet, not Worksheets, object.

So coming back to your original question, that's why the Object Browser shows Range as a property of the Worksheet Object. It is not a property of the Worksheets object, i.e. you can't refer to Worksheets.Range without specifying which Worksheet in Worksheets you want to use.

 

[lezawang]

Sorry the Sheets/Sheet was typo mistakes. I meant Worksheets. Anyway, let me explain it differently.


Workbook is a class and it has Worksheets as a memeber.

Workbook{
Worksheets
}

Therefore, I can type the following

Workbook("hello").Worksheets("Sheet1")

At same token because Range is a memeber of Worksheets project then I can write

Worksheets("Sheet1").Range("a1")

But Range is not a member of worksheet class!! then how can the above line is correct?? Thank you

 

[StephenCrump]

I am not sure how to make it any clearer:

- Range is a property of the Worksheet Object.

- Range is not a property of the Worksheets Object.

- Worksheets("Sheet1").Range("A1") is a valid reference because Worksheets("Sheet1") is a Worksheet.

- The fact that Worksheets("Sheet1").Range("A1") is a valid reference does not mean that Range is a property of the Worksheets object.

 

[thisoldman]

Sorry the Sheets/Sheet was typo mistakes. I meant Worksheets. Anyway, let me explain it differently.


Workbook is a class and it has Worksheets as a memeber.

Workbook{
Worksheets
}

Therefore, I can type the following

Workbook("hello").Worksheets("Sheet1")

At same token because Range is a memeber of Worksheets project then I can write

Worksheets("Sheet1").Range("a1")

But Range is not a member of worksheet class!! then how can the above line is correct?? Thank you

Worksheets is a collection of items. The items are individual worksheet objects. When you write Worksheets("Sheet1") you are referring to an individual worksheet. Range is a property of the Worksheet object. Range is not a property of the Worksheets collection.

Worksheets and its alias Sheets have the properties listed here (Range is not listed):
https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.worksheets_properties.aspx
https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.sheets_properties.aspx

The Worksheet object properties are listed here (Range is a listed property):
https://msdn.microsoft.com/en-us/library/office/dn301176.aspx