编写高效ExcelVBA代码的最佳实践

1、编写高效Excel VBA代码的最佳 实践（二）编写高效Excel VBA 代码的最佳实践(二)时间：2009-06-16 18:38:44来源：网络 作者：未知 点击:0次尽可能少使用“ ，”使用对象变量在前面已经介绍过的对长对象引用使用对象 变量以及使用WithEnd With 等都是简 化” 的方法。因为在代码中的每个句点都表示 至少一个(而且可能是多个)过程调用”而这些过 程调用必?尽可能少使用“.”使用对象变量在前面已经介绍过的对长对象引用使用对象变量以及使用WithEn d With 等都是简化” .的方法。因为在代码中的每个句点都表示至少一个(而且可能是多个)过程调用，而这些过程

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

3、 ws= Workbooks(Book1.xls).Worksheets(Sheet1)For i=1 to 10ws.Cells(1,i).Value=iNext i当您一遍又一遍的使用相同对象引用时，您可以将该对象引用设置成一个变量，然后使用该变量 代替对象引用。这样，您在代码中只需对该对象变量进行引用即可。例如，下面的示例在每行中调用Workbook 对象的Sheets 属性、Range 属性和Value 属Sub DoThis1()Dim Start As Double , Finish As DoubleStart = TimerDim N As LongFor N = 1 To 1

4、000Workbooks(Book1).Sheets(1).Range(c5).Value = 10Workbooks(Book1).Sheets(1).Range(d10).Value = 12NextFinish = TimerMsgBox 本次运行的时间是& Finish - StartEnd Sub您能在循环开始前通过设置Workbooks( “Book1”).Sheets(1)作为一个对象变量来优化上面的例子，下面的示例在每行仅调用一个Range 属性，当循环1000 次时，总共只调用该属性2000 次。注意，“Value”是一个缺省属性，通常不需要明确指定它，它将被自动调用。因此，

5、该属性在下 面的代码中被忽略。然而，就养成良好的编程习惯而言，还是建议您最好写明该属性。Sub DoThis2()快约35%以上Dim Start As Double , Finish As DoubleStart = TimerDim ThisBookSheet As Object , N As LongSet ThisBookSheet = Workbooks(Book1).Sheets(1) For N = 1 To 1000ThisBookSheet.Range(c5) = 10ThisBookSheet.Range(d10) = 12NextFinish = TimerMsgBox

6、本次运行的时间是& Finish - StartEnd Sub您可以比较这两个示例的运行速度，它们都得到同样的结果，但在我的机子上运行时，第二个示 例比第一个快60% 。当然，您还能使用WithEnd With 语句获得相同的结果。您也能不设置明确的对象变量，而是使用With 语句减少对象的重复引用。上面的示例也能使用下面的代码，该代码仅调用 Workbooks 属性和Sheets 属性一次，当循环1000 次时，总共 调用1000 次属性。Sub DoThis3()快约35%以上Dim Start As Double , Finish As DoubleStart = TimerDim N

7、As LongWith Workbooks(Book1).SheetsFor N = 1 To 1000.Range(c5) = 10.Range(d10) = 12NextEnd WithFinish = TimerMsgBox 本次运行的时间是& Finish - StartEnd Sub上述三个示例均得到相同的结果，但在我的机子上运行时，本示例比第一个示例快50% 以上在一个语句中进行复制或者粘贴在用宏录制代码时，首先是选择一个区域，然后再执行ActiveSheet.Paste。在使用Copy方法时，可以在一个语句中指定复制的内容及要复制到的目的地。例如，将B5:C6 区域的内容复制到以

8、单元格B8开始的区域中，使用宏录制器的代码为：Range(B5:C6). SelectSelection.CopyRange(B8). SelectActiveSheet.Paste经修改后的最佳代码是:Range(B5:C6).Copy Destination:=Range(B8)合理地使用消息框和窗体在一个很长的程序中，尝试着将消息框或者窗体安排显示在程序的最开始或最后面，避免干扰用 户。此外，尽管窗体提供了许多功能，但它们能够导致文件大小迅速增加。还有就是尽量避免给 工作表单元格链接用户窗体控件，因为这样将会导致链接更新操作，影响程序运行速度。尽可能加速对数字的运算(1) 当对整数进行除

