Class Modules and Custom Objects

team lib

Remember back in Chapters 4 and 5 we looked at the two primary groups of objects that we work with in Access. The Access objects themselves - including Form , Report, and Module objects - allow us to manipulate the composition of an Access application whereas the Data Access Object hierarchy - including TableDef , QueryDef, and Recordset objects - allows us to access and modify the data in tables programmatically. We are going to spend a little time now reviewing the basics in a little more detail, before we examine how Access provides developers with the capability to create their own custom objects through the use of class modules.

What Are Objects?

Object-oriented development has been a hot topic for quite a few years now, but for many people the topic is one still shrouded in mystery. It often involves obscure jargon and seemingly acrobatic mental leaps and many of the tools that are provided to implement object-oriented development have a steep learning curve. The end result is that many regard it as a black art to be practiced only by the brave, which is a shame, because the principles behind object-orientation are really fairly straightforward, once you get beyond the jargon.

So let's start with the basics and find out what objects really are. There are many definitions of what an object is, but we'll use a simple one to start with and say this:

An object is a self-contained entity that is characterized by a recognizable set of characteristics and behaviors.

For example, think of a dog as an object. Dogs are certainly recognizable by their characteristics and their behavior. If we were to put some of these down on paper we might come up with a list like this:

Characteristics

Behaviors

They are hairy

They bark

They have four legs

They bite mailmen

They have a tail

They sniff things

Size

 

Color

 

Smell

 

Now, if you were to ask anyone what is hairy, has four legs and a tail, and barks, bites, and sniffs things, there aren't many people who wouldn't instantly know that you were talking about a dog - you would have described to them quite succinctly the characteristics and behavior of a dog.

In fact, what you would be describing was not any single dog. Rather, you were describing the characteristics and behavior of all dogs. In one sense, what makes a dog a dog is that it is like all other dogs. Sure, there are some minor differences, in size, color (and smell), but all dogs have a certain dogginess. Now, before you start to think that you are reading a book on canine philosophy, let's apply that to the world of software. An object-oriented programmer would have summarized those last couple of paragraphs like this:

  • There exists a class called Dog

  • Instances of this Dog class have the following properties : Hairiness , Four-Leggedness , Tailedness , Size , Color , Smelliness

  • Instances of this Dog class expose the following methods : Bark , Bite , Sniff

OK, so let's look at some of that jargon. First of all - classes . A class is a type of blueprint or mold. In the case of animals that blueprint is genetic. If the objects we were talking about were candles , the blueprint would be the mold into which the wax is poured.

Individual dogs and candles are instances of their particular class, and as such they inherit the characteristics of the class to which they belong. Dogs bark because that is a characteristic of the Dog class. So we can now define a class.

A class is a blueprint or template that defines the methods and properties of a particular type of object.

Now, let's have a look at an object in the Data Access Object hierarchy with which we are already familiar - the Recordset object - and see how it fits into our model. First of all we can say that all Recordset objects have the same properties and methods. The properties include things like the RecordCount property, which is the number of records in the Recordset object, and the Updatable property, which indicates whether the Recordset object can be updated. The methods include the GetRows method, which takes a given number of records and places them into an array. All Recordset objects possess the same built-in methods and properties, because they are all derived from the same class. As Access developers we cannot see the class itself - all we see are the objects that are instantiated from that class. The class itself ( CDaoRecordset ) was defined by Microsoft developers using the language C. What we see in VBA are instances of that class.

Why Use Objects?

In many respects, this question doesn't even make sense in modern programming. Everything that you manipulate in Access is an object, from forms and reports, to the controls that you place on those forms and reports , to the database and recordset objects that you use to manipulate data. You have already used many different objects in the lessons that you have completed so far, in fact if you use the properties and methods of forms and controls, you are using objects whether you realize it or not.

Given their ubiquitous nature, we need to understand and be comfortable with the lingo that surrounds them Hopefully, this section will blow away some of the mystique that surrounds the long words that plague object orientation. The major benefits of using classes to create custom objects in Access originate from the principles of abstraction, encapsulation, and polymorphism. We'll have a look at what those mean right now.

Abstraction

One of the more important advantages of using classes is gained through something called abstraction . What that means is simply that users of the object shouldn't have to know the nitty-gritty of how the object does what it does. In other words, the developer doesn't need to worry about technicalities. It is a bit like turning an electric light on. People don't need to know anything about voltage, current, and resistance. All they need to know is how to flick a switch. They are removed from the physics that results in the bulb lighting and the room getting brighter.

We can see how this works with built-in DAO objects such as the Recordset object. All we need to do is to use the Requery method and somehow the Recordset object is repopulated with a more recent set of data. How does it do it? Who cares! All we need to know is that it works. And that is cool, because it means that we can spend more time developing our application rather than worrying about the low-level details of things like cursor functionality. The methods and properties of the object are called its interface because that is how it communicates with or interfaces to other objects.

The interface is that part of an object that is exposed on the outside to users of the object; it defines how we are allowed to interact with the object. The implementation is the code, internal to the class and invisible from the outside, which is responsible for defining how the object does what it does. If the lighting in your house was implemented as an object, the interface would consist of the light switches and lamps, and the implementation would consist of the electrical wiring.

We can do the same with the custom objects that we build using class modules. In fact one of our goals when creating objects using class modules should be to keep the interface as simple as possible, irrespective of how complicated the implementation might be.

Important 

Abstraction means we can use objects without having to know the software details of how the object does what it does. This makes them easy to use and is one of the key advantages of using classes to define custom objects in Access. Such objects are often referred to as "Black Boxes" since you can't see past the interface to see how they really work inside.

Encapsulation

Closely related to abstraction is the idea of encapsulation . Objects should encapsulate within them everything they need to allow them to do what they do. That means that they should contain their own methods, properties, and data - and it means that they don't need to rely on other objects to allow them to exist or to perform their own actions.

As we saw in Chapter 5, Forms and Reports are types of objects. They illustrate encapsulation quite well - if you use VBA for your event procedures you can import a form into another database and all the controls on the form and the code in its module go over with it. It's all encapsulated in the form.

Another good example of encapsulation is an ActiveX control such as the Calendar control. The Calendar control carries with it - or encapsulates - its own methods and properties, which are immediately accessible to you when you place it on a form. You could even think of this as a kind of software Plug-and-Play technology for developers: one programmer writes a component - and that component can simply be plugged into another program and function properly.

Important 

Encapsulation makes it easy to reuse classes because everything needed by the class to do it's job is available internally, there is no need to reference external code. This can not only speed up subsequent development projects that use these objects, but it also allows us to apply business rules consistently and makes group development much easier. This reusability is a key benefit of building our own custom objects in Access - if we do it right!

Of course there are cases where objects interact with or rely on other objects. As an example, the recordset object is not truly self contained since it requires the database object to open its dataset for it. You will find instances where you will need to develop systems of classes, each class doing its part in the larger task. The principal of encapsulation remains, however; as much as possible each class should contain all of the properties and methods required to do its part of the task, and should hide the complexity of the implementation details.

Polymorphism

What a great word! The concept is pretty cool - it just means that you can have a whole load of disparate objects, but you can tell them all to do the same thing and they'll all know how to do it. Put another way, it means that objects can share the same methods and properties, but have different content behind the methods and properties to implement their behavior. For example, controls, forms, and pages in Access all have a SetFocus method. In all these cases, invoking the method shifts focus to the selected object, but the way they do it 'under the hood' is different in each case.

We can implement polymorphism in the custom objects we build in Access using class modules in two ways: through early binding and through late binding. We will look at both of these techniques later in this chapter.

Important 

The advantage of polymorphism is that we can present a familiar, consistent interface to the users of our custom objects, while hiding the differences in implementation.

Inheritance

