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:

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

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:

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 Developer

Tagged with: , ,