A common mistake among new VBA programmers is that they put code in the wrong module. When this happens, Excel cannot find the code and cannot execute it. This course describes the different types of modules in Excel VBA, and what you should and shouldn't put in each type.
Excel VBA Code Modules
In Excel VBA, there are four main types of modules:
- Workbook and Sheet 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.
- Standard Modules: which contain custom macros and functions.
- Class Modules: which contain Property Let, Get, and Set procedures for Objects that you create.
It matters very much where you put your code.
Standard Modules
Standard 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.
Modules can be exported and imported into other workbooks, and put under version control.
Workbook And Sheet Modules
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.
The sheets can be deleted. Once deleted, the code saved in Sheet Modules will also be deleted, but code stored in Standard Modules is not deleted.
User Form Modules
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
Class Modules are used to create new objects. Class modules is used to handle Application Event Procedures.
Where should I put the Excel VBA code?
Most VBA code should be placed in Standard Modules unless specified.
If you see a comment '------------------ Modules------------------ in the code header that means put the code in a Standard Module. For example:
Put the VBA code in a Sheet Module
'------------------ Sheet ------------------'------------------ Worksheet ------------------'------------------ Chart ------------------Put the VBA code in ThisWorkbook Module
'------------------ ThisWorkbook ------------------Put the VBA code in a Standard Module
'------------------ Modules------------------Put the VBA code in a Forms Module
'------------------ Forms ------------------Put the VBA code in a Class Module
'------------------ Class ------------------