VBA: 提高 VBA 宏性能的 8 个技巧

文章背景： 作为VBA新手，在运行自己编写的代码时，可能会遇到VBA运行缓慢的问题。可以采取以下8个措施来提高运行效率。

1 关闭除VBA中的必需品之外的所有东西2 通过系统设置禁用Office动画3 删除不必要的Select方法4 使用With语句读取对象属性5 使用 ranges 和 arrays6 使用 .Value2 而不是 .Text 或 .Value7 绕过剪贴板（复制和粘贴）8 使用 Option Explicit 捕捉未声明的变量

1 关闭除VBA中的必需品之外的所有东西

加速 VBA 代码时要做的第一件事就是在宏运行时关闭不必要的功能，例如动画、屏幕更新、自动计算和事件。这些功能可能会增加额外的开销并减慢宏的速度，尤其是宏正在修改许多单元格并触发大量屏幕更新和重新计算的情况下。

下面的代码示例向您展示了如何启用/禁用：

• 手动计算
• 屏幕更新
• 动画

Option Explicit
Dim lCalcSave As Long
Dim bScreenUpdate As Boolean

Sub SwitchOff(bSwitchOff As Boolean)

   Dim ws As Worksheet
   
   With Application
 
       If bSwitchOff Then
   
           ' OFF
           lCalcSave = .Calculation            '模块变量，保存旧值
           bScreenUpdate = .ScreenUpdating     '模块变量，保存旧值
           
           .Calculation = xlCalculationManual
           .ScreenUpdating = False
           .EnableAnimations = False
           

           ' switch off display pagebreaks for all worksheets
           For Each ws In ActiveWorkbook.Worksheets
           
               ws.DisplayPageBreaks = False
               
           Next ws
         
       Else
   
           ' ON
           If .Calculation <> lCalcSave And lCalcSave <> 0 Then .Calculation = lCalcSave
           
           .ScreenUpdating = bScreenUpdate
           .EnableAnimations = True
         
       End If
   
   End With
 
End Sub

Sub Main()

   SwitchOff (True)    'turn off these features
   
   Call MyFunction     'do your processing here
   
   SwitchOff (False)   'turn these features back on
   
End Sub

Sub MyFunction()

   MsgBox "Hello world!"
   
End Sub

• Excel中的计算模式有如下三种：

2 通过系统设置禁用Office动画

Animations can be disabled in Excel specifically, under the Advanced or Ease of Access tab, within the File > Options menu.

3 删除不必要的Select方法

Select方法在 VBA 代码中很常见，但它经常被添加到不需要它的宏中。Select方法可以触发单元格事件，例如动画和条件格式，这会减慢宏的速度，因此删除不必要的Select方法可以显著加快宏的运行速度。 The following example shows the code before and after making the change to remove unnecessary selects.

修改前：

Sheets("Order Details").Select
Columns("AC:AH").Select
Selection.ClearContents

修改后：

Sheets("Order Details").Columns("AC:AH").ClearContents

4 使用With语句读取对象属性

When working with objects, use the With statement to reduce the number of times object properties are read. The following example shows the code before and after making the change to use the With statement.

修改前：

Range("A1").Value = “Hello”
Range("A1").Font.Name = “Calibri”
Range("A1").Font.Bold = True
Range("A1").HorizontalAlignment = xlCenter

修改后：

With Range("A1")
  .Value2 = “Hello” 
  .HorizontalAlignment = xlCenter
    With .Font
      .Name = “Calibri”
      .Bold = True
    End With
End With

5 使用 ranges 和 arrays

通过VBA 读取和写入 Excel 中的单元格非常费时。每次数据在 VBA 和 Excel 之间移动时都会产生开销。

This means that you should try to reduce the number of times you pass data between VBA and Excel. This is where ranges are useful. Instead of reading and writing to each cell individually in a loop, read the entire range into an array at the start, loop through the array, and then write the entire array back at the end. The following example code shows how a range can be used to read and write the values once, instead of reading each cell individually.

Sub test()

    Dim vArray As Variant, dValue As Double
    Dim iRow As Integer, iCol As Integer

    vArray = Range("A1:C10000").Value2      'read all the values at once from the Excel cells, put into an array

    For iRow = LBound(vArray, 1) To UBound(vArray, 1)
    
        For iCol = LBound(vArray, 2) To UBound(vArray, 2)
        
            dValue = vArray(iRow, iCol)
            
            If dValue > 0 Then
            
                dValue = dValue * dValue          'Change the values in the array, not the cells
                vArray(iRow, iCol) = dValue
                
            End If
        
        Next iCol
        
    Next iRow
    
    Range("A1:C10000").Value2 = vArray      'writes all the results back to the range at once

End Sub

6 使用 .Value2 而不是 .Text 或 .Value

There are different ways that you can retrieve values from a cell, and which property you use can make a difference in the performance of your code.

.Text is commonly used to retrieve the value of a cell – it returns the formatted value of a cell. Getting the formatting of a cell is more complex than just retrieving a value, and makes .Text quite slow.

.Value is an improvement over .Text, as this mostly gets the value from the cell, without formatting. However for cells formatted as a date or currency, .Value will return a VBA date or VBA currency (which may truncate decimal places).

.Value2 gives the underlying value of the cell. As it involves no formatting, .Value2 is faster than .Value. .Value2 is faster than .Value when processing numbers (there is no significant difference with text), and is much faster using a variant array.

7 绕过剪贴板（复制和粘贴）

When you use the Macro Recorder to record operations that use copy and paste, the code will use the copy and paste methods by default. However, within VBA code, it is much faster to bypass the clipboard and use internal operations instead. By default, copying will copy everything, including formulas, values and formatting. You can make copying faster by only copying values or formulas, without the formatting. The following example shows the code before and after making the change to bypass the clipboard.

修改前：

Range("A1").Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste

修改后：

' Approach 1: copy everything (formulas, values and formatting
Range("A1").Copy Destination:=Range("A2")

' Approach 2: copy values only
Range("A2").Value2 = Range("A1").Value2

' Approach 3: copy formulas only
Range("A2").Formula = Range("A1").Formula

8 使用 Option Explicit 捕捉未声明的变量

Option Explicit is one of the available Module directives in VBA that instructs VBA on how to treat the code within the code module. Setting Option Explicit requires all variables to be declared and will give compile errors if an undeclared variable is used. This helps catch incorrectly typed variable names and improves performance with all variable types being defined at compile time, instead of being inferred at runtime.

This can be set by typing: Option Explicit at the top of each module in your project or by checking the "Require Variable Declaration" option under Tools -> Options in the VBA editor.

Option Explicit

参考资料：

[1] 大幅度提高 VBA 宏性能的 9 个快速技巧(https://zhuanlan.zhihu.com/p/520105581)

[2] 9 quick tips to improve your VBA macro performance(https://techcommunity.microsoft.com/t5/excel/9-quick-tips-to-improve-your-vba-macro-performance/m-p/173687)

[3] XlCalculation enumeration(https://docs.microsoft.com/en-us/office/vba/api/excel.xlcalculation)

[4] Turn off Office animations(https://support.microsoft.com/en-us/office/turn-off-office-animations-9ee5c4d2-d144-4fd2-b670-22cef9fa025a?ui=en-us&rs=en-us&ad=us)

[5] TEXT vs VALUE vs VALUE2 – Slow TEXT and how to avoid it(https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/)

[6] Macro takes longer than expected to execute many individual copy and paste operations in Excel 2010 and later(https://support.microsoft.com/en-us/topic/macro-takes-longer-than-expected-to-execute-many-individual-copy-and-paste-operations-in-excel-2010-and-later-403dc927-b7e7-7096-0cc8-bd8a2b1e685c)