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.AccDb". 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
Event
s to be handled by the class.There is a caveat to this however, and that is that
Event
s 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
Event
s. 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 Event
s can be found in the code below at lines #17, #18, #96 & #97.Class/Object Properties
Coding
Property
s within a Class is done using the three Property
procedure types shown below. Each comes with an example definition & usage :Property Let
This allows a Class-User to assign a value to thisProperty
in the same way they would assign a value to any normal variable. This does not support objects - just as you can't simply sayA = B
with object variables (but have to useSet
).
Expand|Select|Wrap|Line Numbers- 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
Property Let
definition and that the variable is referenced within theClass
module aslngVar
rather thanY
.Property Get
This is essentially the reverse ofProperty Let
, except it also works for Objects.
Expand|Select|Wrap|Line Numbers- 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
Property Set
This is likeProperty Let
except for assigning Objects.
Expand|Select|Wrap|Line Numbers- 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.
Expand|Select|Wrap|Line Numbers
- 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
- '******************************************************************************
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
Event
s 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,
Restore
s 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 Restore
s the window again. The working basis is that Forms look better in Restore
mode while Reports look better Maximise
d. 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.