VBA Print

What is Print in VBA Excel?

The VBA Print typically refers to the action of controlling the printing of Excel documents. It involves using VBA code to automate or customize the printing of Excel worksheets, charts, ranges, or other document elements.

VBA provides methods and properties to interact with the Excel application, including options to print Excel workbooks and sheets. The VBA Print keyword is typically used in the context of controlling the printing of Excel documents.

Let us look at an example. Here, we will see how to use VBA Print to automate the printing of a specific chart from an Excel worksheet. We start by creating a VBA subroutine called PrintChart. Next, we use the Set keyword to assign the variable myChart to a specific chart named “Chart 1,” located on “Sheet1” within the workbook where the VBA code is running. Finally, to initiate the printing process, we call the PrintOut method on the myChart.Chart object.

When you run this macro, it prints “Chart 1” from “Sheet1” of the workbook referenced by ThisWorkbook without displaying a VBA print preview or print dialog.

What is Print in VBA Excel?

In VBA Excel, “Print” is used to control the printing of Excel documents rather than sending output to a physical printer directly.
The VBA PrintOut method is commonly used to print worksheets, charts, or ranges in Excel using VBA.
You can customize the printing process by using various optional parameters such as From, To, Copies, Collate, and more.
VBA can also be used to interact with other applications like Adobe Acrobat for advanced printing tasks.
You can’t directly VBA print array as a single command; you need to use the “Debug.Print” statement or another method to display the contents of an array.

Syntax of VBA PrintOut in VBA Excel

The VBA Print Out method in Excel is used to print a workbook, worksheet, chart, or range.

Its syntax varies depending on the specific object you want to print.

Here’s a basic syntax example for printing a worksheet:

Worksheets(“Sheet1”).PrintOut

This code prints “Sheet1” without displaying the print dialog. You can customize it by including various optional parameters as needed. For printing a worksheet:

Worksheets(“Sheet1”).PrintOut [From], [To], [Copies], [Preview], [ActivePrinter], [PrintToFile], [Collate]

For printing a chart:

myChartObject.Chart.PrintOut [From], [To], [Copies], [Preview], [ActivePrinter], [PrintToFile], [Collate]

For printing a range:

Worksheets(“Sheet1”).Range(“A1:B10”).PrintOut [From], [To], [Copies], [Preview], [ActivePrinter], [PrintToFile], [Collate]

In these syntax examples, the parameters in square brackets (e.g., [From], [To], [Copies], etc.) are optional and allow you to customize the printing process.

[From]: Determine the starting page for printing. If no value is provided, it will begin from the first page by default.
[To]: Specify the final page to be printed. If not specified, it will print up to the last page.
[Copies]: Indicate the number of copies needed for printing.
[Preview]: Choose whether you want to view a print preview before initiating the print. Set it to TRUE if you want a preview or FALSE if you prefer not to see one.

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.

Start Learning Now

How to Use Print in VBA Excel? (with Steps)

To use printing functionality in VBA Excel, follow these steps:

In the Excel workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
Inside the VBA editor, go to Insert → Module to insert a new module where you can write your VBA code.
Write your VBA code for printing within the module. You can use the VBA PrintOut method or other print-related properties/methods as needed.
Customize your VBA code to specify what you want to print, including worksheets, charts, or ranges, and set any optional parameters such as page range, number of copies, or print preview.
To run your VBA code, press F5 or call the subroutine from within Excel.
Excel will execute the code, and the specified content will be printed according to the settings you defined in your VBA code.

Examples

Example #1 – Print Specific Worksheets

In this scenario, if we have a workbook containing multiple worksheets and we wish to automate the printing process using VBA, we can implement a VBA print that prints all worksheets within the workbook with the exception of “Sheet1.”

Step 1: In the new module, we start by defining a subroutine named PrintWorksheets.

Step 2: We declare a variable ws of type Worksheet. This variable will be used to iterate through the worksheets in the current workbook.

Step 3: We enter a For Each loop, which allows us to loop through each worksheet in ThisWorkbook.Sheets. ThisWorkbook refers to the workbook where the VBA code is running.

Step 4: Inside the loop, we check an If condition. We are comparing the name of each worksheet (ws.Name) with the string “Sheet1”. If the worksheet’s name is not equal to “Sheet1,” we proceed with the code inside the If block.

Step 5: Within the If block, we use the VBA PrintOut method on the worksheet ws. This method prints the worksheet without displaying the print dialog because we haven’t specified any optional parameters.

Step 6: The loop continues to the next worksheet, and the process repeats until all worksheets have been processed.

Step 7: Now, save the macro and exit the VBE. Press Alt + F8 to open the macro window, select “PrintWorksheets,” and click on Run.

