Creating a Simple Object

Ok, let's test the water by creating a simple object and then using it within a standard module. By the powers vested in me, I hereby pronounce you a banker. Umm, make that a bank programmer. I know you would prefer the banker's hours, but without the well-oiled systems that you develop, those bankers wouldn't be able to get on the links so early (banker's hours are traditionally 9:00 A.M. to 3:00 P.M.— of course, these days bankers work just as hard as anyone else).

Your goal is to create a simple Loan object. So what are some ways to describe a loan? Principal amount, interest rate, term, payment, and loan number (aka account number) are a few things that come to mind. You'll make all of these items properties.

To begin, insert a new class module in the VBE. The first order of business is to name the class. I prefer to use a name that's as close as possible to how you'd normally refer to the object. For the simple Loan object, set the name to SimpleLoan (not Loan because you'll create a more complex Loan object later in the chapter) as shown in Figure 11.1. Leave the Instancing property set to 1 - Private. This property is used to allow the ability to share a class between VBA projects using project references.

One advantage of using objects is that they have two event procedures you can attach code to: an Initialize event and a Terminate event (see Figure 11.2). For your Loan object, you'll take advantage of the Initialize event to set default values for all of the Loan's properties. To add the Initialize event procedure, select the Class object from the Object drop-down list. The VBE will automatically enter the Initialize event procedure for you. If you want to add the Terminate event procedure, you need to select Terminate from the Procedure drop-down list.

Figure 11.1

The beginnings of one fine Loan object

Figure 11.1

The beginnings of one fine Loan object

Figure 11.2

Class modules allow you to attach event procedures to the Initialize and Terminate events.

Figure 11.2

Class modules allow you to attach event procedures to the Initialize and Terminate events.

Excel Vba Classes

The easiest way to add properties to a class is to just declare some public variables. You can use this method for all of your properties except the Payment property. Because a loan payment is a function of the principal amount, term, and interest rate, it won't suffice to use a public variable declaration to create this property. Instead you'll need to use another method of creating a property—you need to use a Property Get procedure. This is a special kind of procedure used to create properties in class modules. You'll take a detailed look at this method later in the chapter. Listing 11.1 presents the SimpleLoan class in its entirety.

0 0

Post a comment