Copy an object without reference in VBA

Question

I have trouble with Object Variables in VBA. Is it possible to just copy object variables without any reference?

Here the class module "clstest"

Option Explicit

Public x As Single

And here is my Sub:

Sub CopyWithoutReference()

Dim standard As New clstest
Set standard = New clstest

Dim different As New clstest

standard.x = 20

Set different = standard
different.x = 30

MsgBox "I want standard.x to be 20 and not 30"
MsgBox standard.x
MsgBox different.x

I want standard.x to keep its value and not change if different.x changes. I read this Article here: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/set-statement And it says:

"Because such variables are references to the object rather than copies of the object, any change in the object is reflected in all variables that refer to it."

But i don't know how to obviate this problem. Anyone of you know how to help me?

Nathan_Sav · Accepted Answer · 2019-02-13T10:19:23.503

3

You could add a clone method to the class, so I have

My class

Public x As Integer

Public Function Clone() As Class1
    Set Clone = New Class1
    Clone.x = x
End Function

My module

Sub a()

Dim o As Class1
Dim o2 As Class1

Set o = New Class1
o.x = 20

Set o2 = o.Clone
o2.x = 500

Debug.Print o.x, o2.x

End Sub

------------------- COPY ALLL AT ONCE IDEA ---------------------

New class

Public Properties_ As Scripting.Dictionary

Private Sub Class_Initialize()
    Set Properties_ = New Scripting.Dictionary
End Sub

Public Sub Set_Property(strPropertyName As String, varProperty As Variant)
    If Properties_.Exists(strPropertyName) Then
        Properties_(strPropertyName) = varProperty
    Else
        Properties_.Add strPropertyName, varProperty
    End If
End Sub

Public Function Clone_() As Class1

    Set Clone_ = New Class1

    For i = 0 To Properties_.Count - 1
        Clone_.Set_Property CStr(Properties_.Keys()(i)), Properties_.Items()(i)

    Next i

End Function

New Module

Public Sub x()

Dim o1 As Class1
Dim o2 As Class1

Set o1 = New Class1

o1.Set_Property "Date", Now
o1.Set_Property "Name", "Test Name"

Set o2 = o1.Clone_

o2.Set_Property "Date", DateSerial(2000, 1, 1)

Debug.Print o1.Properties_("Date"), o2.Properties_("Date")

End Sub

edited Feb 13 '19 at 10:19

answered Feb 13 '19 at 09:51

Nathan_Sav

8,466
2
13
20

Thank you for your answer. In my code i have a lot more Properties than just one (x). Is i possible to clone all properties at once? – Charlex Feb 13 '19 at 10:03
You will need to be a little fancy, maybe create a dictionary or collection in your class, called `Properties` and may be iterate through these. Setting the new collection to be the old, may not work that easy. – Nathan_Sav Feb 13 '19 at 10:15
@Charlex I've added a quick attempt – Nathan_Sav Feb 13 '19 at 10:19
That answered my question, thank you for this solution.That answered my question, thank you for this solution. – Charlex Feb 13 '19 at 11:18
So pretty much, following this pattern, every object could set its own properties? E.g., `o2.Set_Property "SomeNewProperty", 19999`. This does not look close to OOP. – Vityata Feb 13 '19 at 11:48

Vityata · Answer 2 · 2019-02-13T11:16:33.977

This answer about VB6 is pretty good, the implementation of the memento pattern and the way to refer to the properties through a type in VBA is what achieves the copying of the properties.

An object of type Employee with properties Salary, Age and RelevantExperience is created. Then a new object, copying the old one with the function .Copy is created. The new object initially has the same properties, but we may choose to change some of them. In the code below the Experience and the Age is changed, the Salary is not mentioned, thus it stays the same:

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 the same as in the NewEmp
    .Age = 99
    .RelevantExperience = 10
End With

This is the result:

The old Employee has the same salary, "inherited" by the new employee, when he is copied. The Experience and the Age are different.

Full implementation

In a module:

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

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

In a class module, called Employee:

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

Exactly what i needed, thank you! Unfortunately VBA itself does not provide something like an argument where you just say "Reference = True/False" — Charlex, Feb 13 '19 at 11:09
@Charlex - welcome. VBA does not have a lot of things, but one may always build them. It is funnier this way! :D — Vityata, Feb 13 '19 at 11:10

Copy an object without reference in VBA

2 Answers2