Advanced Access VBA Class Module Examples

I would like to begin to use Class modules in Access 2010 VBA and have looked at simple examples in several Access VBA books and at online sites. Most of the Class Module examples that I've found are trivial and don't seem to help me understand how I could use classes. What I haven't found are more advanced examples.  I definitely want to create collections for several of my objects. For example, there are 8 different Funds each with different rules for collecting fees, with different start and end dates. A collection of Funds would be a big help to me when calculating Management fees for a specific period.
I would love to see a real world example of using classes for the type of challenges anyone would encounter.
Can anyone point me to a book or online site that has class module examples more complex than adding 1 to the number of students in a class and then building a function to retrieve it?

Over the past 5 years I have created applications to track investments, report on dozens of financial metrics, produce Investor Quarterly Reports/Graphs, and built interfaces with accounting and loan servicing systems for a private equity firm.  There have been a lot of very complex and challenging programs developed in MS Access with a MS SQL Server database. I want to make the code easier to maintain and to minimize complexity by using classes.

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
My personal take is that with Access, you should not bother.

VBA is object based, not object orientated and there's a big difference.   That from a programming perspective robs much of the power of using classes.

Classes do have their place and you can use them, but really there's only a few places where you'd want (or have to) use them.   Multiple instances of a single form is one.

Outside of the, you can emulate what classes do to a large extent with some code and a few utilities (in terms of changing the behavior of things) and do so with a lot less complexity and overhead.

 I know you'll get some comments that flat out disagree with me.  My only point would be if classes in VBA were that useful, then a lot more people would be using them.

 But if you look at many of the apps developed by developers, you'll find limited use of them and I know of only a couple that were done from the ground up using nothing but classes.

My .02
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
While it's out of print, Rockford Lhotka's Professional Visual Basic 6.0 Business Objects is a great book to use when learning about objects (i.e. classes) and how they interact with each other. I don't agree with his use of LSet and such, but otherwise it's a great tutorial for advancing your knowledge of class objects and such.

Even though it's targeted at VB, the concepts are applicable to VBA as well.

I use classes extensively, and for the most part prefer them over Standard Modules - except for those instances where I need a Function to be available throughout my project. Since moving much of my development to .NET, I've also found that my background in class modules made the transition (which is still ongoing) much easier.

Without the concept of class objects I would never have been able to create my largest project, which was a workflow management/QA system used by several aerospace companies.

There's also this one:

It's another good one, although it doesn't deal strictly with class modules.
Gustav BrockCIOCommented:
> I want to make the code easier to maintain and to minimize complexity by using classes.

But that will not be so. Jim is right; classes do fit a purpose and when it is optimal you will know it. One of the best examples is when using WithEvents.

If you really wish to work with OO, if for nothing else than getting more experienced doing so, you should leave VBA and move to C# (or perhaps VB.NET).

The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I will make some comments a bit later ... supporting the use of class modules, which I am totally in favor of, and is certainly on the the more cool features of VBA. Slammed at the moment.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
> I want to make the code easier to maintain and to minimize complexity by using classes.
I tend to agree with Gustav on this. The use of classes does not mean that your code will be easier (or more difficult) to maintain, or less (or more) complex.

In my opinion, class modules are best used when you need to work with multiple distinct objects. For example, an Invoicing system would need to work with a Customers and Invoices. A single Customer who could have multiple Invoices, and each Invoice could have multiple InvoiceItems. You could create a class for Customer, one for Invoice and another for InvoiceLineItem. You could also create a collection class of some form for Invoices and InvoiceLineItems.

Your class can also have various Properties that aren't readily available with straight recordsets and such. For example, I might need to know the CurrentBalance of my Customer. That would need to be calculated by summing all open A/R invoices for that customer, and possibly applying late fees, etc. While you could certainly do that in straight VBA code, if you need this sort of functionality in multiple places, and you need it for multiple items, you could add a CurrentBalance property to the Customer class, and calculate it when you create a new instance of that class.

As far as reuse between projects - yes, I reuse some Class Modules between my projects, but I also reuse several Standard Modules, and even a few Forms, Reports and Queries (which are really just Class modules themselves). So "code reuse" is not exclusively in the domain of Class modules. You can reuse code regardless of the vehicle in which it's maintained.
RCUllrichAuthor Commented:
Thanks for the great discussion... awaiting further "pro" comments from DatabaseMX (Joe Anderson).
I still want to see a good example using collections so I ordered the book Rockford Lhotka's "Professional Visual Basic 6.0 Business Object"
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Rocky's book is based on a video rental store (archaic in today's world, I realize). He goes through the process of creating Customers, Tapes, Rentals, etc etc and shows how everything works together.