Inheritance is mentioned here, as it is a fairly key concept of object-oriented design. What it means is that you can create a new class called a subclass that inherits all of the class's methods and properties. In the analogy we have been using, we could say that the Dog class is a subclass of the Mammal class. It therefore inherits the properties and methods of that class. The mammal class could have properties such as hairiness, four leggedness, and methods such as Runs and Eats . Such properties and methods can now be inherited from the Mammal class into a subclass Dog . The Dog class does not have to create such properties and methods since it can use the inherited ones.

Inheritance makes it easier to create new classes, as you are often able to simply sub-class an existing class and then add some specialization. However, there is no opportunity for this when using custom objects in Access, as VBA does not support it. Lack of inheritance is the largest single reason that VBA is not considered a true OO language.

The MyRectangle Class

That's enough of the theory. What we'll do now is build a fairly simple class so that we can analyze how Access implements the three features of OO that Access does provide us - abstraction, encapsulation, and polymorphism.

In fact, we will be creating a simple method of determining the area of a rectangle given its height and width. The formula that we use to calculate the area is quite straightforward:

click to expand
 Area = Height x Width 

However, we would like our programmers to be able to derive the area without having to remember even this bit of mathematics.

This is, of course, a deliberately simple example. Later in the chapter, once we have got to grips with how class modules work, we will look at a more realistic example of using an object to hide the technicalities of some of the business rules used by the Ice Cream Shop.

Try It Out-Creating the MyRectangle Class

  1. Open up the IceCream.mdb database and switch to the VBA IDE by hitting Alt+F11 .

  2. Insert a new class module. You can do this either by selecting Class Module from the Insert menu or by hitting the Insert Class Module button on the toolbar.

  3. A new class called Class1 should now appear in the Project Explorer window. If the Properties window is not visible, make it so by hitting F4 and then change the name of the class to MyRectangle .

  4. Now, in the code window, add the following declarations to the Declarations section of the class module.

       Option Compare Database     Option Explicit     Private dblHeight As Double     Private dblWidth As Double   
  5. Next add the following two Property Let procedures making sure that you declare them with Public scope. Don't worry about the strange syntax; we will look at how they work in just a moment.

       Public Property Let Height(dblParam As Double)     If dblParam < 0 then    dblHeight = -dblParam  Else     dblHeight = dblParam  End if     End Property     Public Property Let Width(dblParam As Double)     If dblParam < 0 then    dblWidth = -dblParam  Else     dblWidth = dblParam     End if     End Property   
  6. Now you should add the following three Property Get procedures as they appear below. Again, we will look at how they work later on.

       Public Property Get Height() As Double     Height = dblHeight     End Property     Public Property Get Width() As Double     Width = dblWidth     End Property     Public Property Get Area() As Double     Area = dblHeight * dblWidth     End Property   
  7. That is our class completed, so save the module as MyRectangle by hitting Ctrl+S .

  8. Now create a new standard code module and call it Chapter 13 Code . Add the following procedure to the new module:

       Sub ClassDemo()     Dim objRect As MyRectangle     Set objRect = New MyRectangle     objRect.Height = 5     objRect.Width = 8     Debug.Print "The area of a rectangle measuring " & objRect.Height & _     " x " & objRect.Width & " is " & objRect.Area     Set objRect = Nothing     End Sub   
  9. Now run the ClassDemo procedure by typing its name in the Immediate window and hitting Enter . If you have typed everything in correctly, you should see the following result:

    click to expand

So there we have a fully functioning class that we can use to determine a rectangle's area whenever we want to!

How It Works

In this exercise we created a MyRectangle class. This has three properties: Height , Width, and Area .

The first thing that we did was to create a new class by inserting a new class module into our project, a simple enough process. We then declared two private variables that would be used to store the dimensions of the height and width of the rectangle:

 Private dblHeight As Double Private dblWidth As Double 

It is important to notice that these variables all have Private scope. That is to say that although available for use anywhere within the class module, they cannot be accessed from outside the class module. These are part of the implementation of the class; they are not part of its interface and their values can only be viewed or changed by code within the class module itself.

So how do developers specify the dimensions of the rectangle? The answer is that they set the object's Height and Width properties. We expose properties by using two special types of procedure. A Property Let procedure is used to expose a writeable property and a Property Get procedure is used to expose a readable property. In other words, we use a Property Let if we want developers to be able to let the object's property equal some value; and we use a Property Get if we want developers to be able to get (that is, read) the value of the property. If a property is to be readable and writeable then we use both a Property Get and a Property Let procedure.

We'll have a look first at one of the writeable properties:

click to expand

Before any methods or properties can be used, we must dimension the object and create an instance. We will look at that process in a few moments. Once the object has been created, a developer can set the value of the Height property like this:

 objRect.Height = 5 

When this happens, the value ( 5 ) is automatically passed into the Property Let procedure as the argument dblParam . One very important reason for using the property let/get instead of just making a class variable public is that we can do checking on the values passed in to prevent problems. In this example I checked whether the length passed in was negative, and if so, simply used the negative of the value passed in to correct the problem. We know that a length cannot be negative, and if we allowed it to be passed in we could end up with a negative area, so we correct that problem if it happens. We then place the original or corrected value into the module level Private variable dblHeight that we declared earlier.

   If dblParam < 0 then    dblHeight = -dblParam  Else     dblHeight = dblParam  End if   

The Area property is exposed as a read-only property. That means that we do not need a Property Let procedure, but use a Property Get procedure instead.

click to expand

The value returned by the Area property is the result of multiplying the rectangle's height by its width.

 Area = dblHeight * dblWidth 

The Height and Width properties are readable and writeable because they each have a Property Get and a Property Let procedure. By contrast, the Area property is read-only because it has a Property Get procedure but no Property Let procedure.

Less commonly we may encounter write-only properties (that is properties whose values we can set but cannot inspect). The Password property of the User object in the DAO hierarchy is a write-only property. If we wanted to implement a write-only property in one of our class modules, we would do so by exposing the property via a Property Let procedure with no associated Property Get procedure.

Important 

The point of this example is to show how you can hide a piece of logic behind a simple interface. In this case, the logic (working out the area of the rectangle) is very straightforward, but in many situations the logic stored within the implementation of object can be quite complex. In fact the more complex the logic, the more benefit can be realized by wrapping it up in an object with a simple interface.

That is really all there is to defining our class. What we end up with is a class that can be used to create MyRectangle objects. It has two read/write properties ( Height and Width ) and a read-only Area property.

When we want to use one of these MyRectangle objects, we create it using this syntax:

 Dim objRect As MyRectangle Set objRect = New MyRectangle 

The first line creates a variable objRect designed to hold a pointer to the MyRectangle object. A pointer is a generic term for the thing that "points to" the actual memory used to store a variable. Note that at this stage the object has not been created, there is just a variable ready to reference the object once it is created. The second line is the one that actually instantiates (creates an instance of) a MyRectangle object and returns a reference to it as objRect .

You might have noticed as we were typing in the first line of code that the MyRectangle class actually appeared within the list of available object types.

click to expand

The next step is to instantiate a MyRectangle and we do this by using the New keyword.

 Set objRect = New MyRectangle 

This is a key moment in the whole process. It is the programming equivalent of Dr. Frankenstein throwing the big switch on the wall and looking on in amazement as his creation comes to life. Whereas before we just had a lifeless class, we now have a living, breathing object. A MyRectangle object has been created and a reference to it is returned in the objRect variable. Oh, the rapture...

A little more prosaically, once the new MyRectangle object has been created, we can then set its Height and Width properties:

 objRect.Height = 5 objRect.Width = 8 

and then, we can inspect the object's Height , Width, and Area properties:

 Debug.Print "The area of a rectangle measuring " & objRect.Height & _       " x " & objRect.Width & " is " & objRect.Area 

Again, notice that as we type in the lines above, we are prompted by IntelliSense with the names of the properties that we created because they form part of the object's public interface.

click to expand

Finally, once we have finished with our monster (sorry, object) we destroy it by setting it to Nothing , so releasing any resources that it was using up.

 Set clsRect = Nothing 

