[VBA] How to Create Custom Objects Using Class Modules (Basic)

Introduction

One powerful feature that takes VBA programming to the next level is the Class Module.

While a User-Defined Type (UDT) is simply a container for grouping related data (variables), a Class allows you to bundle Data (Properties) and Logic (Methods) into a single, highly functional “Blueprint (Object).”

For example, if you think of a “Product” class:

• Properties (Data): Product Name, Price, Stock Quantity
• Methods (Logic): DisplayInfo (Show details), UpdateStock (Change stock count)

By encapsulating data and related operations into one object, your code becomes much more structured, reusable, and easier to maintain.

In this article, I will explain the basic steps to create and use your own custom class in VBA.

Overview of the Steps

1. Create a Class Module: Create the “blueprint” and define its properties and methods.
2. Create a Standard Module: Write code to create an “instance” (actual object) of that class and use it.

Step 1: Create a Class Module

First, we create the class that serves as the blueprint for the object.

1. In the VBA Editor (VBE), go to Insert > Class Module.
2. In the Properties window, change the (Name) to ClsEmployee.
3. Paste the following code:

Class Module: ClsEmployee

' --- Private Variables (Accessible only inside the class) ---
' Variables to hold the object's data
Private p_EmployeeID As String
Private p_FullName As String

' --- Properties (Gateways to access data from outside) ---

' EmployeeID Property (Write-only)
Public Property Let EmployeeID(value As String)
    p_EmployeeID = value
End Property

' FullName Property (Write-only)
Public Property Let FullName(value As String)
    p_FullName = value
End Property

' --- Methods (Actions the object can perform) ---

' Method to display information
Public Sub DisplayInfo()
    MsgBox "ID: " & p_EmployeeID & vbCrLf & _
           "Name: " & p_FullName, vbInformation, "Employee Info"
End Sub

Step 2: Use the Class in a Standard Module

Next, write the code in a Standard Module to actually create and use an object based on the ClsEmployee class.

Standard Module: Module1

Sub UseCustomClass()

    ' 1. Declare a variable using the created class type
    Dim emp As ClsEmployee
    
    ' 2. Create a new object (instance) using the New keyword
    Set emp = New ClsEmployee
    
    ' 3. Set values to the object's properties
    emp.EmployeeID = "EMP-101"
    emp.FullName = "Ichiro Sato"
    
    ' 4. Call the object's method
    emp.DisplayInfo

End Sub

Execution Result

When you run the UseCustomClass macro, a message box titled “Employee Info” will appear, displaying the ID and Name you configured.

Code Explanation

Class Module (ClsEmployee)

• Private p_EmployeeID As String We declare private variables used only inside the class. It is standard practice to make these Private to protect the data from being accessed directly from the outside.
• Public Property Let EmployeeID(...) This defines a Property. It provides an “Entrance (Let)” for external code to assign values like emp.EmployeeID = "...". The received value is stored in the private variable p_EmployeeID. (You can also define a Get property to read values).
• Public Sub DisplayInfo() This defines a Method. It executes a specific action using the class’s internal data.

Standard Module (Module1)

• Dim emp As ClsEmployee We declare a variable using our custom class ClsEmployee as the data type, just like String or Range.
• Set emp = New ClsEmployee This line creates a specific Object (Instance) in memory from the “Blueprint” (Class). The New keyword is essential here.
• emp.EmployeeID = "EMP-101" We use the property defined with Property Let to set the object’s internal data.
• emp.DisplayInfo We call the method defined with Public Sub to make the object perform an action.

Summary

In this article, we covered the basics of creating custom objects using Class Modules in VBA.

1. Define the object’s Properties (Data) and Methods (Logic) in a Class Module.
2. Generate an object (Instance) from the class using the New keyword in a Standard Module.
3. Access the object’s properties and methods using dot notation (.).

Mastering classes is the gateway to Object-Oriented Programming (OOP). It might feel complex at first, but it is a powerful tool for modularizing your code and significantly improving reusability and maintenance.