VBA Class Modules: A Step By Step Guide
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.
How Is A Class Module Different To A Normal Module?
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.
- Our Introduction to Macros article will get you up to speed with how to add the Developer Tab to the Ribbon. You will learn all about how to create simple macros in Excel. In addition, you will learn how to assign shortcut keys to macros and how to assign macros to buttons.
- Our Introduction to VBA article covers the VBA object model in Excel, and goes over what objects, properties and methods are. You will also learn about how to access the Visual Basic Editor. You will discover how to change the properties of objects.
- In our Mastering VBA Special Cells In Excel tutorial, we go over the Special Cells method in VBA, as well as the Activate, Select and Copy methods. This post will help you to get comfortable with using methods in your code. We also introduce you to Error Handling.
- The How to Use the COUNTA Function in Your VBA Code tutorial, covers the WorksheetFunction object. You will learn how to use the worksheet COUNTA Function and other built-in Excel worksheet functions in your VBA code.
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.
Insert The Class Module
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.
Step 1:
Press ALT-F11 on your keyboard in order to access the Visual Basic Editor.
Step 2:
Go to the Insert Tab and select Class Module.
You should see the following.
Creating the Template for An Object Item
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.
Step 1:
So, in our case we’ll name our class clPlant.
-
- Note: In order to view all the built-in objects at your disposal. Go to the View Tab and select Object Browser.
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.
Step 2:
Enter the following code in the class module we just created.
Option Explicit
‘The properties
Public commonName As String
Public scientificName As String
Public classificationlifeSpan As String
Public wateringRequirements As String
‘This is the growth method
Public Sub plantGrowth()
‘We start by declaring two variables
Dim growthStatus As String
Dim growthstatusShape As ShapegrowthStatus = “is growing”
‘This line is telling Excel to read out the common name of the plant and state that the plant is growing
Application.Speech.Speak “The” & ” ” & commonName & ” ” & growthStatus
‘The message box displays the text giving the common name of the plant and saying that the plant is growing
MsgBox “The” & ” ” & commonName & ” ” & growthStatus, vbOKOnly, “This is the Growth Status of the Plant Currently”
‘We are creating a rectangle on the active sheet and specifying the position and size with this line
Set growthstatusShape = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 70, 16, 170, 20)
‘We are filling the shape with a dark green colour
growthstatusShape.Fill.ForeColor.RGB = RGB(18, 90, 20)
‘We are stating the text that will be added to the shape
growthstatusShape.TextFrame.Characters.Text = “The plant is growing”
‘We are centre aligning the text in the shape
growthstatusShape.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenterEnd Sub
‘This is the flower blooming method
Public Sub flowersBlooming()
‘We are declaring a variable
Dim flowerbloomingStatus As StringflowerbloomingStatus = “The flowers are blooming”
‘We are telling Excel to read out the words, the flowers are blooming
Application.Speech.Speak flowerbloomingStatus
‘The message box displays the text saying that the flowers are blooming
MsgBox flowerbloomingStatus, vbOKOnly, “This is the Flower Blooming Monitoring of the Plant Currently”
End Sub
‘This is the labelling method which is named generalInformation
Public Sub generalInformation()
‘We start by declaring two variables
Dim generalInformationText As String
Dim generalInformationShape As ShapegeneralInformationText = scientificName & Chr(13) & classificationlifeSpan & Chr(13) & wateringRequirements
‘We are creating a rectangle on the active sheet and specifying the position and size with this line
Set generalInformationShape = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 70, 52.5, 170, 50)
‘Using the With…End With Construct we are changing some of the properties of this rectangle
With generalInformationShape
‘We are stating that the text that should be added to the rectangle should be the scientific name of the plant
‘the classification of it’s lifespan and it’s specific watering requirements
.TextFrame.Characters.Text = generalInformationText
‘We are filling the shape with a dark green colour
.Fill.ForeColor.RGB = RGB(18, 90, 20)
‘We are specifying that the font for the text should be in italics
.TextFrame2.TextRange.Font.Italic = msoTrue
End With
End Sub
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.
Use The Object!
We now want to use the custom object in our code.
Step 1:
To do this, go to the Insert Tab and select Module.
Step 2:
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.
Option Explicit
Sub mainCode()
Dim plant As New clPlant
‘We are specifying that the value of the commonName property is Bellflower, for our object
plant.commonName = “Bellflower”
‘We are specifying that the value of the scientificName property is Campanula portenschlagiana, for our object
plant.scientificName = “Campanula portenschlagiana”
‘We are specifying that the value of the classificationlifeSpan property is Perennial, for our object
plant.classificationlifeSpan = “Perennial”
‘We are specifying that the value of the watering requirements property is Medium watering requirements, for our object
plant.wateringRequirements = “Medium watering requirements”
‘We are now accessing the plantGrowth method of the object
plant.plantGrowth
‘We are now accessing the flowersBlooming method of the object
plant.flowersBlooming
‘We are now accessing the generalInformation method of the object
plant.generalInformation
End Sub
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.
Looking for more on Excel? Read our guide to the XLOOKUP Function here.
Step 3:
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.
Step 4:
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.
Advantages and Disadvantages Of Using Class Modules
Let’s review some advantages and disadvantages of using class modules.
Learning Objectives
You now know how to:
- Use a Class Module to create a custom object
- Specify the Properties and Methods of this custom object
- Call methods
- Make use of the Speech.Speak method to make Excel read text out loud
- Insert an Icon, convert it to a shape and assign a macro to the shape
Additionally, you have an understanding of:
- What the difference between a Class Module and a Normal module is
Conclusion:
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!
Special thank you to Taryn Nefdt for collaborating on this article!
