logo-IBA
Cyklické odkazy a iterace
Makra – funkce a metody
4b. Makra Visual Basic
pro Microsoft Excel
Vytvořil Institut biostatistiky a analýz, Masarykova univerzita
J. Kalina

logo-IBA
Cyklické odkazy a iterativní výpočty
—Cyklický odkaz je odkaz, který okazuje na proměnnou (oblast), ve které je obsažena i buňka s
odkazem.
—Pro správnou funkci cyklického odkazu je třeba nastavit iterativní přepočet (opakované
přepočítávání hodnot).
—
—
—
—
—
Vytvořil Institut biostatistiky a analýz, Masarykova univerzita
J. Kalina
Možnosti aplikace Excel
Povolení iterací, nastavení jejich počtu a přesnosti (může zpomalovat výpočet).

logo-IBA
Cyklické odkazy a iterativní výpočty
—Lze je využít při hledání přibližného (numerického) řešení např. při optimalizaci nebo řešení
rovnic.
—Užitečné jsou pro tvorbu jednoduchých heatmap přímo v Excelu.
—
—
—
—
—
Vytvořil Institut biostatistiky a analýz, Masarykova univerzita
J. Kalina

logo-IBA
Z historie
—Možnost napsat vlastní funkci/makro je v Excelu od první verze v roce 1985.
—Do roku 1993 (verze 5) byla makra zaznamenávána ve vlastním jazyce Excelu a ukládána jakou soubory
.xlm.
—
—
—
http://upload.wikimedia.org/wikipedia/en/d/d0/VBDOS-icon.PNG
—Starší verze maker jsou zpětně kompatibilní, ale není doporučné jejich použití z hlediska
bezpečnosti.
—Od verze 5 je možné makra zaznamenávat v jazyce Visual Basic.
—Visual Basic byl vyvinut v roce 1991 kombinací staršího jazyka Basic (1964) a prostředí Ruby
společnosti Tripod.
—
—
Vytvořil Institut biostatistiky a analýz, Masarykova univerzita
J. Kalina

logo-IBA
Visual Basic makro
—Účelem maker v Excelu je buď usnadnění opakujících se činností nebo zpřístupnění složitějších
funkcí, kterých není možné dosáhnout při rozumné složitosti ručně, případně kombinace obého.
http://www.planet-source-code.com/vb/2010Redesign/images/LangugeHomePages/VisualBasic6.gif
—Pomocí maker lze rovněž vkládat do listů Excelu interaktivní prvky.
—„Všechno, co jde udělat ručně, lze udělat také pomocí makra.“
—Existují dva režimy zadávání maker – záznam přímo v prostředí Excelu a ruční zápis makra v jazyce
Visual Basic.
—
—
Vytvořil Institut biostatistiky a analýz, Masarykova univerzita
J. Kalina

logo-IBA
Uložení maker
—Makra lze ukládat jakou součástí sešitů Excelu (v tom případě se mění přípona na .xlsm) nebo jako
samostatné sešity Maker.
—Každý uživatel má uložený na disku svého počítače nepřenositelný soubor personal.xlsb, do kterého
může ukládat svá osobní makra – ta zůstávají k dispozici na daném počítači, ale ne jinde.
—Soubory .xlsb jsou zaznamenávány v jiném binárním kódu a umožňují rychlejší načítání (vhodné pro
velké objemy dat). Lze do nich také ukládat makra.
—Makro uložené přímo v sešitě lze otevřít i na jiném počítači (obsahuje bezpečnostní riziko).
—
—
—
—
Vytvořil Institut biostatistiky a analýz, Masarykova univerzita
J. Kalina

logo-IBA
Záznam makra
—Nejprve je nutné zpřístupnit v Excelu kartu Vývojář (od verze 2010):
Vytvořil Institut biostatistiky a analýz, Masarykova univerzita
J. Kalina
„Zobrazit na pásu kartu Vývojář“.
Položka seznamu „Oblíbené“.

logo-IBA
Záznam makra
—Jednoduchý způsob vytvoření makra. K dispozici jsou pouze standardně přístupné funkce, ale lze je
pomocí makra opakovat jako proceduru.
Tlačítko pro zahájení záznamu makra.
Otevírá dialogové okno se seznamem maker.
Přepíná do prostředí Visual Basic
Tlačítko pro zastavení záznamu makra.
Vytvořil Institut biostatistiky a analýz, Masarykova univerzita
J. Kalina
Přepíná mezi absolutními a relativními odkazy v makru.

logo-IBA
Záznam makra
—Před spuštěním záznamu makra:
Uživatelský název makra.
Vytvořil Institut biostatistiky a analýz, Masarykova univerzita
J. Kalina
Klávesová zkratka
neodporující standardním zkratkám. Musí jít o písmeno nebo příbuzný znak. V případě kolize navrhuje
Excel varianty Ctrl nebo Ctrl+Shift.
Místo pro uložení makra.
Volitelný popis makra.

logo-IBA
Záznam makra
—Okno pro spouštění maker:
Úprav makra v prostředí VB.
Krokování makra v prostředí VB.
Spuštění vybraného makra.
Změna popisu a klávesové zkratky.
Seznam vytvořených maker.

logo-IBA
Spouštění makra
—Zobrazení karty Vývojář:
Soubor > Možnosti > Přizpůsobit pás karet

logo-IBA
Visual Basic
—Integrated development environment (IDE):
Okno pro psaní kódu
Project explorer
Properties window

