VBA执行速度和效率

屏幕刷新：

如果我们不需要看见代码执行过程数据变化，可以在代码开始初关闭屏幕更新

'关闭屏幕更新
Application.ScreenUpdating = False
'恢复屏幕更新
Application.ScreenUpdating = True 

自动计算

如果工作表有很多函数，在代码运行过程中，关闭公式计算可以显著提升运行速度。

'手动重算
Application.Calculation = xlCalculationManual

'计算这个工作簿
calculate

'计算当前工作表
activesheet.calculate

'自动重算
Application.Calculation = xlCalculationAutomatic 

警告提示

有些时候，我们不希望代码运行过程中莫名弹出一些警告提示，可以设置False关闭提示信息。

'关闭提示
Application.DisplayAlerts = False 

'恢复提示
Application.DisplayAlerts = True 

关闭状态栏

Application.DisplayStatusBar 关闭状态栏。 如果将 Application.DisplayStatusBar 设置为 False，Excel 将不显示状态栏。 状态栏设置与屏幕更新设置是分开的，这样即使屏幕没有更新，也可以显示当前操作的状态。 但是，如果不需要显示每个操作的状态，在代码运行时关闭状态栏也可以提高性能。

禁用分页符

ActiveSheet.DisplayPageBreaks 禁用分页符。 如果将 ActiveSheet.DisplayPageBreaks 设置为 False，Excel 将不显示分页符。 不需要在代码运行时重新计算分页符，并且在代码执行后计算分页符可以提高性能。

事件关闭：

如果代码在运行过程中，我们不需要事件触发，可以关闭事件，避免工作簿中事件频繁触发，影响处理效率。

'关闭事件
Application.EnableEvents = False	

'启动事件
Application.EnableEvents = True

读取数据时使用 .Value2

从 Excel 区域读取数据时使用 .Value2 而不是 .Value 或 .Text
.Text 返回单元格的格式化值。 速度较慢，如果用户缩放，可能返回 ###，并可能丢失精度。
.Value 在区域被格式化为日期或货币的情况下，返回 VBA 货币变量或 VBA 日期变量。 速度较慢，可能会丢失精度，并且在调用工作表函数时可能导致错误。.Value2 速度快，不会改变正在从 Excel 检索的数据。

避免选择并激活对象

选择和激活对象的处理过程比直接引用对象更为密集。 通过直接引用 Range 或 Shape 等对象，可以提高性能。

使用单行语句进行複制和粘贴

Range(“E14”).Select
Selection.Copy
Range(“F14:G14”).Select
Activesheet.Paste
'改成*************
Range(“E14”).Copy Destination :=Range(“F14:G14”)

其他优化

通过将数组直接分配给 Range 来返回结果。
使用显式类型声明变量，以避免在代码执行期间确定数据类型的开销（可能在一个循环中进行多次）。
对于在代码中频繁使用的简单函数，请自己在 VBA 中实现这些函数而不是使用 WorksheetFunction 对象。
使用 Range.SpecialCells 方法缩小与代码交互的单元格数量。

方法1：尽量使用VBA原有的属性、方法和Worksheet函数

　　由于Excel对象多达百多个，对象的属性、方法、事件多不胜数，对于初学者来说可能对它们不全部了解，这就产生了编程者经常编写与Excel对象的属性、方法相同功能的VBA代码段，而这些代码段的运行效率显然与Excel对象的属性、方法完成任务的速度相差甚大。例如用Range的属性 CurrentRegion来返回Range 对象，该对象代表当前区。（当前区指以任意空白行及空白列的组合为边界的区域）。同样功能的VBA代码需数十行。因此编程前应尽可能多地了解Excel对象的属性、方法。

　　充分利用Worksheet函数是提高程序运行速度的极度有效的方法。如求平均工资的例子：　 　

For Each c In
　　 Worksheet(1).Range(″A1:A1000″)
　　 TotalValue = TotalValue ＋ c.Value
　　 Next
　　 AverageValue = TotalValue / Worksheet(1).Range(″A1:A1000″).Rows.Count

　　 而下面代码程序比上面例子快得多：

AverageValue=Application.WorksheetFunction.Average(Worksheets(1).Range(″A1:A1000″))

　　 其它函数如Count,Counta,Countif,Match,Lookup等等，都能代替相同功能的VBA程序代码，提高程序的运行速度。

　　方法2：尽量减少使用对象引用，尤其在循环中

　　每一个Excel对象的属性、方法的调用都需要通过OLE接口的一个或多个调用，这些OLE调用都是需要时间的，减少使用对象引用能加快VBA代码的运行。例如

　　1．使用With语句。　

