Classes and Class Modules 101a
What are they
and what are they good for?
(Firstly, for the purists, for the sake of discussion we
will acknowledge but ignore the fact that many visual
classes are not persistent on access forms but are
instanced only when needed. They are merely 'painted on'
pictures the rest of the time. We are also only
discussing classes as implemented by MS Access and not
those of a more OOP nature.)
The popular definition for a class is an
'object' that encapsulates code and data and have been
variously described as 'cookie cutters' or templates. Not
very enlightening ... so lets have a look at Standard
module procedures and variables first.
Lets look at the plain old integer variable. A small
space of memory that can hold numbers. A=5. Print A. This
looks in the memory and prints the value. Not much to
understand. But imagine, if when setting A=5, it
automatically squared itself! ... or perhaps a string
variable that automatically converts to upper case! Now
we have code in there as well. (code we might
not have written). OK, Lets move on and sideways a bit.
A Standard Module.
Lets say you create a standard module that has two public
variables A and B, and one procedure, Square_It (B = A*A). To use it, you might
set A = 2 and then call Square_It. The result 4 can be read from
variable B. Easy.
Now lets say you have a piece of code that uses this Square_It procedure, but it in turn calls
another piece of code that also uses Square_It. You call Square_It and get your answer, then the code
moves on. If you re-read B
it may not the be same value that it originally was,
because the other later procedure (or form or report)
also used it.
look at an alternate behaviour. Take the Windows game
Solitaire. You run Solitaire and get half way through
losing the game<g> and stop. Leaving it open you
can run another and another. Each has a different
'state'. You can run many copies of Solitaire (or
calculator..whatever) and they are all in various states.
You can't easily do this sort of thing with code
in a standard module It exists only once, One copy!
Referring back to our Square_It function, you sort of
need to create new modules with new As & Bs on the
fly. This is where class modules come in.
A Class Module
With your newly created Class module, you still declare
your public Variables (A & B) and your public
Procedure (Public Sub) Square_It. You save the class
module as clsMyMath. Now when you use it you dimension a
variable M as type clsMyMath and use the Set and / or New
keywords to create it. You then ... M.A = 2, M.Square_It,
answer = M.B
Looks almost the same...but...see below for the magic.
Dim M1 As New
clsMyMath, M2 As New clsMyMath
M1.A = 2
M2.A = 44
So you will see from the above, we now have two
different Square_Its available.
Each with different answers that will be available until
the references M1 and M2 go out of scope. This is where
the 'cookie cutter' or template ideas
The cookie cutter metaphore is not a bad one. The key to
understanding this is that the code and attributes of
your class (the class module) are just blue prints. The
class has no reality until it is instantiated.
Dim M1 As New
Set M1 = New
It is at this point that the bubble of code, the object,
is created in memory. Some prefer to call it an object
now that it is 'real'. Object - class instance, it's all
Pointers, References and Variables with
The difference between Pointers (references) and common
variables are often overlooked in Access / VBA.
The M1 variable is just a 'pointer' or reference
to the object. It is not the object nor does it
contain the object.
Dim M1 As New
Set MI5 = M1
Set Cls23b = MI5
All the variables above, M1, MI5, Cls23b all point to the
same object. Changing a property on M1 will change the
property on all of them because they only point to the
object. Where as A = 5 and B=A remain two seperate
variables. Changing A or B after the fact will not affect
the other. In fact the correct (although now redundant)
method for assigning A is...
Let A = 5
where as the method for assigning MI5 is
Set MI5 = M1
Set and Let
This difference in assignment syntax is because of the
differences in the fundamental assignment type.
If I give you A, I give you the actual item. If I give
you M1 I give you a map of where to find a particular
instance of clsMyMath. The keywords ByRef and ByVal when used with procedures are very similar
in concept. See Access help on these two terms as they
are crucial to the way variables are handled
when passed to procedures.
Bear in mind, the concepts, terms and sytax mentioned
above apply every bit as much to forms, reports,
controls, recordsets and just about every other class
object you will strike in Access / VBA.
Now it is easy to imagine that the clsMyMath may in fact
be a very complex calculator that works out scaling
commission payments and, not only provides the amount of
the payment, but also supplies percentages, sales totals,
profits etc in various public variables (properties). It
is chock full of code inside. So when ever you need a
'Commission Calculator' you dimension a variable as
clsCommissionCalc, use it ... then throw it away, safe in
the knowledge that any other code running a
calculator will not interfere or be interfered with. It
is the same as loading a new Solitaire - it wont effect
the state of the existing one.
Dim CommCalc As New clsCommissionCalc
CommCalc.Salesman = "Terry"
Notice in the
above, we did not actually tell the class to calculate!
This is because we can use a Property Set procedure to
run calc code when the 'Salesman' property is set.
You can also pass a 'live'
class to another procedure.
Dim CommCalc As New clsCommissionCalc
CommCalc.Salesman = "Terry"
Call SomeOtherSub (CommCalc)
If TheCalc.Salesman =
So you see, they are just like little applications. You
see this sort of thing all through windows. It's just so
common you don't notice it. Most of the classes we
perceive have a visual aspect to them as well. Take the
command button you put on a form. This is a class. In
this case class CommandButton. It has properties and
methods. Properties are like public variables and the
methods are like public Subs. You can think of even these
button 'objects' or classes as small self-contained
programs as well. When you click the button it tells
Access 'I have been clicked!' via an event procedure.
When you change the font, Access tells the button
to change it's font. This is subtly different from Access
changing the buttons font. Without digressing into the
structure of Windows, take my word for it, you can think
of all these objects on the screen as separate little
programs sending messages (commands and data) to each
other. You can read more about messaging and an overview of the
way Windows works on MSDN. So returning to Access class modules, it
is not hard to imagine, if Access allowed it, and if you
had the API skills, you could draw small objects on the
Access form that could be considered part of your class.
You set properties of the class instance, the code in the
class instance sends and recieves Windows messages and
alters the image / object in the form and maybe does
other stuff as well.
You can see this at work to an extent with much of the
Access code available from Stephen
If you really want to look under the hood into Windows
and classes, and maybe 'roll your own' controls, visit
The Microsoft VB5 Control Creation
Edition site and
download the free version of same.
What else can they do?
In Access 2000 and later you can use the 'RaiseEvent'
keyword in your class. This allowes classes to interact
with your code (code on a form for instance) via event
procedures. Just like buttons or forms whatever. You
declare the events in the class declarations section...
Public Event CalcError(ErrorNumber As Integer, Cancel As Integer)
In your class code, if something happens, you might use
RaiseEvent to call an event procedure on your form code.
The form code can set the cancel to True. Back in your
class you can read intCancel to see if it was changed by
the form code.
RaiseEvent CalcError(intError, intCancel)
How do you get
these 'events' to come up in the form code?
If you are in the code window for a form, and you select
'Form' in the left hand combo at the top of the
code editor it will create a form load event (if it
doesn't exist). The right hand combo lists all
the possible events for the 'Form' object. Selecting one
will create the event procedure. Now!
if you dimension your class in the form module as ...
Dim WithEvents CommCalc As clsCommissionCalc
you will find CommCalc now appears in the left hand
combo. It also has a list of events in the right hand
combo and will create an event procedure when selected.
In this case you will see 'CalcError'.
An easy way to comprehend this if your head is spinning a
bit, it to imagine classes as advertising all their
properties, events, methods and constants to any
application wanting to use the class. This is how the
code on your access form 'knows' about what events are
available from your class. It is also how 'IntelliSense'
knows what to 'pop up' when you are writing code and the
object browser knows just about everything. So imagine a
conversation between VBA and the class as your are
writing the form module code.
VBA...'Ah! a new
class, do you have any events perchance?'
Class...'Yes, I do
in fact. The CalcError event'.
I'll list it in my events combo'.
that, by the way, you will let me know the forms
procedure that I must call when I fire this event?'
I'll give you the address as soon as Fred here sets the
event and writes the code and I compile up the form
module code. By the way, have you got your list of
properties and types handy?'
it is ol' boy.'
you. Well the codes written, he's about to open the form,
I better compile...lets get on with it shall we?'
rest for the wicked!'
Of course all this happens using the OLE 2.0 or Windows
Com framework for dlls and active-x classes, but you get
the picture. :-)
This can be also made to work the other way
around. Setting the event procedure properties
on a form to [Event Procedure] and having a class module
instance that references the form using a WithEvents clause in the declaration, will
cause the event procedure in the class module to fire
when the various form events are activated (Keydown,
MouseMove, Close etc). The combos for object and events
are present when working with your class module as they
are with the form, it's just in this case the form
variable in is the object combo (left hand) and when
selected you see Form! events in the event combo (right
hand). You can even have two or more event procedures for
the one event. One in the form module and another in your
In light of all this, it pays to think of the form's
code module as just another class module.
Access just hooks up the visual control class to a class
module, sets the GUID and presents it to you as a fait
accompli. (Although setting the 'Has Module' to No will
leave only the visual 'light weight' form). In fact, in
the 'olden days' of Access 1.0 and 1.1, there was
no form code module (let alone class
modules), only standard modules. We had to use
expressions such as =MyFunction() or a macro in the event
See http://www.papwalker.com/public/classsink.mdb for an example of form event
A Class also has it's own events, the Initialise and
Terminate events (like the form Open and Close) that
occur within the class module code, so when the class is
first created, the Initialise event fires. Here you can
open recordsets, set up variables etc etc... and when it
dies (goes out of scope) or is set to 'Nothing' the
Terminate event can close off and 'clean up' anything
that needs doing.
BTW. A form is a class. It is not
only possible, but easy to open 100 'Form1's. All 'clones' of
the original but each having a separate life and perhaps
being on different records etc. You open a Customer
Enquiry form and are half way through doing something,
you can open a another 'clone' look at something else,
close it, and return to your original. This is done in
almost the same manner as creating new instances of your
new class module object.
I hope this encourages novices to investigate
the possibilities of using and understanding classes
For forther insights on class modules see...
Custom Collections, Procedure Attributes and other
Subrange checking using classes
For further information on this article contact
peter walker email@example.com