Back to index

VBA Modules

With your Excel workbook open, press ALT-F11. This will open a new window, the VBA Development window. Here is what you will see (this shows Excel 2010 but the VBA window is the same at least back to Excel 2003):


In this example the workbook is called "VBA Sample.xlsx" and can be seen at the bottom of the tree. It has one worksheet called Sheet1. There are several code modules:

Worksheet: Each worksheet has a code module. It has two names. The first name is the code name of the module. This name may be used in VBA code to identify the module in fully qualified references. This name is not visible to the user. You may change it by single-clicking on the module name, then hitting F4. This brings up the Properties window; the name is the first attribute. The second name, in parentheses, is the worksheet name, which is visible to the user. If the user changes it, the change is reflected here. You cannot change in the module Properties window, but you can change it in VBA code.

Workbook: Every workbook has a single code module called ThisWorkbook. The name of this module may be change by by single-clicking on the module name, then hitting F4, although there's not much reason to change it. This brings up the Properties window; the name is the first attribute. This module can be used to contain any code, but the best practice is to use it for code to handle workbook events, like Open and BeforeSave.

Forms: A UserForm is a form that can contain a variety of controls. The form module contains code to handle events for the form and its controls.

Modules: A Module can contain any code except event handlers, since events are specific to a workbook, worksheet, or form. A Module is the only place you can declare a Const to be Public. The fact that these are called Modules in the VBA development window is a little confusing. I refer to all of the types of code containers on this page as modules, but this type of module is called a Module.

Class Modules: A Class Module allows you to create a class. A class is a concept in object-oriented programming. A class defines a state (set of data), operations on that data (Subs), and information about that data (Functions). Yyou can declare variables that are objects belonging to the class. Each such object will have its own copy of the class data, and you can use the Subs and Functions defined for the class to manipulate the object. This is a simplified description of the concept just to get you started. Object-oriented design has become the standard design methodology for professional programmers but the concepts and best practices take a bit of study for the casual VBA programmer. These methodology is very powerful but anything that can be done with classes can be done without it.

You will notice that there is already one line of code in the window, even though this is a new workbook that was just created. What is that?