Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
548 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
If you've been reading the rest of this fairly substantial on-line training tutorial on VBA in Excel, you'll now be something of a guru - but there may be one thing which still puzzles you. Namely the following:
What on earth is a class module?
The answer to that question is not a short one! This tutorial attempts to explain what classes are and why and when you might choose to use them.
VBA is an object-orientated programming (OOP) language, meaning that it works with objects (and their properties and methods). Using classes, you too can now become an object-orientated programmer!
The following gives a quick summary of what classes are, but to understand them properly I'd recommend reading the rest of this tutorial carefully.
It takes even good programmers a fair while to understand what on earth classes are, and how you would use them (I can still remember struggling to understand what they were about). Be patient with yourself!
You've already seen many classes in Excel. For example, a worksheet is a class because:
It has properties (such as its name);
You can apply methods to it (such as deleting it or protecting it); and
You can instantiate new objects based on the class (that is, you can create new worksheets which follow the rules laid down in the class).
A class (or class module - the two things are synonymous) is an object designed by yourself, with its own set of properties and methods. For example, if you work in a hamburger store you might create an Order object with the following properties and methods:
Member | Type | Notes |
---|---|---|
Value | Property | The value of the order |
Take | Method | What you do to an order at the start |
Purchaser | Property | Who it is who is placing the order |
WhenOrdered | Property | The date and time of the order |
Deliver | Method | When you give the customer their order |
It's not obvious what any of these things would mean in an Excel context, however!
There is a powerful reason not to learn classes: they're not straightforward to understand and use. So why would you ever want to learn them? Well, here are some reasons:
Reason | More explanation |
---|---|
Fun | Classes are fun. Programming using objects that you've created is - when you eventually get into the swing of it - both easier and more satisfying than normal programming. |
Career path | If you enjoy programming in VBA, the chances are that you may go on to programme in other languages. There's a strong chance that these will use the concept of classes far more than VBA does: C#, Visual Basic and Java are all object-orientated programming languages. |
Understanding downloaded macros | Sometimes when you want to solve a problem, Googling will reveal a solution which incorporates classes. If you want to understand how to tweak any such solution, you'll need to at least understand how classes work. |
Better programming | There's just a chance that classes will make you a better VBA programmer. Being able to include classes within any solution that you create gives you more tools at your disposal as a programmer. |
However, Excel contains most of the classes that you would frequently want: ranges, worksheets and workbooks, for example. You can survive as a VBA programmer without knowing how to create classes.
Many OOP (Object-Orientated Programming - see above) languages include the concept of inheritance. It's worth noting here that VBA doesn't, which is one reason that many programming snobs will state - quite correctly - that VBA isn't a true object-orientated programming language.
Consider the following lines of code:
Sub CreateWorkbook()
'variable to refer to a workbook
Dim wb As Workbook
'create the new workbook
Set wb = Workbooks.Add
'put something in cell A1
Range("A1").Value = "Kilroy was here"
'now close down the workbook, without saving any changes
wb.Close savechanges:=False
'release the object variable
Set wb = Nothing
End Sub
The result of running these lines? A brand new workbook:
The new workbook doesn't last for long, however.
Unfortunately, the last line of this macro then closes the workbook down. When the variable is set to Nothing, Excel will dispose of the object, or terminate it.
Workbook is a pre-defined class within Excel. When you create an object based on this class, it will automatically have all the properties and methods associated with the class. So, for example, you would be able to:
Apply the Close method to the workbook
Inspect the Name property of the workbook
Using OOP jargon, we instantiate an object based on the Workbook class, then terminate it (or dispose of it) when we close the workbook.
Consider the birth of a child:
A baby doing what babies do best: getting your attention!
Here's an English-to-VBA interpretation of the happy event for those who want to practise their social skills:
English | VBA |
---|---|
A baby has been born. | A new object based on the Human class has been instantiated. |
It's a boy! | The baby is an instance of a Man object, inheriting from the Human class (ie it has all the usual human attributes, but also man-bits, and its IfCanReadMaps property has been pre-set to True). |
It's called Bob. | The Name property of this object has been set to Bob. |
It's been baptised. | Someone has applied the Dip method to the object. |
Sad footnote: eventually, inevitably, this human will eventually die (or in VBA-speak, the object will be disposed of).
Suppose you want to play Hangman in Excel (I'm not sure if this is UK-specific; it's a game where you have to guess a word letter by letter, with only a certain number of guesses allowed before you are "hung").
At the heart of your system will be the need to ask a user to type in a letter, using an input box:
You can type in a letter and select OK.
To make it easier to write and read this code, we could create a LetterGuess object (except we'll call it clsLetterGuess, to emphasise that it's a class). Here's how it'll work:
Hangman event | How class will handle this |
---|---|
Start to ask for a letter. | Create a new instance of a clsLetterGuess class, and keep looping until a single letter is entered. |
See if it's already been been entered. | Check the letter against the ones which have already been guessed, and return True or False in the IfDuplicate property. |
See if the letter exists in the word. | If this isn't a duplicate, apply the Validate method to the letter, which will uncover any matching letters found in the word. |
That's it! | Dispose of the clsLetterGuess object; we no longer need it. |
This perfectly illustrates the problem with classes: you could create a variety of different combinations of properties and methods for the clsLetterGuess class to solve this problem, and it's not obvious which will give the best results. There is rarely an obvious optimal answer with class design.
For this page, we're going to look at a simple, cut-down example of the input box introduced in the previous part of this tutorial:
Our input box class will have a LetterGuessed property and a StartGuess method.
In VBA terms, we say that the class (which we'll call clsLetterGuess) exposes the property and method shown above.
To create a new class, just insert a class module:
Right-click in Project Explorer and choose to insert a Class Module.
You now need to give your class a name (initially it will be called Class1) in the Properties window:
You can press F4 to bring up the Properties window, and overtype the Name property.
You might at this stage like to experience the truly memorable moment when your class first appears in Intellisense, by creating a new procedure in a separate module as follows:
Your new class appears in the list of objects available!
There are two types of property that you can create in VBA. We'll deal with the more complicated read-write properties later in this tutorial, but you can create a simple property just by adding a variable to the top of your class:
Option Explicit
'property to return the letter guessed by the user
Public LetterGuessed As String
It's vital that this variable is Public, because you want to be able to get at its value from outside the class:
This wouldn't happen if you made the variable Private, as its value wouldn't be exposed by the class to the outside world.
A method is just a subroutine, but again we'll make it Public (the default), as we want to expose the method from the class:
Option Explicit
'property to return letter guessed by user
Public LetterGuessed As String
'outside world doesn't want to know about letters
Private Const Letters As String = _
"abcdefghijklmnopqrstuvwxyz"
Sub StartGuess()
'this method displays an input box until
'the user guesses a letter or gives up
Dim Letter As String
'maximum number of guesses
Const MaxGuesses As Integer = 3
'number of guess
Dim GuessNumber As Integer
'initially no guesses, and no letter guessed
GuessNumber = 1
LetterGuessed = ""
Do Until Len(LetterGuessed) > 0 Or GuessNumber > MaxGuesses
'ask user to type in letter
Letter = InputBox("Think of a letter", _
"Guess", "Type letter here")
'check if letter one-character and valid
If Len(Letter) <> 1 Then
MsgBox "You must type in one (and only one) letter"
ElseIf InStr(1, Letters, LCase(Letter)) <= 0 Then
MsgBox "Not a valid letter"
Else
'if we get here, it's a valid letter
LetterGuessed = Letter
End If
'make sure we don't loop indefinitely
GuessNumber = GuessNumber + 1
Loop
End Sub
Note that we've added a constant called Letters for the class to hold the alphabet (the allowed letters). At the end of running the method StartGuess above, LetterGuessed will either hold a valid letter guessed, or an empty string.
To consume your class (ie to create an object based on it), we could create the following procedure in a separate module:
Sub RequestLetter()
'try consuming your class!
Dim Guess As New clsLetterGuess
'keep guessing until letter guessed or too many goes
Guess.StartGuess
'display what was guessed
If Len(Guess.LetterGuessed) = 0 Then
MsgBox "No letter guessed"
Else
MsgBox "You guessed " & Guess.LetterGuessed
End If
'now get rid of the object (the garbage collector will
'do this anyway, so this line is not strictly
'necessary)
Set Guess = Nothing
End Sub
Note that we can only refer to public properties and methods of the class:
You can only see properties and methods (collectively called members) which are publicly exposed.
Stop snickering at the back - yes, you really can talk about class members being publicly exposed!
When you run the code to consume a class, you can often get spurious errors:
Here our code has crashed - I wonder where?
When you click on the Debug button above, you see the offending line:
The code crashed on this line.
To find out what's really going on, you'll need to set a breakpoint within the class:
The real culprit: we're trying to store a letter in an integer variable, but you can only find this out by stepping throught the code.
An alternative approach is to change your VBA settings to allow code to break within a class module: from the VBA menu select Tools --> Options, click on the General tab and select the obvious error trapping option.
A class is like a swan: from the outside it looks serene and simple, but there's a lot of furious paddling going on underneath the water. The following code is nice and straightforward:
'try consuming your class!
Dim Guess As New clsLetterGuess
'keep guessing until letter guessed or too many goes
Guess.StartGuess
'display what was guessed
If Len(Guess.LetterGuessed) = 0 Then
MsgBox "No letter guessed"
Else
MsgBox "You guessed " & Guess.LetterGuessed
End If
The price you have to pay, however, is in writing and testing the properties and methods in the clsLetterGuess class.
Classes are ideally suited to a situation where you have a small team of expert programmers to create classes, and a larger team of less expert programmers who will want to use them over and over again.
Often the act of creating a new object triggers an associated set of events. For example:
When you create a new workbook object, Excel will display a new workbook on screen and add (by default) 3 worksheets to it
When a new baby object is created, the proud parents will buy a cot for it and grandparents will come to visit.
We're now going to turn our attention from guessing an individual hangman letter to running an entire game.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.