回覆列表
-
1 # 套路Excel
-
2 # 望月兄
這裡分享一些我知道的，平時也在運用的提高vba速度的方法。
1、避免使用Variant變數
儘量使用明確的型別變數，比如整型Integer、長整型Long。
2、關閉螢幕更新、禁止觸發事件、禁止互動模式、關閉計算改為手動
啟用了這4項後，執行速度會有顯著的提升。
Application.ScreenUpdating = False Application.EnableEvents = False Application.Interactive = FalseApplication.Calculation = xlCalculationManual3、強制宣告變數
使用變數要宣告，不使用沒宣告過的變數。
4、避免使用錄製宏的一些程式碼
比如Activate、Select，這些都是不需要的。
Range("A1").SelectActiveCell.FormulaR1C1 = "5"請避免上述這樣的寫法。直接寫成：
Range("A1").Value= "5"這樣可以提高速度。
5、能使用Excel內建的autofilter就不要使用迴圈
autofilter比迴圈要快非常多，使用迴圈操作不好，極其容易產生複雜度為N^2的程式碼。速度極慢。
6、明確物件的屬性
比如
a = Range.("A1")
a= Range.("A1").Value
後一條的速度會快不少，使用物件的預設屬性，會增加判斷的時間。
7、多多使用with語句
with語句不僅可以簡化程式碼，還可以提高VBA執行速度。
Dim newBook As WorkbookSet newBook = Workbooks.AddnewBook.SaveAs fileNamenewBook.CloseDim newBook As WorkbookSet newBook = Workbooks.AddWith newBook.SaveAs fileName.CloseEnd With下面使用with語句速度會快一些。
8、內建的排序、函式，有內建的優先使用內建，不要自己設計。
比如Sum函式，已經有內建的Sum函式，就不要自己再設計了。
內建的排序，直接使用就可以了，不需要自己設計如何排序。
9、使用For語句而不是DO/WHILE語句。遍歷優先使用For Each
FOR語句的增量能是1就是1，因為1是VBA自帶的，速度會比較快。
遍歷物件的時候優先使用For Each，會不會快一點不知道，但是程式碼會比較簡潔。
10、IF中不要使用太多的判斷條件
比如A>10 AND B>5 AND C>9這樣的判斷，儘量使用else來分流判斷。
11、優先使用固定長度的陣列
儘量不要用redim，redim非常費時間。
如何最佳化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結構中，判斷一旦成立，就不會執行餘下的判斷。
此題，就不舉例子了。
最後，程式碼最佳化在實際當中還有很多方式方法，需要慢慢積累。
以上，只是一些方法，僅供參考。如有錯誤，敬請指正。