VBA提高速度的技巧

fujison

[编者按]速度是程序设计永恒的热门话题，虽然速度技巧在各种语言之间可以相互借鉴，但差别有时也会很大，比如VC中由于字符串的存储方式决定了判断空串使用len函数更快，同样，由于C内置了大多数API函数，使用API函数速度也会更快。此外，从不同的角度，往往也会产生或得出截然不同的效果和取向，凡此种种，要求程序人员在实际应用时要灵活对待，没有绝对的可以或不可以。这里收集了一些最为常用的提速技巧，谨供大家参考。
一、让代码“专注”运行
1．Application.ScreenUpdating = False关闭屏幕更新
2．Application.EnableEvents = False 禁止触发事件
3．Application.Interactive = False 禁止交互模式
第一句大家都知道用了，没什么说的，第二句有时候是必需要用的，但也有可用可不用的情况，记得用上哦，第三句不被人注意，虽然效果不如前两句明显，但也还是有一定作用的。
无论用了哪一种，要记得在程序结束时改回来为True哦。
二、单元格（区域）写法的选择
单元格（区域）写法有3种，以A1单元格为例，可以使用
1．Cell对象写法：cells(1,1)
2．Range对象写法：Range(“A1”)
3．名称写法：[A1]
三种写法各有优缺点，但以取值速度论，cells(1,1)最快，[A1]写法最慢，Range(“A1”)比cells(1,1)稍慢（优点是在VBE中输入可以显示属性方法等快速输入信息）。
此外，要明示对象属性，这是一种好的编程习惯，不要依赖于对象的默认属性，下面两句，第二句要比第一句快得多：
x= cells(1,1)
x= cells(1,1).value
三、比较语句
1．分解IF语句
1)IF A AND B THEN写成
IF A THEN
IF B THEN
(2)IF A OR B写成
IF A THEN
...
ELSE IF B THEN
或者
IF A THEN GOTO D1
IF B THEN GOTO D1
GOTO D2
D1:
....
呵呵，我们是讲提速，不要嫌麻烦哦。
2．并列的条件使用elseif或select,elseif写起来麻烦，但速度优于select
3．将最可能的条件及容易计算的条件放在前面
1）上面分解后的A应当比B出现的机会更大，或者需要进行计算更容易计算出结果
2）select语句放在前面的case也应这样
4．Iif速度慢于if/else
四、循环语句
1．For 语句快于DO/WHILE，这是因为FOR语句的增量部分是VBA自带的，特别是步长为1的更明显
2．For/each快于for/to
五、使用WITH语句
with语句不仅仅是简化了写法，速度也大大提升了。
六、尽量不用Variant类型
这不用多说吧，使用Option Explicit是一个好习惯。
给返回值一个明确的类型，会对提速有好处。虽然有时候我们觉得不明显，这是很多人使用left$，而不用left，使用int%,而不用int的原因（你是否对这种写法好奇过？）。但如果一开始你就没有DIM，这就很有必要了。
七、关注内存
对占用内存较多的对象变量，不要时要记住set=nothing
如果你对你的机器内存的容量没有信心，应避免使用递归程序，递归的过程会吃掉大量的内存。
八、使用工作表函数（方法）
有些工作表函数（方法）速度是很快的，比如FIND，VLOOKUP等，要记得使用它们，不要花力气去做不讨好的事。
当使用工作表函数时，操作对象应避免使用内存变量，那样反而慢。
九、划零为整
当需要对较大区域进行相同的操作时，可以先使用union等方法收集，一次进行。在Range对象上尤其明显，内容、格式变更，删除等等一个一个的处理要比把这些选定后一起处理慢得多的多。
十、使用内存数组
1．内存变量的运算速度大大快于RANGE对象。
1）将RANGE数据写入内存数组。下面两句将生成一个65536行，6列的数组。用这种方法产生的数组都是两维数组，即使引用的RANGE只有一行或一列。下标始于1，不受option base设置的影响。arr必需声明为Variant类型。
Dim arr()
arr=range(“A1:F65536”)
2）将内存数组数据写入RANGE。在内存数组经过计算处理后，写回时只需下句就可以了。
range(“A1:F65536”)= arr
2．非数组变量快于数组变量。当数组很大时，根据下标提取数值会比从单个变量慢得多，这时可以把需要多次使用的数组值先赋给内存变量。
3．减少使用REDIM的次数。REDIM是对数组操作中最费时的动作。可以先预算大小，不够或多余时再进行调整。

[ 本帖最后由 fujison 于 2011-3-3 09:53 编辑 ]

guoybb

呵呵……感谢分享

无辣味水煮鱼

有意思，原来在编写的过程中不经意的一些小动作会导致速度的差异。

byl2005

学习了
1.先期绑定优于后期绑定
2.IF…Then…结构优于IIf函数
3.非标写法优于标准写法

香川群子

使用内存数组时：

2．非数组变量快于数组变量。
当数组很大时，根据下标提取数值会比从单个变量慢得多，
这时可以把需要多次使用的数组值先赋给内存变量。


这个真有这么回事。

而且即使数组很小，比如arr(100)，也是差异巨大。（在几百万次循环时，差0.1秒左右。）

原因是，从数组中返回内容，是首先根据二分法去搜寻下标值所在位置的。

呵呵。

香川群子

九、划零为整
当需要对较大区域进行相同的操作时，可以先使用union等方法收集，一次进行。在Range对象上尤其明显，内容、格式变更，删除等等一个一个的处理要比把这些选定后一起处理慢得多的多。


…………
这个是正确的，但是要注意，不要用循环的方法，无限制地进行union操作……

越到后面速度越慢。


解决的办法是，一次循环增量控制在50-100次。
即，union操作50-100次以后就停止，然后对union区域进行格式操作，
操作完成后清空union range，从头再来。

即，分段循环处理。


呵呵。这个是我自己的操作体会。

香川群子

七、关注内存

对占用内存较多的对象变量，不要时要记住set=nothing

对数组变量，可以使用 Erase arr 的语句进行清空处理。

……
如果你对你的机器内存的容量没有信心，应避免使用递归程序，递归的过程会吃掉大量的内存。

这个是正确的，但一般说也没必要害怕或拒绝递归的使用。

因为递归的代码简洁的算法优点，是普通循环代码无法替代的。

香川群子

三、比较语句

1．分解IF语句
1)IF A AND B THEN C写成
   If A Then
        If B Then
          C
        End if
     End if

或者
If A Then If B THEN C

主要是循环代码中的if这样处理比较好。
如果仅仅是一次性使用的，就无所谓啦。


(2)If A OR B Then C写成
     If A Then
           C
        Else
            If B Then
               C
            End If
        End If

或者
If A Then C Else If B Then C

或者：
IF A THEN GOTO D1
IF B THEN GOTO D1
GOTO D2
D1:
....


呵呵，我们是讲提速，不要嫌麻烦哦。



2．并列的条件使用elseif或select,elseif写起来麻烦，但速度优于select

呵呵，是的，我已经习惯ElseIf 的写法了。


3．将最可能的条件及容易计算的条件放在前面
1）上面分解后的A应当比B出现的机会更大，或者需要进行计算更容易计算出结果
2）select语句放在前面的case也应这样

呵呵，这个我一直是这么做的。


4．Iif速度慢于if/else

呵呵，这个显然如此。
但如果偶尔用一次，而不是在循环中反复使用，就没有关系了。

yjlexcelhome

多谢整理.{:soso_e163:}

香川群子

好帖！

但是没有多少人关注啊。