Reason for setting variables in VBA

Question

I always had a question regarding setting Collections datatype in VBA

Usual way for Variables, example,

Dim userType1 as String

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

Dim userCollection As Collection
Set userCollection = New Collection

Why can't you just:

Dim userCollection As Collection

Why does Collections work differently in VBA?

Never understood this. Need some explanations on this.

Edit:

What is the difference between these?

userCollection = New Collection
Set userCollection = New Collection

In VB, `Collections` are an _object type_ whereas a `String` (or `Integer`, and several more) are _value types_. Objects are heap allocated and have to be initialised before use, hence the use of `Set xyz = New abc`, wheres value types are _typically_ allocated on the stack and can be used without initialisation. — Martin, Sep 10 '15 at 10:13
@MartinParkin that is an answer, not a comment... please write that as an answer... — Vogel612, Sep 10 '15 at 10:14
And for `Set`, see http://stackoverflow.com/a/17877644/11683 and http://stackoverflow.com/a/5966716/11683. — GSerg, Sep 10 '15 at 10:34
possible duplicate of [What is the difference between dim and set in vba](http://stackoverflow.com/questions/3872339/what-is-the-difference-between-dim-and-set-in-vba) — , Sep 10 '15 at 11:01
Not a duplicate since the user didn't know difference about objects and variables — Noldor130884, Sep 10 '15 at 11:34

score 3 · Accepted Answer · edited May 23 '17 at 12:29

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.

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 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...

Declaring `As New` in VBA is not declare+initialize. The variable is still `Nothing` after the Dim executes. But it will now [auto-instantiate whenever it is accessed](http://stackoverflow.com/q/8114684/11683) but is Nothing. — GSerg, Sep 10 '15 at 10:33
Good spot - took my eye of the locals window for a sec there :) I'll update my answer. — SierraOscar, Sep 10 '15 at 11:26

Reason for setting variables in VBA

1 Answers1

Linked