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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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!