Classes in excel

Skip to first unread message

Darius Blaszijk

Jun 10, 2001, 7:08:11 PM6/10/01
Hello there,

I'm using excel 2000 and i noticed that it's possible to add class sheets to
a file. Is this the same as in Java and C++?? Could someone please provide
me some information on this, as there is no information in the helpfile
regarding this topic. What can I do with it and how can I declare classes??

Many thanks, Darius Blaszijk

Jack Silver

Jun 10, 2001, 7:28:30 PM6/10/01
As I use them, classes in Excel VBA are just like classes in Visual Basic 6.
You have encapsulation, but no inheritance or polymorphism. They are still
quite useful in that they help to make more maintainable code. Check out
almost any modern VB book for more information.

Darius Blaszijk <> wrote in message

Chip Pearson

Jun 10, 2001, 7:46:23 PM6/10/01
Hi Darius,

Broadly speaking, a class module in VBA works the same was as a
class in Java or C++. Classes are not quite as flexible or
versatile in VB/VBA as in C++, but the fundamental concept is
really the same.

You use a class module to define a "template" of your own object.
It may be easier to describe an example. Suppose you were
writing some sort of payroll application, and you want to have
some 'object' to represent an employee of your company.

You would then create a class module called CEmployee with
properties such as Name, Address, PhoneNumber, EmployeeID,
Department, and so on. You would also create methods for that
class, such as PrintPaycheck, and GiveRaise.

The class defines some 'generic' employee, but it doesn't define
any instance of a specific employee. Your application would have
some code to create specific instances representing 'real'
employees. Most likely, you would store each of these employees
in a Collection object. E.g.,

Dim Coll As Collection

Sub CreateEmployees()
Set Coll = New Collection

Set Emp = New CEmployee
Emp.Name = "Chip"
Emp.Address = "111 Main"
Emp.Department = 111
Coll.Add Item:=Emp

Set Emp = New CEmployee
Emp.Name = "Stephen"
Emp.Address = "222 Main"
Emp.Department = 222
Coll.Add Item:=Emp

Set Emp = New CEmployee
Emp.Name = "John"
Emp.Address = "333 Main"
Emp.Department = 333
Coll.Add Item:=Emp
End Sub

At this point, you've created 3 employees, each based on the
CEmployee class. These instances are all stored in the Coll
Collection object. When you create multiple instances of a
class, you MUST store those instances somewhere, and most likely,
you'll use a Collection object to do that.

When you've done that, you can loop through all those employees
with code like

Sub LoopIt()
Dim E As CEmployee
For Each E In Coll
Debug.Pring E.Name & vbTab & E.Address
Next E
End Sub

VB/VBA Classes are not as powerful as classes in other languages
such as C++ or Java. The really are all independent from one
other. You can't have base classes and inherited classes. The
closest you can get is implementing interfaces, via the
Implements keyword (e.g., Implements CEmployee) but this is
pretty trivial to what you may be use to in C++ or Java.

One of the primary advantages of class modules in VBA is that
they are the only place where you can declare a reference
WithEvents to another class, and get its event messages. For
example, you can get application's events with

Dim WithEvents App As Excel.Application

However, this MUST be within a class module. (See for more details.)
You can't trap events within a standard code module.

Moreover, in Excel 2000 and 2002, you can define and raise events
in your own classes. To do this, you use the Event keyword to
declare the event, and the RaiseEvent keyword to actually trigger
it. These work only within class modules (and, of course, these
events can be received only by class modules).

If you are familiar with classes and objects in other languages
such as C++ or Java, you will find the concepts quite the same in
VB/VBA. However, VB/VBA only gives you a limited version of
classes compared to these langauges. Despite that limitation,
they are a very powerful tool in VBA programming.

Under the hood, the Sheet modules, the ThisWorkbook module, and
the code modules behind user forms are all just class modules,
and you can add your own custom properties or methods to these

John Green's "Excel 2000 VBA Programmers Reference" book
describes class modules in some detail.

I have only scratched the surface of what this topic is all
about. Entire books could be written on the topic. Please post
back if you have any more specific questions.

Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC

"Darius Blaszijk" <> wrote in message

Reply all
Reply to author
0 new messages