加速 Excel VBA 代码的方法

“条条大路通罗马”，同样的任务在 VBA 中也可以有不同的实现方式。刚开始学习编程的时候，往往达到目标就够了，不会过多考虑代码的运行速度和资源消耗。随着编程水平的提高和项目复杂度的增加，你会越来越感受到代码优化的重要性，您需要采取措施提高 VBA 代码的性能。在本文中，您将学习到多种加速 Excel VBA 代码的方法。

关闭自动重算

当启用自动重算后，每次影响公式的值更改时，Excel 都会自动重新计算工作簿。在包含大量公式的工作表中，此行为会大大降低您的 VBA 运行速度。

如果您的工作簿包含大量公式，您可能不希望每次程序更改单元格值时 Excel 都触发重新计算。可以使用 Calculation 属性将工作簿计算切换到手动计算模式。当工作簿处于手动计算模式时，工作簿不会自动重新计算，除非您按键盘上的 F9 键明确触发计算。

关闭 Excel 的自动重算可以显著加速您的 VBA。方法是先将 Excel 置于手动重算模式，运行代码，当代码运行完毕再切换回自动重算模式。

'------------- mdlCalculationMode -------------
Sub CalculationMode()
    Application.Calculation = xlCalculationManual
    ' 将您的 VBA 代码放在这里
    Application.Calculation = xlCalculationAutomatic
End Sub

注意

将计算模式设置回 xlCalculationAutomatic 将自动触发工作表的重新计算。因此，您不需要在运行宏后按 F9。

您可能实际上需要在 VBA 运行期间执行计算，在这种情况下您不必使用手动计算模式。请务必考虑您的应用场景并确定在 VBA 运行时关闭自动计算会发生什么情况。

禁用屏幕更新

您可能会注意到在代码运行时屏幕会闪烁。这种闪烁是 Excel 试图重绘屏幕以显示工作表的当前状态。遗憾的是，Excel 每次重绘屏幕都需要时间。在大多数情况下，每次代码执行某些操作时，您不需要 Excel 消耗计算机资源来重新绘制屏幕。

使用 ScreenUpdating 属性禁用屏幕更新，直到您的代码完成运行，这样可以节省时间和资源，并让您的 VBA 运行得更快一些。您可以在 VBA 代码运行完毕后重新启用屏幕更新。

'------------- mdlDisableScreenUpdating -------------
Sub DisableScreenUpdating()
    Application.ScreenUpdating = False
    ' 将您的 VBA 代码放在这里
    Application.ScreenUpdating = True
End Sub

提示

将 ScreenUpdating 属性设置回 True 后，Excel 不仅会恢复重绘，还会触发一次重绘以显示来自 VBA 的所有更改。

关闭状态栏更新

Excel 状态栏显示在 Excel 窗口的底部。状态栏通常显示 Excel 中某些操作的状态。例如，如果您复制/粘贴一个区域，Excel 将在状态栏中显示该操作的进度。通常，操作执行得如此之快以至于您无法在状态栏中看到进度。但是，如果您的 VBA 正在处理大量数据，状态栏将会占用一些资源。

注意

关闭屏幕更新与关闭状态栏显示是分开的。这意味着即使您禁用屏幕更新，状态栏也会继续更新。

您可以通过使用应用程序暂时禁用任何状态栏更新来进一步提高 VBA 的性能。

'------------- mdlStatusBarUpdates -------------
Sub StatusBarUpdates()
    Application.DisplayStatusBar = False
    ' 将您的 VBA 代码放在这里
    Application.DisplayStatusBar = True
End Sub

禁用事件

您可以为工作簿或工作表设置事件过程，这些过程告诉 Excel 在工作表或工作簿更改时运行特定代码。有时，VBA 代码所做的更改实际上会触发事件过程。

例如，假设您为工作表 Sheet1 设置了 Worksheet_Change 事件，每当更改单元格或区域时，都会触发 Worksheet_Change 事件，如果您的 VBA 来操作 Sheet1 上的多个单元格，则每次更改该工作表上的一个单元格时，您的代码都必须在 Worksheet_Change 事件运行时暂停。您可以想象这种行为会如何减慢您的 VBA 执行速度。

