【原】《神奇的VBA》编程：执行速度和效率(2)

外企十年工作，写了很多解决办公效率问题的VBA程序，为了能让程序更加高效的运行，曾经花了大量时间寻找让VBA程序运行得更快的方法和技巧。

本文《神奇的VBA》分享提速VBA程序运行的一些规则。平时在使用小型VBA代码程序时，我们作为普通的职场人（非专业IT编程人士）很容易陷入不良的编程习惯，编写VBA程序代码时，是怎么方便怎么来，怎么简单怎么写。这点对于以解决问题为目标的方式，其实没啥问题。但对于大型的或者运行时间较长的VBA程序，使用高效的编码是至关重要的。本文《神奇的VBA》主要关注Excel VBA的程序，但其中许多规则也适用于微软其它办公软件的VBA脚本程序。

前五条规则通常对Excel VBA的执行效率影响最大。规则6到11的影响很小。请注意，《神奇的VBA》对以下节省时间的预估可能会因您的具体应用而发生显著变化。

本文是对“Excel VBA 执行速度和效率(1)”的延续。

规则 #5. 避免使用某些Excel工作表函数

这个规则让我感到惊讶。我天真地认为常见的工作表函数将由VBA有效地处理。事实显然并非如此。例如，大多数 VBA 用户可能都知道 VBA 没有 Max() 或 Min() 函数。Excel 确实具有这些功能。通常使用以下使用 Excel 版本的Max() 的代码：

variable1 = Application.Max(Value1, Value2)

我在互联网上找到了一个单纯用VBA写的Max()函数。它比上面基于Excel的对应物快10倍。但是，下面的代码速度快了80倍以上！下面的函数用两个参数，不支持数组，执行速度提高是实质性的。

Function Max2 (Value1, Value2)If Value1 > Value2 Then    Max2 = Value1Else    Max2 = Value2End IfEnd Function

在大型、耗时的宏中使用工作表函数时要小心。您应评估重写函数的影响。

请注意，任何以"Application."或者 "WorksheetFunction."开头的命令是指 Excel内置函数。我不能说所有的Excel内置函数都很慢。但是，我已经编写或下载了Min()，Max()，Average()，Match()，NormSInv()和StDev()的版本，它们比Excel版本快得多。

规则 #6. 避免在公式中使用变量

除非必要，否则不要将数值变量声明为变量。请注意，如果选择不在宏开头使用“Option Explicit”，则任何未定义的变量都将是变量。变体非常灵活，因为它们可以是数字或文本，但在公式中处理起来很慢。对效率的影响不大，但每一点都有帮助。请注意，这条规则也适用于您编写的任何函数。根据我的测试，数学方程中的变量类型从最快到最慢依次为：常数、单变量、双变量、长变量、整数、变量。

规则 #7. 规避评估文本字符串

字符串（文本）的评估速度很慢。避免在代码中计算字符串，如下所示：

Select Case Gender    Case "Male"    (在此处插入代码)...    Case "Female"    (在此处插入代码)...    Case "Unisex"    (在此处插入代码)...End Select

枚举将常量数值分配给变量。VBA可以快速处理枚举的值，同时保持可读的代码。枚举可以分配默认数值，也可以分配特定值。

Public Enum enumGenderMale = 0Female = 1Unisex = 2End Enum

Dim Gender as enumGenderSelect Case GenderCase Male(在此处插入代码)...Case Female(在此处插入代码)...Case Unisex(在此处插入代码)...End Select

布尔运算符只是 TRUE 或 FALSE 非常快速地切换该过程。在下面的示例中 bMale、bFemale 和 bUnisex 是布尔变量。布尔代码比使用字符串快大约 10 倍。

If bMale Then    (在此处插入代码)...ElseIf bFemale Then    (在此处插入代码)...ElseIf bUnisex Then    (在此处插入代码)...End If

规则 #8. 尽量避免选中工作表

通常不需要使用“选择”命令来读取或写入工作表。不选择工作表的速度大约快30倍。

避免这种情况：

Worksheets("sheet1").SelectAmount1 = Cells(1, 1)

相反，请执行以下操作：

Amount1 = Worksheets("sheet1").Cells(1,1)

Rule #9. 避免过度更新状态栏

VBA处理数学的速度比显示状态栏还要快。向Excel界面状态栏写入文本是VBA和Excel之间通信的另一个例子。下面的示例将每100个场景中的一个写入状态栏。它比把每个场景都写到状态栏要快90倍。

For n = 1 To 10000  (插入代码)...  If n mod 100 = 0 Then     Application.StatusBar = n  End IfNext

规则 #10. 避免不必要的数学运算

我们喜欢充满公式的宏。通常，公式没有达到应有的效率。在下面的示例中，宏计算出一只基金的月值在 50 年内以 5% 的年有效利息增长。我经常看到这样的代码：

Interest_rate = .05For i = 1 To 600  fund(i) = fund(i-1) * (1+interest_rate)^(1/12)Next

将年利率转换为月利率一次会更有效，如下所示。在循环中，VBA 仅使用单个数值运算（乘法）。上面的示例在循环中使用了四个数值运算（一个乘法、一个加法、一个除法和一个幂），因此速度要慢大约四倍。

Interest rate = .05Interest_factor = (1+interest_rate)^(1/12)For i = 1 to 600  fund(i) = fund(i-1) * interest_factorNext

另请注意，幂运算比加法、减法、乘法或除法运算慢。

规则 #11. 避免采用复制粘贴方式

复制和粘贴（或选择性粘贴）功能很慢。使用以下内容复制和粘贴值的速度大约快 25 倍。

Range("A1:Z100").value = Range("A101:Z200").value

---

最后的思考

《神奇的VBA》发现编写一个小宏来评估与各种方法相关的时间节省很有用。该宏只是执行一个方法一百万次左右，并记录执行该方法所花费的时间。下面的简单宏将 Excel Max()函数与规则 #5 中所示的 Max2 函数进行了比较。

'**评估第1个函数

Start_time = NowFor i = 1 To 1000000value1 = Application.Max(amt1, amt2)NextEnd_time = NowWorksheets("sheet1").Cells(1, 2) = End_Time-Start_Time

'**评估第2个函数

Start_time = NowFor i = 1 To 1000000value1 = Max2(amt1, amt2)NextEnd_time = NowWorksheets("sheet1").Cells(2, 2) = End_Time-Start_Time

本篇分享的思路和代码抛砖引玉! 如果您有其他节省时间的提示，盼请不吝分享。 

原创码字不易

欢迎"点赞"，"转发" 或点击"在看"

---