So, that's how easy it is to implement an object with readable and writeable properties. But what about methods? Well, we'll see just how easy that is now by implementing a new method for our MyRectangle object. The new DoubleSides method will double the height and width of the rectangle that we create.

Try It Out-Extending the MyRectangle Class

  1. Open up the MyRectangle class module that we created in the previous exercise and add the following procedure definition:

       Public Sub DoubleSides()     dblHeight = dblHeight * 2     dblWidth = dblWidth * 2         End Sub   
  2. Now open the Chapter 13 Code module, locate the ClassDemo procedure and add the following lines of code to it:

     Debug.Print "The area of a rectangle measuring " & objRect.Height & _       " x " & objRect.Width & " is " & objRect.Area   objRect.DoubleSides     Debug.Print "The area of a rectangle measuring " & objRect.Height & _     " x " & objRect.Width & " is " & objRect.Area   Set objRect = Nothing 
  3. Now save the changes you have made and run the ClassDemo procedure in the Immediate window. This time the result you see should look like this:

    click to expand

As you can see, the DoubleSides method has doubled the rectangle's height and width (and the area has therefore increased four-fold).

How It Works

To implement a method in our custom object, we simply need to add a public procedure to the class module. The procedure needs to be Public , because it forms part of the MyRectangle object's interface. This procedure can then be invoked as a method of the MyRectangle object.

It really is as simple as that!

Before we look in more detail at creating methods and properties, let's revisit those four concepts that we discussed earlier in relation to classes (abstraction, encapsulation, polymorphism, and inheritance) and see how they apply in this situation.

Abstraction

The principle behind abstraction is producing a simple interface and hiding the complexity of the implementation. It's a bit like watching a swan gliding across the water. It looks so graceful you would think that there was nothing to it, but if you look underwater you will see that its legs are pumping away like there is no tomorrow! That's what we should aim for with our classes, a simple interface irrespective of the complexity of the implementation.

To be fair, the MyRectangle class does not contain any excessively complex logic. The calculation of the area is fairly straightforward. But that's because this is a deliberately simple example to show you how to build classes. In practice the logic implemented by a class's methods and properties might be exceedingly obscure and complicated. But users of the class won't need to worry about what is going on under the hood. They simply set or inspect the properties or invoke the methods and all the hard work is done by the object. Easy! Furthermore, by encapsulating all the functionality in one place, you can comment the code heavily and have a single place to go to discover how it works if you or your replacement ever has to revisit the implementation.

Encapsulation

This is a strong point of the MyRectangle class. It is completely self-contained and doesn't rely on the existence of any other objects in order to allow it to operate properly. We could export this class into another database and it would function just as well there.

The other thing to notice is that the two key variables that hold the sizes of the height and width of the rectangle ( dblHeight , dblWidth ) are owned by the object itself (they are Private ) and cannot be manipulated directly by external code. The only way that developers can interact with our MyRectangle object is through the interface we have defined, while the rest of the implementation is hidden away.

So our MyRectangle class scores highly for encapsulation.

Polymorphism

This is a slightly tougher one. If you remember, polymorphism means that different objects can share the same methods and properties, but can have different content behind the methods and properties to implement their behavior. On its own the MyRectangle doesn't exhibit polymorphism, but we can see how we can introduce it if we define a new MyKite class.

A kite is a two-dimensional object whose area can be calculated by multiplying its height by its width and dividing by two:

 Area = (Height x Width) / 2 

So we can create a MyKite class to return an Area property like this:

   Option Compare Database     Option Explicit     Private dblHeight As Double     Private dblWidth As Double     Public Property Let Height(dblParam As Double)     dblHeight = dblParam     End Property     Public Property Let Width(dblParam As Double)     dblWidth = dblParam     End Property     Public Property Get Height() As Double     Height = dblHeight     End Property     Public Property Get Width() As Double     Width = dblWidth     End Property     Public Property Get Area() As Double     Area = (dblHeight * dblWidth) / 2     End Property     Public Sub DoubleSides()     dblHeight = dblHeight * 2     dblWidth = dblWidth * 2         End Sub   
  • You might find this section easier to follow if you build the MyKite class by using the code laid out in this section. Alternatively, you can find all of this code in the IceCream.mdb database.

Now if we wanted to determine the area of a kite, we could use our MyKite class to do this by writing a procedure such as this in a standard code module:

   Sub ClassDemo2()     Dim objKite As MyKite     Set objKite = New MyKite     objKite.Height = 5     objKite.Width = 8     Debug.Print "The area of a kite measuring " & objKite.Height & _     " x " & objKite.Width & " is " & objKite.Area     objKite.DoubleSides     Debug.Print "The area of a kite measuring " & objKite.Height & _     " x " & objKite.Width & " is " & objKite.Area     Set objKite = Nothing     End Sub   

If you run this code, you should see this in the Immediate window:

click to expand

This should look fairly familiar! The MyRectangle class and the MyKite class share two read-write properties ( Height and Width ), a read-only Area property, and a DoubleSides method. We have implemented a form of polymorphism across our objects. So what good is that? Well, for one thing it makes it easier for the developer to learn how to use the objects, as there is just one interface to learn. We define the dimensions of the MyRectangle and MyKite objects with just the same syntax, and we can find their areas by inspecting the same property.

It also means that we can write a procedure that treats both MyRectangle and MyKite objects the same like this:

   Function GetObjectArea(obj As Object, _     dblHeight As Double, _     dblWidth As Double) As Double     obj.Height = dblHeight     obj.Width = dblWidth     GetObjectArea = obj.Area     End Function   

If we wanted to, we could pass a MyRectangle object to the GetObjectArea function:

   Sub ClassDemo3()     Dim objRectangle As MyRectangle     Set objRectangle = New MyRectangle     Debug.Print "The rectangle's area is " & GetObjectArea(objRectangle, 5, 8)     Set objRectangle = Nothing     End Sub   

Or we could pass a MyKite object in, just as easily:

   Sub ClassDemo3a()     Dim objKite As MyKite     Set objKite = New MyKite     Debug.Print "The kite's area is " & GetObjectArea(objKite, 5, 8)     Set objKite = Nothing     End Sub   

In both situations, the GetObjectArea function is able to use the object's Height , Width, and Area properties, irrespective of whether the object passed in is a MyRectangle object or a MyKite object.

 obj.Height = dblHeight obj.Width = dblWidth GetObjectArea = obj.Area 

The problem with this technique is that we have to use a generic object variable (the variable obj uses the Object data type). Because of this, VBA does not know what type of object will be stored in the variable and, as a result, we are not prompted with the names of the properties when we use the dot operator after the variable obj .

More importantly, if we mis-spell the name of one of the properties, VBA will not pick up this error when we compile our code. Instead, the first time we will know that we have got the property name wrong is when we try to run our code. So a line like this will not cause a compile-time error:

 obj.Height = dblHeight 

But when we try to run the ClassDemo3 procedure, VBA will generate a run-time error.

click to expand

This is because VBA is using a technique known as late binding . Not only does late binding make it harder to ensure our code is error-free at design time; it also makes our code run slower. Fortunately, there is a better way to implement polymorphism.

We will look at the performance implications of late binding later on in Chapter 19.

Polymorphism through Early Binding

Thus far the MyRectangle and MyKite objects share the same interface because we happened to give them both the same properties and methods. However, in VBA it is now possible to implement polymorphism through the use of the Implements keyword. We'll try this out for ourselves , and then investigate how it works and what its implications are.

