This page has 1 thread | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. 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.

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.

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

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 ... !

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 1 thread

Add a new post

From:	duggie
When:	29 Oct 20 at 00:34

Reply to duggie

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

Reply to Andy B

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:	duggie
When:	30 Oct 20 at 10:46

Reply to duggie

Thanks for the clarification.

From:	Andy B
When:	29 Oct 20 at 11:35

Reply to Andy B

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.

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Running Code at Instantiation and Termination

The Hangman Game Class

Attaching Code to the Initialize and Terminate Events

Example of Initialisation and Termination Code

Head office

London

Manchester