编写高效Excel VBA代码的最佳实践(一)

核心提示： 很多Excel VBA文章和图书都介绍过如何优化VBA代码，使代码运行得更快，编写高效Excel VBA代码的最佳实践(一)，下面搜集了一些使Excel VBA代码运行更快的技术和技巧，基本上都是实践经验的总结，同样，上面所讲的对重复使用的对象引用指定一个变量，如果您还有其它优化Excel VBA代码的方法，可以在本

很多Excel VBA文章和图书都介绍过如何优化VBA代码，使代码运行得更快。下面搜集了一些使Excel VBA代码运行更快的技术和技巧，基本上都是实践经验的总结。如果您还有其它优化Excel VBA代码的方法，可以在本文后留言或给出链接，与大家分享。
对于应用程序属性，在代码运行时关闭除必需属性以外的其它所有属性
在代码运行时关闭不需要的Excel功能。其原因是，如果通过VBA更新不同的单元格区域，或者从不同的单元格区域复制/粘贴来创建汇总表，则不希望Excel浪费时间和资源来重新计算公式、显示粘贴进度或者重绘网格，尤其在每次单独的操作后（更有甚者，如果代码使用了循环，则每次单独操作后Excel都会在后台运行这些功能）。只需要在代码执行结束时进行一次重新计算和重绘就足以使工作簿更新。
下面的代码将帮助您提高代码的执行速度。
（1）放置在主代码前的一段代码，获取Excel当前的属性状态，然后将其关闭

'获得当前的Excel设置状态,将其放置在代码的开头
screenUpdateState = application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks '注:这是工作表级的设置
'关闭一些Excel功能使代码运行更快
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False '注:这是工作表级的设置

（2）放置在主代码结束后的一段代码，用来将Excel恢复到代码运行前的设置

'代码运行后,恢复Excel原来的状态;将下面的代码放在代码的末尾
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState '注:这是工作表级的设置

下面简要解释这些设置：
Application.ScreenUpdating：将该属性设置为False，告诉Excel不要重绘屏幕。其优点是不需要Excel花费资源来绘制屏幕，因而其改变会更快而不致让用户察觉其变化。因为如此频繁地绘制屏幕需要大量的资源，所以关闭绘制屏幕直到代码执行结束。在代码结束前，确保重新开启了该属性。
Application.DisplayStatusBar：将该属性设置为False，告诉Excel停止显示状态栏。例如，如果使用VBA复制/粘贴单元格，当粘贴执行时Excel将在状态栏中显示操作的进度。关闭屏幕更新不会关闭状态栏显示，因此，如果需要的话，可以禁用屏幕更新而仍然可以通过状态栏给用户提供反馈。记住，如果将该属性设置为False，在代码结束前应该将其设置为True。
Application.Calculation：该属性允许编程设置Excel的计算模式。“手工的”（xlCalculationManual）模式意味着Excel等待用户（或代码）来触发计算；默认为“自动的”（xlCalculationAutomatic）模式，意味着由Excel来决定何时重新计算工作簿（例如，当在工作表中输入新公式时）。由于重新计算工作簿将花费时间且浪费资源，因此可能不希望每次改变单元格值时Excel都触发重新计算。当代码执行时关闭重新计算，在代码结束前再设置回重新计算模式。
Application.EnableEvents：将该属性设置为False，告诉Excel不要触发事件。你可能不希望Excel为每个正在通过代码发生改变的单元格触发事件，关闭事件将加速VBA代码的执行。
ActiveSheet.DisplayPageBreaks：当在较新版本的Excel中运行VBA时，则可能比在早期版本的Excel中需要更长的时间完成。例如，需要几秒钟在早期版本的Excel中完成的宏可能需要几分钟才能在更高版本的Excel中完成。或者，第二次运行一个宏可能比第一次运行需要的时间更长。这是由于VBA宏修改了多行或列的属性，或者必须强制执行计算Excel分页符。如果宏设置了任何PageSetup属性或者手动设置了PageSetup属性，接着运行较大区域的行或列属性设置时会出现这样的问题。您可以将该属性设置为False来提高代码的运行速度。当然，在代码运行结束前，应将该属性恢复为原设置。

