[干货分享]VBA代码优化常用技巧（vba提升代码运行速度）

天下武功，唯快不破

在这个时间就是金钱、效率就是生命的年代，浪费时间就是罪恶，尤其是对于程序猿来说，如果自己开发的程序，虽然功能实现了，但是在执行上还存在卡、慢的现象，那么不失为一件颇让人沮丧的事情。

大家都知道在Office里面可以用VBA进行二次开发，为日常工作提供了一条快捷的途径，善用它，可以大大的改善我们的工作体验和质量。今天跟大家分享的不是VBA本身，而是一些VBA代码常用的优化技巧，大神请绕路（也欢迎指点），VBA初学者或者爱好者可以看看，哪怕是得到一点点启发和借鉴，那也是极好的。

1.尽量调用内置功能，即，使用系统提供的属性、方法和函数

很多时候我们要实现某些功能，如果本身对excel不熟悉的话，可能会想办法去实现某些看上去很复杂的功能，殊不知，其实excel本身已经提供了类似的功能，有的时候可能仅仅是一个函数就解决了的事情，结果你搞半天，说不定辛苦弄出来，结果效率和效果还没内置的好。

在很大程度上，这简直是一定的，试想，你用的微软的软件，你编出来的东西能比别人开发人员还NB（不乏天才，所以我有所保留）？所以碰到这种情况还是省省心吧，能调用内置的尽量调用内置。

也有例外，如：

sub 获取数组最大值() 'VBA法

dim arr(),temp as byte,i,j,tim as long

for j=1 to 100000 '循环100000次，从而可以更好的进行时间比较

arr=array(1,7,8,6,9,3,5,7,6,8,9,4,1,2,4) '数组的值

temp=arra(1) '将数组中的第一个值赋予变量temp 'temp=worksheetfunction.max(arr) 调用max函数，效率反而偏低

for i=1 to UBound(arr) '循环比较数组中所有元素

if arra(i)>temp then temp=arr(i) '如果数组中某元素大于变量，即将该值赋予变量temp

next i

next j

end sub

先前在帮助一些学生朋友解决VBA难题的时候，碰到过别人计算机老师出题，为了偷懒，直接挑几个工作表函数，让学生拿代码给编出来，这是典型的放着内置的不用而自己编的案例，当然老师的出发点可能是训练学生的逻辑思维和算法理解能力。

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

• 在循环中尽量减少对对象的引用，多用with... end with语句,圆点越少越省时

• 用set语句将反复引用的对象设置为对象变量，因为变量存在内存中

• 利用对象循环代替单元格循环，如判断单元格批注，可以循环批注comments对象，直接找到每个批注，再利用parent获取该批注的父对象单元格即可。

1. sub批注循环()

2. dim com as comment,address asstring

3. for each com in activeSheet.comments

4. address=address&com.parent.address(0,0)&chr(10)

5. next

6. msgbox address

7. endsub

9. sub 单元格循环()

10. dim rng as range,address as string,bl as boolean

11. on error resume next

12. for each rng in activesheet.usedrange

13. bl=rng.comment.visible

14. if err=0 then address=address&rng.address(0,0)&chr(10)

15. err.clear:

16. next rng

17. msgbox address

18. end sub

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

• 少用select和activate语句

这个通常会出现在我们自己录制宏代码的时候，excel记录了大量的点击和激活动作，而这些都不是必须的，大多数都可以省略掉（有些操作必须用到激活或者选择的除外）

4.关闭屏幕更新

就是程序运行的时候会看到屏幕在闪烁，这个会影响计算机的性能，也会闪瞎眼，所以基本上编程的人都会默认使用这一条（尤其是对于运行时间比较长的，短的可以视情况不加）

Application.ScreenUpdating=False '通常放在循环语句前
...Application.ScreenUpdating=True

5.变量的使用

• 强制变量声明（OptionExplicit）：在设置里面勾选，会节省时间，自动带出，而不是自己手动敲出来的

• 尽量显式声明变量：事前连接--early binding；定义object类型的属于事后连接，late binding。通常事前定义会节省时间，但往往会有兼容性问题，后定义的适配性比较好

