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 (this blog)
- Coding Properties for Classes
- 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.
Running Code at Instantiation and Termination
Often the act of creating a new object triggers an associated set of events. For example:
- When you create a new workbook object, Excel will display a new workbook on screen and add (by default) 3 worksheets to it
- When a new baby object is created, the proud parents will buy a cot for it and grandparents will come to visit.
We're going to turn our attention from guessing an individual hangman letter to running an entire game.
The Hangman Game Class
When you create a new clsHangmanGame object, here's what should happen:
- We should create a new workbook
- We should display a welcoming message
When you dispose of the object, the code should close down the relevant workbook.
Attaching Code to the Initialize and Terminate Events
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.
Example of Initialisation and Termination Code
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 ... !
- 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 (this blog)
- Coding Properties for Classes
- A Worked Example: Playing Hangman in Excel
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?
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.
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.