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!
What Classes Are
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!
Reasons to Learn Classes
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.
Inheritance
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.
An Excel Example Class: the Workbook
Consider the following lines of code:
Sub CreateWorkbook()
Dim wb As Workbook
Set wb = Workbooks.Add
Range("A1").Value = "Kilroy was here"
wb.Close savechanges:=False
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:
Using OOP jargon, we instantiate an object based on the Workbook class, then terminate it (or dispose of it) when we close the workbook.
A Real Life Example: the Human Class
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).
A Custom Object: an InputBox for Single Characters
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.
Creating the Class Module
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!
Creating a Simple Property
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
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.
Creating a Method
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
Public LetterGuessed As String
Private Const Letters As String = _
"abcdefghijklmnopqrstuvwxyz"
Sub StartGuess()
Dim Letter As String
Const MaxGuesses As Integer = 3
Dim GuessNumber As Integer
GuessNumber = 1
LetterGuessed = ""
Do Until Len(LetterGuessed) > 0 Or GuessNumber > MaxGuesses
Letter = InputBox("Think of a letter", _
"Guess", "Type letter here")
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
LetterGuessed = Letter
End If
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.
Consuming a Class
To consume your class (ie to create an object based on it), we could create the following procedure in a separate module:
Sub RequestLetter()
Dim Guess As New clsLetterGuess
Guess.StartGuess
If Len(Guess.LetterGuessed) = 0 Then
MsgBox "No letter guessed"
Else
MsgBox "You guessed " & Guess.LetterGuessed
End If
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!
Debugging a Class
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.
Conclusion: Was it Worth It?
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:
Dim Guess As New clsLetterGuess
Guess.StartGuess
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.
