如何优化VBA代码并使程序尽可能快的运行.docx

1、如何优化VBA代码并使程序尽可能快的运行如何优化VBA代码并使程序尽可能快的运行如何优化VBA代码并使程序尽可能快的运行速度问题一直是VBA程序值得关注的一个方面。当您编写了一个对大量数据进行操作的程序后，在运行时可能会发现程序运行得很慢，有的甚至达好几分钟，就像Excel应用程序已崩溃了一样。但当您发现程序确实在运行，并得到正确的结果后，您可能就会想到如何使程序更快的运行了。当然，代码运行速度慢可能是VBA程序的一个缺点，这也可能是程序语言本身的一个原因，但我们也可以找到一些方法优化VBA代码并使程序尽可能快的运行。下面是我整理的一些优化VBA代码或提高程序运行速度的方法，有些方法是我们在编

2、程中好的做法和应该养成的好习惯，希望能带给您一些有益的参考。高质量的程序和运行效率是需要不断实践，并在实践中不断总结和积累经验的，也希望您能将在编写程序过程中发现的一些优化方法介绍给大家共享。1、尽量简化代码通过简化代码，可以提高程序的性能。您可以将通用过程编写为子过程来调用。例如，假设有一个应用程序需要在不同的地方实现查找一定范围内的某个特殊条目，在一个没有简化代码的应用程序中，不同的过程可能需要应用各自的算法以实现在某个范围内查找某一条目，修改每个过程使其采用一个更有效的算法并不是一件很容易的事。而一个简化的程序则只有一个查找算法，即将该查找算法编写成通用的子程序，需要查找某个范围的过程都

3、调用该子程序，通过在查找方法的子程序中优化查找算法，使得调用该方法的所有过程都享受性能提高所带来的好处。另外，删除所有无关的代码，这在所录制宏中表现得尤为明显。在录制宏时，经常会产生一些与所实现的功能无关的代码，您可以将这些代码删除，以使得代码得以简化。在下面将要讲到的设置对象变量代替长对象引用，使用WithEnd With语句、执行For EachNext循环语句，根据程序环境尽量减少OLE引用，等等，均是简化代码的好方法。2、强制声明变量在VBE编辑器中的菜单“工具选项”对话框中“编辑器”选项卡中，您应该始终保持“要求变量声明”复选框被选中，这样将在模块代码顶部出现Option Expli

4、cit语句，要求您在编写代码时对所有出现的变量均进行声明，这样，在使用变量时减少内存需求并加速性能。(1)要节省内存资源，必须始终用特定的数据类型声明所有变量。如果不使用特定的数据类型声明变量，VBA会创建Variant类型的变量，这将比任何其他数据类型要求更多的内存。(2)清楚每种数据类型需要多少内存以及它可以存储的值的范围。除使用较小的数据类型会导致隐性转换的情况外，应始终使用尽可能小的数据类型。例如，因为Integer类型的变量将被转换成Long类型的变量，应该将那些存储整型值的变量声明为Long类型，而不是Integer类型。(3)除非确实需要，应避免使用浮点数据类型。尽管Curren

5、cy数据类型更大，但它比 Single 数据类型快，因为Currency数据类型不使用浮点处理器。(4)如果在一个过程中多次引用一个对象，可以创建对象变量，并将对给对象的引用指派给它。因为对象变量存储对象在内存中的位置，VBA将不必再次查找其位置。(5)将对象变量声明为特定的类型(不是Object类型)，以便利用早期绑定。3、减少变量的作用范围并及时释放变量主要是对象变量，在其使用完后，及时释放。例如，Dim TempObj As AnyObject,AnObj As AnyObjectSet TempObj=New AnyObjectSet AnObj=TempObjSet TempObj=

