Properties as a Means of Restricting Access to Class Fields with Excel VBA

Many classes have public fields, which programmers can access directly. However, in most cases, this approach is not very convenient. A more suitable approach is to use private fields, i.e., fields that are inaccessible outside the class.

The use of private fields allows data to be protected from external interference. Therefore, the process of getting and setting values for the current state of an object is preferably implemented through properties or special methods.

To describe a property, you use special procedures:

• Property Let — declares property names whose values are non-object data types.
• Property Set — declares property names whose values are objects.
• Property Get — provides the ability to read property values.

In the following code, a class Employee is created, encapsulating information about a company employee.

This class has three private fields: fname, lname, and pos, which describe the first name, last name, and position of the employee.

To set and retrieve values for these fields, three properties are defined: FirstName, LastName, and Position.

The class also defines the ToDebug() method, which outputs combined information about the class instance into the Immediate window.

Properties. Class module Employee

Private fname As String
Private lname As String
Private pos As String

Property Get FirstName() As String
    FirstName = fname
End Property

Property Let FirstName(ByRef newfname As String)
    fname = newfname
End Property

Property Get LastName() As String
    LastName = lname
End Property

Property Let LastName(ByRef newlname As String)
    lname = newlname
End Property

Property Get Position() As String
    Position = pos
End Property

Property Let Position(ByRef newpos As String)
    pos = newpos
End Property

Public Sub ToDebug()
    Debug.Print "First Name: " & fname & vbCr & _
                "Last Name: " & lname & vbCr & _
                "Position: " & pos
End Sub

Properties. Standard module

Sub DemoEmployee()
    Dim emp1 As New Employee
    emp1.FirstName = "James"
    emp1.LastName = "Bond"
    emp1.Position = "Secret agent 007"
    emp1.ToDebug
    Dim emp2 As New Employee
    emp2.FirstName = "Jack"
    emp2.LastName = "Sparrow"
    emp2.Position = "Pirate"
    emp2.ToDebug
End Sub