10 načinov za pospešitev makrov

Izogibajte se pretiranemu sklicevanju

Ko kličete metodo ali lastnost objekta, mora iti skozi vmesnik IDispatch komponente OLE. Klici teh komponent OLE zahtevajo nekaj časa, zato lahko zmanjšanje števila referenc na komponente OLE izboljša hitrost kode makra.

Za priklic lastnosti ali metod objekta   se na splošno uporablja predstavitvena metoda Object.Method , to je "." simbol se uporablja za priklic lastnosti in metod.

Zato lahko število klicev metode ali lastnosti ocenimo glede na število simbolov ".". Manj kot je "." simbol, hitreje teče koda.

Naslednji stavek na primer vključuje 3 simbole ".".

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

Naslednji stavek ima samo en simbol ".".

Activewindow.Top = 100

Tukaj je nekaj trikov za zmanjšanje števila simbolov "." teči hitreje.

Prvič, ko se morate večkrat sklicevati na isti objekt, lahko predmet nastavite na spremenljivko, da zmanjšate število klicev. Naslednja koda na primer zahteva dva klica na vrstico.

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

Ker  se je treba predmet Sheets("Sheet1")  večkrat sklicevati, ga je mogoče najprej nastaviti na spremenljivko  sht  , tako da je treba vsako kodo poklicati le enkrat.

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

Drugič, če ne želite deklarirati začasne spremenljivke sht, lahko uporabite tudi   prej omenjeni stavek With . Kot je prikazano v naslednjem primeru:

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

Tretjič,  ko je zank veliko, poskusite obdržati lastnosti in metode zunaj zanke.  Pri ponovni uporabi vrednosti lastnosti istega predmeta v zanki lahko vrednost lastnosti najprej dodelite določeni spremenljivki zunaj zanke in nato uporabite spremenljivko v zanki, s čimer lahko dosežete večjo hitrost. Kot je prikazano v naslednjem primeru:

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

Vsaka zanka v tem primeru dobi lastnost Value celice Cells(1,2). Če spremenljivki dodelite lastnost Value funkcije Cells(1.2), preden se zanka začne, bo zagon hitrejši. Kot je prikazano v naslednjem primeru:

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

Zgornja koda pokliče  ThisWorkbook.Sheets("Sheet1")  vsakič, ko se zažene. To lahko storite hitreje, če s  stavkom With  premaknete klic  ThisWorkbook.Sheets("Sheet1")  izven zanke. Kot je prikazano v naslednjem primeru:

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

Izogibajte se uporabi vrst različic

Začetniki običajno raje uporabljajo spremenljivke tipa Variant, katerih prednost je, da je manj zapletena, saj je mogoče uporabiti katero koli vrsto podatkov brez težave s prepolnitvijo pomnilnika, če so podatki preveliki za vrste podatkov Integer ali Long. Vendar pa podatki tipa Varienmt zahtevajo več dodatnega pomnilniškega prostora kot drugi določeni tipi (2 bajta za podatke Integer, 4 bajti za podatke Long in 16 bajtov za podatke Variant), VBA potrebuje več časa za obdelavo podatkov tipa Variant kot drugi določeni tipi podatkov. Kot kaže naslednji primer.

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

V zgornji kodi vrstice od 8 do 13 izvajajo 1 milijon operacij seštevanja, odštevanja, množenja in deljenja spremenljivk Variant, vrstice od 17 do 22 pa 1 milijon operacij seštevanja, odštevanja, množenja in deljenja spremenljivk Integer. V mojem računalniku je delovanje spremenljivke Variant trajalo približno  0,09375  sekunde, medtem ko je delovanje spremenljivke Integer trajalo približno  0,03125  sekunde. Rezultati se lahko razlikujejo od računalnika do računalnika, vendar  so spremenljivke Variant bistveno počasnejše od spremenljivk Integer .

Iz tega razloga  je priporočljivo, da se izogibate uporabi spremenljivk Variant, kadar lahko izrecno uporabite navedeni tip podatkov .