6、Nothing 释放对象变量4、尽可能使用早期绑定绑定是指将程序调用与实际代码相匹配。为了实现早期绑定，先应创建对对象库的引用。早期绑定可以在代码中使用定义在对象库中的常量，可以自动列出对象的方法和属性，但早期绑定只有在所控制的对象拥有独立的类型库或对象库文件才适用且还需要已安装了特定的库。而后期绑定则只是在运行时才知道对象的类型并对对象进行引用，因此不具备上述特点。使用早期绑定创建对象通常更有效率，使代码能获得更好的性能。因为对象的早期绑定引用在编译时可以通过VBE的解析，而不是通过运行时模块解析，因此早期绑定的性能要好得多。虽然在程序设计时不可能总是使用早期绑定，但应该尽可能使用它。5、关

7、闭屏幕刷新在Excel中，其ScreenUpdating属性值的默认值为True，这样当写数据到工作表或者执行任何导致其显示属性变化的动作时，Excel的屏幕界面将会不断的刷新，不仅影响显示，而且影响程序运行的速度。您可以在进入主程序运行前将屏幕刷新属性关闭，即用Application.ScreenUpdating = False语句关闭屏幕刷新，这样将大大改善程序的运行速度。但在程序运行完成前，要确保将其恢复为原来的设置，即将ScreenUpdating属性的值设置为True。因为您对该属性的修改是永久性的修改，Excel不会为您自动恢复其默认值，您必须用语句Application.Scre

8、enUpdating = True恢复设置。6、设置计算模式为手动如果您的工作表中含有多个公式，在每次单元格中的值发生变化时，公式都将会重新计算，这会影响程序运行速度。您可以在进入主程序运行前，将计算模式设置为手动，即使用如下语句Application.Calculation = xlCalculationManual，以避免不必要的计算。当程序运行结束前，您要恢复Excel的默认计算模式设置，即设置为自动重算，可使用下面的语句Application.Calculation = xlCalculationAutomatic，这同ScreenUpdating属性一样，Excel不会自动恢复其为默

9、认值。Calculation属性是对所有工作簿进行的设置，您也可以用工作表的EnableCalculation属性来设置对某个工作表是否进行重新计算。7、使用For EachNext循环可以使用For EachNext循环来保证程序代码更快地执行。在使用For EachNext循环时，对于存储在集合或数组中的每个对象执行一组语句，程序更简洁，也更容易阅读、调试和维护。当For EachNext语句迭代集合时，自动指定一个对集合当前成员的引用，然后在到达集合的尾部时跳出循环语句。8、使用WithEnd With语句可以使用WithEnd With语句来尽量减少对象引用。使用With语句对指定的对

10、象完成一系列的任务，而不用重复引用对象。也可以使用嵌套的With语句进一步提高程序代码的效率。例如，下面的使用WithEnd With语句是在同一个单元格中执行多个操作。With Workbooks(“Book1.xls”).Worksheets(“Sheet1”).Range(“A1”) .Formula=”=SQRT(20)” With .Font .Name=”Arial” .Bold=True .Size=10 End WithEnd With同理，可使用WithEnd With语句在同一个单元格区域中执行多个操作。9、在执行循环时考虑如何能够尽可能地节省资源(1)分析循环以查看是否正

11、在不必要地执行一些消耗内存的重复操作。例如，是否可以在循环外(而不是在循环中)设置某些变量？每次都通过循环执行的转换过程是否可以在循环之外执行？(2)考虑是否必须在满足特定的条件时才执行循环。如果是，也许可以更早地退出循环。例如，假设正在对一个不应该包含数字字符的字符串进行数据验证。如果循环要检查字符串中的每个字符以确定其中是否包含数字字符，那么您可以在找到第一个数字字符时立即退出循环。(3)如果必须在循环中引用数组的元素，可以创建一个临时变量存储该元素的值，而不是引用数组中的值。从数组中检索值比从相同类型的变量读取值要慢。10、尽量减少OLE引用可以通过尽量减少在VBA程序代码中使用OLE(

12、对象链接与嵌入自动识别)引用来优化程序代码。VBA语句中所调用的方法和属性越多，执行语句所用的时间就越多。例如下面的两个语句：语句1：Workbooks(1).Sheets(1).Range(“A1”).value=/10语句2：ActiveWindow.Left=200执行时，语句2比语句1快。同样，上面所讲的对重复使用的对象引用指定一个变量，通过调用变量从而保证避免多次进行对象引用。11、避免对象激活或者不需要先进行先择在使用宏录制器时，所生成的程序代码在应用任何方法或属性之前都会激活或者选择对象。但是，并不是在所有的情况下都需要这样做。所以，在您编写VBA程序代码时，不需要在对对象执行任

