Class Modules in Access VBA

[Sreedhar]

My experience with VBA in Access is 3 months long. I never used a Class
Module, though I got somewhat comfortable with Standard Modules. I heard
these are great tools to engineer an Application.
Can someone enlighten me about-

*why should I use them ?

*Where can I learn more about these ?
(The book I have on AccessVBA doesn't deal much with Class Modules except
a brief explanation.)

 

[Scott McDaniel]

My experience with VBA in Access is 3 months long. I never used a Class
Module, though I got somewhat comfortable with Standard Modules. I heard
these are great tools to engineer an Application.
Can someone enlighten me about-

*why should I use them ?

I often use them to group together complex functions. For example, most of my apps make use of a 3rd party email
library, and I've built a class module which exposes the functions and such of that library to my app. The library can
connect to an SMTP server in several ways (authenticated, non-authenticated, etc) so to save myself the trouble of
determing which method to use, I built a class with a Connect method, and the class module's code in that method
examines the data present and makes an intelligent decision as to exactly how to connect. Now I can use this class
module without re-examining the somewhat arcane (to me, anyway) useage of the 3rd party library each time I import that
class into my new project; I just call the Class Modules Connect method and (hopefully) everything works as planned! I
do the same thing with Access User Level Security; I have a Class module that allows me to easily manipulate Users,
Groups, and Permissions without using the Access interface (so my enduser Admins can do this easily, without having to
learn anything about ULS).

If you deal with Digital Signatures for your codebase, scripts that could be used for malicious things (modifying the
registry, manipulating the File structure, etc) could be put into a class module for enhanced safety. See this link for
info, especially the "Things to do before signing a file or VBA Project" section:

http://office.microsoft.com/en-us/assistance/HP010397921033.aspx?mode=print

If you're using Access in bound mode, then you won't really use Class Modules for data or object handling, since Access
does this for you.

If you're using Access in un-bound mode, however, you can use class modules to handle your data needs. For example, you
could use a class module to validate data before it goes into the database.

You can also use Class modules to "encapsulate" complex logic, if needed. If, for example, you have an Ordering system,
you may need to build an order based on (a) the OrderHeaderDetails, (b) the OrderLineItems details, (c) Inventory
details (do I have these items in stock?), (d) Customer Details (who is this order going to?), etc etc ... using class
modules, I could build a class modules for Inventory, Customers, OrderHeaders, and OrderDetails ... I could then build
an Orders class module which interacted with all those different modules. Perhaps my main Orders class had a method
named "PostOrder", which could (a) mark the needed Inventory as "unavailable", (b) Fill in the OrderLineItems, calculate
the cost, and report that back to you (c) Fill in the OrderHeaderDetails (such as OrderDate, Shipping Method, etc) and
(c) add a new record to an Invoice table, perhaps, showing that a new Order has been placed and needs to be invoiced and
(d) automatically send an email to the customer with order details. Of course all this could be done with
code-behiind-forms, but class modules, to me, are a more elegant approach.

You can also pass Class modules around to Forms and Reports, if needed. For example, in one app I have a search utility
for WorkOrders that allows users to enter multiple criteria, return the results, and doubleclick on a Listbox to show
the underlying document. I simply build my clsWorkOrders class module on my search form, open my WorkOrders form, and
pass that class to a Property of my WorkOrders form. The property in the WorkOrders form accepts my class module and
fills the form appropriately.

*Where can I learn more about these ?
(The book I have on AccessVBA doesn't deal much with Class Modules except
a brief explanation.)

One of the best I've read on the subject is Rockford Lothka's "Visual Basic 6 Business Objects". He goes into great
detail about the subject. Check the online stores for pricing.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Guest]

Class modules have a number of important theoretical and
practical advantages in certain types of hierarchically defined
data structures.

But in practice, they are mostly used as a way of designing
and handling code interfaces. They come to VB from a
language that had no satisfactory way of structuring code
interfaces, (and was often used for handling complex hierarchically defined
data structures.)

But VBA does have a satisfactory (though not brilliant)
way of structuring the visibility and availability of functions
and data, and is very rarely used to define and handle
complex hierarchically defined data structures. So there is
almost never any technical reason for using a class structure
instead of a standard module.

But, many people are more familiar with the new style
than with the old style. Particularly when they design
complex interfaces, they are more comfortable with a
code interface designed as a class structure. This is
even more important if you expect your code interface
to be used by other people.

Also, if you are thinking of ever learning other languages,
like perhaps VB.Net, you will find the new interface style
more useful than the old interface style. VB.NET doesn't
even support all of the necessary features for the use of
standard modules in code interface design: for example,
there are no local static variables.

(david)

 

[Sreedhar]

Thank you Scott, and

Thank you David,

for your valuable tips on the topic.

 

[chris.nebinger]

Well, I'll add my 2 cents...

Except for projects that I did for exploration of objects in Access,
there's only been a handful of times that I really used them. One of
those times is a wrapper for the FileOpen API found here:
http://mvps.org/access/api/api0001.htm The wrapper is much cleaner,
and I just need to import the class into a new module to be up and
running. The other time was to recreate the progress meter that is on
the bottom left of the screen that NO user ever sees. Another was a
built-in calculator, and a calender date picker replacement.

I've found that for times where I have to have functionality that does
not tie to a specific project, then creating a class is often a good
plan. The class contains all the code that is needed, and requires no
references to anything else. That way it is easy to import into a new
project and use it right away.


Chris Nebinger