What is a Class Module in Visual Basic For Applications?

Introduction

I recently found myself going down the rabbit hole of learning an API for a CAD application , and stumbled upon totally different jargon I had not come across before in VBA, and also the concept of the Class Module.

VBA Class modules differ from regular modules in that they are built into VBA to allow you to make your own custom Objects. It's that simple.

A way to visualize this concept is pretend VBA is like a car factory, the class module would be the blueprint or design for a car, and the car that is produced from the factory is like the Object that is produced from the class module.

These Class Modules, also known and COMs (Component Object modules) differ from regular modules in that normal modules store variables, functions and sub procedures. Class modules can do this too, except they also allow the user to create custom objects that perform very similarly to VBA's built in Objects.

In VBA world, think of Objects like building blocks, they can be used anywhere, over and over, without having to re-write large chunks of code. Very, very useful!

Typically these user created objects can be used behind a UserForm in a custom macro or application, used in a standard module, or the class module itself could be made into an add-in, depending on the needs of the application and level of creativity of the user!

Classes are either created (initialized) when they are referenced for the first time, or when Set and New keywords are used together, but the behaviour is different; the Object is created and initialized when Set is used.

Pros & Cons

There are both advantages, and disadvantages to using a class module, there are situations when there really is no point to use one and create a Custom Object.?

Pros

• They allow us to build objects like building blocks
• A class module can be used in another class module or in a standard module
• The code of an object is self contained
• The code is totally independent from any other code in an application
• multiple copies of the same object can be created
• Using objects allows us to build an application one block at a time
• It's easier to troubleshoot and test individual parts of the application
• Updating the class code wont create problems in other parts of your application
• Easy to add objects between various applications

Cons

• It takes time to build the COMs
• Naming conventions are important as this is what is seen when the COM is used within a normal module.
• Tricky tor define what an object actually is
• Learning curve with understanding objects - in the beginning.
• Time and planning is required initially to design the objects

It goes without saying that an new macro or application needs to be well thought out before thinking about writing any code. In some situations, a class module / Object creation will be the perfect fit, and in others you can get away with doing things the normal way using a regular module.

Useful terms, definitions, and examples

Below are a set of rules and definitions for the Class Module:

Method:?Public function or sub procedure in the class module

Member Variable:?Variable declared inside the class module;?

??????????????????????creating a variable example:??Public Name as string

Property: Functions or sub procedures that behave like variables

Property types: Get, Set, Let

Event - Initialize: Sub procedure that automatically runs when the class module Object is created

Event - Terminate: Sub procedure that runs automatically when the class module Object is deleted

Declaring and Creating a class Object ( Static ): Dim x as New ClassY

Declaring and Creating a class Object ( Dynamic ) :?Dim x as New ClassY; Set x = New ClassY

Calling a class module sub procedure: x.SubExample ExampleCalc

Calling a class module function: calculation = x.calculate()

Using a class module property: x.property1 = 1; totalCalc = x.Calculation

New:??The New keyword is used?to create an objects from a class module

Private - means it is only accessible within the class module

Public - means it can be accessed from outside of the class module

How it all goes together.....

As mentioned, Class Modules take a bit of time to understand and to get your head around, but once you do, the versatility will seem obvious!

Essentially there are 4 main parts to a class module:

1. Members (variables)
2. Methods (functions and sub procedures)
3. Properties (functions and sub procedures that behave like variables)
4. Events (sub procedures that happen at start and end of class creation)

I will go in depth to describe each of these parts.?

Members (Public and private Variables)

Variables in a Class Module can be either Public or Private. It is important to note that any variable that is declared inside a class (even those that are declared as public) are not available for use outside of the class module, they become properties of the class module (Object). Public variables can be assigned values outside of the class module, but they cannot be used.

An interesting detail to note is that Dim and Private declarations treat the variable the same, they are only accessible within the module or sub procedure that they stored in, nowhere else.

As mentioned, declaring a variable as Public does not mean that it is accessible outside of the Class Module, unlike regular modules. Declaring a public variables makes it accessible to having a value assigned to it within sub procedures, and functions via the class module they reside in only. They?are used to declare the properties for the Object (Class Module).