Try It Out-Using the Implements keyword
  1. In the IceCream.mdb database insert a new class module and call it Shape .

  2. Add the following code in the code window of the Shape class:

     Option Compare Database Option Explicit   Public Property Let Height(dblParam As Double)     End Property     Public Property Get Height() As Double     End Property     Public Property Let Width(dblParam As Double)     End Property     Public Property Get Width() As Double     End Property     Public Property Get Area() As Double     End Property     Public Sub DoubleSides()     End Sub   
  3. Now insert a new class module and call it MyShapelyRectangle .

  4. At the top of this new class module, add the following statement:

     Option Compare Database Option Explicit   Implements Shape     Private dblHeight As Double     Private dblWidth As Double   
  5. If you look in the object box at the top of the module window, you should see that there is now a reference to the Shape object.

  6. Select the Shape object and you should then be able to see the names of the available properties and methods in the procedure combo. Start by selecting the Height property:

    click to expand
  7. Add the following code to the Shape_Height property Let procedure and the Shape_Height property Get procedure:

     Private Property Let Shape_Height(RHS As Double)   dblHeight = RHS   End Property Private Property Get Shape_Height() As Double   Shape_Height = dblHeight   End Property 
  8. Now add the code for the remaining two properties and for the DoubleSides method. The completed module should now look like this:

     Option Compare Database Option Explicit Implements Shape Private dblHeight As Double Private dblWidth As Double   Private Property Get Shape_Area() As Double     Shape_Area = dblWidth * dblHeight     End Property     Private Sub Shape_DoubleSides()     dblWidth = dblWidth * 2     dblHeight = dblHeight * 2     End Sub   Private Property Let Shape_Height(RHS As Double)   dblHeight = RHS End Property Private Property Get Shape_Height() As Double   Shape_Height = dblHeight End Property   Private Property Let Shape_Width(RHS As Double)     dblWidth = RHS     End Property     Private Property Get Shape_Width() As Double     Shape_Width = dblWidth     End Property   
  9. Next, create a new MyShapelyKite class and repeat Steps 4 to 8. However, this time, make sure to define the Shape_Area property so that it returns half of the width multiplied by the height. The resultant MyShapelyKite module should look like this:

       Option Compare Database     Option Explicit     Implements Shape     Private dblHeight As Double     Private dblWidth As Double     Private Property Get Shape_Area() As Double     Shape_Area = (dblWidth * dblHeight) / 2     End Property     Private Sub Shape_DoubleSides()     dblWidth = dblWidth * 2     dblHeight = dblHeight * 2     End Sub     Private Property Let Shape_Height(RHS As Double)     dblHeight = RHS     End Property     Private Property Get Shape_Height() As Double     Shape_Height = dblHeight     End Property     Private Property Let Shape_Width(RHS As Double)     dblWidth = RHS     End Property     Private Property Get Shape_Width() As Double     Shape_Width = dblWidth     End Property   
  10. Now open the code module Chapter 13 Code, which you created earlier in this chapter, and add the following three procedures:

       Function GetShapeArea(shp As Shape, _     dblHeight As Double, _     dblWidth As Double) As Double     shp.Height = dblHeight     shp.Width = dblWidth     GetShapeArea = shp.Area     End Function     Sub ClassDemo4()     Dim shpRectangle As Shape     Set shpRectangle = New MyShapelyRectangle     Debug.Print "The rectangle's area is " & GetShapeArea(shpRectangle, 5, 8)     Set shpRectangle = Nothing     End Sub     Sub ClassDemo4a()     Dim shpKite As Shape     Set shpKite = New MyShapelyKite     Debug.Print "The kite's area is " & GetShapeArea(shpKite, 5, 8)     Set shpKite = Nothing     End Sub   
  11. Finally, run the ClassDemo4 and ClassDemo4a subprocedures in the Immediate window. You should see the correct results:

    click to expand

So now we have two separate objects, the MyShapelyKite and the MyShapelyRectangle objects, sharing a single common interface define by the Shape class module. "So what?" you might say! Well, just as in the previous example, we can treat these two objects the same (that is they can both be passed in to the GetShapeArea procedure). The key difference, however is that this time we are using early binding . That means that not only will our code execute more quickly at run time, but also we are much less likely to make mistakes when we write our code in the first place. Let's look at how it works.

How It Works

The first thing that we do in this exercise is to create a dummy interface class called Shape . The purpose of this class is purely to provide a common interface that other classes can then use to provide their own interface services.

As you can see, the only thing that this class contains is the definitions for the methods and properties that will appear in the interface.

 Option Compare Database Option Explicit Public Property Let Height(dblParam As Double) End Property Public Property Get Height() As Double End Property Public Property Let Width(dblParam As Double) End Property Public Property Get Width() As Double End Property Public Property Get Area() As Double End Property Public Sub DoubleSides() End Sub 

As in the previous examples, the interface for this class will contain read-write Height and Width properties, both of which accept a Double value, and one read-only Area property, which returns a Double . There is also a DoubleSides method.

Note how the Shape class contains only the definitions for the methods and properties. There is no code to explain how these methods and properties will be implemented. So the interface is now totally separate from the implementation (Object Nirvana!).

The next step is to instruct VBA that the MyShapelyRectangle and MyShapelyKite classes will use the Shape class to provide their interface. We do that by using the Implements keyword in the Declarations section of the MyShapelyRectangle and MyShapelyKite class modules.

 Implements Shape 

When you insert an Implements statement into a class module, VBA then causes the interface elements (that is, the method and property declarations) to be inherited by the class in which the Implements keyword is placed. This means that both the MyShapelyRectangle and MyShapelyKite classes inherit the Height , Width, and Area properties and the DoubleSides method defined in the Shape class. That is why this method of sharing interfaces is sometimes referred to as interface inheritance .

We need to be careful when using interface inheritance to make sure that the class inheriting the interface provides handlers for every element of the interface defined in the dummy interface class. In our example, that means that the MyShapelyRectangle and MyShapelyKite methods must provide handlers for the Height , Width, and Area properties and the DoubleSides method defined in the Shape class. Failure to implement any property or method defined in the dummy interface class will cause a compile error.

In order to implement a handler for the inherited interface element, we simply provide a Private procedure with the name of the procedure modified to indicate that it represents an inherited element. So whereas our MyRectangle class originally implemented its own native Height property like this:

 Public Property Let Height(dblParam As Double) 

the new MyShapelyRectangle now implements the Height property that it has inherited from the Shape dummy interface class like this:

 Private Property Let Shape_Height(RHS As Double) 

As you can see, the first difference is that the name of the procedure is prefixed with the name of the class from which the interface element has been inherited. More interestingly, the property is now declared with the Private keyword. So, why is it private? The answer is that the MyShapelyRectangle object is now all implementation and no interface. We only want developers to manipulate the object via the Shape interface, so that is why the Shape class is the only module that will contain public procedures. In case you're wondering, you can define a public function in MyShapelyRectangle . Its existence does not cause a compile error but it cannot be seen outside of the class, and trying to reference it as a method will give a compile error.

If you are wondering why VBA gives the parameter the somewhat obscure variable name RHS , it is because the parameter represents the value on the R ight H and S ide of the equals sign in the property assignment, for example shpKite.Height = 8 .

One of the key advantages of interface inheritance is that it allows early binding . Because both the MyShapelyRectangle and MyShapelyKite objects share the same Shape interface, we can define a variable as a Shape and then use it store references to both MyShapelyRectangle and MyShapelyKite objects.