• 选择合适的变量类型：缺省会默认Variant型，但是会增加内存耗用，能明确的就具体指明

• 善用变量：对于反复出现的数值或者字符串，尽量声明一个常量来取代该值，直接调用，修改的时候也能做到一改都改，而不用改动多个地方

讲到这里，想强调一下，有时候为了效率，有的人喜欢在代码编写的时候采用一些简写或者隐式声明，这个确实会让我们少敲不少字，但是带来的另外一个问题是，一旦代码出现了问题，调试或者找错误的时候就会很抓狂了，可能从整体的时间效率上反而不划算，所以还是建议大家养成良好的编程习惯，变量都写清楚，勤备注，这样易读性和可维护性好

6.善用带$的字符串处理函数

VBA中有2套字符串处理函数，带$和不带$的。

如果不带$的的函数处理字符串，则VBA将字符串作为variant数据计算，用带$的，则当string处理。而前者需要耗费更多的内存

7.善用循环中的步长减少循环次数

如判断奇偶，有了步长，就可以省去判断语句，加快速度

讲到循环，还有一个类似的例子：

就是我们在遍历某个区域的时候，如果能用目标区域（Target）与已用区域（UsedRange）的交集（Intersect）区域来循环的话，会缩小循环的范围，规避掉一些空的单元格的比对

8.利用数组代替单元格对象

将中间过程存在数组中，直接从内存调用，最后再读取

这个应该是应用的最多的，效果是最明显的，少了单元格的交互，会省不少事。

当然字典作为特殊的数组存在形式，一样的也能来做这个事情

实例：

对几千个个学生中不及格的成绩标示“不及格”

sub 对小于60分成绩进行注释（）

Dim i as integer,tim as long,arr1(),arr2()'将成绩赋予数组变量arr1=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 then arr2(i,1)="不及格"

next i

'将第二个数组的值赋予单元格

range([c2],cells(rows.count,2).end(xlup).offset(0,1))=arr2

end sub

9.重复调用UDF时才使用它

有点绕啊，UDF，即user-defined-function，即用户自定义函数

为了程序运行的高效，我们通常会把长代码拆成几个子代码或者自定义函数，来相互调用。达到方便调试和互相引用的目的。UDF适合多次调用时，否则调用的速度甚至比执行的速度更慢。

比较短小的和仅使用一次的function，建议直接内置在sub代码中而不是调用UDF。

10.将不改变值或者属性的语句放到循环语句外

这一点往往会被人忽略，可能循环的重要语句本身就一两句，其他无关语句很多的话，在循环的时候会反复check，这个过程会耗时

11.利用长度计算判断单元格是否为空

第一次看到罗刚军老师用这个的时候百思不得姐，上论坛问了很多人也不知道。后来在实践中，逐渐发现这个效率更高（很隐晦，比较难得找合适的例子，老师能发现这个确实是下了功夫的），而且在很多时候用len方法还能解决别的一些比较棘手的问题，比如判断非空单元格的时候或者某些对象的时候，我们有时候搞不清楚返回的值的属性，

xx=""

xx is empty

xx is nothing

都不如一句len（xx）=0来得简便

range("a1")="" '建议len法，此法效率更高len(range("a1"))=0 '.value省略,range的默认属性

好了说了这么多，想必大家都跃跃欲试了，那么怎么知道自己优化结果是积极的呢？我们可以测试啊，下面附上测试代码：

程序运行时间测试代码：

sub aaa()

dim tim as long

tim=timer '获取当前时间

for ...

next

msgbox format(timer-tim,"0.00")&"秒" '执行报告时间

end sub

通常有2种方法timer函数和time函数，网上很多人问有啥区别，搞不清楚，这里简单解释一下（理解有误的话，望高手不吝赐教）：

1. 时间很短时，可以用timer函数，返回秒，即相对于当天相对午夜0点时候经过了多少秒（感觉时间长了，统计不是蛮准，自己编程体会）

2. 时间比较长时，可以用time，time是按照时间如22:58:30这样记录的，即运行前后电脑时间相减

更多精彩，敬请关注微信号Excel-365，后续有更新，会及时分享！