Custom 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

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

Tip

• 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

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

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
prsPerson.Celebrate