Adding Functionality with Classes in Microsoft VBA
- By Andrew Couch
Improving the Dynamic Tab Control
Creating a Hierarchy of Classes
You have seen in earlier chapters how Microsoft VBA program code is either contained in a module or held in a form’s class module. In this chapter, you look at how VBA also allows you to construct your own class modules.
It is often overlooked that VBA supports Object-Oriented Programming (OOP), so in this chapter, we introduce you to OOP concepts by having you construct your own classes. Many Microsoft Access developers take a look at classes and then give up because they have difficulty seeing the benefit and justification for using classes. It’s true that much of what can be achieved with a simple class can also be achieved by using libraries of code, and that to build classes you often need to put in more effort during the initial development, but there are benefits in using classes that will be explored in this chapter as well as in Chapter 10, “Using Classes and Events,” and Chapter 11, “Using Classes and Forms.”
This chapter focuses on two examples of classes, and uses each example to introduce the techniques for creating your own classes.
The first example involves applying classes to solve a problem of designing a dynamic Tab control that saw in Chapter 7, “Using Form Controls and Events.” This example will demonstrate how classes can be used to improve the design of a general purpose tool that can be re-used in your applications.
The second example looks at how to build classes to handle data for a specific business problem.
After reading this chapter, you will:
Understand how to create class modules.
Know how to use Let, Get, Set, and New with classes.
Be able to create collection classes.
Be able to create base and derived classes.
Be able to create a hierarchy of classes.
The object-oriented view to developing software became popular in the 1980s, and in addition to OOP, many terms such as Object-Oriented Design (OOD) and Object-Oriented Analysis (OOA) became increasingly popular.
You have already seen many examples of working with objects in Access. These objects have properties that describe the object, and methods that cause an object to perform an operation. Access maintains collections of like objects; for example, the Forms collection, which contains Form objects that open on the desktop, and the TableDefs collection in the Data Access Object (DAO) model, which contains all the TableDef objects. These are examples of working with objects, but not examples of OOP.
OOP Programming (which is supported in VBA) means taking these ideas of working with objects and extending this concept to guide how program code is written.
Classes can be applied in several different ways in Access to:
Improve the quality of code (OOP can help you develop more maintainable code).
Extend form/report behavior (OOP allows you to take control of the underlying behavior of Access objects and wrap or extend the behavior).
Integrate External Components (some external components do not expose all their functionality and OOP features can help with this).
Improving the Dynamic Tab Control
In Chapter 7, you saw how to design a dynamic Tab control form that can load and unload pages by using an array of Types, where each item in the array corresponds to a form that is loaded into a subform control. The type structure for that is as follows:
Private Type PageInfo strPageName As String strPageSubForm As String strRelatedPage As String blCanBeLoaded As Boolean End Type Dim AvailablePages() As PageInfo
As an alternative to using a Type, you will define these pages as objects with properties that correspond to each part of the Type structure, and then you will build a collection to hold these objects, which replaces the array that held the types.
We need the following properties for our object:
You might have noticed that we have renamed the CanBeLoaded property in the preceding list to CanBeUnloaded. This is because an object-oriented perspective helps you to think in terms of how an object’s state can be changed, so this is a more appropriate term to use. With the object’s basic properties determined, you can now proceed to create the object class.
Creating a Class Module
To begin, in the Project pane, you create a new class module, as shown in Figure 9-1.
Figure 9-1 Use the Project pane to create a new class module.
With this file created, you then save it using an appropriate class name; for this example, use clsTabPage. Because you are now working in a class module, you do not need to explicitly define that you are creating a class (as you would need to do in Microsoft .NET). Next, you define the object’s internal variables at the top of the class module code, as illustrated in the following:
Option Compare Database Option Explicit ' These could be declared as either Dim or Private ' as within a class their scope is restricted Dim p_PageName As String Dim p_SubFormPageName As String Dim p_RelatedPageName As String Dim p_CanBeUnloaded As Boolean
Note that these variables include the prefix “p_” to indicate that they are private variables to each class object (other popular prefixes include “m” or “m_”). The next step is to provide the user with a way of reading and writing these variable values.
The Let and Get Object Properties
After you define the object’s internal variables or attributes for your class, you need to create a mechanism to read or write these values. To do this, you define properties. On the Insert menu, click Procedure to open the Add Procedure dialog box, as shown in Figure 9-2.
Figure 9-2 Use the Add Procedure dialog box to create a new private or public property.
Ensure that you are not clicked inside any other property when you insert a new property; otherwise, it will fail to add the property correctly to the class. The code that is created needs appropriate data types to be specified for the return type of the property and the parameter type passed to the property.
As shown in the code that follows, you use the Get statement to read an object property from the internal private variable, and the Let statement to assign a value to the internal private variable. An object can have a number of internal variables, but you might only need to make a few of these available to the user. The idea is to keep the object’s external interface very simple, exposing only the minimum number of essential features that a user will need. It is up to you to decide for which properties you want both a Let and Get, depending on whether the property is to be read-only (Get but no Let) or write-only (Let but no Get):
Public Property Get PageName() As String PageName = p_PageName End Property Public Property Let PageName(ByVal PageName As String) p_PageName = PageName End Property Public Property Get RelatedPageName() As String RelatedPageName = p_RelatedPageName End Property Public Property Let RelatedPageName(ByVal RelatedPageName As String) p_RelatedPageName = RelatedPageName End Property Public Property Get CanBeUnloaded() As Boolean CanBeUnloaded = p_CanBeUnloaded End Property Public Property Let CanBeUnloaded(ByVal CanBeUnloaded As Boolean) p_CanBeUnloaded = CanBeUnloaded End Property Public Property Get SubFormPageName() As String SubFormPageName = p_SubFormPageName End Property Public Property Let SubFormPageName(ByVal SubFormPageName As String) p_SubFormPageName = SubFormPageName End Property
Creating an Object with New and Set
To test your new class, you create a module (not a class module) to verify that you can create an object. If you insert a breakpoint and trace through the code execution, you will learn a great deal, as you can trace through the codes execution into the class module code.
You can define the object variable and then later create an object with the New keyword, or as is also shown demonstrated in the following code, with the aTab2 object, you can both define and create the object at the same time. It is largely a matter of personal preference as to which method you choose to use.
Once you have finished with the object, set the object variable to Nothing; this destroys the object. The object would be destroyed anyhow when the code stops execution, but explicitly tidying up your objects is good practice and becomes more important when you work with more complex objects:
Sub modTabs_TestObject() ' test creating an object Dim aTab As clsTabPage Set aTab = New clsTabPage aTab.PageName = "ProductList" aTab.RelatedPageName = "Product Details" aTab.SubFormPageName = "frmTabsDynamicProductList" aTab.CanBeUnloaded = False Debug.Print aTab.PageName Set aTab = Nothing Dim aTab2 As New clsTabPage aTab2.PageName = "Product Details" Debug.Print aTab2.PageName Set aTab2 = Nothing End Sub
Collection of Objects
A VBA collection is a set of objects that you can use in a similar manner as the built-in collections, such as the Forms collection that you worked with in earlier chapters.
The example that follows defines a collection that is used to hold our Tab page objects:
Sub modTabs_Collection() ' test creating an object Dim TabPages As New Collection Dim aTab As clsTabPage Set aTab = New clsTabPage aTab.PageName = "ProductList" aTab.RelatedPageName = "Product Details" aTab.SubFormPageName = "frmTabsDynamicProductList" aTab.CanBeUnloaded = False TabPages.Add aTab, aTab.PageName Set aTab = Nothing Set aTab = New clsTabPage aTab.PageName = "Product Details" aTab.RelatedPageName = "" aTab.SubFormPageName = "frmTabsDynamicProductDetails" aTab.CanBeUnloaded = True TabPages.Add aTab, aTab.PageName Set aTab = Nothing For Each aTab In TabPages Debug.Print aTab.PageName, aTab.SubFormPageName, _ aTab.RelatedPageName, aTab.CanBeUnloaded Next Debug.Print TabPages.Count Stop Set aTab = TabPages("ProductList") Debug.Print aTab.PageName Debug.Print TabPages("Product Details").PageName ' note 1 based collection unlike built in collections Debug.Print TabPages(1).PageName Set TabPages = Nothing Set aTab = Nothing End Sub
Notice how the aTab variable is used several times to create objects, and how setting it to Nothing does not destroy the object. This is because once you have created an object, you add it to the collection, which is then responsible for managing the object (when the collection is set to Nothing, it will destroy the objects it contains.
When you add an object to a collection, you must also specify a collection key value (which must be unique). Doing this means that rather than referring to a collection object as TabPages(1), you can use the key and refer to this as TabPages(“Product List”). The Collection object’s Add method also allows you to specify an optional Before or After argument for positioning an object relative to other objects in the collection. The collections first element is 1 and not 0 (which is what the built-in Access collections use).
Be aware that when you refer to an object by using TabPages(1).PageName, you cannot take advantage of IntelliSense assistance. This is because this type of collection can hold different types of objects, so the environment cannot know exactly which properties would apply to an object.
Once you have added an object to a collection and specified the key value, you will find that you cannot subsequently display the key value—it is hidden. If your procedures need to be able to refer to the key, you might find it useful to add your own property to the object class, which saves and holds the key value in each object. Looking in the class clsTabPage, you see the following (it is not essential to do this in the class):
Dim p_Key As String Public Property Get Key() As String Key = p_Key End Property Public Property Let PageName(ByVal PageName As String) p_PageName = PageName p_Key = PageName End Property
Creating Collection Classes
A VBA Collection object supports a limited number of operations—Add, Count, and Remove. You will likely want to be able to add more operations to your collection. To do that, you need to define your own collection class, called clsTabPageCollection.
Defining a collection class follows the same steps as defining a normal class to create the class module. Your collection class will contain a VBA collection, so you define an internal variable called p_TabPages. As we previously described, classes can have two specially named methods for initializing and terminating the class. The simple clsTabPage didn’t need any special operations, but the new class needs to create a VBA collection, and then remove all the objects from the collection when it is terminated, as illustrated in the following code:
Private p_TabPages As Collection Private Sub Class_Initialize() Set p_TabPages = New Collection End Sub Private Sub Class_Terminate() Dim aClassPage As clsTabPage For Each aClassPage In p_TabPages p_TabPages.Remove CStr(aClassPage.PageName) Next Set p_TabPages = Nothing End Sub
You also want to have the standard operations for counting, adding, and removing items from the class, so you need to add these methods to our collection (you also add an Item method, which is another standard feature of a class):
Public Property Get Count() As Long Count = p_TabPages.Count End Property Public Sub Add(aClassPage As clsTabPage) p_TabPages.Add aClassPage, aClassPage.PageName End Sub Public Sub Remove(PageName As Variant) p_TabPages.Remove CStr(PageName) End Sub Public Function Item(PageName As Variant) As clsTabPage Set Item = p_TabPages(PageName) End Function
Once you start defining your own collection class, you will find that a number of the expected built-in collection class features no longer work. For example, you cannot use a For Each loop, or index the collection by using the friendly key name (you will see how to get around this). The following procedure can be used to test the class; the program lines that are commented out have been included to show what will not work in our collection class:
Sub modTabs_clsTabPageCollection() ' test creating an object Dim TabPages As New clsTabPageCollection Dim aTab As clsTabPage Dim lngCount As Long Set aTab = New clsTabPage aTab.PageName = "ProductList" aTab.RelatedPageName = "Product Details" aTab.SubFormPageName = "frmTabsDynamicProductList" aTab.CanBeUnloaded = False TabPages.Add aTab Set aTab = Nothing Set aTab = New clsTabPage aTab.PageName = "Product Details" aTab.RelatedPageName = "" aTab.SubFormPageName = "frmTabsDynamicProductDetails" aTab.CanBeUnloaded = True TabPages.Add aTab Set aTab = Nothing ' For Each aTab In TabPages ' Debug.Print aTab.PageName, aTab.SubFormPageName, _ ' aTab.RelatedPageName, aTab.CanBeUnloaded ' Next For lngCount = 1 To TabPages.Count Set aTab = TabPages.Item(lngCount) Debug.Print aTab.PageName, aTab.SubFormPageName, _ aTab.RelatedPageName, aTab.CanBeUnloaded Next Set aTab = Nothing ' Set aTab = TabPages("ProductList") ' following will work Set aTab = TabPages.Item(1) Debug.Print TabPages.Item(1).PageName Debug.Print aTab.PageName Set aTab = Nothing Set TabPages = Nothing End Sub
There are two techniques available to get around the problem of not being able to refer to the collection class by using the key names. The first technique involves adding an AllItems function to the collection class, and the second method involves exporting, editing, and re-importing the class.
Exporting and Re-importing the Class
The reason that you cannot refer to collections by using standard syntax is because VBA classes do not allow special attributes to be set on a class, and these are required to support standard syntax.
If you right-click the collection class module in the project window, export it to a text file, and then open the text file in notepad, you will see the following header information in the class:
VERSION 1.0 CLASS BEGIN MultiUse = -1 'True END Attribute VB_Name = "clsTabPageCollection" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = False Attribute VB_Exposed = False Option Compare Database Option Explicit ' class clsTabPagesCollection Private p_TabPages As Collection
These attributes are not exposed in the VBA environment. There is a special attribute value, which when set to 0, sets the member as the default member for the object. You want the Item method to be the default member and you need to change the method adding the following attribute definition (this will enable references such as TabPages(“ProductList”) to work). Also, to support enumeration in a For ... Each loop, you need to add the NewEnum method, as shown in the following:
Public Function Item(ByVal Index As Variant) As clsTabPage Attribute Item.VB_UserMemId = 0 Set Item = p_TabPages(Index) End Function Public Function NewEnum() As IUnknown Attribute NewEnum.VB_UserMemId = -4 Set NewEnum = p_TabPages.[_NewEnum] End Function
After saving these changes, import the class back into your project, as shown in Figure 9-3.
Figure 9-3 Re-importing a class back into Access.
If you look in the VBA Editor, you will not be able to see the new attribute you just added in the Item method because it remains hidden.
This then means that the following references will work (note that in the sample database BuildingClassesAfterExportImport.accdb, the following code will work, because we have performed this rather complex operation; in the sample database BuildingClasses.accdb, this code has been commented out because it will not work):
For Each aTab In TabPages Debug.Print aTab.PageName, aTab.SubFormPageName, _ aTab.RelatedPageName, aTab.CanBeUnloaded Next Set aTab = TabPages("ProductList") Debug.Print TabPages("ProductList").PageName Debug.Print aTab.PageName
This process needs to be repeated for each collection class in your project.
Using Classes with the Dynamic Tab
You are now able to modify the code in the frmTabsDynamic form to make use of your new classes.
At the top of the module, where you had defined an array of types, declare your collection class as shown here:
Option Compare Database Option Explicit Dim TabPages As clsTabPageCollection Dim lngTabPages As Long
The form’s Open and Close events then create and dispose of the collection, as shown in the following:
Private Sub Form_Close() Set TabPages = Nothing End Sub Private Sub Form_Open(Cancel As Integer) Set TabPages = New clsTabPageCollection LoadTabs End Sub
In the following code, in the LoadTabs procedure, you create and load your class objects into the collection:
Do While Not rst.EOF Set aTabPage = New clsTabPage aTabPage.PageName = rst!PageName aTabPage.SubFormPageName = rst!SubFormName aTabPage.CanBeUnloaded = rst!CanUnloadPage aTabPage.RelatedPageName = Nz(rst!RelatedPage) TabPages.Add aTabPage Set aTabPage = Nothing If rst!DefaultVisible And lngPageVisibleCount + 1 < lngTabPages Then LoadThePage aTabPage, lngPageVisibleCount lngPageVisibleCount = lngPageVisibleCount + 1 End If lngArray = lngArray + 1 rst.MoveNext Loop
There are some other minor references in the code that used the array of types that now need to be changed to use the new collection and objects.
Simplifying the Application with Classes
In the preceding sections, you have been able to change your dynamic tab to use classes, but it has not as yet resulted in any simplification of the applications code. In fact, you now have more code to maintain than when you started. But you now have a framework in which you can start to work that will lead to simplification and improved maintenance of your code.
In examining the frmTabsDynamic form, you can see that it has a general routine LoadTabs that involves reading information and placing the information into your collection. This operation could be placed inside the collection. So we can start to enhance our collection (clsTabPageCollection2) by adding the data loading function. But the process of loading the information also involves setting values in controls on the form. This means you also want to allow the collection to reference the controls on the form.
To begin, add new private members to the class:
' class clsTabPagesCollection Private p_TabPages As Collection Private p_TabControl As TabControl Private p_Controls As Controls
You must change the termination routine to clear the new variables and provide properties for setting the new variables, as follows:
Private Sub Class_Terminate() Dim aClassPage As clsTabPage For Each aClassPage In p_TabPages p_TabPages.Remove CStr(aClassPage.PageName) Next Set p_TabPages = Nothing Set p_TabControl = Nothing End Sub Public Property Let TabControl(ByRef TabCtl As TabControl) Set p_TabControl = TabCtl End Property Public Property Let Controls(ByRef Ctrls As Controls) Set p_Controls = Ctrls End Property
You can then move the appropriate routines programmed into the form into the collection class.
The result of this is an impressive reduction in the code on the form, which now shrinks to the following (see frmTabsDynamic2):
Option Compare Database Option Explicit Dim TabPages As clsTabPageCollection2 Private Sub Form_Close() Set TabPages = Nothing End Sub Private Sub Form_Open(Cancel As Integer) Set TabPages = New clsTabPageCollection2 TabPages.TabControl = Me.TabCtl0 TabPages.Controls = Me.Controls TabPages.LoadFromTable Me.Name, "tblTabPages" End Sub Private Sub TabCtl0_DblClick(Cancel As Integer) TabPages.TabPageDoubleClick CLng(Me.TabCtl0) End Sub
Although the total amount of code remains unchanged, much of the code has moved out of the form and into the classes. There are a couple of advantages to creating classes to perform these operations:
The code on the form is significantly simplified; it will be easy to add it to other forms or in other applications.
The new classes are easy and intuitive to work with, so using them in the future should improve your applications, and you can add more features to these classes.
Some might argue that rather than using classes, which involves constructing a framework, you could more simply have built a re-useable library. This line of argument nearly always holds; thus, the decision to use classes becomes a question of whether it seems more intuitive and natural than using a traditional code module.