Code Module And Code Names 

Code Modules

A common mistake among new VBA programmers is that they put their code in the wrong module.  When this happens, Excel can't find the code, and it can't be executed.  This page describes the different types of modules in Excel VBA, and what you should and shouldn't put in each type.  Much of this information is specific to Excel97 and 2000, and may not apply to Excel5 or Excel95. 

In Excel VBA, there are four main types of modules: 

  • Standard Code Modules, which contain custom macros and functions,
  • Workbook And Sheet Code Modules, which contain event procedures for the workbook, and worksheets and chart sheets,
  • User Forms, which contain code for the controls on a UserForm object, 
  • Class Modules, which contain Property Let, Get, and Set procedures for Objects that you create.

It matters very much where you put your code. 

NOTE: I must add, for the sake of accuracy, that the Sheet modules, the ThisWorkbook module, and the Userform modules are all really just different flavors of Class Modules.  You can create Property Get/Let/Set procedures, and methods and functions (and events) in these classes, just as you can for "standard" class modules.  Various techniques for using your forms and sheets as classes will be described in the "Advanced Form Techniques" page, coming to a server near you very soon. 

 

Standard Code Modules, also called simply Code Modules or just Modules, are where you put most of your VBA code.  Your basic macros and your custom function (User Defined Functions) should be in these modules.  For the novice programmer, all your code will be in standard modules.   In addition to your basic procedures, the code modules should contain any Declare statements to external functions (Windows APIs or other DLLs), and custom Data Structures defined with the Type statement.  

Your workbook's VBA Project can contain as many standard code modules as you want.  This makes it easy to split your procedure into different modules for organization and ease of maintenance.  For example, you could put all your database procedures in a module named DataBase, and all your mathematical procedures in another module called Math.  As long as a procedure isn't declared with the Private keyword, or the module isn't marked as private, you can call any procedure in any module from any other module without doing anything special.  

Workbook And Sheet Modules are special modules tied directly to the Workbook object and to each Sheet object.   The module for the workbook is called ThisWorkbook, and each Sheet module has the same name as the sheet that it is part of.   These modules should contain the event procedures for the object, and that's all.  If you put the event procedures in a standard code module, Excel won't find them, so they won't be executed.  And if  you put ordinary procedures in a workbook or sheet module, you won't be able to call them without fully qualifying the reference.  

User Form Modules are part of the UserForm object, and contain the event procedures for the controls on that form.  For example, the Click event for a command button on a UserForm is stored in that UserForm's code module.  Like workbook and sheet modules, you should put only event procedures for the UserForm controls in this module.  

Class Modules are used to create new objects.  Class modules aren't discussed here, except to say that a class module is used to handle Application Event Procedures.  

 

Code Names

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. 

You can change 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, and 2) need to do this.  To change the code name of a module, select the module in the Project Explorer window, and the open the Properties Windows (F4 or from the View menu), and change the Name property.  If you change 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. 

Moreover, you can change 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("Sheet2").Name= "NewCodeName"

You can change the code name of the ThisWorkbook object to "NewWBName" with 

ThisWorkbook.VBProject.VBComponents("ThisWorkbook").Name = "NewWBName"

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 casual user.   For more information about programming the VBA components, see Programming To The VBE.