Creating classes in VBA - class modules
Part two 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! (this blog)
  3. Creating and Coding a Class: a Simple Worked Example
  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.

Three Class Examples: from Excel, Life and Me!

This page considers 3 examples of classes:

  • An Excel workbook (a pre-defined class in Excel);
  • A baby (a pre-defined class in life); and
  • An input box into which to type letters  (a class I've created myself)

Each one of these classes has properties and methods, and can be instantiated and terminated (that is, you can create a new object based on the class, and - ultimately - dispose of it).

An Excel Example Class: the Workbook

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:

New workbook created

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.

A Real Life Example: the Human Class

Consider the birth of a child:

A picture of a baby

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:

InputBox for letter

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.

This blog has 0 threads Add post