EXCEL VBA. Programování aplikací v Excelu. Aplikační software na PC

-2-

Programování aplikací v Excelu Aplikační software na PC

„

EXCEL – VBA

„

„

RNDr. Jan Preclík [email protected]

„

Objektem v Excelu se rozumí cokoli, co může být programováno nebo řízeno Excel obsahuje přibližně 130 objektů se kterými můžeme pracovat Každý objekt má své vlastnosti (~proměnné) a metody (~procedury, funkce) Pomocí VBA nastavujeme tyto vlastnosti a voláme metody těchto objektů za účelem jejich řízení

-3-

-4-

Vlastnosti objektů

„

Vlastnosti objektů

Přístup k vlastnostem objektu → tečková notace .<jméno vlastnosti>

„

Některé objekty mají předdefinované (default) vlastnosti (ty není třeba explicitně vypisovat)

Application.WorkBooks("Makra.xls"). _ WorkSheets("List1").Range("A1").Value = 10 „

„

Range("A1").Value = 10 nebo Range("A1") = 10

Vlastnosti je možné buď nastavovat nebo naopak zjišťovat jejich hodnoty Existují vlastnosti, které jsou určeny buď jen pro čtení nebo jen pro zápis

-5-

-6-

Metody objektů

„

Volání metod objektu → tečková notace .<jméno metody>

VBA – objekty

„

„

Selection.Copy Range("A1").Copy Range("B1")

Pro přiřazení hodnoty proměnné typu objekt je nutno použít klíčové slovo Set hodnota Nothing → nenastavená objektová proměnná „

„

Metody mohou mít různý počet povinných i nepovinných parametrů (viz. nápověda pro VBA) „

Pro uvolňování paměti; objekt je uvolněn, pokud všechny na něj ukazující proměnné mají hodnotu Nothing

Rovnost odkazů na objekty → Is

Dim Oblast As Range Set Oblast = Range("A1:B3") If Oblast Is Nothing Then … End If

„1

-7-

-8-

Kolekce objektů

„ „ „

Kolekce objektů

Kolekce = soubor objektů stejného typu Název kolekce je shodný s názvem objektu + s Přístup k prvkům kolekce „

„

„

„

Metoda Count „ „

„

Indexem (indexováno od 1) Worksheets(2).Name = "Druhý list"

počet prvků kolekce nelze nastavit hodnotu, jen číst

Metoda AddItem „

přidá položku do kolekce

Jménem Worksheets("List2").Name = "Druhý list" Pomocí metody Item Worksheets.Item(2).Name = "Druhý list"

-9-

- 10 -

Kolekce a cyklus For Each „

Spouštění makra „

Cyklus přes všechny prvky kolekce

Automaticky „

Dim List As Worksheet For Each List In Worksheets List.Name = List.Name & List.Name Next List

„

„

Při otevření sešitu (AUTO_OPEN) Při uzavření sešitu (AUTO_CLOSE)

Klávesová zkratka (Nástroje / Makro… / Makra / Možnosti)

„

Seznam dostupných maker (Nástroje / Makro… / Makra / Spustit)

'řešení ne ve stylu VBA (ala Pascal) Dim I As Integer For I = 1 To Worksheets.Count Worksheets.Item(I).Name = _ Worksheets.Item(I).Name & _ Worksheets.Item(I).Name Next I

„

Vlastní položka menu → přetáhnout do menu

(Zobrazit / Panely nástrojů / Vlastní / Příkazy / Makra) „

Vytvoření nové nabídky/podnabídky v menu (Zobrazit / Panely nástrojů / Vlastní / Příkazy / Nová nabídka)

- 11 -

- 12 -

Spouštění makra „

Vlastní tlačítko → přetáhnout na lištu

(Zobrazit / Panely nástrojů / Vlastní / Příkazy / Makra) „

„

Ladění makra

„

Vytvoření nového panelu nástrojů (Zobrazit / Panely nástrojů / Vlastní / Panely nástrojů / Nový)

„

Kliknutím na libovolný grafický objekt

„

(místní nabídka objektu / Přiřadit makro)

„ „

Editor jazyka Visual Basic, panel nástrojů Ladění (Debug) Spustit (Run Sub/Userform) Stop (Reset) Přepnout zarážku (Toggle breakpoint) Rychlé kukátko (Quick Watch) „ „

Pro proměnnou na které je kurzor Lze přidat do Watch

„2

- 13 -

- 14 -

Ladění makra

„

Ladění makra

Okno kukátek (Watches) „

„

Krokovat s vnořením (Step Into)

„

Krokovat bez vnoření (Step Over)

„

Krok ven (Step Out)

„

