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.

  1. Classes and Class Modules (this blog)
  2. Three Class Examples: from Excel, Life and Me!
  3. Creating and Coding a Class: a Simple Worked Example
  4. Running Code at Instantiation and Termination
  5. Coding Properties for Classes
  6. 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.

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:

Inserting a class module

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.


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!


This blog has 0 threads Add post