Excel VBA Debug.Print
Debug Print is one of the useful tool presented in the VBA editor to figure out how a program is working and it helps to analyze the changes in the values of variables created in the VBA program. It shows the output of the immediate window when we run the program without any bugs.
Debug.print offers the two main benefits over the use of Msgbox to display the output of the code. It eliminates the need for clicking the OK button every time and displays the log of returned output values to the immediate windows. It saves a lot of time of users. The present article explains the use of excel VBA Debug Print with many examples and explaining how to use it covering the following things.
What is VBA Debug Print?
Debug is an object in VBA and used with the two methods called Assert and Print. The print is helpful in display message and asserts in helpful in the evaluation of the conditions. In VBA, debug.print statement is used in any place of the coding program to show the values of a variable or messages in the Immediate Window. These do not need any acknowledgment or confirmation and do not show any effect on the code developed. It is safe and best to use in the code in the situation to facilitating access to many users. These are just helpful in testing or evaluation of the code to confirm that it is working properly or not. Printing the variables, strings, numbers, array, values in excel sheets, and empty and active sheets.
How to Use Excel VBA Debug Print?
VBA debug.print is the statement helpful in displaying more number of variables at a time in the immediate window. It is the best and alternative approach to show the output.
Debug.print count, sum, average, standard deviation
As shown in the example, all the variables are separated with commas. This statement is able to transfer the output to the immediate window even in the case of a window is not opened. It does not stop running the code as in Msgbox. This flexibility supports continuous monitoring of the changes in the output with respect to changes in the code.
The variables count, sum, average, and standard deviation are displayed in the same line with equal space among them. If Immediate Window is not opened, follow the following steps to see the output.
Steps to Open Immediate Window and See the Output
- Press Ctrl + G or click on the ‘View’ menu in VBA editor.
- Choose the option ‘Immediate Window’.
- Place the cursor in the Window and again run the code.
- Observe the output in the window.
Examples of Excel VBA Debug.Print
The following are the examples to demonstrate the use of debug print in excel VBA.
Example #1 – Displaying the Values of the Variables
First, go to the Developer tab, click on Macros and create a macro to write the code in the VBA and add a name to it.
After adding name click on create. This opens the VBA editor.
4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
Develop a small program as shown in the figure.
Sub Variables() Dim X As Integer Dim Y As String Dim Z As Double X = 5 Y = "John" Z = 105.632 Debug.Print X Debug.Print Y Debug.Print Z End Sub
As shown in the screenshot, three dimensions or variables are decreased as X, Y, and Z as an integer, string, and Double respectively. To print these values Debug.print is used and the output will be displayed in the immediate window. Press CTRL+G to see the output as shown in the screenshot.
Run this code using F5 key and press CTRL+G to see the output in Immediate Window.
This program can be simplified by separating the debug.print statements by a comma.
Sub Variables() Dim X As Integer Dim Y As String Dim Z As Double X = 5 Y = "John" Z = 105.632 Debug.Print X, Y, Z End Sub
This debugs statement prints the output in the same line as shown in the screenshot.
Example #2 – Debug print to File
This example illustrates the use of VBA debug print to display output to a file when the length of the text is too high.
The program to print the output on a file is developed as shown in the figure.
Sub DebugPrintToFile() Dim s As String Dim num As Integer num = FreeFile() Open "D:\Articles\Excel\test.txt" For Output As #num s = "Hello, world!" Debug.Print s ' write to the immediate window Print #num, s ' write output to file Close #num End Sub
In this program, two variables called S and Num are considered as a string and integer respectively. The open statement is used to create a text file with the name test. A string called “Hello World” is declared into the variable S.
When you run the VBA code manually or using F5 key then, the output is written into the immediate window and file at a time is shown in folder.
The output to file is shown in the below-mentioned figure.
Printing output to file is beneficial when long text is presented.
Example #3 – Displaying the Factorial of a Number in the Immediate Window
This example illustrates the use of the debug.print statement to show factorial of a number.
Public Sub Fact() Dim Count As Integer Dim number As Integer Dim Fact As Integer number = 5 Fact = 1 For Count = 1 To number Fact = Fact * Count Next Count Debug.Print Fact End Sub
To determine the factorial, three variables are considered including the count, number, and fact. For loop is taken to repeat the multiplication of fact value with count to determine factorial of the number.
Here, debug.print statement is used outside “for” loop to display the value after completion of the loop. The output is determined as.
If we use debug.print statement in inside “for” loop, the fact-value is displayed for every recurring time as shown in the figure.
Public Sub Fact() Dim Count As Integer Dim number As Integer Dim Fact As Integer number = 5 Fact = 1 For Count = 1 To number Fact = Fact * Count Debug.Print Fact Next Count End Sub
Run the code by pressing F5 key and see the output in the immediate window. In this situation, we should consider the last value as the factorial of the given number.
Example #4 – Printing the Full name of the Active Workbook
This example explains how to print the current workbook name into the immediate window
The program is developed as shown in the figure.
Sub Activework() Dim count As Long For count = 1 To Workbooks.count Debug.Print Workbooks(count).FullName Next count Debug.Print count End Sub
Here ‘count’ is the variable taken to count the number of active workbooks and to display the full name of the active workbook. The full name and number of active workbooks are displayed as shown in the figure.
The path of the workbook in the drives is accurately displayed by using the VBA debug.print statement.
Things to Remember
- The main issue with the debug .print is no text wrapping option for long strings in the immediate window
- The immediate window should be brought to top see the output in the user interface
- It is impossible to wrap the long text displayed in the Immediate Window. In this situation, the output needs to be displayed to a file stored in the drive.
This has been a guide to VBA Debug Print. Here we learn how to use the VBA Debug.print statement to analyze VBA code Output along with examples and explanations. Below are some useful excel articles related to VBA –