Visual Basic for Applications 23 11 2020 VBA

Visual Basic for Applications 23. 11. 2020 VBA 1

Makra § posloupnost příkazů, kterou lze automaticky a opakovaně vykonávat § v aplikacích MS Office (Word, Excel, Power. Point, Access) § použití: automatické provádění často se opakujících akcí § vytváření: § nahrání § zápis v jazyce Visual Basic § kombinace obou způsobů 23. 11. 2020 VBA 2

Nahrávání makra n n po spuštění nahrávání až do jeho ukončení je každá akce (myší i z klávesnice) zaznamenána celou posloupnost akcí je pak možné přehrát tipy pro nahrávání: u použití panelu nástrojů VB u sledování nahraných akcí v okně editoru VB u před začátkem nahrávání makra označení objektů relativní x absolutní odkaz u absolutní odkaz => zaznamenávají se absolutní adresy buněk (např. A 5, B 3) u relativní odkaz => zaznamenávají se pohyby vzhledem k počáteční pozici (posun – Offset (počet řádků, počet sloupců)) u 23. 11. 2020 přepnout lze kdykoliv během nahrávání VBA 3

Spouštění makra n n 23. 11. 2020 automaticky: u po otevření sešitu (makro Auto_Open), po uzavření sešitu (makro Auto_Close) zadanou klávesovou zkratkou vytvořenou položkou nebo tlačítkem v menu (Zobrazit / Panely nástrojů / Vlastní -> Příkazy -> Makra -> Vlastní položka nabídky resp. Vlastní tlačítko + přetažení do menu) kliknutím na grafický objekt: u vytvoření objektu u kliknutí pravým tlačítkem myši => Přiřadit makro VBA 4

Zápis makra n n v editoru VBA podprogramy u u kód podprogramu obvykle v modulech proměnné: « « « u procedury: « « u makra = procedury bez parametrů lze nahrát a upravovat mezi klíčová slova Sub a End lze spustit ze seznamu maker (není-li deklarována jako Private) funkce: « « 23. 11. 2020 platnost v podprogramu – dim nebo static + deklarace v podprogramu platnost v modulu – dim + globální deklarace platnost v celém projektu (tj. ve všech modulech) – public + globální deklarace nelze nahrát mezi klíčová slova Function a End Function množství předdefinovaných funkcí lze vložit do buňky příkazem Vložit / Funkce / Vlastní (není-li deklarována jako Private) VBA 5

Předdefinované funkce n n n n 23. 11. 2020 matematické (Abs, Rnd, Sqr, Sin, …) pro práci s řetězci (&, Len, Mid, Ucase, Trim, …) pro převod na daný typ (Cdate, Cbool, …) pro datum a čas (Date, Time, Now, …) test na typ proměnné (Is. Date, Is. Empty, …) nutno použít anglické názvy funkcí (např. Count. A pro vrácení počtu neprázdných buněk v oblasti místo Počet 2) např. : Application. Work. Sheet. Function. Min(Range (“A 1: C 3“)) ‘vrátí minimální hodnotu z oblasti A 1: C 3 VBA 6

Objekty ve VBA n n n vlastnosti (~ proměnné), metody (~ procedury a funkce) přístup pomocí tečkové notace některé objekty jsou kontejnery: např. : Application. Work. Book. Work. Sheet. Cell odkaz na výběr – Selection (je-li vybráno před spuštěním makra) struktura With: u pro úsporu místa při zápisu např. : With Selection. Font. Name = “Arial Black“. Size = 10. Color. Index = 3 End With ‘ nastaví ve vybrané oblasti žluté písmo Arial Black o velikosti 10 klíčové slovo Set uvozuje přiřazovací příkaz proměnných typu objekt Set oblast = Range (“A 1: B 3“) 23. 11. 2020 VBA 7

Kolekce n 23. 11. 2020 kolekce = soubor objektů stejného typu u název kolekce shodný s objektem + s « přístup k prvkům kolekce: • indexem Work. Sheets(2). Name = “Druhý list“ • jménem Work. Sheets(“List 2“). Name = “Druhý list“ ‘ přejmenování druhého listu v sešitě u metoda Count – počet prvků kolekce (nelze nastavit, jen číst) u metoda Add – přidá položku do kolekce u metoda Delete – odebere položku z kolekce u příkaz For Each Dim list As Work. Sheet For Each list In Work. Sheets … Next list VBA 8

n Nejdůležitější objekty ve VBA I. Application – reprezentuje celou aplikaci Excelu u vlastnosti: Active. Window (aktuální okno), Active. Work. Book (aktuální sešit) « This. Work. Book (sešit, ve kterém je zapsán právě prováděný příkaz makra) « Windows (kolekce všech otevřených oken v aplikaci) « Work. Books (kolekce všech otevřených sešitů) « u metody: Calculate (vyvolá přepočítání vzorců ve všech sešitech) « Quit (ukončí aplikaci Excelu) « Run (spuštění makra z jiného sešitu) « Wait (pozastaví spuštěný podprogram na určitou dobu) « 23. 11. 2020 VBA 9

