Soubor přednášek z KA

olekce : Worksheets(“List1”) nebo Worksheets(1)
Kolekce SHETS pak sdružuje jak pracovní listy, tak listy s grafy
Při odkazu na nějaký objekt pomocí VBA se objekt určuje pomocí tečky – tečkový operátor : Worksheets(1).Range(“A1”)
Odkazy na objekty Range(“A1”) = 10
Worksheets(“List1”).Range(“A1”) = 10
WorkBooks(“Sešit1.xls”). Worksheets(“List1”).Range(“A1”) = 10
Application. WorkBooks(“Sešit1.xls”). Worksheets(“List1”). Range(“A1”) = 10
Vlastnosti objektu Každý objekt má vlastnosti
Použití :
BsgBox Worksheets(“List1”).Range(“A1”). Value
Worksheets(“List1”).Range(“A1”).Value = “ABCD”
Každý objekt má výchozí vlastnost, objekt Range má výchozí vlastnost Value. Výchozí vlastnost objektu se při psaní kódu může vynechat, můžeme tedy napsat :
BsgBox Worksheets(“List1”).Range(“A1”)
Worksheets(“List1”).Range(“A1”) = “ABCD”
Doporučuji však vzhledem k čistotě kódu a větší přehlednosti výchozí vlastnost objektu uvádět.
Vlastnosti objektu, které mají parametry píšeme zásadně do závorek : BsgBox Worksheets(“List1”).Range(“A1”).Address(False)
Metody objektu Každý objekt má své metody
Metoda je nějaká činnost, kterou s objektem provádíme
Použití :
Worksheets(“List1”).Range(“A1:D7”). Clear
Většina metod má parametry, které blíže určují činnost
Parametry metody se umísťují za název metody a oddělují se čárkou : Worksheets(“List1”).Protect “ABCDE”, True, True
Nepovinné parametry metod se nemusí psát, nahrazují se však prázdným místem : Worksheets(“List1”).Protect , True, True
Kód lze psát i včetně pojmenování parametrů, pak u nepovinných parametrů není důležité psát čárky : Worksheets(“List1”).Protect , Structure := True, Windows := True
Definuje text komentáře Text Vrací objekt Comment, který představuje předchozí komentář Previous Vrací objekt Comment, který představuje následující komentář Next Vymaže komentář Delete Popis Metoda Pokud je komentář viditelný, pak je True, jinak False Ne Visible Vrací objekt Shape, který je představuje tvar přidružený komentáři Ano Shape Vrací rodičovský objekt komentáře (vždy je to Range) Ano Parent Pouze u Mac Ano Creator Vrací název osoby, která aplikaci vytvořila Ano Author Vrací název aplikace, která komentář vytvořila Ano Application Popis Jen pro čtení Vlastnost Vlastnosti a metody objektu Comment Kolekce Comments Každý list má kolekci Comments, jež je tvořena objekty Comment listu : Worksheets(“List1”).Comments(1) – kód odkazuje na 1. komentář listu 1.
MsgBox Worksheets(“List1”).Comments(1).Text
Jelikož je kolekce Comments také objekt, má své vlastnosti i metody : MsgBox ActiveSheet.Comments.Count – vrací celkový počet komentářů v sešitě
Vlastnost Comment má i objekt Range : Tato vlastnost vrací objekt Comment. Příkaz se odkazuje na objekt Comment v buňce A1: Range(“A1”).Comment kdyby to byl první komentář v listu, mohli bychom se na tento objekt komentáře odkazovat jako na Comments(1)
Pro zobrazení komentáře uloženého v buňce A1 použijeme : MsgBox Range(“A1”).Comment.Text Přidání nového objektu Comment Metoda AddComment pro přidání komentáře náleží objektu Range
Range(“A1”) .AddComment
Jelikož je AddComment metoda, pak parametrem pro vytvoření bude textový řetězec, který není v závorce :
Range(“A1”) .AddComment “Toto je pokusný komentář”
Sešit, který obsahuje právě vybranou proceduru ThisWorkBook Vybraný objekt Selection Vybrané buňky na pracovním listu v určeném okně RangeSelection Aktivní sešit ActiveWorkBook Aktivní okno ActiveWindow Aktivní list ActiveSheet Aktivní list typu graf, nebo graf vložený na pracovním listu ActiveChart Aktivní buňka ActiveCell Popis Vlastnost Příklady použití :
ActiveWindow.Rangeselection.Value = 12
MsgBox ActiveWindow.Rangeselection.Count
MsgBox ActiveWorkBook.Name
Užitečné vlastnosti objektu Application Objekty Range Tři způsoby odkazu na objekty Range :
Vlastnost Range objektu Worksheet
Vlastnost Cell objektu WorkSheet
Vlastnost Range :
WorkSheets(“List1”).Range(“A1”).Value = 1
WorkSheets(“List1”).Range(“Odpisy”).Value = 1
WorkSheets(“List1”).Range(“A1:B10”).Value = “ABC”
WorkSheets(“List1”).Range(“A1”,”B10”).Value = “NIC”
WorkSheets(“List1”).Range(“C1:C10 A6:E6”).Value = 100 (průnik množin)
WorkSheets(“List1”).Range(“A1, A3, A10, A15, C7”).Value = 20
Objekty Range Vlastnost Cells : (používáme u objektu Worksheet a Range)
WorkSheets(“List1”).Cells(1,1) = 100
ActiveSheet.Cells(3,7) = “ABC”
ActiveCell.Cells(3,3) = “ABC” - dosadí hodnotu o 3 sloupce doprava a 3 řádky dolů proti aktuální buňce (focus)
ActiveSheet.Cells(3) = “ABC” - dosadí hodnotu do buňky C1
WorkSheet(“Pokus”).Range(“A1:D10”).Cells(5) = “ABC” – 5. buňka v pořadí je ve čtverci, který udává pole A1:D10, počítáno po sloupcích v rámci řádku je to 1. buňka 2. řádku tedy buňka A2
Objekty Range Vlastnost Offset : (používáme u objektu Range) – očekává dva parametry, které představují relativní polohu vzhledem k levé horní buňce daného objektu Range. Parametry mohou mít :
- kladnou hodnotu (směr vpravo nebo dolů)
- zápornou hodnotu (směr vlevo nebo nahoru)
- nulovou hodnotu
ActiveCell. Offset(1,0).Value = “ABC” – zapíše hodnotu hned pod aktivní buňku
ActiveCell. Offset(0,1).Value = “ABC” – zapíše hodnotu hned nad aktivní buňku