此时可以使用 EnableEvents 属性告诉 Excel 在 VBA 运行时忽略事件。

只需在代码运行之前将 EnableEvents 属性设置为 False。代码运行后，再将 EnableEvents 属性设置回 True。

'------------- mdlIgnoreEvents -------------
Sub IgnoreEvents()
    Application.EnableEvents = False
    ' 将您的 VBA 代码放在这里
    Application.EnableEvents = True
End Sub

虽然禁用事件确实可以加速您的 VBA，但实际上您可能需要在 VBA 运行时触发一些事件。请务必考虑您的特定情况并确定如果您在 VBA 运行时关闭工作表或工作簿事件会发生什么情况。

隐藏分页符

如果您在工作表上显示分页符，则每次您的 VBA 修改行数、列数或更改工作表的页面设置时，Excel 都被迫花时间重新计算在工作表上显示分页符的位置。

您可以在 VBA 代码运行之前隐藏分页符来避免这种情况。将工作表的 DisplayPageBreaks 属性设置为 False 以隐藏分页符。如果要在 VBA 运行后继续显示分页符，可以将工作表的 DisplayPageBreaks 属性设置回 True。

'------------- mdlDisplayPageBreaks -------------
Sub DisplayPageBreaks()
    ActiveSheet.DisplayPageBreaks = False
    ' 将您的 VBA 代码放在这里
    ActiveSheet.DisplayPageBreaks = True
End Sub

暂停数据透视表更新

如果您的 VBA 在包含大型数据源的数据透视表上运行，则在执行动态添加或移动数据透视字段等操作时，您可能会遇到性能不佳的情况。这是因为您对数据透视表结构所做的每个更改都需要 Excel 重新计算数据透视表中的值。

您可以通过暂停数据透视表的重新计算直到所有数据透视字段更改完成来提高 VBA 的性能。只需将 PivotTable.ManualUpdate 属性设置为 True 以延迟重新计算，运行 VBA 代码，然后将 PivotTable.ManualUpdate 属性设置回 False 以触发重新计算。

'------------- mdlPivotTablesManualUpdate -------------
Sub PivotTablesManualUpdate()
    ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
    ' 将您的 VBA 代码放在这里
    ActiveSheet.PivotTables("PivotTable1").ManualUpdate = False
End Sub

避免复制和粘贴

需要记住的是，虽然使用宏录制器为您编写 VBA 代码节省了时间，但它并不总是编写出最高效的代码。一个典型的例子是宏录制器如何记录您在录制时执行的任何复制和粘贴操作。

如果您在录制宏时复制单元格 A1 并将其粘贴到单元格 B1 中，宏录制器将记录以下内容：

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    Selection.Copy
    Range("B1").Select
    ActiveSheet.Paste
End Sub

虽然此代码确实从单元格 A1 复制并粘贴到 B1，但它会强制 Excel 使用剪贴板，相当于添加了一个中间商的角色。您可以通过删除中间商并执行从一个单元格到目标单元格的直接复制来稍微加速您的 VBA。

下列代码使用 Copy 方法的 Destination 参数绕过剪贴板并将单元格 A1 的内容直接复制到单元格 B1。

Range("A1").Copy Destination:=Range("B1")

如果您只需要复制值（而不是格式或公式），则可以通过完全避免 Copy 方法来进一步提高性能。只需将目标单元格的值设置为与源单元格中相同的值。此方法比使用 Copy 方法快约 25 倍！

Range("B1").Value = Range("A1").Value

如果您只需要将公式从一个单元格复制到另一个单元格（而不是值或格式），您可以将目标单元格中的公式设置为源单元格中包含的相同公式。

Range("B1").Formula = Range("A1").Formula

使用 With 语句

使用宏录制器录制宏时，您可能会多次操作同一个对象。

例如，您的代码可能会更改单元格 A1 的格式，设置下划线、斜体和粗体格式。如果使用宏录制器录制宏时，您将得到以下结果：

Sub Macro2()
    Range("A1").Select
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.Underline = xlUnderlineStyleSingle
End Sub

不幸的是，这段代码并没有达到应有的效率，因为它强制 Excel 选择然后引用每个更改的对象。您可以通过使用 With 语句对给定对象执行多个操作而只引用该对象一次来节省时间并提高性能。

