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.

  1. Classes and Class Modules
  2. Three Class Examples: from Excel, Life and Me!
  3. Creating and Coding a Class: a Simple Worked Example
  4. Running Code at Instantiation and Termination (this blog)
  5. Coding Properties for Classes
  6. 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:

Our hangman class

Create and rename a new class, as described earlier in this blog.

 

Next, choose the Class object:

Select Class from the list

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:

The Initialize and Terminate events

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:

Initialize event message box Play method message box
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 ... !

 

  1. Classes and Class Modules
  2. Three Class Examples: from Excel, Life and Me!
  3. Creating and Coding a Class: a Simple Worked Example
  4. Running Code at Instantiation and Termination (this blog)
  5. Coding Properties for Classes
  6. A Worked Example: Playing Hangman in Excel
This blog has 0 threads Add post