|
如何优化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 = '宋体' .Font.Size = 20 .Font.Bold = True .Font.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 |
|
|
