vba 日期加一年_VBA学习笔记47:程序优化与提速

学习资源：《Excel VBA从入门到进阶》第47集 by兰色幻想

---

今天学习11个程序优化与提速的小技巧：

1. 减少对象的调用

调用对象是非常非常的耗费资源的，所以一定要尽可能的少调用对象。包括：工作簿、工作表、单元格以及外引用对象。

而且在循环内外调用对象，对程序的运行速度也有影响。尽量在循环外调用对象。

2. 减少计算次数

程序的运算速度和计算次数有着很大的关系，所以要尽可能的减少计算的次数。

而且能在循环外计算出结果的，就不要在循环内计算。

3. 禁止闪屏

Application.ScreenUpdating当设置属性值为false时，可以禁止程序运行过程中的屏幕闪动，进而提高运行速度。

但是注意：只有对会引起闪屏操作的代码才有效，否则可能还会拖慢程序的速度。还有在关闭屏幕闪动后，记得在操作完或者程序的末尾，把屏幕闪动打开，否则平时的Excel也会禁止屏幕闪动，没有屏幕刷新。

Application.ScreenUpdating = False    '关闭屏幕闪动
Application.ScreenUpdating = True     '开启屏幕闪动

4. 增加变量的声明类型

声明变量时尽可能把变量的类型给一起声明了，否则计算机都会把它们默认为variant类型，然后据根它的赋值自动判断变量类型，易造成内存的浪费。

数组非常需要声明变量类型，养成习惯，给每个变量声明类型。

复习一下数据类型：

(1) 字符串 String，简写符号$，用于保存文本数据的，字符串内容应放置于双引号内。

(2) 数字类型:

① 整型 Integer，简写符号%，占用两个字节，表示的数据范围：-32768 ~ 32767。

② 长整型 Long，简写符号&，占用4个字节，表示的数据范围：-2147483648 ~ 2147483647。

③ 单精度浮点型 Single，简写符号!，占用4个字节，精度是6，只能保存小数点后最多6位的数据。

在表示负数时： -3.402823E38 ~ -1.401298E-45

在表示正数时： 1.401298E-45 ~ 3.402823E38

④ 双精度浮点型 Double，简写符号#，占用8个字节，精度是14，只能保存小数点后最多14位的数据。

在表示负数时： -1.79769313486231E308 ~ -4.94065645841247E-324

在表示正数时： 4.94065645841247E-324 ~ 1.79769313486231E308

——为避免浪费内存，定义数据类型时，要定义合适的类型。

(3) 日期型 Date，表示日期和时间，

表示的日期范围是：100年1月1日 ~ 9999年12月31日；

表示的时间范围是：0:00:00 ~ 23.59.59。

(4) 布尔型 Boolean，表示逻辑值：真、假 ，常用于条件判断语句。

其中“真”为True，“假”为False。

应当注意的是，当其它数据类型转换为布尔值时，0会转成False，其它值则变成True。当把布尔值转换成其他数据类型时，False会转换为0，True则是-1。

(5) 变体型 Variant, 几乎可以用于保存所有其它数据类型的数据。可以简单地理解为：当不知道变量所要表示的数据是什么类型时，就把它定义为Variant（但这种操作应当尽量避免）。

5. 减少工作表函数的使用

调用工作表函数可以让代码更简捷，但速度却不理想。减少对工作表函数的调用也是提升程序速度的方法之一。

例：统计A列中含有“加10分”的字符串次数。

①使用循环：

Sub C1()
 Dim x, arr, k, t, Y
 t = Timer
 For Y = 1 To 10
 arr = Range("a1:a6800")
 For x = 1 To UBound(arr)
   If InStr(arr(x, 1), "加10分") > 0 Then
     k = k + 1
   End If
 Next x
 Next Y
 Debug.Print Timer - t & "秒"
End Sub

用时：0.05秒

②使用函数：

Sub C2()
 Dim x, arr, k, t, Y
 t = Timer
 For Y = 1 To 10
 arr = Range("a1:a6800")
     k = Application.CountIf([a:a], "*加10分*")
     Next Y
 Debug.Print Timer - t & "秒"
End Sub

用时：0.52秒

使用工作表函数慢了十倍左右。

6. 减少VBA函数的使用

除了工作表函数，还有VBA函数，减少使用VBA函数也能提高程序的运行速度。

例：整除数值。

①用整除运算符

Sub D1()
Dim x, t, k
t = Timer
 For x = 1 To 1000000
   k = 10000  3
 Next x
 Debug.Print Timer - t
End Sub

用时：0.015秒。

②用VBA函数

