Class Modules

Class modules are a special type of module that allow you to create your own customised objects.
You can define the methods and properties for them and even write event handlers.
These are similar to form modules, except they have no visible user interface.
A class module can contain declarations, properties, methods and events.
These are also referred to as Predeclared Class Modules.

Properties, Methods and Events

An object is defined entirely by its properties, methods, and events.
Properties - These are the "Adjectives" which describe the object.
Methods - These are the "Verbs" of the object, an action or state.
Events - These are how an object tells the rest of the world that something happened.
Once you have defined the Class, you never have to worry about the individual properties and methods you can just use them.

class_Employee

Lets create a simple class based on an employee to illustrate how to use classes.
First you would insert a class module called "class_Employee" with the following code.
This Employee class will define a single, generic, employee.

Private field_sName As String 
Private field_sDescription As String 
Private field_dbHourlyRate As Double 
Private field_sngHoursPerWeek As Single 

Property Get Property_Name() As String 
    Property_Name = field_sName 
End Property 
Property Let Property_Name(ByVal sName As String) 
    field_sName = sName 
End Property 

Property Get Property_Department() As String 
    Property_Department = field_sDescription 
End Property 
Property Let Property_Department(ByVal sDescription As String) 
   field_sDescription = sDescription 
End Property 

Property Get Property_HourlyRate() As Double 
   Property_HourlyRate = field_dbHourlyRate 
End Property 
Property Let Property_HourlyRate(ByVal dbHourlyRate As Double) 
   field_dbHourlyRate = dbHourlyRate 
End Property 

Property Get Property_HoursPerWeek() As Single 
   Property_HoursPerWeek = field_sngHoursPerWeek 
End Property 
Property Let Property_HoursPerWeek(ByVal sngHoursPerWeek As Single) 
   field_sngHoursPerWeek = sngHoursPerWeek 
End Property 

Public Function Method_WeeklyPay() As Double 
   Method_WeeklyPay = (field_dbHourlyRate * field_sngHoursPerWeek) 
End Function 

Insert a standard module with the following code/subroutine.
To work with one particular employee, you just call the properties and methods for that employee.

Public Sub TestingClassEmployee() 
   Dim oEmployee As class_Employee 

   Set oEmployee = New class_Employee 
   oEmployee.Property_Name = "Richard" 
   oEmployee.Property_Department = "Sales" 
   oEmployee.Property_HourlyRate = 12.75 
   oEmployee.Property_HoursPerWeek = 35 
   Call MsgBox(oEmployee.Property_Name & " earns £" & oEmployee.Method_WeeklyPay() & " a week") 

   Set oEmployee = Nothing 
End Sub 

Reasons for not using a Class Module

1) No intellisense - if passed or declared as Objects or Variants (commonly done/lazy)
2) Harder/impossible to make changes at run-time. You always have to reset the project
3) Must be slower - prove this with an example
4) Must use more resources - prove this with an example
5) The Watch window only displays 256 items in a collection. Classes are always used in conjunction with collections
6) The Watch window displays duplicates, properties and private variables - SS
7) If properties and private variables have the same name then infinite loops can occur, resulting in "Out of Stack" run-time errors.

Reasons for using a Class Module

There are a few occasions when a class module is necessary:
1) Responding to events (application or chart)
2) Creating a single event for multiple controls. Using a Controls Collection
3) Encapsulating your VBA procedures and functions (or even Windows API calls) so they are easier to use

Important

Class Modules let you create your own objects and allow you to define the corresponding properties and methods.
Remember that userforms are a type of class module although they behave differently to the ones you can create.
The class module name is the name of the class you are creating.