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.

--

Art

How do determine which one you want? By the key? Here's a short example of
how you might do it.

In a class module named Class1, key property and 3 other properties and a
ResetProps method.

Dim mKey As String
Dim mProp1 As String
Dim mProp2 As String
Dim mProp3 As String

Property Get Key() As String
Key = mKey
End Property

Property Let Key(sKey As String)
mKey = sKey
End Property

Property Get prop1() As String
prop1 = mProp1
End Property

Property Let prop1(sProp1 As String)
mProp1 = sProp1
End Property

Property Get Prop2() As String
Prop2 = mProp2
End Property

Property Let Prop2(sProp2 As String)
mProp2 = sProp2
End Property

Property Get Prop3() As String
Prop3 = mProp3
End Property

Property Let Prop3(sProp3 As String)
mProp3 = sProp3
End Property

Sub ResetProps()
mProp1 = ""
mProp2 = ""
mProp3 = ""
End Sub

In a standard module, a public collection variable, a sub to store your
class1 instances in the collection variable, a sub to create the class, and
a sub to work with the class

Public colClass As Collection

Sub CallInst()

Dim clsVar As Class1

Set clsVar = New Class1
clsVar.Key = "First"
StoreObject clsVar, clsVar.Key

End Sub

Sub StoreObject(cObject As Class1, sName As String)

If colClass Is Nothing Then
Set colClass = New Collection
End If

colClass.Add cObject, sName

End Sub

Sub WorkWithClass()

colClass("first").ResetProps

With colClass("first")
Debug.Print Len(.prop1), Len(.Prop2), Len(.Prop3)
End With

End Sub

The CallInst sub is just there to simulate whatever you're doing. Where
ever in your code that you create the class, you can call the StoreObject
sub to put it in the collection. You can get to collection elements using
the key, so you can use your class properties and methods directly from the
collection object as shown in WorkWithClass.

If you have a need to set some variables to empty string, you can set up a
method in your class, which is just a Sub procedure, to do that.

You can iterate through all your class instances by iterating through the
collection elements. If, for instance, you want all of you class instance
properties to be zero length strings,

Dim vItem As Variant

For Each vItem In colClass
vItem.ResetProps
Next vItem

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Art" wrote in message
...
Jamie,

Thanks for taking the time to put all of that information together. In my
particular case the number of things (members or objects) will vary from
month to month. Form what I've seen of the collection object, and from
Chip's post, it looks like I first have to structure my objects using a
class. Only then can I add them to my collection.

What the collection seems to be missing is "methods". For example I'd
like to take one instance (if I instantiated objects) and set 5 of the
properties to an empty string. I don't know how I'd do this with a
collection without iterating in my code whenever I want to do this - or of
course setting up a sub procedure that gets passes a parameter.

I was hoping I could create the class, instantiate as many times as I have
to, and then work with it that way. I would need to be able to "zero in" on
particular instances in order to carry out my work. The only way I know how
to do this (I think) is to iterate through the instances until I find the
one I want - then do whatever I need to do with that particular instance.

Thanks for the help

Art

"Dick Kusleika" wrote ...

How do determine which one you want? By the key? Here's a short example of
how you might do it. <snip

The key is usually the object's Name, isn't it?

As you could probably tell from my last post, I prefer the collection
code to be wrapped in a class too. Also, a parent class comes in handy
e.g. to run the same code on all the child objects.

I'll call my parent object a 'table' and each child object a 'column'
and I'll use a 'columns' collection class because a table can have
zero or more columns, making a total of three classes.

Columns aren't instantiated directly; instead the collection class has
an Add method that creates the child object for me. I'll borrow Dick
K's Prop1, Prop2 and Prop3 but make them writable only when creating a
column. I'll additionally have a ClearAll property at the parent
level i.e. to clear properties in all columns.

The extra code makes the code in the workbook simpler to write and
easier on the eye (can be made even nicer by make Item the default
property of the collection class and enable For Each loops but the
process is a bit controversial in this ng <g).


' ---<ThisWorkbook code module---
Option Explicit

Private m_Table As CTable

Private Sub Workbook_Open()

Dim oCol As CColumn

