Speeding up VBA with the PrintCommunication Property in Excel 2010

by Gregory on March 23, 2011

I have an Excel file that creates different reports from a data table using a macro, and before Excel 2010 was released the “time-hog” was running VBA code for the print settings. Tell me you’ve added something like the following code and had your execution time slow exponentially.

 With ActiveSheet.PageSetup
    .CenterHeader = "&""Verdana,Bold""&12&A"
    .CenterFooter = "&P of &N"
    .LeftMargin = Application.InchesToPoints(0.7)
    .RightMargin = Application.InchesToPoints(0.7)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.3)
    .FooterMargin = Application.InchesToPoints(0.3)
    .CenterHorizontally = True
    .CenterVertically = False
    .Orientation = xlPortrait
    .FitToPagesWide = 1
    .FitToPagesTall = False
End With

Another factor is the default printer of your computer when the code runs. I usually set my default printer to CutePDF, which isn’t really a printer, but a program that writes (prints) to a PDF file. Using this default printer while running the PageSetup code chops mega-time off the execution.

One trick I used was to run this code in the Workbook_BeforePrint module. That way, running my macro to create a new report doesn’t execute the PageSetup code and therefore executes much, much, much, much, much faster.

The New PrintCommunication Property

In Excel 2010 I happen to record a macro for print settings and discovered a new page setup property:

Application.PrintCommunication

The Application.PrintCommunication property is new in Excel 2010. It specifies whether communication with the printer is turned on. The values are Boolean and Read/Write.

Using the PrintCommunication Property

Set the Application.PrintCommunication equal to FALSE, then run the Page Setup code, then set it back to TRUE and your code will run exponentially faster. However, there are two problems with using the Application.PrintCommunication property and both deal with compatibility.

Compatibility with Older Versions

Older versions of Excel don’t recognize this new property so you have to code around this fact to ensure backward compatibility. I checked the Excel 2010 version number by running some code in the VBA Editor’s Immediate window.

Excel Version code

You can also check your Excel version by choosing File > Help and reading the screen.

Since the Application.version property returns a String value, the Val function is required to make use of it. My code now reads like this to achieve backward compatibility:

If Val(Application.Version) >= 14 Then Application.PrintCommunication = False
    With ActiveSheet.PageSetup
'    (all the ugly page setup code goes here)
    End With
    If Val(Application.Version) >= 14 Then Application.PrintCommunication = False

I checked to see how long it takes to run the VBA code to produce my report in three versions of Excel: 2010, 2007 and 2003.

Excel 2010 – run1 = 0.918 sec, run2 = 0.867 sec
Excel 2007 – run1 = 5.965 sec, run2 = 6.273 sec
Excel 2003 – run1 = 6.066 sec, run2 = 6.125 sec

My default printer at the time was a HP D110 and it took over 5 seconds longer to run the PageSetup code in Excel 2007 and 2003.

Compatibility with Excel 2011 for Mac

By now I’m happy with Excel for adding this new property, but reality sets in and I realize that Excel 2011 can run code too. I check to see what version number Excel 2011 for Mac happens to be, and it too is version 14.0.

Excel 2011 version

So while I’m in the immediate window I check for the Application.PrintCommunication property and find out it doesn’t exist.

No Print Communications property

So I have to use the OperatingSystem property of the Application object to give me another condition.

Excel 2010 Operating System

Excel 2010 Operating System

Excel 2011 Operating System

Excel 2011 Operating System

I use the Left function to grab the first three letters of the operating system name.

Left(Application.OperatingSystem, 3) = “Win”

Then change my code to account for earlier Excel versions AND the Windows operating system as follows:

If Val(Application.Version) >= 14 And Left(Application.OperatingSystem, 3) = "Win" _
    Then Application.PrintCommunication = False
'    (my page setup code here)
    If Val(Application.Version) >= 14 And Left(Application.OperatingSystem, 3) = "Win" _
        Then Application.PrintCommunication = True

Using the PrintCommunication property of the Application Object will help speed up your code execution. However, you need to be aware of backward compatibility for earlier versions of Excel that don’t recognize the code, and with the Mac version of Excel 2011 that should have the same property, but doesn’t.

Related Posts Plugin for WordPress, Blogger...
Daniel Ferry March 24, 2011 at 12:19 pm

Nice and useful tip, Gregory. I had not heard of this before!

SkA May 11, 2011 at 12:18 am

smallFix:
In “Compatibility with Older Versions”, last string of code must be with [b]True[/b] at the end:
[code]If Val(Application.Version) >= 14 Then Application.PrintCommunication = True[/code]

Gregory May 11, 2011 at 9:21 am

I’m not sure what you’re talking about here. Excel 2003 would be opened in “Compatibility Mode” in Excel 2010, but if the Application.PrintCommunicaton Property was added to an Excel 2003 file the compiler would give you an error.

Secondly I couldn’t find any documentation on the “last string of code [/b]” and when I tried to use this in the VBA editor I got errors for every variation I coulc think of.

If you have some reference for this [/b] switch I would be interested to know it. Thanks for the input.

p45cal June 28, 2011 at 5:27 am

Application.PrintCommunication has its gotchas, headers and footers not being set properly unles it’s set to True; see from 3 post onwards on this thread:
http://www.mrexcel.com/forum/showthread.php?t=559951

Gregory June 28, 2011 at 5:42 am

I haven’t had any problem yet. Thanks for the tip & link.

Theike May 17, 2013 at 3:01 am

Hi Gregory,

Thank you for the very informative page.
I’ve tried it, but I get the following error:

Compile error:
Expected: Then or GoTo

(It has the ‘;’ highlighted in the ‘If Val(Application.Version) >= 14 Then’)

I got this error on Excel 2007 (ver 12), when trying to run compatibility checks after creating my programme in Excel 2010.

Please let me know if you’ve any advice, thanks a lot!

Gregory May 27, 2013 at 1:30 pm

The PrintCommunication Property was added in Excel 2010, which is why it won’t work with Excel 2007.

Comments on this entry are closed.

Previous post:

Next post: