General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Where to put your code

XL has four types of code modules. Where you put your code has a great effect on how it will behave.

Where to find modules

Open the Visual Basic Editor (VBE) by typing OPT-F11 or choosing Tools/Macro/Visual Basic Editor... On the left side, you will see the Project Browser. Depending on which view you have set (using the Toggle Folders button), you will see either a folder hierarchy or a list of modules:

Picture of Project Browser Expanded view of Project Browser

Workbook and worksheet code modules

The ThisWorkbook and worksheet code modules are a type of class module that are tied to instances of their class objects (the Workbook object and the worksheet objects). In the folder hierarchy, they are stored in the Microsoft Excel Objects folder. These modules generally should be reserved for event macros. As class modules, unqualified references (such as Range("A1")) refer to their class object, which is different than what happens in regular code modules (where an unqualified Range("A1") refers to the ActiveSheet at the time of evaluation). In addition, if you put your regular code into the ThisWorkbook or a sheet module, you'll have to fully qualify your macro call (e.g., Sheet1.MyMacro instead of just MyMacro). The ThisWorkbook module and Sheet modules are created automatically when you create a new workbook or insert a sheet.

Regular code modules

Regular, or Standard code modules are where you should put the majority of your macro code, your User Defined Functions (UDFs), and any global variables. In the folder hierarchy, they are stored in the Modules folder. These modules are accessible from everywhere in the project (unless you use Option Private), so you may put your Sub or Function in any regular code module, and you may have as many regular code modules as you wish. Unlike the workbook and worksheet code modules, unqualified references like Range("A1") will by default refer to the ActiveSheet. You can create a new regular code module in the VBE by choosing Insert/Module.

Userform code modules

Userform code modules, which are another type of class module, are tied to their Userform objects, and reside in the Forms folder within the folder hierarchy. Like worksheet code modules, these modules should be reserved for the event macros of the form (such as Initialize() and Terminate()) and its controls (such as CommandButton1_Click() or Listbox1_Change()), or code which is completely internal to the form. The Userform code module is created automatically when you create a userform in the VBE by choosing Insert/Userform.

Class modules

Class modules can be used to create new objects, with properties and methods of their own. They may inherit events from the Application object (see Chip Pearson's treatment of Application Level Events). Class modules are created in the VBE by choosing Insert/Class Module, and they reside in the Class Modules folder in the folder hierarchy.

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

Chip Pearson's page on modules