[Excel VBA] The Most Reliable Way to Reference Sheets: Using Code Names

Introduction

When manipulating specific sheets in VBA, methods like Worksheets("SheetName") or Worksheets(1) are common. However, these methods have a weakness: the macro stops with an error if the user renames the sheet or changes the sheet order.

Actually, VBA has a safe and secure way to specify sheets that is completely unaffected by user operations. This is the method of manipulating sheets using their “Code Name.”

In this article, I will explain what a Code Name is and how to use it to build robust macros.

What is a Code Name?

A Code Name is the “true name” given to each sheet used internally by VBA. It exists separately from the name (Tab Name) that the user changes on the sheet tab, and can only be checked or changed from the VBE (Visual Basic Editor).

How to Check Code Names

1. Press Alt + F11 in Excel to open the VBE.
2. The “Project Explorer” on the left side displays a list of sheets in the workbook.
3. The name displayed outside the parentheses is the “Code Name,” and the name inside the parentheses is the “Tab Name” visible to the user.

In this example, even if the Tab Name is “Monthly Data,” the Code Name for this sheet is Sheet1.

Why is Code Name the Best?

Code Names do not change even if the user freely changes the sheet name or order. Therefore, you can always reference specific sheets accurately.

Method	User Renames Sheet	User Moves Sheet	Robustness
Tab Name Worksheets("Data")	Breaks	Works	Low
Index Worksheets(1)	Works	Breaks	Low
Code Name Sheet1	Works	Works	Very High

Completed VBA Code

The following code changes the sheet name to the current month’s name and writes headers for the sheet with the Code Name shReport. Even if the user has changed the tab name beforehand, this code works correctly.

' Before running this, ensure the sheet's Code Name is set to "shReport" in the VBE
Sub ManipulateSheetByCodeName()
    
    ' --- 1. Dynamically change sheet name using Code Name ---
    ' The Code Name "shReport" is immutable even if the user changes the tab name
    shReport.Name = Format(Date, "yyyy-mm") & " Report"
    
    ' --- 2. Manipulate cells using Code Name ---
    shReport.Range("A1").Value = "Report Date:"
    shReport.Range("B1").Value = Date
    
    ' --- 3. Change tab color using Code Name ---
    shReport.Tab.Color = vbRed
    
    MsgBox "Edited the sheet specified by Code Name 'shReport'."
    
End Sub

Notice that there is no Worksheets("...") syntax. The sheet is manipulated directly using the object name shReport.

How to Change Code Names (Recommended)

Default Code Names like Sheet1 or Sheet2 are hard to understand. It is recommended that developers change them to easier-to-understand names.

1. Select the target sheet in the VBE Project Explorer.
2. Press F4 to display the “Properties Window”.
3. Change the value of the top (Name) property to a name that describes its role, such as shInput or shReport.

Just adding this step dramatically improves code readability and stability.

Summary

If you want stability in your macros, using Code Names for sheet manipulation is the best practice.

• Reference Method: Use the Code Name checked in VBE (e.g., shMaster) directly as the object name.
• Benefit: The most robust reference method, completely unaffected by sheet renaming or reordering by the user.
• Recommendation: Change the Code Name to something descriptive according to the sheet’s role during development.

From now on, before using Worksheets("SheetName"), try to think, “Can I specify this by Code Name?” This habit will make your VBA code even more professional.