VBA-加快你的代码运行速度--转自ExcelHome

原文链接：加快你的代码运行速度-Excel VBA程序开发-ExcelHome技术论坛 -

致敬，感恩，ExcelHome，在那里学到了很多

主题：VBA-加快你的代码运行速度​​​​​​​

我们知道,用VBA实现同一个效果可能有多种不同的方法,写出一个最有效率的代码是大家都追求的,但是假如我水平有限,经验不足,实践不多,或者逻辑线路的设计不够简洁清爽,是不是就无法大幅提高代码速度?当然不是,如果你学会使用以下技巧,即便你的代码设计水平一般,也可以让它的运行大大加快.

首先记住一个原则: 并不是代码写得越短就运行越快!

1)养成声明变量的习惯,当你声明非Variant的变量类型时,会少占用内存,从而加快代码速度.同时在声明数值类型变量时,尽量避免声明浮点类型变量,因为用到浮点运算器会拖慢速度,比如Currency类型就会Single类型运算快,因为前者不用浮点运算处理器.

2)在指向单元格时,[A1]看上去写得比Range("A1")简短好看,但是写成后者其实运行更快.

原因是:[A1]实际运行的是:Application.Evaluate("A1"),要比写Range("A1")运行速度慢约70%.

所以再请记住这句话:并不是代码写得越短就运行越快!

3)大家可能经常看到的在Sub开始的两条语句可以大大加快代码速度:

Application.ScreenUpdating = FALSE   '禁止屏幕刷新

Application.Calculation = xlCalculationManual   '计算模式为手动

一般在Sub结束前改回:

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = TRUE

4)在引用一个集合目标体(如Worksheets)时,用索引号比用其名称速度会快,如果你明确知道一个"总结"工作表的索引号是1的话,

Worksheets(1)比用Worksheets("总结")速度要快很多,但是索引号的运用会有风险因为表的顺序可能会因增删工作表而经常改变,所以往往实际运用中用工作表名称的方法会更安全更容易些.

5)尽可能运用常量,而不是变量.前面说过声明变量,但是如果你确定这个变量是恒定的,比如你要用的一个路径恒定是C:\MyPath\,则声明:

Const Fp as String = "C:\MyPath\" 比用 Dim Fp as String速度更快!

6)前期绑定比后期绑定速度要快,比如:

Dim WordObj As Word.Application 比用 Dim WordObj As Object 要更快,所以尽可能在VBE的Tools --> References中建立目标索引后直接在代码中引用.

7)For each循环比用For i = 1 to x要快!以下两段代码前者比后者快!

Dim WS as Worksheet

For Each WS In Worksheets

    MsgBox WS.Name

Next WS

------------------------------------------

Dim i as Integer

For i = 1 To Worksheets.Count

    MsgBox Worksheets(i).Name

Next i

8)尽量不用Selection,基本上EXCEL VBA中很少有必须使用Selection的,凡是前一行是某某.Select,后面接着Selection.干嘛干嘛的,都可以直接去掉Select和Selection,如用:

Range("A1").Font.Bold = True

而不是:

Range("A1").Select

Selection.Font.Bold = True

9)当对某一目标进行多次操作时,尽量用With...End With,如:

With Worksheets("Sheet1").Range("A1")

    .Font.Bold = True

    .Value = 123

End With

10)EXCEL自带函数运行比自己在VBA里写的函数运行快!如:

MySum = Application.WorksheetFunction.Sum(Range("A1:A100"))  

比以下速度快!

For Each C In Range("A1:A100")

    MySum = MySum + C.Value

Next C

速度是程序设计永恒的热门话题,使用VBA编程当然也不例外,这里就将在网上收集到的VBA编程常用提速的方法与自己在VBA编程时的心得整理一下,上传到这里.

一、让代码"专注"运行

1．将Application.ScreenUpdating设定为False,关闭屏幕更新.这个是最基本的加速方法,在程序结束设还原时设定为True.

2．将Application.EnableEvents设定为False,禁止触发事件.这个在有其它自定义事件时,如定义了Worksheet_Change时,就有需要设定,还原时设定为True.

3．将Application.Interactive设定为False,禁止交互模式.当我们运行程序时,如删除有数据的工作表而不需要提示,这时就可以使用,见意这段代码只加在需要的地方,之后就立即开启.

4．将Application.Calculation设定为xlCalculationManual,将计算模式设定为手动.当工作簿中有大量公式时,这样的设定提速特别明显,还原时设定为xlCalculationAutomatic.

二、单元格（区域）写法的选择

单元格（区域）写法有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语句

如:

IF A THEN

IF B THEN

优于

IF A AND B THEN

与

IF A THEN

...

ELSE IF B THEN

优于

IF A OR B then

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

3．将最可能的条件及容易计算的条件放在前面

上面分解后的A应当比B出现的机会更大,或者需要进行计算更容易计算出结果,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是对数组操作中最费时的动作。可以先预算大小,不够或多余时再进行调整。

