Dim As New 与 Dim / Set 有什么区别

在 VBA 中，我可以通过以下两种方式之一创建对象：

'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

第二种方式只是第一种方式的更详细版本，还是 myCol1 和 myCol2 对象之间实际上存在差异？

有几个关键的区别。你绝对应该更喜欢第二个Dim/Set方法。

Reason 1 - With As New，在调用该对象的属性或方法之前，不会创建该对象，但请看一下此示例，其中将对象设置为 Nothing，然后调用属性/方法会导致对象重新实例化自身：

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方法较慢，因为 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

Reason 3如果您的对象构造函数执行某些操作，则可能会出现严重的时间差异after你期望它，而不是当你显式实例化它时：

比较这段代码的结果：

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方法打印：

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

The Set y = New方法打印：

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