Class Modules

If you've done any amount ofVBA or VB coding, you have more than likely used objects in your code. Any time you've gone out to a database and retrieved records using ADO, you may have declared and instantiated a variable like this:

Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset

Some of the examples you've seen thus far have also used some of Excel's built-in objects, like the Selection object, which has a Font property, or the Range object, which has many properties and methods you can use in your code.

Using Excel's VBE, you can create your own objects that contain custom properties and methods that you define. You do this by creating classes in class modules. Here's the definition of an object (from Microsoft's ASRNET forums at http://forums.asp.net/p/1117506/ 1933142.aspx):

Class: The formal definition of an object. The class acts as the template from which an instance of an object is created at run time. The class defines the properties of the object and the methods used to control the object's behaviour.

In a standard code module, public functions and subroutines you create can be called from anywhere in your code simply by referencing the procedure. Code in a class module must be explicitly instantiated, as in the preceding ADO Recordset example. Until an object is instantiated in this manner, its methods and properties are not available to your code.

Another difference is that standard code modules can contain any number of related or unrelated procedures (although best practices dictate that code in a given module should be related to specific functionality, reality tells us that this is not always the case, and there is no enforcement of this practice within a standard code module). Code in a class module by definition defines the methods, properties, and events for objects that you create from a class. These methods, properties, and events are all directly related to the object, and their inner workings do not need to be known to implement or use the object. The term used to define this relationship to the object is encapsulation.

Encapsulation can be defined as the capability of an object to conceal its inner workings from client code or other objects. It is one of the fundamental principles of object-oriented programming (OOP). If an object has clearly defined properties and methods, it will be easily reusable and will require limited (if any) documentation. When we look at the ADO recordset object, we can easily understand what its Open or AddNew methods do for us with no concern for how they provide their services. Your objects will be as well defined as any of the Visual Basic objects, and therefore easy for you or anyone else to implement in their applications.

Class modules contain only code—there is no visual interface. Classes you create in Excel VBA are easily portable to other VBA applications, and can be placed into Visual Basic 5 or 6 code with no (or minimal) modifications and compiled into ActiveX DLLs or EXEs. This allows your objects to be used in applications outside of Excel.

Use of classes allows for the design of robust, reusable objects. It requires more forethought and planning, but you receive the benefits of code that is usually more reliable and easier to maintain.

Class modules are inserted into your project by choosing Insert > Class Module or by right-clicking an object in the Project Explorer and choosing Insert > Class Module from the pop-up menu.

Sample Class and Usage

Let's re-create the Employee user-defined data type that we looked at in a previous example as an object. Custom data types are a great way to store more than one related value for an item, but they have a few shortcomings. They don't do any validation, they cannot perform actions (methods or functions), and they cannot by themselves trigger events. Classes allow you to do all of these.

The cEmployee Class

Let's take a quick look at the Employee data type from our previous example:

Type Employee ID As Long Name As String Title As String Phone As String End Type

The first thing we will do is create properties for each value type. In Visual Basic 5/6.0 and VBA, you must create methods for getting and setting the values of a property. These are known as Property Let and Property Get methods. A third method is available if your property will return or set an object. This is known as the Property Set method, and it works in a similar manner to the Property Let method.

1. In a new workbook open the VBE and insert a class module (choose Insert > Class Module).

2. In the Property Sheet, rename the class module cEmployee.

3. In the code pane, enter the following code:

Dim m_lngID As Long Dim m_sName As String Dim m_sTitle As String Dim m_sPhoneNumber As String

These module-level variables will contain the values for our object.

4. Next, enter the Property Let and Get functions for each property:

Property Get ID() As Long

ID = m_lngID End Property

Property Let ID(newID As Long)

m_lngID = newID End Property

Property Get Name() As String

Name = m_sName End Property

Property Let Name(newName As String)

m_sName = newName End Property

Property Get Title() As String

Title = m_sTitle End Property

Property Let Title(newTitle As String)

m_sTitle = newTitle End Property

Property Get PhoneNumber() As String

PhoneNumber = m_sPhoneNumber End Property

Property Let PhoneNumber(newPhoneNumber As String)

m_sPhoneNumber = newPhoneNumber End Property

Note that the module-level variables are used within each Property Let or Get method, and are either being returned (Get) or assigned a value (Let).

Property Get ID() As Long

ID = m_lngID End Property

Property Let ID(newID As Long)

m_lngID = newID End Property

Another advantage class modules give us is the ability to initialize the values of the module-level variables when an object is instantiated from the class.

5. Choose Class from the Object box in the code pane, as shown in Figure 1-29.

Figure 1-29. Selecting Class from the VBA code window Object box

6. The VBE inserts the Class_Initialize method for you. Add code to set the default values for the Employee class, as shown in Listing 1-4.

Listing 1-4. Class Initialization Code—Here It's Set to Nonsense Values Useful in Determining What Properties Have or Have Not Been Set.

Private Sub Class_Initialize() m_lngID = 0 m_sName = "NOG" m_sTitle = "NOG" m_sPhoneNumber = "0000000000" End Sub

There are two methods included with each class module, the Class_Initialize and the Class_Terminate methods. It's always a good idea to initialize your values so that any clients of your class have a value to work with.

The Initialize method is a great place to set default values, to open any data sources or files your class may need, or to perform any other setup that your object may need to do its job.

The Terminate method, although not always used, is important because it gives you a place to clean up any data connections or recordsets (or any other objects your class may use) and close any files you've opened.

Using the cEmployee Class

We can test our cEmployee class using the Immediate window in the VBE:

1. Open the Immediate window by choosing View > Immediate Window or by pressing the Ctrl+G key combination.

2. In the Immediate window, type - Set emp = New cEmployee, and press Enter.

3. Type - ?emp.Name and press Enter. Your screen should look like Figure 1-30.

?emp. name

Figure 1-30. Instantiating the cEmployee object in the Immediate window. Property values have not been set yet.

Figure 1-30. Instantiating the cEmployee object in the Immediate window. Property values have not been set yet.

We've returned our nonsense value from the class initialization code. Now let's assign values to our properties. Type the following commands into the Immediate window, pressing Enter after each. The first group of commands will set the cEmployee object's properties and the second will retrieve and display them.

emp.Name = "John Doe"

emp.Title = "Analyst"

emp.PhoneNumber = "8885555555"

?emp.name

?emp.ID

?emp.title

?emp.phonenumber set emp = Nothing

Your Immediate window should look like Figure 1-31.

Figure 1-31. cEmployee object with property values set and returned

Let's take a look at what's going on here. The first line of code instantiates (or creates) the employee object:

Set emp = New cEmployee

When that object is created, the Class_Initialize method fires and the default values are set. As mentioned earlier, this is where you would set up any activities or objects your class needs to have in place.

Next, a quick check of the Name property is done to see that it is holding your default value—in this case the nonsense value NOG.

The next four lines set all of the properties of the Employee object with real values:

emp.ID = 15 emp.Name = "John Doe" emp.Title = "Analyst" emp.PhoneNumber = "8885555555"

Each time you pressed the Enter key, the Property Let method fired for each property and assigned the value you passed in to the module-level variable for each property. Then you typed in commands to show that the cEmployee object was indeed storing the values entered previously.

?emp.name John Doe ?emp.ID 15

?emp.title Analyst

?emp.phonenumber 8885555555

Each time you pressed the Enter key, the Property Get method fired and retrieved the value currently stored in the module-level variable for each property.

The final line of code removes the object from memory. Any attempt to write or retrieve a value after the object is destroyed will result in an error.

set emp = Nothing

When the object is set to Nothing, any code placed in the Class_Terminate method will run. As previously noted, this is where you will perform any necessary cleanup before the object is destroyed.

The Class-y Way of Thinking

Our cEmployee class, while extremely simple in content and functionality, does serve the purpose of showing some of the benefits of writing class-based code.

Let's assume for a moment that we had written validation and formatting code into the Property Lets and Gets of the class, as well as some business rules; or that we had added methods to export the employee data to a delimited string or set of XML tags for import into an external system. It would be very easy for us to export the class module for use in someone else's Excel project, or an Access database or even a Word document.

The key to successfully implementing classes is to keep the code as generic as possible. Of course, if you are creating a class for one specific task, this is an acceptable exception to the rule, but in general, keeping code generic provides great reuse opportunities.

Classes also provide an excellent example of self-documentation via IntelliSense. Anytime you reference an object variable in your code and type the . operator, you'll see a complete list of the object's functionality (just like the built-in VBA objects, such as ADO) as shown in Figure 1-32.

Figure 1-32. Class objects provide documentation via IntelliSense.

We will focus heavily on classes and object-oriented development as we move on in this text. The ease of maintenance and high probability of reuse are well worth the extra planning required to build applications using these techniques. Once you are comfortable with these concepts, there really won't be much additional thought or planning required. It will be your natural process to work in an OOP fashion.

0 0

Post a comment