有一次交流群一位朋友问我,为什么我的代码运行了20分钟还没运行完?!怎么优化代码?
我首先问了下数据量,也就几千行,但是一运行就是卡着不动了。最后我检查了代码之后,发现他里面写了个死循环。并不是程序运行的慢,而是程序卡主了。
他静静的等待了20多分钟,最后强退了。
一般程序超过两三分钟没响应,就要检查下代码了,极有可能是死循环。这个时候需要优化代码,或者直接使用更高级的武器--数组。
也有朋友说数组太抽象,还没有学数组,那该如何优化代码速度呢?
下面我就列举几个优化代码速度的常用方法,也是几个VBA编程的好习惯。
方法❶:少用Select和Activate
刚开始学VBA,很多都是录制宏得到的语句, VBA 程序里满屏幕的对象的激活和选择。
例如:
Workbooks("案例工作簿").Activate、WorkSheets("案例").Select、Range("A1").Select
这就好比明明打电话一句话就能解决的事,非要先跑过去,见了本人当面说。
事实上大多数情况下这些激活操作不是必需的。例如:
WorkSheets(″案例″).SelectRange(″A1″).SelectRange(″A1″).Value= "VBA说"
完全可以简化成一句
WorkSheets(″案例″).Range("A1").Value="VBA说"
当然,.Value这个默认属性也可以省略。
WorkSheets(″案例″).Range("A1")="VBA说"
这么一番简化之后,不仅代码更简洁,运行速度也会提高。
➜关键的关键
对隐藏的工作表使用select方法的时候,会报错。所以我们读取数据、写入数据尽量避免使用Select。
方法❷:尽量减少使用对象的引用
每一个 Excel 对象的属性、方法的调用都需要通过 OLE 接口的一个或多个调用,这些OLE 调用都是需要时间的,减少使用对象引用能加快 VBA 代码的运行。
说这么官方你一定听不懂。直接拿例子来说一下:
ThisWorkbook.Worksheets("案例").Range ("a1")="VBA说"
一定没有Range ("a1")="VBA说"效率高,尤其放在循环内部的时候。
那我们怎么解决这个问题,减少对象的引用次数呢?
➜使用With...EndWith结构简化
既能精简代码又能一定程度加快速度。
Sub 不使用With结构() Dim tim1 As Date, tim2 As Date: tim1 = Timer For i = 1 To 10000 Workbooks(1).Sheets(1).Range("A" & i).Value = "欢迎关注VBA说" Workbooks(1).Sheets(1).Range("B" & i).Value = "ID:todayvba" Next tim2 = Timer MsgBox Format(tim2 - tim1, "程序执行时间为:0.00秒"), 64, "时间统计"End Sub
Sub 使用With结构() Dim tim1 As Date, tim2 As Date: tim1 = Timer With Workbooks(1).Sheets(1) For i = 1 To 10000 .Range("A" & i).Value = "欢迎关注VBA说" .Range("B" & i).Value = "ID:todayvba" Next End With tim2 = Timer MsgBox Format(tim2 - tim1, "程序执行时间为:0.00秒"), 64, "时间统计"End Sub
➜使用对象变量
如果一个对象引用被多次使用,则你可以将此对象存储到对象变量中,代码直接从内存中读取对象变量的信息,以减少对对象的访问。
ThisWorkbook.Worksheets("案例").Range ("a1")="VBA说"
可简化为:
set sht = ThisWorkbook.Worksheets("案例")sht.Range ("a1")="VBA说"
方法❸:关闭屏幕刷新
VBA代码在运行过程中,不断更单元格中的值。如果不关闭刷新,每更新一次,屏幕就会自动刷新为最新结果。
不过有的人就喜欢把屏幕刷新打开,他感觉点按钮运行代码,屏幕不断闪动,更能体现出来自动化的乐趣。
对于这种朋友,我送给他两个字。
关闭开启屏幕刷新的语句如下,注意不要弄反了,先关闭在再开启。
Application.ScreenUpdate = False
请不要忘记 VBA 程序运行结束时再将该值设回来:
Application.ScreenUpdate = True
➜小技巧
这两句代码记不下来不用背,当出现你想输入的语句之后,直接【按空格】或者【鼠标点击】直接输入。