• 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.
MS Access: Modules. A module is a. Quit Access using VBA code in Access 2003; Add. Access a value from a table using VBA code in Access 2003; Determine number.
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. NOTE As you read through this chapter, we encourage you to also use the companion content sample databases, BuildingClasses.accdb and BuildingClassesAfterExportImport.accdb, which can be downloaded from the book’s catalog page. 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 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 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.
INSIDE OUT: Initialization and Termination Events When you are in a class module, you can select Class from the upper-left drop-down menu, which normally shows (General). INSIDE OUT: VBA collection classes The built-in VBA collection classes that you have been working with are different from an Access collection. The first difference is that the Access collections, such as TableDefs, can only hold one type of object; a VBA collection can hold different types of objects (this explains why the IntelliSense is limited). The second difference is that VBA collection classes are 1-based, whereas the Access collections are 0-based.
In the next section, you will be creating your own collection classes that wrap around the VBA collection class. These collections will start to look more like an Access collection. 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. INSIDE OUT: Adding AllItems to a collection class When you use the AllItems method, you need to add the following property to the class (you can give this property an alternative name): Public Function AllItems() As Collection Set AllItems = p_TabPages End Function In the sample testing file, modTabs_clsTabPageCollection2, you can see how to use this feature. The important code is as follows: ' 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 This is a satisfactory solution as long as you are prepared to insert the.AllItems reference when using the collection with the object’s key. 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 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.
NOTE The full code for this can be seen in the sample file. INSIDE OUT: Classes and associated terminology Another term for creating an object is instantiating the class object. This means using the New keyword to create the class object. The term Encapsulation is often used to convey the idea of tucking away all the functionality inside the class, such that the class only exposes as small a public interface as required to fulfill its purpose. With a class, you are wrapping up all the messy code and placing that inside a box so that you don’t need to deal with it on a regular basis.