Where To Place VBA Code

Many users mistakenly place their VBA code in the wrong area, which can lead to their code not working, errors and large amounts of frustration.This page contains a listing of the different types of objects which can hold code, and what code is intended to go in each object.

Types of objects in Microsoft Excel:
As shown by the screenshot of from the Visual Basic Editor (VBE) Project Explorer, the following types of objects can exist in a Microsoft Excel file:

Object Type

• Worksheet Objects (Sheet1, Sheet2, Sheet3)
• ThisWorkbook Object (ThisWorkbook)
• UserForm Objects (UserForm1, UserForm2)
• Code Module Objects (Module1, Module2)
• Class Module Objects (Class1)
• References (Reference to CalcTools.xla)

These items can be seen in the screen shot below:With the exception of the References object, each of these objects can hold VBA code, but each has a specific purpose. The key to successful programming is making sure that your code makes it into the right place.

Worksheet Objects:
Worksheet modules are special code modules with a very specific purpose. The hold code to monitor and act upon worksheet level "events". Basically, these modules "watch" what is happening on your worksheet, and if predefined things happen, the will call a specific macro (if it exists).

Care should be taken when programming any event driven macros, as your code may trigger other events while it is running. Sometimes, if you're not careful, event code can even retrigger the same event, so careful planning is needed when using these macros.

Some of the worksheet level events that can be programmed to fire are:

• when your worksheet is activated
• when your worksheet is deactivated
• before your worksheet is saved
• when your worksheet is calculated
• other events as well

To see a complete listing, double click the 'Sheet 1' object in the Project Explorer to activate open and activate its code window. Next choose "Worksheet" from the combo box, then click on the down arrow on the next combo box. You should see an image like the following:In addition, Command Buttons, Combo Boxes and other ActiveX objects placed on your worksheet will have their events handlers in these types of modules. Once added, their name will be added to the combo box with "Worksheet", and their events will then show up in the second combo box.

The ThisWorkbook Object:
Another special code modules with a very specific purpose, the ThisWorkbook module monitors events that pertain to the entire workbook.

There are 28 events in the ThisWorkbook module (in Office 2003), but the most commonly used are the Workbook_Open procedure (sometimes used to create menus), and the Workbook_BeforeClose procedure (used to force a save, delete menus, etc...)

To see a complete listing, double click the 'Sheet 1' object in the Project Explorer to activate open and activate its code window. Next choose "Workbook" from the combo box, then click on the down arrow on the next combo box. You should see an image like the following:Userform Objects:
Userform modules actually hold two things: The actual userform design, and the VBA code to monitor the userform's events.

Unlike all the other code modules in shown in the Project Explorer, double clicking a userform will not show the VBA code, but rather shows the userform in design view. This is the area where you can add, remove or modify any of the controls on the userform.

You can access the code window for a userform in four ways:

• Right click its name in the Project Explorer, and choose 'view code'
• Right click the userform itself, and choose 'view code'
• Choose 'Code' from the View menu
• Press F7

As mentioned, the code module for the userform is where you would set up the VBA code to monitor the userform's events. To see a complete listing, open its code window, choose "Userform" from the combo box, then click on the down arrow on the next combo box.Class Modules:
Class Modules are yet another special type of code module. They hold code to program 'Application level events', such as monitoring events in all workbooks at once.
The difficulty with these modules is that there only an Initialize and Terminate events pre-programmed. You can create new events to monitor, but this takes some programming skills and knowledge.

Fortunately for most novice users, however, most of the things that they will want to do can be accomplished without accessing class modules.

Modules:
Regular code modules are where the "meat" of VBA programming should be done. These modules are specifically designed to hold volumes of code, but do NOT hold code specific to events. (Although they can hold code that is called from an event procedure.)

Good programming technique follows a very linear flow. Upon entering the procedure, you should be able to run line by line from top to bottom, possibly skipping some sections. Gone are the days of using a GoTo statement to go back to the top, or part way through the procedure.

This is where the regular code modules come in: They hold "utility" code.

Good programming technique involves breaking up code, where appropriate, into manageable chunks that can be used more than one time, or in more than one application. An example is the following code which will return the user to the range "home" on the "control panel" worksheet:

The advantage of programming this way is two-fold. At the end of every macro I run, I can put in the line "Call Utility_GoHome" to return the user to the home range. In addition, if I change the name of the sheet or the range, or want to do anything else every time, I only have to update ONE macro, not every macro I've written. (For reference, the term Utility does not have to be used, I just prefer to separate my re-usable code from the main functions that I call directly.)

A Final Word On Code Placement:
My personal preference when coding is to use as little code as possible in any of the special modules. Most of the time, I just hold all the code in my regular modules, and insert the line "Call ProcedureName" in the event handler.

Consider the following example of the Workbook_Open event.

In this case, every time the workbook is opened, the Utility_GoHome() macro, as listed above, will be called from the Workbook_Open event, placing the user where the author desires.