9、法时，您可以使用整型除法运算符()而不是浮点除法运算符(/)，因为无论参与除法运算的数值类型如何，浮点除法运算符总会返回Double 类型的值。(2) 在任何具有整数值的算术表达式中使用Single 或Double 值时，整数均将被转换成Single或Double值，最后的结果将是Single 或Double 值。如果要对作为算术运算结果的数字执行多次操作，可能需要明确地将该数字转换为较小的数据类型。提高字符串操作的性能(1)尽可能少使用连接操作。可以在等号左边使用Mid函数替换字符串中的字符，而不是将它们连接在一起。使用Mid函数的缺点是替换字符串必须与要替换的子字符串的长度相同。例如，Di

10、m strText As StringstrText = thisis a testMid(strText, 11,4) = tent(2) VBA提供许多可用来替换函数调用的内部字符串常量。例如，可以使用vbCrLf 常量来表示字符串中的回车/换行组合，而不是使用Chr(13) & Chr(10)。(3) 字符串比较操作的执行速度很慢。有时，可以通过将字符串中的字符转换为ANSI值来避免这些操作。例如，下列代码会检查字符串中的第一个字符是否为空格：If Asc(strText) = 32Then上面的代码会比以下代码更快：If Left(strText, 1) = Then使用Asc()检验

11、ANSI的值在VBA中，可以使用Chr$()函数把数转换成字符，并确定ANSI的值，但是更好的是使用Asc()函数把字符串转换成数值，然后确定它的 ANSI值。如果需要进行有限次数的这种检验， 对程序代码的效率可能不会产生很大影响，但是，如果需要在多个循环内进行这种检验时，这将 节省处理时间并且有助于程序代码更快地执行。使用Len()检验空串尽管有多种方法可检验空串，但首选的是使用Len()函数。为了测试零长度的串，可以选择把串与”相比较，或者比较串的长度是否为0，但这些方法比用Len()函数要用更多的执行时间。当对字符串应用Len()函数并且函数返回0值时，说明该字符串是空的或者是零长度的字

12、符串。并且，因为在If语句内非零值被认为是True，所以直接使用Len()函数而不必与”或0比较，减少了处理时间，因此执行更快。有效地使用数组用VBA数组而不是单元格区域来处理数据，即可以先将数据写入到某个数组，然后用一个语句 就可以将数组中的数据传递到单元格区域中。(前文已述)在创建已知元素的确定数组时，使用Array 函数对于节约空间和时间以及写岀更具效率的代码是非常理想的。例如,Dim Names As VariantNames=Array(Fan,Yang,Wu,Shen)此外，应该尽量使用固定大小的数组。如果确实选择使用了动态数组，应该避免数组每增加一个 元素就改变一次数组的大小，最

13、好是每次增加一定数量的元素。使用Excel的内置函数对于要实现的某一功能，如果有 Excel的内置函数能够实现，那么就用Excel的内置函数，不需要另外自定义函数，因为自定义的函数总比Excel内置的函数慢。考虑在VBA代码中使用工作表函数操作单元格区域的 Excel工作表函数通常比完成同样任务的VBA程序更快(但不能确保总是这样，您可以对它们进行速度测试)例如，在代码中使用 SUM工作表函数比用 VBA代码在单元格区域中循环并相加值要快得多， 以此为例，下面的代码运行速度相对较慢。Sub AddltSlow()Dim Start As Double , Finish As DoubleSta

14、rt = Timer为了进行测试，我们循环5次Dim N As LongFor N = 1 To 5I*Dim Cell As RangeFor Each Cell In Worksheets(2).Range(A1:G200)a1 = a1 + Cell.ValueNext CellI*Next NFinish = TimerMsgBox 本次运行的时间是& Finish - StartEnd SubSub AddltFaster()快近600倍Dim Start As Double , Finish As DoubleStart = Timer为了进行测试，我们循环5次Dim N As L