13、何任务之前都激活或者选择每个对象。例如，在Excel中，我们如果要使第一行变成粗体就必须先选项中它。但在VBA中(除在图表操作时需要选中图表对象外)，很少需要这样做，即VBA可以在不选中第一行的情况下，将它变成粗体。宏录制器的代码：Rows(1:1).SelectSelection.Font.Bold = True改编后的代码为：Row(“1:1”).Font.Bold=True这样做还可以使程序代码更简洁，并且程序可以运行得更快。12、在一个语句中进行复制或者粘贴在用宏录制代码时，首先是选择一个区域，然后再执行ActiveSheet.Paste。在使用Copy方法时，可以在一个语句中指定复制

14、的内容及要复制到的目的地。例如，将B5:C6区域的内容复制到以单元格B8开始的区域中，使用宏录制器的代码为：Range(B5:C6).SelectSelection.CopyRange(B8).SelectActiveSheet.Paste经修改后的最佳代码是：Range(B5:C6).Copy Destination:=Range(B8)13、尽可能少使用“.”在前面已经介绍过的对长对象引用使用对象变量以及使用WithEnd With等都是简化”.”的方法。因为在代码中的每个句点都表示至少一个(而且可能是多个)过程调用，而这些过程调用必须在后台执行。真正好的做法是在局部进行缓存对象引用，例如

15、，应该把对象模型中较高层次的对象引用保存到局部对象变量中，然后用这些对象引用创建其他较低层次的对象引用。例如，引用某单元格数据时，可用如下代码：Dim i As LongFor i=1 to 10 Workbooks(“Book1.xls”).Worksheets(“Sheet1”).Cells(1,i).Value=iNext i但下面的代码运行效率更高，因为代码中引用Workbook对象和Worksheet对象的调用命令只执行一次，而上面的代码中却要执行10次。Dim ws As WorksheetDim i As LongSet ws= Workbooks(“Book1.xls”).Wo

16、rksheets(“Sheet1”)For i=1 to 10 ws.Cells(1,i).Value=iNext i14、合理地使用消息框和窗体在一个很长的程序中，偿试着将消息框或者窗体安排显示在程序的最开始或最后面，避免干扰用户。此外，尽管窗体提供了许多功能，但它们能够导致文件大小迅速增加。还有就是尽量避免给工作表单元格链接用户窗体控件，因为这样将会导致链接更新操作，影响程序运行速度。15、尽可能加速对数字的运算(1)当对整数进行除法时，您可以使用整型除法运算符()而不是浮点除法运算符(/)，因为无论参与除法运算的数值类型如何，浮点除法运算符总会返回Double类型的值。 (2)在任何具有

17、整数值的算术表达式中使用Single或Double值时，整数均将被转换成Single或Double值，最后的结果将是Single或Double值。如果要对作为算术运算结果的数字执行多次操作，可能需要明确地将该数字转换为较小的数据类型。16、提高字符串操作的性能(1)尽可能少使用连接操作。可以在等号左边使用Mid函数替换字符串中的字符，而不是将它们连接在一起。使用 Mid 函数的缺点是替换字符串必须与要替换的子字符串的长度相同。例如，Dim strText As StringstrText = this is a testMid(strText, 11, 4) = tent(2)VBA提供许多可

