Help with class modules

[Paul Warshawsky]

I asked this question a few days ago and was referred to Chip Pearson's
site - but alas, I cannot find the solution to my problem there.

Let me briefly restate the problem. I am trying to write a Class Module to
create a Collection of "Breaths". I have already created a "Breath" object.
How do I tell it that when I use "Breaths(n)" in my code, I want it to
return a "Breath" object which is the n'th item of the collection "Breaths".
(Similar to Sheets(2) returns a Sheet Object representing the second sheet
in a workbook). I cannot simply use a "Collection" because the ".Add"
method has to make some changes to a table, so I must write my own "Add"
method. (Which is what I want to do it as a Class Module).

Please help!

Thank you

Paul

[Chip Pearson]

Paul,

You need to create a class module called "Breaths", which contains a private
collection object. In this class, create a method called Item, which simply
calls the Item method of your private collection variable.

Property Get Item(varIndexKey As Variant) As CBreath
Set Item = pCollection(varIndexKey)
End Property

where pColleciton is the name of the collection object in your class module.

You'll want to make this property the default property of the class, so that
you can call it as Breaths(N) instead of Breaths.Item(N). To make this the
default, export your class module out of VBA (from the File menu) and open
the file in NotePad, or the text editior of your choice. Enter the
following line as the first line in the Item Get property:

Attribute Item.VB_UserMemId = 0

In NotePad, your Item property will look like

Property Get Item(varIndexKey As Variant) As CBreath
Attribute Item.VB_UserMemId = 0
Set Item = pCollection(varIndexKey)
End Property

Close NotePad, and import the file back into VBA. Note that you won't see
the Attribute Item code in VBA.

Now, you can use syntax like Breaths(N) instead of Breaths.Item(N).

You'll also want to allow For/Each to be used with your collection class.
To do this, open the class module in NotePad, and create a property called
NewEnum as follows:

Public Property Get NewEnum() As IUnknown
Attribute NewEnum.VB_UserMemId = -4
Attribute NewEnum.VB_MemberFlags = "40"
Set NewEnum = pCollection.[_NewEnum]
End Property

Like the code for the Item property, you need to enter these Attribute
statements in NotePad, because VBE doesn't accept them when you type them
into the class directly. Import the module back into your VBA project.
Now, you can use For Each syntax like

Dim B As Breath
For Each B In Breaths
Debug.Print B.Name
Next B

Send me an email if you want an example workbook.

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

"Paul Warshawsky" <paulwar...@yahoo.com> wrote in message
news:#GNOznagAHA.1300@tkmsftngp05...

[Paul Sardella]

What are some good text-based and/or internet-based sources for learning about
custom class modules?

TIA

Paul Sardella