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.

--