18、用来替换函数调用的内部字符串常量。例如，可以使用vbCrLf常量来表示字符串中的回车/换行组合，而不是使用Chr(13) & Chr(10)。(3)字符串比较操作的执行速度很慢。有时，可以通过将字符串中的字符转换为 ANSI 值来避免这些操作。例如，下列代码会检查字符串中的第一个字符是否为空格：If Asc(strText) = 32 Then上面的代码会比以下代码更快：If Left(strText, 1) = Then17、使用Asc()检验ANSI的值在VBA中，可以使用Chr$()函数把数转换成字符，并确定ANSI的值，但是更好的是使用Asc()函数把字符串转换成数值，然后确定它的AN

19、SI值。如果需要进行有限次数的这种检验，对程序代码的效率可能不会产生很大影响，但是，如果需要在多个循环内进行这种检验时，这将节省处理时间并且有助于程序代码更快地执行。18、使用Len()检验空串尽管有多种方法可检验空串，但首选的是使用Len()函数。为了测试零长度的串，可以选择把串与”相比较，或者比较串的长度是否为0，但这些方法比用Len()函数要用更多的执行时间。当对字符串应用Len()函数并且函数返回0值时，说明该字符串是空的或者是零长度的字符串。并且，因为在If语句内非零值被认为是True，所以直接使用Len()函数而不必与”或0比较，减少了处理时间，因此执行更快。19、有效地使用数组用

20、VBA数组而不是单元格区域来处理数据，即可以先将数据写入到某个数组，然后用一个语句就可以将数组中的数据传递到单元格区域中。在创建已知元素的确定数组时，使用Array函数对于节约空间和时间以及写出更具效率的代码是非常理想的。例如，Dim Names As VariantNames=Array(“Fan”,“Yang”,“Wu”,“Shen”)此外，应该尽量使用固定大小的数组。如果确实选择使用了动态数组，应该避免数组每增加一个元素就改变一次数组的大小，最好是每次增加一定数量的元素。20、使用Excel的内置函数对于要实现的某一功能，如果有Excel的内置函数能够实现，那么就用Excel的内置函数，

21、不需要另外自定义函数，因为自定义的函数总比Excel内置的函数慢。使VBA代码更快且更简洁的方法前言本文是对如何优化代码并使VBA程序尽可能快的运行一文的补充，您在阅读本文时，可对照参考。在本文中，列举了大量的代码和示例。但在本文中所讨论的代码并不是本文的中心内容，它们只是用作测试代码运行速度，以说明本文的相关内容。因此，您可以将本文中的代码粘贴或输入到您的工作簿中进行测试，当然您也可以下载本文的附件优化代码示例.xls进行调试。注意，您所运行的计算机的环境和配置不同，速度也会有所差异。同样，您要得出准确的结果，也需要对代码进行多次的运行和进行最终平均速度的比较。有必要对代码进行优化吗？这可能

22、不是绝对必要的，但依赖于您要做的工作如果您正好编写了一个快速且简短的或者是一次性使用且与速度和/或简洁要求无关的代码，您就不需要优化代码。但另一方面，如果您处理一个带有很多数据、工作簿、工作表等大的工程，再次检查您第一次编写好的代码，看看是否您的代码需要优化，而这样做总是值得的。最终，您将养成编写代码的好习惯，将会使您的代码更简洁、运行更快速、并且容易为您自已和他人阅读和调试。同时，由于您的代码简洁，因而输入更快，工作效率更高。减少OLE引用调用每个VBA方法或属性都需要一个或多个OLE引用，这样在代码中会有多个点运算符，而每次代码调用都需要对这些点运算符进行解析，这将花费更多的时间。因此，在

