Printing to the Immediate Pane from Code

While you're testing your code, you may want to display the results of expressions in your code as it's running. You can use Debug.Print to display the results in the Immediate pane. For example, you could add Debug.Print to the DueDate function, as shown in the following code.

Function DueDate(ByVal AnyDate As Variant) As Variant
' This function calculates and returns the date of the first day
' of the month that follows the supplied date.

    Debug.Print "Year "; Year(AnyDate); "Month "; Month(AnyDate)
    DueDate = DateSerial(Year(AnyDate), Month(AnyDate) + 1, 1)
End Function

Now whenever this function is called, it will display the year and month of the passed-in value in the Immediate pane. For example, you can call the DueDate function from the Immediate pane, passing in the current date using the Now statement. The value of the year and month, and the function's return value, will be displayed as shown in the following illustration.

Debug.Print always sends its output to the Immediate pane. You can use the function from the preceding example in any expression in Microsoft Excel (including formulas on a worksheet), and it will always display the value of the year and month in the Immediate pane.

The advantage of printing to the Immediate pane from code is that the feedback is displayed in a separate area (the Immediate pane), so it doesn't interfere with output you want users to see.

When you're sure that your code is working correctly, you can remove the Debug.Print statements. Printing to the Immediate pane slows your code slightly, so you don't want to leave Debug.Print in your code when you don't need to use it.

Finally, you can print debugging messages to the status bar using the StatusBar property. The advantage of the status bar is that you don't need to display the Debug window to read the debugging messages. The text you display in the status bar remains there until you set the StatusBar property to another string, or until you set the StatusBar property to False, which returns control of the status bar to Microsoft Excel. If you don't set the StatusBar property to False, the text displayed there remains after the macro ends.