Art,

You can create a class module, say Class1, with the properties
you need, including a field called Key. Then, add the instances
of the class to a Collection object, using the Key property of
the class as the key to the collection. E.g,

Dim Coll As Collection

Sub AAA()
Dim C1 As Class1
If Coll Is Nothing Then
Set Coll = New Collection
End If
Do Until somthing
Set C1 = New Class1
C1.Field1 = "whatever"
C1.Key = "KeyName"
Coll.Add C1, C1.Key
Loop
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Art" wrote in message
...
Hi,

I need a collection for about 50 things. I'm assuming that a
sensible way to do this is to create a class where I can have a
bunch of properties for each of the instances, and instantiate it
about 50 times.

I'd like to create each instance with a "key" so that I can
refer to specific instances as necessary.

It may be that there's a way to create a data structure instead
of a class or to create a collection.

Can anyone suggest anything?

Art

Chip,

Thanks very much that was very helpful. Can I reference specific instances of the class without using the collection object? For example, I'm guessing that I could create a bunch of instances, then using a "for each" look for the one I want, and then extract the information. Assuming that would work, I'd still like to know if there's a better way.

Art

"Chip Pearson" wrote:

Art,

You can create a class module, say Class1, with the properties
you need, including a field called Key. Then, add the instances
of the class to a Collection object, using the Key property of
the class as the key to the collection. E.g,

Dim Coll As Collection

Sub AAA()
Dim C1 As Class1
If Coll Is Nothing Then
Set Coll = New Collection
End If
Do Until somthing
Set C1 = New Class1
C1.Field1 = "whatever"
C1.Key = "KeyName"
Coll.Add C1, C1.Key
Loop
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Art" wrote in message
...
Hi,

I need a collection for about 50 things. I'm assuming that a
sensible way to do this is to create a class where I can have a
bunch of properties for each of the instances, and instantiate it
about 50 times.

I'd like to create each instance with a "key" so that I can
refer to specific instances as necessary.

It may be that there's a way to create a data structure instead
of a class or to create a collection.

Can anyone suggest anything?

Art

Art wrote ...

Can I reference specific instances of the class without
using the collection object?

It depends on what your objects actually are (remember: software
development is about modelling reality).

The classic example is the Bicycle class. Should you have a collection
class called Wheels? The answer is no because all bicycles have only
two wheels. Best to have two members (e.g. Properties), FrontWheel and
BackWheel. On the other hand, a Spokes collection class is justified
because not all bicycle wheels have the same number of spokes. So the
general rule is, use a collection where the number of objects is
indeterminable and use individual members where the number is
predetermined.

However, this can be 'inconvenient' for the coder. If in your reality
you always have 50 distinct objects (I can only think you would be
modelling the Ways To Leave Your Lover <g), it may make more 'sense'
to have 50 individual object variables but easier to code if they
could be accessed collectively. For example, if I was modelling a card
game I might have a parent Deck object with an 52 internal instances
of my Card class, persists them in an array of type Card consisting 52
elements and probably have an enumeration to refer to the cards:
1ofSpades, 2ofSpades, and so on, plus methods to retrieve the next
card to be dealt etc.

If 'about 50 things' means the number of objects varies then a
collection is convenient because you can just keep adding objects as
needed, just like a stack. Note there are more complex container
objects available to the VBA programmer e.g. the Dictionary object
allows you to enumerate keys (a Collection does not and that's why I
assume Chip advised you to make Key a property of the class) and if
you want some advanced functionality such as filtering and sorting
then an fabricated (disconnected) ADO recordset works well (hint: for
objects set the Field.Type = IUnknown).

Even collections are 'complex' enough to justify wrapping in a custom
collection class. When you have a collection class, it's convenient to
also have a parent class. And before you know it you start building an
object hierarchy just like Excel's:

Application.Workbooks.Item("Book1").Worksheets.Ite m(1).Range("A1").Borders(xlEdgeLeft)

Application the parent object and is an instance of type
'Application', which has a Workbooks property, which returns a
collection class object of type 'Workbooks', which has an Item method
(i.e. wraps its internal Collection object's Item method), which
returns an object of type 'Workbook', which has a Worksheets property,
which returns a collection class object of type 'Worksheets' etc.

Workbooks is a collection because you can have an indeterminate number
of workbooks open within an Excel app. However Borders is a collection
even though the number of borders is predetermined and the Excel
developers have provided an enumeration for each border type (look in
the VBE's Object Browser for XLBordersIndex).

As I say, it all depends on what you are modelling.

Jamie.

--