How to print faster in Excel VBA?

Question

Excel print functionality (using VBA) is extremely slow. I hope someone has a way to speed up printing (without using 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, PageSetup properties are very slow when you set them.

You have already installed Application.ScreenUpdating = False, which is good, but equally (or more) an important step in this case is the installation Application.Calculation = xlCalculationManual. (It’s best to save these settings and then restore them at the end of the original.)

, get PageSetup , , . , , , ( ) .

, , :

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

: :

• Excel 2010 "Application.PrintCommunication", Excel 2007 "ExecuteExcel4Macro". . Excel 4 VBA.

• Excel 2007 - "Microsoft XPS Document Writer", . 3 . : Excel PageSetup.

, ...

Answer 2

@rhc, , :

Public Sub CopyPageSetupToAll(ByRef SourceSheet As Worksheet)
    ' Raise error if invalid source sheet is passed to procedure
    '
    If (SourceSheet Is Nothing) Then
        Err.Raise _
            Number:=vbErrorObjectVariableNotSet, _
            Source:="CopyPageSetupToAll", _
            Description:="Unable to copy Page Setup settings: " _
                 & "invalid reference to source sheet."
        Exit Sub
    End If

    SourceSheet.Activate

    With SourceSheet.PageSetup
        ' ...
        ' place PageSetup customizations here
        ' ...
    End With

    SourceSheet.Parent.Worksheets.Select
    Application.SendKeys "{ENTER}", True
    Application.Dialogs(xlDialogPageSetup).Show
End Sub

, :

Public Sub CopyPageSetupToAll(ByRef SourceBook As Workbook)
    Dim tempSheet As Worksheet

    ' Raise error if invalid workbook is passed to procedure
    '
    If (SourceBook Is Nothing) Then
        Err.Raise _
            Number:=vbErrorObjectVariableNotSet, _
            Source:="CopyPageSetupToAll", _
            Description:="Unable to copy Page Setup settings: " _
                 & "invalid reference to source workbook."
        Exit Sub
    End If

    Set tempSheet = SourceBook.Worksheets.Add

    tempSheet.Activate

    With tempSheet.PageSetup
        ' ...
        ' place PageSetup customizations here
        ' ...
    End With

    SourceBook.Worksheets.Select
    Application.SendKeys "{ENTER}", True
    Application.Dialogs(xlDialogPageSetup).Show
    tempSheet.Delete

    Set tempSheet = Nothing
End Sub

SendKeys() Application.Dialogs . .:)

Answer 3

If you want the basic page settings for each tab in the book to speed up work by setting up one desktop, and then copying these worksheet options in some way to other worksheets? Is it possible?