CODEWITHSUNDEEP
excelvba
Jabberwocky
Jabberwocky

Reputation: 50775

Identifying a worksheet other than by its name

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.

Upvotes: 3

Views: 1273

Answers (2)

Ioannis
Ioannis

Reputation: 5388

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!

Upvotes: 5

overflowed
overflowed

Reputation: 1838

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

Set mysheet = ThisWorkbook.Sheets(2)

Upvotes: 1

Related Questions