„

„

„

Možno modifikovat hodnotu i při běhu

Okamžité okno (Immediate Window) „ „

Při krokování sestupuje do podprogramů

„

Příkaz na řádku se provede po stisku klávesy Enter Pro ladění apod.

Zásobník volání právě aktivních procedur (Call Stack)

Nekrokuje podprogramy Provedení všech příkazů až do konce podprogramu a jeho opuštění

- 16 -

Hierarchie objektů v Excelu

Objekt Application „ „

Reprezentuje celou aplikaci Excelu Vlastnosti objektu „ „ „ „ „

„ „ „ „

ActiveWindow – aktuální okno ActiveWorkbook – aktuální sešit Caption – nadpis Cursor – ukazatel myši CutCopyMode – FALSE = zrušení mihotajícího se rámečku při kopírování Dialogs – kolekce dialogových oken DisplayAlerts – potlačí zobrazení některých hlášení DisplayFormulaBar – skrytí řádku vzorců DisplayStatusBar – skrytí stavového řádku

- 17 -

- 18 -

Objekt Application „

Objekt Application

Vlastnosti objektu „ „ „ „

„

„

„

„

OperatingSystem – název a verze oper. systému StandardFont StandardFontSize StatusBar – vrací nebo nastavuje text na stavové řádce, FALSE = vrací řízení Excelu ThisWorkbook – sešit ve kterém je zapsán právě prováděný příkaz makra Windows – kolekce všech otevřených dceřiných oken v aplikaci WindowState – stav okna (xlMaximized, xlMinimized, xlNormal) Workbooks – kolekce otevřených sešitů

„

Metody objektu „ „

„ „

„

Calculate – přepočítání vzorců ve všech sešitech FindFile – spustí akci otevírání souboru včetně dialogového okna Quit – ukončení aplikace Excel Run – umožňuje spustit makro z jiného excelovského sešitu Wait – přeruší makro do času uvedeného jako parametr

„3

- 19 -

- 20 -

Objekt Workbook „ „

Objekt Workbook

Reprezentuje právě otevřený sešit Vlastnosti objektu „ „ „ „ „

„ „

„

„

ActiveSheet – aktuální list FullName – jméno souboru včetně cesty Name – jméno sešitu Names – kolekce pojmenovaných oblastí buněk Path – cesta, kde je soubor uložen (bez posledního oddělovače) Saved – True, je-li soubor uložen Sheets – kolekce listů v sešitě, obsahuje objekty typu Chart a Worksheet Worksheets – kolekce tabulkových listů, objektů typu Worksheet

Metody objektu „ „ „

„ „ „

Activate – aktivuje daný sešit Close – uzavírá sešit Protect – uzamčení sešitu, lze zadat heslo jako parametr Save – uloží sešit na disk SaveAs UnProtect – odemkne daný sešit

- 21 -

- 22 -

Objekt Worksheet „ „

Objekt Worksheet

Reprezentuje jeden list sešitu Vlastnosti objektu „

Cells – kolekce všech buněk sešitu „ „

„ „ „ „ „ „

„

„

Metody objektu „ „

ActiveSheet.Cells(2,3) '= buňka C2 (řád., sloup.) ActiveSheet.Cells(2) '= druhý řádek

„

Columns – kolekce sloupců Name – vrací nebo nastavuje jméno listu Names – kolekce pojmenovaných oblastí buněk Range – parametr vymezuje požadovanou oblast Rows – kolekce řádků UsedRange – vrací objekt typu Range reprezentující využitou oblast listu Visible – skrytí listu

„ „

„

„

Activate – aktivuje daný list Calculate – přepočítává všechny buňky v listě Copy – kopíruje list před nebo za uvedený list v sešitě Delete – odstraní zadaný list ze sešitu Paste – vloží obsah schránky na parametrem určené (nebo aktuální) místo v sešitě, nelze použít na vkládání objektů z jiných aplikací PasteSpecial – vloží obsah schránky, možno určit další podmínky vložení PivotTables – kolekce kontingenčních tabulek

- 23 -

- 24 -

Objekt Worksheet

„

Metody objektu „ „

Protect – uzamkne list Unprotect – odemkne daný list

Objekt Window

„ „

Reprezentuje okno v Excelu Vlastnosti objektu „ „ „ „

„

„

„

Caption – nastavuje nebo vrací jméno okna DisplayFormulas – zobrazuje vzorce v buňkách DisplayGridlines – nastavuje zobrazení mřížky DisplayHeadings – nastavuje, zda bude zobrazeno záhlaví řádků DisplayWorkbookTabs – v sešitě se zobrazí záložky listů ScrollColumn – nastaví nebo zjistí sloupec, který je zobrazen u levého okraje okna dokumentu ScrollRow – stejné jako u sloupce

