[VBA] Essential Debugging Skill: Outputting Values to the Immediate Window (Debug.Print)

Introduction

When creating macros in VBA, you frequently encounter situations where you want to know “how a variable’s value changes during a loop” or “if a specific part of the code is actually running.”

While you can use MsgBox to display values, it pauses the macro every time it pops up, making it extremely tedious to use inside loops.

In such cases, the Immediate Window in the VBE (VBA Editor) and the Debug.Print command are incredibly powerful tools.

In this article, I will explain how to use Debug.Print, an essential VBA debugging technique that allows you to check the movement of your program behind the scenes without interrupting the macro’s execution.

How to Use Debug.Print

Debug.Print is a simple command that writes specified values or variable contents to the Immediate Window.

1. Displaying the Immediate Window

First, you need to make sure the window is visible. Select View > Immediate Window from the VBE menu, or press the shortcut key Ctrl + G.

2. Sample Code

This macro uses a For loop to repeat a calculation 5 times. It outputs the loop counter and the calculation result to the Immediate Window during each iteration.

' Check variables inside a loop using Debug.Print
Sub CheckVariableWithDebugPrint()
    Dim total As Long
    Dim i As Long
    
    total = 100
    
    ' Loop process
    For i = 1 To 5
        ' Calculation
        total = total + (i * 10)
        
        '--- Output current variable values to Immediate Window ---
        Debug.Print "Loop " & i & " -> Total: " & total
    Next i
    
    Debug.Print "Final Total: " & total
End Sub

3. Execution and Checking Results

When you run the macro above, nothing happens on the Excel sheet itself. However, the VBE Immediate Window will display the output as shown below:

Loop 1 -> Total: 110
Loop 2 -> Total: 130
Loop 3 -> Total: 160
Loop 4 -> Total: 200
Loop 5 -> Total: 250
Final Total: 250

Code Explanation

Debug.Print "Loop " & i & " -> Total: " & total

• Debug.Print: The content written after this command is output to the Immediate Window.
• & (Ampersand): This is the string concatenation operator. It connects fixed text strings (like "Loop ") with the values of variables (i or total) to output them as a single line of text.

Unlike MsgBox, Debug.Print does not interrupt the execution of the macro. Therefore, even if you need to check values hundreds of times within a loop, you can monitor what is happening behind the scenes in real-time without obstructing the flow of the process.

Summary

In this article, I explained how to use Debug.Print and the Immediate Window, which are indispensable for VBA debugging.

• Use Ctrl + G to display the Immediate Window.
• Use Debug.Print [Variable/String] to output values without stopping the macro.
• It is one of the most basic yet powerful tools for verifying if a program is working as intended and for identifying the causes of bugs.

When creating complex macros, please make use of this technique to proceed with your debugging work efficiently.