I'm working on a VBA application for creating project plans. I have a
working prototype that uses msoRectangles in which the user can type
descriptions of tasks and task-inputs. But now I need to associate data
other than a simple text string, with each task. So I'm thinking of a new
class. Silly me!

I've read all that I could find and understand about new class modules.
Specifically, I've digested Walkenbach's chapter 29, "Excel 2002 Power
Programming with VBA." But I'm still far from my objective.

I'm not looking for a detailed lesson on how to create new class modules and
all the rest of OOP, because I don't expect anyone here to do my work for
me. But, frankly, I'm so far off the mark that I don't even know where to
find the information that I need. I don't even know if I'm asking the right
questions. So I'm looking for a little guideance from this group. Here's a
sample of what I think I know and what I think I need to know. If anyone
here can direct me to a book that I can understand (I'm really very new to
OOP), I'd be most grateful.

1) How do I create a new class that builds on existing msoRectangles and the
like?

2) If each instance of a new class is destroyed when the procedure that
creates it ends, how do I store the data for the object, so that it's there
when the workbook is opened at a late date? (I'm not even sure that this
question will make much sense to folks here.)

3) How do I define a new class, so that when the user changes the text in an
msoShape, the corresponding object in my new class also gets updated?

Finally, and this is what I really hope that somebody can help me with,
where can I read _understandable_ explanations of all this OOP stuff, with
code examples that illustrate the use of existing msoShapes as the basis for
new class definitions?

Tony Rizzo

Tony,
I'm no class expert but my take on all this is, in no particular order:
Any classes (or collections, objects, etc) that you create in code get
destroyed once they fall out of scope. Hence between closing and opening you
workbook, you will need to store all data somewhere and in Excel the obvious
place is a worksheet, probably hidden as that information is not necessarily
for the user to see.

There is the Shapes collection (of instances of the Shape class) which you
can iterate with something like:
Dim MyShape As Shape
Dim TempStr as String
For Each MyShape In ThisWorkbook.Sheets(1).Shapes
If MyShape.AutoShapeType = msoShapeRectangle Then
'Do something like create a new instance of your class
'TempStr=Application.WorksheetFunction.Lookup(MySh ape.Name,Arg2,Arg3
)
'MyRects.Add MyShape,TempStr,OtherInfo2
End If
Next

Your class would need some properties/methods etc
'Class MyRect --------------------------
Private mobjMyShape As Shape
Private mstrMyInfo1 As String
Private mlngMyInfo2 As Long
Private mvarMyInfo3 As Variant

Public Property Set MyShape(vData As Shape)
Set mobjMyShape = vData
End Property
Public Property Get MyShape() As Shape
Set MyShape = mobjMyShape
End Property

Public Property Let MyInfo1(vData As String)
mstrMyInfo1 = vData
End Property
Public Property Get MyInfo1() As Shape
MyInfo1 = mstrMyInfo1
End Property
-------------- End Class

You would need a collection of your MyRect classes, with an .Add, .Remove,
..Item etc.
'Class MyRects Notice the "s"---------------------------
Private mCol As Collection

Public Function Add(Optional Key As String, _
Optional ThisRect As Shape, _
Optional Info1 As String, _
Optional ......., _
Optional sKey As String) _
As MyRect
'Create a new object
Dim objNewMember As MyRect
Set objNewMember = New MyRect

With objNewMember
.Key = Key
Set .MyShape=ThisRect
.MyInfo1 =Info1
........etc
End With

'return the object created
Set Add = objNewMember
Set objNewMember = Nothing
End Function
-------------- End Class

You can then access properties by:
MyRects(KeyName).MyShape.Height=20 'Or some other property
or
If MyRects(KeyName).Info1="ThisComapnyName" Then.....

As for storing the additional info, a table of data with something like:
MyShape.Name Info1 Info2...

Classes can raise events, so for example, if the number of rectangles in
your collection reaches some limit, you raise a LimitReached event and react
accordingly.

However, depending what you doing, you may only need to do LookUps on the
table of data, rather than using classes at all.

Hope that helps.

NickHK

"Tony Rizzo" wrote in message
...
I'm working on a VBA application for creating project plans. I have a
working prototype that uses msoRectangles in which the user can type
descriptions of tasks and task-inputs. But now I need to associate data
other than a simple text string, with each task. So I'm thinking of a new
class. Silly me!

I've read all that I could find and understand about new class modules.
Specifically, I've digested Walkenbach's chapter 29, "Excel 2002 Power
Programming with VBA." But I'm still far from my objective.

I'm not looking for a detailed lesson on how to create new class modules
and
all the rest of OOP, because I don't expect anyone here to do my work for
me. But, frankly, I'm so far off the mark that I don't even know where to
find the information that I need. I don't even know if I'm asking the
right
questions. So I'm looking for a little guideance from this group. Here's
a
sample of what I think I know and what I think I need to know. If anyone
here can direct me to a book that I can understand (I'm really very new to
OOP), I'd be most grateful.

1) How do I create a new class that builds on existing msoRectangles and
the
like?

2) If each instance of a new class is destroyed when the procedure that
creates it ends, how do I store the data for the object, so that it's
there
when the workbook is opened at a late date? (I'm not even sure that this
question will make much sense to folks here.)

3) How do I define a new class, so that when the user changes the text in
an
msoShape, the corresponding object in my new class also gets updated?

Finally, and this is what I really hope that somebody can help me with,
where can I read _understandable_ explanations of all this OOP stuff,
with
code examples that illustrate the use of existing msoShapes as the basis
for
new class definitions?

Tony Rizzo