Identification of a sheet other than its name
A sheet can be identified by its name, for example, in this example:
Dim mysheet As Worksheet
Set mysheet = ThisWorkbook.Sheets("My Sheetname")
Now I'm wondering if it is possible to define a leaf other than its name, for example, with some unique identifier or some property or something else.
My problem is as follows:
Referring to the code snippet above: If the user changes the name of the worksheet (for example, from "My Sheetname" to "Your Sheetname"), the snippet will obviously no longer work.
source to share
This is a very good article explaining that it is better to use SheetID
(also called codename) instead of Sheet
Name.
Citation:
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 the sheet name or sheet order.
You can also change the name of the code to make it more descriptive:
ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "Revenue_Actuals"
and then
Revenue_Actuals.Range("C2").value = 10
works great.
Using codenames (e.g. Sheet1.Range("C1").value
) is a good idea, however some runtime codenames as stated above are not considered good practice for some developers (see, for example, the comments on the article links above).
Using the leaf index is another way to go, but I personally prefer the codename.
Finally, this article lists many ways to reference a sheet or workbook.
Hope this helps!
source to share