Those who want to add spice to their VBA programming can learn how to create classes, or class modules, and become object-orientated programmers.&nbsp; It's difficult, but fun - and this multi-part blog will guide you along the way.

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.

Coding Properties for Classes

This blog has been guilty of over-simplification so far: properties aren't as
straightforward as I've been making out.

An Example - the Hangman Word to Guess

For our hangman game, you'd want to be
able to tell the game what the hidden word was going to be:

'start a new game

Dim NextGame AsNew clsGame

'tell the game what the hidden word is

NextGame.WordToGuess = "Sausages"

After playing the game, if the user loses you'd want to tell them what the
word was:

'eventually, if user loses, reveal the
word

If IfLost Then MsgBox "The word was " & NextGame.WordToGuess

On the first occasion you are writing the property (telling the class what
value it should take); on the second you are reading it (asking the class what
value it has).

Read-Only, Read-Write and Write-Only Properties

All properties that you create are either Read-Only, Read-Write
or Write-Only. Consider some properties for the baby class I
mentioned earlier:

Property

Type

Why

Name

Read-write

Parents give a baby a name (they write the value); subsequently in its life, other people
will ask it what it is called (thereby reading the value).

IfNoisy

Read-only

You can certainly inspect a baby to tell whether it's noisy or not, but by and
large there's nothing obvious you can do to change this value.

IfBaptised

Write-only

When you baptise a baby (or choose an equivalent festival from any other
religion), you change its status from outside.

Strictly speaking IfBaptised should probably be a read-write
property, as you might want to know when the child grows up whether it has been
baptised or not.

Property Syntax: Get, Let and Set

So far I've shown how you can create a read-write property using a public variable in the class. For example:

'holds the word to guess

Public WordToGuess AsString

However, when you tell the hangman game which word it should guess, you want
this to trigger a chain of events, such as creating a form for the user to fill
in in a template workbook. To do this, you have to use some or all of the
following syntax:

'a private variable to allow the class to remember
the property value

Private pWordToGuess AsString

PropertyGet WordToGuess() AsString

'return the value of the word guessed

pWordToGuess = GuessWord

'do other things triggered by reading the property

EndProperty

PropertyLet WordToGuess(ThisGuessWord As String)

'remember word being guessed

GuessWord = pWordToGuess

'do other things triggered by this change in property

EndProperty

The private variable pWordGuess lets the class remember the
value of the public property. Typically people give this private property
the same name as that for the public property, but
preceded either by an underscore _ or - as here -
by a p.

Note that you don't have to have both a Get and a Let
clause. You will have the following clauses for different types
of property:

Type of property

Clauses

Read-write

Get and Let

Read-only

Get

Write-only

Let

If a property is an object (such as a range or a workbook) you have to use
Set instead of Let. The Get
syntax is the same.

An Example - the Hangman WordGuess Property

How would all of this work in practice? Here's an example of the
WordGuess property mentioned above for our hangman game:

What this shows is that when you read the word being guessed, the
Get clause meekly just returns its value in this instance; but when you
write the word in the first place, the Let clause does a
huge amount of formatting (the full example is shown in the next part of this
blog).

Choosing Property or Method

You can always code properties like the ones above using methods instead.
For the above example, instead of:

'start a new game

Dim game AsNew clsGame

'assign a word

game.WordToGuess = "SAUSAGES"

you could instead write:

'start a new game (runs INITIALIZE event)

Dim game AsNew clsGame

'assign a word

game.AssignWord "SAUSAGES"

One of the things which makes designing and writing classes so hard is that
it is seldom obvious which approach above is the better one. Personally
I'd go for the property, since I think:

The word being guessed is a property of the game; and

It's when you tell the game which word it is using that
it
should create and format the game workbook.

However, it's often a moot point whether a property or method should be used.

Now that I've got all of the theory out of the way, let's look at a full
worked example: the hangman game in all of its glory.