VBA Class Modules: Write Cleaner Access Code
Class modules bring object-oriented structure to VBA. Learn how to use them in Access to reduce repetition, improve maintainability, and write code that scales.
Most Access developers write all their VBA in form modules and standard modules. It works — until the codebase grows and you find yourself copying the same logic into five different forms, hunting down bugs in duplicated code, and dreading any change that touches shared functionality.
Class modules are the answer. They have been in VBA since Access 97, but most developers never touch them. That is a missed opportunity. Here is what they are, why they matter, and how to start using them today.
What Is a Class Module?
A class module is a template for creating objects. Instead of writing a procedure that operates on a bunch of loose variables, you define a class that bundles data (properties) and behavior (methods) together.
Think of it like a blueprint. You define the blueprint once, then create as many instances as you need — each with its own state, each using the same methods.
In Access VBA, you create a class module from the VBA editor: Insert > Class Module.
Why Bother in Access?
Access databases tend to grow organically. What starts as a simple form becomes a 2,000-line module with procedures that do three different things and variables named x, temp2, and strFinalValue.
Class modules help you:
- Encapsulate logic — keep related data and behavior together
- Reduce duplication — write a method once, use it everywhere
- Improve readability — code that reads like English is easier to maintain
- Enable reuse — drop a class into another database without untangling dependencies
If you are planning a migration to SQL Server or Power Platform, well-structured VBA is also much easier to translate. Spaghetti code in a class module is still spaghetti, but at least the boundaries are clear.
A Simple Example: A Customer Class
Here is a minimal class module called clsCustomer:
' clsCustomer class module
Private mstrName As String
Private mstrEmail As String
Private mdtLastOrder As Date
' Properties
Public Property Get Name() As String
Name = mstrName
End Property
Public Property Let Name(value As String)
mstrName = value
End Property
Public Property Get Email() As String
Email = mstrEmail
End Property
Public Property Let Email(value As String)
mstrEmail = value
End Property
Public Property Get LastOrderDate() As Date
LastOrderDate = mdtLastOrder
End Property
Public Property Let LastOrderDate(value As Date)
mdtLastOrder = value
End Property
' Methods
Public Function IsRecentCustomer() As Boolean
IsRecentCustomer = (DateDiff("d", mdtLastOrder, Date) <= 90)
End Function
Public Function DisplaySummary() As String
DisplaySummary = mstrName & " (" & mstrEmail & ")"
If IsRecentCustomer() Then
DisplaySummary = DisplaySummary & " — Active"
End If
End Function
Now in any form or module, you can do this:
Dim oCustomer As New clsCustomer
oCustomer.Name = "Jane Smith"
oCustomer.Email = "jane@example.com"
oCustomer.LastOrderDate = #6/1/2026#
MsgBox oCustomer.DisplaySummary()
The logic for what makes a customer "recent" lives in one place. If the business rule changes from 90 days to 60 days, you change it once.
Properties vs. Public Variables
You might wonder: why use Property Get and Property Let instead of just declaring public variables?
Properties give you control. You can:
- Validate input before storing it (
If value < 0 Then Err.Raise...) - Make a property read-only (define
Getbut notLet) - Compute a value on the fly instead of storing it
- Add logging or debugging hooks
Public variables expose raw data with no guardrails. Properties are the professional approach.
The Initialize and Terminate Events
Every class module has two special events:
Private Sub Class_Initialize()
' Runs when an instance is created (New clsCustomer)
mdtLastOrder = Date ' default to today
End Sub
Private Sub Class_Terminate()
' Runs when the object goes out of scope
' Good place to close connections, release resources
End Sub
Use Class_Initialize to set sensible defaults. Use Class_Terminate to clean up — especially important if your class holds a database connection or opens a recordset.
Collections of Objects
One of the most useful patterns is storing a collection of class instances:
Dim colCustomers As New Collection
Dim oCustomer As clsCustomer
' Load from recordset
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Customers")
Do While Not rs.EOF
Set oCustomer = New clsCustomer
oCustomer.Name = rs!CustomerName
oCustomer.Email = rs!Email
oCustomer.LastOrderDate = rs!LastOrderDate
colCustomers.Add oCustomer, CStr(rs!CustomerID)
rs.MoveNext
Loop
rs.Close
Now you have a typed collection of customer objects you can iterate, filter, and pass around — much cleaner than passing recordsets between procedures.
Practical Use Cases in Access
Form controllers — instead of putting all logic in the form's code-behind, create a controller class that the form delegates to. The form handles UI events; the class handles business logic.
Data access wrappers — wrap your DAO or ADO recordset operations in a class. If you later migrate to SQL Server, you only change the class internals, not every form that uses data.
Validation rules — a clsValidator class that takes a value and a rule set, returns pass/fail and an error message. Reuse it across every form.
Report builders — a class that assembles report parameters, opens the report, and handles errors. One place to maintain report-opening logic.
Common Mistakes
Forgetting Set for object assignment. When assigning an object to a variable, use Set:
Set oCustomer = New clsCustomer ' correct
oCustomer = New clsCustomer ' runtime error
Not releasing objects. When you are done with an object, set it to Nothing:
Set oCustomer = Nothing
This is especially important for objects that hold database connections.
Naming collisions. Prefix class module names with cls (e.g., clsCustomer, clsInvoice) to distinguish them from standard modules and forms.
When Class Modules Are Overkill
Not everything needs a class. Simple utility functions — string formatting, date calculations, file path helpers — belong in standard modules. Reach for a class when you have data and behavior that naturally belong together, or when you find yourself passing the same group of variables to multiple procedures.
The Migration Angle
If you are evaluating a move to Power Apps or SQL Server, class modules are worth understanding for another reason: they reveal your business logic.
Well-written class modules make it clear what your application actually does — what rules govern a customer, what steps constitute an order, what conditions trigger a notification. That clarity is invaluable when you are mapping Access logic to Dataverse tables or SQL Server stored procedures.
Messy form code hides business logic in event handlers. Class modules surface it.
Getting Started
Pick one area of your database where you have duplicated logic — the same validation in three forms, the same recordset-opening pattern in five procedures. Extract it into a class module. Use it in one place first. See how it feels.
The learning curve is gentle. The payoff — in maintainability, readability, and migration readiness — is significant.
Work With Access Evolved
If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.
Building a more maintainable Access VBA architecture? Schedule a free call and get expert guidance on structuring your code.
Thinking about migrating?
Ready to move beyond Microsoft Access?
Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on VBA & Macros
Building an Audit Log in Access with VBA
Knowing who changed what and when is essential for compliance and troubleshooting. Here is how to build a reliable audit trail in Access using VBA form events and a log table.
VBA Debugging Techniques Every Access Developer Needs
The VBA debugger is one of the most powerful tools in Access — and one of the most underused. Here is how to use breakpoints, the Immediate window, and watch expressions effectively.
Automating Access Reports with VBA
Stop manually opening and printing reports. VBA lets you automate report generation, apply filters on the fly, export to PDF, and email output — all without user intervention.