Advanced Access VBA Class Module Examples

> 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).

/gustav

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.

> 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.

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"

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
    SaveCustomer()
  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)
  BeginEdit
  Customer.CustomerID = value
End Property
Property Get Account() As String
  Account = Customer.AccountNumber
End Property
Property Let Account(value As String)
  BeginEdit
  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
  Else
    '/ 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

Select allOpen in new window

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.

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.

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.

mx

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.

/gustav

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.

"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.

mx

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) :-)

Select allOpen 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.

"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.

<<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.

Jim.

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.

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.

Thanks,
Bob