562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
This page considers 3 examples of classes:
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).
Consider the following lines of code:
'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
'release the object variable
Set wb = Nothing
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:
Using OOP jargon, we instantiate an object based on the Workbook class, then terminate it (or dispose of it) when we close the workbook.
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:
|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).
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|
25 Aytoun Street