Step 8: When you run the provided VBA code, it will print all worksheets in the workbook referenced by ThisWorkbook, except for “Sheet1,” without displaying a print preview or print dialog.

The following is the required output that is printed to PDF.

Here is the full code:

Sub PrintWorksheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Name <> “Sheet1” Then
ws.PrintOut
End If
Next ws
End Sub

Example #2 – Print a Range

Suppose we have a worksheet with data in a specific range, and we want to automate the printing process using the VBA Print method and produce two copies of the printout and arrange them in a collated order.

Step 1: In the new module, we define another subroutine named PrintSelectedRange.

Step 2: Next, we reference a specific worksheet in “ThisWorkbook” using ThisWorkbook.Sheets(“Sheet1”).

Step 3: Within this worksheet, we specify a range using Range(“A1:B10”). It is the range that we want to print.

Step 4: We call the PrintOut method on the specified range. In this case, we include optional parameters:

Copies:=2: This parameter specifies that we want to print 2 copies of the selected range.

Collate:=True: This parameter indicates that the copies should be collated, meaning they will be arranged in page order.

Step 5: Now, save the macro and exit the VBE. Press Alt + F8 to open the macro window, select “PrintSelectedRange,” and click on Run.

Step 6: When you run the VBA macro, it will print the specified range “A1:B10” in “Sheet1” of the workbook referenced by ThisWorkbook with two copies, collated, and without displaying a VBA print preview or VBA print dialog.

VBA Print in Excel - Example 2 - Step 6a

The following is the required output that is printed to two copies of a PDF document.

VBA Print in Excel - Example 2 - Step 6b

Here is the full code:

Sub PrintSelectedRange()
ThisWorkbook.Sheets(“Sheet1”).Range(“A1:B10”).PrintOut Copies:=2, Collate:=True
End Sub

Parameters of Printout Method in VBA Excel

The VBA PrintOut method in Excel accepts several optional parameters that allow you to customize the printing process. Common parameters include:

Parameter	Description
From	Specifies the page number to start printing from.
To	Specifies the page number to end printing at.
Copies	Number of copies to print.
Collate	True to collate multiple copies, False to print them consecutively.
Preview	True to display a print preview, False to print directly.
ActivePrinter	Specifies the printer to use.
PrintToFile	True to print to a file, False to print to a physical printer.

How to Use the Parameters of Print Out Method in Excel VBA? (with Steps)

To use the parameters of the VBA PrintOut method:

Step 1: In the new module, write or locate the VBA code that contains the VBA PrintOut method.

Ensure that you are applying the VBA PrintOut method to the appropriate object, such as a worksheet, chart, or range.

Step 2: Within the VBA Print Out method, include the parameters you want to customize. For example:

Sub CustomPrint()

ThisWorkbook.Sheets(“Sheet1”).PrintOut From:=1, To:=3, Copies:=2, Collate:=True, Preview:=True

End Sub

This code prints “Sheet1” from pages 1 to 3, creating 2 copies, collating them, and displaying a print preview.

You can customize these parameters based on the VBA Print out methods mentioned in the above section.

Step 3: To run your VBA code with the specified parameters, press F5 or call the subroutine from within Excel.

Step 4: Excel will execute the code, and the specified content will be printed based on the parameter settings you defined.

Important Things to Note

Ensure that Excel is visible when using print-related VBA code, as some actions require a visible Excel application.
Be cautious with parameters like ActivePrinter as they can affect the default printer used by Excel.
Always handle error checking and exceptions when using print-related methods to handle potential issues gracefully.
There is no dedicated VBA print to console functionality; instead, you can use the Immediate Window for debugging and displaying output messages.
When printing large amounts of data, consider using error handling to avoid crashes or unexpected behavior.

Frequently Asked Questions (FAQs)

1. How to VBA print PDF without opening it in Excel?

To VBA print a PDF without opening it in Excel, you can use VBA to automate printing using an external PDF reader, like Adobe Acrobat. This typically involves using the PDF reader’s API to send print commands to the PDF file.

2. How to set print area in Excel VBA?

To set a print area in Excel VBA, you can use the PageSetup.PrintArea property of a worksheet to specify the range you want to print. For example: Worksheets(“Sheet1”).PageSetup.PrintArea = “A1:B10” sets the print area to cells A1 through B10.

3. How to print an array in VBA?

To print the contents of an array in VBA, you can loop through the array elements and use the Debug.Print statement to output them to the Immediate Window or log them to a file.

4. How to print in the Immediate Window in VBA?

To print messages or variable values to the Immediate Window in VBA, use the Debug.Print statement. For example: Debug.Print “Hello, Immediate Window!” will print the message to the Immediate Window when the code is executed in the VBA editor.

Download Template

This article must be helpful to understand the VBA Print, with its features and examples. You can download the template here to use it instantly.

VBA Print Download

VBA Print