10 Cara Mempercepat Makro Anda

Elakkan Rujukan Berlebihan

Apabila memanggil kaedah atau sifat objek, ia perlu melalui antara muka IDispatch komponen OLE. Panggilan ke komponen OLE ini mengambil masa, jadi mengurangkan bilangan rujukan kepada komponen OLE boleh meningkatkan kelajuan kod makro.

Untuk penyebutan sifat atau kaedah objek, kaedah perwakilan  Object.Method  biasanya digunakan, iaitu, "." simbol digunakan untuk memanggil sifat dan kaedah.

Oleh itu, bilangan kaedah atau panggilan harta boleh dinilai mengikut bilangan simbol ".". Semakin kurang "." simbol, lebih cepat kod berjalan.

Sebagai contoh pernyataan berikut mengandungi 3 simbol ".".

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

Pernyataan berikut hanya mempunyai satu simbol ".".

Activewindow.Top = 100

Berikut ialah beberapa helah untuk mengurangkan bilangan simbol "." untuk berlari lebih laju.

Pertama, apabila anda perlu merujuk kepada objek yang sama berulang kali, anda boleh menetapkan objek kepada pembolehubah untuk mengurangkan bilangan panggilan. Sebagai contoh, kod berikut memerlukan dua panggilan setiap talian.

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

Oleh kerana  objek Sheets("Sheet1")  perlu dirujuk berulang kali, ia boleh ditetapkan kepada pembolehubah  sht  terlebih dahulu, supaya setiap kod hanya perlu dipanggil sekali.

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

Kedua, jika anda tidak mahu mengisytiharkan pembolehubah sementara sht, anda juga boleh menggunakan  pernyataan Dengan  yang dinyatakan sebelum ini. Seperti yang ditunjukkan dalam contoh berikut:

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

Ketiga,  apabila terdapat banyak gelung, cuba simpan sifat dan kaedah di luar gelung.  Apabila menggunakan semula nilai sifat objek yang sama dalam gelung, anda boleh mula-mula menetapkan nilai sifat kepada pembolehubah yang ditentukan di luar gelung, dan kemudian menggunakan pembolehubah dalam gelung, yang boleh mencapai kelajuan yang lebih pantas. Seperti yang ditunjukkan dalam contoh berikut:

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

Setiap gelung dalam contoh ini mendapat sifat Nilai sel Sel(1,2). Jika anda memperuntukkan sifat Nilai Sel(1.2) kepada pembolehubah sebelum gelung bermula, anda akan mendapat larian yang lebih pantas. Seperti yang ditunjukkan dalam contoh berikut:

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

Kod di atas memanggil  ThisWorkbook.Sheets("Sheet1")  setiap kali ia gelung. Anda boleh melakukan ini dengan lebih pantas dengan menggunakan  pernyataan Dengan  untuk mengalihkan panggilan ke  ThisWorkbook.Sheets("Sheet1")  di luar gelung. Seperti yang ditunjukkan dalam contoh berikut:

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

Elakkan Menggunakan Jenis Varian

Pemula biasanya lebih suka menggunakan pembolehubah jenis Varian, yang mempunyai kelebihan menjadi kurang rumit kerana sebarang jenis data boleh digunakan tanpa masalah limpahan memori jika data terlalu besar untuk jenis data Integer atau Long. Walau bagaimanapun, data jenis Varienmt memerlukan lebih banyak ruang memori tambahan daripada jenis yang ditentukan lain (2 bait untuk data Integer, 4 bait untuk data Panjang dan 16 bait untuk data Varian), VBA memerlukan lebih banyak masa untuk memproses data jenis Varian daripada jenis lain yang ditentukan daripada data. Seperti yang ditunjukkan oleh contoh berikut.

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

Dalam kod di atas, baris 8 hingga 13 melakukan 1 juta operasi penambahan, penolakan, pendaraban dan pembahagian pembolehubah Varian, dan baris 17 hingga 22 melakukan 1 juta operasi penambahan, penolakan, pendaraban dan pembahagian pembolehubah Integer. Pada komputer saya, pengendalian pembolehubah Varian mengambil masa kira-kira  0.09375  saat, manakala pengendalian pembolehubah Integer mengambil masa kira-kira  0.03125  saat. Hasilnya mungkin berbeza dari satu komputer ke komputer, tetapi  Pembolehubah Varian adalah lebih perlahan daripada pembolehubah Integer .

Atas sebab ini,  adalah disyorkan untuk mengelak daripada menggunakan pembolehubah Varian apabila anda boleh menggunakan jenis data yang ditentukan secara eksplisit .