VBA Class Basics

[NeoPa]

Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all of the Reports, opened by it have been closed, and then makes that (calling) Form visible again. All Forms are shown in Restore mode while Reports are shown in Maximise mode. Some selections point to Forms that don't exist and show an error message. One of the selections allows one or more Reports to be run. The main Form (frmMainMenu) and the one that opens the Reports (frmReports) illustrate the fact they're aware the called Form or Report(s) have been closed by showing a Message Box on the screen immediately. It looks untidy as the previous object can still be seen on the screen at the same time, but it's there purely to illustrate the capabilities available.

Hopefully, the relatively simple nature of the process will allow you to understand the concept without too much fluff getting in the way.


Working Attachment
Please find attached, a ZIP file that contains the working example called "UKAUG202311.Ac cDb". Feel free to download it now and have a play with it; make sure you understand what it's doing. You may have no desire to do what it does. What I'm hoping to show is the concept behind it and how classes are used in this case - in order that you understand where, and how, they can be used to do things you want to do.


VBA Classes with Objects
Although VBA classes do not support the generally-understood concept of Encapsulation, they do allow for objects to be contained within the class code, as well as, importantly, their Events to be handled by the class.

There is a caveat to this however, and that is that Events can only be handled by the class, that already have Event procedures in the objects themselves. Thus you could have two different Forms used by two different instances of a class (or even the same instance at different times) where one could have its Event handled by the class but the other not, if the first form had a procedure for the Event but the second did not.


Class/Object Events
User Defined Classes can also define their own Events. Events support passing parameters and, by using the ByRef keyword, these can be used to pass information both ways. A class needs to both define the Event, as well as Raise it (using RaiseEvent). Be aware that IntelliSense is a little less helpful than usual with this statement (RaiseEvent)so you may have to type it out in full in order to use it. Examples of defining & raising the Events can be found in the code below at lines #17, #18, #96 & #97.


Class/Object Properties
Coding Propertys within a Class is done using the three Property procedure types shown below. Each comes with an example definition & usage :

1. Property Let
   This allows a Class-User to assign a value to this Property in the same way they would assign a value to any normal variable. This does not support objects - just as you can't simply say A = B with object variables (but have to use Set).
   

   Definition
   Private lngVar As Long
    
   Public Property Let Y(Z As Long)
       lngVar = Z
   End Property
    
    Usage (from outside of the Class module)
   Dim X As YourClass
    
   X.Y = 326

   Notice how the 326 is passed as Z in the Property Let definition and that the variable is referenced within the Class module as lngVar rather than Y.
2. Property Get
   This is essentially the reverse of Property Let, except it also works for Objects.
   

   Definition
   Private lngVar As Long
    
   Public Property Get Y() As Long
       Y = lngVar
   End Property
    
    Usage (from outside of the Class module)
   Dim lngA As Long
   Dim X As YourClass
    
   lngA = X.Y

3. Property Set
   This is like Property Let except for assigning Objects.
   

   Definition
   Private frmVar As Form
    
   Public Property Set Y(Z As Form)
       Set frmVar = Z
   End Property
    
    Usage (from outside of the Class module)
   Dim X As YourClass
    
   Set X.Y = Forms("frmMain")

clsForm Code
Here is the code from that class (clsForm). Feel free to copy it to somewhere more visible, and thus have it to hand, while you go through this section.
NB. When I refer to parts of the code I will do so via the line numbers as shown here below.

Option Compare Database
Option Explicit
 
'21/1/2004  Added Private Set & Public Get code for frmTo.
'21/9/2004  Removed ResumeTo functionality. _
            Now handled by the OnTimer() subroutine in the calling form _
            checking for (Visible) which indicates the called form is finished.
'24/2/2005  Added function Uninitialised to show if instance of this object _
            has yet been initialised with the callers info. _
            It also checks this before it tries to open a new form.
'31/3/2008  Added varOpenArgs as optional parameter to ShowForm.  Simply to be _
            passed directly to the opened form using DoCmd.OpenForm(). _
            Also set .OpenForm() to treat Cancel of the open as NOT an error.
'3/11/2023  Added the Closed Event which allows the calling code to handle the _
            associated Form being closed.
 
'Public Event Closed()
Public Event Closed(ByVal strName As String)
 
Private Const conUnInitMsg As String = _
                  "Object uninitialised - unable to show form."
 
Private frmParent As Form
Private WithEvents frmCalled As Form
 
Public Property Set frmFrom(frmValue As Form)
    Set frmParent = frmValue
End Property
 
Private Property Get frmFrom() As Form
    Set frmFrom = frmParent
End Property
 
Private Property Set frmTo(frmValue As Form)
    Set frmCalled = frmValue
End Property
 
Public Property Get frmTo() As Form
    Set frmTo = frmCalled
End Property
 
'Uninitialised returns True if frmFrom not yet initialised.
Public Function Uninitialised() As Boolean
    Uninitialised = (frmParent Is Nothing)
