BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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 (this blog)
- 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
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.
Creating and Coding a Class: a Simple Worked Example
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.
Creating the Class Module
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!
Creating a Simple Property
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.
Creating a Method
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.
Consuming a Class
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!
Debugging a Class
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.
Conclusion: Was it Worth It?
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.
- Classes and Class Modules
- Three Class Examples: from Excel, Life and Me!
- Creating and Coding a Class: a Simple Worked Example (this blog)
- Running Code at Instantiation and Termination
- Coding Properties for Classes
- A Worked Example: Playing Hangman in Excel