Class Module Objects and Collections in Excel VBA

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.


Further reading:
Creating and Using Collections in VBA
Classes in VBA
Error Trapping with VBA

2 thoughts on “Class Module Objects and Collections in Excel VBA

  1. anu

    Sir, I have been watching your training videos since a month and I like them very much, thanks a lot for these.Last one is a perfect description of class module.
    I feel to prevent data duplication, its easier to use data validation option in excel in stead of using vba.

    Reply
  2. anu

    In order to prevent data from duplication—select the range(suppose a2 to a10, then go to data validation menu from the ribbon>settings> from “allow” option choose “custom”> in formula area key in the formula =countif($a$2:$a$10,a2)=1 then hit OK.
    Now if we enter any value in the array a2:a10 twice, then it will show a message to prevent the second time entry.. Thanks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *