+ Reply to Thread
Results 1 to 5 of 5

sheet referencing

  1. #1
    Registered User
    Join Date
    07-31-2007
    Posts
    35

    sheet referencing

    I am simply puzzled why this one piece of code works for me but not the other. Can anyone shed some light on this? Thanks.

    For a default workbook this works on Sheet1 which is no surprise
    Please Login or Register  to view this content.
    But when I change the name of the sheet from "Sheet1" to "Area" the following does not work.

    Please Login or Register  to view this content.
    But if I do this, it works again....

    Please Login or Register  to view this content.

    Is there something special about using the default name that allows me to make the first one work but not the second? I don't get this. This does kind of matter to me because I will have lots of sheets called Area with a number behind it, example: Area19, so I need to understand what is going on here.

    Thanks
    Last edited by VBA Noob; 10-01-2007 at 05:33 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Soce,

    Area is a VBA keyword. It is a collection object that contains cell group ranges. Placing it inside quotes as a string value is fine.

    Sincerely,
    Leith Ross

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669
    I know of the Areas collection, but no Area keyword ...

    When you reference a sheet as
    Please Login or Register  to view this content.
    , you are referencing the sheet by its codename. Codenames start out the same as sheet names, but diverge when you rename a sheet from the user interface. So after you change it,

    Please Login or Register  to view this content.
    You have to change the codename of the sheet in the VBE Properties window; then you can reference the sheet as
    Please Login or Register  to view this content.
    So the Properties window could look like any of these; the codename is shown on the left, sheet name on the right in parens:

    Sheet1(Sheet1) ' new workbook
    Sheet1(Area) ' name change via user interface
    Area(Sheet1) ' name change in VBE Properties window
    Area(Area) ' name changed both ways

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Shg,

    In computer programming, a keyword is a word or identifier that has a particular meaning to the programming language. What is defined as keyword varies with the language used. In this case, Areas refers to a Range object collection, which is actually a Function, which makes it a method of the Range Object, and therefore a reserved word.

    Sincerely,
    Leith Ross
    Last edited by Leith Ross; 10-01-2007 at 07:11 PM.

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,669
    Leith: My point was that Areas is a VBA keyword, and Area (the word used by the OP) is not.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1