Excel 提高Excel vba的执行效率（转贴）

Excel提高Excel vba的执行效率(授课内容大纲)

2007-01-10 17:03

Excel 提高Excel vba的执行效率

vba广泛应用于Microsoft Office办公套件，尤其是Excel。vba以它的功能强大，方式灵活，越来越引起office人员的重视，由于VBA非常灵活，因此对于特定功能的实现可能存在多种方法。但值得关注的是好的方法与差些的方法在程序运行效率方面可能存在非常大的差别。因此VBA编程的思想和方法直接关系到VBA程序运行的效率，以下从几个方面列举了一些提高VBA程序运行效率的方法。
1 选择合适的数学模型
编程需要数学模型，数学模型好比汽车的发动机。在同等的情况下使用好的发动机速度会大幅度提升。
例如：猴子分桃问题
原题：五只猴子采的一堆桃子，猴子彼此约定隔天早起后再分食。就在半夜里，一只猴子偷偷起来，把桃子均分成五堆后，发现还多了一个，他吃这个桃子，并拿走了其中一堆。第二只猴子醒来，又把桃子均分成五堆后，还是多了一个，它也吃了这个桃子，并拿走了其中一堆桃子。第三只，第四只，第五只猴子都依次如此分食桃子。那么桃子数最少应该是几个？
Vba代码如下：
    Do  '无限循环
        f = 0    '倒数第一次没猴分的桃
        k = 0    '辅助参数
        n = n + 1   'n为递增的数(就是每次运算的桃子数)
        m = n          'm为计算每次剩下的桃
        For i = 1 To 5    '按猴子的只数循环
            If m - Int(m / 5) * 5 = 1 Then  '计算每次剩下的桃分开后余下的是否为每次要吃的
                m = m - Int(m / 5) - 1    '符合的累减
            Else
                k = 1           '不符合的设定k为1，跳出循环
                Exit For
            End If
            If i = 5 - 1 Then f = m      '如果倒数第二次 将累减的数值给f
        Next
        If k = 0 And Int(f / 5) <> 0 Then    '辅助参数为0 （上述循环一直符合条件），并且倒数第二次的值分给猴子后不能是0，跳出无限循环
            Exit Do
        End If
    Loop
    MsgBox n
我们优化算法：
假使我们设最初有a1个桃子，猴子每次分剩下的桃子数依次为a2,a3…a6。得到一个数列{an}。可知该数列的递推公式： an+1=an-(an-1)/5-1
即： an+1=4*(an-1)/5
整理变形得， an+1+4=4*(an+4)/5

故 {an+4}是以4/5作为公式的等比数列。
所以a6+4=(a1+4)*(4/5)^5
 
