如何優(yōu)化EXCEL vba代碼？
下面談?wù)剛€人的看法，若有不當(dāng)之處，歡迎斧正。
1、解決問題的思路、算法的問題；選用合適的工具解決問題。
比如，程序需要進(jìn)行排序。
排序有多種，每種排序的效率不盡相同，適用的數(shù)據(jù)范圍和條件也不一樣。
根據(jù)數(shù)據(jù)的實(shí)際情況來選擇合適的排序。
下面分別是工作表排序、冒泡排序、快速排序、計(jì)數(shù)排序處理5000條數(shù)據(jù)的速度比較，顯然，冒泡排序需要0.68秒，是最慢的。
當(dāng)處理5萬條數(shù)據(jù)的時候，冒泡排序顯然是最不給力的。
上面幾種排序中，工作表排序算法速度是非?？斓?，因此可以將數(shù)組中的數(shù)據(jù)傳遞到工作表，排序后再傳遞回?cái)?shù)組中。
在實(shí)際中，當(dāng)數(shù)組的元素超過10000個時，選擇用冒泡排序效率就相當(dāng)?shù)土恕?/p>
快速排序和計(jì)數(shù)排序只適用于integer和long數(shù)據(jù)類型的排序，速度都非常快。
又如，字典在超過10萬數(shù)據(jù)的時候，速度就慢了，此時執(zhí)著于用字典處理就顯得力不從心了。選擇用SQL方法是不錯的選擇。
當(dāng)需要處理100萬數(shù)據(jù)的時候，用Excel處理就不合適了，選用數(shù)據(jù)庫是比較合適的。
代碼、軟件都是工具，選用合適的就好。
合適的工具、方法解決合適的問題就是最好的優(yōu)化。
2、從操作單元格進(jìn)階為操作數(shù)組和字典。
下面兩個代碼分別填充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 數(shù)組()
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
花費(fèi)時間分別為：顯然第二個數(shù)組的方法比第一個單元格填充效率更佳。
3、減少循環(huán)次數(shù)。
如下圖，需要在單元格A1:A1000中查找數(shù)字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
在本程序中，當(dāng)循環(huán)到i=5的時候，就完成了目的。但此時程序即使完成了目的，仍然會繼續(xù)執(zhí)行余下的For循環(huán)，直到1000.這就相當(dāng)于做了多余的無用功。
因此，當(dāng)目標(biāo)達(dá)到的時候，退出For循環(huán)，則剩下的循環(huán)就無須執(zhí)行，減少了壓力，提高效率。使用Exit For退出循環(huán)。
4、減少對象的激活或調(diào)用。
VBA中歸根到底是操作對象。頻繁地激活對象，將造成程序的效率打折扣。
比如，引用工作表“數(shù)據(jù)”中單元格A1的值。
可以這樣做,先將'數(shù)據(jù)'工作表激活變成當(dāng)前活動工作表，再引用單元格A1的數(shù)據(jù)。
sheets('數(shù)據(jù)').select
t=range('a1').value
上面語句可以改為，無須激活工作表對象。
t=sheets('數(shù)據(jù)').range('a1').value
激活的對象越多，效率越低。
使用with結(jié)構(gòu)除對象變量，美化代碼又提高效率。
要對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結(jié)構(gòu)，只需要引用一次即可，同時代碼簡化美觀，因減少了對象的引用，效率更佳。
With Range('A1').Font
.Name = '宋體'
.Font.Size = 20
.Font.Bold = True
.Font.Color = 255
End With
5、在循環(huán)外調(diào)用對象。
調(diào)用對象就比較耗費(fèi)資源，在循環(huán)中使用對象，更是增加負(fù)擔(dān)。
Sub 在循環(huán)中調(diào)用單元格()
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 在循環(huán)外調(diào)用單元格()
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
注意循環(huán)次數(shù)的差別：
5、聲明變量、定義數(shù)據(jù)類型。
VBA中并不一定要強(qiáng)制聲明變量。聲明變量以及定義數(shù)據(jù)類型，有好處。
5.1、聲明變量以及定義變量的數(shù)據(jù)類型，電腦就可以容易識別，分配資源。
若不聲明變量，或者聲明了變量，不定義數(shù)據(jù)類型或者數(shù)據(jù)類型定義為變體變量，則電腦需花費(fèi)更多的“精力”去辨別。
就如人與人之間的溝通一樣，信息準(zhǔn)確明確，比靠猜更加高效。
機(jī)器也是這樣的，愛情也是這樣的。
5.2、根據(jù)數(shù)據(jù)的實(shí)際情況，選擇合適的數(shù)據(jù)類型。
假如，需要使用變量A來存儲性別信息。
數(shù)據(jù)類型也要占用空間，占用資源。
我們知道性別信息不是男就是女，是短文本。如果定義變量A的數(shù)據(jù)類型為長文本，將占用更多的資源和空間。為后來的搜索查詢增加負(fù)擔(dān)。
當(dāng)然，這點(diǎn)在VBA中可能沒那么重要。
在VBA中，定義數(shù)據(jù)類型的時候，long往往比integer更加高效。
6、禁止屏幕刷新。
Application.ScreenUpdating = False
屏蔽屏幕刷新，可以增加代碼效率。
7、if判斷結(jié)構(gòu)條件的先后順序。
在寫if結(jié)構(gòu)的判斷條件時，先寫條件成立次數(shù)比較多的條件，則利于提高效率。
盡管現(xiàn)在計(jì)算機(jī)配置都很高，這些效率可能會被忽略不計(jì)。
如下A1:A100中分別有100個數(shù)字，分別為20個6，30個8，50個9.
在用if判斷某個值是否等于6、8、9時，先寫判斷9的條件比先寫判斷6、8的效率要高。
因?yàn)閕f結(jié)構(gòu)中，判斷一旦成立，就不會執(zhí)行余下的判斷。
此題，就不舉例子了。
最后，代碼優(yōu)化在實(shí)際當(dāng)中還有很多方式方法，需要慢慢積累。
以上，只是一些方法，僅供參考。如有錯誤，敬請指正。
歡迎關(guān)注套路Excel
