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 

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.

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:

raději napíšeme:

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:

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

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:

Druhé rychlé makro:

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:

a tohoto pomalejšího kódu:

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:

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

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:

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

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

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář

Nejnovější komentáře

TOPlist