VBA & Macro Problems

How to Set an Excel Worksheet CodeName During VBA Creation

WPS Editor Oct 7, 2026 869 views

Question details

The user needs to programmatically set the VBA reference name (CodeName) of a new worksheet at the time of creation, rather than renaming the object manually.

How to Set an Excel Worksheet CodeName During VBA Creation

Product: Excel
Device & OS: not provided
Scenario: Creating a new worksheet via VBA and assigning it a specific CodeName for easier, more robust referencing within the macro code.
Observed behavior: The user is forced to manually rename the worksheet object in the Visual Basic Editor because standard creation methods only set the worksheet's display tab name.

Before you start

Before running the macro, ensure that you have enabled 'Trust access to the VBA project object model' in your Trust Center settings, as this permission is strictly required to modify VBComponents.

Solution 1Recommended

Set Worksheet CodeName via VBProject.VBComponents

Use the VBProject object model to programmatically rename the worksheet's CodeName immediately after generating the new sheet.

When a worksheet is created, Excel assigns a default CodeName (such as Sheet1, Sheet2). To overwrite this default name programmatically, you must access the VBComponents collection through the workbook's VBProject.

Because this involves manipulating the macro project itself, Excel requires explicit user permission to allow macros to access the VBA project object model.

Enable Trust Center Access

In Excel, navigate to File > Options > Trust Center. Click on 'Trust Center Settings', select 'Macro Settings' from the left pane, check the box for 'Trust access to the VBA project object model', and click OK.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic Editor. Right-click on your workbook in the Project Explorer pane and select Insert > Module.

Write the Creation Code

Declare your Workbook and Worksheet variables. Create the new sheet using the standard command: 'Set sht = wrk.Sheets.Add'.

Modify the CodeName property

Add the command 'wrk.VBProject.VBComponents(sht.CodeName).Name = "YourNewCodeName"' immediately after the sheet creation. Replace 'YourNewCodeName' with your desired reference string.

Set Worksheet CodeName via VBProject.VBComponents

CodeName Naming Rules: The new CodeName must start with a letter and cannot contain any spaces, periods, or standard punctuation marks.

Automate with WPS Office

Write and Execute VBA Macros in WPS Spreadsheets

WPS Office provides robust support for VBA and macros. You can easily automate tasks, manage worksheets, and write custom scripts just like you do in Microsoft Excel, with full compatibility for existing .xlsm and .xlsb files.

1. Install WPS Office: Download and install WPS Office, ensuring that the optional VBA module is enabled during setup.
2. Open your Macro Workbook: Launch WPS Spreadsheets and open your macro-enabled workbook (.xlsm) or create a new document.
3. Access the VBA Editor: Navigate to the Tools tab on the top ribbon and click on the 'VBA Editor' icon to open the development environment.
4. Write your Script: Insert a new module and write or paste your VBProject code to manipulate worksheet CodeNames just as you would normally.

Fully compatible with Microsoft Excel VBA syntax and macro-enabled formats.Includes a familiar Visual Basic Editor for writing, editing, and debugging code.Lightweight application design ensures fast execution for complex automated workflows.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a run-time error when trying to change the CodeName?

This usually happens if 'Trust access to the VBA project object model' is not enabled in your Macro Security settings. Excel blocks programmatic access to the VBA project by default to prevent malicious scripts from modifying your code.

What is the difference between an Excel Worksheet Name and a CodeName?

The Worksheet Name (or Tab Name) is what the user sees on the sheet tab at the bottom of the window, changed via 'sht.Name'. The CodeName is the internal object name used in the VBA Editor to reference the sheet directly, which prevents your macros from breaking if a user renames the tab.

Can I change the CodeName of an Excel worksheet without writing VBA?

Yes, you can manually change a worksheet's CodeName by opening the Visual Basic Editor (ALT + F11), selecting the specific sheet in the Project Explorer, and editing the '(Name)' property in the Properties Window (press F4 if it is not visible).

Why does my CodeName change fail when I include spaces?

VBA object names, including worksheet CodeNames, must adhere to strict variable naming conventions. They cannot contain spaces, standard punctuation, or start with a number. Use camelCase or underscores (e.g., 'My_Sheet' or 'MySheet') instead.

Excel & Data Problems