Workbooks(1).Sheets(1).Range(″A1:A1000″).Font.Name=″Pay″
Workbooks(1).Sheets(1).Range(″A1:A1000″).Font.FontStyle=″Bold″ ...

　  则以下语句比上面的快　   

With Workbooks(1).Sheets(1).Range(″A1:A1000″).Font
　　 .Name = ″Pay″
　　 .FontStyle = ″Bold″
　　 ...
End With

　　2．使用对象变量。
　　 如果你发现一个对象引用被多次使用，则你可以将此对象用Set 设置为对象变量，以减少对对象的访问。如：

Workbooks(1).Sheets(1).Range(″A1″).Value = 100
Workbooks(1).Sheets(1).Range(″A2″).Value = 200

　　 则以下代码比上面的要快：　　

Set MySheet = Workbooks(1).Sheets(1)
MySheet.Range(″A1″).Value = 100
MySheet.Range(″A2″).Value = 200

'如果一个对象引用被多次使用，则你可以将此对象存储到对象变量中，代码直接从内存中读取对象变量的信息，以减少对对象的访问。
ThisWorkbook.Worksheets("案例").Range ("a1")="VBA说"
'可简化为：
set sht = ThisWorkbook.Worksheets("案例")sht.Range ("a1")="VBA说"

　　3．在循环中要尽量减少对象的访问。

For k = 1 To 1000
    Sheets(″Sheet1″).Select
    Cells(k,1).Value = Cells(1,1).Value
Next k

　　 则以下代码比上面的要快：　　

Set TheValue = Cells(1,1).Value
Sheets(″Sheet1″).Select
For k = 1 To 1000
　　 Cells(k,1).Value = TheValue
Next k

　　方法3：减少对象的激活和选择

　　如果你的通过录制宏来学习VBA的，则你的VBA程序里一定充满了对象的激活和选择，例如Workbooks(XXX).Activate、Sheets(XXX).Select、Range(XXX).Select等,但事实上大多数情况下这些操作不是必需的。例如

Sheets(″Sheet3″).Select
Range(″A1″).Value = 100
Range(″A2″).Value = 200

　　 可改为：　

With Sheets(″Sheet3″)
　　 .Range(″A1″).Value = 100
　　 .Range(″A2″).Value = 200
End With

网上有很多关于提高VBA代码运行效率的方法，我从中总结了以下14条，我自己使用过程中感觉效果比较好。

• 声明变量

变量声明为非Variant，避免使用浮点型变量。

• Range("A1")  比  [A1]  更快

因为  [A1]  需要调用Application.Evaludate("A1")。

• 禁止屏幕刷新、计算模式手动

Application.ScreenUpdating = False  ' 开启屏幕刷新
Application.ScreenUpdating = True  ' 关闭屏幕刷新
 
Application.Calculation = xlCalculationManual  ' 计算模式为手动
Application.Calculation = xlCalculationAutomatic  ' 计算模式为自动

需要注意的是在sub结束的时候，要将计算模式改回为自动，这样表格中才能自动进行公式计算等。

• 使用工作表(Worksheet)的数字编号比引用“名称”更快

直接使用 sheet(1) 比 引用工作表的名字更快，但是不安全，需要注意sheet的实际编号，以及新建、删除工作表等操作，引起sheet的编号变化。

• 常量比变量更快

• For each  比  For 更快

• 尽量不用Selection

代码中有 

***.select  /  selection  组合的，都尽量直接引用对象

• 对同一目标进行多次操作时，尽量用  With...End With

• 内置函数比自己写的函数快，即尽量不重复造轮子

• 明确对象属性

引用  Cell(1,1).value  比  Cell(1,1)  快

• 多用简单句，少用复合句

If A And B Then ... End If  比两个If语句慢

• 并列条件Elseif 比Select快

所以遇到多重条件判断时，把最常出现的条件放在前面，减少选择次数

• For 比 Do While快

• 使用内存数组变量

Range("A1:F65536") = arr

在excel用vba处理数据也动不动就几十秒，甚至几分钟，有几个很简单或者比较简单的方法就能让没有经过刻意处理的vba处理速度快几百倍。

以下大概按照使用频率以及用途是否广泛大概排序：

2. 关闭自动计算公式

大家的excel里在vba之外必然还存在着大量的公式，如果vba与excel表格内容交互影响到了公式的话，每次操作，涉及到的公式都会重新计算，这大大拖慢了VBA的效率。只要一行简单的代码就可以实现这个功能！

只要在代码开始前加上

Application.Calculation = xlCalculationManual

就可以停止vba运行期间的公式运算，然后在代码的结尾再加上

Application.Calculation = xlCalculationAutomatic

就可以再回复excel的自动计算功能。

但是如果vba运行中需要excel表格公式计算的结果，只要加上

Calculate

Calculate  重新计算整个工作薄

worksheets("sheet1").Calculate  重新计算特定工作表

Range("A1:B10").calculate 重新计算特定区域范围

手动执行这个区域内的运算就好了

使用这个方法，在表格中大量使用类似vlookup等公式的时候，看公式量的大小大约加快了10倍到10000倍运算速度。

2. 关闭屏幕刷新

每次vba对表格进行操作后，屏幕都会进行刷新操作。如果将这个功能关闭也会节省大量资源

方法相似，在代码最前加上

Application.ScreenUpdating = FALSE

以及结尾加上

Application.ScreenUpdating = TRUE

就可以了！

3. 尽量减少VBA和表格的交互

以块的形式读取和写入数据

当逐行大量数据的时候，经常用到的就是写个循环，一行处理一次，读一个格子数据处理后再写入。就算不考虑公式和其他的影响，每次VBA和表格的交互都是会拖慢VBA的读取速度。如果使用类似于如下的代码

Dim x() As Variant '注意这里必须是variant

x = Sheet1.Range("C1:C10").Value

Range("D1:D10").Value = x

先定义数组，然后对整块数据直接读入。处理后整块写入。对于3000行的数据处理，这个代码大概快30倍。

数组写回工作表方法:

''第一种
worksheet("sheet1").range("a1:g100").value = arr

''第二种
with worksheet("sheet1")
    .range("a1:g100").value = arr
end with

''第三种
dim rng as range
set rng = range("a1:g100")
rng.value = arr

例如:

for x = 1 to 100
b = range("关键指数") + x
next

如果把range("关键指数")作为变量,系统只读取一次,使得vba和excel的通信最小化

,代码快100多倍

rng = range("关键指数")
for x = 1 to 100
b = rng + x
next

电子表中常常留下公式,并通过vba调用他们,不如将公式直接放在vba程序里,减少vba和excel的通信.并不需要重新计算电子表格.

4. 对于非常频繁的运算，避免使用excel公式

excel内置公式提供了非常多的运算，但是在vba内并没有，所以很多人就会直接使用worksheetFunction来进行运算，或者甚至在表格里写入公式然后运算后再读入。例如:

for i=1 to 1000000
variablel = application.Max(value1,value2)
next

在运算量比较少的时候没有问题，但是如果是海量的运算，避免使用公式，而是比如使用如下的代码来代替Max（）

Function Max2 (Value1, Value2)
    If Value1 > Value2 Then
        Max2 = Value1
     Else 
        Max2 = Value2
    End If
End Function

for i = 1 to 1000000
value1=max2(amt1, amt2)
next

还有其他的比如计算平均数，标准差等的公式。

在大型和耗时的宏中用工作表函数要小心.

5. 如果不是必要不要进行“select"或者是“activate"操作

这样的操作在VBA里是非常占用资源的，而且也是新手（比如我）经常使用的方法。

比如先select或者activae一个表，然后activesheet怎么样怎么样。

比如类似代码

Worksheets("sheet1").Select

x= Cells(1, 1)

的运算速度，远远低于直接写一句

Worksheets("Sheet1").select

速度差大概是阿三开挂不用眼看直接飞饼，和新手战战兢兢烙饼的速度差。

另外插一句，选择不同的工作表的时候，除了使用工作表名字，还可以直接使用内置的工作表名称，也就是工作表属性里的名称来选择。比如我的一个表，第一个工作表内置名字Sheet1，但是命名为"1st" 如图：

那么下边两行代码是完全一致的

Sheet1.Cells(1,1) = 1

Worksheets("1st").Cells(1,1) = 2

不仅仅是少打了几个字母，而且方便了以后随时修改名称。

6. 不要使用复制粘贴

刚开始使用VBA的时候，很多时候都是先录制宏然后进行代码修改，这样就保留了很多类似于 先activate一个表，然后select一个区域，然后copy然后再选择另外一个表进行paste的操作。

但是这个操作也是非常慢的，大概看数据量的不同和直接写入有几十倍以上的速度差。

其实更合适写起来也更方便的代码是，比如复制A1:A100到B1:B100，使用这样的代码

Range("B1:B100").value = Range("A1:A100").value

现在很多地方的excel都越来越大，需要从其他地方导入、处理再生产报告的时间越来越长甚至会卡死，浪费大量时间，这以上几点全部应用以后，相比完全不注意效率写的代码，一个同事在我简单调教以后运行差不多10分钟的代码只需要十几秒。

总之，让VBA运行速度更快的核心就是，停止excel工作表中的一切活动，尽量少读取和写入数据。然后祝大家工作愉快！