You can insert a Class module in one of three ways:
With the Database window active, select Insert, Class Module.
With the Visual Basic Editor active, select Insert, Class Module.
With the Visual Basic Editor active, right-click the project within the Project Explorer window and select Insert, Class Module from the pop-up menu.
After being inserted, a Class module looks like a Standard Code module (see Figure 13.1). The differences lie in how the variables and procedures in the Class module are accessed, as well as in the behavior of the Class module.
The most basic way to add a property to a Class module is to use a Public variable. For example, the following code shows the declaration of two Public variables, FirstName and LastName. After you add them to a class, VBA considers them properties of the class.
Public FirstName as String Public LastName as String
We consider a function or subroutine placed within a Class module as a method of the class. The Speak subroutine that follows acts as a method of the PublicPerson class. It accesses the FirstName and LastName properties of the class, displaying them in a message box.
Public Function Speak() Speak = FirstName & " " & LastName End Function
To utilize the code within a class, you must instantiate an object based on that class. To do that, you first declare an object based on the class. You then instantiate the object by using a Set statement. You can then access the properties and methods of the object. The code looks like this:
Sub SingleInstance() 'Declare and instantiate a Person object Dim oPerson As Person Set oPerson = New Person 'Set the first name and last name 'properties of the Person object oPerson.FirstName = "Alison" oPerson.LastName = "Balter" 'Display the return value from the Speak 'method in a message box MsgBox oPerson.Speak End Sub
The code begins by declaring a Person object. The code uses a Set statement to create an instance of the Person object. The code then sets the FirstName and LastName properties of the instance to Alison and Balter, respectively. The code then executes the Speak method of the object. It returns the concatenated name, which is displayed in a message box.
Public variables, when used as properties, have the following major disadvantages:
Using Public variables, you cannot create properties that are read-only or write-only.
You cannot validate what goes into Public variables.
You cannot manipulate the value as the Public variable is set.
You cannot track changes to Public variables.
For these reasons, it is prudent to use property procedures rather than Public variables. With property procedures, you can create custom runtime properties of user-defined objects. After you have defined custom properties, you can use Property Let and Get to assign values to and retrieve values from custom properties. Custom properties give you more flexibility in creating your applications; you can create reusable objects that expose properties to other objects.
Custom properties are Public by default and are placed in Class, Form, or Report modules, making them visible to other modules in the current database. They aren't visible to other databases.
The Property Let routine defines a property procedure that assigns a value to a user-defined object's property. Using a Property Let is similar to assigning a value to a Public variable, but a Public variable can be written to from anywhere in the database, with little or no control over what's written to it. With a Property Let routine, you can control exactly what happens when a value is assigned to the property. Here's an example:
Public Property Let FirstName(ByVal strNewValue As String) mstrFirstName = UCase(strNewValue) End Property
You might be thinking this code looks just like a subroutine, and you're somewhat correct. It's a special type of subroutine that executes automatically in response to the change in a custom property's value. The example receives the value that the property is changed to as strNewValue. The code stores the uppercase version of the value in the Private variable mstrFirstName. The following line of code causes the code in the Property Let to execute:
FirstName = "Alison"
Property Let sets the value of a custom property, but Property Get defines a property procedure that retrieves a value from a user-defined object's property. This example illustrates how Property Get is used:
Public Property Get FirstName() As String FirstName = mstrFirstName End Property
The Property Get routine automatically executes whenever the code tries to retrieve the value of the property. The value stored in the Private variable mstrFirstName is returned from the Property Get procedure. This routine can be executed by retrieving the property from anywhere in the database. The following line of code causes the code in the Property Get to execute:
MsgBox FirstName
The code that follows shows the declaration of the two Private variables mstrFirstName and mstrLastName. The Property Let for FirstName and the Property Let for LastName store values into these two Private variables. The Property Get for FirstName and the Property Get for LastName retrieve the values stored in the Private variables.
Private mstrFirstName As String Private mstrLastName As String Public Property Get FirstName() As String FirstName = mstrFirstName End Property Public Property Let FirstName(ByVal strNewValue As String) mstrFirstName = UCase(strNewValue) End Property Public Property Get LastName() As String LastName = mstrLastName End Property Public Property Let LastName(ByVal strNewValue As String) mstrLastName = UCase(strNewValue) End Property
Unlike with Public variables, you have significant control over a property created with Property Let and Property Get routines. To create a read-only property, include only a Property Get. To create a write-only property, include only a Property Let. If you want a read/write property, include both the Property Get and the Property Let routines.