Let's say you want to create a separate copy of a class object. It might be tempting to write something like this:
Dim objTeacher1 As clsTeacher Dim objTeacher2 As clsTeacher Set objTeacher 1 = New clsTeacher Set objTeacher 2 = objTeacher1
On the surface, this looks as if it would create a new object named objTeacher2 that has the same property values as objTeacher2, and that is true. However, these objects actually point to the same data. If you change a property for objTeacher2, objTeacher1 also reflects the same value. The following code demonstrates:
Sub TestCopy() Dim objTeacher1 As clsTeacher Dim objTeacher2 As clsTeacher ' create the first instance Set objTeacher1 = New clsTeacher objTeacher1.Name = "Steven Buchanan" Debug.Print "Before: " & objTeacher1.Name ' create the second instance Set objTeacher2 = objTeacher1 ' set the second name, then print the first objTeacher2.Name = "Nancy Davolio" Debug.Print "After: " & objTeacher1.Name ' Verify that the objects point to different locations Debug.Assert ObjPtr(objTeacher1) <> ObjPtr(objTeacher2) ' cleanup
Set objTeacher1 = Nothing Set objTeacher2 = Nothing End Sub
The first time the Name is printed, the code prints Steven Buchanan. Then after changing the name of objTeacher2, objTeacher1 has been changed to Nancy Davolio.
Note the use of the ObjPtr function. This is a hidden function in VBA that returns the memory address of an object variable. You use this function in a Debug.Assert statement ...