31编写高效Excel-VBA代码的最佳实践

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