Chapter 18: Class Modules

Overview

As you program in VBA and insert modules to hold your code, you will notice from the menu that you can also insert what are called class modules. These are different from ordinary modules in that they allow you to create a component object model (COM) of your own by means of creating an add-in.

Class modules cannot be run in the same way as a standard module procedure and must be referenced from your code within a module. This allows you to create your own objects and collections such as the Workbooks or the Worksheets collection. Unfortunately, you cannot create a DLL (Dynamic Link Library) file, which is what you would do if you were programming in full Visual Basic or C. However, a class module can be changed into an add-in, which is a component object, and it effectively adds multitier architecture to your applications. An add-in is a component that can be distributed and used independent of a particular spreadsheet.

Earlier in this book, you learned that Excel is a multitier application because there is a client services layer that has the Excel object model sitting beneath it, and the data services layer sits under that. The class modules allow you to place another layer between the client services layer and the Excel object model or between the client services layer and an external data source, such as Access or SQL Server.

You can turn your application into an add-in, and it can then be used as reference to your object. However, other programmers who use it won't see the underlying code and rules that you have built into it. As soon as the add-in you created is loaded, the public functions and subroutines can be accessed from other modules within a spreadsheet, although if you have password-protected it, other programmers will not see the underlying code. See Chapter 41 for more details on how to create an add-in.

As an example, the workbook is an object. When the workbook is saved, you can provide a password in your code for security; and when the workbook is reopened, your code has to give the password to succeed. There is even a property called HasPassword on the Workbook object, but the one thing it will not give you is a property containing the password itself. Of course, all the code to encrypt and decrypt passwords is hidden away in the Excel object model, but no methods or properties are provided to access the actual password. Wouldn't life be interesting if you could!

The designers of the Excel object module have written rules saying, ‚“You can save a file with a password, but you cannot view that password. ‚½ In the same way, you can design your own objects and set the rules accordingly about what can be done with those objects ‚ which properties and methods there will be and whether a collection can be changed or whether it is read-only.

In the following example, you will create an object of names taken from cells in a spreadsheet. The object collection will be called PNames , and it will be a collection of PName objects. The names could be the names of people or the names of places.