The second, third, and fourth MsgBoxstatements show the new properties needed to reference the col-
lection and its members. You need to use the Itemproperty to reference a member and the Itemsprop-
erty to reference the whole collection.
Class modules allow you to encapsulate code and data in such a way that it becomes very easy to use,
very easy to share, and much easier to maintain.
You hide the code that does the work from the user, who only needs to know what sort of object the class
module represents, and what properties and methods are associated with the object. This is particularly
useful when it is necessary to make calls to the Windows API (application programming interface) to
perform tasks that are not possible in normal VBA. This topic is presented in Chapter 27, where you can
see examples that encapsulate very complex code and create very usable objects.
Class modules provide a mechanism for encapsulating code that you can use in other workbooks or share
with other programmers to reduce development time. You can easily copy a class module to another work-
book. In the Project Explorer window, it is as straightforward as dragging the class module between the
You can also export the code in the class module to a file by right-clicking the module in the Project
Explorer and choosing Export File to create a text file that can be copied to another PC. The file can then
be imported into another workbook by right-clicking its project in the Project Explorer and choosing
So far, this chapter has examined class modules from a general programming perspective. You will now
see how to use class modules to gain more control over Excel.
Trapping Application Events
You can use a class module to trap application events. Most of these events are the same as the work-
book events, but they apply to all open workbooks, not just the particular workbook that contains the
event procedures. For example, in a workbook there is a BeforePrintevent that is triggered when you
start to print anything in that workbook. At the application level, there is a WorkbookBeforePrint
event that is triggered when any open workbook starts to print.
To see what application events are available, you first insert a class module into your project. The class
module can have any valid module name. The one shown in Figure 16-4 has been named CAppEvents.
You then type in the following variable declaration at the top of the module:
Public WithEvents xlApp As Application
The object variable name, xlApp, can be any valid variable name, as long as you use it consistently in
code that refers to the class module, as a property of the class. The WithEventskeyword causes the
events associated with the application object to be exposed. You can now choose xlAppfrom the drop-
down at the top left of the module and then use the drop-down at the top right to see the event list, as
shown in Figure 16-4.
Chapter 16: Class Modules