Excel Module (Code) Names

[NeoPa]

Introduction :

Excel worksheets all have names, but although the default name for a sheet will always match the module name for that sheet, it is easy to change the name of the sheet, yet not so easy to access and change the module name. If this is a problem you've struggled to deal with, then read on.


Worksheet Name :

This is typically shown in a tab at the bottom of a workbook window in Excel. Double-clicking in that tab allows the operator to overtype this with a new name. This is also visible and changeable by a developer if they look at the Properties Pane (See Debugging in VBA.) in the VBA IDE window. You'll notice there are two properties named 'Name', although one is in parentheses '(Name)'. We'll come to that later. Only the one without parentheses refers to the worksheet name itself. The Worksheet object (when used within VBA) also has a .Name property which reflects this name. This property is updatable in code.


Worksheet Module Name :

The module name of the worksheet is not visible anywhere in the main Excel window, but can be seen (and modified) in the VBA IDE via the properties. The '(Name)' property is actually the name of the worksheet's module and is R/W. It can also be seen via VBA as the .CodeName property of a worksheet object. This is only R/O however.


How Do I Update the Worksheet Module Name in VBA then :

Another way to access this code module is via the VBComponents collection of a VBProject, which can be found as a property of a Workbook. This is easiest if done with the VBA Extensibility library referenced. The version I have for 2003 is named "Microsoft Visual Basic for Applications Extensibility 5.3" (To help readers find their own version). A VBComponent object has a .Name property which is R/W.

In short then, the basic code to update the module name for a worksheet (The scenario is that we've already named "Sheet1" as "Bob" and we would like the module name to match it.) is as follows :

Call RenameModule("Bob")
 
Private Sub RenameModule(strNewName As String)
    Dim strSheet As String
 
    With ThisWorkbook
        strSheet = .Worksheets(strNewName).CodeName
        .VBProject.VBComponents(strSheet).Name = strNewName
    End With
End Sub

Conclusion :

Although Excel seems to restrict the renaming of its Module objects via VBA code, it will allow it for those who are prepared to go beyond the very basics. Maybe the designers felt that developers who merely have that very basic understanding of the object structure are better steered clear of such changes anyway. Hopefully though, if you've found this, and have a real need to do so, then you now have the understanding to handle it.