VBA Programming Guide

More from this guide:

Edit CodeLoopsIf Then ElseRangesError HandlingCustom FunctionsArraysWorksheetsFile OperationsDebuggingBest Practices

Debugging VBA Code

Master debugging tools and techniques to find and fix VBA errors quickly

Quick Reference

F8: Step through code line by line

F9: Set/remove breakpoint

Ctrl+G: Open Immediate Window

Debug.Print: Output to Immediate Window

VBA Editor Debug Tools

Debug Menu Overview:

  • Compile VBAProject: Check for syntax errors before running
  • Step Into (F8): Execute one line, enter procedures
  • Step Over (Shift+F8): Execute one line, skip over procedures
  • Step Out (Ctrl+Shift+F8): Run until current procedure exits
  • Run To Cursor (Ctrl+F8): Run until reaching cursor position
  • Toggle Breakpoint (F9): Pause execution at line
  • Clear All Breakpoints (Ctrl+Shift+F9): Remove all breakpoints

Key Keyboard Shortcuts:

KeyAction
F5Run code / Continue
F8Step Into (line by line)
Shift+F8Step Over
F9Toggle Breakpoint
Ctrl+GImmediate Window
Ctrl+BreakStop running code

Using Breakpoints

Breakpoints pause code execution at specific lines, letting you inspect variables and step through code.

Setting Breakpoints:

  • Click in the left margin (gray bar) next to a line
  • Press F9 with cursor on the line
  • Red dot appears - code will pause here
  • Line highlights in brown when breakpoint is hit

Example Use Case:

Sub ProcessData()
Dim i As Integer
Dim total As Double
For i = 1 To 10
total = total + Cells(i, 1).Value ' Set breakpoint here
' When code pauses, hover over variables to see values
Next i
End Sub

Set breakpoint inside loop to inspect values each iteration

When Code Pauses:

  • Hover over variables to see current values
  • Use Immediate Window to test expressions
  • Press F8 to execute next line
  • Press F5 to continue running
  • Edit code and continue (if changes are simple)

Immediate Window (Ctrl+G)

The Immediate Window lets you test code, print values, and modify variables during debugging.

Print Values with Debug.Print:

Sub TestDebugPrint()
Dim x As Integer
x = 100
Debug.Print "Value of x: " & x
Debug.Print "Date: " & Now
Debug.Print "---" ' Separator
End Sub

Output appears in Immediate Window - perfect for logging

Test Expressions While Debugging:

When code is paused, type in Immediate Window:

? x ' Print variable value
? x * 2 ' Test calculation
? Range("A1").Value ' Check cell value
? UCase("hello") ' Test function

Modify Variables:

Change variable values while debugging:

x = 500 ' Change variable
Range("A1").Value = "Test" ' Change cell

Execute Commands:

Call MyProcedure ' Run another sub
Application.ScreenUpdating = True ' Change settings
Worksheets.Add ' Execute VBA statements

Watch Window

Monitor specific variables or expressions as code runs.

Adding Watches:

  1. View → Watch Window (or Debug → Add Watch)
  2. Enter expression to watch (variable name, calculation, etc.)
  3. Watch Window shows value as code executes
  4. Value updates automatically when changed

Watch Types:

Watch Expression:

Simply shows the value (most common)

Break When Value Is True:

Pause code when expression becomes True

x > 100

Break When Value Changes:

Pause when variable changes

Useful Watch Expressions:

i ' Loop counter
Range("A1").Value ' Cell value
Err.Number ' Current error number
UBound(myArray) ' Array size
ActiveSheet.Name ' Current sheet name

Locals Window

Automatically shows all variables in current scope - no setup required!

Accessing Locals Window:

View → Locals Window (when debugging)

What It Shows:

  • All variables in current procedure
  • Module-level variables
  • Object properties (expandable)
  • Array contents (expandable)
  • Updates automatically as you step through code

Pro Tip: Locals Window is the easiest way to see all variable values at once. No need to add watches for everything!

Common Debugging Scenarios

1. Code Runs But Wrong Results:

Add Debug.Print statements to trace logic:

If x > 100 Then
Debug.Print "Branch A: x = " & x
' Your code
Else
Debug.Print "Branch B: x = " & x
' Other code
End If

2. Loop Issues:

Set breakpoint inside loop, inspect counter:

For i = 1 To 10
Debug.Print "Iteration " & i & ": Value = " & Cells(i, 1).Value
' Set breakpoint here and check i value
Next i

3. Object Not Set Errors:

Check object before use:

Dim ws As Worksheet
Set ws = Worksheets("Data")
' Debug check
If ws Is Nothing Then
Debug.Print "ERROR: Worksheet not found!"
Else
Debug.Print "Worksheet OK: " & ws.Name
End If

4. Slow Code Performance:

Time sections of code:

Dim startTime As Double
startTime = Timer
' Your code here
Debug.Print "Elapsed: " & (Timer - startTime) & " seconds"

Stop Statement

The Stop statement pauses code execution like a breakpoint, but written in code.

Usage:

Sub TestStop()
Dim x As Integer
x = 100
If x > 50 Then
Stop ' Pause here if condition true
End If
' Continue debugging from here
End Sub

When to Use: Conditional debugging - pause only when specific conditions are met. Remove Stop statements before production!

Debug.Assert Statement

Pauses code if a condition is False - perfect for validating assumptions.

Example:

Sub ValidateData()
Dim total As Double
total = CalculateTotal()
' Assert total should be positive
Debug.Assert total > 0
' If total <= 0, code pauses here
End Sub

Common Assertions:

Debug.Assert x > 0 ' Value must be positive
Debug.Assert Not ws Is Nothing ' Object must exist
Debug.Assert i <= UBound(arr) ' Index in range
Debug.Assert Len(text) > 0 ' String not empty

Debugging Best Practices

✓
Start with Compile: Debug → Compile before running to catch syntax errors
✓
Use Option Explicit: Catch typos and undeclared variables
✓
Add Debug.Print liberally: Better than MsgBox for loops
✓
Use descriptive variable names: Easier to identify in debugger
✓
Test edge cases: Empty strings, zero values, missing data
✓
Step through first run: Walk through new code with F8 first time
✓
Remove debug code: Clean up Debug.Print and Stop before deploying
✓
Save before debugging: Code changes during debugging can cause issues

Troubleshooting Common Issues

Code won't stop at breakpoint:

  • Make sure code actually reaches that line
  • Check if breakpoint is enabled (should be red, not white)
  • Try Debug → Clear All Breakpoints and set again

Can't edit code while debugging:

  • Simple changes OK, major changes require stopping
  • Can't add/remove procedures or change declarations
  • Press Stop or Reset button to exit debug mode

Immediate Window not showing output:

  • Press Ctrl+G to open Immediate Window
  • Check if output is scrolled up
  • Verify Debug.Print statements are executing

Variables show "Out of context":

  • Variable is out of scope (different procedure)
  • Code must be paused in procedure where variable exists
  • Use module-level variables if needed across procedures

Related VBA Tutorials

Error Handling

Handle errors in production code

Edit VBA Code

Navigate the VBA Editor

VBA Loops

Debug loop iterations

Excel VBA Guide

Complete VBA programming guide

Generate Bug-Free VBA Code

AI-generated VBA code with proper error handling and debugging support

✓ No credit card required ✓ 5 free generations