3

A worksheet can be identified by its name such as in this example:

Dim mysheet As Worksheet
Set mysheet = ThisWorkbook.Sheets("My Sheetname")

Now I wonder if a worksheet can be identified other than by its name, for example by some unique id or some property or whatever.

My problem is following:

Referring to the code snippet above: if a user changes the name of the worksheet (e.g. from "My Sheetname" to "Your Sheetname"), the snippet obviously won't work anymore.

Community
  • 1
  • 1
Jabberwocky
  • 48,281
  • 17
  • 65
  • 115

2 Answers2

5

This is a very good article that explains that it is better to use the SheetID (also called codename) instead of the Sheet Name.

Quoting:

The Codename property is the internal name that Excel uses to identify each sheet. Unlike the Worksheet.Name property, the Codename remains the same regardless of sheet name, or sheet order.

The code name can also be changed so that it is more descriptive:

ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "Revenue_Actuals"

and then

Revenue_Actuals.Range("C2").value = 10

works fine.

Using codenames (such as Sheet1.Range("C1").value) is a good idea, however changing code names at runtime as above is not considered good practice by some developers (see for example, comments on the article of the link above).

Using the sheet index is another way to go, but I personally prefer the code name.

Finally, this article lists many ways that a sheet or workbook can be referenced.

I hope this helps!

Ioannis
  • 5,238
  • 2
  • 19
  • 31
  • 2
    I don't think the comments in the linked article imply that changing code names is not considered good practice. They are only warning against changing code names via code. It is perfectly fine (and very useful) to change code names via the properties window in the VBE. – Excel Developers Nov 27 '14 at 14:08
  • 1
    Indeed, the comments imply that bad practice is to change the names *at runtime*. The "as above" part of the response implies this, but I edited it to make this point clearer. Another thing is that changing names at runtime implies trusted access to the VBA object model. Thanks for the comment! – Ioannis Nov 27 '14 at 14:12
1

You can just access them by index like e.g. for the second sheet

Set mysheet = ThisWorkbook.Sheets(2)
overflowed
  • 1,773
  • 10
  • 13