Workbook and sheet modules have a property called CodeName, which is how the object is know internally to VBA. By default, the workbook code name is ThisWorkbook, and each sheet module is Sheet1, Sheet2, etc for Worksheets, or Chart1, Chart2, etc for ChartSheets. You can use these names in your VBA code as you would normal variables. For example
This is useful so that you can always refer to a worksheet, for example, even if the user renames the sheet from Excel. For example, if you have a sheet called "Sheet1", both its name and code name will be Sheet1. But if the user renames the sheet to MySheet, the code
will fail, because there is no longer a sheet named Sheet1. However, the code
will continue to work, because VBA still knows that worksheet by its code name of Sheet1.
Rename the Code Name in the Properties Window
You can rename the code name of either the ThisWorkbook or a Sheet object. If you do this once you already have code in these modules, you can run into problems, so only do this if you
- Know what you're doing
- Need to do this
To rename the code name of a module:
- Select the module in the Project Explorer window
- Open the Properties Windows (F4 or click View > Properties Window from the VBE menu bar),
- Change the (Name) property.
If you rename the code name of the ThisWorkbook object, ThisWorkbook will continue to refer to the workbook object. For example, if you change the code name of the ThisWorkbook object to MyWorkbook, both of the following lines of code will work:
However, if you change the code name for the Sheet1 object to MySheet, the following code will fail
because there is no longer a sheet object with a code name of Sheet1.
Rename the Code Name in VBA
Moreover, you can rename the code name of an object with a VBA procedure. However, this can lead to many problems, so again, don't do it unless you know what you're doing and you really need to do this. To change the code name of sheet with a code name of Sheet1 to NewCodeName, use
If you run this code and get an error message: Run-time error'1004: Programmatic access to Visual Basic Project is not trusted, to solve this problem please read Programmatic access to Visual Basic Project is not trusted.
You can change the code name of the ThisWorkbook object to "NewWorkbookName" with
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Name = "NewWorkbookName"
Just to make things more complicated, when you change the code name of the ThisWorkbook object, and you're using the VBA Extensibility library procedures, the code
will continue to work, but
will fail, because there is no object with a code name ThisWorkbook.
In general, changing code names is not for the new VBA programmer.