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

Question

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?

Answer 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