欲使 a6+4∈N，应有a1+4=5^5*m(m∈N )，故最初至少有桃子 a1=5^5-4=3121 ，从而最后至少剩下桃子a6=4^5-4=1020。
k只猴子呢，每次t个桃？
公式推导应该得x+(k-1)*t=m*k^k => x=m*k^k-(k-1)*t
最后剩(k-1)^k-(k-1)*t
所以优化的Vba代码如下：
n=5^5-4*1
MsgBox n
2 提高vba代码执行效率的常用手段
2.1 数据类型的选择
2.1.1 避免使用变体数据类型：变体类型需要16个字节的空间保存数据，而一个整数（Interger）只需要2个字节。变体数据类型存在的目的是为减少设计时的工作量或代码量。所以编程时可以使用变体数据类型，最后优化时统一修改变量类型，不要因为你一时的偷懒影响了vba的执行效率。
2.1.2 整数(Integer)和长整型(Long)的应用：因为vba处理整数(Integer)和长整型(Long)的能力远远高于单精度浮点型(Single)、双精度浮点型(Double)和Currency 数据类型，所以将单精度浮点型(Single)、双精度浮点型(Double)和Currency 数据类型转为整数(Integer)和长整型(Long)可以大大提高执行效率。具体方法：例如程序中约定三位小数，那么只需要将保存在整数(Integer)或长整型(Long)变量的数组除以1000就可以得到结果。
2.1.3 顺便提一句模块前加Option Explicit 语句是良好的习惯：如果模块中使用了 Option Explicit，则必须使用 Dim、Private、Public、ReDim 或 Static 语句来显式声明所有的变量。
2.2 关于对象的调用方法
2.2.1 减少对象的激活和选择：绝大多数的人都是通过录制宏来学习vba，这样就会养成一个不好的习惯。你的vba程序里会有大量的Activate、Select。如Workbooks(****).Activate、Sheets(****).Select、Range(****).Select等，其实多数情况下Activate、Select可以省略，因为Activate、Select占用程序的执行时间，所以减少Activate、Select的使用次数，可以一定程度上提高运行速度。例如
Sheets("Sheet2").Select
Range("A1").Value = "小刀"
可改为：
Sheets("Sheet2").Range("A1").Value = "小刀"
2.2.2 减少调用对象，属性统一处理：每个Excel对象的属性、方法的调用都需要通过OLE接口的一个或多个调用，这些OLE调用都是需要时间的，减少使用对象引用能加快VBA代码的运行。好比去你去菜市场买菜，要买茄子和辣椒，如果你买完茄子走出市场再返回买辣椒，时间花在再次寻找市场、进市场、寻找摊位和走到摊位上。如果第一次买完茄子后，不出市场，直接买辣椒，时间就节省了。最佳的方法是找到一个卖摊位茄子和辣椒，在一个摊位一起买。
2.2.2.1 尽量减少调用同一对象，尤其是在循环中：
例如：
    Dim id As Integer
    For id = 0 To 6000
        [A1].Value = [A1].Value & id & "/"
Next
应为：
    Dim id As Integer
    Dim out As String
    For id = 0 To 6000
        out = out & id & "/"
    Next
    [A1].Value = out
2.2.2.2 使用With语句：
如：
    Workbooks(1).Sheets(1).Range("A1").Font.Size = 12
    Workbooks(1).Sheets(1).Range("A1").Font.Strikethrough = False
    Workbooks(1).Sheets(1).Range("A1").Font.Superscrīpt = False
    Workbooks(1).Sheets(1).Range("A1").Font.Subscrīpt = False
    Workbooks(1).Sheets(1).Range("A1").Font.OutlineFont = False
    Workbooks(1).Sheets(1).Range("A1").Font.Shadow = False
应为:
    With Workbooks(1).Sheets(1).Range("A1").Font
        .Size = 12
        .Strikethrough = False
        .Superscrīpt = False
        .Subscrīpt = False
        .OutlineFont = False
        .Shadow = False
End With
2.2.2.3 擅用对象变量：
如果你发现一个对象引用被多次使用，则你可以将此对象用Set 设置为对象变量，以减少对对象的访问。
如：
    Workbooks(1).Sheets(1).Range("A1").Value = "Excel学习群"
    Workbooks(1).Sheets(1).Range("A2").Value = "Excel交友群"
可以：
    Set MySheet = Workbooks(1).Sheets(1)
    MySheet.Range("A1").Value = "Excel学习群"
    MySheet.Range("A2").Value = "Excel交友群"
综合使用With语句:
    Set mysheet = Workbooks(1).Sheets(1)
    With mysheet
        .Range("A1").Value = "Excel学习群"
        .Range("A2").Value = "Excel交友群"
    End With
