VBA Print Statement

Understanding the VBA Print # Statement

The VBA Print # statement is a key tool for debugging and generating reports. It allows you to print output to the Immediate window or a text file. This statement is particularly useful when you need to inspect values of variables or expressions during the execution of your VBA code.

The Purpose of VBA Print # Statement

The Print # statement in VBA enables you to print values to various output destinations, such as the Immediate window for debugging purposes or a text file for generating reports.

Syntax of VBA Print # Statement

The basic syntax for the Print # statement is:

Print #FileNumber, [OutputList]

• FileNumber: The number of the file you want to write to (must be a valid integer).
• OutputList: The expressions, variables, or text that you want to print (separated by commas).

Examples of VBA Print # Statement

Printing to the Immediate Window

The Immediate window is a powerful tool for testing and debugging code in the VBA editor. By printing values to this window, you can instantly view outputs during code execution. Here’s an example:

Print #1, "Hello World!"
Print #1, 10 + 5

Output in the Immediate Window:

Hello World!
15

In this example, the statement prints a text string and the result of a mathematical expression.

Printing to a Text File

You can also use the Print # statement to save output to a text file. This is useful for generating reports or saving macro data. To print to a text file, you first need to open the file using the Open statement.

Here’s an example:

Sub PrintReportToTextFile()

Dim MyFile As Integer
Dim i As Integer
Dim FilePath As String

 

' Change the path based on your requirement
FilePath = "C:\Users\UserName\Documents\Report.txt"

 
' Get the file number
MyFile = FreeFile


' Open the file for output
Open FilePath For Output As MyFile

' Print the header
Print #MyFile, "Report on accounts:"
Print #MyFile, "Account #, Balance"

 

' Loop through the first 10 rows and print account number and balance
For i = 1 To 10
Print #MyFile, i, Cells(i, 1).Value
Next i

' Close the file
Close MyFile

MsgBox "Report has been written to the text file successfully!"
End Sub

Output in Report.txt:

Report on accounts:

Account #, Balance

1, $1000
2, $1500
3, $2000
4, $3500
5, $5000
6, $8000
7, $9000
8, $10000
9, $12000
10, $15000

This code snippet prints the data from an Excel worksheet to a text file, formatted with a header and data rows.

Printing Multiple Values

The Print # statement can print multiple values in one line. For instance, printing the values of multiple variables or expressions:

Dim x As Integer, y As Integer
x = 10
y = 20

Print #1, "The value of x is", x, "and the value of y is", y

Output in the Immediate Window:

The value of x is 10 and the value of y is 20

Printing to Specific Locations

You can use the Seek function in combination with the Print # statement to print to a specific location within a text file. Here’s how you can do it:

Dim MyFile As Integer
MyFile = FreeFile

FilePath = "C:\Users\UserName\Documents\Report.txt"
Print #MyFile, "Report on accounts:"

For i = 1 To 10
If Cells(i, 2).Value > 5000 Then
Seek MyFile, 1
Print #MyFile, "Customer #" & Cells(i, 1).Value & " has a large balance."
End If
Next i
Close MyFile

Output in Report.txt:

Report on accounts:
Customer #5 has a large balance.
Customer #6 has a large balance.
Customer #7 has a large balance.
Customer #8 has a large balance.
Customer #9 has a large balance.
Customer #10 has a large balance.

In this example, we print messages about customers with a balance over $5000 at the beginning of the report.

Printing to a Workbook

You can also output data into a separate workbook by directly writing to cells in the target workbook, without using the Print # statement. Here’s an example of how to do it:

Sub PrintReportToWorkbook()
Dim OtherWorkbook As Workbook

Dim ws As Worksheet
Dim i As Integer


' Open the other workbook
Set OtherWorkbook = Workbooks.Open("C:\Users\UserName\Documents\OtherWorkbook.xlsx")

' Assume you want to print the report in the first worksheet
Set ws = OtherWorkbook.Sheets(1)

' Print the report headers
ws.Cells(1, 1).Value = "Report on accounts:"
ws.Cells(2, 1).Value = "Account #"
ws.Cells(2, 2).Value = "Balance" 

' Loop through and add data from the first 10 rows of the current workbook (active sheet)

For i = 1 To 10
ws.Cells(i + 2, 1).Value = i ' Account number (row index)
ws.Cells(i + 2, 2).Value = Cells(i, 1).Value ' Balance (cell value from active sheet)
Next i

'Save and close the other workbook
OtherWorkbook.Save
OtherWorkbook.Close

MsgBox "Report has been written to the workbook successfully!"

End Sub

Important Notes & Remarks

• The Print # statement can only print up to 255 characters per line. Use the Write # statement if you need to print longer strings.
• When printing to a text file, the output will be in plain text, without formatting or styling.
• The file number used in the Print # statement must match the one used in the Open statement.
• The Print # statement can only add a fixed number of expressions to the file. For a variable number of items, use the Write # statement.

Conclusion

The Print # statement is a versatile tool in VBA for both debugging and generating reports. By understanding how to use it with different outputs like the Immediate window, text files, and workbooks, you can enhance your VBA projects and streamline data handling.

I hope this post provided a clear understanding of the Print # statement in VBA. If you have any questions or feedback, please feel free to comment below. Thank you for reading!