在单个操作中读/写大块的单元格区域
本技巧用于优化在Excel和代码之间转换数据的次数。使用数组变量存储所需要的值并执行取值或赋值操作，而不是一次遍历单个单元格并获取或设置单个值。
例如，下面的代码在单元格区域A1：C10000中放置随机数。
代码段一：运行速度较慢的代码

Sub testSlow()
Dim DataRange As Range
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double
Set DataRange = Range("A1:C10000")
 
For Irow = 1 To 10000
For Icol = 1 To 3
MyVar = DataRange(Irow, Icol) '从Excel单元格中读取值30K次
If MyVar > 0 Then
MyVar = MyVar * MyVar ' 改变值
DataRange(Irow, Icol) = MyVar '将值写入Excel单元格中30000次
End If
Next Icol
Next Irow
End Sub

代码段二：运行速度更快的代码

Sub testFast()
Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
Dim MyVar As Double
DataRange = Range("A1:C10000").Value '一次从Excel单元格中读取所有的值,将其放入数组

For Irow = 1 To 10000
For Icol = 1 To 3
MyVar = DataRange(Irow, Icol)
If MyVar > 0 Then
MyVar = MyVar * MyVar ' 改变数组中的值
DataRange(Irow, Icol) = MyVar
End If
Next Icol
Next Irow
Range("A1:C10000").Value = DataRange '一次将所有结果写回单元格
End Sub

避免选取/激活对象
使用选取的方法更新单元格区域是最慢的。在试验了使用Range对象、使用Variant类型和使用Select方法对一个大的单元格区域读写数据的操作后，Select方法是最慢的。
再来看一个例子：在工作表中有40个形状，在每个形状中写入“Hello”。使用Select方法的代码为：

Sub testSlow()
Dim i As Integer
For i = 0 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).Select
Selection.Text = "Hello"
Next i
End Sub

运行速度更快的方法是完全避免使用选取并直接引用形状：

Sub testFast()
Dim i As Integer
For i = 0 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
Next i
End Sub

在使用宏录制器时，所生成的程序代码在应用任何方法或属性之前都会激活或者选择对象。但是，并不是在所有的情况下都需要这样做。所以，在您编写VBA程序代码时，不需要在对对象执行任何任务之前都激活或者选择每个对象。
例如，在Excel中，我们如果要使第一行变成粗体就必须先选项中它。但在VBA中(除在图表操作时需要选中图表对象外)，很少需要这样做，即VBA可以在不选中第一行的情况下，将它变成粗体。
宏录制器的代码：

Rows("1:1").Select
Selection.Font.Bold = True

改编后的代码为：

Row(“1:1”).Font.Bold=True

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

工作簿设计
好的工作簿设计和数据组织有助于编写运行良好的代码。良好设计的工作簿，其执行效率和维护量将大大优化。可以说，工作簿设计是从大的宏观方面进行优化，而对代码的优化只是一些微观的细节上的优化。

其他

尽量简化代码
通过简化代码，可以提高程序的性能。您可以将通用过程编写为子过程来调用。例如，假设有一个应用程序需要在不同的地方实现查找一定范围内的某个特殊条目，在一个没有简化代码的应用程序中，不同的过程可能需要应用各自的算法以实现在某个范围内查找某一条目，修改每个过程使其采用一个更有效的算法并不是一件很容易的事。而一个简化的程序则只有一个查找算法，即将该查找算法编写成通用的子程序，需要查找某个范围的过程都调用该子程序，通过在查找方法的子程序中优化查找算法，使得调用该方法的所有过程都享受性能提高所带来的好处。
另外，删除所有无关的代码，这在所录制宏中表现得尤为明显。在录制宏时，经常会产生一些与所实现的功能无关的代码，您可以将这些代码删除，以使得代码得以简化。
宏录制器生成无效代码的一个原因是它不知道在对话框中您选择了哪些选项，因此，当您关闭对话框时它将直接记录所有可用的选项。例如，选择单元格区域G2:G20，然后在单元格格式对话框中改变字体样式为粗体，使用宏录制器生成的代码如下：

