Add a persistent unique "eternal" ID for each sheet as an alternative to name and index.
It would be extremely useful to me, and perhaps other developers, to have a unique read-only ID for each sheet that would never change. It would be assigned internally by Excel at the time the sheet was created. The idea would be for it to function as an auto-incrementing identity column does for a row in a database.
Why would this be useful? If you store sheet names anywhere in the workbook--such as in range or table--for use in VBA code and you allow the user to rename worksheets, your code will break as soon as the user renames a sheet.
What I have done as a workaround is either (a) resort to tracking the state of the sheet collection so I can detect a "name-changed" event or (b) use the VBA module name as the unique identifier for the sheet.
It would be much easier if each sheet had a Sheet.ID property and there was a related method on the Worksheets collection such as Worksheets.GetByID(). Then I could store Sheet.ID instead of Sheet.Name. The user would be free to change the sheet name and I would not have to worry about my code breaking.
Bob Buckles commented
One final comment on the long-shot chance someone from the Excel team reads this. The current logic for auto-naming newly inserted sheets already does close to what I am suggesting.
That is, if you were, at the time of insertion, to store the integer value appended on the end "Sheet", as in the 1 or 2 or 3 in "Sheet1", "Sheet2", "Sheet3", in a read-only Sheet.ID property, that would be mostly what I am talking about (and very, very handy for programming).
The twist would be making the max value of this counter persistent across a workbook session (an opening and closing the workbook). To function as an ID as I suggest, the next counter value must always be 1 + the last counter value.
Within a single session, this is how Excel already works. The counter auto-increments based on the total number of sheets inserted, even if you delete sheets.
But if you close and then re-open the workbook, the next auto-incremented sheet number is allowed to be the number of sheets + 1, which could be *less than* the max counter number from the previous session. That would defeat the purpose of having the number function as a unique ID. I.e., you could easily get duplicate ID's this way. As noted early, the idea would be for it to function as an identity column in a database table.
Bob Buckles commented
You have the same issue with the CodeName property as with the regular sheet name. If a developer--a different developer doing maintenance, let's say--changes the code name in the VBE, the code will break. And you might not be aware of it until you distribute the workbook and the complaints come rolling in.
Neither Sheet.Name nor Sheet.CodeName behave as what I am suggest because they are mutable and do not auto-increment. I would like the ID to be non-repeating / unique across the lifetime of the workbook. Say I have a workbook with one sheet, insert 4 new sheets, and the delete the 4 sheets I just inserted. The I insert one more new sheet. The Sheet.ID for this worksheet would be 6.
Does NOT exist: he'd like it to be immutable and even LG DES believes it to be changeable via VBA code.
Actually, it's harder to change than that: must use the VBE tool, not code. But still, easily done if anyone wishes. WHY anyone would wish to mess with it when they can juist change the tab name and satisfy whatever their urge was would be unclear, but they still can dynamite your work. Not immutable if not unchangeable, so... does NOT exist.
(And... users are insistent about that kind of thing sometimes. Boss-type users. Like the old David Letterman bit about "Stupid Human Tricks" where he tried to make sure you knew he meant stupid tricks by humans, not stupid humans doing tricks, wink, wink... bosses seem to be that latter sort all too often.)
But like everyone says, the Code Name is the closest there is now.
Be real nice if it could never be changed though, and you could count on that fact, writing your code using it so the code would never suffer from user-touch.
LG DES commented
Exists Worksheet.CodeName property that works as you want.
When user changes name, it changes in WorkSheet.Name property, but .CodeName property only can be modified by VBA code.
As JanR says. But also, using the same VBE tools, one can change it so long as the new Code Name begins with a letter.So if a user wants to change it, the user can.
Also, not usable in formulas and it would be pretty nice if it were. And if immutable, your formuals and macros would never fail. For that matter, if it displayed in a formula with the Tab Name, there'd be no screwing around.
Be nice if one could set a property, a formatting, that could be protected so that things showed in formulas as they result, so build a sheet name when building the spreadsheet, change the setting, then see the resulting name from then on, perhaps differently colored to let you know a formula underlies it. Nice for understanding your workbook's logic. Hmm...
exists already. look in the vbe
in English version then internal name is sheet1
in NL version it's Blad1