Private variables on the other hand behave like Dim in regular modules. They are only able to be used within the sub procedure, function, they are declared in. This essentially means that the properties you create can only be modified within property Get, Let, and Set procedures, which we will get into soon....

Methods

These are actions that can be performed by the Object or on the Object. These actions are associated specifically to the Object, and can cause changes, and modifications to the Object itself. They can have arguments, and can also return something if required by the user.

The most simple type of method is the no return type, but return type methods are most useful.

In general, methods may require information to be supplied as arguments, these arguments can also be optional. They may return a value, and are more likely to use or have parameters than a property.

Properties

These behave very similarly to regular variables, except in order to assign a value there are certain steps that must be taken.

The whole point of using properties is to hide the implementation and execution away from the caller, it keeps things nice and neat.

When a property is declared public they become available as properties of the class.

As mentioned earlier, properties hide the details of all the code execution from the user. They behave very similarly to a variable upfront to the person using the property, but it is not the same as making a variable public and using it directly.

Functions, and sub procedures will behave exactly the same if they are treated as a property. So often it is easier to create a property instead of a function for the added versatility of using the Get, Let/Set keywords.

There are 3 keywords used to modify and retrieve properties: Get, Let, and Set.

The Get keyword retrieves a property for a user, it is essentially retrieved for reading purposes only.

The Let/Set keywords do similar things in that they both assign values, the Let keyword will assign a value to a property of the class, where as Set keyword will assign an Object to a property of the class.

Events

Regular modules cannot handle events of any kind, the functionality is not built into them. Class modules one the other hand do have this functionality, and this is why they are so versatile to use. These events are essentially built in sub procedures.

These events occur every time a new instance of the class module (object) is created. It gives you the ability to do the following:

• Initialize and run empty or with as many statements as you would like.
• Initialize property values
• Create references to other objects

One thing to note with event statements is they cannot take any arguments or parameters.

There are two types of events:

Initialize

The initialize event can be created by either typing out Private Sub Class_Initialize() or by selecting it from the dropdown.

Terminate

The terminate class looks like this: Private Sub Class_Terminate()

The terminate event happens when a class is terminated; When the Object is set to nothing; When the last reference to the Object is released, which will happen when a variable goes out of scope

To continue with original analogy associating Class Modules with Cars, lets create an example. We will create a car_object class, which we will then use to create two car Objects with the same set of properties. These properties will be blank at the start of each car objects creation, but will be populated and shown to the user at the end.

First up we need to create a a class module called cars_object.

This class will create a a car object that will contain the following properties:

• Engine on
• Odometer reading
• Make
• Model
• Year
• Number of owners
• Active registration

This is done by going into VBA right clicking on Modules > Insert > Class Modules

Rename the Class Module to cars_object and Copy and paste the code below:

Private engine_status As Boolean
Private car_is_on As String
Private CurrentOdometer As Double
Private make As String
Private model As String
Private year As Integer
Private NumberOfOwners As Integer
Private ActiveRegistration As Boolean



'#####################################################################
'PROPERTIES
'Odometer reading property
Property Get OdometerReading() As Double
? ? OdometerReading = CurrentOdometer
End Property


'Changing the odometer reading
Property Let OdometerReading(NewOdometerReading As Double)
? ? CurrentOdometer = NewOdometerReading
End Property


'Make
Property Get car_make() As String
? ? car_make = make
End Property


Property Let car_make(CarMake As String)
? ? make = CarMake
End Property


'Model
Property Get car_model() As String
? ? car_model = model
End Property


Property Let car_model(CarModel As String)
? ? model = CarModel
End Property


'Year
Property Get car_year() As String
? ? car_year = year
End Property


Property Let car_year(CarYear As String)
? ? year = CarYear
End Property



'Number of owners
Property Get car_owners() As String
? ? car_owners = NumberOfOwners
End Property


Property Let car_owners(carOwners As String)
? ? NumberOfOwners = carOwners
End Property