Příklad 1 Function Secti(rozsah As Range, cislo As Byte) As Byte
Dim co As Byte
co = 0
For Each cell In rozsah
If cislo = cell.Value Then
co = co + 1
End If
Next cell
Secti = co
End Function Příklad 2 Function Secti2(rozsah As Range) As String
Dim co(1 To 5) As Byte
Dim Retez As String
For a = 1 To 5
co(a) = 0
Next a
Retez = ""
For Each cell In rozsah
Select Case cell.Value
Case 1 To 4: co(cell.Value) = co(cell.Value) + 1
Case Else: co(5) = co(5) + 1
End Select
Next cell
For a = 1 To 5
Retez = Retez & Str(co(a)) & ", "
Next a
Secti2 = Retez
End Function Vytváření funkcí deklarací Function Jedná se vlastně o procedury, které nejsou deklarovány klausulí
Sub, ale klausulí Function.
Na proceduru deklarovanou klausulí Sub pohlížíme jako na příkaz,
kdežto procedura definovaná klausulí Function vždy vrací hodnotu.
Funkce jsou všestranné a dají se používat nejen jako součásti výrazu
v procedurách VBA, ale taky ve vzorcích, které vytváříme v
buňkách listu pracovního sešitu.
Function Opacne (Retezec) As String
‘Řetězec znaků obrátí opačně
Opacne = “”
Delka_Retezce = Len(Retezec)
For I = Delka_Retezce To 1 Step -1
Opacne = Opacne & Mid(Retezec,I,1)
Next I
End Function
Použití funkce Použití funkce v pracovním sešitu
Funkce musí být umístěna v normálním modulu, ten vyvoláte přes Project Explorer, kliknutím pravého tlačítka myši vyvoláte nabídku, zvolte Insert/ Module.
Vaše funkce je umístěna v kolekci funkcí MS Excel kategorii vlastní (nabídka Vložit/ Funkci)
Funkci se v listu volá následovně :
= Opacne(C3)
Vaše funkce lze stejně jako funkce MS Excel vnořovat :
= Opacne(Opacne(C3))
Použití funkce v proceduře VBA
Sub Opacne_proc()
Dim Vstup As String
Vstup = InputBox(“Zadejte text”)
MsgBox Opacne(Vstup), , Vstup
End Sub
Filosofie funkce Pozor !! Funkce slouží k tomu, aby vracela nějakou hodnotu, chová se vlastně jako proměnná.
Funkce pracovního listu nemůže provádět žádnou činnost s objekty.
Syntaxe :
[Private | Public ] [Static] Function název [(parametry)] [As typ]
[příkazy]
[Exit Function]
End Function
Private - funkce je dostupná procedurám a funkcím ve stejném modulu
Public – funkce je dostupná všem jiným procedurám a funkcím ve všech modulech sešitu.
Static – hodnoty proměnných deklarovaných ve funkci se budou mezi jednotlivými voláními funkce uchovávat
Function – Slovo určující začátek funkce, která vrací hodnotu
Název – určuje název funkce
Parametry – seznam proměnných, které reprezentují parametry předávané funkci. Tyto parametry jsou zapsány v závorkách
Typ – Datový typ hodnoty vrácené touto funkcí
Příkazy – kód jazyka VBA
Exit Function – příkaz pro ukončení (vystoupení z) funkce před oficiálním koncem
End Function – oficiální konec funkce
Filosofie funkce a její provádění Je třeba mít na paměti následující body :
Pokud nedeklarujete rozsah platnosti funkce v rámci modulu, bude tato funkce standardně veřejná (Public)
Funkce deklarovaná jako soukromá (Private) se neobjeví v kategorii Vlastní dialogovém okně Vložit/Funkci. S takovouto platností má funkce smysl pouze tehdy, nechcete-li, aby uživatel o této funkci věděl, ale přitom funkce mohla být volána některou z Vámi deklarovaných procedur.
Pokud potřebujete volat funkci deklarovanou v jiném sešitě, musíte nastavit odkaz do jiného sešitu pomocí příkazu editoru VB Tools  References
Provádění funkce :
Volání funkce z jiné procedury
Celkem = SectiPole(TotoPole)
Celkem = Application.Run (“Sectipole”, “TotoPole”)
Použití funkce ve vzorci pracovního listu :
Musíme zajistit, aby MS Excel dokázal funkci najít. Je-li funkce deklarovaná ve stejném sešitě, nemusí se dělat nic, ale pokud ne :
Provádění Funkce Před název funkce uvést název sešitu :
= MojeFunkce.xls!SpocitejBunky(A1:C10)
Nastavit odkaz na sešit :
V editoru VB volbou nabídky Tools  References .
Vytvořením doplňku
Když si ze sešitu, který obsahuje funkce vytvoříme doplněk, nebudeme muset při volání některé funkce používat odkaz na soubor.
Řešení přepočtu funkce :
Funkce se přepočte okamžitě, změní-li se nějaká buňka :
Application.Volatile True
Metoda Volatile objektu Application má pouze jediný parametr (true nebo false) Funkce s polem parametrů Funkce mohou mít jako nepovinný parametr pole nebo více polí, následující funkce bude mít jako parametr pole listu a vrací jeho celkový součet :
Function CelkemPole (List) As double
Dim Polozky As Variant
CelkemPole = 0
For Each Polozky in List
If Application.IsNumber(Polozky) Then _
CelkemPole = CelkemPole + Polozky
Next Polozky
End Function
Funkce s nepovinnými parametry Mnohé vestavěné funkce mají množství nepovinných parametrů, například funkce, která vrací znaky z levé části řetězce Left má syntaxi : LEFT (text [, počet_znaků])
První parametr text je povinný, druhý se však uvádět nemusí. Pokud se druhý parametr vynechá, implicitní hodnotou tohoto druhého parametru je 1.
= LEFT (A1,1)
= LEFT (A1)
Oba dva výstupy funkce left jsou stejné.
Vlastní funkce, které napíšete v jazyce VBA mohou mít rovněž nepovinné parametry. Parametr označíme jako nepovinný tak, že před jeho název napíšeme klíčové slovo Optional.
Function Kresli(Oblast As variant, Optional Prepocitej As Boolean – False)
Application.Volatile Prepocitej
Kresli = Oblast(Int((Oblast.Count) * Rnd +1))
End Function
V seznamu parametrů se mohou nepovinné parametry objevit až za posledním povinným parametrem.
Funkce, která vrací pole VBA Jazyk VBA obsahuje užitečnou funkci Array. Tato funkce vrací datový typ variant, který obsahuje pole :
Function NazvyMesicu()
NazvyMesicu = Array(“Leden”, ”Únor”, ”Březen”, ”Duben”, _ ”Květen”, ”Červen”, ”Červenec”, ”Srpen”,”Září”, ”Říjen”, _ ”Listopad”, ”Prosinec”)
End Function
V MS Excel označte buňky, do kterých chcete funkci vložit, pak napište jméno funkce ( =NazvyMesicu() )a zmáčkněte současně klávesy CTRL+SHIFT+ENTER
Funkce s neurčitým počtem parametrů Některé funkce požadují předem neurčitý počet parametrů, příkladem této funkce může být funkce SUM :
= SUM (číslo1, číslo2, .. , číslon)
První parametr této funkce je vyžadován, ostatní jsou nepovinné, následuje příklad funkce SUM, která sečte 4 oblasti :
= SUM (A1:C3, C7:D9, D12:E15, G13:O5)
Typy parametrů lze rovněž kombinovat, lze napsat například takovouto funkci :
= SUM (A1:C3, 55, D8, 12*2)
Nebo si můžeme napsat sami funkci, která bude mít nespecifikovaný počet parametrů pomocí klíčového slova ParamArray. Toto klíčové slovo může být použito u posledního parametru v množině parametrů funkc