Erlandsen Data Consulting

Class modules

1998-07-07 VBA programming 0 219

A class is the formal definition of an object. The class is a template for the creation of the object during programming, and defines the properties and methods that decides how the object behaves. Class modules is new in Office97. With class modules it's possible to create your own objects. These objects can have properties and methods like the built-in objects, and you can create several copies of an object if you need to.
Programmers have managed without class modules earlier, and the reason for using class modules may not become obvious before you have used class modules for a while. Here are some of the advantages when using class modules:

Class modules makes it possible to separate complicated source code for advanced processes. This makes it easier for other to use the source code without understanding how the process is performed.
Class modules makes the development of complicated tasks easier by breaking up the code in smaller and easier manageable parts. This has been possible earlier, but a class module forces you to separate the code from the ordinary procedures, resulting in a more obvious function separation.
Class modules let you create reusable components. Because of the obvious separation between classes and the procedures using them, the classes contains independent code components that can easily be shared between different projects.
Class modules is the foundation of other component technologies, Visual Basic can be used to create X Automation servers and ActiveX controls.

When to use class modules

Here are some examples on when you would want to use class modules:

Database management
Class modules makes it easier to create objects that can manage a database with VBA. You can create a class that contains code for reading or writing to a database table. This class can be used in your macro without the user knowing how and where the data comes from.
Wrapping in API procedures
Class modules makes it easier to use Windows API-functions in your macros. Wrapping the API-functions in a class module makes the development easier for people that are not that familiar with the more complicated function in Windows.
Managing reading and writing to text files
This is really not that complicated, but who goes around and remember how this is done in VBA? By creating a class that manage low level reading to and writing from a text file, the properties and methods in the class can easily be used in your macros.

Class modules can also be used to simplify Registry management, to create new properties and methods to objects in the application or as an alternative to user defined data types

A class module object has more possibilities than a Type:

New instances of a class module object can be created with the New keyword and stored in a Collection or Dictionary object.
Properties of a class module object can be set or retrieved using Property Let / Property Set and Property Get procedures that can contain code for validating input or performing related tasks.
A class module object can have Methods (procedures and functions) that can perform tasks, e.g. update a record in a database.
A class module object can have Event procedures that can perform tasks when the event is triggered (like the Workbook_Open event).

A class module can be as simple as this:

Right-click in the workbook VBProject and choose Insert, Class Module
Give the new class module a "descriptive" name instead of the default Class1 etc. E.g. clsContact
Add the code below to the new class module:

Option Explicit
Public LastName As String
Public FirstName As String
Public Address As String
Public Phone As Long
Public Email As String
Public DateOfBirth As Date

You have now created a simple class module that can be used to create object variables in a normal code module like this:

Dim objContact As clsContact ' declare an object variable based on the class module
Set objContact = New clsContact ' create a new object based on the class module

objContact.LastName = "Doe"
objContact.FirsttName = "John"
objContact.Address = "Streetname 123, N-1234 Oslo"
objContact.Phone = 87654321
objContact.Email = "john.doe@acme.com"
objContact.DateOfBirth = DateSerial(1980, 7, 12) ' date in y, m, d format
Debug.Print objContact.LastName & ", " & objContact.FirstName

Or optionally like this:

With objContact
    .LastName = "Doe"
    .FirstName = "John"
    .Address = "Streetname 123, N-1234 Oslo"
    .Phone = 87654321
    .Email = "john.doe@acme.com"
    .DateOfBirth = DateSerial(1980, 7, 12) ' date in y, m, d format
    Debug.Print .LastName & ", " & .FirstName
End With

Debug.Print objContact.LastName & ", " & objContact.FirstName

Avoid declaring and instanciating the object variable in a single line like this:

Dim objContact As New clsContact ' declare and auto-instanciate an object variable based on the class module

Doing this prevents you from using something like this If objContact Is Nothing Then Exit Sub to check if the object is Nothing since it will be created when you check if it is Nothing.
In addition this can make your code slow since the application will check if the object exists every time the variable is used in the code.

Objects based on class modules can also be added to a Collection or Dictionary object:

Dim coll as Collection, objContact As clsContact
Set coll = New Collection

' populate the collection with one or more objContact objects
For i = LBound(varTwoDimArray, 1) To  UBound(varTwoDimArray, 1)
    Set objContact = New clsContact ' create a new object based on the class module

    With objContact
        .LastName = varTwoDimArray(i, 1)
        .FirstName = varTwoDimArray(i, 2)
        .Address = varTwoDimArray(i, 3)
        .Phone = varTwoDimArray(i, 4)
        .Email = varTwoDimArray(i, 5)
        .DateOfBirth = varTwoDimArray(i, 6)
    End With

    coll.Add objContact
Next i

' loop through the collection of objContact objects
For Each objContact In coll
    Debug.Print objContact.LastName & ", " & objContact.FirstName
Next objContact