Chapter : - 5 Loops & Events in VBA – Repeat, Respond, Automate

VBA Loops & Events – Smart Coding Essentials 

5.1 What is a Loop?

A loop in VBA is a programming structure that repeats a block of code multiple times until a certain condition is met. Loops help automate repetitive tasks efficiently.

5.2 Types of Loops in VBA

• For...Next Loop: Repeats code a specific number of times.
• For Each...Next Loop: Loops through all items in a collection (like worksheets, cells).
• Do While...Loop: Repeats code while a condition is True.
• Do Until...Loop: Repeats code until a condition becomes True.
• While...Wend: Older loop, rarely used.

5.3 Syntax & Examples

Example 1: For...Next Loop

Sub ForLoopExample()
    Dim i As Integer
    For i = 1 To 5
        MsgBox "Number: " & i
    Next i
End Sub

Example 2: For Each...Next Loop

Sub ForEachExample()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        MsgBox "Sheet Name: " & ws.Name
    Next ws
End Sub

Example 3: Do While Loop

Sub DoWhileExample()
    Dim i As Integer
    i = 1
    Do While i <= 5
        MsgBox "Count: " & i
        i = i + 1
    Loop
End Sub

Example 4: Do Until Loop

Sub DoUntilExample()
    Dim i As Integer
    i = 1
    Do Until i > 5
        MsgBox "Number: " & i
        i = i + 1
    Loop
End Sub

5.4 Real-Life Use Cases

• Loop through all worksheets to update formatting.
• Loop through cells to calculate totals or check values.
• Automate repetitive reports or messages.

Chapter 6 – Workbook & Worksheet Events

6.1 What are Events in VBA?

An event is an action that occurs in Excel, such as opening a workbook, changing a cell, or clicking a button. VBA can respond to these events using event procedures.

6.2 Workbook Events

• Workbook_Open: Runs code when the workbook is opened.
• Workbook_BeforeClose: Runs code before the workbook closes.
• Workbook_SheetChange: Runs code whenever any sheet changes.

Example: Workbook_Open

Private Sub Workbook_Open()
    MsgBox "Welcome! Workbook is now open."
End Sub

6.3 Worksheet Events

• Worksheet_Change: Runs code when a cell value changes.
• Worksheet_SelectionChange: Runs code when the selection changes.
• Worksheet_BeforeDoubleClick: Runs code before double-clicking a cell.

Example: Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value > 100 Then
        MsgBox "Value is greater than 100!"
    End If
End Sub

Example: Worksheet_SelectionChange

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "You selected cell: " & Target.Address
End Sub

6.4 Real-Life Applications

• Auto-generate reports when workbook opens.
• Highlight or validate cells automatically when data changes.
• Display reminders, alerts, or messages on workbook/worksheet actions.

6.5 Practice Exercises

• Write a Workbook_Open event that shows your name.
• Create a Worksheet_Change event that alerts if a value exceeds 500.
• Create a Worksheet_SelectionChange event to display the selected cell address.