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 | 5 comments

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 5 comments

Comment added on 01 September 2012 at 11:58 GMT

For LetterPosition = 1 To Len(Alphabet)

SpacedAlphabet = SpacedAlphabet & Mid(Alphabet, LetterPosition, 1) & " "

Next LetterPosition

 

This code is from your blog, It contains a variable 'Alphabet' which I cannot identify from within the code block. Can you enlightem me?

Reply from Andy Brown

Alphabet would be a string constant containing the letters which you could guess.  Something like:

Public Const Alphabet As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

would do the trick.  Seems to have escaped the code published on the blog.

Comment added on 11 September 2012 at 17:13 GMT

I LOVE this blog - thank you for providing this valuable info. source, for having a personality, and for taking the time to (novel idea) explain things before demonstrating them!

"IfCanReadMaps=True"

Cracked me up and taught me something!

Comment added on 26 October 2012 at 17:01 GMT
I took a different approach to design the class. I just want to know if the data-function-flow makes sense. Thank you.

modMain----------------------------------------------------------------------------------------

Option Explicit
Dim ObjclsInputBox As clsInputBox

Sub Main()
    Dim StrName As String
    Dim i As Integer
    Set ObjclsInputBox = New clsInputBox
    ' let class generate the random letter and keep it
    ObjclsInputBox.GenerateRandomLetter
    i = 1
   

    ' user has to guess a letter
    ' user has 3 chances to guess a letter
    Do While (i < 4)
        StrName = InputBox("Type a Letter", "Your Letter", "Type here")
        ' if no letter written then say
        If Len(StrName) = 0 Or StrName = "" Then
            MsgBox "No Letter"
            Set ObjclsInputBox = Nothing
            Exit Do
        ' otherwise process the letter
        Else
            ObjclsInputBox.StrLetterGuessed = StrName
            ObjclsInputBox.StartGuessing
        End If
        i = i + 1
    Loop
   
    MsgBox "The letter you type is " & ObjclsInputBox.BolLetterGuessed

End Sub

modMain End----------------------------------------------------------------------------------

clsInputBox-------------------------------------------------------------------------------------
Option Explicit
' easiest way to define a property for a class
' we make it public, so we can set it's value from outsite the class
' :)
Public StrLetterGuessed As String
Public BolLetterGuessed As Boolean

' outside world doesn't need to know my class's private properties :)
Private StrRandomLetter As String

' there will be a randomizer picking a letter
Public Function GenerateRandomLetter()
    StrRandomLetter = Chr(CInt(Rnd * 26) + 65)
End Function

Public Function StartGuessing()
    If UCase(StrLetterGuessed) = StrRandomLetter Then
        BolLetterGuessed = True
    Else
        BolLetterGuessed = False
    End If
End Function

clsInputBox End--------------------------------------------------------------------------------
Reply from Andy Brown

A Sub procedure in a class is a method, which you can call without storing the return value.  I can't see any reason to make StartGuessing a function, as it doesn't return a value.  Aside from that the VBA looks fine, although I'm not quite sure what you're trying to achieve with it.

Comment added on 07 January 2013 at 09:21 GMT

hi, happy new year,

thanks for the nice example,

i have only two questions - when i download the full hangman game -

1) could you improve the remaining letters - when you guess 'a', it is still on the list of letters to be used (do you see it too? is it a feature or a bug?)

2) why when i try to resize cell a1 my excel crashes (i killed it after it stopped responding)

thanks, Kaska

Reply from Andy Brown

Yes, you could improve the game in many ways!  It's intended as an aid to understanding VBA - it's a long way off a final release ...

As to why cell Excel crashes after you expand cell A1 - 'fraid I have no idea.  It sounds like something peculiar to your installation of Excel.  I'd try restarting your computer!

Comment added on 09 July 2013 at 08:47 GMT
The following would solve the problem raised by mrwoka in his post

Change this in the the clsGame class:

Private Sub RemoveLetter(WhichLetter As String)

    'remove this letter and the space before it
    If WhichLetter = "A" Then
        Range("GuessesLeft").Value = Replace(Range("GuessesLeft").Value, WhichLetter, "")
    Else
        Range("GuessesLeft").Value = Replace(Range("GuessesLeft").Value, " " & WhichLetter, "")
    End If
End Sub

Andy thanks a million times for this very clear explanation of classes with this game and all the pages and effort preceeding it !

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.