以下示例中的 With 语句告诉 Excel 更改三个属性，但只引用一次 Font 对象，使用的资源更少。

With Range("A1").Font
    .Bold = True
    .Italic = True
    .Underline = xlUnderlineStyleSingle
End With

养成将代码分块到 With 语句中的习惯不仅可以使 VBA 运行得更快，还可以使代码更易于阅读。

避免使用对象的 Select、Activate 方法

如果您通过在多个工作表的单元格 A1 中输入值 1234 来录制宏，您最终将得到与此类似的代码。

Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "1234"
Sheets("Sheet2").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "1234"
Sheets("Sheet3").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "1234"

如您所见，宏录制器喜欢使用 Select 方法在操作对象之前明确选择对象。它强制 Excel 花时间明确地选择每个被操作的对象，所以但效率不高。

一般来说，在处理对象之前不需要选择或激活对象。事实上，不使用“select”方法可以大大提高 VBA 的性能。

录制宏后，养成更改生成代码的习惯，删除 select 方法。在这种情况下，优化后的代码将如下所示。请注意，没有选择任何内容。该代码仅使用对象的层次结构来应用所需的操作。

Sheets("Sheet1").Range("A1").FormulaR1C1 = "1234"
Sheets("Sheet2").Range("A1").FormulaR1C1 = "1234"
Sheets("Sheet3").Range("A1").FormulaR1C1 = "1234"

如需了解更多，请参阅：如何避免在 Excel VBA 中使用 Select。

避免过多引用

在调用对象的方法或属性时，需要通过 OLE 组件的 IDispatch 接口。对这些 OLE 组件的调用需要时间，因此减少对 OLE 组件的引用数量可以提高代码的速度。

对于对象属性或方法的调用，一般使用 Object.Method 的表示方法，即使用“.”来调用属性和方法。

因此，可以根据符号“.”的个数来判断方法或属性调用的次数。“.”越少，代码运行得越快。

例如下面的语句包含 3 个符号“.”：

ThisWorkbook.Sheet1.Range("A1").Value = 100

下面的语句只有一个“.”：

Activewindow.Top = 100

这里有一些技巧可以减少“.”的数量使程序运行得更快。

首先，当需要重复引用同一个对象时，可以将对象设置为变量，以减少调用次数。

例如，以下代码每行需要调用两次“.”：

ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300

因为 Sheets("Sheet1") 对象需要被重复引用，所以可以先将其设置为一个变量 sht，这样每段代码只需要调用一次。

Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300

其次，如果不想声明一个临时变量 sht，也可以使用前面提到的 With 语句。如下例所示：

With ThisWorkbook.Sheets("Sheet1")
    .Cells(1, 1) = 100
    .Cells(2, 1) = 200
    .Cells(3, 1) = 300
End With

第三，当循环很多的时候，尽量让属性和方法保持在循环之外。在循环中重复使用同一个对象的某个属性值时，可以先将该属性值赋值给循环外的指定变量，再在循环内使用该变量，这样可以获得更快的速度。如下例所示：

For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next i

上面的示例中的每个循环都获取单元格 Cells(1, 2) 的 Value 属性。如果您在循环开始之前将 Cells(1, 2) 的 Value 属性分配给一个变量，您将获得更快的运行速度。如下例所示：

tmp = Cells(1, 2).Value
For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next i

上面的示例代码每次循环都会调用 ThisWorkbook.Sheets("Sheet1")。通过使用 With 语句将对 ThisWorkbook.Sheets("Sheet1") 的调用移到循环外，可以获得更快的运行速度。如下例所示：

tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
    For i = 1 To 1000
        .Cells(1, 1) = tmp
        .Cells(2, 1) = tmp
        .Cells(3, 1) = tmp
    Next i
End With

避免使用 Variant 数据类型

初学者通常更喜欢使用 Variant 类型（变体数据类型）的变量，它的优点是不那么复杂，因为对于 Integer 或 Long 数据类型来说，任何类型的数据都可以使用而不会出现内存溢出的问题。但是，Variant 类型数据比其他指定类型需要更多额外的内存空间（Integer 数据 2 字节，Long 数据 4 字节，Variant 数据 16 字节），VBA 需要比其他指定类型更多的时间来处理 Variant 类型数据。如以下示例所示：

