Getting Started with VBA Classes

Page Content hide

1 What can you learn here?

2 How to create a VBA class

3 VBA Class Properties

4 VBA Class Instantiation

5 VBA Class methods

6 Further reading

7 Share with your network

What can you learn here?

Learn about classes
Short code snippets
Abbreviated explanation

You won’t be able to fully leverage an Object Oriented language without knowing how to define objects. A class module defines an object. A class consists of methods (procedure that do something using that class) and properties (values that are associated with particular instance of a class).

How to create a VBA class

A class is created just like a module. By default it will create a module called ‘Class1’. First job is to go to the properties tab and rename it to something sensible. The ‘standard’ naming convention is clsYourClassName, however I personally use c prefix rather than cls. This class module is your vba class constructor.

VBA Class Properties

Properties are generally values (or values derived) from variables that are stored along with an instance of a class. Here is some code showing our class with a single property named ‘Key’, I use a naming convention pPropertyName to identify these easily.

Option Explicit
Private pKey As Long
Public Property Get Key() As Long
    Key = pKey
End Property
Public Property Let Key(p As Long)
    pKey = p
End Property

Private is like Dim in a regular module , and its position before any procedure code directs that the value pKey is accessible by every procedure within this class, but is not visible to any other procedure outside of this class module. This technique ensures that an instance of this class allocates the space to preserve a private copy of the variable pKey. To get the value of pKey in and out of the class, Public (meaning accessible to other procedures outside this class) Properties Get Key() and Let Key() have been defined. Since there is both a Get and a Let, this means this is a property that is both readable and writeable to outside procedures. In summary then, the only way to change or read the value of pKey (which is a private variable only visible inside this class) is through the public property object.Key. This concept is fundamental so if there is one thing you need to ‘get’, it’s this.

VBA Class Instantiation

To be able to use our class we need to create an instance of it in the procedure that will need to use it. There are various ways to do this, but I recommend the following. The Set new cMyClass sets aside memory for a copy of the class, and setting it to Nothing releases that memory

Option Explicit
Sub test()
    Dim mc As cMyClass
    Set mc = New cMyClass
    ' do things
    Set mc = Nothing
End Sub

Since we have already created our class of which mc is now an instance, the IDE will automatically will bring up the available public properties, just like it does with inbuilt Excel classes. In this case, only Key.

You access the properties as follows:

'this wil use property let to pass the value 100
    mc.Key = 100
    MsgBox ("Property get returns " & CStr(mc.Key))

VBA Class methods

A method is a procedure that operates on a class, sometimes returning some value. Usually when you create a class you want to populate it with some initial values, and perhaps set up some kind of other structures. I generally create a method called “Init”to do that. Other languages have special methods called constructors that are called on the instantiation of a class. I kind of like the idea of initializing it when I want to. We are now going to extend our class to contain a couple of new properties and also a collection. The concept here is that this class will have ‘children’ of the same class. A very common technique for setting up tree structures. This will all be done in the .Init method. Note also that I return the object “Me” from the .Init method. This is because it can be useful if the .Init is used as a call to some other procedure. In a class “Me” always refers to the instance of the class that is currently being referenced. Here is our extended class

Option Explicit
Private pKey As Long
Private pName As String
Private pChildren As Collection
Public Property Get Key() As Long
    Key = pKey
End Property
Public Property Get Name() As String
    Name = pName
End Property
Public Property Get Children() As Collection
    Set Children = pChildren
End Property
Public Property Let Key(p As Long)
    pKey = p
End Property
Public Function Init(k As Long, sName As String) As cMyClass
    pKey = k
    pName = sName
    Set pChildren = New Collection
    Set Init = Me
End Function

An an example using it:

Firstly we can create the ‘root’, and initialize it – nothing new here

set mRoot = new cMyClass
With mRoot
    .init 100, "John"
end with

Now lets add a couple of children

set mRoot = new cMyClass
With mRoot
    .init 100, "John"
    set mc = new cMyClass
    mc.init 200, "janet"
    .Children.add mc
    set mc = new cMyClass
    mc.init 201, "john"
    .Children.add mc
end with

Since the .init() method actually returns ‘Me’ – the instance of the class, we can short circuit all that by calling init and passing the returned ‘Me’, to the .add() method of the collection. Below is the whole thing.

Sub testChildren()
    Dim mRoot As cMyClass, mc As cMyClass
    Set mRoot = New cMyClass
    With mRoot
        .Init 100, "john"
        Set mc = New cMyClass
        .Children.add mc.Init(200, "janet")
        Set mc = New cMyClass
        .Children.add mc.Init(201, "john")
        MsgBox (.Name & " has " & CStr(.Children.Count) & " children named " & _
            .Children(1).Name & " and " & .Children(2).Name)
    End With
End Sub

Of course, the children could have children and so on. This is where classes become so important if you are considering using recursion. See Getting started with recursion for this example taken a little further.