10 moduri de a vă accelera macrocomenzile

Evitați referința excesivă

Când apelați o metodă sau o proprietate a unui obiect, acesta trebuie să treacă prin interfața IDispatch a componentei OLE. Apelurile către aceste componente OLE necesită timp, astfel încât reducerea numărului de referințe la componentele OLE poate îmbunătăți viteza codului macro.

Pentru invocarea proprietăților sau metodelor obiectului, se folosește în general metoda de reprezentare a  Object.Method  , adică „." simbolul este folosit pentru a invoca proprietăți și metode.

Prin urmare, numărul de apeluri de metodă sau de proprietate poate fi judecat în funcție de numărul de simboluri „.”. Cu cât "." simbol, cu atât mai repede rulează codul.

De exemplu, următoarea declarație include 3 simboluri „.”.

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

Următoarea afirmație are un singur simbol „.”.

Activewindow.Top = 100

Iată câteva trucuri pentru a reduce numărul de simboluri "." a alerga mai repede.

În primul rând, când trebuie să vă referiți la același obiect în mod repetat, puteți seta obiectul la o variabilă pentru a reduce numărul de apeluri. De exemplu, următorul cod necesită două apeluri pe linie.

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

Deoarece  obiectul Sheets(„Sheet1”)  trebuie să fie referit în mod repetat, acesta poate fi setat mai întâi la o variabilă  sht  , astfel încât fiecare cod să fie apelat o singură dată.

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

În al doilea rând, dacă nu doriți să declarați o variabilă temporară sht, puteți utiliza și  instrucțiunea With  menționată mai devreme. După cum se arată în exemplul următor:

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

În al treilea rând,  când există o mulțime de bucle, încercați să păstrați proprietățile și metodele în afara buclei.  Când reutilizați o valoare de proprietate a aceluiași obiect într-o buclă, puteți mai întâi să atribuiți valoarea proprietății unei variabile specificate din afara buclei și apoi să utilizați variabila în buclă, care poate atinge o viteză mai mare. După cum se arată în exemplul următor:

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

Fiecare buclă din acest exemplu primește proprietatea Value a celulei Cells(1,2). Dacă atribuiți proprietatea Value a Cells(1.2) unei variabile înainte de a începe bucla, veți obține o rulare mai rapidă. După cum se arată în exemplul următor:

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

Codul de mai sus apelează  ThisWorkbook.Sheets(„Sheet1”)  de fiecare dată când se execută în buclă. Puteți face acest lucru mai rapid folosind  instrucțiunea With  pentru a muta apelul la  ThisWorkbook.Sheets("Sheet1")  în afara buclei. După cum se arată în exemplul următor:

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

Evitați utilizarea tipurilor de variante

Începătorii preferă, de obicei, să folosească variabile de tip Variant, ceea ce are avantajul de a fi mai puțin complicat, deoarece orice tip de date poate fi folosit fără problema depășirii memoriei dacă datele sunt prea mari pentru tipurile de date Integer sau Long. Cu toate acestea, datele de tip Varienmt necesită mai mult spațiu de memorie suplimentar decât celelalte tipuri specificate (2 octeți pentru date întregi, 4 octeți pentru date lungi și 16 octeți pentru date variante), VBA necesită mai mult timp pentru procesarea datelor de tip variantă decât alte tipuri specificate de date. După cum arată următorul exemplu.

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

În codul de mai sus, liniile de la 8 la 13 fac 1 milion de operații de adunare, scădere, înmulțire și împărțire a variabilelor Variant, iar liniile 17 la 22 fac 1 milion de operații de adunare, scădere, înmulțire și împărțire a variabilelor întregi. Pe computerul meu, funcționarea variabilei Variant a durat aproximativ  0,09375  secunde, în timp ce funcționarea variabilei Integer a durat aproximativ  0,03125  secunde. Rezultatele pot varia de la computer la computer, dar  variabilele Variant sunt semnificativ mai lente decât variabilele Integer .

Din acest motiv,  se recomandă să evitați utilizarea variabilelor Variant atunci când puteți utiliza în mod explicit tipul de date specificat .