So, whereas before we had to pass the generic object like this:

 GetObjectArea (obj As Object, ... 

We can now pass it like this:

 GetShapeArea (shp As Shape, ... 

The advantage of this technique is that, because VBA knows that we will be dealing with objects that use the Shape interface, it can prompt us with the names of the objects' methods and properties as we are typing code in the GetShapeArea procedure.

click to expand

In fact, we can use the very same variable within the same subroutine to contain a reference first to MyShapelyKite object, and then to a MyShapelyRectangle object, as shown in the code sample below:

   Sub ClassDemo4b()     Dim shp As Shape     'First we use shp to refer to a MyShapelyKite object     Set shp = New MyShapelyKite     shp.Height = 8     shp.Width = 5     Debug.Print "The kite's area is " & shp.Area     Set shp = Nothing     'And then we use it to refer to a MyShapelyRectangle object     Set shp = New MyShapelyRectangle     shp.Height = 8     shp.Width = 5     Debug.Print "The rectangle's area is " & shp.Area     Set shp = Nothing     End Sub   

In this example, we would again be prompted with the names of the methods and properties because we are using a Shape object variable:

That shows that VBA is now using early binding, which means that not only will it prove easier for us to write error-free code at design time but also that our code will execute more quickly at run time.

Inheritance

The fourth and final feature that we noted earlier was characteristic of object-oriented development was inheritance. Unfortunately the current version of VBA does not support inheritance in the traditional sense. True inheritance allows us to take an existing class and derive a subclass from it, which inherits both interface and implementation details from the original class. Although VBA allows us to implement interface inheritance through the use of the Implements keyword, there is no easy way to inherit functionality from another class.

The PaymentStats Class

So far we have covered what might seem like a lot of new ground to many of you. If this is your first experience with object-oriented programming, then you might find the wealth of new terminology somewhat overwhelming. So what we'll do now is to look at how we can implement a class in the Ice Cream database. Hopefully, this will help to reinforce some of the concepts that we have covered earlier, while allowing us to look at some more of the features of class-based development in VBA.

The Business Need

As you know, the database that we are using throughout this book is IceCream.mdb , a database that contains stock and sales information for an ice-cream making company. Anyone who has been involved in running a company will know that one of the most important functions within a company is collecting payments. Invoicing clients on a timely basis is fine, but, as the saying goes, "Cash is King!" If clients don't pay their bills in a timely fashion, then a company can very soon find itself facing cash-flow problems.

For this reason, the Dave and Rob's Ice Cream Company employs a credit collection agency, "Harry, Grabbit, and Scarper", to collect payment on their outstanding invoices. The Finance Director at Dave and Rob's know that the company will be OK if it can ensure that 90% of its invoices are paid within 40 days of the order being received. So Dave and Rob's has a service-level agreement in place, which states that every month, if Harry, Grabbit, and Scarper fails to collect 90% of invoices within 40 days, then it will be fined $1,000 for every percentage point of invoices not collected within that period.

For example, if Harry, Grabbit, and Scarper only collects 84% of invoices issued in September within 40 days, then it will have to pay a fine of (90 - 84) * $1,000.00 = $6,000.00.

The Finance Director would therefore like to be provided with the following information on a monthly basis for presenting to board meetings:

  • What percentage of invoices, for orders placed within that month, were collected within 40 days?

  • Did Harry, Grabbit and Scarper hit the targets in the service-level agreement and, if not, what fine does it owe us?

The Finance Director would also like to know:

  • What is the shortest delay between a client placing an order in that month and paying for it?

  • What is the longest delay?

  • What is the average delay?

The Object Model

To help us to answer these questions, we will build a custom object. This object, which we will call PaymentStats , will contain a month's payment information and will expose a number of simple properties, which allow us to determine the information requested by the Finance Director.

The following table details the properties that our PaymentStats object will need to expose:

Property

Data Type

Read/Write

Comments

MeanDelay

Double

Read Only

Stores the average delay (in days) between invoicing ( DateOrdered ) and payment ( DatePaid )

MinimumDelay

Integer

Read Only

Stores the minimum delay (in days) between invoicing and payment

MaximumDelay

Integer

Read Only

Stores the maximum delay (in days) between invoicing and payment

The PaymentStats object will also need to return the percentage of invoices paid within 40 days and the fine payable by Harry, Grabbit, and Scarper, but these will be implemented as methods.

Method

Arguments

Return Data Type

Comments

Percentile

Days (Integer)

Single

Returns the percentage of invoices paid within the number of days specified by the Days argument

FinePayable

Days (Integer)

Percent (Single)

UnitFine (Currency)

Currency

Returns the size of the fine payable calculated according to the following formula:

[UnitFine] times( [Percent] minuspercent of invoices paid within [Days] days)

The reason that these two methods are exposed as methods, rather than properties, is that because they are implemented as methods, we can pass parameters to them. We could have implemented a Percentile property to return the percentage of invoices paid within 40 days. However, that is fairly inflexible . If we changed the service-level agreement to say that 90% of invoices had to be paid within 35 days, we would need to re-write the way that the Percentile property was implemented. By implementing Percentile as a method, we can still return a value, but we can parameterize the method to allow us to specify the delay for which we want the percentile returned.

The same argument applies for the FinePayable method. By implementing it as a method rather than a property we can vary the number of days, the percentage cut-off, and the unit fine per percentage point by which the credit collection company missed its target.

If we are concerned about the fact that developers will have to remember extra arguments when calling these methods, we can implement them as optional arguments with default values. In other words, for the Percentile method, we can make the Delay optional (that is, the programmer can choose to feed it a value or not) but set the default to 40 . That way, if there is no argument provided, 40 will be used. This reduces the chances for error but still allows the programmer to change the delay if needed.

Finally, we will need a method for loading the payment data into the PaymentStats object before we perform the various calculations.

Method

Arguments

Return Data Type

Comments

LoadData

Month (Integer)

Year (Integer)

N/A

Loads payment data for the specified Month of the specified Year .

Building the Interface

The first step is to create the interface for the new PaymentStats object. Once we have done that we can add the implementation.

Try It Out-Creating the PaymentStats Class Interface
  1. Open up the IceCream.mdb database and switch to the VBA IDE by hitting Alt+F11 .

  2. Insert a new class module. You can do this either by selecting Class Module from the Insert menu or by hitting the Insert Class Module button on the toolbar.

  3. In the Properties window, rename the class PaymentStats .

  4. Add the following code to create the declarations for the three properties:

     Option Compare Database Option Explicit   Public Property Get MeanDelay() As Double     End Property     Public Property Get MinimumDelay() As Integer     End Property     Public Property Get MaximumDelay() As Integer     End Property   
  5. Now add the declarations for the three methods:

       Public Sub LoadData(Month As Integer, Year As Integer)     End Sub     Public Function Percentile(Optional Days As Integer = 40) As Single     End Function     Public Function FinePayable(Optional Days As Integer = 40, _     Optional Percent As Single = 90, _     Optional UnitFine As Currency = 1000) As Currency     End Function   
  6. Save the changes you have made to this class module and then open up the standard code module Chapter 13 Code .

  7. Add the following procedure that we will use to create an instance of the PaymentStats class, load it with data, and then retrieve the information we want.

       Sub ShowPaymentStats(intMonth As Integer, intYear As Integer)     Dim objPayStats As PaymentStats     Set objPayStats = New PaymentStats     objPayStats.LoadData intMonth, intYear     Debug.Print "  Min Delay: "; objPayStats.MinimumDelay     Debug.Print "  Max Delay: "; objPayStats.MaximumDelay     Debug.Print " Mean Delay: "; objPayStats.MeanDelay     Debug.Print " 40 day %ile: "; objPayStats.Percentile     Debug.Print "Fine Payable: "; objPayStats.FinePayable     End Sub   
  8. Next compile the project by selecting Compile Ice Cream from the Debug window.

  9. Finally, run the ShowPaymentStats procedure by typing the following in the Immediate window and hitting Enter .

     ShowPaymentStats 12, 2002 

    You should see the results shown next.

    click to expand

How It Works

OK, so there is not a lot of functionality here at the moment, but at least we have got the interface sorted out. The ShowPaymentStats procedure accepts two arguments, intMonth and intYear , which between them denote the month whose data is to be analyzed .

The first two lines of the ShowPaymentStats procedure instantiate the PaymentStats class.

 Dim objPayStats As PaymentStats Set objPayStats = New PaymentStats 

We then invoke the LoadData method, which will load the appropriate month's data into the object.

 objPayStats.LoadData intMonth, intYear 

Next, we inspect the three properties of the PaymentStats object.

 Debug.Print "  Min Delay: "; objPayStats.MinimumDelay Debug.Print "  Max Delay: "; objPayStats.MaximumDelay Debug.Print " Mean Delay: "; objPayStats.MeanDelay 

Finally we invoke the two methods that return values.

 Debug.Print " 40 day %ile: "; objPayStats.Percentile Debug.Print "Fine Payable: "; objPayStats.FinePayable 

Notice that we are not supplying arguments to these two methods. That is because the arguments have been declared as Optional and have default values. This means that the two lines above are equivalent to these:

 Debug.Print " 40 day %ile: "; objPayStats.Percentile 40 Debug.Print "Fine Payable: "; objPayStats.FinePayable 40, 90, 1000 

Implementing the Logic

Now that we have implemented the interface for the PaymentStats class, we can set about implementing its functionality. That's what we will do in this next exercise.

Try It Out-Implementing the PaymentStats logic
  1. Open up the code window for the PaymentStats class module and add the following private variable declarations at the top of the class module:

     Option Compare Database Option Explicit   Private varSalesArray As Variant     Private lngTotalRecords As Long     Private lngTotalDelay As Long     Private dblMeanDelay As Double     Private intMinDelay As Integer     Private intMaxDelay As Integer     Private sngPercentile As Single   
  2. Now add the following code to the procedure that defines the LoadData method:

       Dim rec As Recordset     Dim strSQL As String     strSQL = "SELECT DatePaid - DateOrdered AS PaymentDelay " & _     "FROM tblSales " & _     "WHERE Month(DateOrdered) = " & Month & " " & _     "AND Year(DateOrdered) = " & Year & " " & _     "AND Not IsNull(DatePaid) " & _     "ORDER BY DatePaid - DateOrdered"     Set rec = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)     If rec.RecordCount Then     rec.MoveLast     rec.MoveFirst     varSalesArray = rec.GetRows(rec.RecordCount)     End If     rec.Close     If VarType(varSalesArray) And vbArray Then     CalcStats     End If   
  3. Next add the CalcStats procedure to the class module, making sure to define it as a Private subprocedure:

       Private Sub CalcStats()     Dim i As Integer     'Determine total records     lngTotalRecords = UBound(varSalesArray, 2) + 1     'Determine total dispatch delay     lngTotalDelay = 0     For i = 0 To lngTotalRecords - 1     lngTotalDelay = lngTotalDelay + varSalesArray(0, i)     Next     'Determine mean payment delay     dblMeanDelay = lngTotalDelay / lngTotalRecords     'Determine minimum and maximum delays     intMinDelay = varSalesArray(0, 0)     intMaxDelay = varSalesArray(0, lngTotalRecords - 1)     End Sub   
  4. Now we need to put in the code that will return values from the three properties:

       Public Property Get MeanDelay() As Double     MeanDelay = dblMeanDelay     End Property     Public Property Get MinimumDelay() As Integer     MinimumDelay = intMinDelay     End Property     Public Property Get MaximumDelay() As Integer     MaximumDelay = intMaxDelay     End Property   
  5. Finally, we need to implement the logic to return values from the Percentile and FinePayable methods. To do this, modify these two procedures so that they look like this:

       Public Function Percentile(Optional Days As Integer = 40) As Single     Dim i As Integer     If VarType(varSalesArray) And vbArray Then     Percentile = 100     For i = 0 To lngTotalRecords - 1     If (varSalesArray(0, i)) > Days Then     Percentile = 100 * i / lngTotalRecords     Exit Function     End If     Next     End If     End Function     Public Function FinePayable(Optional Days As Integer = 40, _     Optional Percent As Single = 90, _     Optional UnitFine As Currency = 1000) As Currency     Dim i As Integer     Dim sngPercentActual As Single     If VarType(varSalesArray) And vbArray Then     sngPercentActual = Percentile(Days)     If sngPercentActual < Percent Then     FinePayable = (Percent - sngPercentActual) * UnitFine     End If     End If     End Function   

That is the class completed! All that remains is to test it out by re-running the ShowPaymentStats procedure and inspecting the results for December 2002. You can do this by typing ShowPaymentStats 12, 2002 in the Immediate window. You should see the following results:

click to expand

How It Works

Now there is quite a lot of material to cover here, but most of it uses techniques that we have already encountered .

 Private varSalesArray As Variant Private lngTotalRecords As Long Private lngTotalDelay As Long Private dblMeanDelay As Double Private intMinDelay As Integer Private intMaxDelay As Integer Private sngPercentile As Single 

First up, we declare the variables that we will be using within this class. Note that these are all declared as private. That means that the variables can only be viewed within the class module and are not viewable from outside it, in other words we've encapsulated the data storage. Remember, we don't want to expose any of the details of the implementation; all we want to expose is the interface we defined earlier.

The next step is to build the LoadData method for loading the sales data into our object. We do this by creating a Recordset object, which extracts the payment delay for all orders that were placed in the month specified by the programmer. (For purposes of simplicity we have chosen to exclude orders that have not been paid for yet).

 strSQL = "SELECT DatePaid - DateOrdered AS PaymentDelay " & _     "FROM tblSales " & _     "WHERE Month(DateOrdered) = " & Month & " " & _     "AND Year(DateOrdered) = " & Year & " " & _     "AND Not IsNull(DatePaid) " & _     "ORDER BY DatePaid - DateOrdered" 

Note that we are retrieving the records ordered in such a way that the orders with the smallest payment delay are retrieved first and the orders with the greatest payment delay last.

We retrieve the records using a read-only cursor. However, we have to be alive to the possibility that there might be no records for the particular month that we have selected. That is why we test the Recordset object's RecordCount property before we attempt to extract the records into a variable.

 Set rec = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) If rec.RecordCount Then   rec.MoveLast   rec.MoveFirst   varSalesAray = rec.GetRows(rec.RecordCount) End If 

