MSDN Link
http://msdn.microsoft.com/library/de...hatisclass.asp
Chip Pearson's site
http://cpearson.com/excel/codemods.htm
A previous post from Chip on the subject:-
There is far to much to say about Class Modules and Objects for a simple
newsgroup post, so I'll try to cover a few of the basics.
Basically, a Class is the definition of an Object. The word "object" is
deliberately vague. And object is anything that you want to design. It is
defined entirely (mostly) by its properties, methods, and events. In
Excel, there are hundreds of "built-in" objects, all defined by class
modules. The "class" is the definition of an "object". For example, a
Worksheet is an object. And there is a class module which defines just what
a Worksheet really is. There are various properties of a Worksheet object
(e.g., Visible). Properties simply define and set various attributes.
Think of properties as "Adjectives" which describe an object. An object
also has Methods. Methods are the "Verbs" of objects. For example, a
Worksheet object has an Activate method. This causes something to happen.
Finally there are Events. I can't think of a good grammatical analogy for
events. Essentially, Events are how an object tells the rest of the world
that something happened. For example, in a Worksheet object, there is a
Change event. This is the Worksheet object's way of telling the rest of
world, "Hey, look at me, I changed". The rest of the world can ignore that
event, or it may take action. But the world has been told that object has
done something (or had done something to it).
Now, you use Class Modules to create your own objects. Suppose you were
writing an application that was used for employee tracking. Using a class
module, you would define your own object called "Employee". This class
would define a single, generic, employee. With the DIM and SET statement,
you can create a specific employee, based on the "template" or "definition"
of a generic employee. The Employee class would have several Properties,
such as Name, Address, and Salary. It could also have methods, such as
Promote, GiveRaise, and Fire. In your application, the Promote method would
do the same things -- e.g., increasing the Salary property, updating a
central database, sending an email to another department to buy him a nicer
computer, etc. These actions are all the same whenever you Promote any
employee. By using a Class Module to define a "generic" employee, you only
have to write the code once. Then to work with a *specific* employee, you
just call the methods for that employee:
Dim ThisEmp As CEmployee
' more code
Set ThisEmp = New CEmployee
ThisEmp.Name = "John Smith"
' more code
ThisEmp.Promote
All of the code related to the Promote event is contained in the Class
modules (the definition of any employee), so you can simply call the Promote
method. Once you've defined the Class, you never have to worry about what
Promote actually does.
Here's another way to think about it. In the Worksheet object, there is a
PrintOut method. Within the PrintOut method, there is all the code that
actually formats the worksheet for printing, determines what printer you
have, and actually does all the work of printing the sheet. As a VBA
programmer, you don't have to worry about any of that. You simply call
PrintOut, and let that do all the work for you. You don't have to worry
about what sort of printer the user has, whether it can print color, and a
hundred other things. You just call PrintOut and let the Object do all the
work.
Class Modules let you create you own objects, or extend the functionality of
other, existing objects. They are very useful because they allow you to
write the code once, and then simply create new objects based on the class
(think of it like a blueprint for a house). It is write the code once, and
use it many times.
For example, I have a class module that extends the functionality of a
standard list box. The standard list box doesn't have a MoveUp method,
which simply moves the selected item one row up in the list. By using a
Class Module, I added a MoveUp method (as well as MoveDown, MoveToTop,
MoveToBottom, etc). I wrote that class one time. Now, whenever I need to
use "better" list boxes in my applications, I just use that Class. I don't
have to "re-invent the wheel" for every application I write.
This just scratches the surface of what a Class is and how to use them. If
you've ever heard the term "object oriented", Classes are the foundation of
this entire design philosophy.
-----------------------------------------------------
And from the same post, by Stephen Bullen:-
-----------------------------------------------------
Just to provide the opposite end of the spectrum to Chip's excellent
answer, class modules can also be though of as user-defined types (UDT) on
steroids. A simple UDT can be used to store related information about a
particular thing, such as Chip's employee:
Type Employee
Name As String
DOB As String
Grade As String
Salary As Double
End Type
If you wanted to do stuff with an employee, you'd use a normal procedure
somewhe
Sub RaiseEmployee(uEmp As Employee, sNewGrade As String)
'Validate Grade
'...
uEmp.Grade = sNew Grade
'Do stuff to work out new salary etc.
'...
End Sub
Sub FireEmployee(uEmp As Employee)
uEmp.Grade = "F"
uEmp.Salary = 0
End Sub
Sub SetSalary(uEmp As Employee, dNewSalary As Double)
'Validate Salary
'...
'Does new salary mean a new grade?
'...
End Sub
etc.
That's fine as far as it goes and you can create some great programs
without ever using class modules. The main thing wrong with it is that the
*data* for the object (i.e. the contents of your UDT) is separated from the
*actions* that are performed on the data (the RaiseEmployee and
FireEmployee subs). Hence, you have to be very careful that the same
validation is performed in each sub and that one sub doesn't alter the data
in a way that will cause another sub to fail; this is often the cause of
some of the hardest bugs to find - logic problems.
If you use a class module instead, you can include the validation and other
functionality *with* the data; to the extent that the data can *not* be
changed unless it's valid. You can think of it as that the 'Grade'
property of the Employee (for example) can validate *itself* and can refuse
to be updated, or it can know *itself* that when it changes to a valid new
grade, it needs to change the salary too.
In the example above, with two simple procedures, think what would happen
if we had to add another check before changing the grade, or introduce a
new action to be performed if the grade is changed (such as notifying their
manager). In the procedural approach, we'd have to change two or three
routines to handle it - i.e. wherever the grade is set. In the clas module
approach, it is simply another action to be performed by the 'grade'
property *itself* - none of the other code needs to know about it.
i.e:
Dim msGrade As String 'Data that only code in the class can 'see'
'Property to read the grade
Public Property Get Grade() As String: Grade = msGrade: End property
'Property to set the grade
Public Property Let Grade(sNew As String)
If Not sNew Is Valid Then
Err.Raise "Not a valid grade"
Exit Property
End If
'Grade is valid, so we can safely store it
msGrade = sNew
'Now what else do we need to do when the grade changes?
Select Case sNew
Case "F"
'Being fired, better ask for a redundancy slip
Salary = 0
Case "M"
'Being made a manager, better ask for a better car
'Increase the Salary too
Case "D"
'Being demoted, schedule for more frequent reviews
'Decrease the Salary
'etc
End Select
End Property
Public Sub Fire()
Grade = "F"
End Sub
Now, everything that needs to be done when the grade is changed has been
made an *integral* part of changing the grade - there's no way that the
grade can be changed by anywhere else in the system without those checks
and actions happening.
Really, though, it just boils down to a different design and development
style, and one that hopefully takes us further down the road of improved
code reuse, more stability and fewer opportunities for bugs to creep in.
The hardest thing to work out, though, is to decide which functionality
should be 'in' the class module and which should be on the outside, but
using the class module. For example, do we have a '.Fire' method within
the class, or a Fire(oEmp As Employee) procedure outside that just sets the
grade to "F"?
I find that I'm using class modules more and more; it's almost at the stage
where if I'm asked "Why use a class module", my reply is "Why not?"
--------------------------------------------------------
And then just for the hell of it, Chip's addendum after reading Stephen's
comments:-
Chip Pearson May 18 2000, 12:00 am
--------------------------------------------------------
Newsgroups: microsoft.public.excel.programming
From: "Chip Pearson" - Find messages by this author
Date: 2000/05/18
Subject: Just what is a Class Module?
Reply to Author | Forward | Print | Individual Message | Show original |
Report Abuse
"Stephen Bullen" wrote
class modules can also be thought of as user-defined types (UDT) on
steroids
Excellent. You can think of Classes as "UDTs with verbs", or UDTs that "do
things".
I find that I'm using class modules more and more; it's almost at the
stage
where if I'm asked "Why use a class module", my reply is "Why not?"
Very true. I've found that I do most things now in classes. Write once,
instantiate the class, and it is all done. And once I figured out the
"Implements" business, things got even better. (The help files are rather
poor, here. You need to read MSDN msdn.microsoft.com) I'm sure there is
some overhead involved in creating new objects, but RAM is cheap and the
reliability and maintenance considerations exceed the resource usages.
code reuse, more stability and fewer opportunities for bugs to creep in.
From a practical matter, this cannot be over-estimated or over-stated.. Far
too many bugs and problems are introduced by duplicated code. Classes
allow you to write the code once, debug it there, and only have to maintain
one procedure (or set of procedures). For "professional quality"
applications, this is absolutely essential. As Stephen points out, a
well-designed object model, accomplished with the use of well-thought-out
class modules, will tremendously reduce the potential for bugs in your
application.
If you have VB5 or 6, you have a Class Builder add-in. Learn it and love
it. It is not perfect, but can simplify the creation of your objects. You
can then Import these modules into your Excel VBProject.
The hardest thing to work out, though, is to decide which functionality
should be 'in' the class module and which should be on the outside, but
using the class module. For example, do we have a '.Fire' method within
the class, or a Fire(oEmp As Employee) procedure outside that just sets
the
grade to "F"?
Indeed, that is a "design philosophy" question. The answer is based on the
developer's requirements, not purely technical considerations. There is no
simple "right" way to decide this. It depends on what "Firing" an employee
really means. Suppose you have three categories of employees: Executives,
Supervisors, and Hourly. The process of "Firing" each type of employee may
be quite different. In Excel2000 (VBA6), you can Implement the right class
to have either a generic "Fire" method if that is appropriate, or a
particular "Fire" method for each type of employee, and automatically have
the "Fire" method for the appropriate Type of worker. This functionality
was added in Excel2000 (VBA6) and is not available in Excel97 (VBA5).
Admittedly, it is a bit confusing until you get used to it. But then,
you've go a great tool at your disposal. Chapter 9 in the VB6 Programmer's
Guide has some nice basic info about all of this. But to really learn about
this, just experiment and play.
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"Wazooli" wrote in message
...
For those of you who are obviously quite well versed in VBA, I only ask
how
did you become so familiar with it? Was this brute force learning,
supervised college courses, on the job experience, or something else. As
an
example, I am pretty sure that I need to start implmenting class modules,
but
I honestly feel completely overwhelmed. If there is some resource that
can
concisely and completely inform me as to their workings, I would
appreciate
someone pointing the way for me.
wazooli