在Excel如何优化VBA代码,分享几个小知识,提升效率就在一瞬之间
很多Excel VBA文章和图书都介绍过如何优化VBA代码,使代码运行得更快。一些使Excel VBA代码运行更快的技术和技巧,与大家分享。
1.在一个语句中进行复制或者粘贴
在用宏录制代码时,首先是选择一个区域,然后再执行ActiveSheet.Paste。在使用Copy方法时,可以在一个语句中指定复制的内容及要复制到的目的地。
例如,将B5:C6区域的内容复制到以单元格B8开始的区域中,使用宏录制器的代码为:
Range("B5:C6").Select
Selection.Copy
Range("B8").Select
ActiveSheet.Paste
经修改后的最佳代码是:
Range("B5:C6").Copy Range("B8")
合理地使用消息框和窗体
在一个很长的程序中,尝试着将消息框或者窗体安排显示在程序的最开始或最后面,避免干扰用户。此外,尽管窗体提供了许多功能,但它们能够导致文件大小迅速增加。还有就是尽量避免给工作表单元格链接用户窗体控件,因为这样将会导致链接更新操作,影响程序运行速度。
2.尽可能加速对数字的运算
(1)当对整数进行除法时,您可以使用整型除法运算符(\)而不是浮点除法运算符(/),因为无论参与除法运算的数值类型如何,浮点除法运算符总会返回Double类型的值。
(2)在任何具有整数值的算术表达式中使用Single或Double值时,整数均将被转换成Single或Double值,最后的结果将是Single或Double值。如果要对作为算术运算结果的数字执行多次操作,可能需要明确地将该数字转换为较小的数据类型。
3.提高字符串操作的性能
(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值。如果需要进行有限次数的这种检验,对程序代码的效率可能不会产生很大影响,但是,如果需要在多个循环内进行这种检验时,这将节省处理时间并且有助于程序代码更快地执行。
5.使用Len()检验空串
尽管有多种方法可检验空串,但首选的是使用Len()函数。为了测试零长度的串,可以选择把串与””相比较,或者比较串的长度是否为0,但这些方法比用Len()函数要用更多的执行时间。当对字符串应用Len()函数并且函数返回0值时,说明该字符串是空的或者是零长度的字符串。
并且,因为在If语句内非零值被认为是True,所以直接使用Len()函数而不必与””或0比较,减少了处理时间,因此执行更快。
6.有效地使用数组
用VBA数组而不是单元格区域来处理数据,即可以先将数据写入到某个数组,然后用一个语句就可以将数组中的数据传递到单元格区域中。(前文已述)
在创建已知元素的确定数组时,使用Array函数对于节约空间和时间以及写出更具效率的代码是非常理想的。例如,
Dim Names As Variant
Names=Array("Fan","Yang","Wu","Shen")
此外,应该尽量使用固定大小的数组。如果确实选择使用了动态数组,应该避免数组每增加一个元素就改变一次数组的大小,最好是每次增加一定数量的元素。
7.使用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 1000
[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对象。
关于带有特定目的的VBA方法的更多的信息,您可参见VBA帮助系统相关主题。
结语
当然,代码优化可能不是绝对必要的,这依赖于您要做的工作……如果您正好编写了一个快速且简短的或者是一次性使用且与速度和/或简洁要求无关的代码,您就不需要优化代码。
但另一方面,如果您处理一个带有很多数据、工作簿、工作表等大的工程,再次检查您第一次编写好的代码,看看是否您的代码需要优化,而这样做总是值得的。
最终,您将养成编写代码的好习惯,将会使您的代码更简洁、运行更快速、并且容易为您自已和他人阅读和调试。同时,由于您的代码简洁,因而输入更快,工作效率更高。
请先 后发表评论~