If you can remember that far back, we saw in Chapter 5 that - even though not all of the records might have been returned immediately - the RecordCount will always give you an indication of whether at least one record has been returned. That is because, when the OpenRecordset method is invoked, VBA will always wait until at least the first record in a non-empty recordset is returned before it passes control to the next line of code. So, the RecordCount property of the query will only be if there will definitely be no records in the recordset.

If the query does return records, we need to retrieve them into a variable. However for this operation, we need to make sure that all of the records have finished being retrieved and the easiest way to ensure this is to use the MoveLast method of the Recordset object. Now that is all well and good, but the GetRows method - which is what we will use to copy the records from the Recordset object into the variable - copies rows from the current record onwards. That is why we then need to invoke the MoveFirst method to move back to the beginning of the Recordset object before we invoke the GetRows method.

So, by this stage we have retrieved details of the payment delays for orders placed in the specified month and we have copied them into the variant varSalesArray . Now that we have done that we can perform some the calculations in the CalcStats procedure.

 If VarType(varSalesArray) And vbArray Then   CalcStats End If 

Of course, we only want to perform these calculations if we were successful in retrieving any records. That is why we use the VarType function to determine what type of variable varSalesArray is. The varSalesArray variable was originally declared as a variant and would have had a VarType of - the default empty data type for uninitialized variables. However, once a value is assigned to varSalesArray , the VarType changes to match the data type of the value held by the variable. Now we saw in Chapter 9 that when an array is placed in a variant variable, the number representing the variable's VarType is incremented by 8192 , which is represented by the intrinsic constant vbArray . So, the expression VarType(varSalesArray) And vbArray will return ( False ) if varSalesArray does not contain an array and it will contain a non-zero value if it does contain an array.

For more information on using logical operators with the VarType function, have a look back at Chapter 9. If you are still not sure how this works, don't worry. You can achieve a similar effect by replacing the expression VarType(varSalesArray) And vbArray with VarType(varSalesArray) > = vbArray .

So, if varSalesArray contains an array, we run the CalcStats procedure. This is a fairly straightforward procedure.

 lngTotalRecords = UBound(varSalesArray, 2) + 1 

The first step is to determine the total number of records that we have copied into our varSalesArray array. The GetRows method creates a two dimensional array, the first dimension of which represents the number of fields in the original Recordset object and the second represents the number of rows. The array created by GetRows is zero-based , so although the expression UBound(varSalesArray,2) will return the index of the last element of the array in the dimension representing the rows, we need to add 1 to this to determine the number of elements and therefore the number of rows returned.

