Debugging in VBA: Quick Fixes When Your Macro Fails

When a macro “does nothing” or throws an error, use these fast techniques to find the cause.

---

Checklist (60 seconds)

• Compiler errors? Press Debug and go to the yellow line.
• Option Explicit? Ensure variables are declared.
• Correct sheet/workbook? Qualify with ThisWorkbook / Worksheets("...").
• Valid ranges? Check UsedRange and lastRow calculations.
• Turn off filters? Hidden rows can break loops.

Debug.Print "lastRow:", Cells(Rows.Count, "A").End(xlUp).Row

---

Step-through & inspect

• F8: Step Into
• Shift+F8: Step Over
• F9: Breakpoint
• Ctrl+G: Immediate Window

Debug.Print TypeName(ActiveSheet), ActiveWorkbook.Name

---

Common quick fixes

• Replace Select/Activate with object variables.
• Guard against empty ranges:

If WorksheetFunction.CountA(ActiveSheet.Cells) = 0 Then Exit Sub

• Handle errors gracefully:

On Error GoTo CleanFail
' ...
CleanFail:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation

---

Minimal reproducible example

Comment out everything except the failing lines; re-add in small pieces until the failure reappears.