Class module and objects 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?
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.
• Class module and objects in Excel VBA
