Why Classes at All

You already use objects all day

Worksheet, Range, Collection - each is an object: it holds data (a Range has a .Value) and knows how to do things (.Copy). A class module lets you define your own. If your macro deals with employees, you can have an Employee object with a .Salary and an .ApplyRise method instead of five parallel arrays.

The problem, in code you have written

' Before: four arrays and an unwritten promise that they stay in step
Sub PayRise()
    Dim names() As String
    Dim emails() As String
    Dim salaries() As Double
    Dim startDates() As Date
    Dim i As Long

    For i = 1 To 100
        If salaries(i) < 60000 Then
            salaries(i) = salaries(i) * 1.03
        End If
    Next i
End Sub

Four arrays indexed by the same i, with nothing but discipline keeping them aligned. Sort one and everything is silently wrong. Add a field and you touch every procedure. Pass an employee onward and you pass four arguments in an order nobody remembers.

Inserting it properly

A class module is a different kind of module: class code pasted into a standard module does not work, and the class takes its name from the editor rather than from anything in the code. Follow these six steps exactly:

• Press Alt+F11 for the VBA editor, then Ctrl+R for the Project Explorer.
• Right-click VBAProject (YourFile.xlsm) in that tree, then Insert - Class Module. It appears under a folder called Class Modules - if yours landed under Modules, you picked the wrong entry.
• Press F4 for the Properties window and set (Name) to CEmployee. This is the only place a class is named; typing the name in the code does nothing.
• Paste part 1 into that class module.
• Then Insert - Module for an ordinary one, and paste part 2 there.
• Click inside RunEmployeeDemo and press F5. Save as .xlsm, or the project disappears on close.

Part 1 - the class module CEmployee

Option Explicit

' ---- CEmployee : goes in a CLASS module named CEmployee ----

Private mFullName As String
Private mSalary As Double

Public Property Get FullName() As String
    FullName = mFullName
End Property

Public Property Let FullName(ByVal newValue As String)
    mFullName = Trim$(newValue)
End Property

Public Property Get Salary() As Double
    Salary = mSalary
End Property

Public Property Let Salary(ByVal newValue As Double)
    If newValue < 0 Then
        Err.Raise vbObjectError + 513, "CEmployee", _
                  "A salary cannot be negative: " & newValue
    End If
    mSalary = newValue
End Property

Public Property Get IsJunior() As Boolean
    IsJunior = (mSalary < 60000)
End Property

Public Sub ApplyRise(ByVal fraction As Double)
    mSalary = mSalary * (1 + fraction)
End Sub

Part 2 - the standard module

Option Explicit

' ---- goes in a STANDARD module (Insert - Module) ----

Sub RunEmployeeDemo()
    Dim staff As Collection
    Dim emp As CEmployee
    Dim rowsData As Variant
    Dim parts As Variant
    Dim i As Long
    Dim r As Long
    Dim ws As Worksheet
    Dim raised As Long

    rowsData = Array( _
        "Anna Keller|58000", _
        "Marco Rossi|72000", _
        "Lea Studer|49500", _
        "Tom Brunner|61000", _
        "Sofia Vogt|55250")

    ' 1 - build one object per row
    Set staff = New Collection

    For i = LBound(rowsData) To UBound(rowsData)
        parts = Split(rowsData(i), "|")

        Set emp = New CEmployee          ' inside the loop, deliberately
        emp.FullName = parts(0)
        emp.Salary = Val(parts(1))

        staff.Add emp
    Next i

    ' 2 - the actual business rule, in a form you can read
    For Each emp In staff
        If emp.IsJunior Then
            emp.ApplyRise 0.03
            raised = raised + 1
        End If
    Next emp

    ' 3 - report
    Set ws = DemoSheet("StaffDemo")
    ws.Cells.Clear
    ws.Range("A1").Value = "Employee"
    ws.Range("B1").Value = "Salary"
    ws.Range("A1:B1").Font.Bold = True

    r = 2
    For Each emp In staff
        ws.Cells(r, 1).Value = emp.FullName
        ws.Cells(r, 2).Value = emp.Salary
        ws.Cells(r, 2).NumberFormat = "#,##0.00"
        r = r + 1
    Next emp

    ws.Columns("A:B").AutoFit
    ws.Activate

    MsgBox staff.Count & " employees, " & raised & " of them given a rise.", _
           vbInformation, "Done"
End Sub

Private Function DemoSheet(ByVal sheetName As String) As Worksheet
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add( _
            After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = sheetName
    End If

    Set DemoSheet = ws
End Function

Reading it line by line

• The Private m... variables are the object's memory. Outside code goes through the properties instead.
• Property Get reads, Property Let writes. emp.Salary = 58000 calls the Let; reading it calls the Get. From outside both look like a plain variable.
• Err.Raise in the Let is the payoff: a negative salary is rejected at the door, so no part of the program can hold a nonsensical employee.
• IsJunior is a Get with no Let - read-only, calculated on demand, nothing to keep in sync.
• Set emp = New CEmployee sits inside the loop. Not cosmetic: move it above the loop and all five entries point at one single object holding the last row.
• The business rule now reads like the rule itself, with no index arithmetic in sight.

The trap: a class in the wrong kind of module

The message is Compile error: User-defined type not defined, highlighting New CEmployee and saying nothing about modules. Two common causes: the class code went into a standard module, where the properties compile but no type of that name exists; or the class module is right but still named Class1. Check the Project Explorer - the class must sit under Class Modules, and its name there must match the name after New, letter for letter.

When not to bother

Classes are not free. A twenty-line formatting macro needs none, and wrapping it in one only makes it harder to read. The signal is repetition: the same group of variables written out again and again, or arrays kept in step by hand.

How it goes on

The next lessons take these pieces apart: blueprint versus instance, Get, Let and Set - including why an object-valued property needs Set - and Class_Initialize, the closest thing VBA has to a constructor, since New takes no arguments. Then collections of objects, two classes that reference each other, and laying out a project so a change has one obvious home.