Excel Control Structures

Module 4: Excel Control Structures

Overview

Control structures give your VBA code the ability to make decisions and repeat actions. These structures are essential for writing flexible, powerful macros that respond dynamically to data and user input.

In this module, you'll learn how to:

• Use conditional statements (If, ElseIf, Select Case)
• Create loops to repeat tasks automatically
• Exit loops when specific conditions are met

1. If...Then, If...Then...Else, ElseIf

If...Then

If score >= 50 Then
    MsgBox "You passed!"
End If
  

If...Then...Else

If score >= 50 Then
    MsgBox "You passed!"
Else
    MsgBox "You failed."
End If
  

If...Then...ElseIf

If score >= 80 Then
    MsgBox "Grade: A"
ElseIf score >= 60 Then
    MsgBox "Grade: B"
ElseIf score >= 40 Then
    MsgBox "Grade: C"
Else
    MsgBox "Grade: F"
End If
  

Tip: Always indent code inside your If blocks to improve readability.

2. Select Case

Select Case is a cleaner alternative to complex If...ElseIf structures, especially when evaluating one expression against multiple values.

Select Case day
    Case "Monday"
        MsgBox "Start of the workweek"
    Case "Friday"
        MsgBox "Weekend is near!"
    Case "Saturday", "Sunday"
        MsgBox "It's the weekend!"
    Case Else
        MsgBox "Just another day"
End Select
  

3. For...Next Loops

Dim i As Integer
For i = 1 To 5
    Cells(i, 1).Value = "Row " & i
Next i
  

Using Step

For i = 1 To 10 Step 2
    Debug.Print i  ' Prints 1, 3, 5, 7, 9
Next i
  

Reverse Looping

For i = 5 To 1 Step -1
    Debug.Print i
Next i
  

4. Do While and Do Until Loops

Do While

Dim i As Integer: i = 1
Do While i <= 5
    Cells(i, 2).Value = i * 10
    i = i + 1
Loop
  

Do Until

Dim j As Integer: j = 1
Do Until j > 5
    Cells(j, 3).Value = j * 100
    j = j + 1
Loop
  

You can also use Do...Loop While and Do...Loop Until to check the condition after the first execution.

5. Exiting Loops: Exit For, Exit Do

Exit For

For i = 1 To 10
    If Cells(i, 1).Value = "STOP" Then
        Exit For
    End If
Next i
  

Exit Do

Do While True
    If Cells(i, 1).Value = "" Then Exit Do
    i = i + 1
Loop
  

Conclusion

Control structures are at the heart of powerful Excel automation. By combining conditions with loops, you can create intelligent, adaptive macros that handle all kinds of data scenarios—from grading systems to task scheduling.

In the next module, we’ll explore Working with Ranges and Cells, which allows you to interact with the Excel grid dynamically using code.