VBA Classes

A Class is essentially a blueprint for an object. Object instances are created from a Class the way a house is built using a blueprint. Classes are used to achieve abstraction and encapsulation by storing related state and behavior together and restricting access to certain details within the class while providing a public API for client programs to interact with. Classes can have associated events which trigger code to run when something happens. Classes have a built-in initialize event which is triggered when an object is instantiated and a built-in terminate event which is triggered when the instance is released. In VBA, classes are created using Class Modules. The code modules associated with UserForms and application-specific objects such as Excel Workbooks and Worksheets are class modules with an associated object.

Class Modules

Classes in VBA are written inside class modules. The name of the class module itself is the name of the class and all code inside the class module is part of the class.

Add a Class Module

To insert a Class module into a VBA project right-click in the project explorer and select insert class module or import the class from a file.

Class Module Properties

To change the name of a class change the Name property of the class module.

The Class module's Instancing property can be set to Private or PublicNotCreatable. When the Instancing property is set to Private, which is the default, the class can only be used inside the project in which the class module is located. When the Instancing property is set to PublicNotCreatable the class can be used in outside projects but cannot be instantiated outside the project. PublicNotCreatable classes must be instantiated within the project and passed to an outside project from a public function.

Member Accessibility

Procedures and variables declared in a class module can be declared Public or Private. Public members are accessible from anywhere outside the class including from other VBA projects. Private members can only be accessed inside the class. Private variables or constants in a class can be exposed outside the class using Public Property procedures.

Procedures in a class module can also be declared Friend. Variables cannot be declared using Friend. Friend procedures are accessible from anywhere outside the class and within the VBA project where the class module is located.

Constants, Enums, User-Defined Types, Arrays, and Declare Statements can only be declared Private inside class modules. Fixed-Length Strings cannot be declared inside a class module.

Property Procedures

Property procedures are used to assign or retrieve properties of a class. There are three types of property procedures: Get, Let, and Set. Get is used to retrieve the value of a property. Let is used to assign the value of a property which is a value type variable. Set is used to assign a property which is an object variable. Exit Property can be used to exit a property procedure early.

'Standard Module: Module1

Option Explicit

Public Sub Example()

    Dim E As clsExample
    Set E = New clsExample

    E.Message = "Hello, World!"
    Debug.Print E.Message

    Set E.Coll = New Collection
    Debug.Print E.Coll.Count

End Sub

'Class Module: clsExample

Option Explicit

Private pMessage As String
Private pColl    As Collection


'''Assign and retrieve value type variable

Public Property Let Message(RHS As String)
    pMessage = RHS
End Property

Public Property Get Message() As String
    Message = pMessage
End Property


'''Assign and retrieve object variable

Public Property Set Coll(RHS As Collection)
    Set pColl = RHS
End Property

Public Property Get Coll() As Collection
    Set Coll = pColl
End Property

Initialize and Terminate Event Procedures

When an Initialize procedure is declared inside a class module, it will be executed when an instance of the class is created. When a Terminate procedure is declared inside a class module, it will be executed when the instance is released.

'Standard Module: Module1

Option Explicit

Public Sub Example()

    Dim C1 As Class1

    'Class1 instance created. Initialize event triggered.
    Set C1 = New Class1 

    'Class1 instance terminated. Terminate event triggered.
    Set C1 = Nothing 

End Sub

'Class Module: Class1

Option Explicit

Private Sub Class_Initialize()
    Debug.Print "Class Initialized"
End Sub

Private Sub Class_Terminate()
    Debug.Print "Class Terminated"
End Sub

To add an Initialize or Terminate event procedure to a class module use the object box and procedure box drop-downs in the class module's code window to select the class and then select the event procedure to add.

Me Keyword

The Me keyword is used inside a class module to refer to the specific instance of the class which is executing the code. The Me keyword can be used to access the Public Properties of the current class instance from within the class. The Me keyword can be used to pass the current instance of the class as an argument to another procedure.

'Standard Module: Module1

Option Explicit

Public Sub Example()

    Dim C1 As Class1
    Set C1 = New Class1

    C1.Variable = "Test"
    C1.PrintVariable

End Sub

'Class Module: Class1

Option Explicit

Private pVariable As Variant

Public Property Let Variable(Value As Variant)
    pVariable = Value
End Property

Public Property Get Variable() As Variant
    Variable = pVariable
End Property

Public Sub PrintVariable()
    Debug.Print Me.Variable
End Sub