优化EXCEL中VBA编码性能

如何优化的Excel VBA的性能，或如果你有一个宝贵的意见或连结分享，请随时张贴在此处，以便每个人都可以从中受益。谢谢！


关闭所有的要点，但在您的代码运行

这种优化明确关闭Excel功能你不需要发生（重复以上） ，而您的代码运行。请注意，在下面的代码示例，我们抓住当前国家对这些特性，将其关闭，然后恢复他们在年底代码执行。

这有助于原因之一是，如果您要更新（通过的VBA ）几个不同范围的新的价值观，或者复制/粘贴几个范围，以建立一个综合的数据表，您可能不希望将Excel的考虑时间和资源来计算公式，显示粘贴的进展，甚至重划网格，特别是在每一个操作（甚至如果您的代码使用循环） 。只有重新计算和一个重绘结束时您的代码执行是足以让当前工作簿的所有更改。

下面是一些示例代码显示了如何以及如何关闭，而您的代码运行。这样做将有助于提高您的代码：

'获取当前状态的各种Excel中设置;把这个在开始您的代码

screenUpdateState = Application.ScreenUpdating

statusBarState = Application.DisplayStatusBar

calcState = Application.Calculation

eventsState = Application.EnableEvents

displayPageBreakState = ActiveSheet.DisplayPageBreaks '注意：这是一张级别设置

'关闭一些Excel功能，使您的代码的运行速度

Application.ScreenUpdating =假

Application.DisplayStatusBar =假

Application.Calculation = xlCalculationManual

Application.EnableEvents =假

ActiveSheet.DisplayPageBreaks = FALSE时，注意：这是一张级别设置

“ > ”你的代码这里“ ”

'之后，您的代码运行，恢复状态;把这个结束时您的代码

Application.ScreenUpdating = screenUpdateState

Application.DisplayStatusBar = statusBarState

Application.Calculation = calcState

Application.EnableEvents = eventsState

ActiveSheet.DisplayPageBreaks = displayPageBreaksState '注意：这是一张级别设置

这里有一个快速描述的每一个这些设置：

Application.ScreenUpdating ：此设置告诉Excel在没有重绘屏幕，而假。这里的好处是，你可能不需要Excel中使用最多的资源试图提请屏幕，因为它的变化速度超过了用户可以感知。因为它需要大量的资源，以提请屏幕如此频繁，只要关闭绘图屏幕结束之前的代码执行。一定要重新打开它之前，您的代码的目的。

Application.DisplayStatusBar ：此设置告诉Excel来停止显示地位，同时错。例如，如果您使用VBA来复制/粘贴范围，而正在完成粘贴Excel将显示这一行动的进展的状态栏。关闭屏幕更新是分开的关闭状态栏显示，让您可以禁用屏幕更新，但仍然提供反馈给用户，如果需要的话。再次，重新打开它之前，您的代码结束执行。

Application.Calculation ：此设置允许您以编程方式设置Excel的计算模式。 “手册” （ xlCalculationManual ）模式指的Excel等待用户（或您的代码） ，明确提出的计算方法。 “自动”是默认和手段的Excel决定何时重新计算工作簿（例如，当你进入一个新公式的工作表） 。由于重新计算您的工作簿可以有时间和资源密集型，您可能不想Excel中引发了recalc每次改变一个单元格的值。关闭计算，而您的代码执行，然后设置模式回来。注意：设置模式回“自动” （ xlCalculationAutomatic ）将引发recalc 。

Application.EnableEvents ：此设置告诉Excel在火灾事件，而不是虚假。虽然寻找到Excel VBA的性能问题，我了解到，有些桌面搜索工具执行事件监听（可能更好地跟踪文件的内容，因为它的变化） 。您可能不想Excel的射击的事件，每一个细胞变化通过您的代码，并关闭事件将加速您的VBA代码的性能，如果有一个COM加载听力在Excel的事件。 （感谢道詹金斯为指向了这一点，我以前的帖子） 。

ActiveSheet.DisplayPageBreaks ：一个很好的说明这个设定已经存在： http://support.microsoft.com/kb/199505 （感谢大卫McRitchie为指向了这一点） 。


读/写大块的细胞在单次操作

