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