使VBA代码更快且更简洁的方法

前言
本文是对《如何优化代码并使VBA程序尽可能快的运行》一文的补充，您在阅读本文时，可对照参考。
在本文中，列举了大量的代码和示例。但在本文中所讨论的代码并不是本文的中心内容，它们只是用作测试代码运行速度，以说明本文的相关内容。因此，您可以将本文中的代码粘贴或输入到您的工作簿中进行测试，当然您也可以下载本文的附件——优化代码示例.xls进行调试。注意，您所运行的计算机的环境和配置不同，速度也会有所差异。同样，您要得出准确的结果，也需要对代码进行多次的运行和进行最终平均速度的比较。
有必要对代码进行优化吗？
这可能不是绝对必要的，但依赖于您要做的工作……如果您正好编写了一个快速且简短的或者是一次性使用且与速度和/或简洁要求无关的代码，您就不需要优化代码。
但另一方面，如果您处理一个带有很多数据、工作簿、工作表等大的工程，再次检查您第一次编写好的代码，看看是否您的代码需要优化，而这样做总是值得的。
最终，您将养成编写代码的好习惯，将会使您的代码更简洁、运行更快速、并且容易为您自已和他人阅读和调试。同时，由于您的代码简洁，因而输入更快，工作效率更高。
减少OLE引用
调用每个VBA方法或属性都需要一个或多个OLE引用，这样在代码中会有多个点运算符，而每次代码调用都需要对这些点运算符进行解析，这将花费更多的时间。因此，在调用方法或属性时减少引用长度将是使您的程序运行更快的一种好方法。
例如，下面的代码包含有三个点运算符，因此Workbooks(1)需要调用三次属性。
Workbooks(1).Sheets(1).Range("c5").Value = 10
而下面的代码包含有一个点运算符，这意味着ActiveWindow仅需调用一次属性。
ActiveWindow.Left = 200
在接下来所讲述的内容中有些示例证实了减少点运算符的数量能创建更快运行速度的代码。
使用对象变量
当您一遍又一遍的使用相同对象引用时，您可以将该对象引用设置成一个变量，然后使用该变量代替对象引用。这样，您在代码中只需对该对象变量进行引用即可。
例如，下面的示例在每行中调用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 "本次运行的时间是" & Finish - Start
End Sub
您能在循环开始前通过设置Workbooks(“Book1”).Sheets(1)作为一个对象变量来优化上面的例子，下面的示例在每行仅调用一个Range属性，当循环1000次时，总共只调用该属性2000次。
注意，“Value”是一个缺省属性，通常不需要明确指定它，它将被自动调用。因此，该属性在下面的代码中被忽略。然而，就养成良好的编程习惯而言，还是建议您最好写明该属性。
Sub DoThis2()
  '快约35%以上
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  Dim ThisBookSheet 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 - Start
End Sub
您可以比较这两个示例的运行速度，它们都得到同样的结果，但在我的机子上运行时，第二个示例比第一个快60%。当然，您还能使用With…End With语句获得相同的结果。
使用With…End 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 With
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
上述三个示例均得到相同的结果，但在我的机子上运行时，本示例比第一个示例快50%以上。
使用For Each…Next循环
与使用计数进行循环相比，在遍历集合或数组时使用For Each…Next循环将更快。在多数情况下，使用For Each…Next循环也更方便，并且使您的宏更简洁、更容易阅读和调试。
下面的示例运行很慢，因为在每次循环重复时它设置并调用了行变量.Row(i)。
Sub DoSomethingSlow()
  Dim Start As Double, Finish As Double
  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 - Start