logo-IBA
Visual Basic
Několik úvodních poznámek k jazyku Visual Basic
—jazyk není case sensitive (nerozlišuje malá a velká písmena),
—do kódu lze vepisovat komentáře uvozené apostrofem ',
—mezery a odsazení nemají vliv na interpretaci kódu,
—důležité je rozdělení řádků – jedna funkce na jeden řádek,
—více funkcí na řádku je možné spojit pomocí dvojtečky :,
—dlouhé řádky lze rozdělit pomocí kombinace ,_ .
—
—
—

logo-IBA
Visual Basic
—Dvě základní entity, které lze vytvářet v prostředí Visual Basic jsou metody a funkce (+ objekty).
—Vytvořené funkce se automaticky přenáší do prostředí Excelu (konkrétního sešitu typu .xlsm, ke
kterému je makro připojeno).
—Funkce se od metody liší tím, že má definovánu nějakou návratovou hodnotu.
—Makra nahraná pomocí záznamu maker v Excelu jsou automaticky považována za metody.
—Funkce i metody se zadávají jako zdrojový kód psaný uživatelem nebo generovaný programem do okna
kódu a uvozují se speciálními výrazy.

logo-IBA
Visual Basic - funkce
—Každá funkce je uvozena a uzavřena specifickými příkazy:
Function nazev_funkce(arg1, arg2,…) As typ
tělo funkce
End Function
●Tělo funkce se skládá z operací, v nichž jsou pro výpočet využity proměnné specifikované na vstupu
do funkce (argumenty z 1. řádku funkce) a funkce jazyka Visual Basic.
●Návratová hodnota funkce je určena přiřazením hodnoty do názvu funkce.
nazev_funkce = arg1 + arg2

logo-IBA
Visual Basic - metody
—Každá metoda je uvozena a uzavřena specifickými příkazy:
Sub nazev_metody(arg1, arg2,…)
tělo metody
End Sub
●Tělo metody se skládá z operací, v nichž jsou pro výpočet využity proměnné specifikované na vstupu
do metody a funkce jazyka Visual Basic.

logo-IBA
Visual Basic
—
—
—
Jméno
Popis
Velikost
Rozsah
Integer
Celé číslo
32 bitů
-231 až 231
Long
Celé číslo, ale větší rozsah
64 bitů
-263 až 263
Boolean
Logická hodnota (pravda, nepravda)
8 bitů
True nebo False
String
Textová hodnota
16 bitů pro každý znak
---
Char
Znak
16 bitů
0 až 2-16
Double
Desetinné číslo s dvojitou přesností
64 bitů
± 5 x 10-324 až ± 1,7 x 10308
Primitivní datové typy jazyka Visual Basic

logo-IBA
Visual Basic
Některé užitečné funkce jazyka Visual Basic
vIf podmínka Then příkaz (blok příkazů) End If (v případě bloku),
vWhile podmínka příkaz (blok příkazů) Wend
vFor i = a To b příkaz Next – for cyklus pro předem daný počet kroků,
vSheets("název listu").Select – výběr označeného listu,
vRange("buňka1:buňka2").Select – výběr oblasti buněk,
vRange(buňka1, buňka2).Select – totéž zadáno číselně,
vActiveCell.Offset(radky,sloupce) – přesun do zadané buňky
va Mod b – zbytek po celočíselném dělení čísla a číslem b,
vSqr(a) – druhá odmocnina z čísla a,
v
—

logo-IBA
Visual Basic – objekty a vlastnosti
—Objektově orientované programování pracuje s objekty, které mají určité specifikované vlastnosti.
—Visual Basic považuje v Excelu za objekt celý soubor, list, buňku, graf, ovládací prvek (tlačítko,
zatržítko, fromulář aj.).
—V editoru IDE lze měnit vlastnosti objektů v okně Properties window; některé lze měnit také přímo
v Excelu (např. pojmenování listu, vybarvení buňky) a také samotnými makry.
—Vlastnost objektu lze odkazovat přes tečku ..
—Např. nastavení barvy buňky A1 na červenou se provede následujícím příkazem:
Range("A1").Interior.Color = Red
—

logo-IBA
Visual Basic – události
—Kromě vlastností se k objektu pojí také konkrétní události, které mohou být impulzem pro aktivaci
funkce nebo metody.
—Každý objekt má svoji specifickou sadu událostí, kterých jsou desítky.
—Důležité události mohou být např.:
vActivate – aktivace sešitu (otevření uloženého souboru),
vSheetActivate – aktivace požadovaného listu,
vClick – kliknutí na ovládací prvek,
vChange – změna hodnoty prvku,
vShow – zviditelnění prvku,
vHide – zneviditelnění prvku.
v

logo-IBA
Visual Basic – další zdroje
—Visual Basic je plnohodnotný programovací jazyk, k jeho obsažení by nestačil ani celý předmět
Bi7541,
—existuje celá řada elektronických i klasických učebnic ve všech jazycích,
—příjemnou učebnici lze nalézt např. zde: http://www.gvp.cz/local/new/ucebnice/VisBas/obsah.htm,
—řada věcí je intuitivních a lze na ně přijít i bez odborného základu.
v
http://www.computermedia.cz/knihy/programovani-ve-visual-basicu-2010-CD_big.jpeg
http://www.ucebnice.com/img/auto/138/0/K1611_nahledK1611.jpg