Classes in VBA
Introduction - why use classes
Before we jump into the deep, what benefits will you get from using classes? Why the trouble? One of the nice features of procedural programming (not using classes) is that it is so straight-forward. To achieve task T, first do this, next that and finished. Unfortunately, in many cases the task is not so easy, and while programming more and more requirements get added. Now it is possible to continue adding pieces of code here and there, but after a while your program code is full of patches. Code created this way reflects the order in which requirements were added instead of the logical structure of the problem.
- Makes your code well-organized
- Makes code easier to read and maintain
- Better organized code is easier to test and therefore more reliable
How to decide on classes
Working with the Office objects, be it Excel, Word, Access or Outlook should give us a good basic understanding of what classes are. As an example, in Word we have Application, Documents, Header, Printer - and hundreds more. This brings us to the main characteristic of well-chosen classes: the intuitive meaning and scope of class should be undisputed. If you worked with databases, you will see a correspondence with 'data model' with tables, fields and relationships. Here we have a similar thing, called 'object model', with properties and methods.
Further reading on classes in VBA see MSDN: Custom Classes and Objects.
How to create a class with properties and methods
As an example you will see the creation of a simple sample class Person
Create a class using the Class Builder
| Alt-CCI | Menu: Code VBA » Class and collection » Class Builder |
The Class Builder is a very convenient way to create a class with properties. Once created you add the required methods.
| Tip |
|---|
|
Add Properties to a class
Properties can be added easily using the Property Builder. The Property Builder can be started in several ways:
| Alt-CCP | Menu: Code VBA » Custom class » Add Property |
| Alt-CDP | Menu: Code VBA » Declare » Property |
| Code Explorer - module node: Insert » Property |
- Start Add Property Builder
This opens the dialog asking you to give the property a name - Type the name for the property
- Optionally select the Public or Private radio buttons
- Select a type in the listbox - or type it in if it is not listed
- Press Enter to have the declaration code for the property inserted
| Notes |
|---|
| The inserted property code is both read (Get) write (Let/Set). If you don't want either, just remove it. |
| Public means a property can be used from other classes, whether they are part of the current project or external to it. Friend means a property can only be used by other classes in the current project. Private properties can only be used by procedures in that class. |
Private mstrName As String
Public Property Get Name() As String
Name = mstrName
End Property
Public Property Let Name(rData As String)
mstrName = rData
End Property
| Tip |
|---|
| An alternative to pressing the Insert is doubleclicking on the selected type. |
Add Methods
Toolbar: Procedure Builder  |
| Alt-CDS | Menu: Code VBA » Declare » Procedure Builder |
Methods are either Sub or Function and are inserted the same way as in standard modules using the Procedure Builder.
How to use a custom class in code
The code below shows sample code for working a custom class.
- Declare class variable
- Initialise object
- Use With - optional syntax for efficiently calling a set of class procedures
- Set properties and call a method
Dim prsPerson As Person
Set prsPerson = New Person
With prsPerson
.Name = "John Doe"
.Age = 37
End With
prsPerson.Celebrate
| Notes |
|---|
|