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