常用的VBA提速技巧

qee用

[编者按]速度是程序设计永恒的热门话题，虽然速度技巧在各种语言之间可以相互借鉴，但差别有时也会很大，比如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是对数组操作中最费时的动作。可以先预算大小，不够或多余时再进行调整。

 

 

 

 

 

[参考文]

EXCELHOME有关文章（具体从略）

请原作者谅解。原创权属于原作者。

Emily

Example:

使用 ADO-SQL 處理 EXCEL 文件的程序架構（實例注釋）

實例3：帶窗體進度條的篩選

http://club.excelhome.net/dispbbs.asp?boardid=2&id=160847&star=1

流星

虽然还是不太理解这个，但是以后用的时候就知道意思了。OK！这个我在EH也有收藏。看来得赶紧学了。谢谢EMILY大姐了。呵呵！[em24][em23][em23][em23]

Emily

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xlproInteractive1_HV05202157.asp

 

True if Microsoft Excel is in interactive mode; this property is usually True. If you set the this property to False, Microsoft Excel will block all input from the keyboard and mouse (except input to dialog boxes that are displayed by your code). Blocking user input will prevent the user from interfering with the macro as it moves or activates Microsoft Excel objects. Read/write Boolean.

Remarks

This property is useful if you're using DDE or OLE Automation to communicate with Microsoft Excel from another application.

If you set this property to False, don't forget to set it back to True. Microsoft Excel won't automatically set this property back to True when your macro stops running.

Example

This example sets the Interactive property to False while it's using DDE in Windows and then sets this property back to True when it's finished. This prevents the user from interfering with the macro.

Application.Interactive = False
Application.DisplayAlerts = False
channelNumber = Application.DDEInitiate( _
    app:="WinWord", _
    topic:="C:\WINWORD\FORMLETR.DOC")
Application.DDEExecute channelNumber, "[FILEPRINT]"
Application.DDETerminate channelNumber
Application.DisplayAlerts = True
Application.Interactive = True

流星

防止使用者干扰程序的运行。嗯。我记得应该还有一个属性是防止按ESC和CTRL+BREAK的。不过我不记得了。呵呵！

流星

GOOD！

对于这句没有用过，3．Application.Interactive = False 禁止交互模式，具体谈谈是什么情况下使用呢？

Emily

QUOTE:

以下是引用流星在2006-7-23 18:09:50的发言：

对于这句没有用过，3．Application.Interactive = False 禁止交互模式，具体谈谈是什么情况下使用呢？

Sub Example()
Dim cel As Range

Application.Cursor = xlWait

Application.Interactive = False
Application.ScreenUpdating = False

' Long Task
For Each cel In [A1:AA9999 ]
    cel.Select
Next

Application.Interactive = True
Application.ScreenUpdating = True
Application.Cursor = xlDefault

[A1].Select

End Sub

流星

没有看出什么效果？

Emily

這個是簡單的例子, 試試在 Worksheet 移動 mouse 選擇 cell

[此贴子已经被作者于2006-7-23 22:33:53编辑过]

konggs

QUOTE:

以下是引用流星在2006-7-23 23:55:52的发言：
防止使用者干扰程序的运行。嗯。我记得应该还有一个属性是防止按ESC和CTRL+BREAK的。不过我不记得了。呵呵！

好像是這個：

Application.EnableCancelKey = wdCancelDisabled
'處理
Application.EnableCancelKey = wdCancelInterrupt