[VBA] How to Use Debug.Assert: A Debugging Technique to Pause Macros When Conditions Fail

Introduction

During debugging in VBA, you may encounter situations where you want to pause the program only under specific conditions, such as “stop the macro only when the variable unexpectedly exceeds 100 inside this loop.”

If you use a standard Breakpoint (F9 key), the macro stops at that line every single time. In a loop, this means you have to press F5 repeatedly to reach the moment you are interested in, which is very tedious.

To solve this, VBA provides the Debug.Assert statement. This allows you to continue processing while a specific condition is met, and interrupt the macro only when that condition is not met.

In this article, I will explain how to use Debug.Assert to check the health of your code and start debugging the exact moment an anomaly occurs.

Sample Code Using Debug.Assert

This macro loops through cells A1 to A10 and checks if each cell value is a number (IsNumeric). If non-numeric data is found, Debug.Assert triggers, and the macro pauses at that line.

' Check data validity with Debug.Assert
Sub CheckDataWithAssert()
    Dim targetCell As Range
    
    ' Loop from cell A1 to A10
    For Each targetCell In ActiveSheet.Range("A1:A10")
        '--- Debug Checkpoint ---
        ' We expect targetCell.Value to be a number (IsNumeric=True).
        ' If it is NOT a number (IsNumeric=False), the macro pauses here.
        Debug.Assert IsNumeric(targetCell.Value)
        
        '--- Main Process ---
        ' (Doing nothing in this example)
        Debug.Print targetCell.Address & " is valid."
        
    Next targetCell
    
    MsgBox "All data is valid."
End Sub

Preparation and Execution

1. Enter numbers in cells A1 to A10 on your sheet, but enter a text string like “Error” in cell A5.
2. Open the VBE (Visual Basic Editor) and run the macro above.
3. The macro processes A1 through A4 normally.
4. When it reaches cell A5, IsNumeric("Error") returns False.
5. Since the condition is False, Debug.Assert activates. The line highlights in yellow, and the program pauses.

In this paused state, you can check variable values or use Step Into (F8) to investigate the cause of the issue.

Code Explanation

Debug.Assert IsNumeric(targetCell.Value)

This is the core of the technique.

Syntax:

Debug.Assert [Condition]

• [Condition]: Write a condition that you expect to be True (i.e., “If this is True, everything is normal”).
• Behavior:
  • If the result is True: The macro does nothing and proceeds to the next line.
  • If the result is False: VBA pauses execution at that line and enters debug mode.

In other words, Debug.Assert is a way for the programmer to say, “I assert that this condition must always be True. If it becomes False, something is wrong, so stop immediately.”

Summary

In this article, I explained an advanced debugging technique using Debug.Assert to automatically pause a macro the moment a specific condition is no longer met.

• Write it as Debug.Assert [Condition that should always be True].
• It functions as a “conditional breakpoint” that stops execution the moment the condition becomes False.

Debug.Assert remains in your file unless you delete it. While it is a helpful tool during development, you should consider removing it or using conditional compilation (like #If VBA7 Then) to exclude it from the final version of your tool.