《编写高效Excel_VBA代码的最佳实践》由会员分享，可在线阅读，更多相关《编写高效Excel_VBA代码的最佳实践（32页珍藏版）》请在金锄头文库上搜索。
1、很多 Excel VBA 文章和图书都介绍过如何优化 VBA 代码，使代码运行得更快。下面搜集了一些使 Excel VBA 代码运行更快的技术和技巧，基本上都是实践经验的总结。如果您还有其它优化 Excel VBA 代码的方法，可以在本文后留言或给出链接，与大家分享。对于应用程序属性，在代码运行时关闭除必需属性以外的其它所有属性对于应用程序属性，在代码运行时关闭除必需属性以外的其它所有属性在代码运行时关闭不需要的 Excel 功能。其原因是，如果通过 VBA 更新不同的单元格区域，或者从不同的单元格区域复制 /粘贴来创建汇总表，则不希望 Excel 浪费时间和资源来重新计算公式、显示粘贴进度或
2、者重绘网格，尤其在每次单独的操作后（更有甚者，如果代码使用了循环，则每次单独操作后 Excel 都会在后台运行这些功能）。只需要在代码执行结束时进行一次重新计算和重绘就足以使工作簿更新。下面的代码将帮助您提高代码的执行速度。（1）放置在主代码前的一段代码，获取 Excel 当前的属性状态，然后将其关闭获得当前的 Excel 设置状态,将其放置在代码的开头screenUpdateState = Application.ScreenUpdatingstatusBarState = Application.DisplayStatusBarcalcState = Application.Calcula
3、tioneventsState = Application.EnableEventsdisplayPageBreakState = ActiveSheet.DisplayPageBreaks 注:这是工作表级的设置关闭一些 Excel 功能使代码运行更快Application.ScreenUpdating = FalseApplication.DisplayStatusBar = FalseApplication.Calculation = xlCalculationManualApplication.EnableEvents = FalseActiveSheet.DisplayPageBre
4、aks = False 注:这是工作表级的 设置（2）放置在主代码结束后的一段代码，用来将 Excel 恢复到代码运行前的设置代码运行后,恢复 Excel 原来的状态;将下面的代码放在代码的末尾Application.ScreenUpdating = screenUpdateStateApplication.DisplayStatusBar = statusBarStateApplication.Calculation = calcStateApplication.EnableEvents = eventsStateActiveSheet.DisplayPageBreaks = display
5、PageBreaksState 注:这是工作表级的设置下面简要解释这些设置：Application.ScreenUpdating：将该属性设置为 False，告诉 Excel 不要重绘屏幕。其优点是不需要 Excel 花费资源来绘制屏幕，因而其改变会更快而不致让用户察觉其变化。因为如此频繁地绘制屏幕需要大量的资源，所以关闭绘制屏幕直到代码执行结束。在代码结束前，确保重新开启了该属性。Application.DisplayStatusBar：将该属性设置为 False，告诉 Excel 停止显示状态栏。例如，如果使用 VBA 复制/粘贴单元格，当粘贴执行时 Excel 将在状态栏中显示操作的进度
6、。关闭屏幕更新不会关闭状态栏显示，因此，如果需要的话，可以禁用屏幕更新而仍然可以通过状态栏给用户提供反馈。记住，如果将该属性设置为 False，在代码结束前应该将其设置为 True。Application.Calculation：该属性允许编程设置 Excel 的计算模式。“手工的”（xlCalculationManual）模式意味着 Excel 等待用户（或代码）来触发计算；默认为“自动的”（xlCalculationAutomatic）模式，意味着由 Excel 来决定何时重新计算工作簿（例如，当在工作表中输入新公式时）。由于重新计算工作簿将花费时间且浪费资源，因此可能不希望每次改变单元格
7、值时 Excel 都触发重新计算。当代码执行时关闭重新计算，在代码结束前再设置回重新计算模式。Application.EnableEvents：将该属性设置为 False，告诉 Excel 不要触发事件。你可能不希望 Excel 为每个正在通过代码发生改变的单元格触发事件，关闭事件将加速 VBA 代码的执行。ActiveSheet.DisplayPageBreaks：当在较新版本的 Excel 中运行 VBA 时，则可能比在早期版本的 Excel 中需要更长的时间完成。例如，需要几秒钟在早期版本的 Excel 中完成的宏可能需要几分钟才能在更高版本的 Excel 中完成。或者，第二次运行一个宏
8、可能比第一次运行需要的时间更长。这是由于 VBA 宏修改了多行或列的属性，或者必须强制执行计算Excel 分页符。如果宏设置了任何 PageSetup 属性或者手动设置了 PageSetup 属性，接着运行较大区域的行或列属性设置时会出现这样的问题。您可以将该属性设置为 False 来提高代码的运行速度。当然，在代码运行结束前，应将该属性恢复为原设置。在单个操作中读在单个操作中读/写大块的单元格区域写大块的单元格区域本技巧用于优化在 Excel 和代码之间转换数据的次数。使用数组变量存储所需要的值并执行取值或赋值操作，而不是一次遍历单个单元格并获取或设置单个值。例如，下面的代码在单元格区域 A
9、1：C10000 中放置随机数。代码段一：运行速度较慢的代码Sub testSlow()Dim DataRange As RangeDim Irow As LongDim Icol As IntegerDim MyVar As DoubleSet DataRange = Range(“A1:C10000“)For Irow = 1 To 10000For Icol = 1 To 3MyVar = DataRange(Irow, Icol) 从 Excel 单元格中读取值 30K 次If MyVar 0 ThenMyVar = MyVar * MyVar 改变值DataRange(Irow, I
10、col) = MyVar 将值写入 Excel 单元格中 30000 次End IfNext IcolNext IrowEnd Sub代码段二：运行速度更快的代码Sub testFast()Dim DataRange As VariantDim Irow As LongDim Icol As IntegerDim MyVar As DoubleDataRange = Range(“A1:C10000“).Value 一次从 Excel 单元格 中读取所有的值,将其放入数组For Irow = 1 To 10000For Icol = 1 To 3MyVar = DataRange(Irow,
11、Icol)If MyVar 0 ThenMyVar = MyVar * MyVar 改变数组中的值DataRange(Irow, Icol) = MyVarEnd IfNext IcolNext IrowRange(“A1:C10000“).Value = DataRange 一次将所有结果写回单 元格End Sub避免选取避免选取/激活对象激活对象使用选取的方法更新单元格区域是最慢的。在试验了使用 Range 对象、使用 Variant 类型和使用 Select 方法对一个大的单元格区域读写数据的操作后，Select 方法是最慢的。再来看一个例子：在工作表中有 40 个形状，在每个形状中写入
12、“Hello”。使用 Select 方法的代码为：Sub testSlow()Dim i As IntegerFor i = 0 To ActiveSheet.Shapes.CountActiveSheet.Shapes(i).SelectSelection.Text = “Hello“Next iEnd Sub运行速度更快的方法是完全避免使用选取并直接引用形状：Sub testFast()Dim i As IntegerFor i = 0 To ActiveSheet.Shapes.CountActiveSheet.Shapes(i).TextEffect.Text = “Hello“Nex
13、t iEnd Sub在使用宏录制器时，所生成的程序代码在应用任何方法或属性之前都会激活或者选择对象。但是，并不是在所有的情况下都需要这样做。所以，在您编写 VBA 程序代码时，不需要在对对象执行任何任务之前都激活或者选择每个对象。例如，在 Excel 中，我们如果要使第一行变成粗体就必须先选项中它。但在 VBA 中(除在图表操作时需要选中图表对象外)，很少需要这样做，即 VBA 可以在不选中第一行的情况下，将它变成粗体。宏录制器的代码：Rows(“1:1“).SelectSelection.Font.Bold = True改编后的代码为：Row(“1:1”).Font.Bold=True这样做
14、还可以使程序代码更简洁，并且程序可以运行得更快。工作簿设计工作簿设计好的工作簿设计和数据组织有助于编写运行良好的代码。良好设计的工作簿，其执行效率和维护量将大大优化。可以说，工作簿设计是从大的宏观方面进行优化，而对代码的优化只是一些微观的细节上的优化。其他其他尽量简化代码通过简化代码，可以提高程序的性能。您可以将通用过程编写为子过程来调用。例如，假设有一个应用程序需要在不同的地方实现查找一定范围内的某个特殊条目，在一个没有简化代码的应用程序中，不同的过程可能需要应用各自的算法以实现在某个范围内查找某一条目，修改每个过程使其采用一个更有效的算法并不是一件很容易的事。而一个简化的程序则只有一个查找
15、算法，即将该查找算法编写成通用的子程序，需要查找某个范围的过程都调用该子程序，通过在查找方法的子程序中优化查找算法，使得调用该方法的所有过程都享受性能提高所带来的好处。另外，删除所有无关的代码，这在所录制宏中表现得尤为明显。在录制宏时，经常会产生一些与所实现的功能无关的代码，您可以将这些代码删除，以使得代码得以简化。宏录制器生成无效代码的一个原因是它不知道在对话框中您选择了哪些选项，因此，当您关闭对话框时它将直接记录所有可用的选项。例如，选择单元格区域G2:G20，然后在单元格格式对话框中改变字体样式为粗体，使用宏录制器生成的代码如下： Sub NowThis1()Dim Start As D
16、ouble, Finish As DoubleStart = Timer-为了进行测试,将循环 100 次Dim N As LongFor N = 1 To 100*Range(“G2:G20“).SelectWith Selection.Font.Name = “Arial“.FontStyle = “Bold“.Size = 10.Strikethrough = False.Superscript = False.Subscript = False.OutlineFont = False.Shadow = False.Underline = xlNone.ColorIndex = xlAutomaticEnd With*Next-
