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

menu vba class

The Class Builder is a very convenient way to create a class with properties. Once created you add the required methods.

class builder
Select names and types from the Excel sheet
  • In Excel you can use the Select Names button to pick up the names and types selecting two columns
  • When working in MS Access you can use Generate class from table to create a data bound class
Property Builder

Add Properties to a class

Properties can be added easily using the Property Builder. The Property Builder can be started in several ways:

  1. Start Add Property Builder
    This opens the dialog asking you to give the property a name
  2. Type the name for the property
  3. Optionally select the Public or Private radio buttons
  4. Select a type in the listbox - or type it in if it is not listed
  5. Press Enter to have the declaration code for the property inserted
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
An alternative to pressing the Insert is doubleclicking on the selected type.

Add Methods

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
  • Use the Class_Initialize event to do any work with the object at creation time
  • Class initialisation does not allow use of arguments - you will have to set properties instead