Jak používat (velmi rychlá) VBA makra v MS Office

Znám hodně lidí, typicky různí účetní, ekonomové atp., kteří používají makra v aplikacích MS Office, aniž jim rozumí. Prostě jim někdo ukázal funkci Zaznamenat makro a Spustit makro a oni ji používají. A to je skvělé.

Vážně. Dal bych nevím co, aby takovýhle makro rekordér obsahovala každá aplikace a považuji za zásadní nedostatek Google Apps, že ač obsahují mají svou obdobu maker – Apps Script, makro rekordér neobsahují.

Úvod pro laiky

Následuje úvod pro laiky, zkušení přeskočte až na tučný nadpis níže:

Poznámka: panel Developer/Vývojář, ve kterém se s Makry pracuje, musíte v novějších verzích Office extra zapnout – v aplikaci stistknete tlačítko Office (takové to kulaté vlevo nahoře), nebo Soubor (v nejnovějších Office), a tam „někde“ to bude – např. v anglickém Excel 2007 je to v Excel options (tlačítko dole) a zvolit Show Developer tab in the Ribbon, v českém Excel 2016 je to v Přizpůsobit pás karet a vpravo zaškrtnout Vývojář.

Makra jsou normální programy, přesněji programy v jazyce Visual Basic (VB), resp. jeho odnoži Visual Basic for Appliacations (zkráceně VBA). Tenhle jazyk si dal za úkol usnadnit programování úplným začátečníkům a kdo umí alespoň trochu anglicky, tak vlastně už VB umí.

Makro rekordér prostě jen zaznamenává vše, co uděláte. Tedy např. v Excelu zapnete Zaznamenávat makro. Pojmenujete si jej třeba Macro1. Zvolíte buňku E5, a tam napíšete číslo 1 a stisknete Enter. Automaticky se přeskočí na E6. Tam napíšete číslo 2 a stisknete Enter. Automaticky se přeskočí na E7, tam napíšete vzorec „=E5+E6“, tedy sečtete ty dvě buňky a opět potvrdíte Entrem (přeskočí se na E8). Stisknete Zastavit záznam.

Poznámka: Makra můžete nahrát buď aktuálního Sešitu, nebo do Osobního sešitu maker, kde je budete mít sdílená pro všechny vaše otevírané aplikace. Do stejného sešitu maker můžete psát i uživatelské funkce (viz pár Excel funkcí, o kterých jsem tu už psal).

Nyní si můžete makro prohlédnout (Vývojář / Visual Basic) a tam bude něco jako

Sub Macro1()
'
' Macro1 Macro
'

'
 Range("E5").Select
 ActiveCell.FormulaR1C1 = "1"
 Range("E6").Select
 ActiveCell.FormulaR1C1 = "2"
 Range("E7").Select
 ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C"
 Range("E8").Select
End Sub

Tedy vyber (select) rozsah (range) buněk (tedy ve skutečnosti 1 buňku) „E5“. Do aktivní buňky (ActiveCell), tedy do té, kterou máte vybranou, vložte vzorec (tedy nyní vlastně číslo) „1“ v notaci R1C1 (FormulaR1C1, viz poznámka zcela dole). Pak skočte na další buňku …  To vše je v programu (neboli subrutině – Sub) s názvem Macro1 (která končí řádkem End Sub). Cokoliv za apostrofem jsou poznámky (ve VBA editoru zeleně).

A nyní už pro všechny

Problém těchto automatických zápisů makro rekordérem je, že „fyzicky“ opravdu posouvají aktuální výběr buňky (podobně u maker např. ve Wordu posouvají stránku či kurzor), což danou aplikaci strašně zdržuje. Nejlepší, co můžete udělat, je prostě dát pryč všechny výběry buňky a vzorce do nich rovnou zapisovat. Aktuálně vybraná buňka se tak nemění a nebude podstatná (pakliže nebudete používat relativní zápisy, viz dále).

Tedy z

 Range("E5").Select
 ActiveCell.FormulaR1C1 = "1"

uděláte prostě

 Range("E5").FormulaR1C1 = "1"