● 判断空字符串

判断空字符串的常用方法是使用下面的语句：

If my_string = "" Then...

但是请采用另外一种方法，就是判断字符串的长度是否为0，这要比前者运行快，代码是：

If Len(my_string) = 0 Then ... 。

● 使用With命令引用多次使用的对象

这要比在每条语句中都完整地引用对象名称执行速度快许多！

● 少有字符串操作函数

尽可能少地使用字符串操作函数，它们运行很慢。

● 定制Select Case语句

Select Case是处理多重条件判断的语句，请将经常要用到的选择排列在前面的选项，以增加该选项提前被选择命中的机会。

● 尽可能的使用by ref参数调用函数和子程序

● 将从不需要的表单设置为nothing

如果有许多要标明的表单，采用这个策略，将节省内存并减少运行时间。如果只有少数几个表单，请将它们全部装载进内存并隐藏之，这样将在随后的使用中速度更快。

● 预想一下程序的速度

对程序的运行速度进行想象是非常重要的，设想一下点击按钮后，等待10秒钟。这将是一个非常长的时间！请添加一个进程条显示程序的进展情况，用户就不会感到等待。

● 使用mid$函数而不是mid

如果需要执行许多字符串/文件操作，请使用mid$（以及trim$等等）函数而不是mid。因为后者将数据类型看作是variant 而不是 string，速度将慢3倍。

● 尽快显示启动表单

尽快地显示启动表单可以使程序看起来很快。在表单的Load事件中使用Show命令，从而在执行长时间的启动计算工作前就能显示出程序界面。

● 尽可能地在Form_Load事件中放置少量的代码

● 使用splash技术过渡中间操作

如果初始化的表单要运行很长时间，请先立即显示一个splash画面，然后在初始表单完全装载后，再去除它。关于如果创建不同种类splash画面的资料，请查阅Advanced Visual Basic Techniques。

● 在模块中将子程序分组

当一个程序调用另外一个程序时，另外的程序所在的模块将被装载。如果一个程序需要调用多个不同模块中的程序，那么所有的这些模块都要被装载。因此，将所有相关的程序放置在一个模块中，就可以实现一次装载。

● 不要浪费内存

有时，利用更多的内存可以使程序变快，但是有时却不然。在实际中，如果给程序分配了它不能适应的过多内存，就会严重地影响程序速度。

● 设置AutoRedraw为False

为了节省内存的使用，请将AutoRedraw设置为False。如果设置为True，对于复杂的绘图操作时，刷新屏幕的速度将很快。

● 设置ClipControls为False

● 使用Moveto组件替代设置Left和Top属性

● 隐藏正在修改的控件

当需要修改一系列空间的外观属性值时，请先将此控件执行隐藏操作。完成修改工作后，再使之变为可见。

● 使用临时变量引用多次使用的复杂表达式

比如，需要设置对象SelectedEmployee.NextOfKin.HomeInformation.Address几个属性的数值，请不要多次引用这个长长的表达式，请使用：

Dim addr As AddressInfo

Set addr = SelectedEmployee.NextOfKin.HomeInformation.Address

addr.Street = txtStreet.Text

addr.City = txtCity.Text

addr.State = txtState.Text

addr.Phone = txtPhone.Text

● 缓存多次使用的属性值

如果程序需要多次引用txtLastName.Left的值，请将数值保存在变量中，然后引用这个变量。变量的存取要比属性值的存取在速度上快很多。

● 使用Line (x1, y1)-(x2, y2), , B来绘制一个box，而不要画4次。

● 尽可能地使用Image控件替代PictureBoxes

Image控件消耗较少的内存。

● 使用Frame控件包容其他控件，不要使用PictureBoxes控件做同等工作。前者消耗内存较少。

● 使用控件数组

对于不很重要的控件，请使用控件数组来引用它们。比如，许多表单都包含许多label，请将它们都放入一个控件数组中。一个包含10个控件的控件数组要比10个单独的控件占用更少的内存。

● 使用定时器在后台执行长时间、低级别的运算工作。

● 使用注释，并命名富有含义的变量。

详细的注释、富有含义的变量名以及空白行，不会增加编译程序的长度，反而能大大地提高程序的可读性。

● 消除掉不使用的变量和代码，因为保留它们将消耗内存。

● 使用DoEvents语句

使用DoEvents语句从而运行运行在长时间的进程时可以让用户执行其他的操作。

● 快速搜索目录

为了快速搜索目录，请使用如下相关API函数：FindFirstFile、 FindNextFile 和 FindClose 。

● 大小写比较

UCase$和LCase$函数用于执行大小写比较，但使用下面的API函数将更快：

Declare Function CharLower Lib "user32" _ Alias "CharLowerA" (ByVal lpsz As String) As String

Declare Function CharUpper Lib "user32" _ Alias "CharUpperA" (ByVal lpsz As String) As String

