VBA入门到进阶常用知识代码总结47

第47集 程序优化与提速
205、 减少对象的调用
调用对象是非常非常的耗费资源的，所以一定要尽可能的少调用对象。包括：
工作簿、工作表、单元格以及外引用对象。

Sub 在循环中调用单元格()
Dim x As Integer, k
Dim t
t = Timer
For x = 1 To 20000
k = [a1]
Next x
Debug.Print Timer - t
End Sub

Sub 在循环外调用单元格()
Dim x As Long, k, m
Dim t
t = Timer
m = [a1]
For x = 1 To 2000000
k = m
Next x
Debug.Print Timer - t
End Sub

'上面两个程序运行的结果一样，但速度却差了 28.12/0.03=937倍
206、 减少计算次数
'程序的运算速度和计算次数有着很大的关系，所以要尽可能的减少计算的次数
'能在循环外计算出结果的，就不要在循环内计算。
Sub 在循环中计算()
Dim a, b, c, x, t, k
t = Timer
a = 100: b = 2: c = 3
For x = 1 To 1000000
k = a ^ b + c
Next x
Debug.Print Timer - t
End Sub
Sub 在循环外计算()
Dim a, b, c, x, t, k, m
t = Timer
a = 100: b = 2: c = 3
m = a ^ b + c
For x = 1 To 1000000
k = m
Next x
Debug.Print Timer - t
End Sub
207、 禁止闪屏
Application.ScreenUpdating当设置属性值为false时，可以禁止程序运行过程中的屏幕闪动
进而提高运行速度
注意：只有对会引起闲屏操作的代码才有效，否则可能还会拖慢程序的速度
Sub 没有禁闪屏()
Dim x, t
t = Timer
For x = 1 To 100
Sheets(2).Select
Next x
[a1] = Timer - t
End Sub
Sub 禁闪屏()
Dim x, t
t = Timer
Application.ScreenUpdating = False
For x = 1 To 100
Sheets(2).Select
Next x
[b1] = Timer - t
Application.ScreenUpdating = True
End Sub
208、 增加变量的声明类型
Sub 没声明变量类型()
Dim arr, brr(1 To 6800, 1 To 3), i, s, x
Dim t
t = Timer
arr = Range(“a1”).CurrentRegion
For i = 1 To UBound(arr)
s = Replace(Replace(arr(i, 1), “(”, “’"), “)”, “”)
brr(i, 1) = Split(s, "”)(0)
brr(i, 2) = Split(s, “")(1)
brr(i, 3) = Split(s, "”)(2)
Next
[c1].Resize(UBound(arr), 3) = “”
[c1].Resize(UBound(arr), 3) = brr
MsgBox Timer - t
End Sub
Sub 变量声明后()
Dim arr, brr(1 To 6800, 1 To 3) As String, i%, s , x A s I n t e g e r D i m t t = T i m e r a r r = R a n g e ( " a 1 " ) . C u r r e n t R e g i o n F o r i = 1 T o U B o u n d ( a r r ) s = R e p l a c e ( R e p l a c e ( a r r ( i , 1 ) , " ( " , " ∗ ′ " ) , " ) " , " " ) b r r ( i , 1 ) = S p l i t ( s , " ∗ " ) ( 0 ) b r r ( i , 2 ) = S p l i t ( s , " ∗ " ) ( 1 ) b r r ( i , 3 ) = S p l i t ( s , "