' Instantiate *only* the parent object
Set m_Table = New CTable

' Create columns by adding
m_Table.Columns.Add "ColA", "Norarules", "Hevitoxic", "Pipesagap"
m_Table.Columns.Add "ColB", "Luxasonic", "Katewudes"
Set oCol = m_Table.Columns.Add("ColC", "Tinatotac")

' Do things with the objects
MsgBox oCol.Name

MsgBox m_Table.Columns.Item(1).Prop1

MsgBox m_Table.Columns.Item("ColB").Prop2

m_Table.ClearAll

MsgBox m_Table.Columns.Item("ColB").Prop2

End Sub
' ---</ThisWorkbook code module---


' ---<Code in CTable class module---
Option Explicit

Private m_Columns As CColumns
Private m_strName As String

Private Sub Class_Initialize()
Set m_Columns = New CColumns
End Sub

Public Property Get Name() As String
Name = m_strName
End Property

Public Function Init( _
ByVal Name As String _
) As Boolean

m_strName = Name
Init = True
End Function

Public Property Get Columns() As CColumns
Set Columns = m_Columns
End Property

Public Function ClearAll() As Boolean
Dim lngCounter As Long
With m_Columns
For lngCounter = 1 To .Count
.Item(lngCounter).ClearAll
Next
End With
End Function
' ---<Code in CTable class module---


' ---<Code in CColumns class module---
Option Explicit

Private m_colColumns As Collection

Public Property Get Item(ByVal Index As Variant) As CColumn
On Error Resume Next
Set Item = m_colColumns.Item(Index)
End Property

Public Function Add( _
ByVal ColumnName As String, _
Optional ByVal Prop1 As String, _
Optional ByVal Prop2 As String, _
Optional ByVal Prop3 As String _
) As CColumn

Dim oColumn As CColumn

Set oColumn = New CColumn

On Error Resume Next

m_colColumns.Add oColumn, ColumnName

oColumn.Init ColumnName, _
Prop1, Prop2, Prop3

Set Add = oColumn

End Function

Public Property Get Count() As Long

Count = m_colColumns.Count

End Property

Public Function Remove(ByVal Index As Variant) As Boolean

On Error Resume Next
m_colColumns.Remove Index
Remove = (Err.Number = 0)
On Error GoTo 0

End Function

Private Sub Class_Initialize()
Set m_colColumns = New Collection
End Sub
' ---</Code in CColumns class module---


' ---<Code in CColumn class module---
Option Explicit

Private m_strName As String
Private m_strProp1 As String
Private m_strProp2 As String
Private m_strProp3 As String

Public Function Init( _
ByVal Name As String, _
Optional ByVal Prop1 As String, _
Optional ByVal Prop2 As String, _
Optional ByVal Prop3 As String _
) As Boolean

m_strName = Name
m_strProp1 = Prop1
m_strProp2 = Prop2
m_strProp3 = Prop3

Init = True
End Function

Public Property Get Name() As String
Name = m_strName
End Property

Public Property Get Prop1() As String
Prop1 = m_strProp1
End Property

Public Property Get Prop2() As String
Prop2 = m_strProp2
End Property

Public Property Get Prop3() As String
Prop3 = m_strProp3
End Property

Public Function ClearAll() As Boolean
m_strProp1 = vbNullString
m_strProp2 = vbNullString
m_strProp3 = vbNullString
End Function
' ---</Code in CColumn class module---


Jamie.

--

Jamie

The extra code makes the code in the workbook simpler to write and
easier on the eye (can be made even nicer by make Item the default
property of the collection class and enable For Each loops but the
process is a bit controversial in this ng <g).


Is it? I don't use it because I'm lazy. As soon as someone writes a
utility to add that attribute, I'll use if every time.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Dick Kusleika" wrote ...

make Item the default
property of the collection class and enable For Each loops but the
process is a bit controversial in this ng <g

Is it?

I got 'told off' by a regular about this, something about Notepad not
being part of VBA. We're friends again now :-)

I don't use it because I'm lazy. As soon as someone writes a
utility to add that attribute, I'll use if every time.

I have a ready-rolled collection class (text) that I use as a
template. You see, I'm lazy too <g.

Jamie.

--