Excel VBA程序设计中提高效率的一些建议

首先，效率的提高应该建立在正确的解决方案和正确的算法的基础上，前者保证了结果的正确性，后者保证了效率。通过改进算法和思路得到的运行效率的提高是以下优化方法的十倍百倍，因此，优化首先应该考虑的算法，其次才是本文以下提到的方法。要特别指出，效率的优化必须是针对关键代码的优化，对于一些在程序执行过程中，只执行很少次数的代码，没有必要牺牲可读性而进行优化。在此基础上，可以通过注意以下一些问题，提高程序的运行效率。

1.   尽量使用Excel的内置函数

应该尽量使用Excel的内置函数，使用Excel内置函数不仅可以提高运行效率，而且可以节省代码数量。对于Excel内置函数可以通过以下方式访问：

Application.函数名()

Application.WorksheetFunction.函数名(myRange)

例如以下求平均值的例子，使用的VBA代码如下：

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.Average(Worksheets(1).Range(“A1:A1000”))

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

2.   尽量减少使用对象引用

在VBA代码中，应该尽量减少使用对象引用，尤其在循环中。每一个Excel对象的属性、方法的调用都需要通过COM接口的一个或多个调用，这些COM调用都是比较费时的，因此，减少使用对象引用能加快VBA代码的运行。可以通过以下途径改进效率：

（1）使用With语句

例如以下语句，可以通过替换为With语句，提高运行效率：

ActiveSheet.Range(″A1:A1000″).Font.Name = “Arial”

ActiveSheet.Range(″A1:A1000″).Font.FontStyle = ″Bold″ 

对应的With语句：

With ActiveSheet.Range(″A1:A1000″).Font

    .Name = ″Arial″

    .FontStyle = ″Bold″

End With

 

（2）使用对象变量

如果一个对象引用被多次使用，则可以通过定义一个局部变量，将此对象用Set 设置为对象变量，以减少对对象的访问。如：

ActiveSheet.Range(″A1″).Value = 100

ActiveSheet.Range(″A2″).Value = 200

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

Dim objSheet As Object

Set objSheet = ActiveSheet

objSheet.Range(″A1″).Value = 100

objSheet.Range(″A2″).Value = 200

 

（3）减少循环中的对象访问

例如以下循环，可以通过设置局部变量或者使用With语句来提高效率。

For k = 1 To 1000

    ActiveSheet.Range(“A1000”).Cells(1,k).Value = k

Next k

则以下代码比上面的要快（使用With语句）：

With ActiveSheet.Range(“A1000”)

    For k = 1 to 1000

        .Cells(1,k).Value = k

    Next k

End With

 

VBA编程中对Excel对象的引用是不可避免的，而且是经常性的工作，例如对Range对象的引用，但同时这种引用又是非常耗时的，例如与使用数组相比较，使用Range对象要慢1000倍到10000倍，因此，一定要避免频繁引用Range对象（例如在一些信息查询、矩阵运算等时候），必要的时候，可以通过数组等方式来替代，在运算开始前将Range的数据读入数组，运算完成后再写入Range。

3.   减少对象的激活和选择

通过录制宏得到的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

4.   关闭屏幕更新

避免不断的刷新屏幕，在向工作薄写数据或者绘图时，锁定屏幕刷新；

如果你的VBA程序需要经常更新屏幕工作表的内容，则关闭屏幕更新是提高VBA程序运行速度的最有效的方法，可以缩短运行时间2/3左右。关闭屏幕更新的方法：

Application.ScreenUpdate = False

请不要忘记VBA程序运行结束时再将该值设回来：

Application.ScreenUpdate = True

5.   提高关键代码的效率

最后要特别指出，不要做不必要的优化。虽然本书给出了很多不同方法执行效率的差异，但千万不要因为追求效率而损失了代码的可读性、清晰性。

效率的优化必须是针对关键代码的优化，对于一些在程序执行过程中，只执行很少次数的代码，没有必要牺牲可读性而进行优化。

对于代码执行效率，千万不要人云亦云，必要时候，自己动手测试一下，结果往往会出乎意料。

6.   代码执行时间的测算

VBA和VB中，没有专门的代码执行事件测算工具和方法，笔者一般是使用Timer函数，其返回值是一个Single类型的数值，代表从午夜开始到现在经过的秒数，此数值包括小数部分，但精确程度在Windows NT，2000和XP下大概接近10毫秒。如果要测试一段代码的执行速度，可以使用如下方法：

#001 Sub MeasureTime()

#002    

#003     Dim Time1 As Single, Time2 As Single

#004     Dim TotalTime As Single

#005     Dim Times As Long

#006     Dim i As Long

#007    

#008     Times = 10000

#009     Time1 = Timer

#010    

#011     For i = 1 To Times Step 1

#012         Mytest1

#013     Next i

#014    

#015     Time2 = Timer

#016    

#017     TotalTime = (Time2 - Time1) * 1000

#018     MsgBox "执行时间: " & TotalTime & " 毫秒（次数：" _

#019         & Times & ")"

#020        

#021 End Sub

#022

#023 Sub Mytest1()

#024    

#025     Dim i As Long

#026     Dim s As String

#027     i = Rnd

#028     s = Format(i, "#.00")

#029    

#030 End Sub

过程MeasureTime可以测试一个过程的执行速度，因为一般一个过程执行会很快，所以使用循环，执行n次（第8行设置），在第12行调用测试的过程，通过循环前的时间（第9行）和循环后的时间（第15行），计算总共执行时间（第17行）。

使用这个方法，就可以做一些测试，看哪些方法执行效率更高。另外，由于Windows的多任务特定，测试时最好关闭其他无关程序，以获得较准确的测试结果