Jste zde: Úvodní stránka » excel » vba-triky » zrychleni-kodu-vba-excel

Zrychlení kódu ve VBA - Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na Excel - naučte se efektivně využívat Excel - nejoblíbenější (Hodnocení 97%, přes 13.000 studentů, + 750 pozitivních zpětných vazeb) - Získat kurz
MaxiKurz Excel - získejte lepší práci, více peněz, staňte se nepostradatelní (přes 6 hodin videí, 160 lekcí) - Získat kurz
G tabulky... - pokud nemáte rádi Excel využijte kurz na Google-tabulky

Jak zrychlit provádění VBA kódu v Excelovských aplikacích

Úvodem

Po vytvoření aplikace ve VBA a naplnění skutečnými daty zjistíte, že práce s výpočtem trvá dlouho. Oproti testovacímu balíčku (kdy jste neměli tolik dat). Většinou to nevadí. Pár minut za které se požadované úkony provedou automaticky, oproti několika dnům, kdyby se výpočet prováděl ručně nevadí. Pokud přesto chcete zrychlit v článku proberu několik možností.

Application.ScreenUpdating

Výpočet provádějte na pozadí bez obnovování obrazovky.

Ukázkou rychlosti provedení zápisu číselné řady v listu s názvem 1. Nápad jsem si vypůjčil z http://www.vbforums.com/. Neřeším optimalizaci vnitřního kódu.


For i = 1 To 1000
    Worksheets("1").Range("A1").Offset(i, 0).Value = i
    Worksheets("1").Range("A1").Offset(i, 0).Activate
Next i

Poznámka: Tento kód je uveden jen pro demonstraci provedení rychlosti při (ne)použití Application.ScreenUpdating.

Kód doplněný o Application.ScreenUpdating:


Application.ScreenUpdating = False
   
For i = 1 To 1000
    Worksheets("1").Range("A1").Offset(i, 0).Value = i
    Worksheets("1").Range("A1").Offset(i, 0).Activate
Next i

Application.ScreenUpdating = True

Rychlost provedení bude znatelně rychlejší ;)

Application.Calculation

Pokud není potřeba výpořet proveďte na konci

Nema-li změna buněk vliv na výpočet, případně nepotřebujete-li průběžně při změně v listu provádět výpočet, lze automaticky přepočet vypnout.


Application.Calculation = xlCalculationManual

Na konci makra (VBA) nezapomenout opět zapnout.


Application.Calculation = xlCalculationAutomatic

Application.DisplayAlerts

Nezobrazovat chybové hlášky.

Vypneme zobrazování dialogových a potvrzovacích oken, které se v průběhu práce makra mohou vyskytnout (například potvrzení při ukládání souboru, odstraňování listu, atd.). Pokud to máte v programu dobře ošetřené nemusíte tyto hlášky zbytečně zobrazovat. Vypnutí se provede jednoduše:


Application.DisplayAlerts = False

Před ukončením, či v místech kde je potřeba zobrazování opět zapnete:


  Application.DisplayAlerts = True

Vyplňování buněk

Odkazujte se efektivně.

V jedné knize jsem četl poučku čím méně "teček" VBA prochází tím je rychlejší. Jednak si tuto poučku zapamatoval. Využitím této myšlenky se snažím výsledne kódy optimalizovat. O co jde. Odkazujete se na danou buňku v listě takto:


Workbooks("muj-sesit.xlsm").Sheets("List22").Range("A1")

Kód obsahuje dvě tečky. Teď ho doplníme ať se na prvních 99 řádku napíše text: pomalý kód. Provedeme v cyklu jako nevhodné pomalé řešení:


For i = 1 to 99
	Workbooks("muj-sesit.xlsm").Sheets("List22").Range("A1").offset(i-1) = "pomalý kód"
Next

Kód obsahuje tři tečky. Pro 99 řádku je to 297 teček. Takže pomalý. Když kód upravíme takž že oblast dáme do proměnné.


Set MojePromena = Workbooks("muj-sesit.xlsm").Sheets("List22").Range("A1")
For i = 1 to 99
	MojePromena.offset(i-1) = "rychlý kód"
Next

Již máme 101 teček a tím pádem i rychlejší kód.

Závěrem

Další možnosti zrychlení VBA budu do článku přidávat průběžně. Máte-li k dispozici kód co lépe může demonstrovat rychlost, budu rád přidáte-li do komentářů.

Článek byl aktualizován: 19.09.2020 11:07

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

Komentáře

Martin

Přidáno: 24.07.13 07:31

screenupdating mi velice pomohl. Protože začínám tak jsme si udělal zatím jen primitivní macro pro asi 90 listů. Čas počítaní byl i tak řekněme 10s což je nic, ale s vypnutím obnovování obrazovky je vypočítáno tak do 2s.