这种优化明确降低了多少次之间传输数据的Excel和您的代码。而不是通过细胞循环时，单次和获得或设置一个值，做同样的手术，在整个范围内的一条线，使用数组变量来存储值的需要。

为每一个代码示例下面，我已经把随机值（不是公式）将单元格A1 ： C10000 。

下面是一个缓慢的，循环的方法：

昏暗的幅度DataRange
点心Irow只要
点心Icol为整数
点心MyVar双重
集DataRange =范围（ “格A1 ： C10000 ” ）

为Irow = 1至10000
  为icol = 1至3
     MyVar = DataRange （ Irow ， Icol ） '读值从Excel网格30,000次
    如果MyVar “ 0然后
       MyVar = MyVar * Myvar '的值更改
       DataRange （ Irow ， Icol ） = MyVar '收件值回到Excel的网格30,000次
    为此，如果
  下一步Icol
下一步Irow

这是快速版的代码：

点心DataRange作为变
点心Irow只要
点心Icol作为整数
点心MyVar双重
DataRange =范围（ “格A1 ： C10000 ” ） 。价值，阅读所有的价值观一次从Excel网格，投入一个数组

为Irow = 1到10000
  为Icol = 1至3
   MyVar = DataRange （ Irow ， Icol ）
  如果MyVar “ 0然后
     MyVar = MyVar * Myvar '变化中的值数组
     DataRange （ Irow ， Icol ） = MyVar
  为此，如果
下一步Icol
下一步Irow
范围（ “格A1 ： C10000 ” ） 。价值= DataRange '写入所有结果回到一系列一次

注：我第一次知道这个概念的阅读网页，由John Walkenbach这里找到： http://www.dailydoseofexcel.com/archives/2006/12/04/writing-to-a-range-using-vba/

先前的Excel博客条目由达尼心电图也比较这两种方法，同时选择/抵消方法以及： http://blogs.msdn.com/excel/archive/2008/10/03/what-is-the-最快的方式对扫描一个大范围中excel.aspx

...这使我对我的下一个点。

避免选择/激活对象

请注意，在上面提及的博客帖子，选择方法，更新了一系列最慢。这下一优化最小的频率Excel有应对变化的选择工作簿中的最小的选择改变尽可能。

幅度为例：同样，看到的Excel博客帖子上面引述。它表明，使用的选择是最慢的3种方法的讨论，以阅读和写作的范围。

形状示例： setup ：我有40个形状一张，我要写信“你好”中的每个人。

使用较慢的“选择”的方法，代码如下所示：

我= 0 ActiveSheet.Shapes.Count

    ActiveSheet.Shapes （一） 。选择

    Selection.Text = “你好”

接下来，我

该更快的方法是完全避免的选择，并直接提及的形状：

我= 0 ActiveSheet.Shapes.Count

    ActiveSheet.Shapes （一） 。 TextEffect.Text = “你好”

接下来，我

的概念，说明了上述的例子还可以应用于物体以外范围和形状。

注：我第一次知道这个概念，在形状，通过阅读网页罗恩日布鲁因这里找到： http://www.rondebruin.nl/shape.htm

有关业绩纸

看到“性能提高在Excel 2007中”文件MSDN上： http://msdn.microsoft.com/en-us/library/aa730921.aspx

这是一个相当详尽和全面的文件，介绍了大网格，增加限制在Excel 2007中，主要侧重于Excel的计算性能和调试计算性能瓶颈。还有一个短节如何撰写更快VBA宏。

其他性能优化

虽然上述的优化，是我认为最重要的，有一些其他的“荣誉奖”优化我会简略介绍为你考虑。

考虑提高系统的性能，执行你的代码的功能，通过XLL /架C - API的。概述和辅助材料的SDK可以在这里找到： http://msdn.microsoft.com/en-us/library/bb687827.aspx 。

声明变量的明确类型，以避免间接确定的数据类型（重复，如果用在回路）在执行代码。

简单的功能使用您的代码中的高频率，执行它们自己在VBA而不使用WorksheetFunction对象。

使用Range.SpecialCells （ ）范围下降的单元格的数量您的代码需要同。

发布约瑟夫Chirilov | 9评论