What is the correct way to initialize objects in VBA?

Question

I have a custom object that I was adding to an array via a loop. The problem was when I initialized the object like this:

Dim CallNum As New Lib_CallNum

The last object added in the loop would overwrite all the other objects added during the loop. So I would end up with an array filled with a bunch of the same objects. To fix this I had to change the way I was initializing the object to:

Dim CallNum As Lib_CallNum
Set CallNum = New Lib_CallNum

But I am unsure why the first initialization would not work. So what is the difference between the two sets of code?

Answer 1

The Dim inside a loop is not actually executed on each iteration. It is only executed the first time the variable is encountered.

To demonstrate this, add a section to your Lib_CallNum class initialisation definition:

Private Sub Class_Initialize()
    Debug.Print "Initialise Lib_CallNum"
    ' ...
End Sub

and run your original code. Initialise will only be reported once. From then on you are adding the same instance to the array many times.

The correct way to initialise new instances objects is as @Doug has told you, Set ... = New ...