ThreeWave Introduction To Classes

This page is an introduction to Classes.


Classes are a very powerful tool in intermediate to advanced level VBA programming.  This page is an introduction to what a class and an object are and will hopefully get you started working with classes. This is by no means a comprehensive guide. Entire books have been written about Object Oriented Programming, of which classes are an essential component.

In VBA, a class is defined in class module and serves as a template for an object. The term object is deliberately vague. An object can be defined to represent whatever you want. Anything that you can describe conceptually can be represented by a class. The difference between a class and an object is that a class does nothing and consumes no memory. It is like a blueprint. When you have a variable of that class type and create instance of that class with the New keyword, a process called instantiating, it becomes an object and consumes memory and can carry out actions. A class is defined by its properties, which describe attributes of the class, and its methods (sub and function procedures), which carry out actions in the object. If a class is analogous to a noun, a property is like an adjective -- it describes the object. A method is like a verb -- it carries out an action.

You must instantiate a class into an object in order to do anything with it. There is nothing you can do with a class module beyond creating an object from it. An example of instantiation is shown below:

Dim C As Class1
Set C = New Class1

where Class1 is the name of the class module. Unlike other languages, VB/VBA allows for only one class in a class module, and the name of the class is the name of the module.
You can now work with the properties and methods defined in Class1 in the C object variable.

NOTE: It is also possible to combine the two statements above into a single statement:
Dim C As New Class1
This is called an auto-instancing variable. When the variable C is first encountered in code, a new instance is created. In general, you should avoid auto-instancing variables for two reasons:
  • First, it adds overhead to the code because the variable must be tested for Nothing every time it is encountered in code.
  • Second, you have no way to test whether a auto-instancing variable is Nothing because the very act of using the variable name in an If Obj Is Nothing Then statement will automatically create an instance of the variable.


Before getting in to classes and objects, it will prove useful to examine briefly a class's logical ancestor, the Type declaration. A Type is made up of other basic variable types. You may be familiar with Types from other programming languages, in which they are called a struct, structure, or record. For example, we could declare a Type that describes an employee:

Type Employee
    Name As String
    Address As String
    Salary As Double
End Type

This defines a single type named Employee which has three elements: Name, Address, and Salary. You can then create variables of the Employee type and give values to the elements. For example,

Dim Manager As Employee
Manager.Name = "Joe Smith"
Manager.Address = "123 Main Street"
Manager.Salary = 40000

Types are quite useful, but have three shortcomings. First, you can't declare new instances of a Type. You must declare all the variables you'll need at design time or you need a dynamic array that is resized with Redim Preserve, an awkward and expensive operation. The second shortcoming of a Type is that you have no control over what values are assigned to the elements of a Type. For example, there is nothing to prevent the assignment of a negative value to the Salary element. Finally, a Type can't do anything. It cannot carry out actions; it is simply a static data structure.

While Types have their place (they are used extensively in Windows API functions), a class module is often a better solution. New instances of a class may be created with the New keyword and stored in a Collection or Dictionary object. Next, the properties of a class can be set or retrieved with Property Let and Property Get procedures, which can contain executable code. Thus, code could be written to raise an error or take other appropriate action if an invalid value is used to set a property value, such as a negative value for a Salary. Finally, classes have methods (sub and function procedures) which can carry out actions. In the example of an employee, there might be a method to print a paycheck for the employee.


Class Basics

For illustration, let's adapt the Employee Type described above into a class. First, insert a class module into your VBProject (from the Insert menu in the VBA editor). Name the class CEmployee (it is common practice to use a 'C' as the first letter of a class). There are three properties to create: Name, Address, and Salary. These values will be stored in private variables within the class. Since they are declared Private, they cannot be accessed outside the class module.

Private pName As String
Private pAddress As String
Private pSalary As Double

Next, we need to declare Property procedures to allow these variables to be read from and written to. This is done with Property Get and Property Let functions (or Property Set for object type variables).

' Name property

Public Property Get Name() As String
    Name = pName
End Property
Public Property Let Name(Value As String)
    pName = Value
End Property

' Address property

