This page has 0 threads | Add post

Blogs

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

Creating classes in VBA - class modules Part two 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! (this blog) Creating and Coding a Class: a Simple Worked Example Running Code at Instantiation and Termination 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 two 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! (this blog)
Creating and Coding a Class: a Simple Worked Example
Running Code at Instantiation and Termination
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

Three Class Examples: from Excel, Life and Me!

This page considers 3 examples of classes:

An Excel workbook (a pre-defined class in Excel);
A baby (a pre-defined class in life); and
An input box into which to type letters (a class I've created myself)

Each one of these classes has properties and methods, and can be instantiated and terminated (that is, you can create a new object based on the class, and - ultimately - dispose of it).

An Excel Example Class: the Workbook

Consider the following lines of code:

Sub CreateWorkbook()

'variable to refer to a workbook

Dim wb As Workbook

'create the new workbook

Set wb = Workbooks.Add

'put something in cell A1

Range("A1").Value = "Kilroy was here"

'now close down the workbook, without saving any changes

wb.Close savechanges:=False

'release the object variable

Set wb = Nothing

End Sub

The result of running these lines? A brand new workbook:

The new workbook doesn't last for long, however.

Unfortunately, the last line of this macro then closes the workbook down. When the variable is set to Nothing, Excel will dispose of the object, or terminate it.

Workbook is a pre-defined class within Excel. When you create an object based on this class, it will automatically have all the properties and methods associated with the class. So, for example, you would be able to:

Apply the Close method to the workbook
Inspect the Name property of the workbook

Using OOP jargon, we instantiate an object based on the Workbook class, then terminate it (or dispose of it) when we close the workbook.

A Real Life Example: the Human Class

Consider the birth of a child:

A baby doing what babies do best: getting your attention!

Here's an English-to-VBA interpretation of the happy event for those who want to practise their social skills:

English	VBA
A baby has been born.	A new object based on the Human class has been instantiated.
It's a boy!	The baby is an instance of a Man object, inheriting from the Human class (ie it has all the usual human attributes, but also man-bits, and its IfCanReadMaps property has been pre-set to True).
It's called Bob.	The Name property of this object has been set to Bob.
It's been baptised.	Someone has applied the Dip method to the object.

Sad footnote: eventually, inevitably, this human will eventually die (or in VBA-speak, the object will be disposed of).

A Custom Object: an InputBox for Single Characters

Suppose you want to play Hangman in Excel (I'm not sure if this is UK-specific; it's a game where you have to guess a word letter by letter, with only a certain number of guesses allowed before you are "hung").

At the heart of your system will be the need to ask a user to type in a letter, using an input box:

You can type in a letter and select OK.

To make it easier to write and read this code, we could create a LetterGuess object (except we'll call it clsLetterGuess, to emphasise that it's a class). Here's how it'll work:

Hangman event	How class will handle this
Start to ask for a letter.	Create a new instance of a clsLetterGuess class, and keep looping until a single letter is entered.
See if it's already been been entered.	Check the letter against the ones which have already been guessed, and return True or False in the IfDuplicate property.
See if the letter exists in the word.	If this isn't a duplicate, apply the Validate method to the letter, which will uncover any matching letters found in the word.
That's it!	Dispose of the clsLetterGuess object; we no longer need it.

This perfectly illustrates the problem with classes: you could create a variety of different combinations of properties and methods for the clsLetterGuess class to solve this problem, and it's not obvious which will give the best results. There is rarely an obvious optimal answer with class design.

Parts of this blog
Classes and Class Modules Three Class Examples: from Excel, Life and Me! (this blog) Creating and Coding a Class: a Simple Worked Example Running Code at Instantiation and Termination Coding Properties for Classes A Worked Example: Playing Hangman in Excel

Some other pages relevant to the above blogs include:

This blog has 0 threads

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