Creating classes in VBA - class modules
Part three of a six-part series of blogs

Those who want to add spice to their VBA programming can learn how to create classes, or class modules, and become object-orientated programmers.  It's difficult, but fun - and this multi-part blog will guide you along the way.

  1. Classes and Class Modules
  2. Three Class Examples: from Excel, Life and Me!
  3. Creating and Coding a Class: a Simple Worked Example (this blog)
  4. Running Code at Instantiation and Termination
  5. Coding Properties for Classes
  6. A Worked Example: Playing Hangman in Excel

This blog is part of our free Excel VBA tutorial.  Our classroom courses in VBA aren't free, alas, but they do provide a more structured way to learn to program.

Posted by Andy Brown on 05 March 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating and Coding a Class: a Simple Worked Example

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 blog:

InputBox for a letter

Our input box class will have:


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:

Inserting 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:

Renaming a class

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:

Class appearing in autocompletion

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 blog, 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:

Showing public property

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

'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 = _


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"


'if we get here, it's a valid letter

LetterGuessed = Letter

End If

'make sure we don't loop indefinitely

GuessNumber = GuessNumber + 1


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()

'try consuming your class!

Dim Guess As New clsLetterGuess

'keep guessing until letter guessed or too many goes


'display what was guessed

If Len(Guess.LetterGuessed) = 0 Then

MsgBox "No letter guessed"


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


Set Guess = Nothing

End Sub

Note that we can only refer to public properties and methods of the class:

Public members 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:

Debug message appearing

Here our code has crashed - I wonder where?


When you click on the Debug button above, you see the offending line:

Yellow crash line of code

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 crash line 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:

'try consuming your class!

Dim Guess As New clsLetterGuess

'keep guessing until letter guessed or too many goes


'display what was guessed

If Len(Guess.LetterGuessed) = 0 Then

MsgBox "No letter guessed"


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.

This blog has 0 threads Add post