Adding Functionality with Classes in Microsoft VBA

• 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:

• PageName

• SubFormPageName

• RelatedPageName

• CanBeUnloaded

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

Private Sub Class_Initialize()
    Set p_Object = New ObjectType
End Sub
Private Sub Class_Terminate()
    Set p_Object = 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.

Public Function AllItems() As Collection
    Set AllItems = p_TabPages
End Function

' works with allitems
For Each aTab In TabPages.AllItems
    Debug.Print aTab.PageName, aTab.SubFormPageName, _
        aTab.RelatedPageName, aTab.CanBeUnloaded
Next
Set aTab = TabPages.AllItems("ProductList")
Debug.Print aTab.PageName
Debug.Print TabPages.AllItems("ProductList").PageName

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.