Excel / VBA程式精簡

Excel / VBA程式精簡

Post Views: 242

前一陣子幫家人處理公司的報表

報表是由幾個excel檔案內擷取部分內容、移除重複、組合成新資料等等

依據流程分別為7個sub程序

第一版程式的很多流程是用錄製巨集的方式產生

後來在VBA的寫作技巧與增進效能看到：應該避免不必要的select

而這個通常是在使用錄製巨集產生的程式碼中最常看到的

因為錄製巨集其實就是紀錄使用滑鼠、快捷鍵與excel功能的過程

在試算表的處理流程是無法避免選取點選儲存格，這也就導致程式碼中會有非常多的select

例如：將試算表1的A1:C1複製到試算表的A2:C2

如果使用錄製巨集，產生的程式碼會如下：

Sub 巨集1()

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy

    Sheets("工作表2").Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

End Sub

而這其實就是滑鼠選取範圍、按右鍵複製、在試算表2的A2貼上

如果以程式控制的方式來撰寫的話，程式碼就會非常精簡，方法也有好幾種

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
Public Sub test()

    Sheets(1).Range("A1:C1").Copy
    
    Sheets(2).Range("A2").PasteSpecial Paste:=xlPasteAll
        
End Sub


Public Sub test2()

    Sheets(1).Range("A1:C1").Copy Destination:=Sheets(2).Range("A3")

    
End Sub

Public Sub test3()

     Sheets(2).Range("A4:C4").Value = Sheets(1).Range("A1:C1").Value
     
End Sub

 

不過這樣就必須搞清楚物件的關係以及可以使用的方法或屬性

Sheets(“工作表2”).Select是選取Sheets物件

Range(“A2”).Select是選取Range物件

Sheets(“工作表2”).Range(“A2”)也是 Range物件

Sheets物件有Paste方法 還有PasteSpecial方法

但是，Range物件只有PasteSpecial方法

精簡程式碼的執行效能，比第一版節省了約25%的時間

不過比較玩味的是我的筆電執行所花的時間是學校桌機的2倍

我的筆電是12代的i5 32Gb Ram

學校桌機是 4代的i7 16Gb Ram

備註

後來發現是因為在筆電執行時，執行的檔案是存放在usb外接硬碟

移到內建的SSD硬碟執行時，確實會比學校的桌機快約30%

Tags: