WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Debug Print

VBA Debug Print

Excel VBA Debug.Print

Debug Print is one of the useful tools 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 prompt 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 shows the log of returned output values to the immediate windows. It saves a lot of time for 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.

VBA Debug.Print

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 messages and asserts in useful in the evaluation of the conditions. In VBA, debug. The 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 display 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 correctly or not. It prints 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 variables at a time in the immediate window. It is the best and alternative approach to show the output.

For example,

Debug.print count, sum, average, standard deviation

As shown in the example, all the variables are separated with commas. This statement can transfer the output to the immediate window even if 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 concerning 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 the 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.

You can download this VBA Debug Print Excel Template here – VBA Debug Print Excel Template

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.

VBA debug print Example 1

After adding a name, click on create. This opens the VBA editor.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA debug print Example 1-1

Develop a small program, as shown in the figure.

Code:

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

VBA debug print Example 1-2

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 prompt window. Press CTRL+G to see the result, as shown in the screenshot.

Run this code using the F5 key and press CTRL+G to see the output in Immediate Window.

VBA debug print Example 1-5

This program can be simplified by separating the debug—print statements by a comma.

Code:

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

VBA debug print Example 1-3

This debugs statement prints the output in the same line, as shown in the screenshot.

VBA debug print Example 1-4

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.

Code:

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

VBA debug.print Example 2

In this program, two variables called S and Num, are considered as string and integer. The open statement is used to create a text file with the name test. A column called “Hello World” is declared into the variable S.

When you run the VBA code manually or using the F5 key, the output is written into the immediate window, and the file at a time is shown in the folder.

VBA debug print Example 2-4

The output to file is shown in the below-mentioned figure.

VBA debug.print Example 2-1

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—a print statement to show the factorial of a number.

Code:

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

Dp Example 3

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. The print statement is used outside the “for” loop to display the value after completing the circle. The output is determined.

Dp Example 3-1

If we use debug. Print statement inside the “for” loop, the fact-value is displayed for every recurring time, as shown in the figure.

Code:

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

Example 3-2

Run the code by pressing the 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.

VBA debug print Example 3-4

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

This example explains how to print the current workbook name into the prompt window.

The program is developed, as shown in the figure.

Code:

Sub Activework()

Dim count As Long
For count = 1 To Workbooks.count
    Debug.Print Workbooks(count).FullName
Next count
Debug.Print count

End Sub

Example 4

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.

Dp Example 4-1

The path of the workbook in the drives is accurately displayed by using the VBA debugprint 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 result needs to be shown to a file stored in the drive.

Recommended Articles

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 –

  • Switch Case in VBA
  • VBA Break For Loop
  • VBA DatePart
  • VBA Print
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Debug Print Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More