Instructor-led training - vba training courses
Courses running during train strike
Hide

TrustPilot

starstarstarstarstar Excellent

Excel VBA Advanced Training CoursesExcel VBA Advanced Training Courses

Designed for Excel 365, 2016, 2013, 2010, 2007

From £380 List price £699

Learn Advanced Visual Basic

Free manuals

We are providing our course manuals and exercise files free of charge.

Why not share this resource with your friends and colleagues using the buttons below?

Example training manuals

Below are some extracts from our free Excel training manuals.

Excel VBA Advanced

Unit: Class Modules

What can be done with Class Modules?

Class modules allow you to create and use your own object types in your application. This implies the following;

  • You can easily write code that works with any workbooks that do not have any code.
  • Two or more procedures for the event of a command button can be consolidated in one
  • The code is easy to use by concealing logic and data.

Why use Class Modules?

Classes make your code:

  • Development simpler
  • More manageable
  • Self-documenting
  • Easier to maintain

What is a Class?

A Class is a Blueprint or template of an Object.

In Excel VBA, an Object can mean Workbooks, Worksheets, User forms and Controls etc. Normally an Object has Properties or Methods. A Property stands for Data that describes the Object, and a Method stands for an action that can be ordered to the object.

Properties and Methods of the Object depend on the kind of Object.
For Example;

Worksheet (1).Select

... selects the first worksheet in the workbook. Select is a method of the worksheet object.

How Does a Class Module Work?

A Class Module is a place where a Class is defined. The procedures in a class module are never called directly from other modules like the procedures placed in the standard modules.

In the view of a standard module, the class module doesn't exist.

The thing that exists in the view of a standard module is an instance of the object generated by the class defined by the class module. The methods and procedures of the class are defined within the class module.

Key Elements in a class module

The class module defines all the properties and methods associated with the class. In the example below the "customer" class has two properties associated properties; Name and Main Address.

These are defined by the Property Get and Property let Procedures (see below).

The Customer ID is calculated by taking the leftmost 3 characters from the customer's Name and concatenating that with the 5 leftmost characters from the main Address. This is the result of the method GetCustomerID, and is defined in a function in the class module

Property Get and Let Procedures

A property is implemented using a property let and a property get procedure. When someone sets a value for a property the property let procedure is called with the new value. When someone reads the value of a property the property get procedure is called to return the value. The value is stored as an internal private variable.

Read only properties can be created by implementing a property get procedure without a corresponding property let procedure.

Example of a Class Module

Option Explicit

Private strName As String

Private strAddress As String

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

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

Public Function GetCustomerID()
GetCustomerID = Left(strName, 3) & Left(strAddress, 5)
End Function

Public Property Get MainAddress() As String
MainAddress = strAddress
End Property

Public Property Let MainAddress(ByVal value As String)
strAddress = value
End Property

Referring to user defined Objects in Code

This simply involves creating an instance of the Class in Code and then manipulating it is the way you would any other object.

The following code would be placed in a standard module, and refers to the customer object defined previously.

Option Explicit
Dim aCustomer As Customer (1)
Sub TestCustomer()
Set aCustomer = New Customer    (2)
aCustomer.Name = "Evil Genius" (3)
aCustomer.MainAddress = "123 the Hollowed out Volcano" (4)
MsgBox "Company ID is " & vbCrLf & aCustomer.GetCustomerID() (5)
End Sub

Line 1 defines an object variable as a Customer variable, and line 2 sets it as a new customer object. Line 3 assigns a value to its name property and line 4 a value to its Main Address property.

Line 4 uses the GetCustomerID Method to generate the CustomerID value and returns it in a message box.

Using IntelliSense™

Microsoft IntelliSense is a convenient way to access descriptions of objects and methods. It speeds up software development by reducing the amount of name memorization needed and keyboard input required. Once a class is defined in a class module, Intellisense will automatically provide drop down lists showing the methods and properties of objects the names of which have been entered into the VBE.

Working with Collections

