VBA Class Modules

What is Excel VBA Class Modules?

Excel VBA Class Modules allow you to create custom objects in VBA. These objects can encapsulate data and code, providing a structured and modular approach to programming in Excel. With VBA Class Modules, you can define your own properties, methods, and events, allowing for more efficient and organized code development.

Let us look at the following example of VBA Class Modules. In this example, we have a class module named “Person” with a single private variable “mName” and two properties: “Name” (both Get and Let). The property “Name” allows getting and setting the value of the private variable.

The standard module “TestPersonClass” demonstrates using the “Person” class. It creates an instance of the “Person” class named “person,” sets the name using the Name property, and displays a message box with a greeting using the person’s name.

When you run the “TestPersonClass” subroutine, it will display a message box saying “Hello, Peter Andrews!”.

What is the Class Module in VBA?

A Class Module is a blueprint for creating objects. It defines the structure and behavior of objects in a particular class.

It is a container for variables, properties, procedures (methods), and events. When you create an instance of a class, you are essentially creating an object based on the class definition.

Each object can have its own unique set of properties and methods, allowing for more flexibility and reusability of code.

Examples

Example #1

Suppose you want to manage employee data in Excel. You can create a Class Module called “Employee” with properties like Name, Age, and Salary.

You can then define methods to calculate bonuses, update employee information, and perform other relevant operations. With this class, you can create multiple instances of the Employee object, each representing a different employee, making it easier to manage and manipulate the data.

• Step 1: Here, we create the Employee Class Module.

• In the module, we define private variables (“mName,” “mAge,” “mSalary”) to store the employee’s name, age, and salary.
• The Public Property Get and Property Let procedures define properties for accessing and modifying the private variables.

For example, the Name property consists of a Property Get procedure (to retrieve the value of the Name property) and a Property Let procedure (to set the value of the Name property).

• Open the Visual Basic Editor (VBE) in Excel by pressing Alt+F11.

• Insert a new Class Module by clicking on “Insert” in the VBE menu and selecting “Class Module.”

• In the Class Module window, change the default name “Class1” to “Employee” (you can do this by simply editing the “(Name)” property in the Properties window on the left side).

• Step 2: In the code window for the Employee class module, we include the following VBA code:

• The private variables “mName,” “mAge,” and “mSalary” will store the data for the properties in the class.

• Step 3: The below line defines a property called “Name” with a Property Get procedure. The procedure returns the value of the private variable “mName” when the property is accessed.

• Step 4: This line defines a property called “Name” with a Property Let procedure. The procedure assigns the value passed as an argument to the private variable “mName” when the property is modified.

The same pattern is followed for the “Age” and “Salary” properties.

Save the class module and exit to return to the Excel Workbook.

• Step 5: Now, create a subroutine for testing the Employee Class. The below lines declare two variables, “emp1” and “emp2”, as objects of the Employee class using the “New” keyword.

• Step 6: These below lines set the properties of the emp1 and emp2 objects. Each object’s properties (Name, Age, and Salary) are assigned specific values.

• Step 7: The below lines show message boxes showing the employee’s name and bonus amount.

• Step 8: Now, save the module and exit the VBE. Now press “Alt + F8” to open the Macro list, select “TestEmployeeClass” and then run the macro.

• Step 9: We see a message box showing each employee’s name and bonus amount by calling the “CalculateBonus” function.

Here is the full code:

• Class Module Code:

Private mName As String
Private mAge As Integer
Private mSalary As Double

Public Property Get Name() As String
Name = mName
End Property

Public Property Let Name(ByVal value As String)
mName = value
End Property

Public Property Get Age() As Integer
Age = mAge
End Property

Public Property Let Age(ByVal value As Integer)
mAge = value
End Property

Public Property Get Salary() As Double
Salary = mSalary
End Property

Public Property Let Salary(ByVal value As Double)
mSalary = value
End Property

Public Function CalculateBonus() As Double
‘ Customize your bonus calculation logic here
CalculateBonus = 0.1 * mSalary
End Function

• Module code:

Sub TestEmployeeClass()
Dim emp1 As New Employee
Dim emp2 As New Employee

emp1.Name = “John Smith”
emp1.Age = 30
emp1.Salary = 50000

emp2.Name = “Jane Doe”
emp2.Age = 35
emp2.Salary = 60000

MsgBox emp1.Name & ” Bonus: $” & emp1.CalculateBonus
MsgBox emp2.Name & ” Bonus: $” & emp2.CalculateBonus
End Sub

Example #2

Imagine you must simulate a banking system in Excel.

1. You can create a Class Module called “BankAccount” with properties like AccountNumber, Balance, and Owner.
2. The class can include methods to deposit, withdraw, and calculate interest.
3. By creating multiple instances of the BankAccount object, you can model multiple bank accounts within your Excel application.

• Step 1: Create a new Class Module named “BankAccount” following the same steps mentioned in the earlier example.

• The “BankAccount” class module defines private variables (mAccountNumber, mBalance, mOwner) to store the account number, balance, and owner’s name.
• Property Get, and Property Let procedures are used to create properties for accessing and modifying the private variables.

Enter the following code in the “BankAccount” class module:

• Step 2: This line defines a property called “AccountNumber” with a Property Get procedure. The Property Get procedure returns the private variable “mAccountNumber” value when the property is accessed.

• Step 3: This line defines a property called “AccountNumber” with a Property Let procedure. The Property Let procedure assigns the value passed as an argument to the private variable “mAccountNumber” when the property is modified.

• Step 4: The same pattern is followed for the “Balance” and “Owner” properties.

• Step 5: The below line defines a public subroutine called “Deposit,” which takes an amount as an argument and adds it to the account balance (“mBalance”).

• Step 6: Below, we define a public subroutine called “Withdraw,” which takes an amount as an argument and checks if the withdrawal amount is less than or equal to the account balance. If so, it deducts the amount from the balance “(mBalance”). If the amount exceeds the balance, it displays a message box indicating insufficient funds.

• Step 7: Below, we define a public subroutine called “CalculateInterest,” which takes an interest rate as an argument and calculates the new balance (“mBalance”) by multiplying the existing balance with the factor (“1 + interestRate”).

• Step 8: Close the Class Module window and return to the Excel workbook. Enter the following code to test the “BankAccount” class in a regular module. Here, we create instances of the BankAccount class.

• Step 9: These below lines set the properties of the acc1 and acc2 objects. Each object’s properties (AccountNumber, Balance, and Owner) are assigned specific values.

• Step 10: Below, we call the Deposit and Withdraw subroutines of the “BankAccount” class on the acc1 and acc2 objects, respectively. The Deposit subroutine adds 5000 to the account balance (“mBalance”) of acc1, while the Withdraw subroutine subtracts 2000 from the account balance of acc2 if sufficient funds are available.

• Step 11: This below line calls the “CalculateInterest” subroutine of the “BankAccount” class on the acc1 object, passing the interest rate (0.05) as an argument. The “CalculateInterest” subroutine calculates and updates the account balance (mBalance) of acc1 based on the specified interest rate.

• Step 12: These lines display message boxes showing the current balance of acc1 and acc2. The balances are accessed using the Balance property of the “BankAccount” class.

• Step 13: Now, save the module and exit the VBE. Now press “Alt + F8” to open the Macro list, select “TestBankAccountClass” and then run the macro.

• Step 14: Once you run the Macro, you will see a message box that displays the balances of Account 1 and Account 2

Here is the full code:

• Class module code:

Private mAccountNumber As String
Private mBalance As Double
Private mOwner As String

Public Property Get AccountNumber() As String
AccountNumber = mAccountNumber
End Property

Public Property Let AccountNumber(ByVal value As String)
mAccountNumber = value
End Property

Public Property Get Balance() As Double
Balance = mBalance
End Property

Public Property Let Balance(ByVal value As Double)
mBalance = value
End Property

Public Property Get Owner() As String
Owner = mOwner
End Property

Public Property Let Owner(ByVal value As String)
mOwner = value
End Property

Public Sub Deposit(ByVal amount As Double)
mBalance = mBalance + amount
End Sub

• Class module code continuation:

Public Sub Withdraw(ByVal amount As Double)
If amount <= mBalance Then
mBalance = mBalance – amount
Else
MsgBox “Insufficient funds.”
End If
End Sub

Public Sub CalculateInterest(ByVal interestRate As Double)
mBalance = mBalance * (1 + interestRate)
End Sub

• Module code:

Sub TestBankAccountClass()
Dim acc1 As New BankAccount
Dim acc2 As New BankAccount

acc1.AccountNumber = “123456789”
acc1.Balance = 10000
acc1.Owner = “John Smith”

acc2.AccountNumber = “987654321”
acc2.Balance = 5000
acc2.Owner = “Jane Doe”

acc1.Deposit 5000
acc2.Withdraw 2000

acc1.CalculateInterest 0.05

MsgBox “Account 1 Balance: $” & acc1.Balance
MsgBox “Account 2 Balance: $” & acc2.Balance
End Sub

Class Module vs. Objects

VBA Class Modules and objects go hand in hand. A VBA Class Modules is like a blueprint or template that defines the structure and behavior of objects.

On the other hand, an object is an instance of a class representing a specific entity. Class Modules provide the ability to create multiple objects based on the same blueprint, allowing for code reusability and modularity.

Each object can have its own set of properties and methods, making it easier to manage and manipulate data.

Important Things To Note

• VBA class module constructors are supported, which are special procedures automatically executed when an object is created.
• VBA Class Modules do not support destructors, so you must manually release any resources or clean up after the object’s usage. A VBA Class Module destructor, or a finalizer or cleanup method, is a special method automatically called when an object is being destroyed or going out of scope.
• It is important to note that changing the name of a Class Module can break existing code that references the old name. Therefore, it is recommended to be cautious when renaming Class Modules and update all related references accordingly.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA Class Modules. Here we discuss about class modules & how to use them in Excel VBA code, along with examples & points to note. You can learn more from the following articles –

• VBA Charts
• VBA ChDir
• VBA Active Cell