This super-easy Excel tutorial helps you to learn about the sheet name code and how to find that in Excel.
Often you import raw data from external sources in Excel and perform data analytics to generate insights. To import such external data, there’s a dedicated worksheet. Suppose, it’s name is ‘ProjectX_January01.’
The name clearly suggests that the worksheet contains raw data from ‘January 01’. You or the organization have made it customary that when fetching the next day’s data, you should rename the worksheet to ‘ProjectX_January02.’ Alternatively, you’ve setup an automation for the data import and the code automatically renames the worksheet name according to the date of the data.
Now, if there’s an underlying Excel VBA script to create a pie chart that references to this worksheet using ‘ProjectX_January01,’ the next day, the script won’t work because it has been renamed to ‘ProjectX_January02’ and no one didn’t update the new worksheet name into the VBA script.
Here comes sheet name code, worksheet code name, or (Name) object in Excel. If you’d have referred to the worksheet’s (Name) value instead of the sheet name, the script would work irrespective of the renaming of the sheet names.
What Is a Sheet Name Code in Excel?
Sheet name code is the object name on the Excel VBA Editor. When you create multiple worksheets, charts, and VBA modules in the workbook, the VBA Editor allocates different (Name) codes for these objects. For worksheets, the (Name) code starts with Sheet1 and for charts that’s Chart1.
On the contrary, there’s a Name property within all workbook objects, like worksheets and charts. This is the same sheet tab name. When you rename a worksheet from the sheet tab, the Name property of the object changes.
When you access the VBA code of a worksheet, you shall see the actual object (worksheet or chart) name as a combination of both (Name) code and Name property. For example, Sheet1 (My Sheet) in the above example.
Usually, you can manually change the display names of the Excel workbook objects. Doing so won’t change the (Name) code. However, you can also change the (Name) code if you want to but it’s not accessible to you if you’re a novice to intermediate-level Excel user.
If you’re an Excel pro, you can change the sheet name code. Therefore, when writing Excel VBA scripts referencing to worksheets, being an expert Excel user you should use the (Name) code instead of the Name property. Even if someone renames the worksheets, Excel VBA scripts will still be able to reference to the old worksheet names through the (Name) code.
Excel VBA Worksheet Name Vs Codename
Find below a table to understand the difference between sheet name and sheet code:
Features | Sheet Name | Sheet Codename |
Location | It’s the 10th item on the object properties table on Excel VBA Editor. | It’s the 1st item on the object properties table on Excel VBA Editor. |
Assigned To | Assigned in the VBA editor. | Assigned in the Properties window. |
Purpose | Programmatically refer to a worksheet irrespective of its front end or user interface. It’s the unique identifier for an object VBA referencing. | This is the worksheet name that appears above the Excel status bar, the sheet tabs. It’s the display name for user interface clarity. |
Impact | If you change the (Name) code, the worksheet display name of the sheet tab won’t change. | When you change the Name property, the display name of the worksheet changes in the sheet tab. Changing the sheet name doesn’t impact a VBA script that references to sheet codename. |
Usage in VBA Scripting | If the sheet name code is Sheet1, you can directly use it in your VBA script as in this code:Sheet1.Range("A1").Value = "Hello, World!" | If the sheet name is Sheet1, here’s how you can refer to it in Excel VBA:ThisWorkbook.Sheets("Sheet1") |
Steps to Locate Sheet Name Code in Excel
Open your Excel workbook and go to the worksheet for which you want to get the sheet name code.
Right-click on the sheet name tab and select the View Code option in the context menu.
The Excel VBA Editor will open where the current worksheet will be the selected object.
Look closely at the worksheet properties table. You should see two name rows: (Name) and Name.
The first one, (Name), is the worksheet codename for Excel VBA. The second one, Name, is the worksheet name that you see on the sheet tab.
To change the sheet name code, double-click on the text, such as Sheet1, and start typing the new code on your keyboard. Hit Enter to save the sheet code name.
How to Use Sheet Name Code in Excel VBA
Suppose, you want to print Hello, World! in cell A1 in a worksheet. Here’s an Excel VBA script that does this by referencing to the sheet name in the workbook:
Sub sheetcode()
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Range("A1").Value = "Hello, World!"
End Sub
Now, if you change the worksheet name to Sheet2 from the sheet tab, you’ll get the following error:
However, if you use the following VBA script by referencing to the sheet code in the workbook, the VBA macro will work irrespective of the sheet name changes:
Sub sheetcode()
XYS.Range("A1").Value = "Hello, World!"
End Sub
Wondering how to practice this example on your own worksheet? Read the following article:
๐ Read More: How To Use The VBA Code You Find Online
Conclusions
So, now you know what a sheet name code is, how to find it in Excel, and how to utilize the worksheet codename when writing Excel VBA scripts.
Did the article help? Do you know a secret tip for (Name) codes in Excel VBA? You can comment below to share feedback and suggestions.
0 Comments