Sub NowThis1()
Dim Start As Double, Finish As Double
Start = Timer
'--------------------------------------
'为了进行测试,将循环100次
Dim N As Long
For N = 1 To 100
'***************************
Range("G2:G20").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlNone
.ColorIndex = xlAutomatic
End With
'***************************
Next
'--------------------------------------
Finish = Timer
MsgBox "本次运行的时间是" & Finish - Start
End Sub

您能只用下面的一行代码为指定的单元格设置字体样式，不需要选择单元格区域。

Range("G2:G20").Font.FontStyle = "Bold"

如果您考虑到您想要宏所做的事情(本例中为使字体加粗)，那么您可以查阅应用到Font对象的属性和方法列表，您将知道只需使用Bold属性编写这个宏代码以实现所需的功能。代码如下：

Sub NowThis2()
'快约10倍
Dim Start As Double, Finish As Double
Start = Timer
'--------------------------------------
'为进行测试,将循环100次
Dim N As Long
For N = 1 To 100
'***************************
Range("G2:G20").Font.Bold = True
'***************************
Next
'--------------------------------------
Finish = Timer
MsgBox "本次运行的时间为" & Finish - Start
End Sub

您也能在用户界面中通过执行不同的方法来录制产生结果相同的操作对宏录制器进行试验。例如，如果您通过标准工具栏上的粗体按钮格式化某区域为粗体，那么宏录制器将使用Bold属性。
下面将要讲到的设置对象变量代替长对象引用，使用With…End With语句、执行For Each…Next循环语句，根据程序环境尽量减少OLE引用，等等，均是简化代码的好方法。

强制声明变量
在VBE编辑器中的菜单“工具——选项”对话框中“编辑器”选项卡中，您应该始终保持“要求变量声明”复选框被选中，这样将在模块代码顶部出现Option Explicit语句，要求您在编写代码时对所有出现的变量均进行声明，这样，在使用变量时减少内存需求并加速性能。
(1)要节省内存资源，必须始终用特定的数据类型声明所有变量。如果不使用特定的数据类型声明变量，VBA会创建Variant类型的变量，这将比任何其他数据类型要求更多的内存。
(2)清楚每种数据类型需要多少内存以及它可以存储的值的范围。除使用较小的数据类型会导致隐性转换的情况外，应始终使用尽可能小的数据类型。例如，因为Integer类型的变量将被转换成Long类型的变量，应该将那些存储整型值的变量声明为Long类型，而不是Integer类型。
(3)除非确实需要，应避免使用浮点数据类型。尽管Currency数据类型更大，但它比 Single 数据类型快，因为Currency数据类型不使用浮点处理器。
(4)如果在一个过程中多次引用一个对象，可以创建对象变量，并将对给对象的引用指派给它。因为对象变量存储对象在内存中的位置，VBA将不必再次查找其位置。
(5)将对象变量声明为特定的类型(不是Object类型)，以便利用早期绑定。
(6) 减少”Variant”类型变量的使用
虽然您可能发现在您的代码中使用Variant(变体)变量是方便的，但是如果您将变量清楚地声明为特定的数据类型，然后用VBA处理存储在该变量中的值，要比处理存储在Variant变量里的值快。
如果执行不涉及分数值的数学运算，那么在您的代码中使用Long型变量比使用Variant变量更快。Long型变量也是在For…Next循环中索引值变量类型的最好选择。
然而，您要注意到，您使用特定类型变量所获取的速度是以失去灵活性为代价的。例如，当使用特定数据类型时，您可能遭到变量溢出或类型不匹配的情形，而不会像Variant变量会自动进行类型转换处理。
(7) 声明时指定特定的对象类型
当您的宏被编译或者是运行(后台编译)时，会解析对象及它们的方法和属性的引用。经过宏编译解析的引用比在程序运行时必须被解析的引用要更快，因此，您最好跳过后台编译。
如果您声明变量和参数为特定的对象类型(比如Range或Worksheet)，VBA在编译您的程序时将解析引用为这些对象的属性和方法。(如果要查找指定对象类型列表，请参见”对象浏览器”)
减少变量的作用范围并及时释放变量
主要是对象变量，在其使用完后，及时释放。例如，

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

