Přeskočit navigaci | Přeskočit na novinky
     

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 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: 01.11.2014 14:43

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

Pavel Lasák

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2000 (od 2004 ne této doméně) - Roku 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 také na on-line videích pro SEDUO. Školím a konzultuji, učím na MUNI. Hlavně tvořím tento web. Je zde k dispozici přes 1.000 návodu, tipů a triků včetně stovek různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


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/

m_ato

Přidáno: 15.04.16 10:18

Dakujem za clanok. Makro, ktore pracovalo par minut s Application.ScreenUpdating = False teraz zvladne svoju ulohu za asi 5 sekund. Palec hore.







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2017 | 25529

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.