What's the difference between Dim As New vs Dim / Set

In VBA, I can create objects one of two ways:

'First way
Dim myCol1 As New Collection

'Second way
Dim myCol2 As Collection
Set myCol2 = New Collection

myCol1.Add "AAA"    'Works
myCol2.Add "BBB"    'Works as well

Is the second way just a more verbose version of the first way, or is there actually a difference between the myCol1 and myCol2 objects?

Solution 1:

There are several key differences. You should definitely prefer the second Dim/Set approach.

Reason 1 - With As New, the object doesn't get created until a property or method of that object is called, but look at this example where setting the object to Nothing, and then calling a property/method causes the object re-instantiate itself:

Sub ShortcutInstantiation()

  Dim x As New Collection

  x.Add "FOO", "BAR"
  Set x = Nothing

  'This line implicitly recreates a new Collection
  Debug.Print x.Count

  Debug.Print x Is Nothing 'Prints False

End Sub

Sub SafeInstantiation()

  Dim x As Collection
  Set x = New Collection

  x.Add "FOO", "BAR"
  Set x = Nothing

  'Throws error because x is nothing
  Debug.Print x.Count

End Sub

Reason 2 The As New approach is slower, because VBA needs to check if it has instantiated the object before every single property or method call.

Look at this pseudo code to see what VBA is doing behind the scenes:

Sub NotSoShortcutInstantiation()

  Dim x As New Collection

  If x Is Nothing Then Set x = New Collection
  x.Add "FOO", "BAR"

  If x Is Nothing Then Set x = New Collection
  x.Add "FIZZ", "BUZZ"

  If x Is Nothing Then Set x = New Collection
  x.Add "CAR", "DOOR"

  If x Is Nothing Then Set x = New Collection
  Debug.Print x.Count

End Sub

Reason 3 There can be critical timing differences if you object constructor does something after you expect it to, rather than when you explicitly instantiate it:

Compare the results of this code:

Sub InstantiationTiming()

  Dim foo As String

  Dim x As New Class1
  Debug.Print Format(Now(), "hh:mm:ss") & " x should be ready"
  foo = x.foo

  Dim y As Class1
  Set y = New Class1
  Debug.Print Format(Now(), "hh:mm:ss") & " y should be ready"
  foo = y.foo

End Sub

The As New approach prints:

06:36:57 x should be ready
06:36:57 Class Initialized

The Set y = New approach prints:

06:36:57 Class Initialized
06:36:57 y should be ready

Solution 2:

The As New construct has legitimate uses. In a class, with a module level variable when you do not know which method will be called first then it saves some lines of code. So some code snippet I have lying around in a class is given here

Option Explicit

Private mdicQueryStringParams As New Scripting.Dictionary

Function SafeItem(ByVal sKey As String, ByRef pvItem As Variant) As Boolean

    If mdicQueryStringParams.Exists(sKey) Then
        pvItem = mdicQueryStringParams.Item(sKey)
        SafeItem = True
    End If

End Function

Imagine very many more methods that rely upon an initialised mdicQueryStringParams. You'd have to write guard code to ensure that it was created in all of these methods.

Now at this point you're saying but you can use Sub Class_Initialize to New up upon class creation. Like this

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

But suppose I want to recycle/reset part of the class's state then I could write a Clear method which sets mdicQueryStringParams to Nothing . In this case Sub Class_Initialise won't run again. Here on SO Mat's Mug has taught me that Static classes are possible in VBA (thanks!) so sometimes Sub Class_Initialise will only run once.

(^ Admittedly I could set it to a New instance in the Clear method, yes, yes, I know, I know)

The point is with the As New syntax you get a resurrecting variable as well as auto-initialisation. Surely as developers this is another technique/pattern in our toolbox that we should exploit and not ban.

In truth, I use it infrequently but I just don't like banning stuff.