„4

- 25 -

- 26 -

Objekt Window

„

Objekt Window

Vlastnosti objektu „ „

„ „ „

„

Height – vrací nebo nastavuje výšku okna v bodech Left – nastavuje nebo vrací polohu okna od levého okraje využitelné oblasti Width, Top WindowState – mění stav okna Zoom – nastavuje nebo vrací zvětšení okna v procentech

Metody objektu „ „ „ „

Activate – aktivuje okno Close – uzavře okno LargeScroll – posunuje okno po stránkách SmallScroll – posunuje obsah okna po jednotlivých řádcích nebo sloupcích

- 27 -

- 28 -

Objekt Range

„ „

Reprezentuje buňku nebo výběr buněk tabulky Dva zápisy syntaxe „

„

„

Objekt Range „

jeden parametr → zápis celé oblasti: Range("A1:B3")

Sjednocení více souvislých oblastí → UNION (vrací objekt Range) „

„

dva parametry → rohové buňky vymezují oblast: Range("A1","B3")

Union(Range("A1:A2"), Range("B2:B3"))

Lze použít i lib. pojmenovanou oblast buněk (Vložit / Název / Definovat) „

Range("Oblast1")

Operátor průniku → mezera „

průnik oblastí A1:C3 a B2:D5, tj. oblast B2:C3 Range("A1:C3 B2:D5")

- 29 -

- 30 -

Objekt Range „

Vlastnosti objektu „

„ „

„

„

„ „

Address – obsahuje adresu dané oblasti , zapsanou ve tvaru určeném parametrem Borders – kolekce orámování buněk Characters – kolekce znaků uvnitř buňky, objekt Range musí odpovídat jediné buňce Cells – objekt typu Range reprezentující všechny buňky oblasti Column – vrací číslo prvního sloupce v první oblasti „ Column(Range("C2:E4,A2:B5")) ' = 3 Columns – kolekce sloupců ColumnWith – šířka sloupce

Objekt Range „

Vlastnosti objektu „

„ „

„

„

CurrentRegion – vrací objekt Range aktuální oblasti, to je oblast ohraničená prázdným řádkem a sloupcem nebo okraji tabulky Count – počet buněk v oblasti End – poslední buňka aktuální oblasti ve směru určeném parametrem, podobně jako klávesová zkratka CTRL+šipka EntireColumn, EntireRow – reprezentuje celý řádek, či sloupec ve vybrané oblasti Font – objekt určující vlastnosti písma

„5

- 31 -

- 32 -

Objekt Range „

Objekt Range

Vlastnosti objektu „

„

„

„

„ „

„

Formula – vrací nebo nastavuje vzorec - anglické názvy funkcí FormulaLocal – vrací nebo nastavuje vzorec názvy funkcí dle lokalizace nainstalovaného Excelu a oddělovače nastavené v ovládacím panelu Hidden – skrýt/zobrazit buňky, lze použít pouze na celé sloupce a řádky HorizontalAlignment – zarovnání textu ve vodorovném směru Interior – objekt určující formát výplně buňky Name – vrací nebo nastavuje jméno buněk

Vlastnosti objektu „

„

„

NumberFormat – formát čísla, nutno zadat anglický zápis, nezávislé na lokalizaci instalace Excelu NumberFormatLocal – formát čísla, závislé na lokalizaci Offset – vrací objekt typu Range posunutý o požadovaný počet řádků a sloupců „ ActiveCell.Offset(1,0).Value = 7 zapíše hodnotu 7 do buňky ležící hned pod buňkou aktivní „ pokud se tímto způsobem dostanu na neexistující buňku (mimo oblast listu), dojde k běhové chybě

- 33 -

- 34 -

Objekt Range „

Objekt Range

Vlastnosti objektu „

„ „ „

„

Resize – objekt typu Range zmenšený / zvětšený dle parametrů oproti původnímu objektu Row – vrací číslo prvního řádku v první oblasti Rows – kolekce řádků Value – vrací nebo nastavuje hodnotu v buňce nebo v oblasti buněk VerticalAlignment – zarovnání textu ve svislém směru

„

Metody objektu „ „ „ „ „

„ „

„

Activate – aktivuje jednu buňku ve vybrané oblasti AutoFit – přizpůsobí šířku sloupce AutoFilter – nastaví automatický filtr Calculate – přepočítá buňky ve vybrané oblasti Clear, ClearContents, ClearFormat... - vymaže vše, obsah, formáty... Copy – kopíruje oblast buněk do schránky Cut – kopíruje oblast buněk do schránky nebo na určené místo, na původním místě vymaže obsah Delete – zruší buňky a provede posun buněk v okolí

