Excel VBA提速秘籍：小白也能变大神！

Excel VBA提速秘籍：小白也能变大神！

引用

CSDN

等

7

来源

1.

https://blog.csdn.net/weixin_43837855/article/details/131823567

2.

https://blog.csdn.net/xmxmxm22/article/details/130769182

3.

https://zhuanlan.zhihu.com/p/520105581

4.

https://oacourse.com/excel/ways-to-speed-up-excel-vba-code/

5.

https://learn.microsoft.com/zh-cn/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions

6.

https://www.cnblogs.com/new-june/p/15802804.html

7.

http://www.360doc.com/content/18/0809/17/32342759_777012419.shtml

在日常工作中，Excel是我们不可或缺的工具。然而，当表格变得庞大、公式繁多时，Excel的运行速度可能会让人抓狂。别担心，通过一些简单的VBA优化技巧，你也可以让Excel飞速运转起来。比如启用手动计算模式、关闭屏幕刷新功能以及使用字典代替复杂的查找函数等。这些小技巧不仅能显著提升你的工作效率，还能让你在同事面前瞬间变身Excel大神。快来试试吧，让你的工作更加高效便捷！

当启用自动重算后，每次影响公式的值更改时，Excel 都会自动重新计算工作簿。在包含大量公式的工作表中，此行为会大大降低您的 VBA 运行速度。

如果您的工作簿包含大量公式，您可能不希望每次程序更改单元格值时 Excel 都触发重新计算。可以使用Calculation 属性将工作簿计算切换到手动计算模式。当工作簿处于手动计算模式时，工作簿不会自动重新计算，除非您按键盘上的F9键明确触发计算。

关闭 Excel 的自动重算可以显著加速您的 VBA。方法是先将 Excel 置于手动计算模式，运行代码，当代码运行完毕再切换回自动重算模式。

'------------- mdlCalculationMode -------------
Sub CalculationMode()
    Application.Calculation = xlCalculationManual
    ' 将您的 VBA 代码放在这里
    Application.Calculation = xlCalculationAutomatic
End Sub  

注意
将计算模式设置回
xlCalculationAutomatic
将自动触发工作表的重新计算。因此，您不需要在运行宏后按F9。

您可能实际上需要在 VBA 运行期间执行计算，在这种情况下您不必使用手动计算模式。请务必考虑您的应用场景并确定在 VBA 运行时关闭自动计算会发生什么情况。

您可能会注意到在代码运行时屏幕会闪烁。这种闪烁是 Excel 试图重绘屏幕以显示工作表的当前状态。遗憾的是，Excel 每次重绘屏幕都需要时间。在大多数情况下，每次代码执行某些操作时，您不需要 Excel 消耗计算机资源来重新绘制屏幕。

使用ScreenUpdating 属性禁用屏幕更新，直到您的代码完成运行，这样可以节省时间和资源，并让您的 VBA 运行得更快一些。您可以在 VBA 代码运行完毕后重新启用屏幕更新。

'------------- mdlDisableScreenUpdating -------------
Sub DisableScreenUpdating()
    Application.ScreenUpdating = False
    ' 将您的 VBA 代码放在这里
    Application.ScreenUpdating = True
End Sub  

提示
将
ScreenUpdating
属性设置回
True
后，Excel 不仅会恢复重绘，还会触发一次重绘以显示来自 VBA 的所有更改。

Excel 状态栏显示在 Excel 窗口的底部。状态栏通常显示 Excel 中某些操作的状态。例如，如果您复制/粘贴一个区域，Excel 将在状态栏中显示该操作的进度。通常，操作执行得如此之快以至于您无法在状态栏中看到进度。但是，如果您的 VBA 正在处理大量数据，状态栏将会占用一些资源。

注意
关闭屏幕更新与关闭状态栏显示是分开的。这意味着即使您禁用屏幕更新，状态栏也会继续更新。

您可以通过使用应用程序暂时禁用任何状态栏更新来进一步提高 VBA 的性能。

'------------- mdlStatusBarUpdates -------------
Sub StatusBarUpdates()
    Application.DisplayStatusBar = False
    ' 将您的 VBA 代码放在这里
    Application.DisplayStatusBar = True
End Sub  

您可以为工作簿或工作表设置事件过程，这些过程告诉 Excel 在工作表或工作簿更改时运行特定代码。有时，VBA 代码所做的更改实际上会触发事件过程。

例如，假设您为工作表
Sheet1
设置了Worksheet_Change 事件，每当更改单元格或区域时，都会触发
Worksheet_Change
事件，如果您的 VBA 来操作 Sheet1 上的多个单元格，则每次更改该工作表上的一个单元格时，您的代码都必须在 Worksheet_Change 事件运行时暂停。您可以想象这种行为会如何减慢您的 VBA 执行速度。

