10 วิธีในการเพิ่มความเร็วให้มาโครของคุณ

หลีกเลี่ยงการอ้างอิงมากเกินไป

เมื่อเรียกวิธีการหรือคุณสมบัติของวัตถุ จำเป็นต้องผ่านอินเทอร์เฟซ IDispatch ของคอมโพเนนต์ OLE การเรียกไปยังคอมโพเนนต์ OLE เหล่านี้ต้องใช้เวลา ดังนั้นการลดจำนวนการอ้างอิงไปยังคอมโพเนนต์ OLE สามารถปรับปรุงความเร็วของโค้ดแมโครได้

สำหรับการเรียกใช้คุณสมบัติของอ็อบเจ็กต์หรือเมธอด โดยทั่วไปจะใช้เมธอดการแสดงของ  Object.Method  นั่นคือ "." สัญลักษณ์ใช้เพื่อเรียกคุณสมบัติและวิธีการ

ดังนั้นจำนวนวิธีหรือการเรียกคุณสมบัติจึงสามารถตัดสินได้ตามจำนวนสัญลักษณ์ "." ยิ่ง "" น้อยลง สัญลักษณ์ ยิ่งโค้ดทำงานเร็วขึ้น

ตัวอย่างเช่น ข้อความต่อไปนี้มีสัญลักษณ์ 3 ตัว "."

ThisWorkbook.Sheet1.Range("A1").Value = 100

ข้อความต่อไปนี้มีสัญลักษณ์ "." เพียงตัวเดียวเท่านั้น

Activewindow.Top = 100

ต่อไปนี้เป็นเคล็ดลับในการลดจำนวนสัญลักษณ์ "" เพื่อให้วิ่งเร็วขึ้น

ขั้นแรก เมื่อคุณต้องการอ้างถึงออบเจ็กต์เดียวกันซ้ำๆ คุณสามารถตั้งค่าออบเจ็กต์ให้เป็นตัวแปรเพื่อลดจำนวนการโทรได้ ตัวอย่างเช่น รหัสต่อไปนี้ต้องมีการเรียกสองครั้งต่อบรรทัด

ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300

เนื่องจาก  ออบเจ็กต์ Sheets("Sheet1")  จำเป็นต้องถูกอ้างอิงซ้ำๆ จึงสามารถตั้งค่าเป็นตัวแปร  sht  ก่อนได้ เพื่อให้แต่ละโค้ดถูกเรียกเพียงครั้งเดียว

Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300

ประการที่สอง หากคุณไม่ต้องการประกาศตัวแปรชั่วคราว sht คุณสามารถใช้  คำสั่ง With  ที่กล่าวถึงข้างต้นได้ ดังแสดงในตัวอย่างต่อไปนี้:

With ThisWorkbook.Sheets("Sheet1")
    .Cells(1, 1) = 100
    .Cells(2, 1) = 200
    .Cells(3, 1) = 300
End With

ประการที่สาม  เมื่อมีลูปจำนวนมาก พยายามเก็บคุณสมบัติและวิธีการไว้นอกลูป  เมื่อนำค่าคุณสมบัติของออบเจ็กต์เดียวกันกลับมาใช้ซ้ำในลูป ขั้นแรกคุณสามารถกำหนดค่าคุณสมบัติให้กับตัวแปรที่ระบุภายนอกลูป จากนั้นใช้ตัวแปรในลูปซึ่งจะทำให้ได้ความเร็วที่เร็วขึ้น ดังแสดงในตัวอย่างต่อไปนี้:

For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next i

แต่ละวงในตัวอย่างนี้ได้รับคุณสมบัติค่าของเซลล์ Cells(1,2) หากคุณกำหนดคุณสมบัติ Value ของ Cells(1.2) ให้กับตัวแปรก่อนที่การวนซ้ำจะเริ่มต้น คุณจะได้รับการทำงานที่เร็วขึ้น ดังแสดงในตัวอย่างต่อไปนี้:

tmp = Cells(1, 2).Value
For i = 1 To 1000
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
    ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next i

โค้ดด้านบนเรียก  ThisWorkbook.Sheets("Sheet1")  ทุกครั้งที่วนซ้ำ คุณสามารถทำได้เร็วขึ้นโดยใช้  คำสั่ง With  เพื่อย้ายการเรียกไปยัง  ThisWorkbook.Sheets("Sheet1")  นอกวง ดังแสดงในตัวอย่างต่อไปนี้:

tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
    For i = 1 To 1000
        .Cells(1, 1) = tmp
        .Cells(2, 1) = tmp
        .Cells(3, 1) = tmp
    Next i
End With

หลีกเลี่ยงการใช้ประเภทตัวแปร

ผู้เริ่มต้นมักนิยมใช้ตัวแปรประเภท Variant ซึ่งมีข้อดีคือมีความซับซ้อนน้อยกว่า เนื่องจากข้อมูลประเภทใดก็ได้สามารถใช้ได้โดยไม่มีปัญหาเรื่องหน่วยความจำล้น หากข้อมูลมีขนาดใหญ่เกินไปสำหรับประเภทข้อมูลจำนวนเต็มหรือแบบยาว อย่างไรก็ตาม ข้อมูลประเภท Varienmt ต้องการพื้นที่หน่วยความจำเพิ่มเติมมากกว่าประเภทอื่นๆ ที่ระบุ (2 ไบต์สำหรับข้อมูลจำนวนเต็ม 4 ไบต์สำหรับข้อมูลแบบยาว และ 16 ไบต์สำหรับข้อมูล Variant) VBA ต้องใช้เวลาในการประมวลผลข้อมูลประเภท Variant มากกว่าประเภทที่ระบุอื่นๆ ของข้อมูล ดังตัวอย่างต่อไปนี้

Sub VariantTest()
    Dim i As Long
    Dim ix As Integer, iy As Integer, iz As Integer
    Dim vx As Variant, vy As Variant, vz As Variant
    Dim tm As Date
    vx = 100: vy = 50
    tm = Timer
    For i = 1 To 1000000
        vz = vx * vy
        vz = vx + vy
        vz = vx - vy
        vz = vx / vy
    Next i
    Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
    ix = 100: iy = 50
    tm = Timer
    For i = 1 To 1000000
        iz = ix * iy
        iz = ix + iy
        iz = ix - iy
        iz = ix / iy
    Next i
    Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End Sub

ในโค้ดข้างต้น บรรทัดที่ 8 ถึง 13 ดำเนินการ 1 ล้านการดำเนินการของการบวก การลบ การคูณและการหารของตัวแปร Variant และบรรทัดที่ 17 ถึง 22 ดำเนินการ 1 ล้านการดำเนินการของการบวก ลบ คูณ และหารตัวแปรจำนวนเต็ม บนคอมพิวเตอร์ของฉัน การทำงานของตัวแปร Variant ใช้เวลาประมาณ  0.09375  วินาที ในขณะที่การทำงานของตัวแปร Integer ใช้เวลาประมาณ  0.03125  วินาที ผลลัพธ์อาจแตกต่างกันไปในคอมพิวเตอร์แต่ละเครื่อง แต่  ตัวแปร Variant จะช้ากว่าตัวแปร Integer อย่างมาก

ด้วยเหตุนี้  เราจึงแนะนำให้หลีกเลี่ยงการใช้ตัวแปร Variant เมื่อคุณสามารถใช้ประเภทข้อมูลที่ระบุได้อย่างชัดเจน