● 使用With语句引用对象

下面的语句在引用长表达式对象时，速度更快：

With SelectedEmployee.NextOfKin.HomeInformation.Address

.Street = txtStreet.Text

.City = txtCity.Text

.State = txtState.Text

.Phone = txtPhone.Text

End With

● 使用ByRef方式传递参数数值，而不要使用ByVal方式

当使用ByRef方式时，程序传递数值的地址。而使用ByVal时，程序必须要复制一份数值的拷贝，然后再做传递工作。通常，传递地址要比传递拷贝速度快。

● 使用*而不是^执行简单的整数幂运算

比如，使用语句A=B*B，而不是A=B^2，前者速度要快一些。

● 合成长字符串

如果需要建立一个长字符串，请先一块块地生成，最后再进行合并工作。比如，下面的子程序AddText1, AddText2的功能是在字符串后添加文本，那么如下的代码：

Dim txt As String

txt = AddText1(txt)

txt = AddText2(txt)

txt = AddText3(txt)

要比下面的代码花费更多的时间：

Dim txt As String

Dim txt1 As String

Dim txt2 As String

Dim txt3 As String

AddText1(txt1)

AddText2(txt2)

AddText3(txt3)

txt = txt1 & txt2 & txt3

在第1段代码中，AddText子程序必须要对长字符串进行操作。而第2段代码只需要操作相对短小的字符串。

● 保存数学运算的中间结果

比如，下面的一段代码：

Xsquare = x * x

Ysquare = y * y

a = 2 * Xsquare + 3 * Ysquare

b = 3 * Xsquare + 5 * Ysquare

If a + b > 50 Then ...

将比下面的代码运行速度快：

If 2 * x * x + 3 * y * y + _ 3 * x * x + 5 * y * y > 50 _ Then ...

● 在Loop循环上界中使用数值而不是函数表达式

在使用Loop循环，不要使用函数来判断终止数值，比如如下代码：

i = 1

Do While i <= SlowFunction()

total = total + i

Loop

就要比下面的代码运行慢：

i_max = SlowFunction()

i = 1

Do While i <= i_max

total = total + i

Loop

注意：For语句控制的循环不会每次都计算最终数值，当For语句开始时，系统计算上界数值然后就保存它，每次判断时不用再重新计算。所以，单单使用下面的代码就可以：

For i = 1 To SlowFunction()

total = total + i

Next i

Mid$高速生成长字符串

大家都知道, &操作符的执行速度是相当慢的,特别是处理长字符串时.当必须重复地在同一变量上附加字符时,有一个基于Mid$命令的技巧可以使用.基本思路就是：预留一个足够长的空间存放操作的结果.下面是应用这个技术的一个例子.

假设要建立一个字符串,它要附加从1开始的10000个整数："1 2 3 4 5 6 7 ... 9999 10000".下面是最简单的实现代码：res = ""

For i = 1 to 10000

res = res & Str(i)

Next

代码虽然简单,但问题也很明显：Res变量将被重分配10000次.下面的代码实现同样的目的,但效果明显好转：Dim res As String

Dim i As Long

Dim index As Long

‘预留足够长的缓冲空间

res = Space(90000)

‘指针变量,指出在哪里插入字符串

index = 1

‘循环开始

For i = 1 to 10000

substr = Str(i)

length = Len(substr)

‘填充字符串的相应区间段数值

Mid$(res, index, length) = substr

‘调整指针变量

index = index + length

Next

‘删除多余字符

res = Left$(res, index - 1)

测试表明：在一个333MHz的计算机上,前段代码执行时间为2.2秒,后者仅仅为0.08秒!代码虽然长了些,可是速度却提高了25倍之多.呵呵,由此看来：代码也不可貌相啊(看样子，代码并不一定短才好)

 

=================我是分割线===============

用InStr函数实现代码减肥

可以采用”旁门左道”的方式使用Instr函数实现代码的简练.下面是一个典型的例子,检测字符是否是一个元音字母：

1、普通的方法：If UCase$(char) = "A" Or UCase$(char) = "E" Or UCase$(char) = "I" Or UCase$(char) = "O" Or UCase$(char) = "U" Then

' it is a vowel

End If

'VBA中例子函数

Function 是否元音字母1(char As String) As Boolean

是否元音字母 = False

      If UCase$(char) = "A" Or UCase$(char) = "E" Or UCase$(char) = "I" Or UCase$(char) = "O" Or UCase$(char) = "U"  Then

是否元音字母 = True

      End If

End Function

2、更加简练的方法：If InStr("AaEeIiOoUu", char) Then

' it is a vowel

End If

'VBA中例子函数

Function 是否元音字母2(char As String) As Boolean

是否元音字母 = False

If InStr("AaEeIiOoUu", char) Then

是否元音字母 = True

End If

End Function

同样,通过单词中没有的字符作为分界符,使用InStr来检查变量的内容.下面的例子检查Word中是否包含一个季节的名字：

