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:
- 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)
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
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 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
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.
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:
Sub Utility_GoHome() 'Macro purpose: To take the user back to home range on Control Panel worksheet Application.ScreenUpdating = True With Worksheets("Control Panel") .Activate .Range("Home").Select End With End Sub
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.
Private Sub Workbook_Open() 'Macro purpose: To send the user to the home range Call Utility_GoHome End Sub