560 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
Search our website
We also send out useful tips in a monthly email newsletter ...
Creating classes in VBA - class modules Part three 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 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.
|
For this page, we're going to look at a simple, cut-down example of the input box introduced in the previous part of this blog:
Our input box class will have:
In VBA terms, we say that the class (which we'll call clsLetterGuess) exposes the property and method shown above.
To create a new class, just insert a class module:
Right-click in Project Explorer and choose to insert a Class Module.
You now need to give your class a name (initially it will be called Class1) in the Properties window:
You can press F4 to bring up the Properties window, and overtype the Name property.
You might at this stage like to experience the truly memorable moment when your class first appears in Intellisense, by creating a new procedure in a separate module as follows:
Your new class appears in the list of objects available!
There are two types of property that you can create in VBA. We'll deal with the more complicated read-write properties later in this blog, but you can create a simple property just by adding a variable to the top of your class:
Option Explicit
'property to return the letter guessed by the user
Public LetterGuessed As String
It's vital that this variable is Public, because you want to be able to get at its value from outside the class:
This wouldn't happen if you made the variable Private, as its value wouldn't be exposed by the class to the outside world.
A method is just a subroutine, but again we'll make it Public (the default), as we want to expose the method from the class:
Option Explicit
'property to return letter guessed by user
Public LetterGuessed As String
'outside world doesn't want to know about letters
Private Const Letters As String = _
"abcdefghijklmnopqrstuvwxyz"
Sub StartGuess()
'this method displays an input box until
'the user guesses a letter or gives up
Dim Letter As String
'maximum number of guesses
Const MaxGuesses As Integer = 3
'number of guess
Dim GuessNumber As Integer
'initially no guesses, and no letter guessed
GuessNumber = 1
LetterGuessed = ""
Do Until Len(LetterGuessed) > 0 Or GuessNumber > MaxGuesses
'ask user to type in letter
Letter = InputBox("Think of a letter", _
"Guess", "Type letter here")
'check if letter one-character and valid
If Len(Letter) <> 1 Then
MsgBox "You must type in one (and only one) letter"
ElseIf InStr(1, Letters, LCase(Letter)) <= 0 Then
MsgBox "Not a valid letter"
Else
'if we get here, it's a valid letter
LetterGuessed = Letter
End If
'make sure we don't loop indefinitely
GuessNumber = GuessNumber + 1
Loop
End Sub
Note that we've added a constant called Letters for the class to hold the alphabet (the allowed letters). At the end of running the method StartGuess above, LetterGuessed will either hold a valid letter guessed, or an empty string.
To consume your class (ie to create an object based on it), we could create the following procedure in a separate module:
Sub RequestLetter()
'try consuming your class!
Dim Guess As New clsLetterGuess
'keep guessing until letter guessed or too many goes
Guess.StartGuess
'display what was guessed
If Len(Guess.LetterGuessed) = 0 Then
MsgBox "No letter guessed"
Else
MsgBox "You guessed " & Guess.LetterGuessed
End If
'now get rid of the object (the garbage collector will
'do this anyway, so this line is not strictly
'necessary)
Set Guess = Nothing
End Sub
Note that we can only refer to public properties and methods of the class:
You can only see properties and methods (collectively called members) which are publicly exposed.
Stop snickering at the back - yes, you really can talk about class members being publicly exposed!
When you run the code to consume a class, you can often get spurious errors:
Here our code has crashed - I wonder where?
When you click on the Debug button above, you see the offending line:
The code crashed on this line.
To find out what's really going on, you'll need to set a breakpoint within the class:
The real culprit: we're trying to store a letter in an integer variable, but you can only find this out by stepping throught the code.
An alternative approach is to change your VBA settings to allow code to break within a class module: from the VBA menu select Tools --> Options, click on the General tab and select the obvious error trapping option.
A class is like a swan: from the outside it looks serene and simple, but there's a lot of furious paddling going on underneath the water. The following code is nice and straightforward:
'try consuming your class!
Dim Guess As New clsLetterGuess
'keep guessing until letter guessed or too many goes
Guess.StartGuess
'display what was guessed
If Len(Guess.LetterGuessed) = 0 Then
MsgBox "No letter guessed"
Else
MsgBox "You guessed " & Guess.LetterGuessed
End If
The price you have to pay, however, is in writing and testing the properties and methods in the clsLetterGuess class.
Classes are ideally suited to a situation where you have a small team of expert programmers to create classes, and a larger team of less expert programmers who will want to use them over and over again.
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.