why range is not part of worksheets properties here?

Likes Likes:  0
Results 1 to 10 of 10

Thread: why range is not part of worksheets properties here?

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Posts
    498
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default why range is not part of worksheets properties here?

     
    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

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    14,759
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: why range is not part of worksheets properties here?

    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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Mar 2016
    Posts
    498
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: why range is not part of worksheets properties here?

    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?

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    14,759
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: why range is not part of worksheets properties here?

    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.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,036
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: why range is not part of worksheets properties here?

    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).

  6. #6
    Board Regular
    Join Date
    Mar 2016
    Posts
    498
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: why range is not part of worksheets properties here?

    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
    Quote Originally Posted by StephenCrump View Post
    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).

  7. #7
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,036
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: why range is not part of worksheets properties here?

    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.

  8. #8
    Board Regular
    Join Date
    Mar 2016
    Posts
    498
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: why range is not part of worksheets properties here?

    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

  9. #9
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,036
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: why range is not part of worksheets properties here?

    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.

  10. #10
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    811
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: why range is not part of worksheets properties here?

      
    Quote Originally Posted by lezawang View Post
    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/lib...roperties.aspx
    https://msdn.microsoft.com/en-us/lib...roperties.aspx

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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com