原创：VBA程序优化指南1：神奇的变量

兰色幻想 · 发表于 2012-9-6 00:38

本帖最后由兰色幻想于 2012-9-6 09:10 编辑

      前言：关于VBA代码优化的文章，网上已有很多。大多数我们已经掌握，除此之外，兰色幻想也根据在编程过程中的一些优化心得，总结出来与同学们共享。（本文由兰色幻想原创，转截请注明转自excel精英培训网http://www.excelpx.com  作者：兰色幻想）。
      本系列我们先谈变量，变量就是临时储存在内存空间的一个"小盒子"，我们可以把数值、文本、数组以及对象都可以放在变量里。变量一般常用于循环作为指针，本文我们则重点谈一下变量在代码优化提速中的作用。
      我们先下面两段程序开始。

Sub test1()
Dim x As Integer
Dim y(1 To 10000) As String
For x = 1 To 10000
y(x) = Range("a1") & 20
Next x
End Sub
Sub test2()
Dim x As Integer, SR
Dim y(1 To 10000) As String
SR = Range("A1")
For x = 1 To 10000
y(x) = SR & 20
Next x
End Sub

复制代码
上面两段程序运行结果都是一样的，都是向数组y中装入值，但运行的速度第一个是.1，第二段程序是.01，相差10倍
，原因是什么呢？原来第一段程序中是把range("a1")放在循环里面，而第二段程序在循环前就把单元格的值放在了变量sr中。这里的变量就把对象在循环前转换成了数值，大量循环中，数值的计算远比调用对象要快。
可能有同学说，对象肯定要慢，其他还有什么情况可以用变量提速度呢？其实算试同样需要我们关注。详见下例：

Sub test1()
Dim x As Long, t
Dim y(1 To 100000) As String
For x = 1 To 100000
y(x) = 2 + 10 + 22 ^ 4 + 20
Next x
End Sub
Sub test2()
Dim x As Long, t, SR
Dim y(1 To 100000) As String
SR = 2 + 10 + 22 ^ 4
For x = 1 To 100000
y(x) = SR + 20
Next x
End Sub

复制代码
上面代码中，都需要计算2+10+22^4的值，然后用到循环里面，但速度第一个是.09s,第二段却只有.07s，差别在哪里呢，原来第一段程序是把算式放在循环内，那么每次循环都要重复的计算一次，而第二个则是在循环外先把计算的结果计算出来，然后再在循环中使用变量的值。这样就避免了在循环中的重复计算。

有同学会问，如果只能在循环中计算，有什么优化方法吗？有，如果我们在循环中要进行多次的重复计算，那么我们同样可以在循环中把计算的结果先放在变量中。再看下面的两段程序，第一个运行速度是.45s,第二段的则快了一倍，只有.28s

Sub test1() '运行时间0.4s
Dim x As Long, t
Dim y(1 To 100000, 1 To 5) As String
t = Timer
For x = 1 To 100000
y(x, 1) = 2 + 10 + 22 ^ 4 + 20
y(x, 2) = 2 + 10 + 22 ^ 4 + 30
y(x, 3) = 2 + 10 + 22 ^ 4 + 50
y(x, 4) = 2 + 10 + 22 ^ 4 + 70
y(x, 5) = 2 + 10 + 22 ^ 4 + 80
Next x
Debug.Print Timer - t
End Sub
Sub test2() '运行时间 0.28s
Dim x As Long, t
Dim y(1 To 100000, 1 To 5) As String, k As Long
t = Timer
For x = 1 To 100000
k = 2 + 10 + 22 ^ 4
y(x, 1) = k + 20
y(x, 2) = k + 30
y(x, 3) = k + 50
y(x, 4) = k + 70
y(x, 5) = k + 80
Next x
Debug.Print Timer - t
End Sub

复制代码
最后我再讲一个大家可能很少用的绝招，就是变量替代没有声明类型的数组值。

Sub test1() ‘ 0.15s 慢
Dim x As Long, t, arr
Dim y(1 To 9100000, 1 To 5) As Long
t = Timer
arr = Range("a1:b20")
For x = 1 To 900000
y(x, 1) = arr(1, 2) * 20
Next x
Debug.Print Timer - t
End Sub
Sub test2() '运行时间0.12s 快
Dim x As Long, t, arr
Dim y(1 To 9100000, 1 To 5) As Long, k As Long
t = Timer
arr = Range("a1:b20")
For x = 1 To 900000
k = arr(1, 2)
y(x, 1) = k * 20
Next x
Debug.Print Timer - t
End Sub

复制代码
上面的第二段代码，比第一段多了一个变量K，而且K只是替换ARR（1，2），你肯定在怀疑：这个会提速？不管你信不信，这个还真可以提速。什么原理呢？原来我们用arr从单元格中取值，数据类型是没有办法定义的，而Y数组的值却是数值型的，所以让一个不知道类型的值和数字相乘，必须会让程序进行多余的判断。而如果用一个已声明变量类型的变量替代arr(1,2)则会提高运行效率。所以上面两个程序，第一段运行时间为.15s，第二段程序运行时间为.12s,快了0.03s.

hwc2ycy · 发表于 2012-9-6 07:35

好东东，学习了。

windimi007 · 发表于 2012-9-6 08:42

好东东，感谢校长分享！{:912:}

hhzjxss · 发表于 2012-9-6 08:48

谢谢兰版，支持兰版，三更半夜写代码，辛苦了！

lisachen · 发表于 2012-9-6 09:17

好东东，校长辛苦了！

faliming7 · 发表于 2012-9-6 12:08

最后两段优化的程序，19行与20行，17行与18行互换，也就是在循环体之外给变量赋值，结果会怎样？

MCBA2012 · 发表于 2012-9-6 15:06

多谢兰版，都是精品啊，呵呵，我现在都快成搬运工了

		自动登录	找回密码
密码			注册

[分享] 原创：VBA程序优化指南1：神奇的变量

评分

相关帖子