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

1、VBA 利用数组优化代码运行效率VBA 让工作效率飞起来！ 专门代写 EXCEL VBA 中小型程序代码。本人以诚信立足 ，另欢迎加友交流学习！ VBA 优化代码运行效率 一、强制声明变量 VBA 并没有要求用户必须声明每个变量， VBA 会自动为每个变量分配数据类型。这是 VBA 程序的一个兼容性的优点。但如果不声明变量，其类型程序在执行时就会消耗更多的内存，相当于消耗效率来换取兼容性。如果需要提升程序效率，在编写代码的时候，应尽量将所有变量显示声明。Sub a()Dim i As Integer ' 声明变量类型Dim j ' 没有声明变量i = 100j = 3000Ms

2、gBox i & '*' & j & '=' & i * jEnd Sub 二、善用常量如果某个数值或字符在程序中反复出现，则尽量声明一个产量来取代该值，在后面代码使用中直接调用常量。具体看下例： Const Pi = 3.141526Sub 常量 ()MsgBox ' 圆的面积为 :' & Pi * 2 2End Sub 三、关闭屏幕更新在单元格中写入数据或批量插入对象时，每执行一句代码屏幕会更新一次，而更新屏幕需要时间。在大多数情况下，完全没有必要更新屏幕状态。开发者可以关闭屏幕更新来提升效率，等待所有

3、过程执行完毕后再恢复屏幕更新即可。控制屏幕更新开、关的属性是：Application.ScreenUpdating=True/False例子 1：Sub 隐藏偶数列 ()Dim col As Long, Tim As LongTim = TimerFor col = 1 To Columns.CountIf col Mod 2 = 0 ThenCells(1, col).EntireColumn.Hidden = True End IfNextMsgBox ' 程序运行了 ' & Format(Timer - Tim, '0.00') & 

4、9;s' End Sub 在我的电脑上运行 0.42S例子 2：Sub 隐藏偶数列一 ()Dim col As Long, Tim As LongApplication.ScreenUpdating = FalseTim = TimerFor col = 1 To Columns.CountIf col Mod 2 = 0 ThenCells(1, col).EntireColumn.Hidden = True End IfNextApplication.ScreenUpdating = TrueMsgBox ' 程序运行了 ' & Format(Timer -

5、 Tim, '0.00') & 's'End Sub 而该例子在我电脑的03 版上面，运行才0.00s,效率提高了很多倍。注意： Application.ScreenUpdating = False这句代码通常放置在循环语句之前，在循环完成后再恢复屏幕更新，否则会影响正常工作。四、利用With 减少对象读取次数例子 1：Sub Macro1()Range('I3').Font.Bold = TrueRange('I3').Font.Italic = TrueRange('I3').Font.Underlin

6、e = xlUnderlineStyleSingle Range('I3').Font.ColorIndex = 5End Sub 例子 2：利用 with来优化Sub Macro1()With Range('I3').Font.Bold = True.Font.Italic = True.Font.Underline = xlUnderlineStyleSingle.Font.ColorIndex = 5End WithEnd Sub 单独执行以上两段代码，执行效率差别不大，但如果存在多段代码，累积起来对程序的效率就会有较大的影响了。五、利用变量来减少对象读取

7、次数如果一个变量在一个过程中多次出现，应考虑用一个变量来替换对象。 因为变量存于内存中，VBA 读取内存数据远远快于对象。如例子 1： Sub a()Dim T As Long, rng As RangeT = TimerFor Each rng In Range('A1:A2000')If rng > B1 Then rng.Interior.ColorIndex = 5 NextC1 = Format(Timer - T, '0.00') & 'S'End Sub 在代码中，单元格B1 给引用了 2000 次，程序执行在我的电脑

8、上是1.38S例子 2： Sub b()Dim T As Long, rng As Range, st As LongT = Timerst = B1For Each rng In Range('A1:A2000')If rng > st Then rng.Interior.ColorIndex = 5 NextC2 = Format(Timer - T, '0.00') & 'S'End Sub 在例子 2 代码中，单元格 B1 仅需要读取一次。 在后面的循环中，单元格B1 不再参与运算，而是在内存中的变量“标准” 在参与运算。

9、该运算速度是 0.17S（我的电脑上） 。六、善用带 $的字符串处理函数在 VBA 中，有两套字符串处理函数， 包含带 '$'和不带 '$'的函数，例如 mid 和 mid$， Left 和 Left$,Right 和 Right$ 。如果不使用带 '$'符号的函数计算字符串，那么 VBA 将字符串作为变体数据来进行计算，而使用带 '$'的函数时，则将字符串当作 string 类型来进行计算，显示变体型数据在计算时需要更多的内存空间。如下面两段代码：Str=mid('Wise',2)Str=mid$('Wi

10、se',2) 第二句在执行效率上会占优势。七、循环中减少步长来提速当使用有针对性的For 循环，即仅仅需要对循环对象中的部分对象进行操作时，应该调整循环的步长来减少循环的次数。对比下面两个例子：例子一： Sub T1()tim = TimerFor i = 1 To 10000If i Mod 2 = 1 Then Cells(i,1).EntireRow.Interior.ColorIndex = 23Next iMsgBox Format(Timer - tim, '0.00') & 's'End Sub 该代码要循环次数是10000 次例子

11、二： Sub T2()tim = TimerFor i = 1 To 10000 Step 2Cells(i, 1).EntireRow.Interior.ColorIndex = 23 Next iMsgBox Format(Timer - tim, '0.00') & 's' End Sub 而例子二循环的次数是 5000 次。实现同样的代码，但却循环的次数有差异，明显是代码二比代码一效率更高。八、利用数组代替单元格对象VBA 处理数组的速度远快于对象的速度，对于可以利用数组来替换对象都尽量使用数组。以下两个例子来对比速度：例子 1： Sub 不及格

12、 ()Dim i As Integer, tim As Longtim = TimerFor i = 2 To Cells(Rows.Count, 1).End(xlUp).Row If Cells(i, 2) < 60 Then Cells(i, 3) = ' 不及格 'Next iMsgBox Format(Timer - tim, '0.00') & '秒 'End Sub 例子 2： Sub b()Dim i As Long, tim As Long, arr1(), arr2()tim = Timerarr1 = Range(B2, Cells(Rows.Count, 2).End(xlUp)ReDim arr2(1 To UBound(arr1), 1 To 1)For i = 1 To UBound(arr1)If arr1(i, 1) < 60