I mentioned his use of LSet - essentially, that's how he passes data between his class modules. I was never comfortable with that (LSet is, essentially, a fixed-width file which is left-aligned) and instead used UDTs (in VBA) and Structure (in .NET) to store each classes unique data, and to provide "save" and "cancel" functionality:
Option Compare Database
Option Explicit

Event EditCancelled()
Event Dirty()
Event Saved()
Event Loaded()
Event CustomerNotFound()

Private Type tpCustomer
  CustomerID as long
  AccountNumber As String
  CustomerName As String
  CurrentBalance As Double
  StartDate As Double
  Terms As Double
  'etc etc
End Type

Private Customer As tpCustomer
Private Customer_Save As tpCustomer

Private fNew As Boolean
Private fDirty As Boolean
'/read-write Property IsNew
Property Get IsNew() As Boolean
  IsNew = fNew
End Property
Property Let IsNew(bIsNew as Boolean)
  If fDirty Then
  End If
  fNew = bIsNew
  Customer = New tpCustomer
End Property
'/ readonly property IsDirty
Property Get IsDirty() As Boolean
  IsDirty = fDirty
End Property
'/ read-write CustomerID
Property Get CustomerID() As Long
  CustomerID = Customer.CustomerID
End Property
Property Let CustomerID(value As Long)
  Customer.CustomerID = value
End Property
Property Get Account() As String
  Account = Customer.AccountNumber
End Property
Property Let Account(value As String)
  Customer.Account = value
End Property
'/ ReadOnly CurrBalance
Property Get CurrBalance As Double
  CurrBalance = Customer.CurrentBalance
End Property

Private Sub BeginEdit()
  If Not fDirty Then
    Customer_Save = New tpCustomer
	'/ Save the current state of the Customer data
	Customer_Save = Customer
	fDirty = True
	RaiseEvent Dirty
  End If
End If

Function CancelEdit() As Boolean
    Customer = New tpCustomer
	'/ reset the Customer structure back to the original saved in BeginEdit
	Customer = Customer_Save
	fDirty = False
	fNew = False
    RaiseEvent EditCancelled
	CancelEdit = True
End If

