VBA & Macro Problems

How to Reference an Excel Worksheet by CodeName in VBA

Muhammad Talha Oct 12, 2026 871 views

Question details

The user needs to know how to reference a worksheet using its VBA CodeName so the macro continues to function even if the visible tab name is changed.

How to Reference an Excel Worksheet by CodeName in VBA

Product: Excel
Device & OS: not provided
Scenario: Writing robust VBA macros where end-users might rename worksheet tabs, potentially breaking hardcoded tab name references.
Observed behavior: Using the old tab name in the Worksheets collection causes a subscript out-of-range error after the tab is renamed, whereas the CodeName remains unchanged.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet application and press ALT + F11 to open the VBA Editor so you can view the exact CodeNames of your worksheets.

Solution 1Recommended

Use the CodeName Directly Without Quotation Marks

Reference the worksheet's CodeName directly in your VBA script to ensure your code won't break if a user changes the visible tab name.

Every worksheet has two names: the Tab Name (visible to users) and the CodeName (used by VBA). When a user renames a tab, the CodeName remains unchanged. By using the CodeName, you create more reliable and error-proof macros.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Locate the CodeName

Look at the Project Explorer pane on the left. You will see worksheets listed in the format 'CodeName (TabName)', for example, 'Sheet32 (SalesData)'. The text outside the parentheses is the CodeName.

Write the Reference

In your code module, type the CodeName exactly as it appears, completely without quotation marks. For example, write 'Sheet32'.

Add Methods or Properties

Append the desired action to the CodeName. For instance, to make the sheet visible, write: 'Sheet32.Visible = xlSheetVisible'.

Use the CodeName Directly Without Quotation Marks

Code Reliability: Your macro will now execute flawlessly regardless of whether the user renames the worksheet tab in the main application window.

Solution 2

Replace Fragile Tab Name References

Identify and replace code that relies on the Worksheets collection with string names, which are prone to subscript out-of-range errors.

Advanced Macro Support in WPS Office

Write and Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides robust, built-in support for VBA macros. You can utilize CodeNames, run automated workflows, and edit code exactly as you would in Microsoft Excel, but within a lighter and faster application.

1. Open Your Macro Workbook: Launch WPS Spreadsheet and open your existing .xlsm or .xlsb workbook.
2. Access the Developer Tab: Click on the 'Developer' tab located on the top ribbon menu.
3. Open the VBA Editor: Click the 'Visual Basic' button or press ALT + F11 to launch the editor.
4. Use CodeNames Securely: View your worksheet CodeNames in the Project Explorer and write reliable macros that won't break upon tab renaming.

Fully compatible with Microsoft Excel VBA macros and .xlsm filesBuilt-in Visual Basic Editor for seamless code writing and debuggingLightweight, fast, and free to download for everyday office tasksFamiliar ribbon interface ensures zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

How do I find the CodeName of an Excel worksheet?

Open the VBA Editor by pressing ALT + F11. Look at the Project Explorer pane on the left side. Your worksheets will be listed as 'CodeName (TabName)', such as 'Sheet1 (Data)'. The first word before the parentheses is the CodeName.

Can I change the CodeName of a worksheet?

Yes, you can modify the CodeName inside the VBA Editor. Select the worksheet in the Project Explorer, press F4 to open the Properties window, and change the '(Name)' property at the very top of the list. You cannot change it from the standard Excel interface.

Why am I getting a 'Subscript out of range' error in VBA?

This error frequently happens when you reference a worksheet by its tab name (e.g., Worksheets("Sales")) but the tab has been renamed, deleted, or spelled incorrectly. Switching to CodeName references prevents this issue.

Can I loop through worksheets using CodeNames?

You cannot use CodeNames directly in an index loop like Worksheets(i). However, you can loop through the Worksheets collection and check the CodeName property of each sheet, for example: 'If ws.CodeName = "Sheet1" Then'.

Excel & Data Problems