Nejdůležitější objekty ve VBA II. n Work. Book – otevřený sešit u vlastnosti: (aktuální list) « Name (jméno sešitu), Full. Name (jméno včetně cesty), Path (cesta) « Saved (je-li soubor uložen – True) « Work. Sheets (kolekce tabulkových listů – Work. Sheet « Active. Sheet u metody: (uzavře sešit) « Save, Save. As (uloží sešit na disk) « Close 23. 11. 2020 VBA 10

Nejdůležitější objekty ve VBA III. Work. Sheet – reprezentuje jeden list sešitu n u vlastnosti: « « « 23. 11. 2020 Cells (kolekce všech buněk v sešitu) • Active. Sheet. Cells(2, 3) ‘ = buňka C 2 • Active. Sheet. Cells(2) ‘ = druhý řádek Columns (kolekce sloupců) Name (vrací nebo nastavuje jméno listu) Range (vymezuje oblast buněk na listu) • Range(“A 1: B 3“) ‘ jeden parametr – zápis celé oblasti (i násobné) • Range(“A 1“, “B 3“) ‘ dva parametry – rohové buňky vymezují obdélníkovou oblast • Range(“A 1: C 3 B 2: D 5“) ‘ průnik oblastí A 1: C 3 a B 2: D 5 (operátor mezera), tj. oblast B 2: C 3 • Union(Range(“A 1: A 2“), Range(“B 2: B 3“)) ‘ sjednocení více souvislých oblastí • Range(“Oblast 1“) ‘ odkaz na pojmenovanou oblast buněk (příkaz Vložit / Název / Definovat) Rows (kolekce řádků) Visible (skrytí / zobrazení listu) VBA 11

Nejdůležitější objekty ve VBA IV. n Work. Sheet – reprezentuje jeden list sešitu u metody: « « n Window – reprezentuje okno v Excelu u vlastnosti: « « u Height, Width, Left, Top Window. State (stav okna – xl. Maximized, xl. Minimized, xl. Normal) metody: « « 23. 11. 2020 Copy (kopíruje list před nebo za uvedený list v sešitě) Delete (odstraní zadaný list ze sešitu) Paste (vloží obsah stránky na parametrem určené (nebo aktuální) místo v sešitě) Select (parametr True vybere list, parametr False změní výběr listu) Activate (aktivuje okno) Close (uzavře okno) VBA 12

n Nejdůležitější objekty ve VBA V. Range – reprezentuje buňku nebo výběr buněk v tabulce u vlastnosti: « « « Adress (adresa dané oblasti) Borders (kolekce orámování buněk) Characters (kolekce znaků uvnitř buňky – objekt Range musí odpovídat jediné buňce) Cells (všechny buňky dané oblasti) Columns (kolekce sloupců) Count (počet buněk v oblasti) End (poslední buňka oblasti ve směru určeném parametrem) Font (objekt, určující vlastnosti písma) Hidden (skrytí / zobrazení buněk) Interior (objekt, určující formát výplně) Offset (vrací objekt typu Range posunutý o požadovaný počet řádků a sloupců) Active. Cell. Offset(1, 2). Value = 7 ‘ zapíše hodnotu 7 do buňky ležící o řádek pod a dva sloupce vpravo od aktivní buňky « Rows (kolekce řádků) « Value (vrací nebo nastavuje hodnotu v buňkách oblasti) 23. 11. 2020 VBA 13

Nejdůležitější objekty ve VBA VI. n Range – reprezentuje buňku nebo výběr buněk v tabulce u metody: « « « u Auto. Fit (přizpůsobí šířku sloupce) Calculate (přepočítá buňky v oblasti) Clear, Clear. Contents, Clear. Format (vymaže vše resp. obsah, formáty) Copy (zkopíruje oblast do schránky) Cut (vyjme oblast do schránky) Delete (zruší buňky a provede posun) Find, Find. Next, Find. Previous (prohledávání obsahu buněk) Insert (vloží buňky a okolní posune) Paste (vloží obsah schránky) Select (označí oblast buněk) Sort (setřídí oblast dle zadaných podmínek) příklady: Active. Sheet. Range(“A 4“) ‘ buňka A 4 z aktivního listu « Work. Sheets(“Prosinec“). Range(“B: B“) nebo Range(“Prosinec!B: B“) ‘ sloupec B z listu Prosinec « Pozor! Range(i & “: “ & j). Select ‘ odkaz na i-tý řádek a j-tý sloupec Nelze: Range( “i: j“). Select ani Range(i: j). Select « 23. 11. 2020 VBA 14

n Nejdůležitější objekty ve VBA VII. Font – objekt slouží k nastavení vlastností písma u vlastnosti: « Bold, Italic, Underline, … Color (barva písma, možno použít funkci RGB nebo konstanty vb. Red, …), Color. Index (barva písma z palety barev) Name (jméno fontu) « Size (velikost písma) « « n Interior – objekt popisuje vnitřek buňky u vlastnosti: « « n Color, Colorindex (barva výplně) Pattern (vzorek výplně), Pattern. Color. Index (barva vzorku výplně) Border – objekt popisuje ohraničení buněk u vlastnosti: « Color, Color. Index (barva ohraničení) Line. Style (styl čáry – plná, čárkovaná, …) « Weight (tloušťka čáry) « 23. 11. 2020 VBA 15

Komunikace s uživatelem I. n n Msg. Box(prompt, buttons, title, helpfile, context) : stisknuté_tlacítko Input. Box(prompt, title, defualt, xpoz, ypoz, helpfile, context ): string u u 23. 11. 2020 zobrazí dialogové okno se vstupní řádkou a dvěma tlačítky OK a Storno ukončí-li uživatel tlačítkem OK, vrací text zapsaný ve vstupní řádce, jinak vrací prázdný řetězec VBA 16

n Komunikace s uživatelem II. Applications. Input. Box (prompt, title, default, left, top, help. File, help. Context. Id, type): Variant u navíc type – typ zadávané hodnoty: 0 – vzorec ve tvaru textu, např. “=A 1+A 2“ « 1 – číslo « 2 – textový řetězec « 4 – logická hodnota (True nebo False) « 8 – adresa oblasti buněk (Range); přiřazení je nutné použít funkci Set « u 23. 11. 2020 např. : Dim oblast As Range Set oblast = Applications. Input. Box(Title: = “Výstup“, Prompt: =“Zadej adresu oblasti“, Type: =8) VBA 17