CHAPTER 11
OBJECT-ORIENTED PROGRAMMING WITH ACCESS VBA
Have you ever wondered how objects, methods, properties, and collections in VBA are created? Well, this chapter shows you how to leverage the power of object-oriented programming (also known as OOP) in Access VBA to create your very own custom objects, methods, properties, and collections!
WORKING WITH COLLECTIONS
Collections are a data structure similar to arrays in that they allow you to refer to a grouping of items as one entity. Collections, however, provide an ordered means for grouping not only strings and numbers, but objects as well. As demonstrated next, the Collection object is used to create a collection data structure.
Dim myCollection As New Collection
Collections are popular data structures in object-oriented programming because they allow the grouping of objects using an ordered name/value pair. In fact, collections are objects themselves!
Items in a collection are referred to as members. All collection objects have one property and three methods for managing members, as described in Table 11.1.
TABLE 11.1 COLLECTION OBJECT PROPERTIES AND METHODS
Adding Members to a Collection
Use the Add method of the Collection object to add members to a collection. The Add method takes four parameters:
object.Add item, key, before, after
• item. A required expression that identifies the member to be added.
• key. An optional expression (string-based) that uniquely identifies the member.
• before. An optional expression that adds the member before the member position identified.
• after. An optional expression that adds the member after the member position identified.
When adding a member to a collection, only the before or after parameter can be used, not both.
The following VBA code creates a new collection and adds three string-based members.
Dim myColors As New Collection myColors.Add “red” myColors.Add “white” myColors.Add “blue”
As mentioned, collections are useful for grouping objects. The next VBA code creates three ADO Recordset objects and adds them to a Collection object.
Dim books As New ADODB.Recordset Dim authors As New ADODB.Recordset Dim publishers As New ADODB.Recordset
Dim myRecordsets As New Collection myRecordsets.Add books myRecordsets.Add authors myRecordsets.Add publishers
By grouping objects in a collection, I can simplify code by accessing all objects through one Collection object.
Removing Members from a Collection
Members are removed from a collection using the Collection object’s Remove method. The Remove method takes a single parameter that identifies the index or key value of the member.
Removing a collection member using both the index value and key value is demonstrated here.
Dim myColors As New Collection myColors.Add “red”, “r” myColors.Add “white”, “w” myColors.Add “blue”, “b” myColors.Remove 1 ’using an index value myColors.Remove “w” ’using a key value
Accessing a Member in a Collection
To access a member in a collection, use the Item method, which takes a single parameter that matches a member’s index or key value.
Dim myColors As New Collection myColors.Add “red”, “r” myColors.Add “white”, “w” myColors.Add “blue”, “b” MsgBox myColors.Item(1) MsgBox myColors.Item(“b”) MsgBox myColors.Item(4) ’Generates an error.
If the index or key value of the member is not found in the collection, an error like that in Figure 11.1 is generated.
FIGURE 11.1 An error is generated when trying to access a member’s key value or index that does not exist.
For Each Loops
VBA provides a looping structure specifically designed for iterating through members in a collection or an array. The For Each loop executes one or more statements inside the block as long as there is at least one member in the collection.
Dim myColors As New Collection
Dim vColor As Variant
myColors.Add “red”, “r”
myColors.Add “white”, “w”
myColors.Add “blue”, “b”
For Each vColor In myColors
MsgBox vColor
Next
Notice the syntax of the For Each statement. The statement basically says, “For every object in the collection, display the member name in a message box.”
The variable used in the For Each statement (called vColor in this section’s example) is always of variant type regardless of the collection’s content. This is an important note because For Each statements cannot be used with an array of user-defined types; variants can’t contain a user-defined type.
The next program code loops through all control names on a form using the built-in VBA Controls collection.
Dim myControls As New Collection
Dim vControl As Variant
For Each vControl In Form_Form1.Controls
MsgBox vControl.Name
Next
If you need to exit the For Each loop early, VBA provides the Exit For statement.
INTRODUCTION TO OBJECT-ORIENTED PROGRAMMING
Object-oriented programming (OOP) is not a language unto itself, but rather a programming practice. OOP is seemingly easy at the surface, but can be quite challenging to master. In fact, many programmers coming from the procedural world of languages such as C or COBOL find they need to make a paradigm shift in how they think about programming. Even programmers who work with object-based languages such as VBA, find the same paradigm shift inevitable. The paradigm shift I refer to is that of relating data, structures, and business requirements to objects.
OOP contains five core concepts, which are objects, classes, encapsulation, inheritance, and polymorphism:
• Objects represent a real-world thing such as person, place, or thing. Objects have behaviors and attributes.
• Classes are the blueprint for objects. They define how objects behave and how they expose attributes.
• Encapsulation hides implementation details from a user.
• Inheritance allows one class to inherit the features of another class.
• Polymorphism allows a class to implement the same operation in a number of different ways.
Unfortunately, VBA does not support inheritance or polymorphism in OOP’s truest sense. Nevertheless, object-oriented programming in VBA allows the implementation of one of the most important benefits of OOP development known as encapsulation. In OOP terms, encapsulation allows programmers to reduce code complexity by hiding data and complex structures in classes. You and other programmers simply instantiate (create an object from a class) these classes as objects, and then access the object’s methods and properties. Encapsulating implementation details is a wonderful benefit of OOP. Not only are complex details hidden, but code reuse is promoted. In VBA, OOP development is achieved through custom objects that are defined in class modules. Once built however, custom objects don’t necessarily add new functionality to your code. In fact, the same code you write in class modules could be written in event procedures, subprocedures, and function procedures. The purpose of using class modules is to provide encapsulation, code-reuse, and self-documenting code. Programmers using your custom objects work with them just as they would with other built-in VBA objects such as the ones found in the ADO library (Connection and Recordset objects).
Development with OOP generally requires more planning up front than in other programming paradigms. This design phase is crucial to OOP, and to your application’s success.
At the very minimum, OOP design should include the following tasks:
• Identify and map objects to programming and business requirements.
• Identify the actions (methods) and attributes (properties) of each object. This action is commonly referred to as identifying the responsibilities of each object.
• Identify the relationships between objects.
• Determine the scope of objects, and their methods and properties.
CREATING CUSTOM OBJECTS
Let’s begin your investigation into object-oriented programming by creating custom objects that encapsulate implementation details through OOP techniques and class modules. I’ll specifically show you how to build class modules that contain member variables, and property and method procedures. After learning how to build custom objects with class modules, I’ll show you how to put it all together by instantiating custom objects, and accessing their methods and properties.
Working with Class Modules
Classes are the blueprints for an object. They contain the implementation details, which are hidden from users (programmers who use your custom objects). In object-oriented programming with VBA, classes are implemented as class modules.
Class modules do not exist in memory. Rather, the instance of the class known as the object does. Multiple instances of a single class can be created. Each instance (object) created from a class shares the same access to the class’s methods and properties. Even though multiple objects created from one class share the same characteristics, they are different in two ways. First, objects instantiated from the same class can have different property values. For example, an object called Bob instantiated from the Person class may have its hairColor property value set to brown, whereas an object called Sue, also instantiated from the Person class, could have its hairColor property value set to blonde. Second, objects instantiated from the same class have unique memory addresses.
In OOP terms, an instance refers to the object that was created from a class. The term instantiate means to create an object from a class.
To create a class module in Access VBA, simply open a Visual Basic window (VBE) and select the Class Module menu item from the Insert menu. Microsoft VBA automatically creates the class module for you, as depicted in Figure 11.2.
FIGURE 11.2 A newly created class module.
By default, VBA class modules contain two events called Initialize and Terminate. These events can be accessed through the Code window shown in Figure 11.3.
FIGURE 11.3 The Initialize and Terminate events are accessed through the VBE Code window.
The Initialize event for a class module is triggered each time the class is instantiated (created) using the New or Set keywords. The class module’s Initialize event is similar to that of a constructor in OOP languages such as Java and C++. It is used to execute code when the object is first created. For example, you might want to initialize certain member variables each time an instance of your class is created.
The Terminate event is triggered each time the instance is removed from memory. You can place code in this event procedure to free up other objects from memory or finalize any necessary transactions.
Another common use of the Initialize and Terminate events is in debugging your applications. If you’d like to know each time your application creates and destroys one of your custom objects, simply use the Initialize and Terminate events, like I’ve done here.
Private Sub Class_Initialize()
Debug.Print “Object created.”
End Sub
Private Sub Class_Terminate()
Debug.Print “Object destroyed.”
End Sub
Microsoft recommends not using message boxes in the Initialize and Terminate events, which require Windows messages to be processed.
Property Procedures
VBA provides property procedures for managing the attributes of a class, which are exposed internally for the class to use, or exposed externally as object properties. Simply put, properties are just variables.
To work with properties in VBA, you create variables of various scopes, and use a combination of property procedures to manage (access the property value, and assign values to the property) them. VBA provides three types of property procedures:
• Property Get. Returns the value of a property.
• Property Let. Assigns a value to the property.
• Property Set. Sets the value of an object property.
Property Get procedures are often used in conjunction with both Property Let and Property Set procedures, and when used together (Property Let with Property Get, or Property Set with Property Get) must share the same name.
Note, Property Let and Property Set procedures cannot be used together. They perform distinctly different roles in VBA object-oriented programming. Specifically, Property Let procedures are used for assigning data to scalar variables such as String, Integer, Double, or Date data types. Property Set procedures are used for assigning a reference to an object.
To add property procedures to your class module, select the Add Procedure dialog window from the VBE Insert menu, as demonstrated in Figure 11.4.
FIGURE 11.4 Use the Add Procedure dialog window to create property procedures.
VBA automatically adds a matching set of Property Get and Property Let procedures for you, as shown next:
Public Property Get Something() As Variant End Property
Public Property Let Something(ByVal vNewValue As Variant) End Property
The code required in each property procedure is short. You simply add a line to each respective procedure to assign a value and return a value.
Before adding code to your property procedures, you must first have a property (sometimes referred to as member variables) to manage. When working with property procedures, your properties are generally declared as Private in the general declarations area. By declaring the variable (property) in the general declarations area, you provide access to the property from any procedure in the class module. Moreover, declaring the variable (property) as Private provides encapsulation and forces your object’s users to use the property procedures to access the member variable instead of accessing the member variable directly.
The concept of private properties and procedures is very important in OOP. Any procedure or property declared as Private is only accessible to the class module (not instances of your class module). Examine this concept further by studying the next block of VBA code.
Option Compare Database Private privateSomething As Variant
Public Property Get Something() As Variant
Something = privateSomething
End Property
Public Property Let Something(ByVal vNewValue As Variant)
privateSomething = vNewValue End Property
Notice that the Property Get procedure behaves much like a Function procedure in that a value is assigned to the procedure’s name. This type of assignment statement returns the property’s value to the calling procedure. The Property Let procedure takes a single argument as a parameter (vNewValue), and assigns its value to the privateSomething property. This is how instantiated objects of this class access the privateSomething property without knowing how it’s declared or what its name is.
The previous code blocks typify a read/write property. In other words, instantiated objects of this class can read this property and write data to it. It’s common, however, to require read-only properties in OOP. To do so, simply use a single Property Get procedure by removing the corresponding Property Let procedure.
Option Compare Database Private readOnlySomething As Variant
Public Property Get something() As Variant
something = readOnlySomething
End Property
Using a Private property called readOnlySomething, and a single Property Get procedure, instantiated objects of this class can only read the property.
You can also use the Add Procedure dialog window to create matching Property Set and Property Get procedures. After VBA has created the matching property procedures, simply change the keyword Let to Set.
Option Compare Database Private employee As Employee
Public Property Get NewEmployee() As Variant
NewEmployee = employee End Property
Public Property Set NewEmployee(ByVal vNewValue As Employee)
employee = vNewValue
End Property
Instead of a Variant data type (or any other data type, for that matter), the Property Set procedure called NewEmployee takes in a parameter of Employee type. The Property Set procedure then assigns the object reference from the argument to the property of the same object type. A matching Property Get procedure is used to return an object reference of the property.
You’ve probably noticed by now that these procedures are very simple. That’s because they should be! The primary purpose of property procedures is to manage access to member variables. It may seem like overkill for what appears to be variable access, but in sections to come, and through practice, you will see the power of property encapsulation through property procedures.
Method Procedures
Method procedures expose methods internally to the class module, or externally to an instance of the class. They are the meat and potatoes of object-oriented programming! For example, creating a Person object isn’t really that interesting, or useful unless the Person object has not only customizable properties such as EyeColor, Height, Weight, Age, and Gender, but can also perform actions (methods) like Run, Work, Eat, Sleep, Play, Drive, or Dance.
Creating methods for custom objects is quite easy. Simply create and place Sub or Function procedures in your class modules to represent methods.
Remember from earlier in the book that function procedures return a value, and subprocedures do not.
An example of each type of method is shown next.
Option Compare Database Private result1 As Integer Private result2 As Integer
Public Sub AddTwoNumbers(num1 As Integer, num2 As Integer) result1 = num1 + num2 End Sub
Public Function MultiplyTwoNumbers(num1 As Integer, num2 As Integer) As Double
MultiplyTwoNumbers = num1 * num2
End Function
The first method, AddTwoNumbers, takes two parameters and sets a property. If it’s necessary for instances of this class to access this result, you should create a Property Get procedure that returns the value of the result1 member variable.
The second method, MultiplyTwoNumbers, is a function that also takes two arguments and performs a simple calculation. The big difference is that this method is a function, which returns a value to the calling procedure by assigning a value to the method’s name.
Working with object methods and properties is pretty straightforward. If you’ve been working with VBA even a little, you’ve already had exposure to objects, and their properties and methods. For example, consider the Connection object from the ADO library. The Connection object has a method called Open. You and I both know this method establishes a connection to a database. But do we know how that method is implemented? No, we don’t. And believe it or not, that’s a good thing. Think about all the programming that must be involved to implement the Open method of the Connection object. It’s a sure bet that it contains complicated data structures and algorithms. This is encapsulation at its finest. Because the implementation detail is hidden, VBA programmers can simply call the method, and pass it a few parameters to successfully open a database connection.
Putting It all Together
Let’s now use what we’ve learned about object-oriented programming in VBA including class modules, property procedures, and method procedures to build a simple program, as demonstrated in Figure 11.5.
FIGURE 11.5 Using class modules, property procedures, and method procedures to change characteristics of a house.
The OOP House program in Figure 11.5 allows a user to change the color of a house, and turn its lights on and off using property and method procedures. Note that there are three images used in this program that can be found on the book’s companion website (www.courseptr.com/downloads). Remember to change the location of your image’s Picture property to that of your own drive and directory location.
Controls and properties of the OOP House program are shown in Table 11.2.
The first thing I’ll do is create a new class module called House, and declare two private properties, one to manage the house color, and the other to determine whether the lights are on or off.
Option Compare Database Option Explicit ‘ Declare private class properties Private sHouseColor As String Private bLights As Boolean
I can now create public property procedures to access, and assign values to the class’s private properties.
TABLE 11.2 CONTROLS AND PROPERTIES OF THE OOP HOUSE PROGRAM
Public Property Get Lights() As Variant
Lights = bLights
End Property
Public Property Let Lights(ByVal vNewValue As Variant) bLights = vNewValue End Property
Public Property Get HouseColor() As Variant HouseColor = sHouseColor End Property
Public Property Let HouseColor(ByVal vNewValue As Variant) sHouseColor = vNewValue End Property
Writing the code to manage my private properties was easy! The public functions (methods) to perform an action on the house lights and house color require a bit more thought, but are still pretty straightforward. Specifically, I will create two public methods, one called LightSwitch that will turn off and on the house lights, and another called ChangeHouseColor that will modify the Picture property of the image based on user selection.
Public Function LightSwitch()
Dim cRectangle As Control
‘ Loop through each rectangle on the form
For Each cRectangle In Form_frmHouse.Controls
‘ Look for Rectangle controls; TypeName function returns an object’s name
If TypeName(cRectangle) = “Rectangle” Then
‘ Turn lights on or off depending on the value of the private property
If bLights = True Then
cRectangle.BackColor = vbYellow Else cRectangle.BackColor = vbBlack End If End If Next End Function
Public Function ChangeHouseColor() Form_frmHouse.imgHouse.Picture = sHouseColor End Function
Everything in my House class module so far should be familiar to you with the exception of the next line of code.
If TypeName(cRectangle) = “Rectangle” Then
TypeName is a built-in VBA function that takes an Object as a parameter, and returns the object’s name. I use the TypeName function as part of an overall For Each loop to find only Rectangle controls on the form. I’m using Rectangle control as house lights. When I find a Rectangle control, I assign a color to it based on user input (i.e., yellow for lights on, and black for lights off).
With my new class module created and saved, I can now declare variables of House type from other modules. The next series of code demonstrates creating a module level variable of House type called aNewHouse, and accessing its properties and methods through event procedures to change the characteristics of the house based on user input.
Option Compare Database Option Explicit Dim aNewHouse As New House
Private Sub optBlue_GotFocus() aNewHouse.HouseColor = Application.CurrentProject.Path & “lue_house.PNG” aNewHouse.ChangeHouseColor End Sub
Private Sub optBrown_GotFocus() aNewHouse.HouseColor = Application.CurrentProject.Path & “rown_house.PNG” aNewHouse.ChangeHouseColor End Sub
Private Sub optGreen_GotFocus() aNewHouse.HouseColor = Application.CurrentProject.Path & “green_house.PNG” aNewHouse.ChangeHouseColor End Sub
Private Sub optOff_GotFocus() aNewHouse.Lights = False aNewHouse.LightSwitch End Sub
Private Sub optOn_GotFocus() aNewHouse.Lights = True aNewHouse.LightSwitch End Sub
An important part of working with object instances is freeing and reclaiming resources when your objects are no longer required. When objects are instantiated, VBA reserves memory and resources for processing. To free these resources, simply set the object to Nothing as demonstrated next.
Private Sub Form_Unload(Cancel As Integer) ‘ Free memory Set aNewHouse = Nothing End Sub
It’s good programming practice to reclaim resources not only from custom objects but from built-in objects such as the ones found in the ADO library. If you neglect to free object resources, VBA does not remove them from memory until the application is terminated. If your application uses a lot of objects, this could lead to performance problems.
CHAPTER PROGRAM: MONSTER DATING SERVICE
The Monster Dating Service program in Figure 11.6 uses chapter-based concepts to build a fun and easy-to-use application. Essentially, the program allows a user to find a date with an available monster by selecting character criteria.
FIGURE 11.6 Using chapter-based concepts to build the Monster Dating Service program.
Controls and properties of the Monster Dating Service program are shown in Table 11.3.
TABLE 11.3 CONTROLS AND PROPERTIES OF THE MONSTER DATING SERVICE PROGRAM
The Monster Dating Service program uses object-oriented programming techniques split across two modules. The first module is a class called Monster that defines an object that encapsulates all functionality required to connect to the current database, and retrieve monster attributes. Database connections and recordset management are accomplished using ADO concepts discussed in Chapter 10, “Database Programming with ADO.”
Option Compare Database Option Explicit Private name As String Private picture As String Private id As Integer
Public Sub FindMonster(sql_ As String) Dim rs As New ADODB.Recordset Dim sql As String ’This method finds and sets all necessary monster details. ’If does not return a value. Users of this method must use ’the read-only property procedures to access the monster ’attributes. On Error GoTo MonsterError ’Open the recordset based on the SQL string passed ’in as an argument. rs.Open sql_, CurrentProject.AccessConnection, _ adOpenForwardOnly, adLockReadOnly, adCmdText If rs.EOF = False Then ’Retrieve the monster’s id, which will be used later. id = rs!monsterId Else ’No monster found with those attributes. ’Raise a custom error. Err.Raise vbObjectError + 512, , “No monster found.” End If rs.Close ’Generate a new SQL string to retrieve the monster’s ’name and picture. sql = “select * from Monsters where MonsterId = ” & id rs.Open sql, CurrentProject.AccessConnection, _ adOpenForwardOnly, adLockReadOnly, adCmdText If rs.EOF = False Then ’Assign monster name and picture to properties. name = rs!MonsterName picture = Application.CurrentProject.Path & “” & _ rs!picture End If rs.Close Exit Sub
MonsterError: MsgBox “Sorry, there was a problem finding the monster. ” & _ Chr(13) & Err.Number & “, ” & Err.Description End Sub
Public Property Get MonsterName() As Variant
‘This property procedure returns the monster’s name.
MonsterName = name
End Property
Public Property Get MonsterPicture() As String
‘This property procedure returns the path and file
‘name of the monster’s picture.
MonsterPicture = picture
End Property
The second module is a form class used to instantiate a Monster object. Methods and properties of the Monster object are used in conjunction with event procedures and user input to find an available date.
Option Compare Database Option Explicit
Private Sub cmdFindMonster_Click()
Dim aMonster As New Monster
Dim gender As String
Dim personality As String
Dim outdoors As Boolean
Dim sql As String
’Generate a SQL string based on user selection criteria. If Me.fraGender.Value = 1 Then gender = “Male” Else gender = “Female” End If If Me.fraOutdoors.Value = 1 Then outdoors = True Else outdoors = False End If If Me.fraPersonality = 1 Then personality = “Outgoing” Else personality = “Shy” End If sql = “select * from MonsterAttributes where Outdoors = ” & _ outdoors & “ and Gender = ’” & gender & “’” & _ “ and Personality = ’” & personality & “’” ’Try to find a monster based on the search criteria. aMonster.FindMonster sql ’If a monster was found, display their name and picture. If aMonster.MonsterName = “” Then Me.lblMonsterName.Caption = _ “Sorry, no one is available with ” & _ “that search criteria.” Me.imgPicture.picture = _ Application.CurrentProject.Path & “” & “logo.gif” Else Me.lblMonsterName.Caption = aMonster.MonsterName & _ “ is available for a date.”
Me.imgPicture.picture = aMonster.MonsterPicture End If End Sub
SUMMARY
• Object-oriented programming maps data, structures, and business requirements to objects.
• Encapsulation allows programmers to reduce code complexity by hiding data and complex data structures in classes.
• Class modules contain member variables as well as property and method procedures.
• Class modules do not exist in memory.
• Multiple instances of a single class can be created.
• By default, VBA class modules contain two events called Initialize and Terminate.
• The Initialize event for a class module is triggered each time the class is instantiated (created) using the New or Set keywords.
• The Terminate event is triggered each time the class’s instance is removed from memory.
• VBA provides property procedures for managing the attributes of a class.
• VBA provides three types of property procedures: Property Get, Property Let, and Property Set.
• Property Get procedures return the value of a property.
• Property Let procedures assign a value to a property.
• Property Set procedures set the value of an object property.
• Use a single Property Get procedure to create a read-only property.
• Method procedures are created in class modules with Sub and Function procedures.
• Setting objects to Nothing frees system resources.
• The TypeName function is a built-in VBA function that takes an Object as a parameter, and returns the object’s name.
• Collections are objects that contain an ordered list of items.
• Items in a collection are called members.
• Members in a collection can be referenced with an index or key value.
• VBA provides the For Each loop to iterate through members in a collection or an array.
1. Create a new database called BookStore with one table called Books. Add the columns ISBN,Title,PublishDate, and Price to theBooks table. Add a few records to the Books table. Create a new connection class called CustomConnection that connects to your BookStore database. The new class should have two methods—one method for opening an ADO Connection object and a second method for closing the ADO Connection object. The method that opens a database connection should take a single string argument, which represents the path and filename of the database.
2. In the same database application from Challenge 1, create a new class called Books. This class should have a read-only property for each column in the Books table. Create a method in the Books class called FindBook. The FindBook method should take in an ISBN. Build a SQL string based on the ISBN and use ADO programming techniques to open a recordset and assign the recordset field values to the class’s matching properties. You should use the CustomConnection class to create and retrieve any Connection objects.
3. Enhance Challenges 1 and 2 by building a form with controls that allow a user to find a book by entering an ISBN. Use your Books class from Challenge 2 to find and retrieve book details.
4. Enhance the user interface from Challenge 3 to allow a user to add and remove books. To accomplish this, you need to modify the Books class from Challenge 2 by adding two methods called AddBook and RemoveBook.
5. In a new Access application, create a Collection object called Friends. Construct a user interface that allows a user to add and remove names of friends in the Friends collection.
6. Add a command button to Challenge 5’s user interface that displays each friend in a message box. Hint: Use the For Eachloop to iterate through members in the Friends collection.