速度更上一层楼。
2.2.3 模块的调用：
2.2.3.1 vba加载模块的方式是只有在模块中的函数或变量被调用时，vba才将模块加载到内存中，当vba退出时，模块才从内存中拆卸。vba代码最好在一个模块中编写，模块多了vba就会多次加载，vba代码的效率可能会降低。
2.2.3.2 一个过程需要调用一个过程或函数，因为调用是费时间的。如果被调用的过程或函数代码较少(几行代码)，建议将代码直接写进过程。虽然牺牲了代码的长度，但执行效率会提高。
2.3 数组的应用
2.3.1 类似的数据变量：在vba中，数组是最高的数据结构之一。当需要保存类似数据的变量时，优先考虑将它们有一个数组代替。
2.3.2 动态数组：定义数组需要浪费内存资源，而适当的应用动态数组可以节省出一部分资源空间。这些节省出空间对配置好的机器可能效果不大，但对于老爷车级的电脑效果还是有的。
2.3.3 针对Excel的数组
Excel vba每调用一次对象、方法就要耗费一定的时间。在Excel vba里情况会好点，如果是外部调用的如VB、C+等调用Excel对象情况会很糟，数据越多，表现就越明显。因为Excel vba本身可以很快的识别Excel的对象，而外部程序识别就要先识别Excel 再识别Excel的对象（前绑定或迟绑定效果相差不大）。解决的办法应就是减少调用对象的次数。这时我们应该考虑数组（这里不是指Excel工作表里的数组函数）。
data = Sheets(1).Range("A1:A3000").Value
data就是数组，这句的意思是将A1:A3000的值传递给data数组。
反之，Sheets(1).Range("A1:A3000").Value = data
就是将数组data写入A1:A3000。
看下面的例子：
        For i = 1 To 8000 Step 1
            For j = 1 To 10 Step 1
                Sheets(1).Cells(i, j).Value = i
            Next
        Next
调用80000次对象，A1:J8000填完了！
为了明显我改成用数组,填A1:J20000：
    Dim data(1 To 20000, 1 To 10) As Variant
    For i = 1 To 20000 Step 1
        For j = 1 To 10 Step 1
            data(i, j) = i
        Next
    Next
    Sheets(1).Range("A1:J20000").Value = data
计算象飞一样！
再来（针对Excel2003）：
    h = String(912, "A")
    Dim data(1 To 20, 1 To 10) As Variant
    For i = 1 To 20 Step 1
        For j = 1 To 10 Step 1
            data(i, j) = h
        Next
    Next
    Sheets(1).Range("A1:J20").Value = data
程序挂了！！
    h = String(911, "A")
    Dim data(1 To 20, 1 To 10) As Variant
    For i = 1 To 20 Step 1
        For j = 1 To 10 Step 1
            data(i, j) = h
        Next
    Next
    Sheets(1).Range("A1:J20").Value = data
程序没挂！
说明字符串长度>911时有限制（Excel2007这个问题有所改善>8023的才挂掉）。遇到这种情况也只能是一个一个对象的调用。看来美味不可多食啊。
2.4 使用VBA原有的属性、方法和Worksheet函数
2.4.1 使用VBA原有的属性、方法：尽量避免使用自己编的，而Excel vba原来就用的属性、方法，因为“夫妻”还是原配的好。尽管vba的属性、方法多不胜数，但多看代码、多编程、多按F1，你最后还是会掌握的。例如：Range的属性CurrentRegion来返回Range 对象，该对象代表当前区。（当前区指以任意空白行及空白列的组合为边界的区域）。如果你自己编的话要几十行，而直接使用CurrentRegion，不但减少编程，速度也可大大提高。又如[A65536].End(xlup)直接可以到达A列的最后一个有数据的单元格，但如果你通过自己编程的话，又会增加数十行代码。
2.4.2 Worksheet函数：直接调用Excel的工作表函数可以大大提高运行速度。工作表函数的调用方法Application.WorksheetFunction.工作表函数
例如：求A1到A20的合计、算术平均数
vba代码
    TotalValue = 0
    CountValue = 0
    AverageValue = 0
    For Each c In Worksheets(1).Range("A1:A20")
        If IsNumeric(c) Then
            TotalValue = TotalValue + c.Value
            CountValue = CountValue + 1
        End If
    Next
    AverageValue = TotalValue / CountValue
可以变成
   TotalValue = Application.WorksheetFunction.Sum(Worksheets(1).Range("A1:A20"))
   AverageValue = Application.WorksheetFunction.Average(Worksheets(1).Range("A1:A20"))
2.5 其他一些技巧：
2.5.1 处理大量Excel函数：
处理大量Excel函数尤其是数组函数和易失性函数时
Excel工作表不可避免有大量的函数，函数的自动计算对Excel运行影响很大，尤其是有大量数组函数和易失性函数时。提高vba的效率不要只考虑到vba本身，也应该考虑考虑函数问题。最简单的方法就是关闭自动重算，启用手动重算。通过减少重算量提高Excel速度。
    Calculation 属性：返回或设置计算模式。如：Application.Calculation = xlAutomatic自动重算Application.Calculation = xlManual手动重算 Application.Calculation = xlSemiautomatic除模拟运算表，手动重算。
    Calculate 方法：计算所有打开的工作簿、工作簿中的一张特定的工作表或者工作表中指定区域的单元格。如：Application.Calculate （或只是 Calculate）对所有工作表重新计算 ActiveSheet.Calculate对活动工作表重新计算 Worksheets(1).Rows(2).Calculate只针对所选区域重新计算。