Navíc jestli chcete být ještě za trochu větší profíky, tak tam kde jsou jen hodnoty a nikoliv vzorce můžete místo FormulaR1C1 použít Value (hodnota). A když je to číselná hodnota, tak ani nepoužívejte uvozovky

 Range("E5").Value = 1

Pakliže chcete, můžete místo R1C1 zápisu používat i ten „standardní“, např. „A1“, jen místo slovíčka FormulaR1C1 použijete Formula. Tedy místo

 Range("E7").Select
 ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C"

byste mohli mít pro vás srozumitelnější

 Range("E7").Formula = "=E5+E6"

Co makro rekordér neumí

Jak už bylo řečeno, makro rekordér jen zaznamenává to, co jste provedli na obrazovce, krok po kroku. To je absolutně skvělé, protože když prostě něco chcete zautomatizovat, tak nemusím koukat do manuálu, jak se co udělá ve VBA – prostě si to nahraju makrorekordérem a pak to jen doladím.

Ale makro rekordér tu samotnou automatizaci – opakování či podmínky. K tomu vám stačí ale naučit se příkazy IF/End If a cyklus For / Next. To je mimo téma tohohle článku.

Jak zrychlit rozsáhlá makra

Někdy jsou ale makra opravdu rozsáhlá a dělají hodně změn (mé nejsložitější makro v roce 1997 běhalo asi 2 dny v kuse, i po všech optimalizacích běhalo asi 2 hodiny). Velkým zpomalovačem je přitom právě to, že i při výše popsané optimalizaci musí Excel „fyzicky“ tu hodnotu napsat do buňky a tu buňku vám ukázat. Jednořádkovým řešením, jak aplikaci zrychlit je tak zakázat (=False) aktualizaci (Updating) obrazovky (Screen) aplikace (Application):

    Application.ScreenUpdating = False

Tohle můžete dát na začátek aplikace a máte hotovo. Trochu problémem někdy může být to, že pak nevíte, jestli aplikace něco dělá. Osobně to řeším tak, že po nějakých klíčových krocích (či třeba po každých 10 % probíhajícího cyklu) měním status bar aplikace, takže stav je pak krásně vidět dole v aplikaci.

Application.StatusBar = "Nyní jste v 10 %"

Mám na to i vlastní subrutinu, v případě zájmu taky zveřejním. Nezapomeňte na konci (či po chybě) makra statusbar „vyčistit“, jinak vám tam bude strašit poslední hodnota:

Application.StatusBar = False

Dalšího zrychlení jsem dosahoval minimalizací aplikace po startu (ukazatelem stavu byl pak titulek aplikace).

Poznámka – notace R1C1

Notace R1C1 je původní způsob zápisu vzorců z prvních tabulkových editorů, který využíval absolutní či relativní pořadí (pozici) řádků a sloupců. Tedy např.

Absolutní pozicování:

• R1C1 je buňka v prvním řádku a prvním sloupci (tedy „$A$1“)
• R1C5 je buňka v prvním řádku a pátém sloupci (tedy „$E$1“)
• R1C1:R1C5 je rozsah od první po pátou buňku v prvním řádku (tedy „$A$1:$E$1“)
• R1 je celý první řádek (tedy „$1:$1“), C1 je celý první sloupec (tedy ($A:$A)

Relativní pozicování

• R[-2]C je buňka ve stejném sloupci o dva řádky výše
• R[-2]C[1] je buňka o dva řádký výše a o jeden sloupec vpravo
• R[-2]C1 – kombinace absolutního a relativního – buňka o dva řádky výše v prvním sloupci.

U vytvářených maker máte možnost přepnout na relativní pozicování (tedy veškeré vaše výběry budou počítány relativně vůči aktuálně vybrané buňce) tlačítkem umístěným v liště hned pod Zahájit nahrávání).

Chcete notaci R1C1 využívat vždy?

Excel umožňuje přepínat notaci tabulek mezi jednou a druhou variantou (včetně již zapsaných vzorců), najdete to opět v možnosti Excelu (Formulas / R1C1 Reference Style, resp. Vzorce / Styl odkazu R1C1). Bez tohoto přepnutí Excel neví, že zápisem =C2 nemyslíte buňku ve druhém řádku sloupce C, ale celý sloupec B (čili ve standardním zápisu B:B).