Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 4951|回复: 6

[分享] 程序提速19条优化方法

[复制链接]
发表于 2012-9-7 22:58 | 显示全部楼层 |阅读模式

VBA代码的优化,不仅能使代码容易读懂,同时,更能让代码功能更加强大,另外,还能让VBA代码的执行速度更快。

  以下是优化VBA代码的一些方法,希望对您有所帮助。

  1、尽量简化代码

  通过简化代码,可以提高程序的性能。您可以将通用过程编写为子过程来调用。例如,假设有一个应用程序需要在不同的地方实现查找一定范围内的某个特殊条目,在一个没有简化代码的应用程序中,不同的过程可能需要应用各自的算法以实现在某个范围内查找某一条目,修改每个过程使其采用一个更有效的算法并不是一件很容易的事。而一个简化的程序则只有一个查找算法,即将该查找算法编写成通用的子程序,需要查找某个范围的过程都调用该子程序,通过在查找方法的子程序中优化查找算法,使得调用该方法的所有过程都享受性能提高所带来的好处。

  另外,删除所有无关的代码,这在所录制宏中表现得尤为明显。在录制宏时,经常会产生一些与所实现的功能无关的代码,您可以将这些代码删除,以使得代码得以简化。

  在下面将要讲到的设置对象变量代替长对象引用,使用With…End With语句、执行For Each…Next循环语句,根据程序环境尽量减少OLE引用,等等,均是简化代码的好方法。

  2、强制声明变量

  在VBE 编辑器中的菜单“工具——选项”对话框中“编辑器”选项卡中,您应该始终保持“要求变量声明”复选框被选中,这样将在模块代码顶部出现Option Explicit语句,要求您在编写代码时对所有出现的变量均进行声明,这样,在使用变量时减少内存需求并加速性能。

  A:要节省内存资源,必须始终用特定的数据类型声明所有变量。如果不使用特定的数据类型声明变量,VBA会创建Variant类型的变量,这将比任何其他数据类型要求更多的内存。

  B: 清楚每种数据类型需要多少内存以及它可以存储的值的范围。除使用较小的数据类型会导致隐性转换的情况外,应始终使用尽可能小的数据类型。例如,因为 Integer类型的变量将被转换成Long类型的变量,应该将那些存储整型值的变量声明为Long类型,而不是Integer类型。

  C:除非确实需要,应避免使用浮点数据类型。尽管Currency数据类型更大,但它比 Single 数据类型快,因为Currency数据类型不使用浮点处理器。

  E:如果在一个过程中多次引用一个对象,可以创建对象变量,并将对给对象的引用指派给它。因为对象变量存储对象在内存中的位置,VBA将不必再次查找其位置。

  F:将对象变量声明为特定的类型(不是Object类型),以便利用早期绑定。

  3、减少变量的作用范围并及时释放变量

  主要是对象变量,在其使用完后,及时释放。例如,

  Dim TempObj As AnyObject,AnObj As AnyObject
  Set TempObj=New AnyObject
  Set AnObj=TempObj
  Set TempObj=Nothing ‘释放对象变量

  4、关闭屏幕刷新

  在Excel 中,其ScreenUpdating属性值的默认值为True,这样当写数据到工作表或者执行任何导致其显示属性变化的动作时,Excel的屏幕界面将会不断的刷新,不仅影响显示,而且影响程序运行的速度。您可以在进入主程序运行前将屏幕刷新属性关闭,即用 Application.ScreenUpdating = False语句关闭屏幕刷新,这样将大大改善程序的运行速度。

  但在程序运行完成前,要确保将其恢复为原来的设置,即将ScreenUpdating属性的值设置为True。因为您对该属性的修改是永久性的修改,Excel不会为您自动恢复其默认值,您必须用语句Application.ScreenUpdating = True恢复设置。

  5、设置计算模式为手动

  如果您的工作表中含有多个公式,在每次单元格中的值发生变化时,公式都将会重新计算,这会影响程序运行速度。您可以在进入主程序运行前,将计算模式设置为手动,即使用如下语句Application.Calculation = xlCalculationManual,以避免不必要的计算。

  当程序运行结束前,您要恢复Excel的默认计算模式设置,即设置为自动重算,可使用下面的语句Application.Calculation = xlCalculationAutomatic,这同ScreenUpdating属性一样,Excel不会自动恢复其为默认值。Calculation 属性是对所有工作簿进行的设置,您也可以用工作表的EnableCalculation属性来设置对某个工作表是否进行重新计算。

  6、使用For Each…Next循环

  可以使用For Each…Next循环来保证程序代码更快地执行。在使用For Each…Next循环时,对于存储在集合或数组中的每个对象执行一组语句,程序更简洁,也更容易阅读、调试和维护。当For Each…Next语句迭代集合时,自动指定一个对集合当前成员的引用,然后在到达集合的尾部时跳出循环语句。

  7、使用With…End With语句

  可以使用With…End With语句来尽量减少对象引用。使用With语句对指定的对象完成一系列的任务,而不用重复引用对象。也可以使用嵌套的With语句进一步提高程序代码的效率。例如,下面的使用With…End With语句是在同一个单元格中执行多个操作。

  With Workbooks(“Book1.xls”).Worksheets(“Sheet1”).Range(“A1”)
  .Formula=”=SQRT(20)”
  With .Font
  .Name=”Arial”
  .Bold=True
  .Size=10
  End With
  End With

  同理,可使用With…End With语句在同一个单元格区域中执行多个操作。

  8、在执行循环时考虑如何能够尽可能地节省资源

  A:分析循环以查看是否正在不必要地执行一些消耗内存的重复操作。例如,是否可以在循环外(而不是在循环中)设置某些变量?每次都通过循环执行的转换过程是否可以在循环之外执行?

  B:考虑是否必须在满足特定的条件时才执行循环。如果是,也许可以更早地退出循环。例如,假设正在对一个不应该包含数字字符的字符串进行数据验证。如果循环要检查字符串中的每个字符以确定其中是否包含数字字符,那么您可以在找到第一个数字字符时立即退出循环。

  C:如果必须在循环中引用数组的元素,可以创建一个临时变量存储该元素的值,而不是引用数组中的值。从数组中检索值比从相同类型的变量读取值要慢。

  9、尽量减少OLE引用

  可以通过尽量减少在VBA程序代码中使用OLE(对象链接与嵌入自动识别)引用来优化程序代码。VBA语句中所调用的方法和属性越多,执行语句所用的时间就越多。例如下面的两个语句:

  语句1:Workbooks(1).Sheets(1).Range(“A1”).Value=10
  语句2:ActiveWindow.Left=200
  执行时,语句2比语句1快。

  同样,上面所讲的对重复使用的对象引用指定一个变量,通过调用变量从而保证避免多次进行对象引用。

      10、避免对象激活或者不需要先进行先择

  在使用宏录制器时,所生成的程序代码在应用任何方法或属性之前都会激活或者选择对象。但是,并不是在所有的情况下都需要这样做。所以,在您编写VBA程序代码时,不需要在对对象执行任何任务之前都激活或者选择每个对象。

  例如,在Excel中,我们如果要使第一行变成粗体就必须先选项中它。但在VBA中(除在图表操作时需要选中图表对象外),很少需要这样做,即VBA可以在不选中第一行的情况下,将它变成粗体。

  宏录制器的代码:
  Rows("1:1").Select
  Selection.Font.Bold = True

  改编后的代码为:
  Row(“1:1”).Font.Bold=True

  这样做还可以使程序代码更简洁,并且程序可以运行得更快。

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

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

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

  Range("B5:C6").Select
  Selection.Copy
  Range("B8").Select
  ActiveSheet.Paste

  经修改后的最佳代码是:
  Range("B5:C6").Copy Destination:=Range("B8")

  12、尽可能少使用“.”

  在前面已经介绍过的对长对象引用使用对象变量以及使用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

  13、合理地使用消息框和窗体

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

  14、尽可能加速对数字的运算

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

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

  15、提高字符串操作的性能

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

  Dim strText As String
  strText = "this is a test"
  Mid(strText, 11, 4) = "tent"

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

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

  If Asc(strText) = 32 Then
  上面的代码会比以下代码更快:
  If Left(strText, 1) = " " Then

  16、使用Asc()检验ANSI的值

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

  17、使用Len()检验空串

  尽管有多种方法可检验空串,但首选的是使用Len()函数。为了测试零长度的串,可以选择把串与””相比较,或者比较串的长度是否为0,但这些方法比用Len()函数要用更多的执行时间。当对字符串应用Len()函数并且函数返回0值时,说明该字符串是空的或者是零长度的字符串。
