如何优化EXCEL vba代码？

下面谈谈个人的看法，若有不当之处，欢迎斧正。

1、解决问题的思路、算法的问题；选用合适的工具解决问题。

比如，程序需要进行排序。

排序有多种，每种排序的效率不尽相同，适用的数据范围和条件也不一样。

根据数据的实际情况来选择合适的排序。

下面分别是工作表排序、冒泡排序、快速排序、计数排序处理5000条数据的速度比较，显然，冒泡排序需要0.68秒，是最慢的。

当处理5万条数据的时候，冒泡排序显然是最不给力的。

上面几种排序中，工作表排序算法速度是非常快的，因此可以将数组中的数据传递到工作表，排序后再传递回数组中。

在实际中，当数组的元素超过10000个时，选择用冒泡排序效率就相当低了。

快速排序和计数排序只适用于integer和long数据类型的排序，速度都非常快。

又如，字典在超过10万数据的时候，速度就慢了，此时执着于用字典处理就显得力不从心了。选择用SQL方法是不错的选择。

当需要处理100万数据的时候，用Excel处理就不合适了，选用数据库是比较合适的。

代码、软件都是工具，选用合适的就好。

合适的工具、方法解决合适的问题就是最好的优化。

2、从操作单元格进阶为操作数组和字典。

下面两个代码分别填充1-10000到单元格A1:A10000.

Sub 单元格()

Dim i&

t1 = Time

For i = 1 To 10000

Cells(i, 1) = i

Next i

t2 = Time

Debug.Print "共花去" & DateDiff("s", t1, t2) & "秒时间"

End Sub

Sub 数组()

Dim i&, arr(1 To 10000, 1 To 1)

t1 = Time

For i = 1 To UBound(arr)

arr(i, 1) = i

Next

Range("a1").Resize(10000, 1) = arr

t2 = Time

Debug.Print "共花去" & DateDiff("s", t1, t2) & "秒时间"

End Sub

花费时间分别为：显然第二个数组的方法比第一个单元格填充效率更佳。

3、减少循环次数。

如下图，需要在单元格A1:A1000中查找数字5.

代码如下：

Sub 单元格()

Dim i&

t1 = Time

For i = 1 To 1000

If Range("a" & i) = 5 Then

Range("A" & i).Interior.ColorIndex = 3

End If

Next

t2 = Time

Debug.Print "共花去" & DateDiff("s", t1, t2) & "秒时间"

End Sub

在本程序中，当循环到i=5的时候，就完成了目的。但此时程序即使完成了目的，仍然会继续执行余下的For循环，直到1000.这就相当于做了多余的无用功。

因此，当目标达到的时候，退出For循环，则剩下的循环就无须执行，减少了压力，提高效率。使用Exit For退出循环。

4、减少对象的激活或调用。

VBA中归根到底是操作对象。频繁地激活对象，将造成程序的效率打折扣。

比如，引用工作表“数据”中单元格A1的值。

可以这样做,先将"数据"工作表激活变成当前活动工作表，再引用单元格A1的数据。

sheets("数据").select

t=range("a1").value

上面语句可以改为，无须激活工作表对象。

t=sheets("数据").range("a1").value

激活的对象越多，效率越低。

使用with结构除对象变量，美化代码又提高效率。

要对Range("a1"),作下列操作，一般代码的写法是：

Range("a1").Font.Name = "宋体"

Range("a1").Font.Size = 20

Range("a1").Font.Bold = True

Range("a1").Font.Color = 255

这样每次都要引用对象Range("a1")一次，总共引用了4次。

用With结构，只需要引用一次即可，同时代码简化美观，因减少了对象的引用，效率更佳。

With Range("A1").Font

.Name = "宋体"

.Size = 20

.Bold = True

.Color = 255

End With

5、在循环外调用对象。

调用对象就比较耗费资源，在循环中使用对象，更是增加负担。

Sub 在循环中调用单元格()

Dim i As Integer, k, t

t1 = Time

For i = 1 To 20000

k = [a1]

Next i

t2 = Time

Debug.Print "共花去" & DateDiff("s", t1, t2) & "秒时间"

End Sub

Sub 在循环外调用单元格()

Dim i As Long, j, k, t

t1 = Time

k = [a1]

For i = 1 To 2000000

j = k

Next i

t2 = Time

Debug.Print "共花去" & DateDiff("s", t1, t2) & "秒时间"

End Sub

注意循环次数的差别：

5、声明变量、定义数据类型。

VBA中并不一定要强制声明变量。声明变量以及定义数据类型，有好处。

5.1、声明变量以及定义变量的数据类型，电脑就可以容易识别，分配资源。

若不声明变量，或者声明了变量，不定义数据类型或者数据类型定义为变体变量，则电脑需花费更多的“精力”去辨别。

就如人与人之间的沟通一样，信息准确明确，比靠猜更加高效。

机器也是这样的，爱情也是这样的。

5.2、根据数据的实际情况，选择合适的数据类型。

假如，需要使用变量A来存储性别信息。

数据类型也要占用空间，占用资源。

我们知道性别信息不是男就是女，是短文本。如果定义变量A的数据类型为长文本，将占用更多的资源和空间。为后来的搜索查询增加负担。

当然，这点在VBA中可能没那么重要。

在VBA中，定义数据类型的时候，long往往比integer更加高效。

6、禁止屏幕刷新。

Application.ScreenUpdating = False

屏蔽屏幕刷新，可以增加代码效率。

7、if判断结构条件的先后顺序。

在写if结构的判断条件时，先写条件成立次数比较多的条件，则利于提高效率。

尽管现在计算机配置都很高，这些效率可能会被忽略不计。

如下A1:A100中分别有100个数字，分别为20个6，30个8，50个9.

在用if判断某个值是否等于6、8、9时，先写判断9的条件比先写判断6、8的效率要高。

因为if结构中，判断一旦成立，就不会执行余下的判断。

此题，就不举例子了。

最后，代码优化在实际当中还有很多方式方法，需要慢慢积累。

以上，只是一些方法，仅供参考。如有错误，敬请指正。

欢迎关注套路Excel