- 35 -

- 36 -

Objekt Range

„

Metody objektu „

„ „

„ „

Find, FindNext, FindPrevious – prohledávání obsahu buněk Insert – vloží buňky a provede posun buněk v okolí PasteSpecial – vloží obsah schránky na místo dané objektem Select – označí oblast buněk Sort – setřídí oblast podle zadaných podmínek

Objekt Font

„ „

Reprezentuje font (buněk, oblasti, objektu…) Vlastnosti objektu „

„

„

„ „

Bold, Italic, Underline... – tučné, kurzíva, podtržené... Color – barva písma, možno použít funkci RGB nebo konstanty vbRed, vbGreen... ColorIndex – vrací nebo nastavuje barvu písma z palety barev Name – jméno fontu Size – vrací nebo nastavuje velikost písma v bodech

„6

- 37 -

- 38 -

Objekt Border

„ „

Objekt Interior

Reprezentuje okraj (ohraničení) buněk Vlastnosti objektu „ „ „ „

„ „

Color ColorIndex LineStyle – styl čáry (plná, čárkovaná...) Weight – tloušťka čáry

Reprezentuje vnitřek buněk Vlastnosti objektu „ „ „

„ „ „

Color – barva výplně ColorIndex – barva vybíraná z palety InvertIfNegative – booleovská, zobrazení v doplňkových barvách Pattern – vzorek výplně buňky PatternColor – barva vzorku PatternColorIndex – číslo barvy vzorku z palety barev

- 39 -

- 40 -

Dialogy

Dialogy funkce InputBox (prompt,title,default, xpoz,ypoz,helpfile,context): string

MsgBox (prompt,buttons,title,helpfile, context): stisknuté_tlačítko

„

If MsgBox("Chcete ukončit práci makra?", _ vbYesNo + vbQuestion, "Konec") = vbYes Then Exit Sub End If

zobrazí dialogové okno se vstupní řádkou a se dvěma tlačítky OK a Storno, pokud uživatel ukončí tlačítkem OK, vrací text zapsaný ve vstupní řádce, jinak vrací prázdný řetězec

- 41 -

- 42 -

Dialogy metoda Aplications.InputBox (prompt, title,default,left,top,helpFile, helpContextId,type):Variant „

Jako funkce InputBox ale má více možností

'Načtení oblasti od uživatele (včetně 'možnosti výběru myší) Dim Oblast As Range Set Oblast = Application.InputBox(Prompt:= _ "Zadej adresu zpracovávané oblasti.", _ Type:=8)

Uživatelské formuláře

„ „

~ tvorba vlastních dialogových oken Postup při tvorbě „

„

„

„

vložíme nový formulář – v editoru jazyka VBA volba Insert / UserForm v okně Properties lze měnit vlastnosti formuláře (jméno…) na formulář přidáme ovládací prvky a nastavíme v okně Properties jejich vlastnosti ošetříme události generované ovládacími prvky (tj. napíšeme příslušné procedury)

„7

- 43 -

- 44 -

Uživatelské formuláře

„

Uživatelské funkce použitelné na listu

Postup při tvorbě (pokračování) „

„

„

napíšeme proceduru, které formulář zobrazí UserForm1.Show

„

na konec formulář „ uvolníme: UnLoad UserForm1 → potom se veškeré nastavení na něm zničí „ nebo pouze schováme: UserForm1.Hide → s nastaveními na formuláři lze i po schování pracovat

Funkce, která má být použitelná v buňce na listu, nesmí být deklarovaná jako Private! Funkce nesmí manipulovat s oblastmi, hodnotami buněk na listě!

- 45 -

- 46 -

Uživatelské funkce použitelné na listu

„

Řízení volání funkce = kdy se bude funkce na listu přepočítávat „ „

volatile → přepočítává se při každé změně na listu nonvolatile → přepočítává se pouze pokud se změní buňky na které se odkazuje

Function XY (Obl As Range) As Integer Application.Volatile ( False ) ... End Function

Uživatelské funkce použitelné na listu

„

Určení kategorie funkce → do jaké kategorie bude naše funkce v dialogu Vložit funkci zařazena (standardně kategorie Vlastní) „

při otevření sešitu je nutno provést kousek VB kódu (vloží se k objektu Workbook, resp. Tento_sešit), který funkci zařadí do příslušné kategorie (0 = vše, 1 = finanční, …, 14 = vlastní)

Private Sub Workbook_Open() Application.MacroOptions Macro:="Obrat", _ Category:=7 End Sub

„8