562 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 four 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.
|
Often the act of creating a new object triggers an associated set of events. For example:
We're going to turn our attention from guessing an individual hangman letter to running an entire game.
When you create a new clsHangmanGame object, here's what should happen:
When you dispose of the object, the code should close down the relevant workbook.
To do this, first create your class:
Create and rename a new class, as described earlier in this blog.
Next, choose the Class object:
Note that even though the class has a perfectly good name - clsHangmanGame - the dropdown list never shows this.
You can now choose to attach code to either the initalisation or termination event:
By default VBA will create an event-handler for the Initialize event, but you can change this here.
Here's some code to play a hangman game:
Sub PlayHangman()
'start a new game (runs INITIALIZE event)
Dim game As New clsHangmanGame
'play it (doesn't do anything yet)
game.Play
'game over (runs TERMINATE event)
Set game = Nothing
End Sub
The class could contain the following code:
Option Explicit
Private HangmanBook As Workbook
Private Sub Class_Initialize()
'on starting a game, create new workbook
Set HangmanBook = Workbooks.Add
'display welcoming message
MsgBox "Welcome to Hangman!"
End Sub
Private Sub Class_Terminate()
'on ending the game, we'll close down the workbook
HangmanBook.Close savechanges:=False
End Sub
Sub Play()
'something should happen here!
MsgBox "Should be able to play game here"
End Sub
It doesn't matter in what order the properties and methods in the class appear, nor do you have to have an Initialize or Terminate event-handler at all.
Here's what you'll see when you play this game:
![]() |
![]() |
Displayed by Initialize event | Displayed by Play method |
When you finish running the macro, your new workbook created by the Initialize event will be closed down. Thus far it's all been a bit pointless, but it will get more detailed ... !
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
From: | duggie |
When: | 29 Oct 20 at 00:34 |
Instead of using the Initialise event, ie
Option Explicit
Private HangmanBook As Workbook
Private Sub Class_Initialize()
'on starting a game, create new workbook
Set HangmanBook = Workbooks.Add
'display welcoming message
MsgBox "Welcome to Hangman!"
End Sub
Private Sub Class_Terminate()
'on ending the game, we'll close down the workbook
HangmanBook.Close savechanges:=False
End Sub
Sub Play()
'something should happen here!
MsgBox "Should be able to play game here"
End Sub
Could you have written this way?
Sub Play()
Private HangmanBook As Workbook
Set HangmanBook = Workbooks.Add
'display welcoming message
MsgBox "Welcome to Hangman!"
something should happen here!
MsgBox "Should be able to play game here"
End Sub
Ie do away with the Initialise event and add whatever was in there into the Sub instead?
If so, under what circumstances would you use the Initialise event?
From: | Andy B |
When: | 29 Oct 20 at 11:51 |
Except just an hour later, I've been making changes to our website and found an example. When I create a new instance of a website user, code attached to the instantiation event of the class authenticates the user to check they exist.
From: | Andy B |
When: | 29 Oct 20 at 11:35 |
Yes, you certainly could have written the code like this, and it probably would have been more transparent and easier to read as a result. Remember that the intention of the blog was to teach classes! It's often hard to think of good examples of classes in Excel.
To answer the more general question, suppose whenever you create a new instance of a cllas, you always have to do A, B and C. Then rather than always having to write code:
Create new instance of class
A
B
C
You can attach A, B and C to the initialise event and just write:
Create new instance of class
The hard thing is thinking of a class for which this would be useful. I do lots of programming in VB (for our internal system) and C# (for this MVC website), and rarely use the Initiialise or Terminate events, even though both systems have hundreds of classes - I'm actually struggling to think of a good example of where I've used either, which perhaps answers your question.
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.