Programming Excel with VBA and .NET

Classes and Modules

Excel stores recorded code in modules and stores code associated with workbooks and worksheets in classes . Here’s why:

Modules are static

That is, they exist without having to be created in memory at runtime. That means the code in modules is always available to Excel; however, it limits what type of code they can contain. Specifically, modules can’t contain event procedures.

Classes are dynamic

They must be instantiated at runtime (that means an instance of an object must be created from the class at runtime). Classes can contain event procedures because of this relationship with an object.

Workbook, chart, and worksheet classes are automatically instantiated by Excel because those classes are associated with visible Excel objects: the current workbook and each of the sheets it contains. Because of that relationship, Visual Basic shows those classes as Microsoft Excel Objects in the Project window (Figure 2-2).

Figure 2-2. Excel Visual Basic projects store code in three different folders

Because Excel instantiates classes automatically, how you create objects from classes is mostly hidden and therefore often not completely understood. To see how creating an object from a class works, create a new workbook in Excel, start the Visual Basic Editor, and follow these steps:

1. Choose Insert → Class Module. Visual Basic creates a new class and adds it to the ...