23、调用方法或属性时减少引用长度将是使您的程序运行更快的一种好方法。例如，下面的代码包含有三个点运算符，因此Workbooks(1)需要调用三次属性。Workbooks(1).Sheets(1).Range(c5).Value = 10而下面的代码包含有一个点运算符，这意味着ActiveWindow仅需调用一次属性。ActiveWindow.Left = 200在接下来所讲述的内容中有些示例证实了减少点运算符的数量能创建更快运行速度的代码。使用对象变量当您一遍又一遍的使用相同对象引用时，您可以将该对象引用设置成一个变量，然后使用该变量代替对象引用。这样，您在代码中只需对该对象变量进行引用即可。例如

24、，下面的示例在每行中调用Workbook对象的Sheets属性、Range属性和Value属性三次，当您循环1000次时，总共要调用属性6000次。Sub DoThis1() Dim Start As Double, Finish As Double Start = Timer - Dim N As Long For N = 1 To 1000 Workbooks(Book1).Sheets(1).Range(c5).Value = 10 Workbooks(Book1).Sheets(1).Range(d10).Value = 12 Next - Finish = Timer MsgBox

25、本次运行的时间是 & Finish - StartEnd Sub您能在循环开始前通过设置Workbooks(“Book1”).Sheets(1)作为一个对象变量来优化上面的例子，下面的示例在每行仅调用一个Range属性，当循环1000次时，总共只调用该属性2000次。注意，“Value”是一个缺省属性，通常不需要明确指定它，它将被自动调用。因此，该属性在下面的代码中被忽略。然而，就养成良好的编程习惯而言，还是建议您最好写明该属性。Sub DoThis2() 快约35%以上 Dim Start As Double, Finish As Double Start = Timer - Dim Thi

26、sBookSheet As Object, N As Long Set ThisBookSheet = Workbooks(Book1).Sheets(1) For N = 1 To 1000 ThisBookSheet.Range(c5) = 10 ThisBookSheet.Range(d10) = 12 Next - Finish = Timer MsgBox 本次运行的时间是 & Finish - StartEnd Sub您可以比较这两个示例的运行速度，它们都得到同样的结果，但在我的机子上运行时，第二个示例比第一个快60%。当然，您还能使用WithEnd With语句获得相同的结果。使

27、用WithEnd With语句您也能不设置明确的对象变量，而是使用With语句减少对象的重复引用。上面的示例也能使用下面的代码，该代码仅调用Workbooks属性和Sheets属性一次，当循环1000次时，总共调用1000次属性。Sub DoThis3() 快约35%以上 Dim Start As Double, Finish As Double Start = Timer - Dim N As Long With Workbooks(Book1).Sheets(1) For N = 1 To 1000 .Range(c5) = 10 .Range(d10) = 12 Next End Wit

28、h - Finish = Timer MsgBox 本次运行的时间是 & Finish - StartEnd Sub上述三个示例均得到相同的结果，但在我的机子上运行时，本示例比第一个示例快50%以上。使用For EachNext循环与使用计数进行循环相比，在遍历集合或数组时使用For EachNext循环将更快。在多数情况下，使用For EachNext循环也更方便，并且使您的宏更简洁、更容易阅读和调试。下面的示例运行很慢，因为在每次循环重复时它设置并调用了行变量.Row(i)。Sub DoSomethingSlow() Dim Start As Double, Finish As Doubl

29、e Start = Timer - Dim Cell As Range, i As Long With Sheet1.Range(A1:A10000) For i = 1 To 10000 Set Cell = .Rows(i) If Cell 0 Then Cell.Font.ColorIndex = 5 End If Next End With - Finish = Timer MsgBox 本次运行的时间是 & Finish - StartEnd Sub下面的示例代码更简洁，其运行速度大约是上面代码的23倍。因为For EachNext循环自动记录行数并定位，而不需要调用变量i。Sub DoSomethingFaster() 快两至三倍 Dim Start As Double, Finish As Double Start = Timer - Dim Cell As Range With Sheet1 For Each Cell In .Range(A1:A10000) If Cell 0 Then Cell.Font.ColorIndex = 5 End If Next End With - Finish = Timer MsgBo