BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Classes and Class Modules
- Three Class Examples: from Excel, Life and Me!
- Creating and Coding a Class: a Simple Worked Example
- Running Code at Instantiation and Termination
- Coding Properties for Classes (this blog)
- 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.