Excel VBA程序设计中提高效率的方法

使用VBA代码，能做很多函数处理不到的，提高Excel的工作效率。但是VBA代码运行也需要时间，提高代码执行效率也非常重要。当然，效率的提高应该建立在正确的解决方案和正确的算法的基础上，前者保证了结果的正确性，后者保证了效率。通过改进算法和思路得到的运行效率的提高是以下优化方法的十倍百倍，因此，优化首先应该考虑的算法。要特别指出，效率的优化必须是针对关键代码的优化，对于一些在程序执行过程中，只执行很少次数的代码，没有必要牺牲可读性而进行优化。在此基础上，可以通过注意以下一些问题，提高程序的运行效率。
1.尽量使用Excel的内置函数应该尽量使用Excel的内置函数，使用Excel内置函数不仅可以提高运行效率，而且可以节省代码数量。对于Excel内置函数可以通过以下方式访问：Application.函数名()Application.WorksheetFunction.函数名(myRange)例如以下求平均值的例子，使用的VBA代码如下：For Each c In Worksheet(1).Range(″A1:A1000″)TotalValue = TotalValue＋c.ValueNextAverageValue = 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语句：WithActiveSheet.Range(″A1:A1000″).Font.Name=″Arial″.FontStyle=″Bold″EndWith（2）使用对象变量如果一个对象引用被多次使用，则可以通过定义一个局部变量，将此对象用Set设置为对象变量，以减少对对象的访问。如：ActiveSheet.Range(″A1″).Value=100ActiveSheet.Range(″A2″).Value=200则以下代码要比上面的要快：Dim objSheet As ObjectSetobjSheet =ActiveSheetobjSheet.Range(″A1″).Value=100objSheet.Range(″A2″).Value=200（3）减少循环中的对象访问例如以下循环，可以通过设置局部变量或者使用With语句来提高效率。Fork=1To1000ActiveSheet.Range(“A1000”).Cells(1,k).Value=kNextk则以下代码比上面的要快（使用With语句）：With ActiveSheet.Range(“A1000”)For k = 1 to 1000.Cells(1,k).Value = kNext kEnd With  VBA编程中对Excel对象的引用是不可避免的，而且是经常性的工作，例如对Range对象的引用，但同时这种引用又是非常耗时的，例如与使用数组相比较，使用Range对象要慢1000倍到10000倍，因此，一定要避免频繁引用Range对象（例如在一些信息查询、矩阵运算等时候），必要的时候，可以通过数组等方式来替代，在运算开始前将Range的数据读入数组，运算完成后再写入Range。
3.减少对象的激活和选择通过录制宏得到的VBA代码充满了对象的激活和选择，例如Workbooks(XXX).Activate、Sheets(XXX).Select、Range(XXX).Select等，但事实上大多数情况下这些操作不是必需的，因此，应该尽量避免这样的代码。例如：Sheets(″Sheet3″).SelectRange(″A1″).Value=100Range(″A2″).Value=200可改为：WithSheets(″Sheet3″).Range(″A1″).Value=100.Range(″A2″).Value=200EndWith
4.关闭屏幕更新避免不断的刷新屏幕，在向工作薄写数据或者绘图时，锁定屏幕刷新；如果你的VBA程序需要经常更新屏幕工作表的内容，则关闭屏幕更新是提高VBA程序运行速度的最有效的方法，可以缩短运行时间2/3左右。关闭屏幕更新的方法：Application.ScreenUpdate=False请不要忘记VBA程序运行结束时再将该值设回来：Application.ScreenUpdate=True
5.提高关键代码的效率最后要特别指出，不要做不必要的优化。虽然本书给出了很多不同方法执行效率的差异，但千万不要因为追求效率而损失了代码的可读性、清晰性。效率的优化必须是针对关键代码的优化，对于一些在程序执行过程中，只执行很少次数的代码，没有必要牺牲可读性而进行优化。对于代码执行效率，千万不要人云亦云，必要时候，自己动手测试一下，结果往往会出乎意料。
6.代码执行时间的测算VBA和VB中，没有专门的代码执行事件测算工具和方法，笔者一般是使用Timer函数，其返回值是一个Single类型的数值，代表从午夜开始到现在经过的秒数，此数值包括小数部分，但精确程度在Windows NT，2000和XP下大概接近10毫秒。如果要测试一段代码的执行速度，可以使用如下方法：Sub MeasureTime()   Dim Time1 As Single, Time2 As Single  Dim TotalTime As Single  Dim Times As Long  Dim i As Long   Times = 10000  Time1 = Timer    For i = 1 To Times Step 1    Mytest1  Next i   Time2 = Timer  TotalTime = (Time2 - Time1) * 1000  MsgBox "执行时间: " & TotalTime & " 毫秒（次数：" & Times & ")"  End Sub
Sub Mytest1()    Dim i As Long  Dim s As String  i = Rnd  s = Format(i, "#.00") End Sub
过程MeasureTime可以测试一个过程的执行速度，因为一般一个过程执行会很快，所以使用循环，执行n次，通过循环前的时间和循环后的时间，计算总共执行时间。使用这个方法，就可以做一些测试，看哪些方法执行效率更高。另外，由于Windows的多任务特定，测试时最好关闭其他无关程序，以获得较准确的测试结果。
参考自：马维峰（博客园）