Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Determining a Worksheet's Number.
Written by Allen Wyatt (last updated June 12, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
Lawrence needs a way to determine the number of a worksheet even if the worksheet has been renamed. For instance, if a worksheet is named Sheet11 it is easy enough to figure out that it is sheet 11. If he renames the sheet to January, Lawrence still needs a way to know this is sheet 11.
The solution to this problem is best done with a user-defined function (a macro). There are, in reality, two numbers that the macro could return for each worksheet. The first is the index number for the worksheet. This number represents the index of the worksheet's Worksheet object within the Worksheets collection. This value can be returned by a macro similar to the following:
Function SheetNumber1(shtname As String) Dim sht As Worksheet Application.Volatile For Each sht In ThisWorkbook.Worksheets If LCase(sht.Name) = LCase(shtname) Then SheetNumber1 = sht.Index Exit Function End If Next SheetNumber1 = -1 End Function
This function, when used in a worksheet, will return the index number of any worksheet whose name is passed to the function. If the name that is passed to the function doesn't exist in the worksheets collection, then a value of -1 is returned by the function. For instance, the following used in a cell would return the index value for the worksheet named "January" within the collection:
=SheetNumber("January")
The problem with this approach is that the order of Worksheet objects in the Worksheets collection can change over time. Thus, you can't always assume that the eleventh sheet in the collection is the sheet that was originally Sheet11.
A more consistent way of figuring out the original name for a worksheet (regardless of how it is renamed) is to use what Visual Basic refers to as the sheet's "CodeName." This is a property of the worksheet and can be determined in the following manner:
Function SheetNumber2(shtname As String) Dim sht As Worksheet Dim sTemp As String Application.Volatile For Each sht In ThisWorkbook.Worksheets If LCase(sht.Name) = LCase(shtname) Then sTemp = sht.CodeName SheetNumber2 = Val(Mid(sTemp, 6, 4)) Exit Function End If Next SheetNumber2 = -1 End Function
The CodeName property is read-only in a macro. It is assigned at the time that the worksheet is created, but it is possible for it to be manually changed within the Visual Basic Editor. The CodeName is always a string, representing the very first name that was applied to the worksheet, so it will be something like "Sheet11". Once the CodeName is set, even if the worksheet is renamed (such as to "January"), it will remain stable ("Sheet11").
In the macro example (SheetNumber2) the CodeName property is assigned to the sTemp variable. This will, most of the time, be something like "Sheet3" or "Sheet11". So, the macro then grabs the numeric value of whatever begins with the sixth character (right after "Sheet"). This is the value that is returned by the function.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11103) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Determining a Worksheet's Number.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
If your workbook contains a multitude of worksheets, the worksheet tabs at the bottom of the program window start to lose ...
Discover MoreWant the gridlines in your worksheet to be a different color? You aren't limited to stodgy black; Excel lets you make ...
Discover MoreIf you need to work on two worksheets in the same workbook at the same time, Excel makes this rather easy to do. All you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-06-14 12:32:17
J. Woolley
Re. my previous comment, the built-in SHEET function returns a sheet's Index number, not its CodeName's number. The SHEET function applies to all worksheets (visible, hidden, or very hidden) in addition to all other sheet types (chart, macro, or dialog).
You might also be interested in the freely available VBAResult function in My Excel Toolbox, which returns the result of a VBA expression.
The Index number for a worksheet or chart sheet named January is
=VBAResult("Sheets(""January"").Index")
The CodeName for a worksheet or chart sheet named January is
=VBAResult("Sheets(""January"").CodeName")
The CodeName's number for a worksheet or chart sheet named January is
=VBAResult("Val(Mid(Sheets(""January"").CodeName, 6))")
The MyToolbox.xlam add-in file includes everything in My Excel Toolbox. See https://sites.google.com/view/MyExcelToolbox/
2021-06-12 10:14:38
J. Woolley
The built-in SHEET(...) function was introduced in Excel 2013. See https://support.microsoft.com/en-us/office/sheet-function-44718b6f-8b87-47a1-a9d6-b701c06cff24
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments