Creating classes in VBA - class modules
Part one 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 (this blog)
  2. Three Class Examples: from Excel, Life and Me!
  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

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.

Classes and Class Modules

If you've been reading the rest of this fairly substantial on-line training blog 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:

Inserting a class module

What on earth is a class module?

 

The answer to that question is not a short one!  This blog 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 blog 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.

 

After all that preamble, then, it's time to take a deep breath and see in more detail what a class is, using 3 examples:

  • A class built into Excel (a workbook);
  • A class built into life (a baby, as it happens); and
  • A class that we'll design ourselves (an input box).

Let's begin!

 

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.