This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
XL has four types of code modules. Where you put your code has a great effect on how it will behave.
Open the Visual Basic Editor (VBE) by typing OPT-F11 or choosing On the left side, you will see the Project Browser. Depending on which view you have set (using the button), you will see either a folder hierarchy or a list of 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, 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 .
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 .
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 , and they reside in the Class Modules folder in the folder hierarchy.
This page last updated
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
Chip Pearson's page on modules