Jak zrychlit (nebo alespoň nezpomalit) makro

V tomto článku jsou uvedeny některé tipy na to, jak vytvořit rychlejší makro. 

U některých jednoduchých maker se s výkonností nemusíme trápit, naopak u jiných může být naprosto nutné  brát rychlost do úvahy.

1. Správná logika makra

Zaprvé musí být makro vytvořené tak, aby počítač musel provádět co nejméně kroků. Samozřejmě jde hlavně o kroky, které jsou uvnitř cyklů, a opakují se tedy nejčastěji.  

2. Vypnutí přepočtů

Během trvání makra je obvykle zbytečné, aby se přepočítávaly vzorečky v listech. Stačí, když se přepočtou až nakonec. Proto je vhodné na začátku makra přepočítávání vypnout, a na konci zapnout 

'Vypnout přepočet
Application.Calculation = xlCalculationManual 
'Zapnout přepočet
Application.Calculation = xlCalculationAutomatic 

3. Vypnutí aktualizace obrazovky

Podobně nebývá nutné, aby se po každém kroku makra aktualizovala obrazovka. Na začátku tedy můžeme aktualizaci vypnout, a na konci zase zapnout.

'Vypnutí updatů
Application.ScreenUpdating = FALSE
'Zapnutí updatů
Application.ScreenUpdating = TRUE

Např. v tomto bloku se vypnutím aktualizace zobrazovky na mém počítači ušetřilo cca 5% času, jinde to může být více, nebo samozřejmě také nic – pokud aplikace nemá žádný grafický výstup.

4. Nevybírání buněk

V makrech je často zbytečné (a zpomalující) vybírat buňku kvůli tomu, abychom s ní něco udělali. Takže místo tohoto kódu:

Range("A1:A2").Select
Selection.ClearContents

raději napíšeme:

Range("A1:A2").ClearContents

Ušetříme tím zhruba polovinu času. Tento problém často vznikne tím, že makro míst napsání nahrajeme – při nahrávání maker Excel používá první z dvou možností.

5. Omezení přenosu dat mezi sešitem a pamětí

Makro je zásadně výrazně zpomalované tím, když se přenášejí data z buněk do výpočtů v makrech, nebo naopak když se ukládají hodnoty z maker do buněk.

Příkladem jsou tato dvě ukázky VBA kódu se stejnou funkcí. Toto makro s ukládáním do buněk trvá na určitém počítači trvá 16 sekund:

For x = 1 To 100000
    Range("a1").Value = Range("a1").Value + Rnd - Rnd
Next x

Toto makro bez ukládání do buněk trvá 0,2 sekundy:

For x = 1 To 100000
    a = a + Rnd - Rnd
Next x

Pokud pracujeme s oblastí buněk, není prakticky možné ukládat do proměnné každou zvlášť a hodnoty načítáme do polí.

Rozdíl si můžete vyzkoušet na dvou makrech, které provádějí jednoduchou početní operaci s buňkami ve sloupci A. Makra jsou funkčně stejná. Zatímco ale první makro, pracující přímo s buňkami, trvá 9,5 sekundy, tak druhé makro s využitím polí trvá na stejném počítači 0,15 sekundy.

První pomalé makro:

For opakovani = 1 To 1000
    For radek = 1 To 1000
        If Cells(radek, 1) > 0.5 Then
            Cells(radek, 1) = Cells(radek, 1) + Rnd - 0.5
        End If
    Next radek
Next opakovani

Druhé rychlé makro:

'Načtení prvního sloupce do pole
For radek = 1 To 1000
    hodnota_v_poli(radek) = Cells(radek, 1).Value
Next radek
'Opakované procházení buněk v prvním sloupci a početní operace s nimi
For opakovani = 1 To 1000
    For radek = 1 To 1000
        If hodnota_v_poli(radek) > 0.5 Then
            hodnota_v_poli(radek) = hodnota_v_poli(radek) + Rnd - 0.5
        End If
    Next radek
Next opakovani
'Uložení výsledku zpět do prvního sloupce
For radek = 1 To 1000
    Cells(radek, 1).Value = hodnota_v_poli(radek)
Next radek

6. Nepoužívání zbytečných částí adresy

Pokud se odkazujeme na buňky, je efektivnější odkazovat se pouze na buňku a neodkazovat na list. V jednom zdroji, který už nejsem schopný najít a citovat, píše autor, že platí pravidlo “čím méně teček  odkazech, tím pomalejší zpracování”. To je  velmi dobře řečené.

Zatímco rozdíl ve zpracování tohoto rychlejšího kódu:

Cells(1, 1).Value = 1

a tohoto pomalejšího kódu:

worksheets("List1").Cells(1, 1).Value = 1

mě vychází na 10% – 15% času.

Při “kratších” adresách je nutné dávat pozor na to, abychom akce vždy vykonávali na správném listu. Jinak se samozřejmě může snadno stát, že nežádoucím způsobem změníme nebo třeba i smažeme úplně jiný list – a je to pro mě osobně důvod, proč takto makra optimalizuji jen pokud jsou opravdu pomalá. U nenáročných maker se raději dopřeji bezpečí “dlouhých” odkazů.

7. Používání WITH

Určitý vliv na rychlost může mít i používání bloků WITH.

Například tento kód:

With Cells(1, 1).Font
    .Italic = True
    .Color = RGB(1, 1, 1)
    .Underline = True
End With

je o několik procent rychlejší než tento kód:

Cells(1, 1).Font.Italic = True
Cells(1, 1).Font.Color = RGB(1, 1, 1)
Cells(1, 1).Font.Underline = True

Deklarace proměnných

Rychlost makra může zásadně ovlivnit to, jestli na začátku deklarujeme nebo nedeklarujeme proměnné.

Tento rozdíl je způsobený tím, že pokud proměnnou nenadefinujeme, nastaví si ji aplikace “pro jistotu” automaticky na typ “variant”. Ten je ale ze všech typů paměťově nejnáročnější – zabere 16 bajtů, zatímco např. “integer” jen dva bajty, “long” a “singl” 4 bajty a “double” 8 bajtů.

S větším objemem dat se logicky pomaleji manipuluje a makro probíhá pomaleji. V našem případě to vychází na mém trochu obstrarožním Lenovu na lehce přes 20 sekund s deklarací proměnných x a y a kolem 56 sekund bez deklarace.  Rozdíl je tedy zásadní.

Rychlý kód s deklaracemi proměnných:

'Měříč času
Sub čas_trvání_makra()
'Definice proměnných
Dim x As Long
Dim y As Single
'Samotný kód makra
For x = 1 To 100000000
    y = y + x * y * Rnd - Rnd * y * x
Next x
End Sub

Pomalý kód bez deklarací proměnných:

'Měříč času
Sub čas_trvání_makra()
'Definice proměnných
Dim zacatek As Double
Dim trvani As Double
'Dim x As Long
'Dim y As Single
'Samotný kód makra
For x = 1 To 100000000
    y = y + x * y * Rnd - Rnd * y * x
Next x
End Sub

Pokud má někdo další nápad, jak makra z pohledu rychlosti optimalizovat, budu rád, když napíšete do diskuse.