10 způsobů, jak urychlit vaše makra

Vyhněte se nadměrnému odkazování

Při volání metody nebo vlastnosti objektu potřebuje projít rozhraním IDispatch součásti OLE. Volání těchto komponent OLE nějakou dobu trvá, takže snížení počtu odkazů na komponenty OLE může zlepšit rychlost kódu makra.

Pro vyvolání vlastností nebo metod objektu se obecně používá metoda reprezentace Object.Method  ,  tedy "." symbol se používá k vyvolání vlastností a metod.

Proto lze počet volání metody nebo vlastnosti posuzovat podle počtu symbolů ".". Čím méně "." tím rychleji kód běží.

Například následující příkaz obsahuje 3 symboly ".".

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

Následující příkaz má pouze jeden symbol ".".

Activewindow.Top = 100

Zde je několik triků, jak snížit počet symbolů "." běžet rychleji.

Za prvé, když potřebujete opakovaně odkazovat na stejný objekt, můžete objekt nastavit na proměnnou, abyste snížili počet volání. Například následující kód vyžaduje dvě volání na řádek.

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

Protože  na objekt Sheets("Sheet1")  je třeba odkazovat opakovaně, lze jej nejprve nastavit na proměnnou  sht  , takže každý kód stačí zavolat pouze jednou.

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

Za druhé, pokud nechcete deklarovat dočasnou proměnnou sht, můžete také použít   výše zmíněný příkaz With . Jak ukazuje následující příklad:

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

Za třetí,  když je mnoho smyček, snažte se ponechat vlastnosti a metody mimo smyčku.  Při opětovném použití hodnoty vlastnosti stejného objektu ve smyčce můžete nejprve přiřadit hodnotu vlastnosti zadané proměnné mimo smyčku a poté použít proměnnou ve smyčce, což může dosáhnout vyšší rychlosti. Jak ukazuje následující příklad:

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

Každá smyčka v tomto příkladu získá vlastnost Value buňky Cells(1,2). Pokud přiřadíte vlastnost Value Cells(1.2) proměnné před spuštěním smyčky, získáte rychlejší běh. Jak ukazuje následující příklad:

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

Výše uvedený kód volá  ThisWorkbook.Sheets("Sheet1")  při každém opakování. Můžete to provést rychleji pomocí  příkazu With  k přesunutí volání  ThisWorkbook.Sheets("Sheet1")  mimo smyčku. Jak ukazuje následující příklad:

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

Vyhněte se používání typů variant

Začátečníci obvykle preferují použití proměnných typu Variant, což má tu výhodu, že je méně komplikované, protože lze použít jakýkoli typ dat bez problému s přetečením paměti, pokud jsou data příliš velká pro datové typy Integer nebo Long. Data typu Varienmt však vyžadují více místa v paměti než ostatní specifikované typy (2 bajty pro data typu Integer, 4 bajty pro data Long a 16 bajtů pro data Variant), VBA vyžaduje více času na zpracování dat typu Variant než jiné specifikované typy. dat. Jak ukazuje následující příklad.

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

Ve výše uvedeném kódu řádky 8 až 13 provádějí 1 milion operací sčítání, odčítání, násobení a dělení proměnných Variant a řádky 17 až 22 provádějí 1 milion operací sčítání, odčítání, násobení a dělení proměnných typu Integer. Na mém počítači trvala operace proměnné Variant asi  0,09375  sekundy, zatímco operace proměnné Integer asi  0,03125  sekundy. Výsledky se mohou lišit počítač od počítače, ale  proměnné Variant jsou výrazně pomalejší než proměnné Integer .

Z tohoto důvodu  se doporučuje vyhnout se použití proměnných Variant, když můžete explicitně použít zadaný datový typ .