10 módszer a makrók felgyorsítására

Kerülje a túlzott hivatkozást

Egy objektum metódusának vagy tulajdonságának meghívásakor át kell mennie az OLE összetevő IDispatch felületén. Ezen OLE-összetevők hívása időt vesz igénybe, így az OLE-összetevőkre való hivatkozások számának csökkentése javíthatja a makrókód sebességét.

Az objektumtulajdonságok vagy metódusok meghívásához általában az  Object.Method ábrázolási módszerét  használják, azaz a "." szimbólum a tulajdonságok és metódusok meghívására szolgál.

Ezért a metódus- vagy tulajdonsághívások száma a "." szimbólumok száma alapján ítélhető meg. Minél kevesebb a "." szimbólum, annál gyorsabban fut a kód.

Például a következő utasítás 3 "." szimbólumot tartalmaz.

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

A következő állításnak csak egy "." szimbóluma van.

Activewindow.Top = 100

Íme néhány trükk a "." szimbólumok számának csökkentésére. gyorsabban futni.

Először is, ha ismételten hivatkoznia kell ugyanarra az objektumra, beállíthatja az objektumot egy változóra a hívások számának csökkentése érdekében. Például a következő kód vonalonként két hívást igényel.

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

Mivel a  Sheets("Sheet1")  objektumra többször kell hivatkozni, először beállítható egy  sht változóra  , így minden kódot csak egyszer kell meghívni.

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

Másodszor, ha nem akarunk ideiglenes sht változót deklarálni, használhatjuk a  korábban említett With  utasítást is. A következő példában látható módon:

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

Harmadszor,  ha sok a ciklus, próbálja meg a tulajdonságokat és metódusokat a cikluson kívül tartani.  Ha ugyanazon objektum egy tulajdonságértékét ismételten felhasználja egy hurokban, először hozzárendelheti a tulajdonság értékét egy megadott változóhoz a hurkon kívül, majd használhatja a hurokban lévő változót, amellyel gyorsabb sebesség érhető el. A következő példában látható módon:

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

Ebben a példában minden ciklus megkapja a Cells(1,2) cella Value tulajdonságát. Ha a Cells(1.2) Value tulajdonságát a ciklus megkezdése előtt hozzárendeli egy változóhoz, akkor gyorsabban fut. A következő példában látható módon:

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

A fenti kód  a ThisWorkbook.Sheets("Sheet1") függvényt  minden alkalommal meghívja, amikor ismétlődik. Ezt gyorsabban megteheti, ha a  With utasítással  a cikluson kívülre  helyezi át a hívást a  ThisWorkbook.Sheets("Sheet1") oldalra. A következő példában látható módon:

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

Kerülje a változattípusok használatát

A kezdők általában előnyben részesítik a Variant típusú változókat, aminek az az előnye, hogy kevésbé bonyolult, mivel bármilyen típusú adat felhasználható a memória túlcsordulási problémája nélkül, ha az adatok túl nagyok az Integer vagy Long adattípusokhoz. A Varienmt típusú adatok azonban több extra memóriaterületet igényelnek, mint a többi megadott típus (2 bájt egész adatokhoz, 4 bájt hosszú adatokhoz és 16 bájt Variant adatokhoz), a VBA több időt igényel a Variant típusú adatok feldolgozásához, mint a többi megadott típus. adatokból. Ahogy a következő példa mutatja.

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

A fenti kódban a 8-tól 13-ig terjedő sorok 1 millió összeadási, kivonási, szorzási és osztási műveletet hajtanak végre a Variant változók, a 17-22 sorok pedig 1 millió műveletet egész változók összeadására, kivonására, szorzására és osztására. Számítógépemen a Variant változó működése körülbelül  0,09375  másodpercig, míg az Integer változó működése körülbelül  0,03125  másodpercig tartott. Az eredmények számítógépenként változhatnak, de  a Variant változók lényegesen lassabbak, mint az Integer változók .

Emiatt  ajánlatos kerülni a Variant változók használatát, ha kifejezetten használhatja a megadott adattípust .