Printing faster in Excel
Yes, the PageSetup properties are very slow when you set them.
You have already set Application.ScreenUpdating = False
, which is good, but an equally (or more) important step in this case is to set Application.Calculation = xlCalculationManual
. (It is best if you save these settings and then restore them to the original at the end.)
Additionally, the property get for each PageSetup property is very fast, while it is only the property set that is so slow. Therefore, you should test the new property setting to make sure it isn't already the same as the existing property value in order to prevent an unnecessary (and expensive) call.
With all this in mind, you should be able to use code that looks something like the following:
Dim origScreenUpdating As Boolean
origScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
Dim origCalcMode As xlCalculation
origCalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
With ActiveSheet.PageSetup
If .PrintHeadings <> False Then .PrintHeadings = False
If .PrintGridlines <> False Then .PrintGridlines = False
If .PrintComments <> xlPrintNoComments Then .PrintComments = xlPrintNoComments
' Etc...
End With
Application.ScreenUpdating = origScreenUpdating
Application.Calculation = origCalcMode
Edit: A couple of updates:
For Excel 2010 and above you can make use of the 'Application.PrintCommunication' property, while for Excel 2007 and below, you can make use of 'ExecuteExcel4Macro'. For more details, see Migrating Excel 4 Macros to VBA.
For Excel 2007 and below, another interesting trick is to temporarily assign the printer driver to the 'Microsoft XPS Document Writer' and then set it back. Printing speed can improve by 3x. See: Slow Excel PageSetup Methods.
Hope this helps...
Lance Roberts
Control Systems Engineer. Most people want to stick their head in the sand and ignore problems, in an effort to avoid conflict. I refuse to be that passive person. Problems are there to be fixed, which means that first they have to identified. Denial is not just a river in Egypt.
Updated on June 04, 2022Comments
-
Lance Roberts almost 2 years
The print functionality of Excel (using VBA) is extremely slow. I'm hoping someone has a way of speeding the printing up (without using the Excel 4 Macro trick). Here's how I do it now:
Application.ScreenUpdating = False With ActiveSheet.PageSetup -various setup statements which I've already minimized- End With ActiveSheet.PrintOut Application.ScreenUpdating = True
-
Lance Roberts over 15 yearsI was able to get quite an increase, most of my sets were non-boolean values, do you know if Print Setup is by sheet or workbook?
-
Vahid over 15 yearsHey Lance, yes, you can get a very big improvement by following this approach -- I'm glad it worked for you. :-) The page setup is for each worksheet individually, not for the workbook as a whole. (The Worksheeet class has a PageSetup property, but the Workbook class does not.)
-
JimmyPena over 12 yearsIn addition to Mike's answer, my understanding is that PageSetup could be made faster by turning off events. I can't find the link, but there should be a MSKB article somewhere stating something like "PageSetup calls an event each time it's called". So turning off events might add a bit of speed as well.