Having determined the total number of records, we then loop through the array to determine the total of all of the payment delays added together.

 lngTotalDelay = 0 For i = 0 To lngTotalRecords - 1   lngTotalDelay = lngTotalDelay + varSalesArray(0, i) Next 

This value is stored in the variable lngTotalDelay . It is then very easy to determine the average payment delay:

 dblMeanDelay = lngTotalDelay / lngTotalRecords 

The next step is to determine the minimum and maximum payment delays.

 intMinDelay = varSalesArray(0, 0) intMaxDelay = varSalesArray (0, lngTotalRecords - 1) 

These values will be located in the first and last elements of the array because our query returned the records ordered by the magnitude of the payment delay.

So, let's take stock of where we are at the moment. If the LoadData method is invoked, the payment delays for the specified period are returned and the values loaded into an array. From this we determine the mean delay, the minimum delay, and the average delay. These are calculated in the CalcStats function (called by the LoadData method after the data has been loaded into the object) and are stored in three private variables ( dblMeanDelay , intMinDelay, and intMaxDelay ). If we want to expose these to the outside world, we need to return them as the values of the relevant properties of the PaymentStats object.

 Public Property Get MeanDelay() As Double   MeanDelay = dblMeanDelay End Property Public Property Get MinimumDelay() As Integer   MinimumDelay = intMinDelay End Property Public Property Get MaximumDelay() As Integer   MaximumDelay = intMaxDelay End Property 

Now that is most of the functionality of the class implemented. All we need to do now is to return the percentage of payments made within a certain number of days and any fine that is due. The Percentile method returns the percentage of payments made and relies on the fact that our array is ordered by the magnitude of payment delay.

Because this calculation is carried out every time this method is invoked, we need to check again that the varSalesArray variable contains an array.

 If VarType(varSalesArray) And vbArray Then . . . End If 

If it does, then we loop through the array until we find the first element with a payment delay greater than the payment delay specified as an argument to this method:

 For i = 0 To lngTotalRecords - 1   If (varSalesArray(0, i)) > Days Then . . .   End If Next 

As soon as we find one, we know that this and all future elements will have a payment delay greater than the one specified. So we can say that the percentage of elements with a payment delay of less than or equal to the specified payment delay is 100 times the current index of the array ( i ) divided by the total number of elements in the array ( lngTotalRecords ).

 Percentile = 100 * i / lngTotalRecords 

Of course, if a payment delay is specified that is greater than all of those in the array, this line of code will never be reached. That is why we started by initializing the value of Percentile to 100 (meaning 100%).

 Percentile = 100 

The final stage is to calculate the fine payable. To do this, we must again check that there are elements in the array. After all, there is nothing to stop someone invoking the FinePayable method prior to invoking the LoadData method.

Then we find the percentage of payments made within the specified period represented by the argument Days (which defaults to 40 days):

 sngPercentActual = Percentile(Days) 

Finally we take the difference between the percentage specified by the user (which defaults to 90) and the percentage of payments actually made. If fewer payments than the specified percentage have been made then we multiply the difference by the UnitFine (which defaults to $1000).

 If sngPercentActual < Percent Then   FinePayable = (Percent - sngPercentActual) * UnitFine End If 

That is the class finished. So we test it out by loading it with data and inspecting its properties. If you have typed all of our code in correctly, you should see the right results!

click to expand

You should be able to see that we could use the same class to give us a listing of all of the fines payable by our credit collection agency in 2002, simply by running the following procedure:

   Sub ShowFines(intYear As Integer)     Dim i As Integer     Dim objStats As New PaymentStats     For i = 1 To 12     objStats.LoadData i, intYear     Debug.Print MonthName(i, True) & _     " Fine Payable: "; FormatCurrency(objStats.FinePayable, 2, , , vbTrue), _     objStats.Percentile(40)     Next     End Sub   

If you run this in the Immediate window, you should see these results:

click to expand

Finishing Touches

So far, we have seen how to create objects with custom methods by using either public functions or public subprocedures. Public subprocedures are used for methods that simply perform an action (for example PaymentStats.LoadData ), whereas public functions are used for methods that return a value, such as PaymentStats.FinePayable .

We have also looked at how to expose properties by using Property Get and Property Let procedures. We will use that knowledge to implement an Accuracy property that will allow us to specify the number of decimal places that will be used when returning values from the PaymentStats object.

Try It Out-Adding a Writeable Property
  1. Add the following variable declaration to the Declarations section of the PaymentStats object we just created.

     Private intMaxDelay As Integer Private sngPercentile As Single   Private intAccuracy As Integer   
  2. Now add the following procedure, which will allow us to inspect the value of the Accuracy property

       Public Property Get Accuracy() As Integer     Accuracy = intAccuracy     End Property   
  3. Next we will add a procedure to allow us to assign a value to the Accuracy property of the PaymentStats object.

       Public Property Let Accuracy(DecimalPlaces As Integer)     If DecimalPlaces < 0 Or DecimalPlaces > 9 Then DecimalPlaces = 3     intAccuracy = DecimalPlaces     End Property   
  4. The next step is to use the Accuracy property to modify the way that properties are returned. So we will modify the line in the Percentile property that returns the value so that it now looks like this:

     If (varSalesArray(0, i)) > Days Then   Percentile = Round(100 * i / lngTotalRecords, intAccuracy)   Exit Function 
  5. Then modify the MeanDelay property so that it looks like this:

     Public Property Get MeanDelay() As Integer   MeanDelay = Round(dblMeanDelay, intAccuracy)   End Property 
  6. Save the changes to the PaymentStats object and switch to the Chapter 13 Code module.

  7. Finally, modify the ShowPaymentStats subprocedure to include a line to specify the number of decimal places that will be used in returning values from the PaymentStats object.

     Sub ShowPaymentStats(intMonth As Integer, _            intYear As Integer, _   Optional intDecimalPlaces As Integer)   Dim objPayStats As PaymentStats Set objPayStats = New PaymentStats objPayStats.Accuracy = intDecimalPlaces objPayStats.LoadData intMonth, intYear Debug.Print "  Min Delay: "; objPayStats.MinimumDelay Debug.Print "  Max Delay: "; objPayStats.MaximumDelay Debug.Print " Mean Delay: "; objPayStats.MeanDelay Debug.Print " 40 day %ile: "; objPayStats.Percentile Debug.Print "Fine Payable: "; objPayStats.FinePayable End Sub 
  8. Now, when you run the ShowPaymentStats procedure in the Immediate window, it should return the MeanDelay property and Percentile method to the specified number of decimal places.

    click to expand

You may find it odd that the accuracy of the fine payable is to 4 decimal places rather than 2 - after all it is a currency value we wish to display. We've left that for you to change as an exercise at the end of this chapter.

How It Works

A value is passed in via the DecimalPlaces argument and is stored in the intAccuracy variable.

 intAccuracy = DecimalPlaces 

It is later used as an argument to the Round function when modifying the result of the MeanDelay property procedure and the Percentile method.

Because the FinePayable method uses the Percentile method in its calculations, the value it returns is also affected by the use of the Accuracy property.

The only thing we need to check is that an appropriate value is passed in as the Accuracy argument. If the value is too high or too low, we simply choose to use 3 decimal places instead.

 If DecimalPlaces < 0 Or DecimalPlaces > 9 Then DecimalPlaces = 3 

Once the Accuracy argument has been added to the interface of the PaymentStats object, we can use it in the ShowPaymentStats procedure.

 objPayStats.Accuracy = intAccuracy 

If we then want to inspect the value of intAccuracy , we can do so through the Public Property Get Accuracy() procedure, which makes the Accuracy property readable.

Of course, now that we are using the private intAccuracy variable - exposed as the Accuracy property - to regulate the number of decimal places in answers returned by the MeanDelay property and Percentile method, we need to consider what will happen if we do not assign a value to this property. For example, if we ran the ShowPaymentStats procedure without assigning a value to the Accuracy property, we would get these results:

   Min Delay: 30     Max Delay: 43     Mean Delay: 36     40 day %ile: 86     Fine Payable: 4000   

That is because the intAccuracy variable, like all integer variables, is initialized to if no one explicitly assigns a value to it. So, by exposing the Accuracy property, we have made the default accuracy for the PaymentStats to be zero decimal places. We will look in a few moments at how we can keep the Accuracy property readable and writeable, but allow it to default to a different value.

Benefits of an Object-Oriented Approach

At this stage it is worth looking at the benefits that this approach offers the developer when compared to alternative techniques. If we had not decided to use an object-oriented approach to solving this problem, how could we have done it?

The most likely approach is that we would have used a series of functions, so that our code would have looked something like this:

 Debug.Print "  Min Delay: "; GetMinimumDelay(intYear, intMonth) Debug.Print "  Max Delay: "; GetMaximumDelay(intYear, intMonth) Debug.Print " Mean Delay: "; GetMeanDelay(intYear, intMonth) Debug.Print " 40 day %ile: "; GetPercentile(intYear, intMonth) Debug.Print "Fine Payable: "; GetFinePayable(intYear, intMonth) 

In this case, each of the five functions would accept a year and month as an argument and return the requested statistic relating to the sales data for that period. Each of these five functions would need to fetch the required subset of data from the sales table and analyze it to determine the correct value of the required statistic. That's five potentially expensive queries to be executed compared to the single one required by our object-based approach.

If we had wanted to minimize the number of times we fetched data from the database, we could incur the one database hit up front like this:

 Dim varMonthlyDataArray As Variant varMonthlyDataArray = GetMonthlyData(intYear, intMonth) Debug.Print "  Min Delay: "; GetMinimumDelay(varMonthlyDataArray) Debug.Print "  Max Delay: "; GetMaximumDelay(varMonthlyDataArray) Debug.Print " Mean Delay: "; GetMeanDelay(varMonthlyDataArray) Debug.Print " 40 day %ile: "; GetPercentile(varMonthlyDataArray) Debug.Print "Fine Payable: "; GetFinePayable(varMonthlyDataArray) 

In this situation, the initial GetMonthlyData function would return an array containing the month's sales data and the five subsequent functions would each return the required statistic from that data. Although more efficient from a data access point of view, there is still a major drawback compared to the object-oriented approach.

The drawback is this: if we want to use this functionality in another database, with the procedural approach shown above, we have to copy all six procedures into our new database; with the object-based approach we only have to copy one class module. Because the class encapsulates within itself everything it needs in order to function properly, it provides a significantly more manageable way of building applications than the procedural approach. Now, the PaymentStats class is only a very simple business object and the benefits that this encapsulation offers over the procedural approach, although noticeable, are not necessarily compelling. But the more complex the object becomes (and, therefore, the more discrete procedures that can be replaced by a single object) the more convincing the argument for an object-oriented approach becomes.

Another key benefit of the object-oriented approach is the way that it makes programming more intuitive in the VBA programming environment. The ability of VBA to expose an object's properties and methods to a developer via the Auto List Members feature of IntelliSense (those funny pop-up thingies) means that the object-oriented approach is likely to yield fewer design-time errors and so lead to faster development than the procedural approach. Also, the more complex the object becomes, the more noticeable will be the benefits that this approach offers over the traditional procedural approach.

Using class modules will inevitably involve a slight development overhead compared to the traditional procedural approach, especially if you are new to the concepts of object orientation. For very simple processes, this overhead may not be worth entertaining, but for more complex processes the use of an object-oriented approach will make subsequent programming more intuitive, will increase the possibilities for code reuse and will make code maintenance significantly easier. How can you say no to that?

Differences between Classes and Modules

We have seen so far that class modules resemble standard modules, in that they contain a Declarations section and can contain Public and Private procedures. In class modules, Private procedures are used to construct the implementation of the class and Public procedures are used to expose methods in the interface of the class.

Unlike standard code modules, however, class modules can contain Public Property procedures, which are used to expose properties in the interface of the class. Public Property Get procedures are used to make properties readable and Public Property Let procedures are used to make properties writeable.

In fact, there is also a third type of Public Property procedure, the Public Property Set procedure, which is used to make properties writeable in situations where the property returns a reference to an object.

However, there is another, more fundamental way in which standard modules and class modules differ . Standard modules are in scope for the duration of the VBA project to which they belong. That means that if a variable, constant, or procedure is declared with Public visibility in a standard code module, then that variable, constant, or procedure will remain publicly visible for the whole of the time that the Access database in which that code module is located remains open. Standard code modules do not need to be explicitly loaded or instantiated; they are always there and always accessible.

By way of contrast, class modules provide templates for objects, rather than being objects in their own right. That means that we need to explicitly create an instance of an object based on a class before we can access any of the public procedures (methods) or properties in the object's interface.

The process of creating a new instance of an object from a class is called instantiation, and we have already seen this in action several times already. For example, we created an instance of the PaymentStats class. First, we declared a variable to hold a reference to the new PaymentStats object once it was created:

 Dim objPayStats As PaymentStats 

Then we actually created an instance of the object and place a reference to it in the objPayStats variable:

 Set objPayStats = New PaymentStats 

It is worth remembering that it is the second of these lines that actually causes the object to come into existence. You might sometimes see this alternative method of instantiating objects being used:

   Dim objPayStats As New PaymentStats   

However, it is recommended that you avoid using this technique for a number of reasons. When you use the Dim...As   New... syntax, VBA creates a variable to hold a reference to the new object, but it does not actually create an instance of the object until the object is next referenced in code. In fact, when you use this syntax, every time that you subsequently refer to the objPayStats variable, VBA checks to see whether the object has been instantiated. If it has, VBA uses the existing object; if not, VBA creates a new instance. The overhead of checking for the existence of this object every time it is referenced means that this method is noticeably slower than specifically instantiating the object straight away using the Set...= New... syntax.

The other disadvantage of the Dim... As New... syntax is that it is sometimes difficult to keep track of when the object is actually instantiated as this only happens the next time that the object is referenced after the Dim... As New... statement.

The Class Initialize and Terminate Events

The more perceptive of you will have noticed that class modules contain a couple of events that do not appear in standard code modules. These are the Initialize and the Terminate events of the Class object. You can see these if you look in the object and procedure combo boxes for the code window of a class module.

click to expand

The Class_Initialize event fires whenever a class object is instantiated and the Class_Terminate event is fired whenever the object is destroyed .

Important 

Objects can be destroyed either explicitly (by setting the object variable to Nothing ) or implicitly when the variable containing the object goes out of scope. Bear in mind that if an object has several variables all containing a reference to it, th e object will only be destroyed when all of the variables containing a reference to it go out of scope. We will examine this in more detail later when we look at how to create multiple instances of a form .

A frequent use of the Class_Initialize event is to initialize the value of variables within the new object. For example, we could use the Class_Initialize event of the PaymentStats object to ensure that the initial value of the Accuracy property is something other than . To do this, we would add the following code to the Class_Initialize event of the PaymentStats object.

   Private Sub Class_Initialize()     intAccuracy = 3     End Sub   

Now, whenever a new instance of the PaymentStats object is created, a value of 3 is instantly assigned to the Private variable intAccuracy . This is exposed as the Accuracy property, and is used to limit the number of decimal places that will be used when returning values via the MeanDelay property and the Percentile method.

Access's trash collector is notoriously unreliable and there are developers who always close any objects that have a close method and set their pointers to nothing. If your class has such variables and you want to do your own cleanup, the Class_Terminate event is a good place to do so since it will fire if the class is destroyed, allowing you to ensure that objects that you have pointers to will be cleaned up.

 
team lib


Beginning Access 2002 VBA
Beginning Access 2002 VBA (Programmer to Programmer)
ISBN: 0764544020
EAN: 2147483647
Year: 2003
Pages: 256

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net