编写高效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

wdy1983

很强大啊，我vvba没学好

gaom8

竟然复制了一段