Hi all

On XL2003 I have written a function that builds 100,000 instances of
an object and stores them as a collection. Other functions then can
access this collection and perform further tasks.

Unfortunately this can take up a lot of memory, so in order to make
the best use of my memory I would like to be able to refer to a unique
identifier for the collection and first check if it exists. Then, if
it does exist I wish to set it to nothing before running the rest of
the function.

This is my first time using class modules. In the past I have always
used the type declaration to create objects and then stored them in an
element of a global, public array. In this way, if I then rebuilt
those objects I would end up just saving the data in the same element
and thus avoiding this memory problem.

Can anybody suggest the most efficient method to achieve my goal?

Many Thanks
pinkfloydan

You can Set the Collection object to Nothing to complete empty it out. That
will destroy the contents of the Collection and invoke the Terminate event
of every object stored in the Collection. E.g.,

Public Coll As Collection
Sub ClearCollection()
Set Coll = Nothing
End Sub

You might also consider moving from a Collection object to a Dictionary
object. In my experience, a Dictionary is considerably faster than a
Collection object, especially with large numbers of objects. Also, the
Dictionary object has an Exists property that will return True of False
indicating whether a specific key exist in the Dictionary. This faster and
cleaner than using an On Error to test whether a key exists in a Collection.

I have a variety of procedures for working with Collections and Dictionaries
at http://www.cpearson.com/excel/Collec...ctionaries.htm .

This is my first time using class modules.

I have some notes regarding class modules at
http://www.cpearson.com/excel/ClassModules.htm , which you might useful.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"pinkfloydfan" wrote in message
ups.com...
Hi all

On XL2003 I have written a function that builds 100,000 instances of
an object and stores them as a collection. Other functions then can
access this collection and perform further tasks.

Unfortunately this can take up a lot of memory, so in order to make
the best use of my memory I would like to be able to refer to a unique
identifier for the collection and first check if it exists. Then, if
it does exist I wish to set it to nothing before running the rest of
the function.

This is my first time using class modules. In the past I have always
used the type declaration to create objects and then stored them in an
element of a global, public array. In this way, if I then rebuilt
those objects I would end up just saving the data in the same element
and thus avoiding this memory problem.

Can anybody suggest the most efficient method to achieve my goal?

Many Thanks
pinkfloydan

Thanks Chip, I'll look at that now

Best Regards
pinkfloydfan