What is the difference between Dim As New vs Dim / Set

Question

In VBA, I can create objects in 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 method just a more verbose version of the first method, or is there a difference between the objects myCol1 and myCol2?

Answer 1

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

Reason 1 . C, As Newan object is not created until a property or method of this object is called, but look at this example where setting the object to “No” and then calling the property / method forces the object to re-create an instance:

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

2 As New , VBA , .

-, , VBA :

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

3 , object - , , :

:

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

As New:

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

Set y = New:

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

Answer 2

As New . , , , . , , ,

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

, mdicQueryStringParams. , , .

, , Sub Class_Initialize to New .

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

, / reset , Clear, mdicQueryStringParams Nothing. Sub Class_Initialise . SO Mat Mug , VBA (!), Sub Class_Initialise .

(^ , New Clear, , , , )

As New resurrecting, . , - / , , .

, , .