End Sub
下面的示例代码更简洁，其运行速度大约是上面代码的2～3倍。因为For Each…Next循环自动记录行数并定位，而不需要调用变量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
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
将属性和方法放在循环外部
在代码运行时，获取变量的值快于获取属性的值。因此，如果您的代码在循环内部获取属性的值，您可以在循环外部将该属性的值先指定给一个变量，然后在循环内部使用此变量代替属性的值，这样的代码将运行得更快。
下面所示的代码运行较慢，因为在每次重复循环时都必须获取Sheet的Range属性的值。
Sub TryThisSlow()
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  Dim MyLoop As Long
  For MyLoop = 2 To 4001
     Cells(MyLoop, 2) = Sheet1.Range("B1")
  Next
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
下面的示例与上面所产生的结果相同，但比上面的要更快，因为在循环开始以前我们已经将Sheet的Range属性的值指定给了单独的变量MyVar。这样，代码将在每次重复循环时利用该变量的值，而不必每次都要调有属性。
Sub TryThisFaster()
  '快约35%以上
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  Dim MyVar As String, MyLoop As Long
  MyVar = Sheet1.Range("B1")
  For MyLoop = 2 To 4001
     Cells(MyLoop, 2) = MyVar
  Next
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
如果您在一个循环内部使用多个对象访问，您也可以使用With…End With将您能够移动的对象移到循环外部。下面的示例在每次循环重复时都调用Sheets对象和Cells属性。
Sub NowTryThisSlow()
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  Dim c As Long
  For c = 1 To 8000
    Sheet1.Cells(c, 5) = c
  Next
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
对上面的代码改写如下，使用With语句将调用Sheets对象移到循环外部，只剩余调用Cells。
Sub NowTryThisFaster()
  '约快3倍
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  Dim c As Long
  With Sheet1
    For c = 1 To 8000
      .Cells(c, 5) = c
    Next
  End With
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行时间为" & Finish - Start
End Sub
注：您也能通过使用对象变量在循环外部调用该对象。
只要有可能就使用集合索引值
您能在集合中使用名称或者数字来指定某个单一的对象，但使用对象的索引值通常是更快的。如果您使用对象的名字，VBA必须解析名字成为索引值；但如果您使用索引值，就能避免这个额外的步骤。
但另一方面，我们要注意到在集合中通过名称指定对象有很多优点。使用对象名称能使您的代码更容易阅读和调试。此外，通过名称指定一个对象比通过索引值更安全，因为当您的代码运行时该对象的索引值可能变化。
例如，某菜单的索引值表示它在菜单栏中的位置，但是如果在菜单栏中添加了菜单或者删除了菜单，该菜单的索引值会变化。这样，您就不应该考虑代码的速度，而应保证代码运行可靠。您使用索引值加快代码速度之前，应该确保该索引值在代码运行过程中或使用应用程序时不会改变。
减少对对象的激活和选择
在多数情况下，您不必在操作某对象前激活它。如果您是通过宏录制器来学习VBA编程，您可能习惯于在操作某对象前激活或者选择该对象。
宏录制器生成这样的代码是因为在您激活窗口和选取它们的内容时，它必须跟踪您的按键并生成与您所操作的步骤相对应的代码。但是，您通常可以编写更简洁且快速的VBA代码产生同样的结果，而无须在处理某对象前激活或选择该对象。。
例如，若要用随机数字填充Sheet1中单元格F1：F20(使用自动填充AutoFill方法)，使用宏录制器生成的代码如下：
Sub DoThisSlow()
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  '为进行测试,我们将进行100次循环
  Dim N As Long
  For N = 1 To 100
    '***************************
    Sheets("Sheet1").Select
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=RAND()"
    Selection.AutoFill Destination:=Range("F1:F20"), Type:=xlFillDefault
    Range("F1:F20").Select
    '***************************
   Next
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
在上面的代码中，所有调用Select方法的代码都不是必须的。您能使用With语句编写代码直接操作工作表和单元格，如下代码所示：
Sub DoThisFaster()
  '快约两倍
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  '为了进行测试,我们将进行100次循环
  Dim N As Long
  For N = 1 To 100
    '***************************
    With Sheets("Sheet1")
       .Range("F1").FormulaR1C1 = "=RAND()"
       .Range("F1").AutoFill Destination:=.Range("F1:F20"), _
         Type:=xlFillDefault
    End With
    '***************************
  Next
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
当使用宏录制器时，记住它会精确地记录你所做的一切，但它不会自动优化代码。上面所录制的宏使用AutoFill方法，这不是在某单元格区域填充随机数的最有效的方法，您能仅用一行代码实现相同的结果，如下所示：
Sub DoThisMuchFaster()
  '比原来的快约四倍
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  '为了进行测试,我们将进行100次循环
  Dim N As Long
  For N = 1 To 100
   '***************************
   Sheets("Sheet1").Range("F1:F20").Formula = "=RAND()"
   '***************************
  Next
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
当您优化所录制的代码时，您应考虑用这个宏将要去实现什么功能。您在用户界面中执行的一些操作将作为一个方法被宏录制(例如从一个单元格拖拉公式到单元格区域时，会录制为自动填充AutoFill)，有时您能对这些代码进行修改，因为在VBA中执行相同的操作有更好的方式。
移除不必要的代码
宏录制器生成无效代码的一个原因是它不知道在对话框中您选择了哪些选项，因此，当您关闭对话框时它将直接记录所有可用的选项。例如，选择单元格区域G2:G20，然后在单元格格式对话框中改变字体样式为粗体，使用宏录制器生成的代码如下：
Sub NowThis1()
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  '为了进行测试,将循环100次
  Dim N As Long
  For N = 1 To 100
    '***************************
    Range("G2:G20").Select
    With Selection.Font
      .Name = "Arial"
      .FontStyle = "Bold"
      .Size = 10
      .Strikethrough = False
      .Superscript = False
      .Subscript = False
      .OutlineFont = False
      .Shadow = False
      .Underline = xlNone
      .ColorIndex = xlAutomatic
    End With
    '***************************
  Next
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
您能只用下面的一行代码为指定的单元格设置字体样式，不需要选择单元格区域。
Range("G2:G20").Font.FontStyle = "Bold"
如果您考虑到您想要宏所做的事情(本例中为使字体加粗)，那么您可以查阅应用到Font对象的属性和方法列表，您将知道只需使用Bold属性编写这个宏代码以实现所需的功能。代码如下：
Sub NowThis2()
  '快约10倍
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  '为进行测试,将循环100次
  Dim N As Long
  For N = 1 To 100
    '***************************
    Range("G2:G20").Font.Bold = True
    '***************************
  Next
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间为" & Finish - Start
End Sub
您也能在用户界面中通过执行不同的方法来录制产生结果相同的操作对宏录制器进行试验。例如，如果您通过标准工具栏上的粗体按钮格式化某区域为粗体，那么宏录制器将使用Bold属性。
减少”Variant”类型变量的使用
虽然您可能发现在您的代码中使用Variant(变体)变量是方便的，但是如果您将变量清楚地声明为特定的数据类型，然后用VBA处理存储在该变量中的值，要比处理存储在Variant变量里的值快。
如果执行不涉及分数值的数学运算，那么在您的代码中使用Long型变量比使用Variant变量更快。Long型变量也是在For…Next循环中索引值变量类型的最好选择。
然而，您要注意到，您使用特定类型变量所获取的速度是以失去灵活性为代价的。例如，当使用特定数据类型时，您可能遭到变量溢出或类型不匹配的情形，而不会像Variant变量会自动进行类型转换处理。
使用特定的对象类型
当您的宏被编译或者是运行(后台编译)时，会解析对象及它们的方法和属性的引用。经过宏编译解析的引用比在程序运行时必须被解析的引用要更快，因此，您最好跳过后台编译。
如果您声明变量和参数为特定的对象类型(比如Range或Worksheet)，VBA在编译您的程序时将解析引用为这些对象的属性和方法。(如果要查找指定对象类型列表，请参见”对象浏览器”)
使用完全受限制的对象引用
使用完全受限制的对象引用消除了引用模糊并确保变量有明确的类型。
一个完全受限制的对象引用包括了对象库名称，如下代码所示：
Dim wb As Excel.Workbook
如果您使用通用的对象数据类型声明变量和参数，在运行过程中VBA可能必须对它们的引用进行解析为(某对象的)属性和方法，这将导致速度变慢。
一个通用对象数据类型示例如下：
Dim wb As Workbook
使用常量
变量会发生变化，因此VBA在程序运行时必须获取当前变量的值。
在应用程序中使用常量会使程序运行更快。在编译您的代码时，常量仅计算一次并被存储。
常量也能使您的宏程序更易阅读和维护。如果在您的程序中有一些不变的字符串或数值的话，您可以声明它们作为常量。
关闭屏幕刷新
使工作簿的显示发生变化的程序——例如，在很大的单元格区域改变每个单元格颜色的程序，或者是创建很多图形对象——在您关闭屏幕刷新后都将运行得更快。
这意味着您不能观察到程序的运行过程(当您重新开启屏幕更新时将立即显示变化)，但程序将运行更快。当您编写和调试程序时，您可能想让屏幕更新开启，然后在您运行程序前关闭屏幕更新。
为了关闭屏幕更新，设置ScreenUpdating属性的值为False，如下代码所示：
Application.ScreenUpdating = False
记得当您的宏运行结束时将ScreenUpdating属性的值重新设置为True。
技巧：您有时能通过不激活您所改变的对象来达到同样的效果。例如，如果您不需要首先激活文档而在工作表中创建图形对象，您不需要关闭屏幕更新设置，因为这些变化总是不可见的。
使用已有的VBA方法
也有一些特定目的的VBA方法，它们提供在单元格区域执行特定操作的一种简单的方式。例如工作表函数，这些特定的方法比使用通常的VBA编码完成相同的任务要更快。最常用的是”Replace”方法和”Find”方法。
Replace方法:
下面的示例用了一种相当慢的方式代码改变单元格区域H1:H20000中每个单元格的值。
Sub NowDoThis1()
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  Dim Cell As Range
  For Each Cell In Worksheets(1).Range("H1:H20000").Cells
    If Cell.Value = 4 Then Cell.Value = 4.5
  Next
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
下面的示例使用Replace方法进行同样的操作，但运行得更快。
Sub NowDoThis2()
  '快约两倍
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  Worksheets(1).Range("H1:H20000").Replace "4", "4.5"
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
Find方法:
下面的代码使用一种相对较慢的方法在单元格区域I1:I5000中值为4的单元格内添加一个蓝色的椭圆。
Sub FindItSlow()
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  Dim Cell As Range
  For Each Cell In Worksheets(1).Range("I1:I5000").Cells
    If Cell.Value = 4 Then
       With Worksheets(1).Ovals.Add(Cell.Left, _
                                    Cell.Top, Cell.Width, _
                                    Cell.Height)
                               .Interior.Pattern = xlNone
                               .Border.ColorIndex = 5
       End With
    End If
  Next
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
下面的示例使用了Find方法和FindNext方法执行相同的任务，但运行速度更快。
Sub FindItFaster()
  '快约25倍
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  Dim Cell As Range, FirstAddress As String
  With Worksheets(1).Range("I1:I5000")
    Set Cell = .Find(4)
    If Not Cell Is Nothing Then
       FirstAddress = Cell.Address
       Do
         With Worksheets(1).Ovals.Add(Cell.Left, _
                                      Cell.Top, Cell.Width, _
                                      Cell.Height)
                                 .Interior.Pattern = xlNone
                                 .Border.ColorIndex = 5
         End With
         Set Cell = .FindNext(Cell)
         Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
    End If
  End With
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
关于带有特定目的的VBA方法的更多的信息，您可参见VBA帮助系统相关主题。
考虑在VBA代码中使用工作表函数
操作单元格区域的Excel工作表函数通常比完成同样任务的VBA程序更快(但不能确保总是这样，您可以对它们进行速度测试)
例如，在代码中使用SUM工作表函数比用VBA代码在单元格区域中循环并相加值要快得多，以此为例，下面的代码运行速度相对较慢。
Sub AddItSlow()
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  '为了进行测试,我们循环5次
  Dim N As Long
  For N = 1 To 5
     '***************************
     Dim Cell As Range
     For Each Cell In Worksheets(2).Range("A1:G200")
        [a1] = [a1] + Cell.Value
     Next Cell
     '***************************
  Next N
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
下面的代码实现相同的功能，但运行得更快(几乎瞬间完成)。
Sub AddItFaster()
  '快近600倍
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  '为了进行测试,我们循环5次
  Dim N As Long
  For N = 1 To 5
     '***************************
     [a1] = Application.WorksheetFunction. _
               Sum(Worksheets(2).Range("A1:G200"))
     '***************************
  Next
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
产生统计结果的函数(例如PRODUCT、COUNT、COUNTA和COUNTIF)是代替运行速度更慢的VBA代码的很好的选择，并且，一些工作表函数(例如MATCH和LOOKUP)能够将单元格区域作为参数。
不要认为工作表函数总是更快的
如下例所示，在VBA中没有Max或Min函数，但Excel中有该函数。于是，您能编写出如下代码：
Sub MaxIt1()
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  '为了测试,我们循环10000次
  Dim N As Long
  For N = 1 To 10000
    '***************************
    [J1] = Application.Max([J2], [J3])
    '***************************
  Next N
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行时间是" & Finish - Start
End Sub
或者，您能在VBA中使用下面的方式实现相同的功能：
Sub MaxIt2()
  Dim Start As Double, Finish As Double
  Start = Timer
  '--------------------------------------
  '为了测试,我们循环10000次
  Dim N As Long
  For N = 1 To 10000
     '***************************
     If [J2] >= [J3] Then [J1] = [J2] Else [J1] = [J3]
     '***************************
  Next N
  '--------------------------------------
  Finish = Timer
  MsgBox "本次运行的时间是" & Finish - Start
End Sub
比较上面的两个程序，可能认为使用工作表函数会更快，但事实上用VBA代码可以获得几乎相同的速度。因此，在一些大的循环中，您可以对实现同样功能的工作表函数的VBA代码进行测试。一些内置的VBA函数事实上运行速度也是慢的，因此，在编写代码时，在不同方式之间进行速度测试总是值得的。

示例文档见UploadFiles/2006-8/828917942.rar