此时可以使用EnableEvents 属性告诉 Excel 在 VBA 运行时忽略事件。

只需在代码运行之前将
EnableEvents
属性设置为
False
。代码运行后，再将
EnableEvents
属性设置回
True
。

'------------- mdlIgnoreEvents -------------
Sub IgnoreEvents()
    Application.EnableEvents = False
    ' 将您的 VBA 代码放在这里
    Application.EnableEvents = True
End Sub  

虽然禁用事件确实可以加速您的 VBA，但实际上您可能需要在 VBA 运行时触发一些事件。请务必考虑您的特定情况并确定如果您在 VBA 运行时关闭工作表或工作簿事件会发生什么情况。

如果您在工作表上显示分页符，则每次您的 VBA 修改行数、列数或更改工作表的页面设置时，Excel 都被迫花时间重新计算在工作表上显示分页符的位置。

您可以在 VBA 代码运行之前隐藏分页符来避免这种情况。将工作表的 DisplayPageBreaks 属性设置为
False
以隐藏分页符。如果要在 VBA 运行后继续显示分页符，可以将工作表的
DisplayPageBreaks
属性设置回
True
。

'------------- mdlDisplayPageBreaks -------------
Sub DisplayPageBreaks()
    ActiveSheet.DisplayPageBreaks = False
    ' 将您的 VBA 代码放在这里
    ActiveSheet.DisplayPageBreaks = True
End Sub  

如果您的 VBA 在包含大型数据源的数据透视表上运行，则在执行动态添加或移动数据透视字段等操作时，您可能会遇到性能不佳的情况。这是因为您对数据透

屏幕刷新：

如果我们不需要看见代码执行过程数据变化，可以在代码开始初关闭屏幕更新

'关闭屏幕更新
Application.ScreenUpdating = False
'恢复屏幕更新
Application.ScreenUpdating = True   

自动计算

如果工作表有很多函数，在代码运行过程中，关闭公式计算可以显著提升运行速度。

'手动重算
Application.Calculation = xlCalculationManual

'计算这个工作簿
calculate

'计算当前工作表
activesheet.calculate

'自动重算
Application.Calculation = xlCalculationAutomatic   

警告提示

有些时候，我们不希望代码运行过程中莫名弹出一些警告提示，可以设置False关闭提示信息。

'关闭提示
Application.DisplayAlerts = False 

'恢复提示
Application.DisplayAlerts = True 
  

关闭状态栏

Application.DisplayStatusBar 关闭状态栏。 如果将 Application.DisplayStatusBar 设置为 False，Excel 将不显示状态栏。 状态栏设置与屏幕更新设置是分开的，这样即使屏幕没有更新，也可以显示当前操作的状态。 但是，如果不需要显示每个操作的状态，在代码运行时关闭状态栏也可以提高性能。

禁用分页符

ActiveSheet.DisplayPageBreaks 禁用分页符。 如果将 ActiveSheet.DisplayPageBreaks 设置为 False，Excel 将不显示分页符。 不需要在代码运行时重新计算分页符，并且在代码执行后计算分页符可以提高性能。

事件关闭：

如果代码在运行过程中，我们不需要事件触发，可以关闭事件，避免工作簿中事件频繁触发，影响处理效率。

'关闭事件
Application.EnableEvents = False	

'启动事件
Application.EnableEvents = True
  

读取数据时使用 .Value2

从 Excel 区域读取数据时使用 .Value2 而不是 .Value 或 .Text
.Text 返回单元格的格式化值。 速度较慢，如果用户缩放，可能返回 ###，并可能丢失精度。
.Value 在区域被格式化为日期或货币的情况下，返回 VBA 货币变量或 VBA 日期变量。 速度较慢，可能会丢失精度，并且在调用工作表函数时可能导致错误。.Value2 速度快，不会改变正在从 Excel 检索的数据。

避免选择并激活对象

选择和激活对象的处理过程比直接引用对象更为密集。 通过直接引用 Range 或 Shape 等对象，可以提高性能。

使用单行语句进行複制和粘贴

Range(“E14”).Select
Selection.Copy
Range(“F14:G14”).Select
Activesheet.Paste
'改成*************
Range(“E14”).Copy Destination :=Range(“F14:G14”)  

其他优化

通过将数组直接分配给 Range 来返回结果。
使用显式类型声明变量，以避免在代码执行期间确定数据类型的开销（可能在一个循环中进行多次）。
对于在代码中频繁使用的简单函数，请自己在 VBA 中实现这些函数而不是使用 WorksheetFunction 对象。
使用 Range.SpecialCells 方法缩小与代码