那么vba在这里起的作用是在需要的时候重算，比如修改完所有数据、激活某个表、选择某个单元格等。另外可以把计算量较大的公式放到一个辅助表的小范围区域里，通过vba在需要的时机手动重算该区域，再vba模拟复制-选择性粘贴-数值过程，将计算后的数值搬家。这样你的Excel工作簿运算速度会有大幅度提高。
2.5.2 关闭屏幕更新：
关闭屏幕更新是经常用到的提高VBA程序运行速度非常有效的方法，主要是针对与屏幕刷新相关的操作。不用修改代码的结构，实现容易。关闭屏幕更新的方法：
Application.ScreenUpdate = False，请不要忘记VBA程序运行结束时再将该值设回来：Application.ScreenUpdate = True
2.5.3 避免事件触发时的连锁反应：如工作表的Change事件中，一个单元格的值改变影响两个以上的单元格值改变，因为事件的连续触发造成CPU耗尽。
可以在Change事件中Application.EnableEvents = False，请不要忘记VBA程序运行结束时再将该值设回来：Application.EnableEvents = True
2.5.4 避免在频繁的事件中写代码：如Activate事件中。
2.5.5 减少用代码处理图片、音乐的使用：
2.5.6 检查字符串是否为空：进行字符串比较比读取的处理量大。如
If [A1] = "" Then
'执行过程
End If
应为：
If Len([A1]) = 0 Then
'执行过程
End If
2.5.7 优化的方法：
2.5.7.1 “过早的优化是一切麻烦的根源”（Premature optimization is the root of all evil）这是Knuth在编程界有一句名言。最明智的做法是抑制过早优化的冲动，因为那样做可能遗漏多种有用的编程技术，造成代码更难理解和操控，并需更大的精力进行维护。
2.5.7.2 注意代码中的每个细节，尽量将发挥其最大的性能，从而使整体性能得到一定的提升。
3 伪性能的设计
3.1 何为伪性能：程序的某个功能（某个操作）被人的第一感觉认为是好的或坏的性能。
实物举例（十字路口的红绿灯）：
假设红灯的时间为 30 秒，行人或车辆正好遇红灯停止前行，并处于等待状态。对于行人和车辆并不知道距红灯结束还有多长时间，直到红灯停止，绿灯亮起。虽然很短的30秒，但对等待的对象而言却好似很漫长的时间。
现在，红绿灯设备改进了，配备了可视化的倒计时设备。这回，对于等待对象的感觉时间不是很长了——“仅仅剩下29秒了！”
这就是所谓的伪性能。
其实程序的某些操作过程与红绿灯是非常相似的，注意程序细节的处理，避免给使用者带来无奈地等待。
3.2 设计好的伪性能程序
坏的伪性能程序会使用户心律焦急，对软件产生反感的情绪。好的伪性能程序却会为用户带来体贴感。
设计好的伪程序其原则为：对长时间的操作过程展示给用户可视化的操作进程情况（说白了，就是让用户知道或估计出还剩下多长时间此操作结束，或者说还有多少时间可以进行下一步操作了）。
如代码中加上Application.StatusBar
    Application.StatusBar = "正在计算，请稍后..."
    For i = 1 To 10000000
        If (i / 1000000) = Int(i / 1000000) Then
            Application.StatusBar = "已经完成" & Int(i / 2000000) * 10 & "%"
        End If
    Next
    Application.StatusBar = False

编后语：本文第二部分的2.5.7和第三部分引用leaf的文章《编程思想概述及在VB中的体现》，这里感谢leaf。同时为能结识Excel技术联盟群组的所有朋友感到高兴，愿大家的技术都有所提高，愿我们的友谊天长地久！