Class Modules
By Richard Rost
29 hours ago
Microsoft Access VBA Class Modules for Beginners In this lesson, we will explain what Class Modules are in Microsoft Access and how they differ from standard modules, form modules, tables, and other objects. We will discuss classes, instances, properties, methods, encapsulation, and object variables, then walk through creating a simple CLSEmployee class with an EmployeeName property and DisplayGreeting method. You will also learn when class modules can help organize related data and behavior, and when a standard module is the simpler choice. Rory from Provo, Utah (a Gold Member) asks: I am building an employee database in Access and most of my code is in standard modules and form events. I noticed that Access also has class modules, and I have seen examples where developers create objects to store and manage information. How are class modules different from tables, form code, and standard modules, and when would it make sense to use them in an Access database? MembersIn the extended cut, we will build another class module example. This time, we'll make a Stopwatch class so that each form can have its own dedicated stopwatch timer that the user can start and stop. It's another example to demonstrate the basics of class modules. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, Access class modules, standard modules vs class modules, VBA custom objects, VBA properties and methods, Property Get Let Set, VBA object instantiation, encapsulation, Class_Initialize, Class_Terminate, employee class module, VBA memory leaks
Subscribe to Class Modules
Get notifications when this page is updated
More InformationTranscript You ever click on Create in Microsoft Access and you see Module and Class Module? And you thought to yourself, I know what a module is, but what's a class module? Is that some advanced programmer thing I'm supposed to pretend I understand?
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today we're going to demystify class modules. They're one of those Access features that scare a lot of developers away because they sound complicated, but the basic idea is pretty straightforward.
We'll go through the basics, we'll compare class modules to regular modules, we'll go ahead and build one, and then by the end you'll know when they make sense and when a plain old standard module is the better choice.
Today's question comes from Rory in Provo, Utah, one of my Gold members. He says, "I'm building an employee database in Access, and most of my code is in standard modules and form events. I noticed that Access also has class modules, and I've seen examples where developers create objects to store and manage information. How are class modules different from tables, form code, and standard modules, and when does it make sense to use them in an Access database?"
Well, I get this one a lot. People are like, "What's that thing up there that says Class Modules? We got tables, queries, and forms, and over here Module and Class Module. There it is, right there."
All right, well, let's talk about it. Let's talk about class modules and how they compare to regular standard modules that most developers use every day.
We're going to define what a class module is, we'll talk about properties, methods, events, objects, and instances. I know that all sounds crazy, but we'll get to it, and we'll compare that to what standard modules are that you're probably already using.
And then we'll build one. I'll show you how to actually create a class module. And you'll see it's not magic. It's just another way to organize VBA code.
And I want to set expectations up front. Class modules are cool and they've got some really nifty uses, but they're a much more advanced topic, and you absolutely do not need them to build really powerful Access databases.
In fact, in my 30-plus-year career as an Access developer and instructor, I've never needed one. I've used them before because they are cool from time to time, but I've never needed to use one.
So first, let's get the word module straightened out. A module is just a place where VBA code lives. Think of it like a folder in a filing cabinet. The folder holds related procedures, functions, variables, and declarations.
Most people start with a standard module. You've got a module called Utilities, one for email, one for general. I have a global module. I've got a window positioning module. It's where you put general-purpose code the whole database can use.
Functions that format phone numbers, code that opens forms, routines that send email, all that good stuff.
Now, forms and reports also have modules behind them. When you click the Build button next to a form's On Click event, let's say, Access creates a procedure like Command0_Click. That code is stored in the form's module. Same idea for reports.
If I right-click on this guy, go to Build Event, that opens up the form's module. And you can see all these guys in here too, if you go to View and then Project Explorer, and here's all the modules. There you go.
These are the form and report modules, the code behind the forms and reports, and down here you've got your global system modules.
Now, a class module is another type of VBA module, but it has a different purpose. Instead of just being a bucket full of independent procedures, it defines a custom object. It's a template for making things that have their own data and their own behavior, and that distinction is the whole ball game.
Standard modules you're probably familiar with if you're watching this video. You can write a public function called FormatFullName. You can call it from a query, a form or report, another module, wherever else in your database that you want to.
You don't have to create anything else first. You just call FormatFullName, give it the values it needs, like first name and last name, and then it returns something to you. And that's how most Access VBA is written, and there's absolutely nothing wrong with that.
Standard modules are great for utility code. They're great for things like checking whether a form is open, escaping text for SQL, exporting a report to a PDF, calculating a date, all kinds of stuff.
The key idea here is that a standard module doesn't create separate copies of itself. If it has a module-level variable, that variable is shared by code using that module during the current Access session. There's one copy of it, not one copy per customer or one copy per invoice or one copy per form. It's just one variable.
And for a huge percentage of Access applications, standard modules, plus form and report code modules, are all you'll ever need.
Now, what's a class module? Well, the easiest way to understand a class module is with a blueprint analogy. A class module is like a blueprint for a house. The blueprint says a house has bedrooms, bathrooms, doors, windows, whatever.
But the blueprint itself is not a house that you can actually live in. But using that same blueprint, you can build lots of houses. One house can be blue, one can be red, one can have the Kirk family. One can have the Picard family in it.
Each house is separate, even though they come from the same basic blueprint plan.
So in VBA, the class module is the blueprint. The object created from that class is the house, and that's called an instance.
For example, you can create a class called CLSEmployee. Usually you start off class modules with CLS, so class employee.
Now, for each employee object, you might have a name, an employee ID, a hire date, and then methods that do employee-related work. You could then create one employee object or instance for Susan, another for Bob, another for Mr. Spock.
Each one keeps track of its own information. And that's the big advantage over a simple shared module variable. Classes let you work with multiple separate things at the same time, with each thing carrying its own data around with it.
Now, a class usually has two main pieces: properties and methods. You should be familiar with these from working with different Access objects.
Properties are characteristics. An employee's name, hourly rate, hire date, active status, all those things are properties. They describe that employee.
Methods are actions. An employee object might have a method called CalculatePay, Deactivate, GetDisplayName. Those are things the object can do.
Inside the class, you'll often store the actual value in a private variable. Private means code outside the class can't touch that variable directly. You have to go through the class's public properties and methods.
That's called encapsulation. It's basically a fancy word for keeping things internal so that external stuff can't touch them. It's like the internal plumbing inside the object. You expose the controls people need, but you don't let them reach in and start yanking wires out from inside the Jeffries tubes.
Now, in VBA, Property Get is used to retrieve a value, and Property Let lets you assign an ordinary value such as text, number, date, those kinds of things.
There's another one called Property Set when you are assigning an object reference, like setting a property equal to a form, a recordset, or another custom object.
And don't worry if this all sounds like a lot right now. Once you see the little demo, it will make a lot more sense.
But if you've done any work with things like recordsets, you've already used a lot of this already. Set DB equals CurrentDb. Set RS equals CurrentDb.OpenRecordset. You're already working with this stuff. You just don't know it yet.
Now, here's another fancy word you're going to hear a lot with classes: instantiation. It sounds scary, but it just means creating an object from a class.
So in VBA, we normally do that with the New keyword. You might declare a variable like Dim E or EMP or whatever you want to call it as CLSEmployee. Dim EmployeeBob As CLSEmployee.
So Susan and Bob are both objects of type CLSEmployee. Now you've created a variable at this point that can hold a reference to an employee object, but you haven't actually created the object yet.
So then you say Set EmployeeBob or EmployeeSusan As New Employee. Now VBA creates one fresh employee object in memory and your employee variable points to it.
Now, the word Set is important here. Set, you've probably seen it before. In VBA, when you assign an object reference, you use Set. When you assign an ordinary value, you use an equal sign. That's for numbers or strings or dates or whatever. You don't use Set for those.
Now Susan's object can have Susan's name, and Bob's object can have Bob's name. They're both employee names, though. They are separate instances with separate state. It just means the current values stored in that particular object.
It's what makes classes useful when you've got many similar things that each need to remember their own information.
Now, with a standard module, you write a public procedure or a function and call it directly. It's ready to use. You might have SendInvoiceEmail or IsFormOpen or GetNextBusinessDay. Those are all different functions or subs you can just call and use.
With a class module, it's a little more work. First you define the type of thing you want, then you create an instance of it, then you work through that object.
So standard modules are usually best when code is the general tool. It doesn't need to remember information about one particular thing.
A class module makes more sense when you have a cohesive object: a customer, an invoice, an employee, a shopping cart. And an object has related data and behavior.
Standard module variables are shared by the module. Class variables are private to each instance of that class. This lets you, for example, create 10 different invoice objects all at different stages without their totals stepping all over one another.
And like I said before, you're already working with classes. You might just not realize it yet. A form, for example, is an object. A report is an object. A text box is an object. A DAO recordset is an object. Access itself is one big object.
When you write Me.Requery in a form module, you're calling a method on the current form object. If you reference Me.Caption, you're looking at a property of that object, the form. When you use Me.Dirty, you're referring to a property. If you write code in the form's Before Update event, you're responding to an event that fires.
The code module behind a form or a report is effectively a class module associated with that specific object. It gives you special access to the form or report and its events.
A standalone class module is different. You create it yourself in the Visual Basic Editor using Insert Class Module. It's not automatically attached to a particular form or event. That means you can make your own reusable object types.
But again, this is completely optional. You don't need to go start making class modules just because you learn that they exist. Nobody gets an Access merit badge for having the most complicated architecture.
Now, classes can also have events of their own. The two basic events are Class_Initialize and Class_Terminate.
Class_Initialize runs when an instance is created. It's commonly used to set default values or create internal objects such as a collection that the class will use.
And, of course, Class_Terminate runs when the object is being released from memory. You may use it for simple cleanup, such as clearing an object variable.
You can create classes that listen for custom events from controls on forms, allowing shared button behavior across multiple forms, for example. This stuff can get pretty complex. And the first time you see it, it's like you're trying to follow a plate of spaghetti. You can see it through all the code.
But for today, just understand that classes can do more than store data. They can actually participate in events too.
That's about enough theory for today. Let's go ahead and build one. This is going to be the world's smallest useful class module.
We're going to create a class called CLSEmployee, and inside it we'll have a private variable named mEmployeeName. The M is just a naming convention to remind us that it's a module-level variable. You can use whatever sensible naming convention you prefer.
Then we'll create a Property Let procedure to set the employee's name and a Property Get procedure to read it back. And then we'll add one simple method called DisplayGreeting.
Then we'll test it. We'll create an employee object, give it a name, and call the method.
And I'm keeping this nice and simple because it's your first time working with classes. We're not going to be opening recordsets and writing to tables and launching shuttlecraft and all that stuff. The goal is simply to see the relationship between the class blueprint, the object instance, the property, and the method.
Now, before we get too deep, if you haven't done any VBA programming yet, you're watching the wrong video. But if all this doesn't seem like Greek to you and you want to learn more about VBA, go watch my intro to VBA class. It'll teach you everything that you need to know to get started in about 20 minutes.
All righty, so here I am in my TechHelp free template. This is a free database. You can grab a copy off my website if you'd like one.
And let's go ahead and create a class module. So we're going to go to Create Class Module. Now, in most of my videos, I'm like, "Click Module, not the Class Module." Well, this one, we're going to click Class Module.
Same Visual Basic Editor opens up. I'm going to close this one for now. But notice down here, it's under Class Modules instead of just Modules. Class Module.
So I'm going to close this. We don't need it right now. We still want to make sure we've got Option Compare Database and Option Explicit up top.
Now in here, I'm going to start off by just declaring a module-level variable called employee name. So it's going to be private, meaning only this class module can use it.
Private mEmployeeName As String
The M is just telling me that this is a module-level variable. Nobody outside this module can work with it. But how do people get that employee name? Well, you'll see. We're going to expose a method for them to get it.
First, we're going to give them a way to set that variable. And we do that with the Let property. There's also a Set property for objects, but just Let and Get for now, for today.
Public Property Let EmployeeName(ByVal Value As String)
ByVal just means we're not going to accidentally change the value that's being sent in. If you don't know ByVal versus ByRef, then go watch this video. Very important.
So we're going to get a value into EmployeeName. And what are we going to do with it? Well, we're going to save the value that the user just gave us in our private string there.
mEmployeeName = Value
So what did we just do? We took a value in from the user with a public property, and we stored it in a private variable. That's how encapsulation works. You only let the users work with what you let them work with, not with everything.
They can't. Let's say you've got employee first name and last name and Social Security number and all these things, and you want to only allow them to change certain things. Well, you can do it by only exposing certain Property Lets.
Now, you've saved it. How do you get it back to the user? Well, that's:
Public Property Get EmployeeName() As String
Now, this is basically a function. This is going to return a string. What is it going to do?
EmployeeName = mEmployeeName
See what's happening here? You set it with this, which is basically like a subroutine. It's like a Sub. You send in a value. It stores it in this variable.
This is basically like a function that gets the variable and returns it back to the user. Think function, and for this one think Sub. One sets, one gets.
I really wish they would have used the Let for object variables because you use those a lot less.
Now, in addition to directly working with this variable here, you can also create methods to do stuff inside your class module.
So you can do:
Public Sub DisplayGreeting()
And this will just do something. So you can have it, for example:
MsgBox "Hello, " & mEmployeeName
So if you say DisplayGreeting for this particular object, whatever it is, it'll say, "Hello, Sarah," "Bob," whoever's in there.
So now we've got a complete class all set up. Let's save this. I'm going to save this as CLS to indicate it's a class module: Employee.
Usually I do the thing where I put the suffix on, like CustomerT, CustomerF, or GlobalMod, but class modules are different. Probably because when I learned how to do this, I was following closely from a book way, way back 20-some years ago. And so I just picked up the habit of whatever book I was reading at the time.
So I do stick with this naming convention when it comes to class modules. That way I know it's class module stuff.
Now come back here and you'll see we've got a CLSEmployee down here. You can see it's got a little candy ring instead of the little pile of M&Ms. Or a pile of more like Skittles, I guess, with the colors.
So how do we use this in our code? Well, let's go into Design View and go into this button here, our Hello World button.
Now we're in a regular form module. And instead of saying Hello World, let's do some stuff with our brand new shiny toy.
So I'm going to create two employee objects.
Dim EmployeeKirk As CLSEmployee, EmployeeSpock As CLSEmployee
So now I've got two employee object variables set up. And we're going to:
Set EmployeeKirk = New CLSEmployee
Set EmployeeSpock = New CLSEmployee
So now we've dimmed them and we've set their variables, or set what they are. We've initialized them.
And now we can set some values, the actual variables inside the class.
EmployeeKirk.EmployeeName = "Jim"
EmployeeSpock.EmployeeName = "Spock"
So now I've set those variables. Now notice how I couldn't get to mEmployeeName. That's a private variable inside the class. I can't get to it. I have to use the EmployeeName that is exposed.
And now I can say things like:
EmployeeKirk.DisplayGreeting
EmployeeSpock.DisplayGreeting
And then when you're all done, remember, if you set it, you've got to forget it. It's such a good idea, I put it on a mouse pad.
If you set it, you've got to forget it. Otherwise you're going to get something called a memory leak.
Now, normally subroutines clean up something like this when they exit, but they don't always. So:
Set EmployeeKirk = Nothing
Set EmployeeSpock = Nothing
Let's see if it works. Save it. Debug Compile once in a while. We compile. Let's close it. Close it. Open it.
Hello, Jim. And hello, Spock.
And that doesn't look like we did much, but behind the scenes, what happened? We created two new class modules, or classes. Each of these is a separate object.
We set some values in those objects. We ran some methods from those objects, and then we destroyed the objects.
Now you're probably looking at that little demo that we just did thinking, "Richard, I probably could have displayed that with a message box and two lines of code."
And yes, you absolutely could have. For one name and one message box, a class is definitely overkill. We're using a small example so the mechanics are clear, not because it's the best way to say, "Hello, Jim," or, "He's dead, Jim."
The benefits start showing up when the object gets more responsibilities. Maybe an employee has a first name, a last name, an employee ID, a hire date, a pay rate, an active status, and methods to calculate pay or return a properly formatted display name.
You can put that logic that belongs to an employee inside the employee class. Then other code doesn't need to know every internal detail. It can simply work with that object.
So when should you seriously consider using a class module? Well, one good use is modeling a business object. For example, a customer might contain customer information plus methods for calculating their status, formatting their display name, checking whether they're eligible for some special discount, or whatever.
An invoice object can hold header information and a collection of invoice line items. It can calculate its own total. A shopping cart object can add items, remove items, calculate tax.
Another good use is reusable application behavior, especially when you need multiple separate objects. You can use classes to handle events for groups of controls across many forms or to standardize reusable form behaviors.
Classes can also make testing and maintenance easier because related rules are collected in one place instead of scattered all over your 20 different forms and four random modules named Stuff and Stuff2 and ReallyReallyReallyImportantStuff and all that stuff.
But use classes because they make design clearer, not because object-oriented programming sounds fancy at a cocktail party. If a standard function does the job simply and clearly, use a standard function.
And here's the practical reality. Like I said before, in my 30 years of developing with and teaching Access, I've never needed to use a class module. They're cool, and they've got a lot of nifty uses. Yes, I'm old. I use the word nifty. But they're really a more advanced topic.
And you don't need to use classes to build really powerful, cool databases. You could build awesome databases with just tables, queries, forms, reports, macros, where appropriate, and standard modules, and of course, form and report modules.
That's how 99.9% of my Access databases are built. Don't feel that you're not somehow a real programmer if you're not using classes. That's nonsense.
Also, classes do not replace proper relational database design. Your customer information still belongs in a properly designed customer table. Relationships still matter. A class can represent a customer in VBA memory while your code is working, but it's not a replacement for the customer table.
Classes are just another cool tool in the toolbox. Learn what classes do, recognize when they may help, but don't force them into every project like hot sauce on breakfast cereal. That's gross.
Now, if you decide that classes are something you want to explore further, there's a lot more you can do with them. We're going to do another example in the extended cut for the members, but you could do things like create calculated properties.
For example, an employee class might store first name and last name, but expose a read-only FullName property that calculates the display name whenever you ask for it. You can see an example right there:
Property Get FullName()
And it puts them together. That's a simple one, but you see how it works. It's kind of like the same thing we do in a query where the calculated fields are in a text box.
You can enforce rules through properties. Instead of allowing code anywhere to set a value however it wants, the class can validate that value before accepting it.
You can create objects from records in a table, work with them in memory, and use collections to manage groups of those objects. You can also get into advanced events techniques for reusable form and control behavior.
And I cover a lot of this and much more in my Access Developer 50 class. We start with the basics, compare classes to user-defined types, we cover encapsulation in more detail, calculated and read-only properties, methods and behaviors.
We build a more realistic example using customer data and recordsets. So that's all in Access Developer 50.
So, to wrap up, a standard module is a container for general-purpose VBA procedures, functions, constants, and other shared code. You call its public procedures directly, and that's the right solution for most Access programming tasks.
A class module is a blueprint for a custom object. You create instances from it with New. Each instance has its own state, meaning its own stored values. Properties describe the object. Methods let the object do work.
And, of course, classes are optional. They're an advanced tool, and they're useful when they improve organization. But you don't need them. You can build excellent professional Access databases without ever creating one.
But now you know what class modules are, how they differ from standard modules, and when they might be worth using.
So that's class modules. That's the basics. Now you've got your feet wet, and if you want to learn more, check out my website.
Post a comment down below. Let me know what you thought of today's video and if you ever plan on using class modules, or if you have, what you use them for and how you like them.
But that's going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the extended cut. Intro In this lesson, we will explain what Class Modules are in Microsoft Access and how they differ from standard modules, form modules, tables, and other objects. We will discuss classes, instances, properties, methods, encapsulation, and object variables, then walk through creating a simple CLSEmployee class with an EmployeeName property and DisplayGreeting method. You will also learn when class modules can help organize related data and behavior, and when a standard module is the simpler choice. Quiz Q1. What is the primary purpose of a standard module in Access VBA?
A. To store general-purpose procedures, functions, constants, and shared code
B. To store records permanently in the database
C. To define relationships between tables
D. To create a custom object with separate instances
Q2. What is the primary purpose of a class module?
A. To create a new table type
B. To define a blueprint for custom objects
C. To replace all form modules
D. To store global variables for the entire application
Q3. In the house blueprint analogy, what does an instance of a class represent?
A. The blueprint itself
B. A database table
C. An individual house built from the blueprint
D. A shared public variable
Q4. What is meant by instantiation in VBA?
A. Creating an object from a class
B. Saving a class module to disk
C. Adding a field to a table
D. Running a query from a form
Q5. Why are class modules useful when working with multiple similar items, such as employees or invoices?
A. Each instance can keep its own separate data and state
B. They automatically create tables for each item
C. They eliminate the need for form controls
D. They make all module variables public
Q6. Which of the following is an example of a property for an employee object?
A. EmployeeName
B. DisplayGreeting
C. CalculatePayReport
D. OpenEmployeeForm
Q7. Which of the following is an example of a method for an employee object?
A. HireDate
B. HourlyRate
C. ActiveStatus
D. DisplayGreeting
Q8. What does encapsulation mean in the context of a class module?
A. Keeping internal data private and exposing only controlled properties and methods
B. Saving all object data directly into a table
C. Combining multiple modules into one module
D. Making every variable available throughout the database
Q9. What is the purpose of a Private variable inside a class module?
A. It can only be accessed directly by code inside that class module
B. It can be accessed by any form in the database
C. It is automatically saved in a table
D. It is shared by every class instance
Q10. Which procedure is used to return the value of a property from a class module?
A. Property Get
B. Property Let
C. Property Set
D. Public Sub
Q11. Which procedure is normally used to assign a text, number, or date value to a class property?
A. Property Get
B. Property Let
C. Property Set
D. Class_Terminate
Q12. When is Property Set used instead of Property Let?
A. When assigning an object reference, such as a form, recordset, or another custom object
B. When assigning a string value
C. When returning a number from a function
D. When deleting an object from memory
Q13. Which keyword is required when assigning an object reference in VBA?
A. Set
B. Let
C. Get
D. Call
Q14. What is created by this statement?
Set EmployeeKirk = New CLSEmployee
A. A new instance of the CLSEmployee class
B. A new Employee table
C. A public variable shared by all employees
D. A new form named EmployeeKirk
Q15. Why can code outside the CLSEmployee class not directly use mEmployeeName if it is declared Private?
A. Private members are only available within the class module
B. Private members can only be used in queries
C. Private members are automatically deleted after assignment
D. Private members must be stored in a table first
Q16. Which statement best describes the difference between a module-level variable in a standard module and a private variable in a class module?
A. A standard module variable is shared during the Access session, while a class variable belongs to each object instance
B. A class variable is always public, while a standard module variable is always private
C. Standard module variables can only store strings, while class variables can store any type
D. Class variables are automatically stored in database tables
Q17. Which is a good reason to use a class module?
A. To model a business object that has related data and behavior, such as a customer or invoice
B. To avoid creating properly related database tables
C. To replace every simple utility function
D. To make a message box display text
Q18. Which is generally a better choice for a simple reusable task like checking whether a form is open?
A. A public function in a standard module
B. A new class module for every form
C. A table with one record per function
D. A report module
Q19. What does Class_Initialize do?
A. Runs when a new instance of the class is created
B. Runs whenever a table is opened
C. Runs when Access starts
D. Runs when a form is printed
Q20. What does Class_Terminate generally do?
A. Runs when an object is being released from memory and can be used for cleanup
B. Runs before a query is executed
C. Runs each time a property is read
D. Runs only when a class module is renamed
Q21. Which statement about forms and reports in Access is correct?
A. They have modules behind them and are themselves objects with properties, methods, and events
B. They cannot contain VBA code
C. They are standard modules with no events
D. They are replacements for class modules
Q22. What is the purpose of using Set EmployeeKirk = Nothing when finished with an object variable?
A. To release the object reference and help clean up memory
B. To save the object as a new record
C. To make the object variable public
D. To copy the object into a standard module
Q23. Do class modules replace proper relational database design?
A. No, business data still belongs in properly designed tables with appropriate relationships
B. Yes, a class module eliminates the need for tables
C. Yes, each class instance automatically becomes a database record
D. No, because classes can only work with text values
Q24. When should you choose a class module over a standard module?
A. When it makes the design clearer by grouping related data and behavior for separate objects
B. Whenever you need to display a message box
C. Whenever you need a global constant
D. Whenever you create a simple formatting function
Answers: 1-A; 2-B; 3-C; 4-A; 5-A; 6-A; 7-D; 8-A; 9-A; 10-A; 11-B; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A; 18-A; 19-A; 20-A; 21-A; 22-A; 23-A; 24-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. Summary Today's video from Access Learning Zone explains what class modules are in Microsoft Access VBA, how they differ from standard modules, and when they are worth using.
Many Access developers are comfortable with tables, queries, forms, reports, macros, and standard VBA modules, but class modules can seem mysterious. They sound like an advanced programming feature that only professional software developers need to understand. The truth is that class modules are useful in certain situations, but they are not required to build powerful, professional Access databases.
I want to explain the basic ideas behind class modules without making them more complicated than they need to be. We will compare class modules with standard modules, tables, and form code. Then I will show how a simple class works, including properties, methods, objects, and instances.
First, let's clarify what a module is. A module is simply a place where VBA code is stored. You can think of it as a folder that holds related procedures, functions, constants, variables, and declarations.
Most Access developers work primarily with standard modules. You might have one module for utility functions, another for email routines, another for general-purpose code, and perhaps another for global variables. These modules often contain code that can be used throughout the database.
For example, a standard module might contain functions that format phone numbers, determine whether a form is open, calculate dates, prepare text for SQL statements, send email, export reports to PDF, or open forms. This type of code is general-purpose code that can be called from forms, reports, queries, macros, and other VBA procedures.
Forms and reports also have their own code modules. When you create code for a form event, such as a button's On Click event or a form's Before Update event, that code is stored in the form's module. The same applies to report events. These modules are associated with a particular form or report and give you access to that object's controls, properties, methods, and events.
A standard module is generally a container for independent procedures and functions. For example, you might create a public function that formats a full name from a first name and last name. You can call that function from almost anywhere in your database without having to create anything first.
Standard modules are especially good for utility code. They work well when you have a procedure or function that performs a job but does not need to maintain separate information for multiple different items.
One important point is that module-level variables in a standard module are shared during the current Access session. If you have a public or module-level variable in a standard module, there is one copy of that variable. It is not automatically duplicated for each customer, employee, invoice, or form.
For most Access databases, standard modules plus form and report modules are all you will ever need. There is absolutely nothing wrong with writing your database that way.
A class module serves a different purpose. A class module is a blueprint for creating your own custom object.
The simplest way to understand this is to think about a house blueprint. A blueprint describes what a house should contain, such as bedrooms, bathrooms, doors, windows, and other features. But the blueprint itself is not a house. You cannot live in the blueprint.
Using the same blueprint, however, you can build many separate houses. One house may be blue, another may be red, and each house can contain a different family. Even though the houses came from the same blueprint, each one has its own separate information and existence.
In VBA, the class module is the blueprint. The object created from that class is the house. Each separate object created from the class is called an instance.
For example, you could create a class named CLSEmployee. That class could describe the information and behavior associated with an employee. It might store an employee name, employee ID, hire date, pay rate, and active status. It could also include actions such as calculating pay, deactivating the employee, or returning a formatted display name.
You could then create one employee object for Susan, another for Bob, and another for Mr. Spock. Each employee object would retain its own data. Susan's name would not overwrite Bob's name, and Bob's pay rate would not overwrite Spock's pay rate.
That is one of the main advantages of class modules. They allow you to work with multiple separate things at the same time. Each object can carry its own information around with it.
Classes generally contain two major categories of members: properties and methods.
Properties describe the characteristics of an object. For an employee object, properties could include employee name, hourly rate, hire date, department, active status, and employee ID. These are pieces of information that describe that particular employee.
Methods are actions that an object can perform. An employee object might include methods such as CalculatePay, Deactivate, GetDisplayName, or DisplayGreeting. Methods are procedures that belong to the object and work with that object's information.
Inside a class module, the actual data is often stored in private variables. A private variable can only be accessed by code inside that class module. Code outside the class cannot directly read or change it.
Instead, outside code works with public properties and methods that the class exposes. This is called encapsulation. Encapsulation means that the class protects its internal details and only allows other code to interact with it in approved ways.
For example, you might store an employee's name in a private variable inside the class. Outside code would not be allowed to change that private variable directly. Instead, it would use a public EmployeeName property to assign or retrieve the employee's name.
This gives you control over how data is handled. If you later want to validate employee names, prevent blank values, format names consistently, or restrict changes to sensitive values, you can place that logic inside the class property.
In VBA, a Property Get procedure is used to return a value. It works much like a function because it provides a result to the calling code.
A Property Let procedure is used to assign ordinary values, such as strings, numbers, dates, and Boolean values. It works more like a Sub procedure because it receives a value and stores or processes it.
There is also a Property Set procedure. Property Set is used when assigning an object reference, such as a form, a recordset, another custom class object, or another object variable.
You have already used this concept if you have worked with DAO recordsets or database objects. When you assign an object reference in VBA, you use the Set keyword. When you assign a normal value such as a string, number, or date, you use an equal sign without Set.
Another important word associated with classes is instantiation. This simply means creating an object from a class.
Suppose you create a class named CLSEmployee. You can declare a variable that is capable of holding a reference to an employee object. At that point, you have declared the variable, but you have not yet created an actual employee object.
To create the object, you use the New keyword. VBA then creates a fresh copy of the class in memory, and your variable points to that object.
You could create one object for Captain Kirk and another object for Mr. Spock. Both objects are based on the same CLSEmployee class, but each one has its own separate private variables and state.
State simply means the current values stored inside a particular object. If one employee object contains the name Jim and another contains the name Spock, those values are separate because the objects are separate instances of the class.
In a standard module, you normally create a public Sub or Function and call it directly. For example, you might have a function to determine whether a form is open, a procedure that sends an invoice email, or a function that calculates the next business day. The procedure is ready to use as soon as it is available in the module.
With a class module, there is an additional step. First, you define the type of object you want to create. Then you create an instance of that class. Once you have the object, you work with its properties and methods.
Standard modules are usually the best choice when your code is a general-purpose tool. If the code does not need to remember separate information for different customers, employees, invoices, or other entities, a standard function or procedure is often simpler and more appropriate.
Class modules are more useful when you have a cohesive object that has both related data and related behavior. Examples could include a customer, employee, invoice, shopping cart, order, appointment, or other business object.
For example, an invoice object might store invoice header information, hold a collection of line items, calculate its own total, determine tax, and check whether it has been paid. A shopping cart object might add items, remove items, calculate tax, and determine a subtotal.
You are already using classes in Access, even if you have never created a standalone class module yourself.
Forms are objects. Reports are objects. Text boxes are objects. Recordsets are objects. The Access application itself is an object.
When you use Me.Requery in a form module, you are calling a method on the current form object. When you use Me.Caption, you are reading a property of the form object. When you use Me.Dirty, you are checking another property. When you write code for a form event such as Before Update, you are responding to an event raised by that form object.
The code module behind a form or report is effectively a class module associated with that specific form or report. It is tied to that object and provides access to that object's events and features.
A standalone class module is different because you create it yourself. It is not automatically attached to a specific form, report, or event. Instead, it allows you to define your own reusable object type.
In the example from this lesson, I create a simple employee class. The class stores an employee name in a private module-level variable. The class then exposes a public property that allows outside code to assign the employee name. It also exposes another public property that allows outside code to retrieve the employee name.
The class also contains a simple public method that displays a greeting using the name stored inside that particular employee object.
The important point is that outside code cannot directly access the private employee name variable. It must use the public property exposed by the class. This is a simple example of encapsulation.
I then create two separate employee objects from the same class. One object represents Kirk and the other represents Spock. Each object is assigned its own employee name. When the greeting method is called for each object, each object displays its own name.
This may seem like a lot of work just to display two greeting messages, and it is. For a simple message box, a class module is definitely overkill. The goal of the example is not to show the best way to display a greeting. The goal is to demonstrate the mechanics of a class blueprint, object instances, properties, private variables, and methods.
The advantages of classes become more apparent when an object has more information and more responsibilities.
An employee object might contain first name, last name, employee ID, hire date, pay rate, active status, department, and other information. It could include methods to calculate pay, determine vacation eligibility, deactivate an employee, or produce a properly formatted display name.
Rather than scattering employee-related logic throughout many forms, reports, and standard modules, you can place logic that belongs to an employee inside the employee class. Other code can then work with the employee object without needing to know every internal detail.
Class modules can also include special events. Two basic class events are Class_Initialize and Class_Terminate.
Class_Initialize runs when an object instance is created. It is often used to assign default values or initialize internal objects, such as collections.
Class_Terminate runs when an object is being released from memory. It can be used for cleanup tasks, such as releasing object references.
Classes can also be used for more advanced event handling. For example, you can create classes that listen to events from controls on forms. This can allow shared behavior across multiple controls or multiple forms. These techniques can become fairly advanced, but it is useful to know that class modules can do more than simply store information.
There are several situations in which class modules may be a good choice.
One common use is modeling business objects. A customer class can contain customer information and methods that determine customer status, format a customer display name, or determine eligibility for a discount. An invoice class can hold invoice data and calculate totals. A shopping cart class can manage items and calculate tax.
Another use is reusable application behavior. Classes can be helpful when you need multiple separate objects that all perform similar tasks but maintain their own information. They can also be used for reusable form behaviors and advanced control event handling.
Classes can make maintenance easier because they collect related rules and behavior in one place. Instead of having customer rules scattered across numerous forms and miscellaneous modules, a customer class can centralize that logic.
However, you should use classes because they make the design of your database clearer, not because object-oriented programming sounds impressive. If a standard function does the job simply and clearly, then a standard function is probably the right choice.
In my own work with Access over several decades, I have rarely needed class modules. I have used them because they can be useful and interesting, but I have not needed them to build powerful databases.
You can build excellent Access applications with properly designed tables, queries, forms, reports, macros where appropriate, standard modules, and form and report code modules. Most Access databases do not require custom class modules.
Do not feel that you are somehow not a real programmer because you do not use classes. That is nonsense. Classes are simply another tool available to you when they help solve a particular problem.
It is also important to understand that class modules do not replace proper relational database design. Customer information still belongs in a properly designed customer table. Employee data belongs in employee tables. Relationships, keys, normalization, and database structure still matter.
A class can represent a customer, employee, or invoice in VBA memory while your code is working with it. It is not a replacement for the table where that information should be stored permanently.
Also, in today's Extended Cut, we will go beyond this basic introduction and look at additional class module techniques. This includes calculated and read-only properties, validation through properties, creating objects from table records, working with collections of objects, and more advanced class-related techniques.
For example, an employee class might store first name and last name separately but expose a read-only FullName property that combines them whenever you request it. This is similar to a calculated field in a query or a calculated expression in a form control.
A class property can also enforce rules. Instead of allowing any code anywhere in the database to assign a value without validation, the class can check the value before accepting it. This can help prevent invalid or incomplete data from being stored in an object.
You can also create class objects from records in tables, work with those objects in memory, and manage groups of objects with collections. These techniques are covered in greater detail in my Access Developer 50 class, where I compare class modules with user-defined types, explain encapsulation in more detail, cover calculated and read-only properties, and build more realistic examples using customer data and recordsets.
To summarize, a standard module is a container for general-purpose VBA code, including procedures, functions, constants, declarations, and shared code. Public procedures in a standard module can be called directly, and standard modules are the right solution for most Access programming tasks.
A class module is a blueprint for a custom object. You create instances of the class using New. Each instance maintains its own state, meaning its own stored values. Properties describe the object, while methods allow the object to perform actions.
Class modules are optional. They are an advanced tool that can improve organization and code reuse in the right situations, but they are not required for building professional Access databases.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic List Class modules versus standard modules
Class modules as object blueprints
Objects, instances, and object state
Class properties and methods
Property Get and Property Let procedures
Private variables and encapsulation
Creating a CLSEmployee class module
Instantiating class objects with New
Using Set for object references
Creating multiple class object instances
Calling class properties and methods
Class_Initialize and Class_Terminate events
Class modules versus tables and form code
When class modules are useful in Access Article In Microsoft Access, a module is simply a place where VBA code lives. Most developers work with standard modules, along with the code modules behind forms and reports. A standard module is useful for general-purpose procedures and functions that can be used throughout the database, such as formatting a phone number, checking whether a form is open, calculating a date, sending email, or opening a report.
A class module is also a place where VBA code lives, but it serves a different purpose. Instead of being a collection of independent procedures, a class module defines a custom object. It is a blueprint for creating separate objects that each have their own data and behavior.
A useful analogy is a house blueprint. The blueprint is not a house itself. It describes what a house should contain, such as rooms, doors, and windows. You can use the same blueprint to build multiple houses, and each house can have different people, furniture, colors, and other details. In the same way, a class module defines what an object should contain, while each object created from that class is a separate instance with its own values.
For example, you might create an employee class. The class could define that every employee has a name, employee ID, hire date, pay rate, and active status. It could also define actions an employee object can perform, such as calculating pay, returning a display name, or deactivating the employee.
The class module is the employee blueprint. An employee object is an actual instance created from that blueprint. You could create one employee object for Susan, another for Bob, and another for Spock. Each object would maintain its own information independently. Changing Bob's name would not affect Susan's name because they are different instances.
This is one of the biggest differences between class modules and standard modules. A standard module can contain module-level variables, but those variables are shared while the database is open. There is only one copy of each module-level variable. If different procedures use the same shared variable, they can overwrite one another's values.
A class module, on the other hand, gives each object instance its own private copy of its variables. If you create ten invoice objects, each invoice can keep track of its own customer, date, line items, and total without interfering with the others.
A class typically contains properties and methods. Properties describe the object. For an employee, properties might include EmployeeName, EmployeeID, HireDate, HourlyRate, or IsActive. Methods are actions the object can perform. An employee object might have methods such as CalculatePay, Deactivate, SaveChanges, or GetDisplayName.
Classes often keep their actual stored values private. This means code outside the class cannot directly change those internal variables. Instead, outside code works through the public properties and methods the class exposes. This approach is called encapsulation.
Encapsulation is useful because it lets the class control how information is handled. For example, if an employee's hourly rate must never be negative, the class can check the value before storing it. If the value is invalid, the class can reject it or handle it appropriately. Without a class, that validation logic might need to be repeated in many forms and standard modules.
In VBA, properties are commonly used to retrieve or assign values. A property that retrieves a value gives outside code access to information stored inside the object. A property that assigns a value accepts information from outside code and stores it internally. Properties can also be read-only, meaning outside code can view a value but cannot directly change it.
For example, an employee class might store a first name and last name separately but provide a read-only FullName property. When another part of the database asks for FullName, the class combines the first and last names into a properly formatted display name. This keeps the formatting logic inside the employee class instead of repeating it in forms, reports, queries, and other procedures.
Methods allow the class to perform work related to the object. For a simple employee class, a method might display a greeting using the employee's stored name. In a more practical class, a method could calculate payroll, determine whether an employee qualifies for a benefit, or load the employee's information from a table.
To use a class module, you first create the class module in the Visual Basic Editor and give it a meaningful name. A common convention is to begin class module names with CLS, such as CLSEmployee or CLSInvoice. This makes it easy to recognize that the module defines a class rather than containing general-purpose procedures.
Inside the class module, you define the private data the object will store, the public properties that allow controlled access to that data, and the methods that perform related actions.
Then, from a form module, report module, or standard module, you create an object based on that class. Creating an object from a class is called instantiation. You declare a variable as the class type, then create a new instance of that class. Once the object exists, you can assign values to its properties and call its methods.
For example, you could create two employee objects from the same employee class. You would assign Jim as the name for one object and Spock as the name for the other. Each object would retain its own name. Calling the greeting method on each object would produce a different result because each object has separate internal state.
State simply means the current values stored in a particular object. An employee object's state might include its name, ID, pay rate, and active status. An invoice object's state might include its invoice number, customer, date, line items, taxes, and total.
When you are finished working with an object, it is good practice to release the reference to it. In many cases VBA cleans up objects automatically when a procedure ends, but explicitly releasing object variables can help make your intentions clear and can prevent problems in more complex applications.
You are probably already using classes in Access without realizing it. Forms are objects. Reports are objects. Text boxes are objects. Recordsets are objects. The Access application itself is an object.
When you refer to Me in a form's code module, you are referring to the current form object. When you use a form property such as Caption or Dirty, you are reading or changing a property of that form object. When you call a method such as Requery, you are telling the form object to perform an action. When you write code for a form event such as Before Update or On Click, you are responding to an event raised by that object.
The code module behind a form or report behaves much like a class module because it is associated with a specific object. A standalone class module is different because you create it yourself to define your own reusable object type.
Class modules can also support events. Every class can respond to initialization and termination events. Initialization occurs when a new object instance is created, and it is often used to establish default values or prepare internal objects such as collections. Termination occurs when the object is being released and can be used for cleanup.
More advanced class techniques can allow a custom object to listen for events from form controls. This can be useful when you want consistent behavior across many controls or forms. For example, a class might manage a group of text boxes or command buttons and apply the same validation or formatting behavior to all of them. This is powerful, but it is more advanced than most Access applications require.
Class modules are most useful when you have a cohesive business object with related data and behavior. Customers, employees, invoices, shopping carts, orders, appointments, and projects are common examples. If information and actions naturally belong together, a class can help keep that logic organized.
An invoice class, for example, could hold invoice header information and a collection of invoice line items. It could calculate its own subtotal, taxes, discounts, and final total. Other code would not need to know all the internal calculations. It could simply ask the invoice object for its total.
A shopping cart class could add items, remove items, calculate taxes, apply discounts, and determine the total amount due. A customer class could format the customer's name, determine account status, check eligibility for a discount, or calculate a balance.
Classes can improve maintenance because related logic is kept in one place. Instead of spreading employee rules across numerous forms, reports, and standard modules, you can place employee-related rules inside an employee class. If a rule changes, you have a central place to update it.
However, class modules are not required to build excellent Access databases. Standard modules, form modules, report modules, tables, queries, and well-designed relationships are enough for most projects. A simple utility function is often clearer and easier than creating a class.
Use a standard module when you have a general-purpose procedure that does not need to remember information about a specific thing. Procedures for formatting values, checking form status, exporting reports, calculating dates, and sending messages are often ideal for standard modules.
Use a class module when you need multiple separate objects that each maintain their own related information and behavior. If you find yourself passing the same group of values from procedure to procedure, or repeatedly writing rules that belong to a single type of thing, a class may be helpful.
Classes do not replace proper database design. Customer information still belongs in a properly normalized customer table. Invoice records still belong in invoice tables, with proper relationships to customers and invoice line items. A class represents information temporarily in VBA memory while code is working with it. It is not a replacement for tables, queries, relationships, or data integrity rules.
The important point is that class modules are an optional organizational tool. They are useful when they make the design of your database clearer and easier to maintain. They should not be used simply because object-oriented programming sounds advanced.
A standard module is a container for shared procedures, functions, constants, and general-purpose code. A class module is a blueprint for a custom object. Each object created from that class has its own stored values, properties, methods, and possible events.
Once you understand that distinction, class modules become much less mysterious. They are simply a way to group related data and behavior into reusable objects when that approach makes sense for your application. Primary Topics class modules versus standard modules, custom objects and instances, properties and methods, encapsulation and private variables, object instantiation with New, object references and Set, form and report modules, appropriate class module use cases Secondary Topics Property Get, Property Let, Property Set, Class_Initialize, Class_Terminate, module-level variable scope, built-in Access objects, memory cleanup with Nothing, business object modeling |
