VBA & Macros

VBA Debugging Techniques Every Access Developer Needs

The VBA debugger is one of the most powerful tools in Access — and one of the most underused. Here is how to use breakpoints, the Immediate window, and watch expressions effectively.

A
Anthony Passeri, MBA
••8 min read
VBA Debugging Techniques Every Access Developer Needs

Most Access developers debug VBA the same way: add a MsgBox to display a variable value, run the code, close the dialog, remove the MsgBox, repeat. It works, but it is slow and leaves traces in your code.

The VBA debugger — built into the Access VBA editor — is dramatically more powerful. Once you know how to use it, you will never go back to MsgBox debugging.

The VBA Editor Debugger Toolbar

Open the VBA editor (Alt+F11) and look at the toolbar. The debug-related buttons are:

  • Run (F5) — run the code or continue from a breakpoint
  • Break (Ctrl+Break) — pause running code
  • Reset (square button) — stop execution and reset
  • Step Into (F8) — execute one line, stepping into called procedures
  • Step Over (Shift+F8) — execute one line, treating called procedures as a single step
  • Step Out (Ctrl+Shift+F8) — finish the current procedure and pause at the caller

These are the core controls. The rest of debugging flows from them.

Breakpoints: Pause Execution at Any Line

A breakpoint tells VBA to pause execution at a specific line. When execution reaches that line, the editor opens, highlights the line, and waits for you to inspect the state and decide what to do next.

Set a breakpoint: click in the left margin next to any line of code. A red dot appears. Press F9 to toggle a breakpoint on the current line.

Remove a breakpoint: click the red dot again, or press F9 on the highlighted line.

Remove all breakpoints: Debug > Clear All Breakpoints (Ctrl+Shift+F9).

Breakpoints are not saved when you close the database — they are session-only. This is usually what you want.

When execution pauses at a breakpoint, you can:

  • Hover over any variable to see its current value in a tooltip
  • Use the Immediate window to inspect or change values
  • Step through code line by line
  • Continue execution (F5) or stop (Reset)

The Immediate Window: Your Interactive Console

The Immediate window (Ctrl+G in the VBA editor) is the most useful debugging tool in VBA. It lets you:

Print variable values:

' In the Immediate window, type:
? Me.CustomerID
? strFilter
? Format(Date, "YYYY-MM-DD")
? CurrentDb.Name

The ? is shorthand for Print. Press Enter and the value appears on the next line.

Execute statements:

' Change a variable value mid-execution
strFilter = "CustomerID = 42"

' Call a function
? IsValidEmail("test@example.com")

' Run a SQL statement
CurrentDb.Execute "UPDATE Orders SET Status='Closed' WHERE OrderID=99"

Test expressions before using them in code:

? DateAdd("d", 30, Date)
? Left("Hello World", 5)
? InStr("test@example.com", "@")

The Immediate window is available both during debugging (when execution is paused) and at any time in the VBA editor. Use it to test expressions before writing them into your code.

Watch Expressions: Monitor Variables Continuously

Watch expressions let you monitor the value of a variable or expression continuously as code executes — without stopping to check manually.

Add a watch: Debug > Add Watch (or right-click a variable and choose Add Watch).

Three watch types:

  • Watch Expression — displays the value in the Watch window as you step through code
  • Break When Value Is True — pauses execution when the expression becomes true (e.g., intCount > 1000)
  • Break When Value Changes — pauses execution whenever the value changes

Break watches are particularly useful for tracking down bugs where a variable is being changed unexpectedly. Set a "Break When Value Changes" watch on the variable, run the code, and execution will pause the moment something changes it.

Stepping Through Code

Once execution is paused at a breakpoint, use the step controls to move through code carefully:

F8 (Step Into) — executes the current line. If the line calls another procedure, steps into that procedure.

Shift+F8 (Step Over) — executes the current line. If the line calls another procedure, runs it completely and pauses at the next line in the current procedure. Use this when you trust the called procedure and do not want to step through it.

Ctrl+Shift+F8 (Step Out) — runs the rest of the current procedure and pauses at the line that called it. Use this when you have stepped into a procedure and want to get back to the caller quickly.

F5 (Continue) — resumes normal execution until the next breakpoint or the end of the code.

A common debugging pattern: set a breakpoint near where you think the problem is, run the code, then step through line by line watching variable values until you see something unexpected.

