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.

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!

 

This blog has 2 threads Add post
06 Jul 18 at 00:42

Could this be a solution?

 

Sub WithClassDo()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Integer
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim Counter As Integer
   
    Counter = 2
   
    Dim MyName() As Class1
    Dim MyAge() As Class1

    ReDim MyName(1 To DataArrayRows, 1 To 2) As Class1
    ReDim MyAge(1 To DataArrayRows, 1 To 2) As Class1
   
    Set MyName(Counter, 1) = New Class1
    Set MyAge(Counter, 2) = New Class1
   
    Set MyName(Counter + 1, 1) = New Class1
    Set MyAge(Counter + 1, 2) = New Class1
   
    MyName(Counter, 1).Name = DataArray(Counter, 1)
    MyAge(Counter, 2).Age = DataArray(Counter, 2)
   
    MyName(Counter + 1, 1).Name = DataArray(Counter + 1, 1)
    MyAge(Counter + 1, 2).Age = DataArray(Counter + 1, 2)
     
    Do Until MyName(Counter, 1).Name <> MyName(Counter + 1, 1).Name
   
        Counter = Counter + 1
       

'***** NEW CODE


        Set MyName(Counter + 1, 1) = New Class1
        MyName(Counter + 1, 1).Name = DataArray(Counter + 1, 1)

'*****

      
    Loop
   
End Sub

06 Jul 18 at 12:26

Rather than using arrays to hold your collection of objects, have you considered using the Collection class?  We have a blog and a pair of videos on the subject which you might find useful:

https://www.wiseowl.co.uk/blog/s239/collections.htm

https://www.wiseowl.co.uk/vba-macros/videos/vba-class-structure/custom-collections/

https://www.wiseowl.co.uk/vba-macros/videos/vba-class-structure/typed-collections/

I hope that helps!

06 Jul 18 at 13:55

I see.

Just getting a buzz out of classes and thought it would be good to incorporate it whenever I can!

 

 

Andrew G  
09 Jul 18 at 09:45

I completely understand that feeling!

06 Jul 18 at 12:41

Andrew,

The main reason I prefer arrays over collections is when returning results onto the worksheet, I can do it in a single line:

Range("A1").Resize(MyRows, MyCols).Value  = MyArray()

My understanding of collections is that I would have to loop to return values, which will be slower if there is a lot of data.

Andrew G  
06 Jul 18 at 13:31

Hi Duggie,

I see what you mean but you wouldn't be able to use that technique to write an array of objects based on a custom class into a worksheet anyway.  I think the solution in this case might be to not use class modules at all!

05 Jul 18 at 22:46

We can use class modules to bring headings into our code to make things easier to read.

Assume there are two columns of data named Name and Age.

WITHOUT using class modules, we might write this:

Sub WithoutClassForNext()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Integer
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim Counter As Integer
   
    For Counter = 2 To DataArrayRows
       
        MsgBox DataArray(Counter, 1)
       
        MsgBox DataArray(Counter, 2)
       
    Next Counter
   
End Sub

 

However, we can use class modules as follows:

 

'Class1

Option Explicit

    Private pName As String
    Private pAge As Integer

Public Property Get Name() As Variant

    Name = pName
   
End Property

Public Property Let Name(ByVal vNewValue As Variant)

    pName = vNewValue
   
End Property

Public Property Get Age() As Variant

    Age = pAge
   
End Property

Public Property Let Age(ByVal vNewValue As Variant)

    pAge = vNewValue
   
End Property

 

'Normal module:

Sub WithClassForNext()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Integer
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim Counter As Integer
   
    Dim MyName As Class1
    Dim MyAge As Class1

    Set MyName = New Class1
    Set MyAge = New Class1
   
    For Counter = 2 To DataArrayRows
   
        MyName.Name = DataArray(Counter, 1)
        MyAge.Age = DataArray(Counter, 2)
   
        MsgBox MyName.Name
        MsgBox MyAge.Age
   
    Next Counter
   
End Sub

 

Both methods work.

The problem I have lies with a Do Loop.

WITHOUT classes, it might look like:

 

Sub WithoutClassDo()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Integer
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim Counter As Integer
   
    Counter = 2
   
    Do Until DataArray(Counter, 1) <> DataArray(Counter + 1, 1)

        Counter = Counter + 1

    Loop
   
End Sub

 

But how might I do it using classes? My attempt as follows fails:

 

Sub WithClassDo()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Integer
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim Counter As Integer
   
    Counter = 2
   
    Dim MyName() As Class1
    Dim MyAge() As Class1

    ReDim MyName(1 To DataArrayRows, 1 To 2) As Class1
    ReDim MyAge(1 To DataArrayRows, 1 To 2) As Class1
   
    Set MyName(Counter, 1) = New Class1
    Set MyAge(Counter, 2) = New Class1
   
    Set MyName(Counter + 1, 1) = New Class1
    Set MyAge(Counter + 1, 2) = New Class1
   
    MyName(Counter, 1).Name = DataArray(Counter, 1)
    MyAge(Counter, 2).Age = DataArray(Counter, 2)
   
    MyName(Counter + 1, 1).Name = DataArray(Counter + 1, 1)
    MyAge(Counter + 1, 2).Age = DataArray(Counter + 1, 2)
     
    Do Until MyName(Counter, 1).Name <> MyName(Counter + 1, 1).Name
   
        Counter = Counter + 1
   
    Loop
   
End Sub

 

Any help greatly appreciated.