Global Collections

Question

I have a collection that I have declared at the top of a module as

Public myCol as new Collection

I'm using a Sub to populate the collection, however, the data are lost when the Sub is terminated because it doesn't write to the global collection.

As the items were generated in the sub, I'm guessing they just have a local scope.

How can I populate the collection worldwide using a routine or function?

I would prefer to not have to pass objects if possible as I am populating multiple global collections in the single subroutine.

Answer 1

To store your public objects and expose them via properties, create a Standard module. The object can then be returned after the property getter has verified that it has been initialised.

See an example:

Private m_collection As VBA.Collection

Public Property Get GlobalCollection() As VBA.Collection
    If m_collection Is Nothing Then Set m_collection = New VBA.Collection
    Set GlobalCollection = m_collection
End Property

To call it:

YourModuleName.GlobalCollection.Add "Something"

or

GlobalCollection.Add "Something"