VBA – How to copy a new object in VBA, without copying its reference

Copying an object in VBA to a new object is somehow tough. If you use Set foo = bar then, both foo and bar would be pointing to the same address in the heap. And if you change the properties of one, you would automatically change the properties of the other.

Using these answer for VB6 and this one for VBA, there is a good way to copy an object, with a new address in the heap. The “trick” is to use the Memento Pattern, and the Type structure in VBA. Thus, let’s imagine that the object we are creating has the following properties in a type, written in a module in VBA:

Type MyMemento
    Salary As Double
    Age As Long
    RelevantExperience As Long
End Type

These are referred in its Employee class with get-ters and let-ters:

Option Explicit

Private Memento As MyMemento

Friend Sub SetMemento(NewMemento As MyMemento)
    Memento = NewMemento
End Sub

Public Function Copy() As Employee
    Dim Result As Employee
    Set Result = New Employee    
    Result.SetMemento Memento
    Set Copy = Result    
End Function

Public Property Get Salary() As Double
    Salary = Memento.Salary
End Property

Public Property Let Salary(value As Double)
    Memento.Salary = value
End Property

Public Property Get Age() As Long
    Age = Memento.Age
End Property

Public Property Let Age(value As Long)
    Memento.Age = value
End Property

Public Property Get RelevantExperience() As Long
    RelevantExperience = Memento.RelevantExperience
End Property

Public Property Let RelevantExperience(value As Long)
    Memento.RelevantExperience = value
End Property

Using the type referred in the class as Private Memento As MyMemento allows to have a quick access to all properties, whenever a property is assigned to an object. Thus, if all the properties should be assigned to a New object, this is done with the one liner – Memento = NewMemento called from a Copy function.

This is a minimal example of the usage of the class:

Sub Main()

    Dim newEmp As Employee
    Dim oldEmp As Employee
    
    Set newEmp = New Employee
    With newEmp
        .Salary = 100
        .Age = 22
        .RelevantExperience = 1
    End With
    
    Set oldEmp = newEmp.Copy
    With oldEmp
        'Salary is inherited, thus the same
        .Age = 99
        .RelevantExperience = 10
    End With
    
    Debug.Print "Salary"; vbCrLf; newEmp.Salary, oldEmp.Salary
    Debug.Print "Experience"; vbCrLf; newEmp.RelevantExperience, oldEmp.RelevantExperience
    Debug.Print "Age"; vbTab; vbCrLf; newEmp.Age, oldEmp.Age

End Sub

Once the code is ran, the following data is displayed in the immediate window:

Thus, it works quite well! Enjoy it in GitHub as well!

VBA – How to copy a new object in VBA, without copying its reference

Related posts: