Creating classes in VBA - class modules
Part five 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
  4. Running Code at Instantiation and Termination
  5. Coding Properties for Classes (this blog)
  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.

Coding Properties for Classes

This blog has been guilty of over-simplification so far: properties aren't as straightforward as I've been making out.

An Example - the Hangman Word to Guess 

For our hangman game, you'd want to be able to tell the game what the hidden word was going to be:

'start a new game

Dim NextGame As New clsGame

'tell the game what the hidden word is

NextGame.WordToGuess = "Sausages"

After playing the game, if the user loses you'd want to tell them what the word was:

'eventually, if user loses, reveal the word

If IfLost Then MsgBox "The word was " & NextGame.WordToGuess

On the first occasion you are writing the property (telling the class what value it should take); on the second you are reading it (asking the class what value it has).

Read-Only, Read-Write and Write-Only Properties

All properties that you create are either Read-Only, Read-Write or Write-Only.  Consider some properties for the baby class I mentioned earlier:

Property Type Why
Name Read-write Parents give a baby a name (they write the value); subsequently in its life, other people will ask it what it is called (thereby reading the value).
IfNoisy Read-only You can certainly inspect a baby to tell whether it's noisy or not, but by and large there's nothing obvious you can do to change this value.
IfBaptised Write-only When you baptise a baby (or choose an equivalent festival from any other religion), you change its status from outside.

Strictly speaking IfBaptised should probably be a read-write property, as you might want to know when the child grows up whether it has been baptised or not.

Property Syntax: Get, Let and Set

So far I've shown how you can create a read-write property using a public variable in the class.  For example:

'holds the word to guess

Public WordToGuess As String

However, when you tell the hangman game which word it should guess, you want this to trigger a chain of events, such as creating a form for the user to fill in in a template workbook.  To do this, you have to use some or all of the following syntax:

'a private variable to allow the class to remember the property value

Private pWordToGuess As String

Property Get WordToGuess() As String

'return the value of the word guessed

pWordToGuess = GuessWord

'do other things triggered by reading the property

End Property

Property Let WordToGuess(ThisGuessWord As String)

'remember word being guessed

GuessWord = pWordToGuess

'do other things triggered by this change in property

End Property

The private variable pWordGuess lets the class remember the value of the public property.  Typically people give this private property the same name as that for the public property, but preceded either by an underscore _ or - as here - by a p.

Note that you don't have to have both a Get and a Let clause.  You will have the following clauses for different types of property:

Type of property Clauses
Read-write Get and Let
Read-only Get
Write-only Let

If a property is an object (such as a range or a workbook) you have to use Set instead of Let.  The Get syntax is the same.

An Example - the Hangman WordGuess Property

How would all of this work in practice?  Here's an example of the WordGuess property mentioned above for our hangman game:

'private variable to remember word being guessed

Private GuessWord As String

Property Get WordToGuess() As String

'return the value of the word guessed

WordToGuess = GuessWord

End Property

Property Let WordToGuess(ThisGuessWord As String)

'remember word being guessed

GuessWord = ThisGuessWord

Dim WordCount As Integer

WordCount = Len(GuessWord)

'hide all other columns and colour word

Range(Cells(1, 1), Cells(1, WordCount)).Name = "Word"

Range(Cells(1, WordCount + 1), Range("A1").End(xlToRight)).EntireColumn.Hidden = True

Range(Cells(8, 1), Cells(8, 1).End(xlDown)).EntireRow.Hidden = True

With Range("Word")

'increase row height and set alignments

.EntireRow.RowHeight = 40

.VerticalAlignment = xlCenter

.HorizontalAlignment = xlCenter

'change font and back colour

.Font.Bold = True

.Interior.Color = RGB(240, 240, 240)

End With

'put borders round cells

Dim c As Range

For Each c In Range("Word").Cells

With c

.Borders(xlEdgeLeft).Weight = xlThin

.Borders(xlEdgeLeft).LineStyle = xlContinuous

.Borders(xlEdgeTop).Weight = xlThin

.Borders(xlEdgeTop).LineStyle = xlContinuous

.Borders(xlEdgeRight).Weight = xlThin

.Borders(xlEdgeRight).LineStyle = xlContinuous

.Borders(xlEdgeBottom).Weight = xlThin

.Borders(xlEdgeBottom).LineStyle = xlContinuous

.EntireColumn.ColumnWidth = 15

End With

Next c

Range("B3").Value = "Correct"

Range("B4").Value = "Wrong"

Range("B5").Value = "Left"

Range("A3").Name = "Correct"

Range("A4").Name = "Wrong"

Range("A5").Name = "Left"

Range("C3").Name = "GuessesCorrect"

Range("C4").Name = "GuessesWrong"

Range("C5").Name = "GuessesLeft"

'initially, all letters are guessable

Dim SpacedAlphabet As String

Dim LetterPosition As Integer

SpacedAlphabet = ""

For LetterPosition = 1 To Len(Alphabet)

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

Next LetterPosition

Range("GuessesLeft").Value = SpacedAlphabet

Range("Correct").Value = 0

Range("Wrong").Value = 0

Range("Left").Value = MaxGuesses

End Property

What this shows is that when you read the word being guessed, the Get clause meekly just returns its value in this instance; but when you write the word in the first place, the Let clause does a huge amount of formatting (the full example is shown in the next part of this blog).

Choosing Property or Method

You can always code properties like the ones above using methods instead.  For the above example, instead of:

'start a new game

Dim game As New clsGame

'assign a word

game.WordToGuess = "SAUSAGES"

you could instead write:

'start a new game (runs INITIALIZE event)

Dim game As New clsGame

'assign a word

game.AssignWord "SAUSAGES"

One of the things which makes designing and writing classes so hard is that it is seldom obvious which approach above is the better one.  Personally I'd go for the property, since I think:

  • The word being guessed is a property of the game; and
  • It's when you tell the game which word it is using that it should create and format the game workbook.

However, it's often a moot point whether a property or method should be used.


Now that I've got all of the theory out of the way, let's look at a full worked example: the hangman game in all of its glory.


This blog has 0 threads Add post