返回列表 上一主題 發帖

[轉貼] VBA的寫作技巧與增進效能

[轉貼] VBA的寫作技巧與增進效能

VBA的寫作技巧與增進效能

經由錄製產生的巨集,通常程式碼都會含有很多 Select,甚至往後自己寫的程式也習慣用一堆 Select。寫程式的人以為必須 Select 一個物件後才能對它做處理,但這是 [錄製巨集] 誤導的錯誤觀念 (自己也沒有徹底了解語法),而且是造成巨集執行效率不佳的原因之一。

一、數數看你的程式裡有多少 "Select" ?
除非程式就是要依使用者選取的物件來做動作,否則 Select 和 Selection 都是多餘的.
◎ 標準的物件控制語法:
  物件.方法 (例如 Range("A1").Copy)
  物件.屬性 = 值 (例如 Range("A1").ColorIndex = 15)
而不是一定要先 Select 物件然後再對 Selection 做動作.

舉例而言,你要複製 Sheet1.A1 的值到 Sheet2.B1 --
 Range("A1").Copy
 Sheets(2).Select
 Range("B1").Select
 Range("B1").PasteSpecial xlPasteValues
其實可以這麼寫 --
 Sheets(2).Range("B1") = Sheets(1).Range("A1")
如果內容與格式都要複製,可以這麼寫 --
 Sheets(1).Range("A1").Copy Sheets(2).Range("B1")

不要看這沒什麼,你的VBA觀念和程度能否更進一步,這是很重要的一點。

二、關閉螢幕更新 (Application.ScreenUpdating)
程式裡做的動作越多,螢幕更新的問題就越明顯。例如選取了儲存格、選取物件、複製、貼上、切換工作表... Excel 都會改變焦點 (Focus). 每改變一次,就是一次螢幕更新。想想看,在一連串的螢幕更新之中,不但令使用者眼花撩亂,程式執行的整體效能也會下降。

這與減少 Select 是一體兩面的事,其實很多選取儲存格、選取物件、複製、貼上、切換工作表... 的動作都是不必要的。只要技巧用的好,ScreenUpdating 幾乎可以束之高閣。

三、過多/不必要的迴圈也會降低執行效率
迴圈 (如 For...Next、Do...Loop等等) 是很重要的寫作技巧之一,它能大幅簡化程式中重複的動作,而且是錄製不出來的。
這裡所謂不必要的迴圈是指處理的範圍太大,浪費過多時間。例如
For Each cell In Columns(1)
 ......
Next
For Each cell In [A1:A65536]
 ......
Next
以上兩個迴圈都是處理 A 欄 6 萬多個儲存格。
說實在的,連幾千個Cell我都有點擔心了,何況幾萬個 -- 有必要嗎??
何不判斷好資料的範圍再來做迴圈 --
For Each cell In Range([A1], [A65536].End(xlUp))
 ......
Next

參考: 如何判斷資料範圍
http://gb.twbts.com/index.php/topic,315.0.html
http://gb.twbts.com/index.php/topic,584.0.html

四、釋放物件變數佔用的記憶體空間
在這裡尤指對應用程式(Appliation)的引用與存取,下例從Word表格取回資料至Excel工作表 --

Sub get_word_table( )
Dim wrdApp As Object
Set wrdApp = CreateObject("Word.Application") '建立引用Word應用程式的物件
Set wrdDoc = wrdApp.Documents.Open("D:\Temp\ole_test.doc") '引用Word文件
With wrdDoc.Tables(1)
 For r = 1 To .Rows.Count
  For c = 1 To .Columns.Count
  Cells(r, c) = .Cell(r, c)
  Next c
 Next r
End With
wrdDoc.Close 'close the document
wrdApp.Quit 'close Word
Set wrdDoc = Nothing '釋放物件變數
Set wrdApp = Nothing
End Sub

初學者常常會忽略最後兩句,如果不寫雖然不會影響程式的運行,但從記憶體管理和效能控制的角度而言,這是個很不好的習慣。

當省則省,省的是多餘重複的程式碼;
當用則用,用的是不可或缺的程式碼。

--------------------------------------------------------------------------------

大家認為有不足或疑問之處,歡迎提出補充和討論。

請問版主
 Sheets(2).Range("B1") = Sheets(1).Range("A1")
這種寫法如果要針對某範圍例如從sheet1的A1:a10做陣列轉換到sheet2的a1:j1
要如何編寫程式碼?

TOP

回復 2# yuch8663


    sheet2.[A1:J1]=application.transpose(sheet1.[A1:A10])
學海無涯_不恥下問

TOP

原來是這樣寫,承蒙版主指導,讓我增廣見聞了。

TOP

専心看了一片,很實用.
ddlam

TOP

感謝版主的分享,獲益良多
節省新手很多的時間也培養良好的習慣
只做自己瞭解的市場

TOP

回復 6# kingaaron


    謝謝大大的用心,受益匪淺,感謝!

TOP

感謝板主分享:
一、數數看你的程式裡有多少 "Select" ?
select 真的是不大需要, 我目前已經很少使用了, 大部分用在要取得特定位址, 以作為下一個指令參考之用o

三、過多/不必要的迴圈也會降低執行效率
我以前會用 do while or if 指令來判斷後繼續執行, 後來碰到 i=i+1 時會碰到溢位問題, 後來就改用 find 指令,
我感覺執行速度是快了很多, 這是否比較好?
James

TOP

感謝版主分享
我剛開始使用VBA的確也是把錄製到的巨集照單全收
不過 現在已經能寫出比較有效率的CODE了

TOP

感謝板主分享:
這是一篇好文章,對於初學者的我而言有很大的幫助
ASUS

TOP

        靜思自在 : 自己害自己,莫過於亂發脾氣。
返回列表 上一主題