Excel 2016 VBA速度提示 - 虚拟变量

VBA for Excel 2016速度很快，但速度并不总是很快。 （计算机程序永远不够快）。继续阅读以发现一些可以用来加速宏的编程示例。

关闭屏幕更新

执行宏时，您可以坐下来观看宏中出现的所有屏幕上的操作。尽管这样做可能很有启发性，但在宏得以正常工作之后，通常很烦人，并且会显着降低宏的性能。幸运的是，您可以禁用执行宏时通常发生的屏幕更新。要关闭屏幕更新，请使用以下语句：

<！ --1 - >

中的应用。 ScreenUpdating = False

如果您希望用户在宏期间的任何时刻查看发生了什么，请使用以下语句重新开启屏幕更新：

应用程序。 ScreenUpdating = True

为了演示速度的差异，执行这个简单的宏，它用数字填充一个范围：Sub FillRange（）Dim r as Long，c As Long Dim Number as Long Number = 0对于r = 1至50对于c = 1至50 Number = Number + 1个单元格（r，c）。选择单元格（r，c）。值=数字下一个c下一个r结束小组

您会看到每个单元格被选中，并在单元格中输入的值。现在在过程开始处插入以下语句并再次执行：

中的应用。 ScreenUpdating = False

范围填充得更快，并且在宏完成运行并且屏幕更新（自动）设置为True时才会看到结果。

当您正在调试代码时，程序执行有时会在中间的某个地方结束，而您没有重新开启屏幕更新。这有时会导致Excel的应用程序窗口变得完全没有响应。退出该冻结状态的方法很简单：返回VBE，然后在立即窗口中执行以下语句：

中的应用。 ScreenUpdating = True

关闭自动计算

如果您有一个包含许多复杂公式的工作表，您可能会发现通过在宏执行时将计算模式设置为手动，可以大大加快速度。宏完成后，将计算模式设回自动。

以下语句将Excel计算模式设置为手动：

应用程序。 Calculation = xlCalculationManual

执行下一条语句将计算模式设置为自动：

应用程序。 Calculation = xlCalculationAutomatic

如果您的代码使用带公式结果的单元格，关闭计算意味着单元格将不会被重新计算，除非您明确告诉Excel这样做！

消除那些讨厌的警报消息

如你所知，一个宏可以自动执行一系列的动作。在很多情况下，你可以启动一个宏，然后在休息室里闲逛，而Excel做它的事情。但是，某些Excel操作会显示需要人工响应的消息。这些类型的消息意味着你不能在执行你的宏的时候无人看管Excel，除非你知道秘密的技巧。

您可以指示Excel在运行宏时不显示这些类型的警报。

避免这些警报消息的秘密技巧是将以下VBA语句插入到您的宏：

应用程序。 DisplayAlerts = False

Excel对这些类型的消息执行默认操作。在删除工作表的情况下，默认操作是删除。如果您不确定默认操作是什么，请执行测试以查看会发生什么情况。

当过程结束时，Excel自动将DisplayAlerts属性重置为True。如果您需要在过程结束之前重新打开警报，请使用以下语句：

应用程序。 DisplayAlerts = True

简化对象引用

正如你可能已经知道的那样，引用对象可能会变得非常冗长。例如，对Range对象的完全限定引用可能如下所示：

Workbooks（“MyBook。xlsx”）。工作表（“Sheet1”）_。 Range（“InterestRate”）

如果您的宏经常使用这个范围，您可能需要使用Set命令创建一个对象变量。例如，以下语句将此Range对象分配给名为Rate：

Set Rate = Workbooks（“MyBook。xlsx”）_的对象变量。工作表（“工作表Sheet1”）。范围（“InterestRate”）

定义此对象变量后，可以使用可变的Rate而不是冗长的引用。例如，您可以更改名为InterestRate的单元格的值：

Rate。值=。 085

这比键入要容易得多：

Workbooks（“MyBook。xlsx”）。工作表（“工作表Sheet1”）。 _范围（“InterestRate”）=。 085

除了简化编码之外，使用对象变量还会显着加速宏。

声明变量类型

您通常不必担心分配给变量的数据类型。 Excel在幕后为您处理所有细节。例如，如果您有一个名为MyVar的变量，则可以为该变量分配任意类型的数字。你甚至可以在程序中稍后分配一个文本字符串。

如果您希望程序尽可能快地执行，请告诉Excel将为每个变量分配哪种类型的数据。这被称为

声明 一个变量的类型。 通常，您应该使用需要最少字节数的数据类型，但仍可以处理分配给它的所有数据。当VBA使用数据时，执行速度取决于VBA具有的字节数。换句话说，数据使用的字节越少，VBA就可以访问和操作数据。整数数据类型是一个例外。如果速度很关键，请使用长数据类型。

如果使用对象变量，则可以将变量声明为特定的对象类型。下面是一个例子：

Dim Rate as Range Set Rate = Workbooks（“MyBook。xlsx”）_。工作表（“工作表Sheet1”）。范围（“InterestRate”）

使用With-End With结构

是否需要为对象设置多个属性？如果使用With-End With结构，则代码运行速度会更快。另一个好处是你的代码可能更容易阅读。

以下代码不使用With-End With：

选择。 Horizo​​ntalAlignment = xlCenter选择。 VerticalAlignment = xlCenter选择。 WrapText =真正的选择。方向= 0选择。 ShrinkToFit =假选择。 MergeCells = False

下面是相同的代码，重写为With With End With：

With Selection。 Horizo​​ntalAlignment = xlCenter。 VerticalAlignment = xlCenter。 WrapText = True。方向= 0. ShrinkToFit = False。 MergeCells = False End With

当您使用With-End With时，确保每个语句以点开始。