VBA Debug Print

What is Excel VBA Debug.Print?

Excel VBA Debug Print is a statement that outputs information to the Immediate Window during the debugging process. The Immediate Window is a built-in VBA development tool that allows developers to interact with their code and view the output of statements in real-time.

The Debug Print statement is typically used to display the values of variables, properties, or expressions during the execution of VBA code. It is a helpful tool for diagnosing and fixing errors in your code by providing insights into the values and flow of your program. Let us look at an example. In this example, we define a variable x of type Integer and assign it a value of 10. The Debug Print statement is then used to output the value of x to the Immediate Window.

The statement “Debug.Print “The value of x is: ” & x” concatenates the descriptive text “The value of x is: ” with the value of x. When the code is executed, the Immediate Window will display the output “The value of x is: 10.”

How to Use Excel VBA Debug.Print?

To use Excel VBA Debug Print, follow these steps:

Step 1: Open the Visual Basic Editor.

In Excel, press ALT + F11 to open the Visual Basic Editor (VBE).

Step 2: In the VBE, click “Insert” in the menu bar and select “Module.” It will insert a new module into the project.

Step 3: In the module, write your VBA code. You can include the Debug.Print statement at any point in your code where you want to output information to the Immediate Window.

For example, consider the following code:

Sub CalculateSum()
Dim num1 As Integer
Dim num2 As Integer
Dim sum As Integer
num1 = 10
num2 = 20
sum = num1 + num2
Debug.Print “The sum of num1 and num2 is: ” & sum
End Sub

Step 4: To run the VBA code, close the VBE and return to Excel. Press ALT + F8 to open the “Macro” dialog box. Select the macro you want to run (e.g., “CalculateSum”) and click the “Run” button.

Step 5: After running the code, open the Immediate Window by pressing CTRL + G or going to “View” -> “Immediate Window.” The output generated by the Debug.Print statement will be displayed in the Immediate Window.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Let us look at some examples of how to implement the VBA Debug.Print statement in different scenarios.

Example #1 – Displaying the Values of the Variables.

In this example, the Debug.Print statement is used to display the values of the variables “name,” “age,” and “salary” in the Immediate Window.

Step 1: In the new module, create a subroutine “DisplayVariables” and declare three variables: name (String), age (Integer), and salary (Double).

Step 2: Assign values to the variables: name = “John Doe,” age = 30, salary = 5000.50.

Step 3: The Debug.Print statement is used to display the values of the variables in the Immediate Window.

•  Debug.Print “Name: ” & name – concatenates the text “Name: ” with the value of the name variable and displays it in the Immediate Window.
• Debug.Print “Age: ” & age concatenates the text “Age: ” with the value of the age variable and displays it in the Immediate Window.
• Debug.Print “Salary: ” & salary concatenates the text “Salary: ” with the value of the salary variable and displays it in the Immediate Window.

Step 4: Save the module and run the code. When the code is executed, the following values of the variables are shown via VBA Debug Print Window or also known as the Immediate window:

Here is the complete code:

Sub DisplayVariables()
Dim name As String
Dim age As Integer
Dim salary As Double
name = “John Doe”
age = 30
salary = 5000.5
Debug.Print “Name: ” & name
Debug.Print “Age: ” & age
Debug.Print “Salary: ” & salary
End Sub

Example #2 – Debug Print to File

This example demonstrates how to redirect the output of Debug.Print to a file. The Open statement is used to open a file in write mode, and the Debug.Print statement is prefixed with the file number (#fileNum) to print the output to the file. Finally, the “Close” statement is used to close the file.

Step 1: In the new module, create a subroutine PrintToFile” and declare a variable fileNum of type Integer to hold the file number.

Step 2: Use the FreeFile function to obtain an available file number and assign it to fileNum.

Step 3: Use the Open statement to open a file in write mode and associate it with the file number fileNum.

In this example, the file path “”C:\Users\Vikram\Desktop\My name is John Smith.txt”” is used. You can modify it as per your desired location and file name.

Step 4: Use the Debug.Print statement with the file number (#fileNum) to print the desired text to the file.

The text “This line will be printed to a file.” will be written to the file associated with fileNum.

Step 5: Close the file using the Close statement to release the file resources.

Step 6: Save the VBA macro and run the code. When the code is executed, the specified text will be printed to the file at the specified location.

 Here is the complete code:

Sub PrintToFile()
Dim fileNum As Integer
fileNum = FreeFile
Open “C:\Output.txt” For Output As fileNum
Print #fileNum, “This line will be printed to a file.”
Close fileNum
End Sub

Example #3 – Displaying the Factorial of a Number in the Immediate Window.

This example calculates the factorial of a number and displays the result using Debug.Print. It utilizes a loop to calculate the factorial by multiplying the numbers from 1 to the given number. The output is then printed in the Immediate Window.

Step 1: In the new module, start by creating a subroutine “CalculateFactorial” and declare two variables: num (Integer) to store the number, and factorial (Double) to store the factorial.

Step 2: Assign a value to num (e.g., num = 5).

Step 3: Set factorial to 1 as the initial value.

Step 4: Use a For loop to calculate the factorial by multiplying the variable factorial with numbers from 1 to num.

The loop iterates from 1 to num using the loop variable i. The factorial is updated inside the loop by multiplying it with the current value of i.

(factorial = factorial * i).

For num = 5, the loop runs from 1 to 5 and updates the factorial as follows:

Iteration 1: factorial = 1 * 1 = 1

Iteration 2: factorial = 1 * 2 = 2

Iteration 3: factorial = 2 * 3 = 6

Iteration 4: factorial = 6 * 4 = 24

Iteration 5: factorial = 24 * 5 = 120

Step 5: Use VBA Debug.Print to display the calculated factorial in the Immediate Window.

Step 6: When the code is executed, the Immediate Window will show the factorial of the given number (5 in this case) as: “The factorial of 5 is: 120”.

Here is the full code:

Sub CalculateFactorial()
Dim num As Integer
Dim factorial As Double
num = 5
factorial = 1
For i = 1 To num
factorial = factorial * i
Next
Debug.Print “The factorial of ” & num & ” is: ” & factorial
End Sub

Example #4 – Printing the Full Name of the Active Workbook

In this example, VBA Debug Print is used to display the full name of the active workbook. The FullName property of the ThisWorkbook object returns the complete path and name of the workbook, which is concatenated with the descriptive text using the ampersand (&) operator.

Step 1: Start by creating a new subroutine called “PrintWorkbookName”

Step 2: “ThisWorkbook” object is used to refer to the active workbook (the workbook where the VBA code is running).

Step 3: The “FullName” property of the “ThisWorkbook” object is used to get the complete path and name of the workbook.

The FullName property returns a string representing the complete path and name of the workbook.

Step 4: The Debug.Print statement is used to display the full name of the active workbook in the Immediate Window.

The Debug.Print statement concatenates the descriptive text with the full name of the active workbook and displays it in the Immediate Window.

Step 5: Save the module and run the code. When the code is executed, the Immediate Window will show the full name of the active workbook.

Here is the complete code:

Sub PrintWorkbookName()
Debug.Print “The full name of the active workbook is: ” & ThisWorkbook.FullName
End Sub

Important Things to Note

1. VBA Debug Print statements are primarily used during the debugging process to provide information about the execution of your code. Removing or disabling these statements once the debugging is complete is recommended to avoid unnecessary output or performance impact.
2. To clear the Immediate Window, you can either close and reopen the Immediate Window or press CTRL + A followed by the DELETE key.
3. VBA Debug Print statements can be used to output variables, expressions, function results, or any text you want to display for debugging purposes.
4. When using Debug.Print with complex expressions or concatenated strings, it’s helpful to enclose them within parentheses for clarity and to ensure proper evaluation.
5. It’s possible to use conditional logic with VBA Debug Print statements by using the If…Then statement. It allows you to control when particular output is displayed based on specific conditions.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to What is VBA Debug Print. We learn how to implement debug.Print statement in different scenarios with examples. You can learn more from the following articles –

• User Defined Function in VBA
• VBA ByRef Argument
• VBA CDEC