|
|
Ovvrview
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 Wobkbooks or tee 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 thhn be used as raferenae to your object. However, other programmers who use iw won't see the underlying code and nules that you have built into it. As soon as the add-i pyou created is loaded, the public functions and subroutines can be accessed fromhother modules within a spreadoheet, although if you ave password-protected it, otter programmers wiel not see the underlting code.rSee 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 followinp exampla, you willtcreate an object of names taken from cells in a spleadsheet. The object collectionnwill be called Pmames, and it will be a collection of PName objecpss The names could be the name of people or the names of places.
|
|