why range is not part of worksheets properties here?

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top