Function Load(CustomerID As Long) As Boolean
  Dim rst As New DAO.Recordset
  Set rst = Currentdb.OpenRecordset("SELECT * FROM Customer WHERE ID=" & CustomerID)
  If Not (rst.EOF and rst.BOF) Then
    '/ Found the Customer - load data
	Customer = New tpCustomer
	With Customer
	  .CustomerID = rst("ID")
	  .CustomerAccount = rst("Account")
	  '/ GetCurrentBalance is a routine somewhere that calculates the balance
	  .CurrentBalance = GetCurrentBalance(rst("ID")
	  ' etc etc
	End With
	Load = True
    '/ did not find the customer - raise event
	RaiseEvent CustomerNotFound
	Load = False
  End If
End Function

Function SaveCustomer() As Boolean
  If fDirty Then
    '/ code here to save the record
  End If
  SaveCustomer = True
End Function

Open in new window

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
@ RCUllrich ...

RE:"I would like to begin to use Class modules in Access 2010 VBA"
Question ... if you ever added Code Behind Form, ie put some code in one or more of the events on a Form and/or Controls on a Form ?

If so ... then good news: You are already working with Class Modules. Because, a Form (or Report) Module IS a Class Module. In fact, pretty much the only significant difference between a Form (class) module and an external Class Module is a Class Module has Initialize and Terminate events. However, the Load and Unload in a Form pretty emulate the Initialize and Terminate events.

So, it pretty much boils down to not that much difference. Did you know that in a Form, you can add your own custom Properties - just like you do in an external Class module?  I have done this many times.

Real World Example:
Ok ...let's take a relatively simple real world example. A few years back, a client that worked in a lab needed to have several timers that could run simultaneously, with Start, Pause & Stop buttons. So, he might need to start one timer, then while that one was running, start another, pause the first timer, start a third ... and so one.

What to do?

Well, this is a perfect application for a Class Module.  I built a fairly simple class module with the required functions, which also included the start, end, pause and elapsed times (Properties of the Class), etc.

There were a total of five timer sections in the UI. Each time another timer need to be started, the Start button would instantiate another instance of the timer class, which of course was completely independent of the other for timer sections.

Now of course I could ... have built a timer function in a regular VBA Function, and duplicated it N times, one for each timer section. But, for a while we were adding additional functionality to the timers. So, with separate function approach, I would have had to make the same change in *each* function, instead of - with the class module approach - make the change in *one* place.  And eventually,  we added two more timer sections to the UI - for a total of seven.

Note that timer section and corresponding controls (buttons, text boxes for time display, etc) were on a subform - contained on a main form. So, to add two more time sections ... just pop on two more (physical) instances of the subform.

REgarding this ...
" I want to make the code easier to maintain and to minimize complexity by using classes."

I would say in the context of the example above, the code was much easier to maintain (no duplicating Functions to add more timers) and overall complexity was minimized (all timer code - one place).

The fact the VBA Class Modules are not true 'object oriented' has never been an issue for me.  And the fact you can open multiple instances of the same Form for different reasons is something I have utilized many times, again reducing complexity.  I have many Class Modules in my Library MDA that are used for all sorts of things, including a full blown Multi-Select List Box management applet, used many places including a Report Setup tool.

So, in my opinion ... Class Modules are definitely one of more elegant entities of VBA.

I have never seen a case where class modules made an application more efficient or easier to understand.  Perhaps I have seen only poor examples (so far there have been 6 of them).  I picked up one where I was following threads 7 or more levels only to find a single line of code.  It was truly awful.

I think with proper use of subroutines and functions and a clear understanding of coupling and cohesion (concepts that don't seem to be taught any more), you will find you can do what ever you need without using classes.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I think the proper use of class objects would be right in line with the concepts of coupling and cohesion.

Of course, "proper use" is not often seen in the Access world, since often we're dealing with hobbyist programmers who found a shiny new toy to play with (i.e. class modules). I know that's what I did when I first figured them out - everything went into a class module, regardless of what it was. I've since figured out the best way to use them, and as I mentioned earlier I make extensive use of them.

That's not to say the methods I use are the only way, or even the best way. They're just the best way for me, at the time.

I do agree that misuse of class modules can cause some seriously deep code dives. I'm under contract with a big software outfit to provide "outside consulting", and that often involves reworking utilities that were created by other consultants. I recently had one that managed invoices and such external to the program, and it was a serious mess. Class modules for items like PhoneNumbers, EmailAddress, WebSite, InvoiceQuantities (yes, the actual quantity of an invoice line item ... go figure). Took me two days to decipher what the code was doing, and about 2 hours to fix it once I understood what was happening.
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
So, how would you build the Timer example I noted using ' coupling and cohesion' ... which I must say I've never, ever seen before mentioned in an Access forum.

Gustav BrockCIOCommented:
Yes, I noted that too, and wondered how successful it ran, single-threaded as Access or VBA is. Just with a single timer I've had serious trouble with a form, so I use timers only when any other route is closed.

You don't see concepts like coupling and cohesion discussed in Access forums because most Access developers are self taught.

For those who don't know what we are talking about, coupling refers to the connections between procedures and cohesion refers to the contents of a procedure.  So, we want loosely coupled procedures.  That means that when procedure A calls procedure B it isn't passing dozens of variables (either directly or as globals).  And we want highly cohesive procedures so we want them to do one and only one thing.  You wouldn't think of writing a procedure that calculates the outstanding balance of all accounts and sends emails to employees who haven't yet filled in their timecards and yet people do stranger things.
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"And we want highly cohesive procedures so we want them to do one and only one thing. "
Kind of sounds like a Class Module. Does one and only one thing (or is should) :-)

@ Gustav: Don't  recall there was even an issue running the timers. And of course, I was using the Windows API timer ... just to be clear.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Kind of sounds like a Class Module
You could say the same about a VBA Standard module. The difference, of course, is that you can have one and only one instance of a Standard Module, whereas you can have multiple instances of a Class object. I realize you know this, of course, but was pointing this out for other readers.

I think the concepts of Cohesion and Coupling have much more to do with the actual attributes, methods and processes of the "entity", regardless of the container. A tightly coupled Standard Module, with Functions/Subs that required a LOT of knowledge of their outside data points in order to function, would be no less (or more) coupled than a Class module that required the same. You'd still have the same issues, except that you could have X number of tightly coupled classes, whereas you could only have a single instance of the tightly coupled Module.

I think Joe's Timer class is a good example of a utility or "helper" class, but it doesn't really tell us much about the way classes are used with data, or how they interact with each other, or attributes that are good or bad about them. Granted I see the usefulness of them - you can run multiple, independent timers - but it would really have no interaction with other objects, like (for example) an Invoicing system, or a CRM system.
Kind of sounds like a Class Module. Does one and only one thing (or is should) :-)