1、普通的方法：If LCase$(word) = "winter" Or LCase$(word) = "spring" Or LCase$(word) = "summer" Or LCase$(word) = "fall" Then

'it is a season's name这是一个季节名称？？

End If

2、更加简练的方法：If Instr(";winter;spring;summer;fall;", ";" & word & ";") Then

' it is a season's name

End If

有时候,甚至可以使用InStr来替代Select Case代码段,但一定要注意参数中的字符数目.下面的例子中,转换数字0到9的相应英文名称为阿拉伯数字： （下面这个太高档了，很难想到）

1、普通的方法：Select Case LCase$(word)

Case "zero"

result = 0

Case "one"

result = 1

Case "two"

result = 2

Case "three"

result = 3

Case "four"

result = 4

Case "five"

result = 5

Case "six"

result = 6

Case "seven"

result = 7

Case "eight"

result = 8

Case "nine"

result = 9

End Select

2、更加简练的方法：result =InStr(";zero;;one;;;two;;;three;four;;five;;six;;;seven;eight;nine;", ";" & LCase$(word) & ";") \ 6

——怎么样？这个是比较变态吧,很难想到,不过确实很巧妙。很难想象吧,上面那么长一段代码这里就这一句就搞定了

======================我也是分割线======================

使用"$-类型"字符串函数会更快

——在这里确实很少见到大家用含$的函数,本人也是偶尔用用，我原来的那本VB6教材中还是有介绍的

VB官方文档似乎很鼓励使用"无$"类字符串函数,比如：Left、LTrim或者UCase,而不是实现同样功能的Left$、LTrim$和UCase$函数.但是我们必须认识到：前者返回variant类型的数值,当用于字符串表达式中时,最终必须要转换为字符串（string）类型. 因此,在严格要求时间的代码段中,我们应该使用后者,它们将快5-10%.

=======================又见分割线=======================

妙用Replace函数替代字符串连接操作符&

你大概不知道Replace函数还能这么用吧？比如下面的语句：MsgBox "Disk not ready." & vbCr & vbCr & _

"Please check that the diskette is in the drive" & vbCr & _

"and that the drive's door is closed."

可以看出,为了显示完整的字符串含义,要将可打印字符与非打印字符（比如：回车符vbCr）用&符号连接在一起.结果是：长长的字符连接串变得难于阅读.但是,使用Replace函数,可以巧妙地解决这个问题.方法就是：将非打印字符以字符串中不出现的一个可打印字符表示,这样完整地写出整个字符串,然后使用Replace函数替换那个特别的打印字符为非打印字符（比如：回车符vbCr）.代码如下：MsgBox Replace("Disk not ready.§§Please check that the diskette is in the " _

& "drive§and that the drive's door is closed.", "§", vbCr)

注：这个可真的没有想到过，汗一个::L

 

=========================我不说你也知道我是分割线了吧========================

固定长度字符串数组：赋值快,释放快!

固定长度字符串的处理速度通常慢于可变长度字符串,这是因为所有的VB字符串函数和命令只能识别可变长度字符串.因此,所有固定长度字符串必然被转换为可变长度字符串.

但是,由于固定长度字符串数组占据着一块连续的内存区域,因此在被分配以及释放时,速度明显快于可变长度的数组.比如：在一个Pentium 233MHz机器上,对于一个固定长度为100,000的数组,给其中30个位置分配数值,大约只花费半秒种的时间.而如果是可变长度的数组,同样的操作要耗费8秒之多!后者的删除操作耗时大约0.35秒,但固定长度的数组几乎可以立即"毙命"!如果应用程序中涉及到这么大的一个数组操作,选择固定长度方式数组绝对是确定无疑的了,无论是分配数值,还是释放操作,都可以风驰电掣般完成.

===========================;:P 我又来了=========================================

创建任意长度重复字符串的简洁方法

String$函数只能重复复制单字符（99%的朋友都知道吧）,当需要重复复制2个或多个字符时,就需要一个循环.看起来是否很麻烦？

然而,使用以下的函数就能解决这个问题.基本思路是：建立一个空格字符串,其长度为要重复复制的数目,然后替换每一个空格为要复制的字符串：Function ReplicateString(Source As String, Times As Long) As String

ReplicateString = Replace$(Space$(Times), " ", Source)

End Function

但是请注意：根据字符串的长度以及重复的数目,这个方法也许比传统的循环方法要慢些.

 

===========================::$ 我又来了=====================================

另辟蹊径处理字符串中的字符：字节数组法

当要处理字符串中的每一个字符时,可以将字符串赋值到一个byte数组中进行操作.要记住：每一个Unicode字符对应双字节.

这种方法通常要快许多,因为节省了大量的Mid$函数操作以及大量的临时字符串空间.下面的代码是统计字符串中空格数目的最快方法Dim b() as Byte, count As Integer

b() = source$

For i = 0 to UBound(b) Step LenB("A")

If b(i) = 32 Then count = count + 1

Next

请注意上面代码中LenB()函数的特殊用法：在VB4（32位）、VB5和VB6中它返回数值2, 在VB4（16位）中返回数值1.因此,我们就可以使用同一代码段,而无需#If编译指令. （::o 什么？不知道VB456，就当这句话没说）

 

=================================:hug: 换个马甲[em07] =============================

无闪烁地快速附加字符串到textbox控件

附加文本到TextBox或者RichTextBox控件的通常方法是在当前内容上连接上新的字符串：

Text1.Text = Text1.Text & newString

但还有一个更快的方法,并且会减少连接操作的闪烁感,代码如下：

Text1.SelStart = Len(Text1.Text)

Text1.SelText = newString

精用Boolean表达式让代码减肥

当设置基于表达式结果的Boolean型数值时,要避免使用多余的If/Then/Else语句结果.比如：If SomeVar > SomeOtherVar Then

BoolVal = True

Else

BoolVal = False

End If

上面这段代码就很烦琐,它们完全可以使用下面的一行代码来替代：BoolVal = (SomeVar > SomeOtherVar)

括号不是必须的,但可以增加可读性.根据表达式中的操作数不同,后者比前者执行起来大约快50%到85%.后者中的括号对速度没有影响.

有时,使用这个技术实现代码的简练并非很明显.关键是要牢记：所有的比较操作结果或者是0（false）,或者是－1（True）.所以,下面例子中的2段代码是完全相同的,但是第2段要运行得快些：

1、传统方法：If SomeVar > SomeOtherVar Then

x = x + 1

End If

2、更简练的方法x = x - (SomeVar > SomeOtherVar)

--------------------------------------------------------------------------------------------------------

用And、Or和Xor来优化表达式

要检测一个整数值的最高有效位是否有数值,通常要使用如下的代码（有二种情况：第一组If判断表明对Integer类型,第二组对Long类型）：If intValue And &H8000 Then

' most significant bit is set

End If

If lngValue And &H80000000 Then

' most significant bit is set

End If

但由于所有的VB变量都是有符号的,因此,最高有效位也是符号位,不管处理什么类型的数值,通过下面的代码就可以实现检测目的：If anyValue < 0 Then

' most significant bit is set

End If

注:上面这段话似乎涉及到数据在内存中的储存表达方式,本人不是太明白,不过下面的代码还是有效的,也都能够理解

另外,要检测2个或者更多个数值的符号,只需要通过一个Bit位与符号位的简单表达式就可以完成.下面是应用这个技术的几段具体代码：1、判断X和Y是否为同符号数值：

If (x < 0 And y < 0) Or (x >= 0 And y >=0) Then ...

' the optimized approach

If (x Xor y) >= 0 Then

2、判断X、Y和Z是否都为正数

If x >= 0 And y >= 0 And z >= 0 Then ...

' the optimized approach

If (x Or y Or z) >= 0 Then ...

3、判断X、Y和Z是否都为负数

If x < 0 And y < 0 And z < 0 Then ...

' the optimized approach

If (x And y And z) < 0 Then ...

4、判断X、Y和Z是否都为0

If x = 0 And y = 0 And z = 0 Then ...

' the optimized approach

If (x Or y Or z) = 0 Then ...

5、判断X、Y和Z是否都不为0

If x = 0 And y = 0 And z = 0 Then ...

' the optimized approach

If (x Or y Or z) = 0 Then ...

要使用这些来简单化一个复杂的表达式,必须要完全理解boolean型的操作原理.比如,你可能会认为下面的2行代码在功能上是一致的：If x <> 0 And y <> 0 Then

If (x And y) Then ...

然而我们可以轻易地证明他们是不同的,比如X＝3（二进制＝0011）,Y＝4（二进制＝0100）.不过没有关系,遇到这种情况时,我们可以对上面的代码进行局部优化,就能实现目的.代码如下：If (x <> 0) And y Then ...

尽可能少使用“.”，使用对象变量

在前面已经介绍过的对长对象引用使用对象变量以及使用With…End With等都是简化”.”的方法。因为在代码中的每个句点都表示至少一个(而且可能是多个)过程调用，而这些过程调用必须在后台执行。真正好的做法是在局部进行缓存对象引用，例如，应该把对象模型中较高层次的对象引用保存到局部对象变量中，然后用这些对象引用创建其他较低层次的对象引用。例如，引用某单元格数据时，可用如下代码：

Dim i As Long

For i=1 to 10

Workbooks("Book1.xls").Worksheets("Sheet1").Cells(1,i).Value=i

Next i

但下面的代码运行效率更高，因为代码中引用Workbook对象和Worksheet对象的调用命令只执行一次，而上面的代码中却要执行10次。

Dim ws As Worksheet

Dim i As Long

