VBA Print Statement

One of the key statements in VBA is the Print # statement, which allows you to print output to the Immediate window or a text file. The Print # statement is especially useful when debugging code, as it can display the values of variables and expressions.

The Purpose of VBA Print # Statement

Syntax

The syntax for the Print # statement in VBA is:
Print #FileNumber, [OutputList] Where FileNumber is the number of the opened file you want to print to, and OutputList is a list of expressions, variables, or text that you want to print. The file number must be a valid integer, and the output list can include any valid expressions or variables separated by commas.

Examples on VBA Print # Statement

Printing to the Immediate window

The Immediate window in the VBA editor is a useful tool for testing and debugging code. You can print values to the Immediate window using the Print # statement and view the output as soon as the code runs. For example:

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

Output:
Hello World!
15

In this example, we have printed a text string and the result of a mathematical expression to the Immediate window.

Printing to a text file

The Print # statement can also be used to print output to a text file. This is useful for creating reports or saving data from a macro. In order to print to a text file, you must first open the file using the Open statement and specifying the Output mode. For example:

  Dim MyFile as Integer
  Open "C:\Users\MyName\Documents\Report.txt" For Output As MyFile
  Print #MyFile, "Report on accounts:"
  Print #MyFile, "Account #, Balance"
  
  For i = 1 to 10
      Print #MyFile, i, Cells(i, 1).Value
  Next i
  
  Close MyFile

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 example demonstrates how the Print # statement can be used to print data from a worksheet to a text file in a formatted manner.

Printing multiple values

You can print multiple values separated by commas in the Print # statement. This is useful when you want to print the values of multiple variables or expressions. For example:

  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:
The value of x is 10 and the value of y is 20
In this example, we have printed the text and values of two variables in a single line.

Printing to specific locations

The Print # statement can also be used to print output to a specific location in a text file. This is achieved by using the Seek function combined with the Print # statement. For example:

  Dim MyFile as Integer
  Open "C:\Users\MyName\Documents\Report.txt" For Output As MyFile
  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 have printed a message for customers with a balance over $5000 at the beginning of the report, using the Seek function to move the cursor to the first line.

Printing to a workbook

Similar to printing to a text file, the Print # statement can also be used to print to a separate workbook. The syntax is the same, except you must specify the workbook and sheet name in the Open statement. For example:

  Dim OtherWorkbook as Workbook
  Set OtherWorkbook = Workbooks.Open("C:\Users\MyName\Documents\OtherWorkbook.xlsx")
  Open OtherWorkbook.Path & "\" & OtherWorkbook.Name For Output As MyFile
  Print #MyFile, "Report on accounts:"
  
  For i = 1 to 10
      Print #MyFile, Cells(i, 1).Value, Cells(i, 2).Value
  Next i
  
  Close MyFile
  OtherWorkbook.Close

Output (in OtherWorkbook.xlsx):
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
In this example, the Print # statement is used to print data from the current workbook to a sheet in a separate workbook.

Important Notes & Remarks

• The Print # statement can only be used to print a fixed number of expressions to a file. If you want to print a variable number of items, you can use the Write # statement instead.
• When using the Print # statement to print to a text file, the output will be in plain text format, without any formatting or styling.
• The file number used in the Print # statement must match the one used in the Open statement when printing to a file or workbook.
• The Print # statement can also be used to print to a sequential file, where each new print statement is added to a new line in the file.
• The Print # statement can only print up to 255 characters per line. If you need to print longer strings, use the Write # statement.

Conclusion

In conclusion, the Print # statement in VBA is a powerful tool for debugging code, generating reports, and printing data from a macro. By understanding its purpose, syntax, and different use cases, you can make the most out of this statement in your VBA projects.

I hope this post has provided a comprehensive overview of the Print # statement in VBA. If you have any feedback or views on this topic, please leave a comment below. Thank you for reading!