使用常量
变量会发生变化，因此VBA在程序运行时必须获取当前变量的值。
在应用程序中使用常量会使程序运行更快。在编译您的代码时，常量仅计算一次并被存储。
常量也能使您的宏程序更易阅读和维护。如果在您的程序中有一些不变的字符串或数值的话，您可以声明它们作为常量。
尽可能使用早期绑定
绑定是指将程序调用与实际代码相匹配。为了实现早期绑定，先应创建对对象库的引用。早期绑定可以在代码中使用定义在对象库中的常量，可以自动列出对象的方法和属性，但早期绑定只有在所控制的对象拥有独立的类型库或对象库文件才适用且还需要已安装了特定的库。而后期绑定则只是在运行时才知道对象的类型并对对象进行引用，因此不具备上述特点。
使用早期绑定创建对象通常更有效率，使代码能获得更好的性能。因为对象的早期绑定引用在编译时可以通过VBE的解析，而不是通过运行时模块解析，因此早期绑定的性能要好得多。虽然在程序设计时不可能总是使用早期绑定，但应该尽可能使用它。
使用For Each…Next循环
可以使用For Each…Next循环来保证程序代码更快地执行。在使用For Each…Next循环时，对于存储在集合或数组中的每个对象执行一组语句，程序更简洁，也更容易阅读、调试和维护。当For Each…Next语句迭代集合时，自动指定一个对集合当前成员的引用，然后在到达集合的尾部时跳出循环语句。
与使用计数进行循环相比，在遍历集合或数组时使用For Each…Next循环将更快。在多数情况下，使用For Each…Next循环也更方便，并且使您的宏更简洁、更容易阅读和调试。
下面的示例运行很慢，因为在每次循环重复时它设置并调用了行变量.Row(i)。

Sub DoSomethingSlow()
Dim Start As Double, Finish As Double
Start = Timer
'--------------------------------------
Dim Cell As Range, i As Long
With Sheet1.Range("A1:A10000")
For i = 1 To 10000
Set Cell = .Rows(i)
If Cell > 0 Then
Cell.Font.ColorIndex = 5
End If
Next
End With
'--------------------------------------
Finish = Timer
MsgBox "本次运行的时间是" & Finish - Start
End Sub

下面的示例代码更简洁，其运行速度大约是上面代码的2～3倍。因为For Each…Next循环自动记录行数并定位，而不需要调用变量i。

Sub DoSomethingFaster()
'快两至三倍
Dim Start As Double, Finish As Double
Start = Timer
'--------------------------------------
Dim Cell As Range
With Sheet1
For Each Cell In .Range("A1:A10000")
If Cell > 0 Then
Cell.Font.ColorIndex = 5
End If
Next
End With
'--------------------------------------
Finish = Timer
MsgBox "本次运行的时间是" & Finish - Start
End Sub