Set ws= Workbooks("Book1.xls").Worksheets("Sheet1")

For i=1 to 10

ws.Cells(1,i).Value=i

Next i

当您一遍又一遍的使用相同对象引用时，您可以将该对象引用设置成一个变量，然后使用该变量代替对象引用。这样，您在代码中只需对该对象变量进行引用即可。

例如，下面的示例在每行中调用Workbook对象的Sheets属性、Range属性和Value属性三次，当您循环1000次时，总共要调用属性6000次。

Sub DoThis1()

Dim Start As Double, Finish As Double

Start = Timer

'--------------------------------------

Dim N As Long

For N = 1 To 1000

Workbooks("Book1").Sheets(1).Range("c5").Value = 10

Workbooks("Book1").Sheets(1).Range("d10").Value = 12

Next

'--------------------------------------

Finish = Timer

MsgBox "本次运行的时间是" & Finish - Start

End Sub

您能在循环开始前通过设置Workbooks(“Book1”).Sheets(1)作为一个对象变量来优化上面的例子，下面的示例在每行仅调用一个Range属性，当循环1000次时，总共只调用该属性2000次。

注意，“Value”是一个缺省属性，通常不需要明确指定它，它将被自动调用。因此，该属性在下面的代码中被忽略。然而，就养成良好的编程习惯而言，还是建议您最好写明该属性。

Sub DoThis2()

'快约35%以上

Dim Start As Double, Finish As Double

Start = Timer

'--------------------------------------

Dim ThisBookSheet As Object, N As Long

Set ThisBookSheet = Workbooks("Book1").Sheets(1)

For N = 1 To 1000

ThisBookSheet.Range("c5") = 10

ThisBookSheet.Range("d10") = 12

Next

'--------------------------------------

Finish = Timer

MsgBox "本次运行的时间是" & Finish - Start

End Sub

您可以比较这两个示例的运行速度，它们都得到同样的结果，但在我的机子上运行时，第二个示例比第一个快60%。当然，您还能使用With…End With语句获得相同的结果。

您也能不设置明确的对象变量，而是使用With语句减少对象的重复引用。上面的示例也能使用下面的代码，该代码仅调用Workbooks属性和Sheets属性一次，当循环1000次时，总共调用1000次属性。

Sub DoThis3()

'快约35%以上

Dim Start As Double, Finish As Double

Start = Timer

'--------------------------------------

Dim N As Long

With Workbooks("Book1").Sheets(1)

For N = 1 To 1000

.Range("c5") = 10

.Range("d10") = 12

Next

End With

'--------------------------------------

Finish = Timer

MsgBox "本次运行的时间是" & Finish - Start

End Sub

上述三个示例均得到相同的结果，但在我的机子上运行时，本示例比第一个示例快50%以上。

在一个语句中进行复制或者粘贴

在用宏录制代码时，首先是选择一个区域，然后再执行ActiveSheet.Paste。在使用Copy方法时，可以在一个语句中指定复制的内容及要复制到的目的地。

例如，将B5:C6区域的内容复制到以单元格B8开始的区域中，使用宏录制器的代码为：

Range("B5:C6").Select

Selection.Copy

Range("B8").Select

ActiveSheet.Paste

经修改后的最佳代码是：

Range("B5:C6").Copy Destination:=Range("B8")

合理地使用消息框和窗体

在一个很长的程序中，尝试着将消息框或者窗体安排显示在程序的最开始或最后面，避免干扰用户。此外，尽管窗体提供了许多功能，但它们能够导致文件大小迅速增加。还有就是尽量避免给工作表单元格链接用户窗体控件，因为这样将会导致链接更新操作，影响程序运行速度。

尽可能加速对数字的运算

(1)当对整数进行除法时，您可以使用整型除法运算符(\)而不是浮点除法运算符(/)，因为无论参与除法运算的数值类型如何，浮点除法运算符总会返回Double类型的值。

(2)在任何具有整数值的算术表达式中使用Single或Double值时，整数均将被转换成Single或Double值，最后的结果将是Single或Double值。如果要对作为算术运算结果的数字执行多次操作，可能需要明确地将该数字转换为较小的数据类型。

提高字符串操作的性能

(1)尽可能少使用连接操作。可以在等号左边使用Mid函数替换字符串中的字符，而不是将它们连接在一起。使用 Mid 函数的缺点是替换字符串必须与要替换的子字符串的长度相同。例如，

Dim strText As String

strText = "this is a test"

Mid(strText, 11, 4) = "tent"

(2)VBA提供许多可用来替换函数调用的内部字符串常量。例如，可以使用vbCrLf常量来表示字符串中的回车/换行组合，而不是使用Chr(13) & Chr(10)。

(3)字符串比较操作的执行速度很慢。有时，可以通过将字符串中的字符转换为 ANSI 值来避免这些操作。例如，下列代码会检查字符串中的第一个字符是否为空格：

If Asc(strText) = 32 Then

