vba - Printing faster in Excel

Question

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

Answer 1

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:

1. 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.

2. 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...