By: Ben Richardson Last updated: April 13th, 2022
News & tips
In this tutorial, we will provide an introduction to VBA class modules. You can create your own custom objects by using VBA class modules.
This allows you to take advantage of concepts, associated with object-oriented programming in your VBA code.
If you would like to learn more about VBA, then we have a whole course dedicated to Excel VBA.
Did you know that an Expert Excel user is 400% more likely to have recieved formal training compared to the average user? For more Excel facts read here.
VBA is an object-oriented programming language. When you write VBA code, you will often be manipulating objects in some way.
Excel has standard built-in objects that are available, for you to work with. These include the application object, the workbook object, and the worksheet object among others.
Each of these objects have associated properties and methods.
To go over these topics in greater detail, please visit some of our previous VBA tutorials.
When you insert a class module, you are creating the specifications for a custom object. VBA class modules are useful when you want to model real-world objects.
For example, let’s say a VBA developer working for a small plant nursery, wanted to create a plant object that was based on a plant class.
A plant has a common name and a scientific name. It’s life cycle can be classified as annual, perennial or biennial. It can be drought-resistant, of medium water requirements or needing to be watered constantly.
These are all the attributes that a plant has. This would be analogous to the properties of the plant object.
If the plant receives the right nutrients and conditions are optimal for growth, then the plant will grow. So, growth is analogous to a method. If the plant is a flowering plant, then the flowers will bloom at a certain time. This could be another method.
The nursery requires that each pot containing a plant be labelled, with the scientific name of the plant, its life cycle classification as well as it’s water needs. This labelling can be considered to be an action or method, associated with each plant.
A normal module on the other hand, is a place where you will store your sub procedures and functions. A normal module cannot be instantiated as a class.
We are going to model the plant object that we described above, using a class module.
So, let’s look at how to insert a class module.
Press ALT-F11 on your keyboard in order to access the Visual Basic Editor.
Go to the Insert Tab and select Class Module.
You should see the following.
The first thing we are going to do, is choose a name for our Class. When naming the class don’t give it the name of an existing built-in object or keyword.
So, in our case we’ll name our class clPlant.
Now you will be able to see all the built-in objects and their associated properties and methods.
You will also be able to see the new class that we just created.
You can close the window.
Enter the following code in the class module we just created.
Now let’s look at the code in greater detail. We start off by making four variable declarations in our class module. These will be the properties of our object. We use the Public statement.
We then create the methods of our custom object by using sub routines.
In our growth method, we are utilising the speak method of the built-in Speech object. By using this method we are able to make Excel play back the text string that we have input as an argument.
We now want to use the custom object in our code.
To do this, go to the Insert Tab and select Module.
In the module create a sub procedure called mainCode and start by using the object. Here, we use the Dim keyword in combination with the New keyword.
You will notice that as you type, Intellisense will detect the new class that we just created. Select clPlant from the menu and press Tab.
Now if we wanted to create another object based on our class, called plant1 for example, we could do this. This object would have all the properties and be able to access all the methods specified, in the clPlant class.
plant1 could be used for the Magnolia plant for example.
In this way, the class is used as a template for multiple copies of the object. You can also see that most of the difficult code is hidden from view when one is using the object in a normal module.
So let’s say more than one programmer was working on the application, they could use the custom objects in any of their modules with ease.
Now we want to assign the mainCode macro to a shape. So, when the user clicks on the shape, the code will run.
On a blank sheet in the workbook, go to the Insert Tab and in the Illustrations Group, select the Icons option.
Type plant in the Search box to see all the icons that are related to plants.
Select the filled leaf icon and click the Insert button.
With the icon selected, go to the Graphics Format Tab and in the Change Group, select the Convert to Shape option.
Now with the shape selected. Go the Shape Format Tab, and in the Shape Styles Group, select Shape Fill.
Select the Green, Accent 6, Darker 25% fill.
Now right-click the shape and select Assign Macro…
Using the Assign Macro Dialog Box, select the mainCode macro and then click Ok.
Now make sure the shape is not selected. Then go to the View Tab and in the Show Group, uncheck the Gridlines option.
If you hover over the shape, the cursor should change.
If you press the shape. You should hear the words the Bellflower is growing, followed by a message box displaying the text.
You should then hear the words the flowers are blooming spoken, followed by a message box displaying the text.
Excel will then create two rectangles on the worksheet. One with text stating that the plant is growing and another containing the scientific name, life span classification and watering requirements of the Bellflower plant.
Let’s review some advantages and disadvantages of using class modules.
Class modules are useful, when you need to create your own custom objects.
If you would like to delve into advanced VBA topics such as creating Add-Ins, then you should consider learning more about class modules.
Looking for more Excel tips? Check out the Creating a Data Model in Excel article here!