560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
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.
|
This blog has been guilty of over-simplification so far: properties aren't as straightforward as I've been making out.
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).
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.
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.
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).
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:
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.
Parts of this blog |
---|
Some other pages relevant to the above blogs include:
From: | duggie |
When: | 22 Jun 20 at 22:55 |
I have noticed something strange with the Hangman game. Can someone please check if they experience the same problem.
1. Open the Hangman game and enable macros.
2. Go to the VB editor, look for the class module, clsGame and put break point on this line:
StopGameStatus = UserKeptGuessingInvalidLetters
3. Press the Play button.
4. When the Debugging messagebox pops up, click No.
5. When the Guess message box pops up, click Cancel.
6. When the "You must type in one (and only one) letter" message box pops up, click OK
7. Click Cancel
8. Click OK
9. Click Cancel
10. Click OK
Now the program should hit the break point.
Look for this line (found in the class module, clsGame and Sub PlayRound):
If Not Letter.IfTooManyGoes Then
What I found was if I hovered my mouse over the word Not, then the program continues to run and the Guess message box pops up!
Can someone please explain why the program continues to run, despite I have NOT stepped onto the next line of code after the break point.
Thanks.
From: | Jenny Owl |
When: | 23 Jun 20 at 08:23 |
I've asked around, and no one has any idea I'm afraid. Any ideas, reading public?
From: | duggie |
When: | 23 Jun 20 at 13:07 |
Jenny,
Do you mean you experienced the same problem or it didn't happen for you?
From: | Jenny Owl |
When: | 24 Jun 20 at 09:43 |
I didn't try it, but it certainly worked when originally written. Breakpoints can exhibit odd behaviour for several reasons, as explained here.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.