Sub 用VBA函数算整除()
Dim x, t, k
t = Timer
 For x = 1 To 1000000
   k = Int(10000 / 3)
 Next x
 Debug.Print Timer - t
End Sub

用时：0.031秒。

使用VBA函数慢了一倍左右。

7. 用静态数组替换动态

直接声明足够大的静态数组，比声明动态数组再重新声明要快一点。一样是因为内存的原因，动态数组占用的内存较大，即使后面重新声明了，但按需操作吧。

8. 填充前先清空

填充新数据前，先把旧数据清空掉，再填充新数据会快一点。这也算是一个编程习惯，如果旧数据没用的话，就先清除再填充吧，以免出现某些数据没有更新而导致出错。

9. 批量替代个体

尽量想办法让程序做到批量操作，尤其是数据很多的时候，反复单个操作会出现不停调用内存的情况，会让程序变慢、甚至卡机。

例：判断工作表名称并隐藏。

① 循环判断每个工作表的名称再隐藏。

Sub T1()
 Dim x As Integer, t
 显示工作表
 t = Timer
 For x = 1 To Sheets.Count
   If Sheets(x).Name Like "Sh*" Then
     Sheets(x).Visible = False
   End If
 Next x
 Debug.Print Timer - t
End Sub

用时： 0.125秒。

② 把所有的工作表名称放到数组里判断，后再批量隐藏。

Sub 隐藏工作表2()
 Dim x As Integer, t, arr(), k
  显示工作表
 t = Timer
 For x = 1 To Sheets.Count
   If Sheets(x).Name Like "Sh*" Then
      k = k + 1
      ReDim Preserve arr(1 To k)
      arr(k) = Sheets(x).Name
   End If
 Next x
 Sheets(arr).Visible = False
 Debug.Print Timer - t
End Sub 

用时：0.047秒。

10. 减少循环次数

减少循环次数是最直接的优化速度方法,但操作起来有难度,需要学习相关的知识.如:

① 利用VBA字典，在查找时替代循环查找，相关方法参见VBA80集字典部分。

② 学习VBA算法，减少不必要的循环和运算，如:第45集的换位算法和29集下棋法。

另外，像26集的排序算法，也是通过排除不必要的运算和循环来加快程序运行速度。

11. 巧妙填充公式

向单元格输入公式,如果是连续的,就可以用填充的方法来完成。

例：在E列金额列填充公式，金额=数量*单价。

① 平常是通过循环来填充公式：

Sub F1()
 Dim x, t
 t = Timer
 Range("e2:e1000") = ""
 For x = 2 To 1000
   Cells(x, "e") = "=C" & x & "*D" & x
 Next x
 Debug.Print Timer - t
End Sub

用时：0.125秒。

② 使用filldown方法，向下填充。

Sub 填充公式方法2()
 Dim x, t
 Range("e2:e2000") = ""
 t = Timer
   Cells(2, "e") = "=C2*D2"
   Range("e2:e2000").FillDown
 Debug.Print Timer - t
End Sub

用时：0.016秒。

---

在上个月，刚好和知友Erik H 讨论了一个数据类型的问题：

sub test()
dim x as Long
x=2000*365
end sub

程序报错：数据溢出了。

按理，x=2000*365=730000，是在Long的范围-2147483648 ~ 2147483647里，为什么还会溢出呢？

调用watch框，查看数据类型，可以发现常量2000和365的数据类型是integer整型，也就是说系统会默认给常量进行分配数据类型了。

在VBA中，等式的运算逻辑是把等式右边的值赋值给等式左边的变量，即把2000*365的计算结果赋值给x。

试着把不给x赋值，看会怎么样。

依旧是溢出了，那就应该和x的类型没关系，在2000*365计算的时候就出错了，两个integer相乘，系统应该默认结果也为integer。而计算结果是730000，已经超出了integer的数值范围。

把2000和365都定义为Long之后就没有报错了。

Sub test()

Dim x As Long，a As Long，b As Long

a = 2000
b = 365
x = a * b

End Sub

所以在写程序时，还需要考虑运算式的计算结果的数值范围，以免数值溢出。

---

小结

上面有些例子可能举的不算太贴切，但只是为了作简单说明，希望大家能get到这些点。

我最近也有为工作写了一些程序，比如合并工作簿，自己写的还是不够周全，在公众号看到大神写的合并工作簿程序，会有检查的步骤，比如有没有选中要合并的工作簿已经被打开了。如果没写检查这一步，又出现了这种情况，程序会报错的。要逐渐去养成好的编程思维和习惯，多学多练。

以上，晚安。