VBA Print

Excel VBA Print Function

All of us know that Microsoft Excel is used for creating formulae, easy calculation, multiple databases. Many of such data is used by large organizations in their day to day work. In the world of computers and the internet, everything is digitized. However, we frequently use the Print function for taking out hard copies of the data and working upon it. Have we ever thought about how to create an automated Print format for any workbook?

How to Use Print Function in Excel VBA?

We are very well aware of the Print function which forms part of tab File – Print. However, today we will learn to insert the print function but with the help of VBA. To make our understanding better we have explained the process with the help of different examples to use VBA Print.

VBA Print – Example #1

We have the following data of several companies with details of Revenue, Expenses, Profit and percentage of profit on Revenue.

To print the above, we need to have a printer added to our computer/ laptop and we can directly print the above data by going to File—Print.

Like the above, we have such options in VBA also, with advanced features. It is called VBA Print.

However, for making a VBA Print function work properly, we first need to create a Macro. A Macro is generally created for ease of access to data. It is usually created as a shortcut to a large volume of data.

Follow the below steps to use the Print function in Excel VBA.

Step 1: Create a Macro as follows

Select the Data—View—Macros – Record Macro. 

As soon as we put this option, we get the below mentioned screen.

Step 2: Name the Macro

In the present case, we have kept the name of our Macros as “Macro1” which is a default name provided. Once the Macro is created we can proceed with creating the Print option.

Step 3: The Developer option.

The Print function can be used in VBA with the help of a Developer option. For getting the option on Excel, we need to follow the instruction as follows: Click on Developer’s tab then click on Visual Basic to get into VBA.

Step 4: Once we are in VBA we need to insert a module so that we can write code in it. Do as follows,

Go to Insert tab and click on Module.

Step 5: Now let us start writing the code, to do that we need to name the macro first as follows, The subject shall start with command “Sub- Print1 ()” since the information database is taken from Macro1 which we created in Step 2. The function is written as follows:

Code:

Sub Print1()

End Sub

Step 6: The first command is the source data which we have mentioned as Macro1. The command “Selected Sheets” denotes that the same data has been copied from the source sheet where data is mentioned.

Code:

Sub Print1()

ActiveWindow.SelectedSheets

End Sub

Step 7: Then we have to use “Printout” option in VBA which appears on the screen.

Code:

Step 8: After putting the “Printout” option, we select the number of copies in the argument.

Code:

Sub Print1()

ActiveWindow.SelectedSheets.PrintOut copies:=1

End Sub

For instance, in our example, we have mentioned “Copies=1”. But we can also modify the details like 2 or 3 copies if required. We can customize it based on a number of copies we need to print.

Step 8: The next argument is “Collate” function. By inputting “Collate _:=True” function we ensure that the data is composed together in the sheet. In the above function,

Code:

Sub Print1()

ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True

End Sub

Step 9: We have also mentioned “Ignore print areas” because we have only 1 sheet to print and that is well within the ambit of Print areas. But we can customize this option also, if necessary.

Code:

Sub Print1()

ActiveWindow.SelectedSheets.PrintOut copies:=1, collate:=True, IgnorePrintAreas:=False

End Sub

 

Step 10: To end the command we need to enter “End Sub”. Unless we enter this command, the arguments are considered incomplete.

In case we do not enter the above-mentioned command, the following message displays while execution—

Step 10: We can directly execute by pressing F5 or clicking on the play button.

As soon as we press the Execution command, the document area is automatically taken to the Printer for Printing.

VBA Print – Example #2

In the same way, we have one more function related to Print called Print Preview in VBA. This helps us in looking at the data, as to how it will appear at the time of Print, before moving ahead with the execution. To explain the Print preview function in VBA, we have used the same data as used in the previous example as follows:

Step 1: In the VBE, start writing the macro and define the variable name.

Code:

Sub Print2()

End Sub

Step 2: In this, the function “Printpreview” is used.

Code:

Sub Print2()

ActiveSheet.PrintPreview

End Sub

Step 3: Run this code by hitting F5 directly or manually hitting the Run button on the upper left panel. As soon as we execute the command, the following screen automatically appears.

The Print Preview helps us in looking through the data before moving ahead with the Print.

So, this is how the functions in VBA Print can be used for performing Printouts directly. I hope we now have a better understanding and implementation of the function.

Things to Remember

• The VBA function becomes accessible after creating Macros for the source data. We have learned in the first few steps on how to create Macros.
• We should always remember not to provide spaces in between the functions.
• VBA function can directly be accessed by Alt+F11 instead of going through Developer mode.
• Always remember when you are typing Activesheet function as you can see in Step 2 of Example 2, then make sure that your cursor (click) before executing the command is upon the same sheet whose Print you require.

Recommended Articles

This is a guide to VBA Print Function. Here we discuss how to use Print Function in Excel VBA along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Block Comment
2. Excel Print
3. VBA Break for Loop
4. Print Comments in Excel