Open in new window

Code is only as good as the person who writes it.  Class modules are worse than most because they are not well understood and people use them inappropriately.  Despite that, the worst Access applications I have encountered were written not by novices but by the most experienced programmers who were forced to lower themselves to use Access (at least that's what they thought).  The problem seems to be that folks who know how to code think that they should write code to control everything.  They never take advantage of queries, or the properties of forms and reports or even collections.  As far as I'm concerned, they shouldn't be using Access at all.  They should be using whatever they think is better since they clearly think Access is inferior.
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"but it doesn't really tell us much about the way classes are used with data"
Well, I guess in that context, most of the class modules I have are helper/utility classes - not that it changes anything.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<You could say the same about a VBA Standard module. The difference, of course, is that you can have one and only one instance of a Standard Module, whereas you can have multiple instances of a Class object. I realize you know this, of course, but was pointing this out for other readers.>>

 While that's true, you easily can write routines so that they work against an array in order to support multiple instances.

 Joe's timer class is a good example.  You could easily write that with a couple of routines.  Probably not quite as nice and neat, but you could do it.

  This is where classes do have their place in VBA; when you have multiple instances of something and they all work exactly the same.   Timers, Printers, Forms, progress bars, etc.  

 Beyond that however, I find using classes a lot of overhead for very little gain.   There's only partial inheritance in VBA and therefore no sub-classing.  With that, much of the power of classes is robbed.

  Say I have a text control, which can be used for general text entry, entry for currency amounts, or date/time values. In VBA, I would need to write three classes for that because I can't write one base class and subclass for the different behaviors I need.

  Now I want a change in the way all text boxes look; I'm left stuck modifying each of the three classes. So much for code being in one place.

 And hooking up all your text controls to a class takes effort.  But I can achieve the same thing simply enough by writing a standard procedure and then updating the appropriate property of the controls.

  That's why I find class use in VBA limiting.  Access/VBA also doesn't do multi-tired apps very well.  If it did, classes might be more useful.

  Actually, I'd really like to know how that guys project came out (and his name escapes me at the moment).  He came in (and this goes to Pat's points) thinking of Access like other developer tools, and wanted to do everything from the ground up using classes.

  I wonder if at this point he feels it was worth the effort or not.   Judging by his comments towards the end, I think probably not.

I wonder if at this point he feels it was worth the effort or not.   Judging by his comments towards the end, I think probably not.
I'm sure he's out there telling anyone who will listen how inferior Access is because it doesn't work the way he expected it to.  I came to Access from COBOL so I was an experienced programmer to begin with and I can tell you truthfully, it was a huge paradigm shift for me to go from procedural code to event driven code.  But it never occurred to me for one minute that I should make Access more like COBOL.  I just got on with it and once I got the hang of all the stuff Access actually does for you, I was hooked.  I could create a form in minutes that would have taken hours or days to code if I had to do it all by hand in COBOL.  Even after 20 years I still sometimes find it difficult to analyze a problem because of the many places I have to look to see the various properties and code.  I would really like to see that better organized so I don't have to click into every single control to see if it has conditional formatting for example but that's a complaint for Microsoft and I send it once per year since the only way to get them to respond is for people to tell them about the good and the bad.

There is so much more good than bad with Access that I am always mystified by people who want it to work like other environments.  They forget that the reason they are using Access is because it is a RAD tool and so it shouldn't work like everything else.  In fact, more things should work like it which accounts for the proliferation of tools to create web apps.  The basic environment sucks and is so hard to work with that people keep inventing tools to make it easier.   The key to being productive with Access is to go with the flow.  Learn how properties and events work.  Use queries rather than code to do bulk updates whenever possible and then relax for the rest of the day while your coworkers struggle with the various .net languages and their classes.
RCUllrichAuthor Commented:
Thanks for the lively discussion. I have purchased Visual Basic 6 Business Objects (by Rockford Lhotka) and will study it to determine if I want to create class objects.

As Jim Dettman suggested I also use arrays extensively. My application has several modules that collect data and put it into an array. These single purpose modules have one or more functions to retrieve the various attributes of the data.
This method improves performance for many data-intensive consolidating reports because there is only the initial i/o to get the data instead of thousands of queries to the database to retrieve data.

All Courses

From novice to tech pro — start learning today.