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
© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext