[VBA] How to Define Methods (Sub/Function) in a Class

Introduction

A VBA Class Module is a blueprint for an “Object” that combines data (Properties) and the processing to manipulate that data (Methods). While properties represent the “state” of an object, methods define the object’s “behavior.”

For example, if you have a “Product” object:

• Properties: Name, Price (Data)
• Methods: ApplyDiscount, GetInfoText (Operations)

By using classes, you can encapsulate data and related operations together.

In this article, I will explain how to define and use your own methods within a Class Module using Public Sub and Public Function.

VBA Sample Code: Defining Methods in a Class

In this example, we will create a class named ClsInventory to represent product inventory. We will implement a method to add stock and a method to return the current inventory status as a text string.

Step 1: Create the Class Module

1. In the VBE (Visual Basic Editor), select Insert -> Class Module.
2. In the Properties window, change the (Name) to ClsInventory.
3. Paste the following code into the class module.

Class Module: ClsInventory Code

' --- Properties ---
Public ItemName As String
Public Quantity As Long

' --- Method (Sub Procedure) ---
' A method that executes an "Action" to increase stock
Public Sub AddToStock(ByVal amount As Long)
    ' Add the amount passed in the argument to the current Quantity
    Me.Quantity = Me.Quantity + amount
End Sub

' --- Method (Function Procedure) ---
' A method that returns information as a "Value" based on the current state
Public Function GetStatusText() As String
    GetStatusText = Me.ItemName & ": Current Inventory is " & Me.Quantity & " units."
End Function

Step 2: Use the Class Methods in a Standard Module

Next, write the code in a Standard Module to actually create an object from the ClsInventory class and call its methods.

Standard Module: Module1 Code

Sub ManageInventory()
    ' 1. Create a new object (instance) from the class
    Dim item As New ClsInventory
    
    ' 2. Set initial values for the object's properties
    item.ItemName = "A4 Copy Paper"
    item.Quantity = 100
    
    ' 3. Call the Sub method to change the object's state
    item.AddToStock 50 ' Add 50 units to stock
    
    ' 4. Call the Function method to get information from the object
    Dim status As String
    status = item.GetStatusText()
    
    ' Display the result in a message box
    MsgBox status, vbInformation, "Inventory Status"

End Sub

Execution Result

When you run the ManageInventory macro, a message box will display: “A4 Copy Paper: Current Inventory is 150 units.”

Explanation of the Code

Public Sub AddToStock(…)

A Public Sub written inside a Class Module becomes a method of that class.

• This method is called like item.AddToStock 50.
• It performs an “action” that changes the values of the object’s own properties (Me.Quantity).
• Since it is a Sub, it does not return a value.

Public Function GetStatusText() As String

A Public Function written inside a Class Module also becomes a method.

• This method is called like status = item.GetStatusText().
• It calculates or formats a value based on the object’s properties (ItemName or Quantity) and returns the result.
• Since it is a Function, it returns a value.

The “Me” Keyword

Using the Me keyword inside a Class Module refers to the object instance itself. While Me.Quantity works similarly to writing ClsInventory.Quantity in a standard module logic, using Me ensures you are referring to the specific instance where the code is currently running. It is the standard and safest way to reference internal properties.

Summary

In this article, I explained how to define custom methods (behaviors) in VBA classes.

• Public Sub in a class module becomes a method for executing actions on the object.
• Public Function in a class module becomes a method for returning values based on the object’s state.

By properly grouping properties (data) and methods (processing) within a class, you promote modular code. This makes it easier to reuse components from other programs, which is a crucial design concept for building large and complex applications.