'Regitration status
Property Get has_active_registration() As Boolean
? ?has_active_registration = ActiveRegistration
End Property


Property Let has_active_registration(rego As Boolean)
? ? ActiveRegistration = rego
End Property


'#####################################################################
'METHODS
'Turn the engine ON or OFF
Public Sub EngineOperationState(Ignition As Boolean)
? ??
? ? engine_status = Ignition
? ??
End Sub


'Add a travelled distance to the odometer
Public Function new_odometer_reading(DistanceTravelled As Double)

? ? CurrentOdometer = CurrentOdometer + DistanceTravelled
? ??
End Function


Public Sub showCarAttributes()


Dim is_engine_on_off As String
Dim is_registration_active As String


If ActiveRegistration = False Then
? ? is_registration_active = "Inactive"
Else
? ? is_registration_active = "Active"
End If


If engine_status = False Then
? ? is_engine_on_off = "OFF"
Else
? ? is_engine_on_off = "ON"
End If


MsgBox ("Make: " & make & vbNewLine & "Model: " & model & vbNewLine & "Year: " & year & vbNewLine & "Number Of Owners: " & NumberOfOwners & vbNewLine & "Active Registration: " & is_registration_active & vbNewLine & "Engine status : " & is_engine_on_off)


End Sub


'#####################################################################
'EVENTS
'This sub procedure executes when the object is first created
Private Sub Class_Initialize()


MsgBox ("new object started....")


End Sub
Private Sub Class_Terminate()


showCarAttributes


End Sub

        

Next.... Create a new module using the same steps and paste the following

Sub main()
'First car
'Declaring a new instance of class car
Dim MazdaRX7 As car_object
Dim MazdaRX7_TripOdometerReading As Double
'Setting MyCar to a new instance of car.
Set MazdaRX7 = New car_object


'Setting all the properties of the first car
MazdaRX7.EngineOperationState (True)
MazdaRX7.car_make = "Mazda"
MazdaRX7.car_model = "RX7"
MazdaRX7.car_year = 1992
MazdaRX7.car_owners = 3
MazdaRX7.OdometerReading = 10000
MazdaRX7.has_active_registration = True


'Getting the current Odometer reading at the start of the trip and assinging it to a value
MazdaRX7_TripOdometerReading = MazdaRX7.OdometerReading
MsgBox ("Odometer at start: " & MazdaRX7_TripOdometerReading & "km")


'log a new distance travelled of 100.45km
MazdaRX7.new_odometer_reading (100.45)


'Getting an updated value for the odometer reading
MazdaRX7_TripOdometerReading = MazdaRX7.OdometerReading
MsgBox ("Odometer at end: " & MazdaRX7_TripOdometerReading & "km")


'second car
'Declaring a new instance of class car
Dim HondaS2000 As car_object
Dim HondaS2000_TripOdometerReading As Double


'Setting MyCar to a new instance of car.
Set HondaS2000 = New car_object


'Setting all the properties of the first car
HondaS2000.EngineOperationState (True)
HondaS2000.car_make = "Honda"
HondaS2000.car_model = "S2000"
HondaS2000.car_year = 1999
HondaS2000.car_owners = 2
HondaS2000.OdometerReading = 79456
HondaS2000.has_active_registration = False


'Getting the current Odometer reading at the start of the trip and assinging it to a value
HondaS2000_TripOdometerReading = HondaS2000.OdometerReading
MsgBox ("Odometer at start: " & HondaS2000_TripOdometerReading & "km")


'log a new distance travelled of 100.45km
HondaS2000.new_odometer_reading (100.45)


'Getting an updated value for the odometer reading
HondaS2000_TripOdometerReading = HondaS2000.OdometerReading
MsgBox ("Odometer at end: " & HondaS2000_TripOdometerReading & "km")


End Sub
        

Conclusion

After playing around with the code example provided its easy to see that Class Modules are extremely versatile, and have their place in VBA projects.

Spend some time designing your project on paper before going ahead and starting to write out your code. You may find several areas where efficiency can be gained by using a class module over a regular module.

Take the time to learn and understand all the various elements of the class module, it will serve you well in the long run!