Pavel Lasák

Přidáno: 24.07.13 11:11

To Martin: Ten pocit si vzpomínám, když jsem poprvé použil a zrychlil makro... Jsem rád že jsem pomohl.

Jirka

Přidáno: 20.08.13 12:58

Vyplňování buněk: Možná by se dalo provádění kódu zrychlit i použitím with: With Workbooks("muj-sesit.xlsm").Sheets ("List22").Range("A1") For i = 1 to 99 .offset(i-1) = "rychlý kód" Next i End With Kód se zrychlí i v případě, vyhneme-li se proměnným typu Variant, který jsou vždy pokud je předem nenadimenzujeme pod hlavičkou funkce, např: Sub VyplnBunky() Dim i as integer ... kód ... End Sub

Jirka

Přidáno: 20.08.13 13:16

Pardon, zkusím příklad ještě jednou (viz předchozí komentář)s použitím code, co to udělá: With Workbooks("muj-sesit.xlsm").Sheets("List22").Range("A1") For i = 1 to 99 .offset(i-1) = "rychlý kód" Next i End With

Radek

Přidáno: 20.04.14 22:31

ne všichni mají to štěstí, že jim běží MS Office přímo v jejich počítači. Já v práci přistupuji na office přes nějaký server a rychlost lajny aplikaci dost shodí, to mi dalo možnost všimnout si pár zrychlení. 1) Skutečné zrychlení: Když nepotřebujete s uživatelem přímo interagovat a výpočty v sešitu lze odsunout na později proveďte je v události sešitu Private Sub Workbook_Open() Nevím proč, ale při otevření dokumentu se dovedou řádově stovky tisíc operací zavřených do for next navíc s if then provést v mžiku. Vhodné pro velké databázové soubory 2) virtuální zrychlení pokud nepotřebujete s uživatelem přímo interagovat, ale je třeba sešit nějak učesat před zavřením, vložte to do události listu Private Sub Workbook_BeforeClose(Cancel As Boolean) Výpočetní operace v řádech desítek tisíc nebudou obtěžovat. Kurzor sice chvilku nad aplikací excel bliká, ale uživatel to už zasejvoval a může se věnovat jiným věcem. Tohle zpomalení vůbec nevnímá. 3) virtuální zrychlení je třeba reagovat na chování uživatele a např. otevírat jiné sešity a kopírovat velký objem dat, někam to přepisovat... Takto dlouhou operaci je lepší spouštět přímo uživatem přes commandButton a pokud to netrvá enormě dlouho ještě vypnout Application.ScreenUpdating = True Uživatel je v pohodě. Má pocit, že klikl na něco velkýho, protože teď mu před očima probíhá otevírání nových sešitů, mazání nepotřebných sloupců, kopírování, výpočty, barvičky, ... No a až až si na to zvykne, tak zadáte Application.ScreenUpdating = False a jste opět za boha Pro někoho jsou virtuální zrychlení nepodstatná, ale je třeba neustále myslet na uživatele. Aplikace je jen tak rychlá, jak vlastně je rychlá

Vladimír

Přidáno: 28.10.14 16:26

Mám vypnutou obrazovku pomocí Application.DisplayAlerts = False, ale při ukládání nebo otevíránání velkého souboru zmizí hlavní obrazovka a zůstane bílá. Po dokončení makra opět obrazovka naskočí správně. Nemohu se toho zbavit. Nemáte nějakou radu ? Díky Vladimír

Jirka Wee

Přidáno: 12.11.14 08:15

Já když zpracovávám údaje s opravdu BIG tabulkou (cca 200 tisíc řádků), tak si jí na začátku nadimenzuju a pak jedu jen v indexech. Je to šílená rychlost! např. DIM dtab as Variant pradek = Cells(987654, 1).End(xlUp).Row dtab = Range("a1:z" & pradek) ... a pak všude místo Range("a1"), používáme dtab(1,1).. atd. světu mír...

Honza

Přidáno: 26.11.15 15:27

Díky moc, za tento článek. Zkrátil mi čas procesu na 1/3.

Jiří Beran

Přidáno: 26.12.15 10:17

Ahoj, to s tím počtem teček je vtipná pomůcka :) Jinak ještě bych doplnil, že makro se může zrychlit: 1) Deklarací správného datového typu proměnných. Čím "menší" datový typ deklarujeme, tím se rychleji zpracovává. 2) Používáním "with", když chceme pracovat s více buňkami. I když to souvisí s tím efektivním adresováním uvedeným v článku. U podobných postupů jsem měřil, jaký mají skutečný dopad na délku makra. Výsledky jsem dal sem. http://vyuka-excelu.cz/navody/makra/jak-zrychlit-nebo-alespon-nezpomalit-makro/