Excel VBA Code Names

Code Name

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

Msgbox ThisWorkbook.Name

or

Msgbox Sheet1.Name 

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

Msgbox Worksheets("Sheet1").Name

will fail, because there is no longer a sheet named Sheet1. However, the code

Msgbox Sheet1.Name

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

1. Know what you're doing
2. Need to do this

To rename the code name of a module:

1. Select the module in the Project Explorer window
2. Open the Properties Windows (F4 or click View > Properties Window from the VBE menu bar),
3. 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:

Msgbox ThisWorkbook.Name

Msgbox MyWorkbook.Name

However, if you change the code name for the Sheet1 object to MySheet, the following code will fail

Msgbox Sheet1.Name

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

ThisWorkbook.VBProject.VBComponents("Sheet1").Name= "NewCodeName"

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

Msgbox ThisWorkbook.Name

will continue to work, but

Msgbox ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Name

will fail, because there is no object with a code name ThisWorkbook.

In general, changing code names is not for the new VBA programmer.