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.
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.
So while I’m in the immediate window I check for the Application.PrintCommunication property and find out it doesn’t exist.
So I have to use the OperatingSystem property of the Application object to give me another condition.
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.
Nice and useful tip, Gregory. I had not heard of this before!
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]
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.
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:
https://www.mrexcel.com/forum/showthread.php?t=559951
I haven’t had any problem yet. Thanks for the tip & link.
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!
The PrintCommunication Property was added in Excel 2010, which is why it won’t work with Excel 2007.