MS Excel a VBA Motivace pro VBA Zvldnut
MS Excel a VBA
Motivace pro VBA • Zvládnutí základních možností MS Excel – podmíněný příkaz, maticové operace, … • Zvládnutí doplňků – nové funkce, komplexní nástroj Řešitel (Solver) • Zvládnutí jednoduchých maker – záznam, spuštění – úprava – VBA editor • Čas pro VBA – – zvládnutí jazyka, vytváření UDF funkcí objektový model MS Excel (manipulace s objekty) vytváření vlastních tříd
Základní možnosti MS Excel • Názvy buněk, rozsahů • Definice podmínek (A(), NEBO()) • Podmíněný příkaz – Limit vnoření příkazů • Maticové operace – Determinant, inverzní matice, součin matic a vektorů • Balíčky – inženýrské výpočty – Komplexní čísla, Besselovy fce, …
Řešitel (Solver) • Účel – Úlohy lineárního a nelineárního programování • nastavení úlohy a parametry – Kriteriální fce, jaký extrém hledáme, co měníme, máme požadavky na řešení • Možnosti – Maximální čas, iterace, tolerance, konvergence, lineární model, extrapolace, derivace, metoda vyhledávání směru
Makra • • Záznam makra Úprava makra Krokování makra (F 8) Nástroj pro poznání objektů
Otevření VBA editoru Alt + F 11
Vložení Modulu Modul je základní kontejner pro VBA kód.
VBA • Syntaxe jazyka, operátory – Aritmetické, relační, logické, spojení řetězců • Klíčová slova • Příkazy • Proměnné, typy – Byte, boolean, integer, long, single, double, currency, decimal, date, string, variant, object • VBA funkce
Deklarace proměnných Proměnná je dočasný pojmenovaný prostor pro uložení dat (~ buňka) • Dva způsoby deklarace – Explicitní – Dim, název a typ proměnné – Implicitní – použijeme název bez předchozí deklarace Dim L 0 As Single Dim P 0 As Double • Přiřazení hodnoty P 0 = 150 L 0 = 10
Podmíněný příkaz If podmínka. Then [příkazy] [Else příkazy] Nebo If podmínka. Then [příkazy] [Else. If podmínka. Then [příkazy]
Příkazy cyklu For čítač = začátek To konec [Step krok] [příkazy] [Exit For] [příkazy] Next [čítač] For Each prvek In skupina [příkazy] [Exit For] [příkazy] Next [prvek] Do [{While | Until} podmínka] [příkazy] [Exit Do] [příkazy] Loop Do [příkazy] [Exit Do] [příkazy] Loop [{While | Until} podmínka]
Vytvoření procedury / funkce Procedura / funkce představuje pojmenovanou jednotku kódu, kterou lze opakovaně použít (volat) • Deklarace – Sub název (parametry) … End Sub – Function název (parametry) as Single … End Function • Typ – privátní (Private) – dostupná v modulu, ve kterém je definována – veřejná (Public) – dostupná ve všech modulech projektu • Parametry a jejich předávání – Referencí (výchozí) – Hodnotou Sub Square(By. Ref x As Single) Sub Square(By. Val x As Single)
Příklad funkce Funkce generuje náhodné celé číslo ze zadaného intervalu Public Function Random. Int. Number(Lowest As Long, Highest As Long) Randomize Random. Int. Number = Lowest + Int(Rnd * (Highest - Lowest)) End Function Funkce generuje náhodné číslo ze zadaného intervalu Public Function Random. Number(Lowest As Single, Highest As Single) Randomize Random. Number = Lowest + Rnd * (Highest – Lowest) End Function
Objektový mode MS Excelu
Jak se odkazujeme na sešit? • Pomocí kolekce Work. Books – popisuje všechny otevřené sešity – Work. Books("Prosty. Nosnik. xls") – Work. Books(1) • Aktivní sešit – Objekt Active. Work. Book
Jak se dkazujeme na list? • V úplné hierarchii objektů – Work. Books("Nosnik. xls"). Work. Sheets("List 1") – Work. Books(1). Work. Sheets(1) • Vynecháme-li kolekci Work. Books(), odkazujeme se na aktivní list – Work. Sheets("List 1") • Aktivní list také pomocí – objektu Active. Sheet
Jak se odkazujeme na buňku? • Pomocí objektu Range. – Worksheets("List 1"). Range("A 1") – Worksheets("List 1"). Range("A 1: B 5") – Worksheets("List 1"). Range("A 1, A 3, A 5") • vlastnost Cells – Worksheets("List 1"). Range("A 1: B 5"). Cells(1, 1) • Aktivní buňka – Objekt Active. Cell
Range – metody, vlastnosti Objekt Metody Vlastnosti Range Activate, Auto. Filter, Auto. Fit, Auto. Format, Border. Around, Calculate, Clear. Contents, Clear. Formats, Copy, Cut, Delete, Insert, Justify, Paste. Special, Print. Out, Print. Preview, Select, Show, Sort Address, Application, Borders, Cells, Columns, Count, Current. Region, Font, Formula. Array, Formula. R 1 C 1, Height, Hidden, Interior, Locked, Name, Number. Format, Offset, Orientation Page. Break, Pivot. Table, Rows, Value 2, Width, Worksheet
Příklady
Hlavička tabulky Základní obsah buněk Worksheets("List 1"). Range("A 11"). Value = "i" Worksheets("List 1"). Range("B 11"). Value = "Li [m]". . . Worksheets("List 1"). Range("K 11"). Value = "s [MPa]" Tučné písmo na úrovni buňky Worksheets("List 1"). Range("A 11: K 11"). Font. Bold = True Orámování buněk With Worksheets("List 1"). Range("A 11: K 11"). Borders(xl. Edge. Right). Line. Style = xl. Continuous. Weight = xl. Medium. Color. Index = xl. Automatic End With
Hlavička tabulky Dolní index With Worksheets("List 1"). Range("B 11"). Characters(2, 1). Font. Subscript = True Změna fontu na úrovni znaku With Worksheets("List 1"). Range("K 11"). Characters(1, 1). Font. Name = "Symbol"
Vzorce – vlastnost Formula Index varianty Worksheets("List 1"). Range("A 12"). Formula = "=ROW()" Náhodná délka Worksheets("List 1"). Range("B 12"). Formula = "=L 0 * (0. 9 + 0. 2 * RAND())" Náhodné působiště síly Worksheets("List 1"). Range("C 12"). Formula = "=B 12/3 * (0. 9 + 0. 2 * RAND()) Výpočet reakce Worksheets("List 1"). Range("E 12"). Formula = "=C 12 * (B 12 - D 12) / B 12"
Vzorce – vlastnost Formula Index varianty Worksheets("List 1"). Range("A 12: A 111"). Formula = "=ROW()" Náhodná délka Worksheets("List 1"). Range("B 12: B 111"). Formula = "=L 0 * (0. 9 + 0. 2 * RAND())" Náhodné působiště síly Worksheets("List 1"). Range("C 12: C 111"). Formula = "=B 12/3 * (0. 9 + 0. 2 * RAND()) Výpočet reakce Worksheets("List 1"). Range("E 12: E 111"). Formula = "=C 12 * (B 12 - D 12) / B 12"
UDF příklady Reakce v levé opoře Function r. A() as Single L = Work. Sheets(‘Parametr’). Range(‘L’). Value a = Work. Sheets (‘Parametr’). Range(‘a’). Value P = Work. Sheets (‘Parametr’). Range(‘P’). Value r. A = P * (L – a) / L End Function r. A(L as Single, a as Single, P as Single) as Single r. A = P * (L – a) / L End Function
UDF příklady Function r. A(L as Single, a as Single, P as Single) as Single if (a < 0) or (a > L) then Msg. Box(“Nesprávně zadané působiště síly. ”) Exit Function end if if (L < 0) then Msg. Box(“Nesprávně zadaná délka. ”) Exit Function end if r. A = P * (L – a) / L End Function
- Slides: 26