Declaring

A collection object variable must be created as a "new" collection unless you are going to assign it to an existing collection.
There are two ways you can do this in VBA
1) Placing a "New" keyword in the Set statement
2) Placing a "New" keyword in the declaration statement
This is often referred to as auto instancing.

Dim MyCollection As New Collection 

This method does no actually create the object.
This method is actually more like a compiler directive rather than a declaration resulting in slower code.
The following code is actually executed "behind the scenes" whenever the MyCollection variable is used

If MyCollection Is Nothing Then 
   Set MyCollection = New Collection
End If

It will do this everytime you use the MyCollection variable and in a large application this accumulates to a lot of unnecessary lines of code.
Worse than that this method does not allow you to test if the collection is ever Empty which can be a useful debugging and diagnostic tool.
The following IF statement will never be true.

If MyCollection Is Nothing Then 
'do something
End If

The reason for this is because the actual code that is run is the following:

If MyCollection Is Nothing Then 
   Set MyCollection = New Collection
End If
If MyCollection Is Nothing Then
'do something
End If

Typically collections are declared as module or global variables.


A collection can be created the same way other objects are created

Dim objCollection As New Collection 

You must use a Set fnName = colCollection
When you are returning a collection object from a function


Declaring a Collection

This is the preferred method

Dim MyCollection As Collection 
Set MyCollection = New Collection


© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext