优化Excel VBA执行效率，提高程序运行效率-excel打开很慢

很多Excel VBA文章和图书都介绍过如何优化VBA代码，使代码运行得更快。一些使Excel VBA代码运行更快的技术和技巧，与大家分享。鉴于VBA与VB的关系，资料中绝大多数技巧对于VBA同样适用。其中的大多数代码都是亲自在VBA中测试过的，某些地方加了一点注解。

1.快速初始化Variant和String类型数组

在编程的时候,将变量进行初始化也是非常重要的!特别的变体类型和字符串类型,下面将有个优化过程

VB中没有提供定义数组并同时初始化其内容的方法,所以大多数情况下,必须单独地设置每一个元素,就象下面一样：

1. Dim strArray(0 To 3) As String
2. strArray(0) = "Spring"
3. strArray(1) = "Summer"
4. strArray(2) = "Fall"
5. strArray(3) = "Winter"

在VB4、VB5和VB6中,可以使用Array（）函数随意创建一个Variants类型数组：

1. Dim varArray() As Variant
2. varArray() = Array("Spring", "Summer", "Fall", "Winter") '这个我们现在似乎经常在用噢

但却没有同样的函数能创建非Variant类型数组.但是我们发现,在VB6中可以使用Split（）函数创建字符串数组：

1. Dim varArray() As String
2. '由Split建立的数组下标通常是从0开始的
3. varArray() = Split("Spring;Summer;Fall;Winter", ";")

在VB6中,同样能充分利用函数返回数组的能力,创建数组初始化程序段.比如下面的代码段：

1. Function ArrayInt(ParamArray values() As Variant) As Integer()
2. Dim i As Long
3. ReDim res(0 To UBound(values)) As Integer
4. For i = 0 To UBound(values)
5. res(i) = values(i)
6. Next
7. ArrayInt = res()
8. End Function

同时,也可以创建一个子程序段来检测传递给它的数值的类型,并返回正确类型的数组.这种情况下,函数应该定义为返回Variant.

2.快速删除集合——从头开始删除集合项目

删除集合中的所有内容有许多方法,其中有些非常得迅速.来看看一个包含10,000个项目的集合：

下面这段是创建一个集合

1. Dim col As New Collection, i As Long
2. For i = 1 To 10000
3. col.Add i, CStr(i)
4. Next

可以从末尾位置为起点删除集合内容,如下：

1. For i = col.Count To 1 Step -1
2. col.Remove i
3. Next

也可以从开始位置为起点删除集合内容,如下：

1. For i = 1 To col.Count Step 1
2. col.Remove 1
3. Next

测试证明,后者要快于前者百倍多（我也试过，太震撼了）,比如0.06秒比4.1秒.原因在于：当引用接近末尾位置的集合项目时,VB必须要从第1个项目开始遍历整个的项目链.更有趣的是,如果集合项目的数量加倍,那么从末尾开始删除与从头开始删除,前者要比后者花费的时间将成倍增长,比如前者是24秒,后者可能为0.12秒这么短!

最后提醒您：删除集合的所有内容的最快方法就是"毁灭"它,使用下面的语句：

1. Set col = New Collection

对于一个包含20,000个项目的集合,上述操作仅仅耗时0.05秒,这比使用最快的循环操作进行删除也要快2倍左右.

3.GoSub在编译程序中速度变慢

编译为本地代码的VB应用程序中,如果使用 Go Subs 命令,就会比通常的 Subs 或者 Function 调用慢5-6倍；相反,如果是p-code模式,就会相当快.

4.减少DoEvents语句的数量

不要在代码中放置不必要的DoEvents语句,尤其是在时间要求高的循环中.遵循这个原则,至少能在循环中的每N次反复时才执行DoEvents语句,从而增强效率.比如使用下面的语句：

1. If (loopNdx Mod 10) = 0 Then DoEvents

如果只是使用DoEvents来屏蔽鼠标以及键盘操作,那么就可以在事件队列中存在待处理项目时调用它.通过API函数GetInputState来检查这个条件的发生：

1. Declare Function GetInputState Lib "user32" Alias "GetInputState" () As Long
2. ' ...上一句是API声明
3. '下面这句可以写在一个循环中
4. If GetInputState() Then DoEvents

5.读写文件 如果数据量大的时候,读取文件内容的简洁方法

读取text文件的最快方法是使用Input$函数,就象下面的过程：

1. Function FileText (filename$) As String
2. Dim handle As Integer
3. handle = FreeFile
4. Open filename$ For Input As #handle
5. FileText = Input$(LOF(handle), handle)
6. Close #handle
7. End Function

使用上述方法要比使用Input命令读取文件每一行的方法快很多.下面是应用这个函数读取Autoexec.bat的内容到多行textbox控件的例子：

1. Text1.Text = FileText("c:\\autoexec.bat")

但请注意：当文件包含Ctrl－Z（EOF）字符时,上面的函数代码可能会发生错误.因此,要修改一下代码：

1. Function FileText(ByVal filename As String) As String
2. Dim handle As Integer
3. ' 判断文件存在性
4. If Len(Dir$(filename)) = 0 Then
5. Err.Raise 53 '文件没有找到
6. End If
7. ' 以binary模式打开文件
8. handle = FreeFile
9. Open filename$ For Binary As #handle
10. ' 读取内容,关闭文件
11. FileText = Space$(LOF(handle))
12. Get #handle, , FileText
13. Close #handle
14. End Function

结语

当然，代码优化可能不是绝对必要的，这依赖于您要做的工作……如果您正好编写了一个快速且简短的或者是一次性使用且与速度和/或简洁要求无关的代码，您就不需要优化代码。

但另一方面，如果您处理一个带有很多数据、工作簿、工作表等大的工程，再次检查您第一次编写好的代码，看看是否您的代码需要优化，而这样做总是值得的。

最终，您将养成编写代码的好习惯，将会使您的代码更简洁、运行更快速、并且容易为您自已和他人阅读和调试。同时，由于您的代码简洁，因而输入更快，工作效率更高