This page has 2 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

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

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.

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

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:

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!

Parts of this blog
Classes and Class Modules (this blog) Three Class Examples: from Excel, Life and Me! Creating and Coding a Class: a Simple Worked Example Running Code at Instantiation and Termination Coding Properties for Classes A Worked Example: Playing Hangman in Excel

Some other pages relevant to the above blogs include:

This blog has 2 threads

Add a new post

From:	duggie
When:	06 Jul 18 at 00:42

Reply to duggie

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

From:	Andrew G
When:	06 Jul 18 at 12:26

Reply to Andrew G

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!

From:	duggie
When:	06 Jul 18 at 13:55

Reply to duggie

I see.

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

From:	Andrew G
When:	09 Jul 18 at 09:45

Reply to Andrew G

I completely understand that feeling!

From:	duggie
When:	06 Jul 18 at 12:41

Reply to duggie

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.

From:	Andrew G
When:	06 Jul 18 at 13:31

Reply to Andrew G

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!

From:	duggie
When:	05 Jul 18 at 22:46

Reply to duggie

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()

    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()

Counter = Counter + 1

Loop

End Sub

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

Sub WithClassDo()

Any help greatly appreciated.

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Classes and Class Modules

What Classes Are

Reasons to Learn Classes

Inheritance

Head office

London

Manchester