并且,因为在If语句内非零值被认为是True,所以直接使用Len()函数而不必与””或0比较,减少了处理时间,因此执行更快。

  18、有效地使用数组

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

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

  Dim Names As Variant
  Names=Array(“Fan”,“Yang”,“Wu”,“Shen”)
  此外,应该尽量使用固定大小的数组。如果确实选择使用了动态数组,应该避免数组每增加一个元素就改变一次数组的大小,最好是每次增加一定数量的元素。

  19、使用Excel的内置函数

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



评分

参与人数 3 +41 金币 +22 收起 理由
qiaohui + 1 佩服
zjcat35 + 18 很给力!
JLxiangwei + 22 + 22 很给力!

查看全部评分

 楼主| 发表于 2012-9-7 23:12 | 显示全部楼层
● 判断空字符串
   判断空字符串的常用方法是使用下面的语句: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控件做同等工作。前者消耗内存较少。
   ● 使用控件数组
回复

使用道具 举报

 楼主| 发表于 2012-9-7 23:12 | 显示全部楼层
对于不很重要的控件,请使用控件数组来引用它们。比如,许多表单都包含许多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
回复

使用道具 举报

发表于 2012-9-7 23:04 | 显示全部楼层
刚开始,总觉得学的有限,所以了代码都是写着很长的,还不懂的来优化了,看了觉的在以后写代码很有用,支持
回复

使用道具 举报

发表于 2012-9-8 06:48 | 显示全部楼层
刚开始,总觉得学的有限,所以了代码都是写着很长的,还不懂的来优化了,看了觉的在以后写代码很有用,支持
回复

使用道具 举报

发表于 2012-9-8 07:59 | 显示全部楼层
这个真的 有必要看一下   精辟   楼主辛苦了
回复

使用道具 举报

发表于 2015-10-16 21:39 | 显示全部楼层
这真的是好东西,学习了,楼主威武
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-11-1 07:25 , Processed in 0.309135 second(s), 12 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表