如何在Excel VBA中打印更快？ [英] How to print faster in Excel VBA?

问题描述

Excel（使用VBA）的打印功能非常慢。我希望有人可以加快打印速度（不使用Excel 4 Macro技巧）。以下是我现在如何做：

  Application.ScreenUpdating = False 
 
 With ActiveSheet.PageSetup 
 
 -various安装程序语句，我已经最小化了
 
结束
 ActiveSheet.PrintOut 
 
 Application.ScreenUpdating = True 
  

解决方案

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

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

此外，每个PageSetup属性的属性都非常快，而只有属性集是那么慢。因此，您应该测试新的属性设置，以确保它与现有的属性值不一致，以防止不必要的（并且昂贵的）调用。

考虑到这一点，您应该可以使用看起来像以下代码：

  Dim origScreenUpdating As Boolean 
 origScreenUpdating = Application.ScreenUpdating 
 Application.ScreenUpdating = False 
 
 Dim origCalcMode As xlCalculation 
 origCalcMode = Application.Calculation 
 Application.Calculation = xlCalculationManual 
 
使用ActiveSheet.PageSetup 
如果.PrintHeadings<> False Then .PrintHeadings = False 
如果.PrintGridlines<> False Then .PrintGridlines = False 
如果.PrintComments<> xlPrintNoComments然后.PrintComments = xlPrintNoComments 
'等等
结束
 
 Application.ScreenUpdating = origScreenUpdating 
 Application.Calculation = origCalcMode 
  

编辑：几个更新：

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




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

希望这有帮助...

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

解决方案

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

这篇关于如何在Excel VBA中打印更快？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！