Class Module, Objects and Collections in Excel VBA
Home More Videos




We describe how to use a class module, instantiate an object and collections and use them fruitfully in a sub routine to accomplish an interesting task like avoiding duplicate entries in an Excel worksheet.
The concepts of class and objcts is not difficult to understand. Just take some time to study the concept carefully.
Here is the user's problem:
I'm entering data about employees in my company in Excel. Things like EmployeeID, FirstName, LastName, etc. I wish to be warned if I enter a duplicate ID. Also such duplicate entry should not be entered. Is it possible to do something like this in MS-Excel?
Watch the video below to learn about  the MS-Excel solution:

In VBA a class is defined in class module and serves as a template or blue-print for an object. You can create an object from a class. A class consumes no memory. A class is defined by its properties which describe the attributes of the class and its methods which carry out the actions in the object. Example of a class: clsEmployees. An employee can have attributes or properties such as First Name, Last Name, Date of birth, Date of joining, Salary, etc.
How to create an object of the class?
When you create an instance of a class with the 'New' keyword, a process called instantiating, it becomes an object. An object consumes memory and can carry out actions. An example of instantiation or object creation for our class clsEmployees is shown below:
Dim recEmployee as clsEmployee
Set recEmployee = New clsEmployee

It is possible to combine the two statements above into a single statement:

Dim recEmployee As New clsEmployee

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 multiple objects from the class and store them in a Collection or Dictionary object as shown below:

Dim colEmployees As Collection
Set colEmployees = New Collection

Again it is possible to combine the two statements above into a single statement:

Dim colEmployees As New Collection

Now let's write two sub-routines that provide a solution. The second module or macro offers more possibilities:
Sub addEmployee1()
    Dim colEmployees     As Collection
    Dim recEmployee      As clsEmployee
    Dim erow             As Long
    Dim answer           As String


    Set colEmployees = New Collection


    answer = "y"
    Do While answer = "y"
        answer = InputBox("Do you wish to enter a new record?Please enter y or n only!")
        If answer = "n" Then Exit Sub
        erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Set recEmployee = New clsEmployee
        recEmployee.ID = InputBox("Enter Employee ID")
        On Error Resume Next
        colEmployees.Add recEmployee, recEmployee.ID
        If Err.Number = 0 Then
            Cells(erow, 1) = recEmployee.ID
            recEmployee.FirstName = InputBox("Enter First Name")
            Cells(erow, 2) = recEmployee.FirstName
            recEmployee.LastName = InputBox("Enter Last Name")
            Cells(erow, 3) = recEmployee.LastName
        Else
            Err.Clear
            MsgBox "Duplicate ID. Data not entered"
        End If
    Loop
End Sub

Sub addEmployee2()
Dim colEmployees As New Collection
Dim recEmployee As New clsEmployee
Dim erow As Long
Dim answer As String
Do
  answer = InputBox("Do you wish to enter a new record?Please enter y or n only!")
  If answer = "n" Then Exit Sub
  erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  recEmployee.ID = InputBox("Enter Employee ID")
  If ActiveSheet.Range("A1").Resize(erow - 1, 1).Find(what:=recEmployee.ID, LookAt:=xlWhole) Is Nothing Then
    recEmployee.FirstName = InputBox("Enter First Name")
    recEmployee.LastName = InputBox("Enter Last Name")
    foundID = 0
    For r = 1 To erow - 1
      If Cells(r, 2) = recEmployee.FirstName And Cells(r, 3) = recEmployee.LastName Then
        foundID = Cells(r, 1)
        Exit For
      End If
    Next r
    If foundID = 0 Then
      Cells(erow, 1) = recEmployee.ID
      Cells(erow, 2) = recEmployee.FirstName
      Cells(erow, 3) = recEmployee.LastName
      colEmployees.Add recEmployee, recEmployee.ID
    Else
      MsgBox "Person " & recEmployee.FirstName & " " & recEmployee.LastName & " does already exist (with ID=" & foundID & ")"
    End If
  Else
     MsgBox "ID " & recEmployee.ID & " is already used. Please use a new ID!", vbInformation
  End If
Loop
End Sub

If you notice we are creating a database using the key 'ID'.
Using a class module (clsEmployee), an object (recEmployee) and a collection (colEmployees) we have achieved our objective of:
(1) Avoiding duplicate ID entries and
(2) Avoiding duplicate 'first and last name' entries of an employee with a different ID

We have demonstrated these concepts in Excel 2003 but they can be done in MS-Excel 2007 or Excel 2010 as well with the same ease.
If you find all this difficult and not so useful you can avoid duplicate entries using the standard countif function and data validation.

More reading:
Creating and Using Collections in VBA
Classes in VBA
Error Trapping with Visual Basic for Applications