15、ongFor N = 1 To 5I*a1 = Application.WorksheetFunction. _Sum(Worksheets(2).Range(A1:G200)I*NextFinish = TimerMsgBox 本次运行的时间是& Finish - StartEnd Sub产生统计结果的函数（例如PRODUCT、COUNT、COUNTA 和COUNTIF） 是代替运行速度 更慢的VBA代码的很好的选择， 并且，一些工作表函数（例如MATCH 和LOOKUP）能够将单 元格区域作为参数。不要认为工作表函数总是更快的如下例所示，在 VBA中没有Max或Min函数，但Excel中有

16、该函数。于是，您能编写出如 下代码：Sub MaxIt1()Dim Start As Double , Finish As DoubleStart = Timer为了测试，我们循环10000次Dim N As LongFor N = 1 To 10000 *J1 = Application.Max(J2, J3)I*Next NFinish = TimerMsgBox 本次运行时间是& Finish - StartEnd Sub或者，您能在VBA中使用下面的方式实现相同的功能:Sub Maxlt2()Dim Start As Double , Finish As DoubleStart = T

17、imer为了测试，我们循环10000次Dim N As LongFor N = 1 To 10000I*If J2 = J3Then J1 = J2Else J1 = J3I*Next NFinish = TimerMsgBox 本次运行的时间是& Finish - StartEnd Sub比较上面的两个程序，可能认为使用工作表函数会更快，但事实上用VBA代码可以获得几乎相同的速度。因此，在一些大的循环中，您可以对实现同样功能的工作表函数的VBA代码进行测试。一些内置的 VBA函数事实上运行速度也是慢的，因此，在编写代码时，在不同方式之间进 行速度测试总是值得的。但是，在代码中经常使用的简单的

18、函数，就直接编写代码，而不是使用WorksheetFunction对象。使用Range.SpecialCells()来缩小需要处理的单元格数。只要有可能就使用集合索引值您能在集合中使用名称或者数字来指定某个单一的对象，但使用对象的索引值通常是更快的。如 果您使用对象的名字，VBA必须解析名字成为索引值；但如果您使用索引值，就能避免这个额 外的步骤。但另一方面，我们要注意到在集合中通过名称指定对象有很多优点。使用对象名称能使您的代码 更容易阅读和调试。此外，通过名称指定一个对象比通过索引值更安全，因为当您的代码运行时该对象的索引值可能变化。例如，某菜单的索引值表示它在菜单栏中的位置，但是如果在菜

19、单栏中添加了菜单或者删除了菜 单，该菜单的索引值会变化。这样，您就不应该考虑代码的速度，而应保证代码运行可靠。您使 用索引值加快代码速度之前，应该确保该索引值在代码运行过程中或使用应用程序时不会改变。 使用完全受限制的对象引用使用完全受限制的对象引用消除了引用模糊并确保变量有明确的类型。一个完全受限制的对象引用包括了对象库名称，如下代码所示：Dim wb As Excel.WorkbookVBA可能必须对它们的引用进如果您使用通用的对象数据类型声明变量和参数，在运行过程中 行解析为(某对象的)属性和方法，这将导致速度变慢。一个通用对象数据类型示例如下：Dim wb As Workbook使用已

20、有的VBA方法也有一些特定目的的 VBA方法，它们提供在单元格区域执行特定操作的一种简单的方式。例如 工作表函数，这些特定的方法比使用通常的VBA编码完成相同的任务要更快。最常用的是” Replace”方法和” Find”方法。Replace 方法：下面的示例用了一种相当慢的方式代码改变单元格区域H1:H20000中每个单元格的值。Sub NowDoThis1()Dim Start As Double , Finish As DoubleStart = TimerDim Cell As RangeFor Each Cell In Worksheets(1).Range(H1:H20000).C

21、ellsIf Cell.Value = 4Then Cell.Value = 4.5NextFinish = TimerMsgBox 本次运行的时间是& Finish - StartEnd Sub下面的示例使用Replace方法进行同样的操作，但运行得更快。Sub NowDoThis2()快约两倍Dim StartAs Double , FinishAs DoubleStart = TimerWorksheets(1).Range(H1:H20000).Replace 4, 4.5IFinish = TimerMsgBox 本次运行的时间是& Finish - StartEnd SubFind

22、方法：下面的代码使用一种相对较慢的方法在单元格区域11:15000 中值为4的单元格内添加一个蓝色的椭圆。Sub FindltSlow()Dim Start As Double , Finish As DoubleStart = TimerDim Cell As RangeFor Each Cell In Worksheets(1).Range(l1:l5000).CellsIf Cell.Value = 4ThenWith Worksheets(1).Ovals.Add(Cell.Left, _Cell.Top, Cell.Width, _Cell.Height).In terior.Pattern = xlNone.Border.Colorlndex = 5End WithEnd IfNextFinish = TimerMsgBox 本次运行的时间是& Finish - StartEnd Sub下面的示例使用了Find方法和FindNext方法执行相同的任务，但运行速度更快。Sub FindItFaster()快约25倍Dim Start A