在执行循环时考虑如何能够尽可能地节省资源
(1)分析循环以查看是否正在不必要地执行一些消耗内存的重复操作。例如，是否可以在循环外(而不是在循环中)设置某些变量？每次都通过循环执行的转换过程是否可以在循环之外执行？
(2)考虑是否必须在满足特定的条件时才执行循环。如果是，也许可以更早地退出循环。例如，假设正在对一个不应该包含数字字符的字符串进行数据验证。如果循环要检查字符串中的每个字符以确定其中是否包含数字字符，那么您可以在找到第一个数字字符时立即退出循环。
(3)如果必须在循环中引用数组的元素，可以创建一个临时变量存储该元素的值，而不是引用数组中的值。从数组中检索值比从相同类型的变量读取值要慢。
(4) 将属性和方法放在循环外部
在代码运行时，获取变量的值快于获取属性的值。因此，如果您的代码在循环内部获取属性的值，您可以在循环外部将该属性的值先指定给一个变量，然后在循环内部使用此变量代替属性的值，这样的代码将运行得更快。
下面所示的代码运行较慢，因为在每次重复循环时都必须获取Sheet的Range属性的值。

Sub TryThisSlow()
Dim Start As Double, Finish As Double
Start = Timer
'--------------------------------------
Dim MyLoop As Long
For MyLoop = 2 To 4001
Cells(MyLoop, 2) = Sheet1.Range("B1")
Next
'--------------------------------------
Finish = Timer
MsgBox "本次运行的时间是" & Finish - Start
End Sub

下面的示例与上面所产生的结果相同，但比上面的要更快，因为在循环开始以前我们已经将Sheet的Range属性的值指定给了单独的变量MyVar。这样，代码将在每次重复循环时利用该变量的值，而不必每次都要调有属性。

Sub TryThisFaster()
'快约35%以上
Dim Start As Double, Finish As Double
Start = Timer
'--------------------------------------
Dim MyVar As String, MyLoop As Long
MyVar = Sheet1.Range("B1")
For MyLoop = 2 To 4001
Cells(MyLoop, 2) = MyVar
Next
'--------------------------------------
Finish = Timer
MsgBox "本次运行的时间是" & Finish - Start
End Sub

如果您在一个循环内部使用多个对象访问，您也可以使用With…End With将您能够移动的对象移到循环外部。下面的示例在每次循环重复时都调用Sheets对象和Cells属性。

Sub NowTryThisSlow()
Dim Start As Double, Finish As Double
Start = Timer
'--------------------------------------
Dim c As Long
For c = 1 To 8000
Sheet1.Cells(c, 5) = c
Next
'--------------------------------------
Finish = Timer
MsgBox "本次运行的时间是" & Finish - Start
End Sub

对上面的代码改写如下，使用With语句将调用Sheets对象移到循环外部，只剩余调用Cells。

Sub NowTryThisFaster()
'约快3倍
Dim Start As Double, Finish As Double
Start = Timer
'--------------------------------------
Dim c As Long
With Sheet1
For c = 1 To 8000
.Cells(c, 5) = c
Next
End With
'--------------------------------------
Finish = Timer
MsgBox "本次运行时间为" & Finish - Start
End Sub

注：您也能通过使用对象变量在循环外部调用该对象。

使用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语句在同一个单元格区域中执行多个操作。

尽量减少OLE引用
调用每个VBA方法或属性都需要一个或多个OLE引用，这样在代码中会有多个点运算符，而每次代码调用都需要对这些点运算符进行解析，这将花费更多的时间。因此，在调用方法或属性时减少引用长度将是使您的程序运行更快的一种好方法。
可以通过尽量减少在VBA程序代码中使用OLE(对象链接与嵌入自动识别)引用来优化程序代码。VBA语句中所调用的方法和属性越多，执行语句所用的时间就越多。例如下面的两个语句：
语句1：

Workbooks(1).Sheets(1).Range("A1").value="10"

语句2：

ActiveWindow.Left=200

执行时，语句2比语句1快。
同样，上面所讲的对重复使用的对象引用指定一个变量，通过调用变量从而保证避免多次进行对象引用。