You are doing early binding which is a very bad idea. Check out the link
provided. What you wnat to do is late binding...

dim col1 as collection

set col1 = new collection

http://www.cpearson.com/excel/variables.htm
Look at "Don't Use The New Keyword In A Dim Statement"

--
HTH...

Jim Thomlinson


"John" wrote:

Hi there,

I'm trying to create a variable number (between 1 and 4) of collections, but
I don't know what that number will be in advance. Is there a way of
creating (and declaring) collections at runtime?

So far my solution has been to declare four collections at the beginning of
the code ie

Dim col1 As New Collection
Dim col2 .....

and then setting the unwanted ones to Nothing once the requirement can be
counted, however this seems a bit wasteful.

Any guidance?

Best regards

John