- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
CREATING CLASSES IN VBA - CLASS MODULES
Part one 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 (this article)
- Three Class Examples: from Excel, Life and Me!
- 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.
Classes and Class Modules
If you've been reading the rest of this fairly substantial on-line training blog on VBA in Excel, you'll now be something of a guru - but there may be one thing which still puzzles you. Namely the following:
What on earth is a class module?
The answer to that question is not a short one! This blog attempts to explain what classes are and why and when you might choose to use them.
VBA is an object-orientated programming (OOP) language, meaning that it works with objects (and their properties and methods). Using classes, you too can now become an object-orientated programmer!
What Classes Are
The following gives a quick summary of what classes are, but to understand them properly I'd recommend reading the rest of this blog carefully.
It takes even good programmers a fair while to understand what on earth classes are, and how you would use them (I can still remember struggling to understand what they were about). Be patient with yourself!
You've already seen many classes in Excel. For example, a worksheet is a class because:
- It has properties (such as its name);
- You can apply methods to it (such as deleting it or protecting it); and
- You can instantiate new objects based on the class (that is, you can create new worksheets which follow the rules laid down in the class).
A class (or class module - the two things are synonymous) is an object designed by yourself, with its own set of properties and methods. For example, if you work in a hamburger store you might create an Order object with the following properties and methods:
| Member | Type | Notes |
|---|---|---|
| Value | Property | The value of the order |
| Take | Method | What you do to an order at the start |
| Purchaser | Property | Who it is who is placing the order |
| WhenOrdered | Property | The date and time of the order |
| Deliver | Method | When you give the customer their order |
It's not obvious what any of these things would mean in an Excel context, however!
Reasons to Learn Classes
There is a powerful reason not to learn classes: they're not straightforward to understand and use. So why would you ever want to learn them? Well, here are some reasons:
| Reason | More explanation |
|---|---|
| Fun | Classes are fun. Programming using objects that you've created is - when you eventually get into the swing of it - both easier and more satisfying than normal programming. |
| Career path | If you enjoy programming in VBA, the chances are that you may go on to programme in other languages. There's a strong chance that these will use the concept of classes far more than VBA does: C#, Visual Basic and Java are all object-orientated programming languages. |
| Understanding downloaded macros | Sometimes when you want to solve a problem, Googling will reveal a solution which incorporates classes. If you want to understand how to tweak any such solution, you'll need to at least understand how classes work. |
| Better programming | There's just a chance that classes will make you a better VBA programmer. Being able to include classes within any solution that you create gives you more tools at your disposal as a programmer. |
However, Excel contains most of the classes that you would frequently want: ranges, worksheets and workbooks, for example. You can survive as a VBA programmer without knowing how to create classes.
Inheritance
Many OOP (Object-Orientated Programming - see above) languages include the concept of inheritance. It's worth noting here that VBA doesn't, which is one reason that many programming snobs will state - quite correctly - that VBA isn't a true object-orientated programming language.
After all that preamble, then, it's time to take a deep breath and see in more detail what a class is, using 3 examples:
- A class built into Excel (a workbook);
- A class built into life (a baby, as it happens); and
- A class that we'll design ourselves (an input box).
Let's begin!
CREATING CLASSES IN VBA - CLASS MODULES
Part one 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 (this article)
- Three Class Examples: from Excel, Life and Me!
- 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.
Comments on this blog
This blog has 4 comments:
For LetterPosition = 1 To Len(Alphabet)
SpacedAlphabet = SpacedAlphabet & Mid(Alphabet, LetterPosition, 1) & " "
Next LetterPosition
This code is from your blog, It contains a variable 'Alphabet' which I cannot identify from within the code block. Can you enlightem me?
Alphabet would be a string constant containing the letters which you could guess. Something like:
Public Const Alphabet As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
would do the trick. Seems to have escaped the code published on the blog.
I LOVE this blog - thank you for providing this valuable info. source, for having a personality, and for taking the time to (novel idea) explain things before demonstrating them!
"IfCanReadMaps=True"
Cracked me up and taught me something!
modMain----------------------------------------------------------------------------------------
Option Explicit
Dim ObjclsInputBox As clsInputBox
Sub Main()
Dim StrName As String
Dim i As Integer
Set ObjclsInputBox = New clsInputBox
' let class generate the random letter and keep it
ObjclsInputBox.GenerateRandomLetter
i = 1
' user has to guess a letter
' user has 3 chances to guess a letter
Do While (i < 4)
StrName = InputBox("Type a Letter", "Your Letter", "Type here")
' if no letter written then say
If Len(StrName) = 0 Or StrName = "" Then
MsgBox "No Letter"
Set ObjclsInputBox = Nothing
Exit Do
' otherwise process the letter
Else
ObjclsInputBox.StrLetterGuessed = StrName
ObjclsInputBox.StartGuessing
End If
i = i + 1
Loop
MsgBox "The letter you type is " & ObjclsInputBox.BolLetterGuessed
End Sub
modMain End----------------------------------------------------------------------------------
clsInputBox-------------------------------------------------------------------------------------
Option Explicit
' easiest way to define a property for a class
' we make it public, so we can set it's value from outsite the class
' :)
Public StrLetterGuessed As String
Public BolLetterGuessed As Boolean
' outside world doesn't need to know my class's private properties :)
Private StrRandomLetter As String
' there will be a randomizer picking a letter
Public Function GenerateRandomLetter()
StrRandomLetter = Chr(CInt(Rnd * 26) + 65)
End Function
Public Function StartGuessing()
If UCase(StrLetterGuessed) = StrRandomLetter Then
BolLetterGuessed = True
Else
BolLetterGuessed = False
End If
End Function
clsInputBox End--------------------------------------------------------------------------------
A Sub procedure in a class is a method, which you can call without storing the return value. I can't see any reason to make StartGuessing a function, as it doesn't return a value. Aside from that the VBA looks fine, although I'm not quite sure what you're trying to achieve with it.
hi, happy new year,
thanks for the nice example,
i have only two questions - when i download the full hangman game -
1) could you improve the remaining letters - when you guess 'a', it is still on the list of letters to be used (do you see it too? is it a feature or a bug?)
2) why when i try to resize cell a1 my excel crashes (i killed it after it stopped responding)
thanks, Kaska
Yes, you could improve the game in many ways! It's intended as an aid to understanding VBA - it's a long way off a final release ...
As to why cell Excel crashes after you expand cell A1 - 'fraid I have no idea. It sounds like something peculiar to your installation of Excel. I'd try restarting your computer!