上面的代码会比以下代码更快：

If Left(strText, 1) = " " Then

使用Asc()检验ANSI的值

在VBA中，可以使用Chr$()函数把数转换成字符，并确定ANSI的值，但是更好的是使用Asc()函数把字符串转换成数值，然后确定它的ANSI值。如果需要进行有限次数的这种检验，对程序代码的效率可能不会产生很大影响，但是，如果需要在多个循环内进行这种检验时，这将节省处理时间并且有助于程序代码更快地执行。

使用Len()检验空串

尽管有多种方法可检验空串，但首选的是使用Len()函数。为了测试零长度的串，可以选择把串与””相比较，或者比较串的长度是否为0，但这些方法比用Len()函数要用更多的执行时间。当对字符串应用Len()函数并且函数返回0值时，说明该字符串是空的或者是零长度的字符串。

并且，因为在If语句内非零值被认为是True，所以直接使用Len()函数而不必与””或0比较，减少了处理时间，因此执行更快。

有效地使用数组

用VBA数组而不是单元格区域来处理数据，即可以先将数据写入到某个数组，然后用一个语句就可以将数组中的数据传递到单元格区域中。（前文已述）

在创建已知元素的确定数组时，使用Array函数对于节约空间和时间以及写出更具效率的代码是非常理想的。例如：

http://www.knowsky.com/

Dim Names As Variant

Names=Array("Fan","Yang","Wu","Shen")

此外，应该尽量使用固定大小的数组。如果确实选择使用了动态数组，应该避免数组每增加一个元素就改变一次数组的大小，最好是每次增加一定数量的元素。

使用Excel的内置函数

对于要实现的某一功能，如果有Excel的内置函数能够实现，那么就用Excel的内置函数，不需要另外自定义函数，因为自定义的函数总比Excel内置的函数慢。

考虑在VBA代码中使用工作表函数

操作单元格区域的Excel工作表函数通常比完成同样任务的VBA程序更快(但不能确保总是这样，您可以对它们进行速度测试)。

例如，在代码中使用SUM工作表函数比用VBA代码在单元格区域中循环并相加值要快得多，以此为例，下面的代码运行速度相对较慢。

Sub AddItSlow()

Dim Start As Double, Finish As Double

Start = Timer

'--------------------------------------

'为了进行测试,我们循环5次

Dim N As Long

For N = 1 To 5

'***************************

Dim Cell As Range

For Each Cell In Worksheets(2).Range("A1:G200")

[a1] = [a1] + Cell.Value

Next Cell

'***************************

Next N

'--------------------------------------

Finish = Timer

MsgBox "本次运行的时间是" & Finish - Start

End Sub

下面的代码实现相同的功能，但运行得更快(几乎瞬间完成)。

Sub AddItFaster()

'快近600倍

Dim Start As Double, Finish As Double

Start = Timer

'--------------------------------------

'为了进行测试,我们循环5次

Dim N As Long

For N = 1 To 5

'***************************

[a1] = application.WorksheetFunction. _

Sum(Worksheets(2).Range("A1:G200"))

'***************************

Next

'--------------------------------------

Finish = Timer

MsgBox "本次运行的时间是" & Finish - Start

End Sub

产生统计结果的函数(例如PRODUCT、COUNT、COUNTA和COUNTIF)是代替运行速度更慢的VBA代码的很好的选择，并且，一些工作表函数(例如MATCH和LOOKUP)能够将单元格区域作为参数。

不要认为工作表函数总是更快的

如下例所示，在VBA中没有Max或Min函数，但Excel中有该函数。于是，您能编写出如下代码：

Sub MaxIt1()

Dim Start As Double, Finish As Double

Start = Timer

'--------------------------------------

'为了测试,我们循环10000次

Dim N As Long

For N = 1 To 10000

'***************************

[J1] = Application.Max([J2], [J3])

'***************************

Next N

'--------------------------------------

Finish = Timer

MsgBox "本次运行时间是" & Finish - Start

End Sub

或者，您能在VBA中使用下面的方式实现相同的功能：

Sub MaxIt2()

Dim Start As Double, Finish As Double

Start = Timer

'--------------------------------------

'为了测试,我们循环10000次

Dim N As Long

For N = 1 To 10000

'***************************

If [J2] >= [J3] Then [J1] = [J2] Else [J1] = [J3]

'***************************

Next N

'--------------------------------------

Finish = Timer

MsgBox "本次运行的时间是" & Finish - Start

End Sub

比较上面的两个程序，可能认为使用工作表函数会更快，但事实上用VBA代码可以获得几乎相同的速度。因此，在一些大的循环中，您可以对实现同样功能的工作表函数的VBA代码进行测试。一些内置的VBA函数事实上运行速度也是慢的，因此，在编写代码时，在不同方式之间进行速度测试总是值得的。

但是，在代码中经常使用的简单的函数，就直接编写代码，而不是使用WorksheetFunction对象。

