ideal way of clearing out collections

Question

In my procedure, I have multiple collections that are templates for data. These collections will be stocked with data, called upon, and then emptied for repopulation during my repetitive process. I've read that I can do: set myCollection = new Collection to essentially clear it out for reuse.

I've also seen set myCollection = Nothing. Is there a preferred method for handling the clearing out of existing collections?

Answer 1

I have extracted related portion from an article which clarifies this point. Use Set means the collection will behave differently than when you set the collection to nothing. The next section explains this.

Removing All items from a Collection

To remove all items from a collection you can simply set it to nothing.

Set Coll = Nothing

An important point to understand here is that what this does depends on how you created the collection. As we saw you can create a Collection by declaring using New or by using Set and New. Let’s look at both types

Declaring Using New

If you set this collection to nothing then it will be set to the state where the “object is not set”. When you add a new item VBA automatically sets the Collection variable to a valid collection.

In other words, if you set the collection to nothing it will empty all the items. If you then add an item to the collection you will now have a collection with one item. This makes it simple to empty a collection.

The following code demonstrates this.

Sub EmptyColl()

    ' Create collection and add items
    Dim coll As New Collection

    ' add items here

    ' Empty collection
    Set coll = Nothing

    ' Add item
    coll.Add "Pear"

End Sub

A subtle point to emphasize here is that when you set the collection to Nothing it is not actually set to nothing. Therefore if you try to compare it with being it will not work.

Using Set and New

When you use Set to create a collection you must create the collection again if you set it to Nothing. In the following code after setting to nothing you must then set using new again. If you don’t do this you will get the error: “Object Variable or With block variable not set”.

Sub EmptyCollSet()

    ' Create collection
    Dim coll As Collection
    Set coll = New Collection

    ' Add items here

    ' Empty collection
    Set coll = Nothing

    ' SET TO NEW BEFORE USING
    Set coll = New Collection

    ' Add item
    coll.Add "Pear"

End Sub

Remove All – An Alternative Method

The following method will also remove all the elements of a collection but is a slower way to do it. The advantage is that is will work no matter which way you create the collection.

Sub RemoveAll(ByRef coll As Collection)

    Dim i As Long
    For i = coll.Count To 1 Step -1
        coll.Remove i
    Next i

End Sub

For details please refer The Ultimate Guide To Collections in Excel VBA by Paul Kelly