VBA利用数组优化代码运行效率

L罗乐 2017-01-29

展开全文

VBA让工作效率飞起来！

专门代写EXCEL VBA中小型程序代码。

本人以诚信立足，另欢迎加友交流学习！

VBA优化代码运行效率

一、强制声明变量

VBA并没有要求用户必须声明每个变量，VBA会自动为每个变量分配数据类型。这是VBA程序的一个兼容性的优点。但如果不声明变量，其类型程序在执行时就会消耗更多的内存，相当于消耗效率来换取兼容性。
如果需要提升程序效率，在编写代码的时候，应尽量将所有变量显示声明。

Sub a()
Dim i As Integer '声明变量类型
Dim j '没有声明变量
i = 100
j = 3000
MsgBox i & '*' & j & '=' & i * j
End Sub

二、善用常量
如果某个数值或字符在程序中反复出现，则尽量声明一个产量来取代该值，在后面代码使用中直接调用常量。
具体看下例：

Const Pi = 3.141526
Sub 常量()
MsgBox '圆的面积为:' & Pi * 2 ^ 2
End Sub

三、关闭屏幕更新
在单元格中写入数据或批量插入对象时，每执行一句代码屏幕会更新一次，而更新屏幕需要时间。在大多数情况下，完全没有必要更新屏幕状态。开发者可以关闭屏幕更新来提升效率，等待所有过程执行完毕后再恢复屏幕更新即可。
控制屏幕更新开、关的属性是：
Application.ScreenUpdating=True/False
例子1：

Sub 隐藏偶数列()
Dim col As Long, Tim As Long
Tim = Timer
For col = 1 To Columns.Count
If col Mod 2 = 0 Then
Cells(1, col).EntireColumn.Hidden = True
End If
Next
MsgBox '程序运行了' & Format(Timer - Tim, '0.00') & 's'
End Sub

在我的电脑上运行0.42S
例子2：

Sub 隐藏偶数列一()
Dim col As Long, Tim As Long
Application.ScreenUpdating = False
Tim = Timer
For col = 1 To Columns.Count
If col Mod 2 = 0 Then
Cells(1, col).EntireColumn.Hidden = True
End If
Next
Application.ScreenUpdating = True
MsgBox '程序运行了' & Format(Timer - Tim, '0.00') & 's'
End Sub

而该例子在我电脑的03版上面，运行才0.00s,效率提高了很多倍。
注意：Application.ScreenUpdating = False 这句代码通常放置在循环语句之前，在循环完成后再恢复屏幕更新，否则会影响正常工作。

四、利用With 减少对象读取次数
例子1：

Sub Macro1()
Range('I3').Font.Bold = True
Range('I3').Font.Italic = True
Range('I3').Font.Underline = xlUnderlineStyleSingle
Range('I3').Font.ColorIndex = 5
End Sub

例子2：利用with 来优化

Sub Macro1()
With Range('I3').Font
.Bold = True
.Font.Italic = True
.Font.Underline = xlUnderlineStyleSingle
.Font.ColorIndex = 5
End With
End Sub

单独执行以上两段代码，执行效率差别不大，但如果存在多段代码，累积起来对程序的效率就会有较大的影响了。

五、利用变量来减少对象读取次数
如果一个变量在一个过程中多次出现，应考虑用一个变量来替换对象。因为变量存于内存中，VBA读取内存数据远远快于对象。
如例子1：

Sub a()
Dim T As Long, rng As Range
T = Timer
For Each rng In Range('A1:A2000')
If rng > [B1] Then rng.Interior.ColorIndex = 5
Next
[C1] = Format(Timer - T, '0.00') & 'S'
End Sub

在代码中，单元格B1 给引用了2000次，程序执行在我的电脑上是1.38S
例子2：

Sub b()
Dim T As Long, rng As Range, st As Long
T = Timer
st = [B1]
For Each rng In Range('A1:A2000')
If rng > st Then rng.Interior.ColorIndex = 5
Next
[C2] = Format(Timer - T, '0.00') & 'S'
End Sub

在例子2代码中，单元格B1仅需要读取一次。在后面的循环中，单元格B1不再参与运算，而是在内存中的变量“标准”在参与运算。该运算速度是0.17S（我的电脑上）。

六、善用带$的字符串处理函数
在VBA中，有两套字符串处理函数，包含带'$'和不带'$'的函数，例如mid 和mid$，Left 和Left$,Right 和Right$。
如果不使用带'$'符号的函数计算字符串，那么VBA将字符串作为变体数据来进行计算，而使用带'$'的函数时，则将字符串当作string类型来进行计算，显示变体型数据在计算时需要更多的内存空间。
如下面两段代码：