使用Range.SpecialCells()来缩小需要处理的单元格数。

只要有可能就使用集合索引值

您能在集合中使用名称或者数字来指定某个单一的对象，但使用对象的索引值通常是更快的。如果您使用对象的名字，VBA必须解析名字成为索引值；但如果您使用索引值，就能避免这个额外的步骤。

但另一方面，我们要注意到在集合中通过名称指定对象有很多优点。使用对象名称能使您的代码更容易阅读和调试。此外，通过名称指定一个对象比通过索引值更安全，因为当您的代码运行时该对象的索引值可能变化。

例如，某菜单的索引值表示它在菜单栏中的位置，但是如果在菜单栏中添加了菜单或者删除了菜单，该菜单的索引值会变化。这样，您就不应该考虑代码的速度，而应保证代码运行可靠。您使用索引值加快代码速度之前，应该确保该索引值在代码运行过程中或使用应用程序时不会改变。

使用完全受限制的对象引用

使用完全受限制的对象引用消除了引用模糊并确保变量有明确的类型。

一个完全受限制的对象引用包括了对象库名称，如下代码所示：

Dim wb As Excel.Workbook

如果您使用通用的对象数据类型声明变量和参数，在运行过程中VBA可能必须对它们的引用进行解析为(某对象的)属性和方法，这将导致速度变慢。

一个通用对象数据类型示例如下：

Dim wb As Workbook

使用已有的VBA方法

也有一些特定目的的VBA方法，它们提供在单元格区域执行特定操作的一种简单的方式。例如工作表函数，这些特定的方法比使用通常的VBA编码完成相同的任务要更快。最常用的是”Replace”方法和”Find”方法。

Replace方法:

下面的示例用了一种相当慢的方式代码改变单元格区域H1:H20000中每个单元格的值。

Sub NowDoThis1()

Dim Start As Double, Finish As Double

Start = Timer

'--------------------------------------

Dim Cell As Range

For Each Cell In Worksheets(1).Range("H1:H20000").Cells

If Cell.Value = 4 Then Cell.Value = 4.5

Next

'--------------------------------------

Finish = Timer

MsgBox "本次运行的时间是" & Finish - Start

End Sub

下面的示例使用Replace方法进行同样的操作，但运行得更快。

Sub NowDoThis2()

'快约两倍

Dim Start As Double, Finish As Double

Start = Timer

'--------------------------------------

Worksheets(1).Range("H1:H20000").Replace "4", "4.5"

'--------------------------------------

Finish = Timer

MsgBox "本次运行的时间是" & Finish - Start

End Sub

Find方法:

下面的代码使用一种相对较慢的方法在单元格区域I1:I5000中值为4的单元格内添加一个蓝色的椭圆。

Sub FindItSlow()

Dim Start As Double, Finish As Double

Start = Timer

'--------------------------------------

Dim Cell As Range

For Each Cell In Worksheets(1).Range("I1:I5000").Cells

If Cell.Value = 4 Then

With Worksheets(1).Ovals.Add(Cell.Left, _

Cell.Top, Cell.Width, _

Cell.Height)

.Interior.Pattern = xlNone

.Border.ColorIndex = 5

End With

End If

Next

'--------------------------------------

Finish = Timer

MsgBox "本次运行的时间是" & Finish - Start

End Sub

下面的示例使用了Find方法和FindNext方法执行相同的任务，但运行速度更快。

Sub FindItFaster()

'快约25倍

Dim Start As Double, Finish As Double

Start = Timer

'--------------------------------------

Dim Cell As Range, FirstAddress As String

With Worksheets(1).Range("I1:I5000")

Set Cell = .Find(4)

If Not Cell Is Nothing Then

FirstAddress = Cell.Address

Do

With Worksheets(1).Ovals.Add(Cell.Left, _

Cell.Top, Cell.Width, _

Cell.Height)

.Interior.Pattern = xlNone

.Border.ColorIndex = 5

End With

Set Cell = .FindNext(Cell)

Loop Until Cell Is Nothing Or Cell.Address = FirstAddress

End If

End With

'--------------------------------------

Finish = Timer

MsgBox "本次运行的时间是" & Finish - Start

End Sub

关于带有特定目的的VBA方法的更多的信息，您可参见VBA帮助系统相关主题。

结语：

当然，代码优化可能不是绝对必要的，这依赖于您要做的工作……如果您正好编写了一个快速且简短的或者是一次性使用且与速度和/或简洁要求无关的代码，您就不需要优化代码。

但另一方面，如果您处理一个带有很多数据、工作簿、工作表等大的工程，再次检查您第一次编写好的代码，看看是否您的代码需要优化，而这样做总是值得的。

最终，您将养成编写代码的好习惯，将会使您的代码更简洁、运行更快速、并且容易为您自已和他人阅读和调试。同时，由于您的代码简洁，因而输入更快，工作效率更高。