MS Excel 2010
Základy maker
Operační program Vzdělávání pro konkurenceschopnost
Projekt Zvyšování IT gramotnosti zaměstnanců vybraných fakult MU
Registrační číslo: CZ.1.07/2.2.00/15.0224, Oblast podpory: 7.2.2
Aktuální nabídku kurzů najdete na adrese http://cit.ukb.muni.cz/kurzy/
Co to je makro – program, který neběží na úrovni počítače, operačního systému, ale uvnitř aplikace.
Používá jazyk, kterému daný program rozumí. Slouží ke zjednodušení opakovaných operací.
V Excelu se jako makrojazyk používá Visual Basic for Applications (VBA).
Tvorba maker
a) Záznamem operací v Excelu – vždy dělá přesně totéž, nevyžaduje žádné znalosti
b) Ručním zadáním kódu – složitější, flexibilní. Vše co lze udělat záznamem, lze udělat i ručně,
ale ne naopak.
c) Kombinací
Záznam makra
/úkol: vytvořte makro, které vloží do aktivní buňky Vaše jméno, o buňku níže aktuální datum a čas, a
obě buňky naformátuje tučně/
-
Přepněte na kartu Vývojář
Klepněte ve skupině Kód na Záznam makra
Zadejte název makra, případně klávesovou zkratku a popis
Zvolte, kam se má makro uložit
Provádějte operace, které chcete zaznamenat
Klepněte ve skupině Kód na Zastavit záznam
Ukládání maker
- Standardně se ukládá do aktivního sešitu, pak je makro dostupné jen v případě, že je sešit
otevřen
- Je možné zvolit Osobní sešit maker, pak je makro dostupné vždy
Ukládání sešitu s makry
- Je potřeba ukládat jako .xlsm (xlsx makra obsahovat nemůže)
Zobrazení dostupných maker
- Klepněte na Kód – Makra
Spuštění makra
- Klávesovou zkratkou (pokud jsme ji definovali)
- Z okna s dostupnými makry poklepáním na jméno makra nebo výběrem makra a stiskem
spustit
/úkol: spusťte vytvořené makro. Proč nefunguje správně?/
Relativní a absolutní režim odkazů
Při nahrávání makra je možné použít absolutní nebo relativní adresování. Pokud použijeme absolutní
adresování, tak každý krok, ve kterém vybereme nějako buňku nebo rozsah, zaznamená přesné
adresy buněk a při spuštění makra je zopakuje vždy pro tytéž buňky. Při relativních adresách neuvede
absolutní adresy, ale adresy relativní vůči aktivní buňce. Při spuštění makra pak bude záležet na
poloze výchozí buňky.
Editace makra
Makra se mohou upravovat v editoru jazyka VBA. Ten spustíme
-
Kód – Visual Basic
Klávesovou zkratkou Alt – F11
Makra jsou uložena v modulech (Modules). Poklepejte na modul a zobrazí se kód maker uložených
v tomto modulu.
/úkol: projděte si kód zaznamenaného makra a zkuste identifikovat, co který příkaz dělá/
Sledování nahrávání makra
Zobrazte vedle sebe okno Excelu a editor VBA a spusťte záznam makra. V editoru byste měli vidět,
jaké kroky se právě nahrávají.
Objekty v MS Excelu
Každý prvek v Excelu je objekt, počínaje Excelem samotným. Každý objekt má vlastnosti(parametry,
atributy), metody (schopnosti, funkce) a události (events). Např. ActiveCell je objekt třídy Cell (buňka)
a má jeden z parametrů FormulaR1C1, který představuje obsah buňky
Object Selection má parametr Font, který je sám objektem s parametry Underline, Italic, Bold atd.
Pokud chceme objektu nastavit nějaký parametr, použijeme konstrukci
Objekt.Parametr = hodnota
Jestliže objektu chceme nastavit zároveň parametrů, můžeme použít
With Object
.parametr1 = hodnota
.paramatr2 = hodnota
…
End With
S jakými objekty potřebuji pracovat?
Obvykle není třeba příslušný objekt vyhledávat, stačí zaznamenat makro, které s daným objektem
pracuje, a potom daný objekt vyhledat v kódu.
Automatické doplňování parametrů, metod a argumentů
Pokud v editoru VBA zadáme jméno nějakého objektu a tečku, zobrazí se nám všechny dostupné
parametry, funkce a jejich argumenty. Šipkami můžeme označit požadovanou volbu a tabelátorem
potvrdit. Lze použít i Enter, ale ten způsobí přechod na další řádek. Pokud se doplnění nezobrazí, je
možné je vyvolat pomocí Ctrl – Mezerník.
Kolekce (Collections)
Jsou sady objektů stejného typu. Např. všechny sešity otevřené v excelu představují kolekci, stejně
jako všechny listy v sešitu.
Workbooks – všechny sešity v excelu, obsahuje objekty typu Workbook
Worksheets – všechny listy (s tabulkami) v aktuálním sešitu, obsahuje objekty typu Worksheet
Charts – všechny grafy (vložené jako samostatné listy) v aktuálním sešitu, obsahuje objekty typu
Chart
Procedury a funkce
Procedura (Sub) – vykonává nějakou činnost, provádí změny v Excelu
Funkce (Function) – provádí výpočet a vrací jednu hodnotu
V rámci procedury je možné používat jiné procedury a funkce, v rámci funkce je možné používat jiné
funkce.
Procedura: call Jméno procedury
Funkce: parametr = JménoFunkce(parametry)
Funkce lze použít i přímo ve vzorci v buňce.
Proměnné, konstanty a datové typy
Proměnné jsou pojmenovaná úložiště hodnot. Mohou obsahovat jednoduchý obsah (např. číslo)
nebo velmi komplexní (rozsáhlý objekt). Např.
i = 1 proměnná pojmenovaná X, typu celé číslo, přiřazujeme hodnotu 1
r=4.528 proměnná pojmenovaná r, typu reálné číslo
Jmeno = „Jan Novák“ proměnná Jmeno typu řetězec
Datum = #10/5/2011# Proměnná Datum typu datum
Jména proměnných je možné doplňovat pomocí Ctrl + mezerník
Konstanty jsou předdefinovaná pojmenování obsahující stále stejný obsah. Mohou být definovány
globálně (na úrovni Excelu), na úrovni modulu nebo funkce či procedury.
Základní datové typy
Typ
Boolean
Integer
Long
Single
Currency
Date
String
Object
Variant
vysvětlení
Logický, nabývá hodnot True a False
Celé číslo, –32,768 to 32,767
–2,147,483,648 to 2,147,483,647
–3.402823E38 to 1.401298E45
–922,337,203,685,477.5808 to
922,337,203,685,477.5807
1/1/100 to 12/31/9999
Řetězec znaků
Objekt (libovolného typu)
Libovolný typ
Deklarace proměnných
Proměnné je možné (nikoliv nezbytné) deklarovat. Ačkoliv je deklarace nepovinná, vřele se
doporučuje, a zároveň se doporučuje uvést na první řádek modulu „Option Explicit“ – tím se
deklarace stanou povinnými. Deklarace zaručuje
1. Že do dané proměnné není možné omylem přiřadit jiný typ hodnoty
2. Že nemůže dojít k chybě překlepem (Používám proměnnou ObjemVzorku a na jednom místě
uvedu omylem ObejmVzorku)
Deklarace má tvar
Dim JmenoPromenne As Typ
a je uvedena v proceduře nebo funkci před svým prvním použitím. Taková proměnná je viditelná
pouze v rámci dané procedury. Je možné definovat i proměnné viditelné v rámci modulu nebo ve
všech modulech, těmi se nebudeme zabývat.
/Úkol: vytvořte proceduru vynásobící hodnotu v buňce A1 třemi. Použijte v proceduře
nedeklarovanou proměnnou, zkuste ji na některém místě zapsat chybně a proceduru spustit. Pak ji
nadeklarujte a zkuste proceduru znovu spustit. /
Pole
Pole je skupina proměnných pod společným jménem, jednotlivé proměnné se rozlišují indexem. Pole
jsou deklarována jako
Dim NazevPole(IndexOd to IndexDo) as typ
Např. MojePole(10 to 110) as integer je pole o 100 prvcích obsahující celá čísla, indexy jsou od 10 do
100
Pole může být i vícerozměrné, definuje se jako
Např.
Dim MojePole(1 to 5, 10 to 110) as long
Je možné vytvářet i dynamická pole, u kterých nezadávám na počátku rozměry. Před použitím je
potřeba mu velikost nastavit (a pak ji lze změnit).
Konstanty
Konstanty jsou pojmenované hodnoty, které se nedají změnit. Definují se stejně jako proměnné, ale
místo „dim“ je použito „const“ a zároveň se jim přiřadí hodnota.
Const Pi As Single = 3,141592654
Konstanty mohou mít stejný rozsah platnosti jako proměnné.
Podmínky
Podmínky umožňují měnit průběh procedur na základě platnosti nebo neplatnosti nějakých výrazů.
Podmínka If-Then
Má tvar
If podmínka then akce
pokud je akce na stejném řádku, jako then, nebo
If podmínka then
akce
end if
pokud je (jsou) akce na dalších řádcích. Provádí akci (akce) v případě, že platí zadaná podmínka.
Pokud podmínka neplatí, neděje se nic.
Podmínka If – Then – Else
Má tvar
If podmínka then
Akce1
Else
Akce2
End if
Pokud podmínka platí, provede (provedou) se akce1, jinak akce2.
Podmínka Select – Case
Umožňuje testovat více „úrovní“ podmínky.
Select Case Proměnná
Case hodnota
Case hodnota to hodnota akce
Case is podmínka hodnota to hodnota akce
…
Case Else akce
End Select
Cykly
Umožňuje opakování akce po předem daný počet opakování, nebo dokud platí nebo neplatí nějaká
podmínka.
Cyklus For – Next
Opakuje akci po daný počet cyklů
For počítadlo = počáteční hodnota to konečná hodnota volitelně step velikost kroku
Akce
Nepovinně Exit for
Next počítadlo
Cyklus Do-While
Provádí akci, dokud platí zadaná podmínka. Pozor na nekonečné cykly!!!
Do While Platí podmínka
Akce
Nepovinně exit do
Loop
Nebo
Do
Akce
Nepovinně exit do
Loop While Platí podmínka
Cyklus Do – Until
Provádí akci, dokud nezačne platit zadaná podmínka.
Do until podmínka
Exit do
loop
nebo
Do
Akce
Nepovinně exit do
Loop until podmínka
Cyklus přes kolekce
Prochází všechny členy kolekce a pro každý člen provede akci
For Each člen kolekce in kolekce
Akce
Nepovinně exit for
Next člen kolekce
Práce s rozsahy
Práce s rozsahy je v Excelu velmi důležitá, proto se jim budeme věnovat podrobněji.
Rozsah v aktivním listu může být použit jako Range(„adresa buňky“) nebo Range(„Adresa1:adresa2“).
Pro pojmenované rozsahy pak Range(„Název rozsahu“). Např. Range(„A1:D10“).Select
Je možné pracovat s celými řádky nebo sloupci, např.
Range(„A:D“) nebo Range(„1:10“) (obdobně Columns(„A:D“) nebo Rows(„1:10“))
Nespojité rozsahy je možno zadat oddělené čárkou, např. Range("A:c, 3:4")
Pokud chceme použít rozsah na jiném listu, je třeba použít příslušný list:
Worksheets(„název listu“). Range…
Pokud se jedná o list v jiném sešitu, použijeme Workbooks(„Název souboru“). Worksheets(„název
listu“). Range…
Např. Workbooks("sešit1").Worksheets("List1").Range("A1:c5").Select
Zadávání rozsahů pomocí buněk (cells)
Rozsahy je možné zadávat i pomocí buněk (Cells). Např.
Worksheets(“List1”).Cells(2, 3) (buňka C2)
Range(Cells(1, 1), Cells(5, 5))
Workbooks("sešit1").Worksheets("List1").Range("A1:c5").Value = 100
Tento způsob je výhodný při programování, protože není potřeba vytvářet textové adresy typu „A1“
Vlastnost Offset
Vrací buňku, která je o zadaný počet buněk od zadaného rozsahu.
Range(“A1”).Offset(1, 2) je buňka C2
Offset může mít i záporné argumenty
Ladění maker
Pokud makro nedělá to, co od něj očekáváme, je potřeba jej odladit. Je možné
-
Makro krokovat - nespustit je „plnou rychlostí“ celé, ale procházet řádek po řádku a
sledovat, co se děje
Sledovat stav jednotlivých objektů a proměnných
Komentáře
Jsou součásti kódu ve VBS editoru, které začínají apostrofem. Vše vpravo od apostrofu se ignoruje.
Nedotkli jsme se
-
Událostí
Práce s chybovými stavy
Formulářů
Download

Poznámky k semináři