Declaring the variables with the Private keyword guarantees that the variables cannot be accessed directly from outside the object. This means that the VBA procedures outside the class module will not be able to set or read data stored in those variables. To enable other parts of your VBA application to set or retrieve the employee data, you must add special property procedures to the CEmployee class module. There are three types of property procedures: Property Let — This type of procedure allows other parts of the application to set the value of a property.
Property Get — This type of procedure allows other parts of the application to get or read the value of a property.
Property Set — This type of procedure is used instead of Property Let when setting the reference to an object.
Property procedures are executed when an object property needs to be set or retrieved. The Property Get procedure can have the same name as the Property Let procedure. You should create property procedures for each property of the object that can be accessed by another part of your VBA application.
The easiest of the three types of property statements to understand is the Property Get procedure. Let's examine the syntax of the property procedures by taking a close look at the Property Get LastName procedure.
Property procedures contain the following parts: A procedure declaration line, an assignment statement, and the End Property keywords.
A procedure declaration line specifies the name of the property and the data type:
Property Get LastName() As String LastName
LastName is the name of the property and As String determines the data type of the property's return value.
An assignment statement is similar to the one used in a function procedure:
LastName = m_LastName LastName
LastName is the name of the property and m_LastName is the data member variable that holds the value of the property you want to retrieve or set. The m_LastName variable should be defined with the Private keyword at the top of the class module.
If the retrieved value is obtained as the result of a calculation, you can include the appropriate VBA statement:
Property Get Royalty()
Royalty = (Sales * Percent) - Advance End Property
The End Property keywords specify the end of the property procedure. Immediate Exit from Property Procedures
Just as the Exit Sub and Exit Function keywords allow you to exit early from a subroutine or a procedure, the Exit Property keywords give you a way to immediately exit from a property procedure. Program execution will continue with the statements following the statement that called the Property Get, Property Let, or Property Set procedure.
The CEmployee class object has four properties that need to be exposed to VBA procedures that reside in a standard module named EmpOperations. When working with the CEmployee object, you would certainly like to get information about the employee ID, first and last name, and current salary.
©Custom Project 8-1 (Step 3): Writing Property Get Procedures
1. Type the following Property Get procedures in the CEmployee class module, just below the declaration section:
Property Get Id() As String
Id = m_Id End Property
Property Get LastName() As String
LastName = m_LastName End Property
Property Get FirstName() As String
FirstName = m_FirstName End Property
Property Get Salary() As Currency
Salary = m_Salary End Property
Each employee information type requires a separate Property Get procedure. Each of the above Property Get procedures returns the current value of the property. Notice how a Property Get procedure is similar to a function procedure. Similar to function procedures, the Property Get procedures contain an assignment statement. As you recall, in order to return a value from a function procedure, you must assign it to the function's name.
In addition to retrieving values stored in data members (private variables) with Property Get procedures, you must prepare corresponding Property Let procedures to allow other procedures to change the values of these variables as needed. The only time you don't define a Property Let procedure is when the value stored in a private variable is meant to be read-only.
The example CEmployee class allows the user to assign values of the following data items by typing entries in the appropriate text boxes: last name, first name, and salary. The employee ID will be assigned automatically with a return value from a function procedure.
©Custom Project 8-1 (Step 4): Writing Property Let Procedures
1. Type the following Property Let procedures in the CEmployee class module below the Property Get procedures:
Property Let Id(ref As String)
m_Id = ref End Property
Property Let LastName(L As String)
m_LastName = L End Property
Property Let FirstName(F As String)
m_FirstName = F End Property
Property Let Salary(ByVal dollar As Currency)
m_Salary = dollar End Property
The Property Let procedures require at least one parameter that specifies the value you want to assign to the property. This parameter can be passed by value (see the ByVal keyword in Property Let Salary above) or by reference (ByRef is the default). If you need a refresher on the meaning of these keywords, see the section titled "Passing Arguments by Reference and by Value" in Chapter 4. The data type of the parameter passed to the Property Let procedure must have exactly the same data type as the value returned from the Property Get or Set procedure with the same name. Notice that the Property Let procedures have the same names as the Property Get procedures prepared in the preceding section.
You can place the Public, Private, or Static keyword before the name of a property procedure to define its scope. To indicate that the Property Get procedure is accessible to procedures in all modules, use the following statement format:
Public Property Get FirstName() As String
To make the Property Get procedure accessible only to other procedures in the module where it is declared, use the following statement format:
Private Property Get FirstName() As String
To preserve the Property Get procedure's local variables between procedure calls, use the following statement format:
Static Property Get FirstName() As String If not explicitly specified using either Public or Private, property procedures are Public by default. Also, if the Static keyword is not used, the values of local variables are not preserved between procedure calls.
Was this article helpful?