Class module and objects in Excel VBA скачать в хорошем качестве

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