The Call Stack

The call stack shows the chain of procedure calls that led to the current execution point. View it with View > Call Stack (Ctrl+L) when execution is paused.

If ProcedureA calls ProcedureB which calls ProcedureC, and you are paused in ProcedureC, the call stack shows all three — with ProcedureC at the top. You can click any entry to see the code at that point in the call chain.

This is invaluable for understanding how you got to a particular line of code, especially in complex databases where procedures call other procedures several levels deep.

Debug.Print: Logging Without MsgBox

Debug.Print writes output to the Immediate window without pausing execution. It is the right tool when you want to log values as code runs without interrupting the flow.

Sub ProcessOrders()
    Dim rs As DAO.Recordset
    Dim intCount As Integer

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Orders WHERE Status='Pending'")
    Debug.Print "Processing " & rs.RecordCount & " pending orders at " & Now()

    Do While Not rs.EOF
        intCount = intCount + 1
        Debug.Print "  Processing OrderID: " & rs!OrderID & " for CustomerID: " & rs!CustomerID
        ' ... processing logic ...
        rs.MoveNext
    Loop

    Debug.Print "Completed. Processed " & intCount & " orders."
    rs.Close
End Sub

Run the procedure, then open the Immediate window to see the log. Much cleaner than MsgBox for tracing execution flow.

Remove Debug.Print statements before deploying — or use a conditional constant to disable them in production:

#Const DEBUG_MODE = True

Sub ProcessOrders()
    #If DEBUG_MODE Then
        Debug.Print "Starting ProcessOrders at " & Now()
    #End If
    ' ... rest of procedure
End Sub

Structured Error Handling for Production Code

Debugging tools help you find bugs during development. Error handling catches unexpected problems in production.

A robust error handler:

Sub ImportCustomerData(strFilePath As String)
    On Error GoTo ErrorHandler

    Dim db As DAO.Database
    Dim intImported As Integer

    Set db = CurrentDb
    ' ... import logic ...
    intImported = 42 ' placeholder

    MsgBox intImported & " customers imported successfully.", vbInformation
    Exit Sub

ErrorHandler:
    Dim strMsg As String
    strMsg = "Error in ImportCustomerData:" & vbCrLf & _
             "Error Number: " & Err.Number & vbCrLf & _
             "Description: " & Err.Description & vbCrLf & _
             "File: " & strFilePath

    ' Log to a table
    On Error Resume Next  ' don't let logging failure mask the original error
    db.Execute "INSERT INTO tblErrorLog (ErrorDate, Procedure, ErrorNum, ErrorDesc) " & _
               "VALUES (#" & Now() & "#, 'ImportCustomerData', " & _
               Err.Number & ", '" & Replace(Err.Description, "'", "''") & "')"
    On Error GoTo 0

    MsgBox strMsg, vbCritical, "Import Error"
End Sub

Key elements:

  • On Error GoTo ErrorHandler at the top of every procedure that touches data
  • Exit Sub before the error handler (so it does not run on success)
  • Log errors to a table for post-mortem analysis
  • Show a meaningful message to the user, not just the error number

A Debugging Workflow

When you encounter a bug:

  1. Reproduce it reliably. You cannot fix what you cannot reproduce. Find the exact steps that trigger the problem.

  2. Set a breakpoint near where you think the problem occurs — or at the start of the procedure if you are not sure.

  3. Step through the code watching variable values. Look for the first moment something is not what you expect.

  4. Use the Immediate window to test hypotheses. "What does this expression evaluate to? What if I change this variable?"

  5. Fix the root cause, not the symptom. If a variable has the wrong value, find where it gets set incorrectly — do not just add a check that corrects it after the fact.

  6. Test the fix by running the reproduction steps again.

The VBA debugger makes this process fast. Once you are comfortable with breakpoints, stepping, and the Immediate window, most bugs that used to take an hour to find take ten minutes.

Work With Access Evolved

If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.

Stuck debugging a complex VBA issue in your Access database? Schedule a free call and get a second set of expert eyes on the problem.

Schedule a Free Call →

Thinking about migrating?

Ready to move beyond Microsoft Access?

Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.

A

Written by

Anthony Passeri, MBA

Access Expert and Editor

Bridging the gap between Access and the Future

Explore Topics

#VBA#debugging#development#error handling#Access development

Keep reading

More on VBA & Macros