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?

score 10 · Accepted Answer · answered Apr 02 '12 at 03:37

10

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 ...

answered Apr 02 '12 at 03:37

chris neilsen

52,446
10
84
123

1

It's also worth mentioning here that using the `Dim obj as new [object type]` syntax can cause issues with destroying objects, and is basically never a good idea if you may want to manually destroy objects or if you use circular object references. [See this answer for more details](https://stackoverflow.com/questions/8114684/what-is-the-reason-for-not-instantiating-an-object-at-the-time-of-declaration). I would recommend getting into the habit of always using the 2-line syntax to create objects so you always know when and how your objects are being created and destroyed. – nateAtwork Jan 15 '18 at 22:00

score 3 · Answer 2 · edited May 23 '17 at 12:02

From my experience on your previous post, I still don't think you've got quite the right approach, although not sure since never saw your finished code. You should have something like:

Function Something
Dim CallNum as Lib_CallNum
...
Do While SomeCondition
   Set CallNum = New Lib_CallNum
   'do some stuff
   Set CallNum = Nothing
Loop
Return Whatever
End Function

In other words, you should declare the object at the top of the function - not repeatedly in the loop - and instantiate (and set it to Nothing) in the loop.

If you google you can find explanations of why not to instantiate an object in its declaration. Professional Excel Development has a good one. But I think your problem might have been in never setting it to Nothing. Not sure though.

What is the correct way to initialize objects in VBA?

2 Answers2