Public Property Get Address() As String
    Address = pAddress
End Property
Public Property Let Address(Value As String)
    pAddress = Value
End Property

' Salary property

Public Property Get Salary() As Double
    Salary = pSalary
End Property
Public Property Let Salary(Value As Double)
    pSalary = Value
End Property

The Get procedure is used to return a value out of the class, and the Let procedure is to put a value into the class. Note that the return data type of the Get property procedure must be the same data type as the (last) parameter to the Let property procedure. Otherwise, you'll get a compiler error. 

Because Property procedures can contain any code you like, the Let Salary procedure can be written to exclude non-positive values.

Public Property Let Salary(Value As Double)
    If Value > 0 Then
          pSalary = Value
        ' appropriate error code here
    End If
End Property


A property can be made read-only simply by omitting the Let procedure. For example, a read-only property might be withholding tax, which is calculated when it is called. E.g.,

Property Get WithholdingTax() As Double
    WithholdingTax = calculated value
End Property

Finally, the class can contain methods, such as a PrintPaycheck procedure.

Public Sub PrintPaycheck()
    ' actual code to print check
End Sub

Now that we have defined the class, we can create objects based on the class. In a standard code module, declare a variable of type CEmployee.

Dim Emp As CEmployee

Then, Set that variable to a new instance of the class and assign some property values.

Set Emp = New CEmployee
Emp.Name = "Joe Smith"
Emp.Address = "123 Main Street"
Emp.Salary = 40000


Storing Multiple Objects In A Collection

If you need to store multiple instances of a class, such as for a group of employees, you can create mutliple objects from the class and store them in a Collection or Dictionary object, as shown below.

Dim Employees As Collection
Dim Emp As CEmployee

Set Employees = New Collection

For Each Item In SomeList
    Set Emp = New CEmployee
    ' set properties for Emp
    Employees.Add Emp
Next Item

Now, you can use a simple For Each loop to loop through the collection and iterate through the collection and access each instance of CEmployee sequentailly:

Dim Emp As CEmployee
For Each Emp In Employees
    Debug.Print Emp.Name
Next Emp


The Instancing Property Of A Class

The Instancing property of a class controls where that class may be used. The default value is Private, which means that the class can be used only in the project in which the class is defined. You can set the instancing property to PublicNotCreatable, which allows a variable to be declared as that class type in projects that have a reference to the project containing the class. The second class may declare a variable of the class type, but cannot create an instance of the class with the New keyword. See the next section for more details.

Using Classes In Multiple Projects

If the Instancing property of the class is PublicNotCreatable a variable of that class type may be declared in other projects, but cannot be created in that project. You can use a function in the project containing the class to return a new instance to the caller. First, change the name of the project containing the class from the default value of VBProject to something meaningful like projSourceProject. Then, in the class that will use the class, set a reference to projSourceProject. Back in the project containing the class, create a procedure that will create and return a new instance of the class:

Public Function GetClass() As CEmployee
   Set GetClass = New CEmployee
End Function

Then call this function in the project that will use the class:

Dim NewEmp As projSourceProject.CEmployee
Set NewEmp = projSourceProject.GetClass()


Self-Referencing An Instance Of A Class

Code within a Property or method of a class can refer to its own instance by using the Me reference. For example,

    Private pName As String
    Property Let Name(S As String)
        pName = S
    End Property
    Public Sub SomeMethod()
        ' some code
        Me.Name = "ABCD"  
    End Sub

This refers to the Name property of the instance of the class from which it is executed. Other programming languages, such as C++, C# and Java, use the keyword this to refer to the instance of a class.



Setting The Default Value Of A Class

You can specify a property to be the default property of a class. When you do this, you can omit that property name and the compiler will use the default property. For example if you made Name the default property, the following lines of code are functionally equivalent:

Emp.Name = "Joe Smith"

Emp = "Joe Smith"

See Default Property Of A Class for information and examples of creating a default property.

This page just scratches the surface of what you can do with classes. Consult a good book on VBA or VB6 programming for a more in depth treatment of classes.

This page last updated: 26-Jan-2012