A class is a blueprint for an object, and individual objects are "instances" of a class. A collection is simply a group of individual objects with which we are going to work.

For example in the code above we have defined a class called customers, and code to generate a single instance of that class; i.e. one individual customer. In practice we will be working with more than one customer and we will wish to define them as being part of a collection object so we can process them using some of the methods and properties of the collection object.

The Collection Object

The collection object has a number of properties and methods associated with it; of which the most important are:

Method/Property

Description

Count

A method that returns the number of objects in the collection

Add

A method that adds an item to the collection

Remove

Removes an item to a collection

Items(index)

Refers to an individual item in the collection either by its index number (position in collection) or by its name

Explicit creation of a collection

We can create a collection in a class module. This simply requires us to define the collections objects and methods in the normal way

Option Explicit
Private FCustomers As New Collection

Public Function add(ByVal value As Customer)
Call FCustomers.add(value, value.Name)
End Function

Public Property Get Count() As Long
Count = FCustomers.Count
End Property

Public Property Get Items() As Collection
Set Items = FCustomers
End Property

Public Property Get Item(ByVal value As Variant) As Customer
Set Item = FCustomers(value)
End Property

Public Sub Remove(ByVal value As Variant)
Call FCustomers.Remove(value)
End Sub

The above code simply defines a collection called customers (class module name). The variable FCustomers is defined as a collection object. The various methods and properties are then defined. For example, the remove method is defined in a procedure that uses the remove method of the collection object to remove a specified item from the collection.

Referring to a collection in a standard module

Once defined, a collection can be employed in the same way as any other collection.

Dim aCustomer As Customer
Dim theCustomers As New Customers
Set aCustomer = New Customer   
aCustomer.Name = "Kur Avon"
aCustomer.MainAddress = "132 Long Lane"   
Call theCustomers.add(aCustomer)   

Set aCustomer = New Customer   
aCustomer.Name = "Fred Perry"
aCustomer.MainAddress = "133 Long Lane"   
Call theCustomers.add(aCustomer)   

Set aCustomer = New Customer   
aCustomer.Name = "Jo Bloggs"
aCustomer.MainAddress = "134 Long Lane"   
Call theCustomers.add(aCustomer)   

For Each aCustomer In theCustomers.Items   
Sheets(1).Range("A1").Select       
ActiveCell.value = aCustomer.Name
ActiveCell.Offset(0, 1).value = aCustomer.MainAddress       
ActiveCell.Offset(1, 0).Select
Next aCustomer

The above code simply defines a "customer" variable and a "customers" variable; assigns three objects to the collection and then writes the name and address to a worksheet in the current workbook, using a "FOR EACH" loop.

Using the Collections Object Directly

It is possible to create a collection using the VBA collection class directly. The code below creates a collection called employees and assigns three instances of the custom object employees to it.

Sub TestEmployeesCollection()

    Dim anEmployee As Employee
Dim i As Long   

Set anEmployee = New Employee
anEmployee.Name = "Stephen Williams"
anEmployee.Rate = 500
anEmployee.HoursPerWeek = 50
Call Employees.add(anEmployee, anEmployee.Name)

Set anEmployee = New Employee
anEmployee.Name = "Kur Avon"
anEmployee.Rate = 50
anEmployee.HoursPerWeek = 50
Call Employees.add(anEmployee, anEmployee.Name)

Set anEmployee = New Employee
anEmployee.Name = "Bill Bailey"
anEmployee.Rate = 250
anEmployee.HoursPerWeek = 50
Call Employees.add(anEmployee, anEmployee.Name)

Set anEmployee = New Employee
anEmployee.Name = "Alexander Armstrong"
anEmployee.Rate = 250
anEmployee.HoursPerWeek = 50
Call Employees.add(anEmployee, anEmployee.Name)   

For Each anEmployee In Employees

MsgBox anEmployee.Name & " Earns " & "£" & anEmployee.GetGrossWeeklyPay()

Next anEmployee   

End Sub