O'Reilly logo

Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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).

Excel Visual Basic projects store code in three different folders

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required