在VBA中设置变量的原因

I always had a question regarding setting Collections datatype in VBA

我总是有一个关于在VBA中设置Collections数据类型的问题

Usual way for Variables, example,

变量的常用方法,例如,

Dim userType1 as String

But for Collections, we need to declare it as such:

但是对于Collections,我们需要声明它:

Dim userCollection As Collection
Set userCollection = New Collection

Why can't you just:

你为什么不能这样做:

Dim userCollection As Collection

Why does Collections work differently in VBA?

为什么集合在VBA中的工作方式不同?

Never understood this. Need some explanations on this.

永远不明白这一点需要一些解释。

Edit:

What is the difference between these?

这些有什么区别?

userCollection = New Collection
Set userCollection = New Collection

1 个解决方案

#1

Because it's an Object

因为它是一个对象

Dim (or Dimension) will allocate a certain amount of memory for whichever data type is declared (or a Variant if no type is declared) and allow the appropriate IntelliSense menus to become available. This is pretty much the be-all and end-all of Dim.

对于声明的数据类型,Dim(或Dimension)将分配一定量的内存(如果没有声明类型,则为Variant),并允许相应的IntelliSense菜单可用。这几乎是Dim的全部和最终目标。

So for an object, you can allocate memory for it by using:

因此,对于对象,您可以使用以下命令为其分配内存:

Dim x As Collection

But this doesn't actually create the required object, this is where Set and New come in.

但这实际上并没有创建所需的对象,这就是Set和New的用武之地。

Set x = New Collection

You can actually declare like so:

你可以这样声明:

Dim x As New Collection

Which will allow the variable to auto-instantiate when it is accessed^†

这将允许变量在访问时自动实例化†

So for example:

例如:

Sub Foo()
    Dim x As Collection
    x.Add "Test" '// <~~ Error, because the actual Collection object doesn't exist yet.

    Set x = New Collection '// <~~ Create a 'New' collection object and 'Set' it's
                           '//     reference to the memory allocated for 'x'

    x.Add "Test 1"         '// <~~ Works fine.

    Dim y As New Collection       
    y.Add "Test 2"         '// <~~ Works fine, because the Collection
                           '//     object will now instantiate itself

End Sub

^†Credit to GSerg for clarifying the auto-instantiate feature as I initially got this wrong...

†归功于GSerg澄清自动实例化功能,因为我最初弄错了...

#1