Jak zrychlit (nebo alespoň nezpomalit) makro
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.
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.
S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.