Linking classes in VBA

Question

I have defined a class in VBA as below

Class Employee(clsEmployee):

Private pEmpName As String
Private pEmpID As Long
Private pDOJ As Date
Private pManager As clsEmployee
Private pOffice As clsOffice

where pManager is a property of the class of type clsEmployee and pOffice is also the property of the class of type clsOffice(another class)

I also have defined the Let and Get method inside the class to read and write properties of the class as below

Public Property Let Name(sName As String)
    If sName <> "" Then
        pEmpName = sName
    End If
End Property

Public Property Get Name() As String
    Name = pEmpName
End Property

Public Property Let EmployeeId(lngID As Long)
    If IsNumeric(lngID) Then
        pEmpID = lngID
    End If
End Property

Public Property Get EmployeeId() As Long
    EmployeeId = pEmpID
End Property

Public Property Let PDM(obj As clsEmployee)
    Set pPDManager = obj
End Property

Public Property Get PDM() As clsEmployee
    Set PDM = pPDManager
End Property

Now in the code module I have written a test sub to set some of the properties of my Employee class as below

    Sub test()
    Dim obj As clsEmployee

    Set obj = New clsEmployee

    obj.Name = "Employee 100"
    obj.EmployeeId = 11111111
    obj.PDM.Name = "Employee 1"

When the code executes the statement obj.Name="Employee 100" then the Let property which is Name gets executed and the pEmpName is set, while when the code tries to execute the statemnet obj.PDM.Name="Employee 1 VBA executes the GET method PDM.

My question is why does the get method (PDM in class) gets executed on statement `obj.PDM.Name="Employee 1" when clearly I am trying to set the property instead of retrieving it.

Answer 1

where pManager is a property of the class of type clsEmployee

Each instance of this class clsEmployee has to have a reference to another instance of clsEmployee which denotes the manager. The employee must have its manager.

The property PDM retuns clsEmployee so it needs to use Set

Public Property Set PDM(obj As clsEmployee)
    Set pManager = obj
End Property

Public Property Get PDM() As clsEmployee
    Set PDM = pManager
End Property

why does the get method (PDM in class) gets executed on statement `obj.PDM.Name="Employee 1"

The employee has its manager. This manager is accessible via the property PDM. If we have reference to employee in variable obj and we want to change name of the manager of this employee, we have to get to its manager. That is why the PDM is called. So we get to the manager and can change its name. HTH

The test method could be now modified as following.

Option Explicit

Sub test()
    Dim mgr As clsEmployee
    Set mgr = New clsEmployee

    Dim obj As clsEmployee

    Set obj = New clsEmployee
    Set obj.PDM = mgr

    obj.Name = "Employee 100"
    obj.EmployeeId = 11111111
    obj.PDM.Name = "Employee 1"
End Sub

---

When we want a property which sets a reference to an object we need to use Set-Property. Otherwise Let is enough.

By convention we use Set for object references, like your employee. Employee is composition of data like Name, Id, DateOfBirth etc. Let is for atomic data like string, integer, bool etc.

Have a look at Worksheet class of Excel library. This class has property Name which is of type string. If we have a variable which references a particular worksheet then we can change name of the worksheet like this:

Dim w as Worksheet
Set w = Worksheets(1)
Let w.Name = "Demo1"

Notice Let here. Because Name is string-Property Let is used. The keyword Let can be omitted though.

Worksheet has property Parent of type Object which is a reference to the parent of the particular workseet. If we would like to change the parent, we will write:

Dim w as Worksheet
Set w = Worksheets(1)
Set w.Parent = new Parent ' Is just example, it won't compile, Parent is read-only :)

In case when Set is needed it can't be omitted like Let.

---

In your case the properties which work with clsEmployee must use Get-Set where the properties which work with e.g. string use Get-Let.

Here in my Dropbox I have created an very ugly picture which should illustrate the situation with the object references in variables obj and mgr.