“Classes struggle, some classes triumph, others are eliminated. Such is history” – Chairman Mao
Contents
- 1 A Quick Guide to the VBA Class Module
- 2 The Webinar
- 3 Introduction
- 4 Download the Source Code
- 5 Why Do We Use Objects
- 6 Advantages of Using Objects
- 7 Disadvantages of Using Objects
- 8 Creating a Simple Class Module
- 9 VBA Class Modules Versus VBA Normal Modules
- 10 The Parts of a Class Module
- 11 Class Module Events
- 12 Class Module Example 1
- 13 Class Module Example 2
- 14 Conclusion
- 15 What’s Next?
A Quick Guide to the VBA Class Module
Item | Explanation |
---|---|
Class Module | Allows the user to create their own objects. |
Method | A public function or sub in the class module. |
Member variable | A variable declared in the class module. |
Property | Special function/subs that behave like variables when used |
Property types | Get, Set and Let. |
Event - Initialize | Sub that automatically runs when the class module object is created. |
Event - Terminate | Sub that automatically runs when the class module object is deleted. |
Declaring and Creating - Static | Dim o As New Class1 |
Declaring and Creating - Dynamic | Dim o As Class1 Set o = New Class1 |
Calling a class module sub | o.WriteValues Total |
Calling a class module function | Amount = o.Calculate() |
Using a class module property | o.Amount = 1 Total = o.Amount |
The Webinar
Members of the Webinar Archives can access the webinar for this article by clicking on the image below.
(Note: Archive members have access to the webinar archive.)
Introduction
VBA Class Modules allow the user to create their own objects. If you are not familiar with objects then I would highly recommend that you first check out my previous post VBA Objects – The Ultimate Guide.
In languages such as C# and Java, classes are used to create objects. Class Modules are the VBA equivalent of these classes. The major difference is that VBA Class Modules have a very limited type of Inheritance* compared to classes in the other languages. In VBA, Inheritance works in a similar way to Interfaces** in C#\Java.
In VBA we have built-in objects such as the Collection, Workbook, Worksheet and so on. The purpose of VBA Class Modules is to allow us to custom build our own objects.
Let’s start this post by looking at why we use objects in the first place.
*Inheritance is using an existing class to build a new class.
**Interfaces are a form of Inheritance that forces a class to implement specifics procedures or properties.
Download the Source Code
Why Do We Use Objects
Using objects allows us to build our applications like we are using building blocks.
The idea is that the code of each object is self-contained. It is completely independent of any other code in our application.
This is similar to how things are built using Lego® bricks. There are many different types of Lego® components used. For example, a block, steering wheel, and laser are different items. They behave completely independently of each other. The wheel spins, the laser rotates etc. Yet we can connect them together to create a building, vehicle, space station and so on.
If you are still not clear about this then don’t worry. We’ll be breaking it all down into simple terms in the rest of this post.
Advantages of Using Objects
Treating parts of our code as blocks provide us with a lot of great advantages
- It allows us to build an application one block at a time.
- It is much easier to test individual parts of an application.
- Updating code won’t cause problems in other parts of the application.
- It is easy to add objects between applications.
Disadvantages of Using Objects
With most things in life there are pros and cons. Using VBA class modules is no different. The following are the disadvantages of using class module to create objects
- It takes more time initially to build applications*.
- It is not always easy to clearly define what an object is.
- People new to classes and objects can find them difficult to understand at first.
*If you create an application using objects it will take longer to create it initially as you have to spend more time planning and designing it. However, in the long run it will save you a huge amount of time. Your code will be easier to manage, update and reuse.
Creating a Simple Class Module
If you to see working examples of this code you can download the source code from the top of this post.
Let’s look at a very simple example of creating a class module and using it in our code.
To create a class module we right-click in the Project window and then select Insert and Class Module
Our new class is called Class1. We can change the name in the Properties window as the following screenshot shows:
Let’s change the name of the class module to clsCustomer. Then we will add a variable to the class module like this
Public Name As String
We can use now use this class module in any module(standard or class) in our workbook. For example
' Create the object from the class module Dim oCustomer As New clsCustomer ' Set the customer name oCustomer.Name = "John" ' Print the name to the Immediate Window(Ctrl + G) Debug.Print oCustomer.Name
Class Module versus Objects
People who are new to using classes and VBA class modules, often get confused between what is a class and what is an object.
Let’s look at a real world example. Think of a mass produced item like a coffee mug. A design of the mug is created first. Then, thousands of coffee mugs are created from this design.
This is similar to how class modules and objects work.
The class module can be thought of as the design.
The object can be thought of as the item that is created from the design.
The New keyword in VBA is what we use to create an object from a class module. For example:
' Creating objects using new Dim oItem As New Class1 Dim oCustomer1 As New clsCustomer Dim coll As New Collection
Note: We don’t use New with items such as Workbooks and Worksheets. See When New is not required for more information.
VBA Class Modules Versus VBA Normal Modules
Writing code in a class module is almost the same as writing code in a normal module. We can use the same code we use in normal modules. It’s how this code is used which is very different.
Let’s look at the two main differences between the class and the normal module. These often cause confusion among new users.
Difference 1 – How the modules are used
If you want to use a sub/function etc. from a class module you must create the object first.
For example, imagine we have two identical PrintCustomer subs. One is in a class module and one is in a normal module…
' CLASS MODULE CODE - clsCustomer Public Sub PrintCustomer() Debug.Print "Sample Output" End Sub
' NORMAL MODULE CODE Public Sub PrintCustomer() Debug.Print "Sample Output" End Sub
You will notice the code for both is exactly the same.
To use the PrintCustomer sub from the class module, you must first create an object of that type
' Other Module ' https://excelmacromastery.com/ Sub UseCustomer() Dim oCust As New clsCustomer oCust.PrintCustomer End Sub
To use PrintCustomer from the normal module you can call it directly
' Other Module ' https://excelmacromastery.com/ Sub UseCustomer() PrintCustomer End Sub
Difference 2 – Number of copies
When you create a variable in a normal module there is only one copy of it. For a class module, there is one copy of the variable for each object you create.
For example, imagine we create a variable StudentName in both a class and normal module..
' NORMAL MODULE Public StudentName As String
' CLASS MODULE Public StudentName As String
For the normal module variable there will only be one copy of this variable in our application.
StudentName = "John"
For the class module, a new copy of the variable StudentName is created each time a new object is created.
Dim student1 As New clsStudent Dim student2 As New clsStudent student1.StudentName = "Bill" student2.StudentName = "Ted"
When you fully understand VBA class modules, these differences will seem obvious.
The Parts of a Class Module
There are four different items in a class module. These are
- Methods – functions/subs.
- Member variables – variables.
- Properties– types of functions/subs that behave like variables.
- Events – subs that are triggered by an event.
You can see they are all either functions, subs or variables.
Let’s have a quick look at class that has examples of each of these:
' CLASS MODULE CODE from clsAccount ' https://excelmacromastery.com/vba-class-modules/ ' Member variable Private m_balance As Double ' Properties Property Get Balance() As Double Balance = m_balance End Property Property Let Balance(value As Double) m_balance = value End Property ' Event - triggered when class created Private Sub Class_Initialize() m_balance = 100 End Sub ' Methods Public Sub Withdraw(amount As Double) m_balance = m_balance - amount End Sub Public Sub Deposit(amount As Double) m_balance = m_balance + amount End Sub
The following code demonstrates how this class could be used:
' This sub uses the clsAccount class ' The results are printed to the Immediate Window(Ctrl + G) ' https://excelmacromastery.com/vba-class-modules/ Sub Demo_clsAccount() Dim oAccount As New clsAccount ' Print the balance Debug.Print "Starting balance is: " & oAccount.Balance ' Deposit money oAccount.Deposit 25 ' Print the balance Debug.Print "Balance after deposit is: " & oAccount.Balance ' Withdraw Money oAccount.Withdraw 100 ' Print the balance Debug.Print "Balance after withdrawl is: " & oAccount.Balance End Sub
If we run the code we will get the following:
Starting balance is: 100
Balance after deposit is: 125
Balance after withdrawl is: 25
Now that we have seen examples, let’s take a look at each of these in turn.
Class Module Methods
Methods refer to the procedures of the class. In VBA procedures are subs and functions. Like member variables they can be Public or Private.
Let’s look at an example:
' CLASS MODULE CODE for clsExample ' https://excelmacromastery.com/vba-class-modules/ ' Public procedures can be called from outside the object Public Sub PrintText(text As String) Debug.Print text End Sub Public Function Calculate(amount As Double) As Double Calculate = amount - GetDeduction End Function ' private procedures can only be called from within the Class Module Private Function GetDeduction() As Double GetDeduction = 2.78 End Function
We can use the clsExample class module like this:
' Sub used to demonstrate Class clsExample ' https://excelmacromastery.com/vba-class-modules/ Public Sub ClassMembers() Dim oSimple As New clsExample oSimple.PrintText "Hello" Dim total As Double total = oSimple.Calculate(22.44) Debug.Print total End Sub
Class Module Member Variables
The member variable is very similar to the normal variable we use in VBA. The difference is we use Public or Private instead of Dim.
' CLASS MODULE CODE Private Balance As Double Public AccountID As String
Note: Dim and Private do exactly the same thing but the convention is to use Dim in sub/functions and to use Private outside sub/functions.
The Public keyword means the variable can be accessed from outside the class module. For example:
' This code will give an ERROR!! Sub Demo_BankAccount() Dim oAccount As New clsBankAccount ' Valid - AccountID is public oAccount.AccountID = "499789" ' ERROR - Balance is private oAccount.Balance = 678.9 End Sub
In the above example, we cannot access Balance because it is declared as Private. We can only use a Private variable within the class module. We can use in a function/sub in the class module e.g.
' CLASS MODULE CODE ' https://excelmacromastery.com/ Private Balance As Double Public Sub SetBalance() Balance = 100 Debug.Print Balance End Sub
It is considered poor practice to have public member variables. This is because you are allowing code outside the object to interfere with how the class works. The purpose of the using classes is so that we hide what is happening from the caller.
To avoid the user directly talking to our member variables we use Properties.
Class Module Properties
- Get – returns an object or value from the class
- Let – sets a value in the class
- Set – sets an object in the class
Format of VBA Property
The normal format for the properties are as follows:
Public Property Get () As Type End Property Public Property Let (varname As Type ) End Property Public Property Set (varname As Type ) End Property
We have seen already that the Property is simply a type of sub. The purpose of the Property is to allow the caller to get and set values.
Why we use Properties
Why can’t we just make the variables Public and use them directly?
Let’s explain with some examples. Imagine we have a class that maintains a list of Countries. We could store the list as an array
' Use array to store countries ' https://excelmacromastery.com/ Public arrCountries As Variant ' Set size of array when class is initialized ' https://excelmacromastery.com/ Private Sub Class_Initialize() ReDim arrCountries(1 To 1000) End Sub
When the user wants to get the number of countries in the list they could do this:
' NORMAL MODULE CODE Dim oCountry As New clsCountry ' Get the number of items NumCountries = UBound(oCountry.arrCountries) - LBound(oCountry.arrCountries) + 1
There are two major problems with the above code:
- To get the number of countries you need to know how the list is stored e.g. Array.
- If we change the Array to a Collection, we need to change all code that reference the array directly.
To solve these problems we can create a function to return the number of countries
' CLASS MODULE CODE - clsCountryList ' Array Private arrCountries() As String Public Function Count() As Long Count = UBound(arrCountries) + 1 End Function
We then use it like this:
' MODULE CODE Dim oCountries As New clsCountries Debug.Print "Number of countries is " & oCountries.Count
This code solves the two problems we listed above. We can change our Array to a Collection and the caller code will still work e.g.
' CLASS MODULE CODE ' Collection ' https://excelmacromastery.com/ Private collCountries() As Collection Public Function Count() As Long Count = collCountries.Count End Function
The caller is oblivious to how the countries are stored. All the caller needs to know is that the Count function will return the number of countries.
As we have just seen, a sub or function provides a solution to the above problems. However, using a Property can provide a more elegant solution.
Using a Property instead of a Function/Sub
Instead of the creating a Count Function we can create a Count Property. As you can see below they are very similar:
' Replace this Public Function Count() As Long Count = UBound(m_countries) - LBound(m_countries) + 1 End Function ' With this Property Get Count() As Long Count = UBound(m_countries) - LBound(m_countries) + 1 End Property
In this scenario, there is not a lot of difference between using the Property and using a function. However, there are differences. We normally create a Get and Let property like this:
' https://excelmacromastery.com/vba-class-modules/ Private m_totalCost As Double Property Get totalCost() As Long totalCost = m_totalCost End Property Property Let totalCost(value As Long) m_totalCost = value End Property
Using Let allows us to treat the property like a variable. So we can do this
oAccount.TotalCost = 6
The second difference is that using Let and Get allows us to use the same name when referencing the Get or Let property. So we can use the property like a variable. This is the purpose of using Properties over a sub and function.
oAccount.TotalCost = 6 value = oAccount.TotalCost
If we used a function and a sub then we cannot get the behaviour of a variable. Instead we have to call two different procedures e.g.
oAccount.SetTotalCost 6 value = oAccount.GetTotalCost
You can also see that when we used Let we can assign the value like a variable. When we use SetTotalCost , we had to pass it as a parameter.
The Property in a Nutshell
- The Property hides the details of the implementation from the caller.
- The Property allows us to provide the same behaviour as a variable.
Types of VBA Property
There are three types of Properties. We have seen Get and Let already. The one we haven’t looked at is Set.
Set is similar to Let but it is used for an object(see Assigning VBA Objects for more detail about this).
Originally in Visual Basic, the Let keyword was used to assign a variable. In fact, we can still use it if we like.
' These line are equivalent Let a = 7 a = 7
So we use Let to assign a value to a variable and we use Set to assign an object to an object variable.
' Using Let Dim a As Long Let a = 7 ' Using Set Dim coll1 As Collection, coll2 As Collection Set coll1 = New Collection Set coll2 = coll1
- Let is used to assign a value to a basic variable type.
- Set is used to assign an object to an object variable.
In the following example, we use Get and Let properties for a string variable:
' CLASS MODULE CODE for clsPerson ' https://excelmacromastery.com/vba-class-modules/ ' SET/LET PROPERTIES for a variable Private m_name As String ' Get/Let Properties Property Get name() As String name = m_name End Property Property Let name(name As String) m_name = name End Property
We can then use the name properties like this:
' Testing Let and Set for the clsPerson Class ' https://excelmacromastery.com/vba-class-modules/ Sub TestLetSet() Dim name As String Dim oPerson As New clsPerson ' Let Property oPerson.name = "Bill" ' Get Property name = oPerson.name End Sub
In the next example, we use Get and Set properties for an object variable:
' CLASS MODULE CODE for clsCurrency ' https://excelmacromastery.com/vba-class-modules/ Private m_Prices As Collection ' Get/Set Properties Property Get Prices() As Collection Set Prices = m_Prices End Property Property Set Prices(newPrices As Collection) Set m_Prices = newPrices End Property
We can then use the properties like this:
' The code below demonstrates how to use the Let\Set properties with a class. ' ' 1. The sub creates a collection and adds value. ' 2. We then add it to the clsCurrency class object using the Set Property. ' 3. We then read it from the class object usiing the Get property. ' ' https://excelmacromastery.com/vba-class-modules/ Sub TestLetSet() ' Create a collection and add prices Dim Prices As New Collection Prices.Add 21.23 Prices.Add 22.12 Prices.Add 20.12 Dim oCurrency As New clsCurrency ' Uses the Set property of clsCurrency to ' add the collection to the class Set oCurrency.Prices = Prices Dim PricesCopy As Collection ' Uses the Get property of clsCurrency ' to read the collection from the class Set PricesCopy = oCurrency.Prices ' Print the results to the Immediate Window(Ctrl + G) PrintCollection Prices, "Prices" PrintCollection PricesCopy, "Copy" End Sub ' Print the contents of a Collection to the Immediate Window(Ctrl + G) Sub PrintCollection(c As Collection, name As String) Debug.Print vbNewLine & "Printing " & name & ":" Dim item As Variant For Each item In c Debug.Print item Next item End Sub
We use the Get property to return the values for both items. Notice that even though we use the Get Property to return the Collection, we still need to use the Set keyword to assign it.
One very important thing to understand is that when we use Set we are still referencing the same collection. Set is not creating a copy of the collection. You can read more about this here
Class Module Events
If you to see working examples of this code you can download the source code from the top of this post.
A class module has two events
- Initialize – occurs when a new object of the class is created.
- Terminate – occurrs when the class object is deleted.
In Object-Oriented languages like C++, these events are referred to as the Constructor and the Destructor. In most languages, you can pass parameters to a constructor but in VBA you cannot. We can use a Class Factory to get around this issue as we will see below.
Initialize
Let’s create a very simple class module called clsSimple with Initialize and Terminate events:
' CLASS MODULE CODE ' https://excelmacromastery.com/ Private Sub Class_Initialize() MsgBox "Class is being initialized" End Sub Private Sub Class_Terminate() MsgBox "Class is being terminated" End Sub Public Sub PrintHello() Debug.Print "Hello" End Sub
In the following example, we use Dim and New to create the object.
In this case, oSimple is not created until we reference it for the first time e.g.
' https://excelmacromastery.com/ Sub ClassEventsInit2() Dim oSimple As New clsSimple ' Initialize occurs here oSimple.PrintHello End Sub
When we use Set and New together the behaviour is different. In this case the object is created when Set is used e.g.
' https://excelmacromastery.com/ Sub ClassEventsInit() Dim oSimple As clsSimple ' Initialize occurs here Set oSimple = New clsSimple oSimple.PrintHello End Sub
Note: For more information about the different between using New with Dim and using New with Set see Subtle Differences of Dim Versus Set
As I said earlier, you cannot pass a parameter to Initialize. If you need to do this you need a function to create the object first
' CLASS MODULE - clsSimple ' https://excelmacromastery.com/ Public Sub Init(Price As Double) End Sub ' NORMAL MODULE Public Sub Test() ' Use CreateSimpleObject function Dim oSimple As clsSimple Set oSimple = CreateSimpleObject(199.99) End Sub Public Function CreateSimpleObject(Price As Double) As clsSimple Dim oSimple As New clsSimple oSimple.Init Price Set CreateSimpleObject = oSimple End Function
We will expand on this CreateSimpleObject in Example 2 to create a Class Factory.
Terminate
The Terminate event occurs when the class is deleted. This happens when we set it to Nothing
' https://excelmacromastery.com/ Sub ClassEventsTerm() Dim oSimple As clsSimple Set oSimple = New clsSimple ' Terminate occurs here Set oSimple = Nothing End Sub
If we don’t set the object to Nothing then VBA will automatically delete it when it goes out of scope.
What this means is that if we create an object in a procedure, when that procedure ends VBA will delete any objects that were created.
' https://excelmacromastery.com/ Sub ClassEventsTerm2() Dim oSimple As New clsSimple ' Initialize occurs here oSimple.PrintHello ' oSimple is deleted when we exit this Sub calling Terminate End Sub
Class Module Example 1
In this example, we are going to look at a very common use of a Class module.
Imagine we have the following data
We want to read the Albums based on a range of years and then create various reports.
We could use a 2D Array for this or a Collection of collections e.g.
' https://excelmacromastery.com/ For i = 2 To rg.Rows.Count Year = rg.Cells(i, 3) If startYear <= Year And endYear >= Year Then ' Create a new collection for each row Set rowColl = New Collect ' Add artist rowColl.Add rg.Cells(i, 1).Value ' Add Title rowColl.Add rg.Cells(i, 2).Value ' and so on ' Add row collection to main collection coll.Add rowColl End If Next i
As you can imagine this code would get messy very quickly.
Lucky for us we have VBA class modules to make our life easier. We can create a class module to store the items.
' clsAlbum class module Private m_sArtist As String Private m_sTitle As String Private m_sYear As String Private m_sGenre As String Private m_sSales As String ' Properties ' https://excelmacromastery.com/ Public Property Get Artist() As String Artist = m_sArtist End Property Public Property Let Artist(ByVal sArtist As String) m_sArtist = sArtist End Property ' etc
Each time we want to add a record we can do it as follows:
' Declare the Variable Dim oAlbum As clsAlbum ' Create new album Set oAlbum = New clsAlbum ' Add the details oAlbum.Artist = rg.Cells(i, 1) oAlbum.Title = rg.Cells(i, 2) oAlbum.Year = rg.Cells(i, 3) oAlbum.Genre = rg.Cells(i, 4) oAlbum.Sales = rg.Cells(i, 5) ' Add the album object to the collection coll.Add oAlbum
You can see that this makes our code much more readable. It is clear what Artist, Title etc. are being used for.
We can then easily use this data to create reports, write to files etc.
' https://excelmacromastery.com/ Sub PrintAlbum(coll As Collection) Dim oAlbum As clsAlbum For Each oAlbum In coll ' Print out the title and artist for each album Debug.Print oAlbum.Title, oAlbum.Artist Next End Sub
Below is the full code for this example:
' https://excelmacromastery.com/ Sub CreateReport() Dim coll As Collection ' read the data Set coll = ReadAlbums(1990, 2001) ' Print the album details PrintAlbum coll ' Print the total sales PrintTotalSales coll End Sub Function ReadAlbums(startYear As Long, endYear As Long) _ As Collection Dim rg As Range Set rg = Sheet1.Range("A1").CurrentRegion ' Create a collection to store the albums Dim coll As New Collection Dim oAlbum As clsAlbum Dim i As Long, Year As Long For i = 2 To rg.Rows.Count Year = rg.Cells(i, 3) If startYear <= Year And endYear >= Year Then ' Create new album Set oAlbum = New clsAlbum ' Add the details oAlbum.Artist = rg.Cells(i, 1) oAlbum.Title = rg.Cells(i, 2) oAlbum.Year = Year oAlbum.Genre = rg.Cells(i, 4) oAlbum.sales = rg.Cells(i, 5) ' Add the album objecdt to the collection coll.Add oAlbum End If Next i Set ReadAlbums = coll End Function Sub PrintAlbum(coll As Collection) Dim oAlbum As clsAlbum For Each oAlbum In coll Debug.Print oAlbum.Title, oAlbum.Artist Next End Sub Sub PrintTotalSales(coll As Collection) Dim oAlbum As clsAlbum, sales As Double For Each oAlbum In coll sales = sales + oAlbum.sales Next Debug.Print "Total number sales is " & sales End Sub
Class Module Example 2
In this example, we’re going to take things a bit further. We’re going to look at some neat tricks when using objects.
Imagine you have a list of products like in the image below.
The products have different fields so we need to use a different class module for each product type. One type for a Book row, one type for a Film row.
We’ll create our class modules first. As you can imagine the are very similar for both product types
' CLASS MODULE - clsBook ' https://excelmacromastery.com/ ' Member variables Private m_Title As String Private m_Year As Long ' Properties Property Get ItemType() As String ItemType = "Book" End Property Property Get Title() As String Title = m_Title End Property Property Get Year() As Long Year = m_Year End Property ' Methods Public Sub Init(rg As Range) m_Title = rg.Cells(1, 2) m_Year = CLng(rg.Cells(1, 4)) End Sub Public Sub PrintToImmediate() Debug.Print ItemType, m_Title, m_Year End Sub
' CLASS MODULE - clsFilm ' https://excelmacromastery.com/ ' Member variables Private m_Title As String Private m_Year As Long ' Properties Property Get ItemType() As String ItemType = "Film" End Property Property Get Title() As String Title = m_Title End Property Property Get Year() As Long Year = m_Year End Property ' Methods Sub Init(rg As Range) m_Title = rg.Cells(1, 2) m_Year = CLng(rg.Cells(1, 5)) End Sub Public Sub PrintToImmediate() Debug.Print ItemType, m_Title, m_Year End Sub
As you can see, the only real difference is the Init sub.
When we read each record we need to determine if it is a Book or Film. Then we create the appropriate object. You would imagine we would have to create a variable for each type e.g.
' One variable required for each type Dim oBook As clsBook Dim oFilm As clsFilm ' If book do this Set oBook = New clsBook ' Else If film do this Set oFilm = New clsFilm
If we had lots of different types this would get very messy indeed. The good news is we only need to use one variable!
In VBA we can declare a variable as a Variant. When we use a Variant we are essentially saying “We will decide the type of variable when the code is running”.
This is very useful when dealing with objects and allows us to get away with using one variable e.g.
' Only one variable required Dim oItem As Variant ' If book set type to clsBook Set oItem = New clsBook ' Else If film set type to clsFilm Set oItem = New clsFilm
This is really useful as we only need one variable no matter how many objects we have.
A second advantage of using a Variant is this. If each Class Module has a sub/function with the same name and parameters, we can use the same variable to call it
So imagine clsBook has a function called InitBook and clsFilm has a function called InitFilm. We would need to do this:
' If clsBook If Type = "Book" Then oItem.InitBook ElseIf Type = "Film" Then oItem.InitFilm
However, if they have the same name, e.g. Init, we can replace the If\ElseIf lines of code with one line:
' this will call the Init sub of whatever type oItem is set to
oItem.Init
We can now create a function to create the appropriate object. In Object Oriented Programming, we have what is called a Class Factory. This is simply a function that creates an object based on a given type.
We saw earlier that the Initialize event does not take parameters. We can call Init in the Class Factory to get around this issue.
The full code for the ClassFactory function is here:
' https://excelmacromastery.com/ Function ClassFactory(rg As Range) As Variant ' Get product type Dim sType As String sType = rg.Cells(1, 1) ' Create an object based on the type Dim oItem As Variant Select Case sType Case "Book": Set oItem = New clsBook Case "Film": Set oItem = New clsFilm Case Else MsgBox "Invalid type" End Select ' Parse the fields to the correct class variables oItem.Init rg ' Return the product object Set ClassFactory = oItem End Function
This following is our starting sub. In this sub, we read through the worksheet and pass the range to ClassFactory.
It creates the object, passes the range to the object Parse method. Then it returns the object which we add to our Collection.
' https://excelmacromastery.com/ Sub ReadProducts() ' Create the collection Dim coll As New Collection Dim product As Variant Dim rg As Range ' Read products from the worksheet Dim i As Long For i = 1 To 2 Set rg = Sheet1.Range("A" & i & ":E" & i) Set product = ClassFactory(rg) coll.Add product Next ' Print the product details to the Immediate Window(Ctrl + G) PrintCollection coll End Sub
We can also use the variant object to print the items. As long as both objects have a sub with the same name and parameters(e.g PrintToImmediate) we can call it using a Variant type.
' https://excelmacromastery.com/ Public Sub PrintCollection(ByRef coll As Collection) Dim v As Variant For Each v In coll ' Print items v.PrintToImmediate Next End Sub
The source code for this post also includes this example using Inheritance(i.e. class interfaces). You can download the code here:
Conclusion
That concludes my post on the VBA Class Modules. In this post, we have looked at the parts of the VBA Class Module and two example cases where you would use them.
It’s important to understand that Classes and Objects is a vast topic. There are countless types of objects you can create and ways you can use them.
If you plan to use Class Modules then my advice is to start simple and get familiar with how to create a simple one. Once you have mastered the basics it will be much easier to move onto more challenging scenarios.
What’s Next?
Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.
Related Training: Get full access to the Excel VBA training webinars and all the tutorials.
(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)
Thank you for this detailed and informative article.
Some proofreading :
In the above example we cannot access Balance because it is declared as Balance.
The above should read :
In the above example we cannot access Balance because it is declared as Private.
Thanks Narayan, I have updated the post with the correct text.
Great post Paul, finally a detailed tutorial on classes
Thank you
You’re welcome Rob.
Very detailed post till now I have seen on Class Module. Thanks for this information.
You’re welcome Shweta
Good post. I have recently been learning about interfaces and implements in vba. If I understand them correctly then the above example could be extended, by making and defining a product class as an interface and then the books and film would implement the interface. Then instead of using a variant use a variable of type product, which is compatible with both book and film. The drop down list in VBE displays all the methods which have to be implemented. All methods get prefixed with the implemented class name, but as long as use an object of the product class, only need to use the method name from the interface class.
Hi Conrad,
Thanks for your commnet.
Yes you could extend the example using Interfaces.
An Interface is essentially an abstract base class. That is, a class with declarations but no implementation for the components.
I thought about including Interfaces in this post but I think they need a post of their own. I will update the post to mention them.
Paul
Hi Paul,
I’m learning VBA and I have an error when I use this class:
‘ CLASS MODULE CODE – clsAccount
Private dTotalCost As Double
Property Get TotalCost() As Long
dTotalCost = TotalCost
End Property
Property Let TotalCost(dValue as Long)
TotalCost = dTotalCost
End Property
The Property Let never ends.
My test sub is this:
Sub test()
Dim oAccount As New clsAccount
oAccount.TotalCost=100
MsgBox oAccount.TotalCost
End Sub
I think, the properties are:
Property Get TotalCost() as Long
TotalCost=dTotalCost
End Property
Property Let TotalCost(dValue as Long)
dTotalCost=dValue
End Property
Many thanks for your blog. It’s very usefull for me to understand VBA.
Have a nice day,
Daniel
Thanks Daniel,
I updated this over the weekend as someone pointed it out. You’re exactly right. The properties should be as you say.
Paul
I have an observation regarding this piece of code:
‘ CLASS MODULE – clsSimple
Public Sub Init(Price As Double)
End Sub
‘ NORMAL MODULE
Public Sub Test()
‘ Use CreateSimpleObject function
Dim oSimple As clsSimple
Set oSimple = CreateSimpleObject(199.99)
End Sub
Public Function CreateSimpleObject(Price As Double) As clsSimple
Dim oSimple As New clsSimple
oSimple.Init Price
End Function
Since I learn form your blog by typing your examples and watching carefully what they can/ can’t do while pressing F8 button, I noticed that something is lacking in the body of the ‘Init’ procedure, so:
1)I declared a variable in a class module:
Private pPrice As Double
2) I filled the body of Init procedure:
Public Sub init(dPrice As Double)
pPrice = dPrice
End Sub
3) and of course the get property to be able to read the parameter:
Property Get Price() As Double
Price = pPrice
End Property
4) and in the standard module i added a line to the test procedure, to see if it works:
Debug.Print oSimple.Price
Now I tried to run the macro only to get the error:
“Runtime Error 91: Object variable or with block variable not set”
“What the hell?” I thought, “it seems like my object doesn’t exist!”
5) So what I did was creating Initialize and Terminate procedures:
Private Sub Class_Initialize()
Debug.Print “let’s get started”
End Sub
Private Sub Class_Terminate()
Debug.Print ” We’re doomed!”
End Sub
So what happened? My object was terminated at the end of the function CreateSimpleObject. Now I was home: that function was doing something, but it didn’t return anything.
6) so adding line in the body of the function:
Set CreateSimpleObject = oSimple
made it all working
the full code in standard module:
Public Sub Test()
‘ Use CreateSimpleObject function
Dim oSimple As clsSimple
Set oSimple = CreateSimpleObject(199.99)
Debug.Print oSimple.Price
End Sub
Public Function CreateSimpleObject(Price As Double) As clsSimple
Dim oSimple As New clsSimple
oSimple.init Price
Set CreateSimpleObject = oSimple
End Function
and the class module:
Option Explicit
Private pPrice As Double
Public Sub init(dPrice As Double)
pPrice = dPrice
End Sub
Property Get Price() As Double
Price = pPrice
End Property
I don’t know, maybe it’s obvious for others, but I just want to show it from the persepctive of a person, who’s still learning and may get confused. Anyway, thanks for this lesson, as always I have learned a lot!
Hi Tomek.
Thanks for pointing that out. I updated the code to make it clearer.
Great article – just one point that books omit (and I see you too have also done).
To add a property, instead of typing it, go to Insert, then Procedure, the Property.
Notice the keyword ByVal is automatically added to the Let Property.
Any reason why the VB editor does that?
If you:
A) deleted the keyword what will happen
B) if you added the keyword, ByRef instead, what will be its behaviour?
because the default for passing arguments is ByRef, so why does the keyword ByVal get added automatically? Can it only be ByVal?
Thanks
Hi Simon.
Using Insert is a handy way of creating the Property. When somebody uses them first it is a good idea to type them out so they understand them.
A) ByRef is the default and is used if you don’t specify a type
B)
1. ByVal passes a copy of the variable so if you change the value the original variable will not be changed.
2. ByRef passes a reference so if you change the value the original will be changed.
It’s a good idea to use ByVal as you don’t want the variable you pass to a property to be changed in the property.
Paul, thanks for the reply.
I understand ByRef is deemed to be the default way of passing args but I was curious as to why when inserting a Let Property, the VB Editor adds ByVal?
Surely if ByRef is the default, it should be adding that.
The idea is that the variable you pass should not be changed by the property. Using ByVal prevents this happening.
In general, when you pass any basic data type (e.g. long, double, string) to any sub/function it is a good idea to use byVal to prevent the original value being changed.
This means ensures the variable you pass will not get changed by accident.
Thanks!
if i’m right, then a construction like:
Unit.collArticles.Count is not possible.
while
collArticles.Count can.
Hi Ludo,
It is possible to do it like that. However it is better practice to put collArticle.Count as a property.
Then you would use Unit.Count to get the number of items in Unit.
Brilliant – thanks heaps Paul you are a gem.
Thanks John,
Hope you are keeping well and glad you enjoyed the post.
Paul
I have read all your posts, you made a remarkable work!
Clear, comprehensive, systematic, friendly, informative, functional
I like your new look and style and of your blog..
Looking forward for you next post!
Thanks
Abe
Great explanation of how to use class module. Thank you!
You’re Welcome!
Paul I just want to let you know I am so glad I register to this class. I am learning so much!
Thank you
Thanks Elba
Hi Paul,
Is it odd that no matter if I add ByRef or ByVal or omit altogether in this line:
Property Let SomeString(ByVal abc As String)
I still get in the immediate window, “Hi”.
I thought ByVal doesn’t change the Calling procedure.
Option Explicit
Dim pSomeString As String
Property Get SomeString() As String
SomeString = pSomeString
End Property
Property Let SomeString(ByVal abc As String)
abc = “Hi”
pSomeString = abc
End Property
Sub Test()
SomeString = “Hello”
Debug.Print SomeString
End Sub
Hi Doug,
Using ByVal creates a copy of the variable passed as a parameter. This means if the variable is changed in the procedure it will not be changed in the procedure that called it.
For example
Thanks Paul but why does my example change, even though I put ByVal?
Hi Doug,
I see what you mean now. When a reference is passed to a property it behaves differently than passing it to a sub function. See this stackOverFlow question for more details.
Paul
Hi Paul, in the section
“Difference 1 – How the modules are used”
‘ CLASS MODULE CODE – clsCustomer
Print Sub PrintCustomer()
Print Sub PrintCustomer() should be Public Sub PrintCustomer()
Regards,
Jaime
Thanks Jaime, I updated that code.
Hi Paul, in the section
“Why we use Properties”
‘ CLASS MODULE CODE – clsCountryList
‘ Array
Private arrCountries() As String
Public Function Count() As Long
Count = UBound(sCountries) + 1
should be “Count = UBound(arrCountries) + 1”
End Function
Regards,
Jaime
Hi Paul,
I need to learn how to use VB scripting from scratch. I would like to take an advance course of some sort which will assist me with reporting. Please can you advise or provide guidance on the courses I can attend or do online?
Most of my issues are things that I cannot seem to Google the solution for.
Hi Janeca,
If have written a book aim at a person learning from scratch. It contains tons of examples and you can get at Excel Macro Mastery – How You Can Write VBA Like a Professional in 15 Simple Steps.
If you want to become more advanced and create VBA reporting applications etc. then I have a package called The Excel VBA Handbook which shows you how to build 10 VBA Applications from scratch. Full code and instructions provided.
I hope this helps
Paul
Simply awesome! I got so much better understanding of how classes are used in VBA. Was reading at many other sites and books – this one is the best. Specially the examples are very well set and down to earth – exactly what needed.
Was looking for the way to optimize my code – pretty sure I found it. Thanks so much for your work, it’s simply priceless. I am afraid to imagine what you offer in the book for the money.
Thanks Yury. Glad you liked it.
I think there is a minor error in your example
Sub ReadProducts()
… Set rg = Sheet1.Range(“A” & i & “:E” & i)
End Sub
To this..
Set rg = Sheets(1).Range(“A” & i & “:E” & i)
Hi David,
It isn’t an error
Sheet1 refers to the code name of the worksheet.
Sheets(1) refers to the sheet at the leftmost tab in the workbook.
Paul
Great tutorial, Paul. I’m not an expert by any means, but in one of your examples you have
‘ Declare the Variable
Dim oAlbum = clsAlbum
Shouldn’t that be
Dim oAlbum As clsAlbum
Or is it OK to use = without New?
Jerome
Hi Jerome,
It’s a typo. Should be “As clsAlbum”. I’ve updated the code.
Thanks
Paul
Thank you very much for tutorials.
I made useful userforms using class modules :
– Fast percentage calculation form
– Product discount calculation according to quantity,price and percent rate
– Hiding / displaying the columns of sheet (Column management with userform)
Thanks you a lot for this free tutorial! It is easy to follow and understand!
Hi Paul
Can you recommend any article about Interfaces implementation in VBA?
Hi Yury,
If you google “VBA Implements” you will see a list of results. The Chip Pearson article is pretty good.
Paul
Thank you, Paul,
Good and very clear explanation of Class modules. Helped me a lot.
Glad you liked it Zahari.
Paul
Learning lost from your posts. They are very useful and informative. Quick question on memory usage. If I create a Class Module with a function that contains say 100 lines of code, and then create array of 1,000,000 objects from that class, an I creating 1,000,000 copies of the class function code in memory?
Thanks,
Simon
Hi Simon,
There is just one copy of the function created. The same function is used each time a call to it is made.
A pointer is used in the class object to link it to the function.
Paul
Thank you for the guide 🙂
I think there is a typo, it says “Prices” and then “Price”, here:
Property Get Prices() As Collection
Set Price = m_collPrices
Frank
Thanks Frank.
I have updated this typo.
Excellent guide.
Simple, concise, easy to follow and comprehensive.
Hi Paul,
Thank You, the first class tutorial that explains the things clearly. But could You add a post related to WithEvents. I saw a great example of using class event that is hard to exchange for non-class procedures ( it was a calculator tool). Part of the code consists of:
“Public WithEvents GroupBoutons As MSForms.CommandButton”
Thanks for this BLOG.
Glad you enjoyed the tutorial. I will add WithEvents to my list of topics to write about.
Hi Paul, very good post you wrote. Cheers for the effort to make it so clear with all the examples in between.
Would you agree that the following two statements should have words switched?
“we use Set to assign an object variable to an object”
“Set is used to assign a variable to a an object.”
It should respectively be:
“we use Set to assign an object to an object variable”
“Set is used to assign an object to an object variable”
For reference, here is the Set statement by the official definition:
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/set-statement
Glad you liked it.
Yes – these lines should have been written like the Let lines. I have updated the post.
Thanks for sharing Paul, this brought me up to speed fairly quickly!
I’m trying to build objects that are assigned the same name as the Class Module Variable. Is it possible to pass this string to the class module with a ‘Me’, ‘This’ or something similar? For example:
Private x As ClassModule
Set x = New ClassModule
x.ClassModule ThisClassModule.Name, 10
I can see the variable name in the locals window but not sure how it could be referenced in the code.
Glad you liked it Ted.
You can use TypeName(x)
Thanks Paul!
Is there a way to use TypeName(ClassModule) in order to return x?
No luck so far but I’ll keep playing with it. I figure if x is in the locals window it has to be hanging around somewhere.