vba 在 Excel 中打印速度更快

Printing faster in Excel

提问by Lance Roberts

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:

Excel 的打印功能（使用 VBA）非常慢。我希望有人有办法加快打印速度（不使用 Excel 4 宏技巧）。这是我现在的做法：

Application.ScreenUpdating = False

With ActiveSheet.PageSetup

  -various setup statements which I've already minimized-

End With   
ActiveSheet.PrintOut

Application.ScreenUpdating = True

回答by Mike Rosenblum

Yes, the PageSetup properties are very slow when you set them.

是的，当您设置 PageSetup 属性时，它们非常慢。

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

您已经设置了Application.ScreenUpdating = False，这很好，但在这种情况下同样（或更多）重要的步骤是设置Application.Calculation = xlCalculationManual. （最好保存这些设置，然后在最后将它们恢复为原始设置。）

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.

此外，每个 PageSetup 属性的属性获取都非常快，而只有属性集才这么慢。因此，您应该测试新的属性设置以确保它与现有的属性值不同，以防止不必要的（且代价高昂的）调用。

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.

1. 对于 Excel 2010 及更高版本，您可以使用“Application.PrintCommunication”属性，而对于 Excel 2007 及更低版本，您可以使用“ExecuteExcel4Macro”。有关更多详细信息，请参阅将Excel 4 宏迁移到 VBA。

2. 对于 Excel 2007 及更低版本，另一个有趣的技巧是将打印机驱动程序暂时分配给“Microsoft XPS 文档编写器”，然后再将其重新设置。打印速度可提高 3 倍。请参阅：慢速 Excel 页面设置方法。

Hope this helps...

希望这可以帮助...

回答by Lance Roberts

In furthering Michael's post and answering @rhc's question, the following code may also help you if need to copy Page Setup customizations from a single worksheet to multiple worksheets in a workbook:

在进一步推进 Michael 的帖子并回答 @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

Alternatively, you could also modify the procedure to create a temporary worksheet to host your Page Setup changes, and then propagate those changes out to the other worksheets in your workbook:

或者，您也可以修改该过程以创建临时工作表来承载您的页面设置更改，然后将这些更改传播到工作簿中的其他工作表：

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

Due to the use of the SendKeys()function and Application.Dialogsfunctionality, this code does not offer the cleanest possible solution. However, it gets the job done. :)

由于SendKeys()函数和Application.Dialogs功能的使用，此代码不提供最干净的解决方案。但是，它完成了工作。:)

回答by Lance Roberts

if you want to have basicly the same page settings for every tab in a workbook can you speed things up by setting up one workshet and then copying that worksheet's settings somehow to the other worksheets? Is this possible?

如果您想为工作簿中的每个选项卡设置基本相同的页面设置，是否可以通过设置一个工作表然后将该工作表的设置以某种方式复制到其他工作表来加快速度？这可能吗？