Sub VariantTest()
    Dim i As Long
    Dim ix As Integer, iy As Integer, iz As Integer
    Dim vx As Variant, vy As Variant, vz As Variant
    Dim tm As Date
    vx = 100: vy = 50
    tm = Timer
    For i = 1 To 1000000
        vz = vx * vy
        vz = vx + vy
        vz = vx - vy
        vz = vx / vy
    Next i
    Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
    ix = 100: iy = 50
    tm = Timer
    For i = 1 To 1000000
        iz = ix * iy
        iz = ix + iy
        iz = ix - iy
        iz = ix / iy
    Next i
    Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

上面的代码中，第 8 到 13 行对 Variant 变量类型做了100万次加减乘除运算，第 17 到 22 行对 Integer 变量类型同样做了100万次加减乘除运算。在我的电脑上，Variant 变量的运算耗时约 0.08984秒，而 Integer 变量的运算耗时约 0.03516 秒。结果可能因计算机而异，但 Variant 变量明显比 Integer 变量慢。

因此，建议在可以明确使用指定数据类型时避免使用 Variant 类型变量。

使用工作表函数或方法

Excel 工作表函数通常比仅使用 VBA 普通代码来操作单元格区域要快得多。如以下示例所示，WorksheetFunctionTest 过程比较两种方法计算工作表区域数据总和所花费的时间。

Sub WorksheetFunctionTest()
    Dim Total1 As Long, Total2 As Long, i As Integer, tm As Date
    Dim cl
    tm = Timer
    For i = 1 To 1000
        For Each cl In Range("A1:A500")
            Total1 = Total1 + cl.Value
        Next
    Next i
    Debug.Print "VBA: " & Format((Timer - tm), "0.00000") & " seconds"
    tm = Timer
    For i = 1 To 1000
        Total2 = Total2 + WorksheetFunction.Sum(Range("A1:A500"))
    Next i
    Debug.Print "WorksheetFunction: " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

第 5 至 9 行使用 VBA 计算单元格区域 A1:A500 的总和，第 12 至 14 行使用工作表函数 SUM 计算相同单元格区域的总和，每个方法运行1000次。在我的电脑上，前者大约用时 0.78125 秒，而工作表函数法只用了 0.01953 秒。

Excel 还包括其他数据统计函数如 PRODUCT、COUNTIF 等，以及一些查找函数如 VLOOKUP。有效地使用这些函数可以获得比使用 VBA 代码更快的速度。

使用数组代替 Range

如果您只需要处理 Range 对象中单元格的值而不需要单元格的属性和方法，则可以使用数组来处理 Range 对象，因为 Array 变量比 Range 对象的操作快得多。

Sub ArrayTest()
    Dim Total1 As Long, Total2 As Long, i As Integer, tm As Date
    Dim cl
    Dim arr()
    tm = Timer
    arr = Range("A1:A500")
    For i = 1 To 1000
        For Each cl In arr
            Total1 = Total1 + cl
        Next
    Next i
    Debug.Print "Array: " & Format((Timer - tm), "0.00000") & " seconds"
    tm = Timer
    For i = 1 To 1000
        For Each cl In Range("A1:A500")
            Total2 = Total2 + cl.Value
        Next
    Next i
    Debug.Print "Range: " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

第 7 到 11 行使用 Array 计算单元格区域 A1:A500 的和，第 14 到 18 行使用 Range 计算相同范围的和，每个方法运行1000次。在我的电脑上，Range 方法用了 0.94531 秒，而 Array 方法只用了大约 0.01563 秒。

综上所述，如果要用 VBA 操作单元格区域，运行速度最快的是使用工作表函数，其次是数组，最慢的是 Range。

加速 VBA 常用代码

总之，您通常可以使用以下代码来简单加速 VBA 的运行速度。

'------------- mdlSpeedUpMacros -------------
Sub SpeedUpMacros()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    'ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True
    ' 将您的 VBA 代码放在这里
    'ActiveSheet.PivotTables("PivotTable1").ManualUpdate = False
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True
End Sub