End Function
 
'ShowForm opens form strTo and hides the calling form.  Returns True on success.
Public Function ShowForm(strTo As String, _
                         Optional strFilter As String = "", _
                         Optional varOpenArgs As Variant = Null) As Boolean
    ShowForm = True
    'Don't even try if caller hasn't initialised Form object yet
    If Uninitialised() Then
        ShowForm = False
        Call ShowMsg(strMsg:=conUnInitMsg, strTitle:="clsForm.ShowForm")
        Exit Function
    End If
    Call DoCmd.Restore
    'Handle error on OpenForm() only.
    On Error GoTo ErrorSF
    Call DoCmd.OpenForm(FormName:=strTo, _
                        WhereCondition:=strFilter, _
                        OpenArgs:=varOpenArgs)
    On Error GoTo 0
    Set frmCalled = Forms(strTo)
    frmFrom.Visible = False
    Exit Function
 
ErrorSF:
    ShowForm = False
    ' If open is cancelled (either by user or code - 2501) then simply exit
    With Err
        If .Number <> 2501 Then
            Call ErrorHandler(strName:=strTo, _
                              strFrom:=frmFrom.Name & ".ShowForm", _
                              lngErrNo:=.Number, _
                              strDesc:=.Description)
 
            'Call .Raise(Number:=.Number _
                      , Source:=frmFrom.Name & ".ShowForm" _
                      , Description:=.Description)
        End If
    End With
End Function
 
'************************* Contained Object Method(s) *************************
'For these subroutines to be activated the contained object must have the
''On Close' property set to a valid subroutine within the contained object.
Private Sub frmCalled_Close()
    Dim strName As String
 
    frmParent.Visible = True
    Call DoCmd.Restore
    strName = frmCalled.Name
    Set frmCalled = Nothing
    'RaiseEvent Closed
    RaiseEvent Closed(strName)
End Sub
'******************************************************************************

Lines #17 - #18 define the Event named Closed which is exposed by the class. Line #17 shows the simpler usage and #18 is used to illustrate how you can pass information to & from the class while handling the Event.

Lines #20 - #21 define an error message used when the class is invoked inappropriately .

Lines #23 - #24 show two different ways to define objects in VBA. #24 is different & special as it allows Events from the object (In this case a Form obviously.) to be handled in the class itself. See lines #86 through #99 of this module for the format of a class's handling of (one of) its WithEvents objects. Notice the name of the Event Procedure includes the name of the specified object as declared in line #24.

Lines #26 - #40 show examples of Property Let/Get/Set procedures. These are explained above under Class/Object Properties. Notice how frmCalled, as defined on line #24, is how the object is referred to internally, yet when presented to users of this class, it is as the Property frmTo.

Lines #42 - #45 show the Uninitialised() Method. Any user of the class must set the value of frmParent using the frmFrom() Property before anything else can work sensibly. This function is provided to test that. It is Public so it can also be called from outside of the class module itself.

Lines #47 - #84 show the ShowForm() Method. This roughly mirrors the DoCmd.OpenForm( ) Method - at least the parameters I ever use. It explicitly handles the Form failing to open but passes any other errors onto the main error handler, which I don't cover here (as irrelevant). However, the code can be found in the attached example database if interested.

Fundamentally, it checks the class instance has been properly initialised then, if it has, Restores the window before opening the Form, using the parameters as passed. Once this has succeeded it sets frmCalled to this Form and hides the parent/calling Form.

Lines #86 - #99 handle the Close event of the called Form (internally referenced as frmCalled - hence the name of frmCalled_Close ()). Once it closes, this procedure ensures frmCalled is reset to Nothing and the parent/calling Form is made visible again. For good measure it Restores the window again. The working basis is that Forms look better in Restore mode while Reports look better Maximised. It also raises the clsForm.Closed Event which allows the calling code, if it handles that Event, to be informed that the called Form has just closed. In this example, for illustration only, it also passes the name of the Form that has just closed. Calling code will ignore this if no Procedure has been defined for this Event.


Conclusion
While the attached example database also includes a clsReport class, along with the clsForm one as shown here, the fundamentals of class usage, & I hope I've kept to the very basics in order to show how straightforward it is getting into the subject, are covered here.

Feel free to leave comments below, but please post any related questions separately (in the Access Questions Forum). Also, feel free to use any parts of this work. Attribution is purely voluntary.

[Nauticalgent]

Excellent contribution, more to come?

[NeoPa]

Thank you :-)

I'm working on a description & breakdown of the clsReport class - also included in the attached database (already).

[NeoPa]

For anyone interested, I've just reposted an updated version. This new version contains information on how to get a class to define & raise Events. Thanks are due to John Colby who presented recently at AUG Pacific on some of what he's been doing with classes.

I have also caught up with myself and posted the VBA Class Basics (Report Class) companion article that deals with the clsReport class found in this same database.