Soubor přednášek z KA

e. Jedná se vždy o datový typ Variant a je vždy volitelným parametrem (i v případě, že nepřidáme klíčové slovo Optional) Příklad Function Jednoduchy (ParamArray pole() As Variant) As Double
For Each prvek in pole
Jednoduchy = Jednoduchy + prvek
Next prvek
End Function
Práce s procedurami Sub ve VBA Procedura obsahuje skupinu (posloupnost) příkazů VBA.
Je umístěna v modulu VBA.
Modul může obsahovat libovolný počet procedur
Procedura se vykonává od začátku do konce, může však být přerušena (ukončena předčasně)
Některé procedury očekávají parametry, které se do procedury předávají při jejím volání.
Výsledky procedur zpravidla závisejí na vstupních parametrech.
Syntaxe :
[Private | Public ] [Static] Sub název [(parametry)]
[příkazy]
[Exit Sub]
End Sub
Práce s procedurami Sub ve VBA Private - procedura je dostupná procedurám ve stejném modulu
Public – Procedura je dostupná všem jiným procedurám ve všech modulech sešitu. Pokud se použije direktiva Option Private nebude procedura dostupná mimo projekt.
Static – proměnné procedury se uchovají i po ukončení procedury.
Sub – Slovo určující začátek procedury
Název – určuje název procedury
Parametry – seznam proměnných, které vstupují do proceduru. Jsou umístěny v závorce, oddělovačem parametrů je čárka.
Příkazy – kód jazyka VBA
Exit Sub – příkaz pro ukončení (vystoupení z) procedury před oficiálním koncem
End Sub – oficiální konec procedury Práce s procedurami Sub ve VBA Všechny příkazy VBA musí být napsány v procedurách nebo funkcích až na některé výjimky :
Proměnné deklarované na úrovni modulu
Deklarace uživatelských datových typů
Volby s globální platností :
Option Explicit
Option Base 1
Option Private
Název procedury by měl vystihovat její účel
V názvu procedur a funkcí nelze používat klíčová slova VBA nebo názvy globálně deklarovaných proměnných a uživatelských typů
Procedury veřejné a privátní Procedura nemusí mít v deklaraci před jménem direktivu „Public“ a přesto je implicitně veřejná. Použití je kvůli jasnějšímu kódu.
Privátní procedury – ty které mají před klíčovým slovem „Sub“ direktivu „Private“, mohou být volány pouze ze stejného modulu.
Chceme-li aby všechny procedury i funkce byly privátní, i ty, které mají před klíčovým slovem „Sub“ direktivu „Public“ – přidáme před první proceduru příkaz :
Option Private Module
Provádění procedur Pomocí příkazu Run | Run Sub/ UseForm nebo klávesou F5. Excel provede proceduru tam, kde se nachází kurzor. Jestliže procedura očekává jeden nebo více parametrů, pak se tato činnost neprovede.
Kliknutím na nějakém objektu na pracovním listu, který spouští proceduru.
Voláním z jiné procedury :
Zadáním názvu procedury, která se má volat
Použitím klíčového slova Call a názvu procedury
Použitím metody Run objektu Application. Používá se tehdy, chceme-li spustit proceduru a název je přiřazen proměnné. Potom se metodě Run předává tato proměnná jako její parametr.
Jako reakci na výskyt nějaké události.
Z okna Imediate v editoru VBA, stačí napsat název procedury, zapsat případné parametry a zmáčknout Enter
Provádění procedur
Private Sub Toto_je_moje_procedura(neco As Byte)
Range(Cells(1, 1), Cells(10, 10)).Value = neco
End Sub
1.Private Sub CommandButton1_Click()
Dim Cislo As Byte
Cislo = InputBox("Zadej nějaké číslo", "Titul", 0)
Toto_je_moje_procedura(Cislo)
End Sub
2. Private Sub CommandButton1_Click()
Dim Cislo As Byte
Cislo = InputBox("Zadej nějaké číslo", "Titul", 0)
Call Toto_je_moje_procedura(Cislo)
End Sub
3. Private Sub CommandButton1_Click()
Dim Cislo As Byte
Cislo = InputBox("Zadej nějaké číslo", "Titul", 0)
Vysledek = Application.Run(“Toto_je_moje_procedura”,Cislo)
End Sub
Provádění procedur
Private Sub Moje()
Select Case WeekDay(Now)
case 0 To 1 : Den_v_tydnu = “List1.Vikend”
case 2 To 6 : Den_v_tydnu = “List1.Prace”
Application.Run Den_v_tydnu,
End Sub
Private Sub Vikend()
MsgBox “Je víkend”
End Sub
Private Sub Práce()
MsgBox “Je pracovní den”
End Sub
Předávání parametrů procedurám
Jako parametr může být některý z následujících typů :
- Proměnná
- Konstanta
- Pole
- Objekt
Procedura :
- Nemusí mít žádný parametr
- Může očekávat pevně stanovený počet parametrů
- Může přijímat neurčený počet parametrů
- Může některý parametr vyžadovat, jiné parametry mohou být
nepovinné
- Může mít všechny parametry nepovinné
Parametry se proceduře předávají 2 způsoby :
- Odkazem
- Hodnotou
Předávání parametrů procedurám
Předávání parametrů proceduře odkazem :
Sub Moje()
Hodnota = 10
Call Spocitej (Hodnota)
MsgBox (Hodnota) ‘ Hodnota = 20
End Sub
Sub Spocitej (MojeHodnota)
MojeHodnota = MojeHodnota + 10
End Sub
Předávání parametrů proceduře hodnotou :
Sub Moje()
Hodnota = 10
Call Spocitej (Hodnota)
MsgBox (Hodnota) ‘ Hodnota = 10
End Sub
Sub Spocitej (ByVal MojeHodnota)
MojeHodnota = MojeHodnota + 10
End Sub
Předávání parametrů proceduře pomocí definovaných datových typů :
Sub MojeProcedura (cislo As Byte, Retezec As String * 30)

Techniky pro ošetření chyb Možné chyby při provádění procedur ve VBA :
syntaktické chyby
chyby při běhu programu (runtime error)
Aby se mohly procedury správně odladit se zpracováním chyb je nutné vypnout volbu Break on All Errors (Tools
--> Option ---> na kartě General).
Zachytávaní chyb : příkazem On Error jsou 2 možnosti :
ignorovat chybu a pokračovat dále
přeskočit do speciální části programu, která má na starosti zpracování chyb. Tato část programu bývá na konci procedury a je označena návěstím
Chceme-li aby po výskytu chyby program pokračoval napište výraz : On Error Resume Next Techniky pro ošetření chyb Pokud dojde při běhu programu k chybě, tu lze zjistit (její číslo) z objektu Err.
Funkci VBA Error lze použít pro zobrazení textu pro položku Err. Ta je u objektu Err výchozí vlastností
Příklad :
MsgBox “Chyba” & Err & “ : “ & Error(Err)
Příkazem On Error můžete určít, kde má program přejít při výskytu chyby :
On Error GoTo Chyba
Příklad zpracování chyb (1):
Sub Vyber_vzorec
On Error Resume Next
Selection.SpecialCells(xlFormulas, XlNumbers).Select
On Error Goto 0
End Sub
On Error GoTo 0 obnoví výchozí zpracování chyb Techniky pro ošetření chyb Příklad zpracování chyb (2):
Sub Vyber_vzorec_2
On Error Resume Next
Selection.SpecialCells(xlFormulas, XlNumbers).Select
If Err 0 Then MsgBox “Nebyly nalezeny žádné buňky“
On Error Goto 0
End Sub
Příklad zpracování chyb (3):
Sub Vyber_vzorec_3
On Error GoTo chyba
Selection.Value = 123
Exit Sub
Chyba : MsgBox “Nelze přiřadit hodnotu“
End Sub
Ladění funkcí Použití funkce MsgBox kvůli sledování hodnot určitých proměnných nebo jejich typu (TypeName)
Funkce vložená do procedury Sub, místo volání ze vzorce na pracovním sešitu (chyby se budou zobrazovat normálním způsobem)
Vložením do těla funkce zarážku – BreakPoint – a funkci potom krokovat – Trace – Step Into (F8). Zarážku umistníme na místě kurzoru klávesou F9 (Toggle BreakPoint) nebo myší v levém sloupci kódu
V kódu použitím rutiny Debug.Print a výpisu těchto proměnných (objektů) v okně Immediate
Ladění funkcí Příklad :
Function Pismena_V_Retezci (retezec) as double
Pocet = 0
For I = 1 to len (retezec)
PISM = UCase(Mid(Retezec,I,1))
If PISM Like “[AEIYOU]” Then
Pocet = Počet + 1
Debug.Print PISM, I
End If
Next I
Pismena_V_Retezci = Pocet
End Function
Techniky – Kopírování a přesun oblastí Příklady :
Sub Zkopiruj_Oblast_1 ()
Range(„A1“).Copy Range(„C3“)
End Sub
Sub Zkopiruj_Oblast_2 ()
Range(Cells(1,1),Cells(10,10)).Copy Range(„C12“)
End Sub
Sub Zkopiruj_Oblast_3 ()
Range(“A1:C20”).Copy Range(„D22“)
End Sub
Sub Zkopiruj_Oblast_4 ()
Sheets(“List1”).Range(“A1”).Copy _
Sheets(“List2”).Range(“A1”)
End Sub
Sub Presun_Oblast_1 ()
Range(Cells(1,1),Cells(10,10)).Cut Range(„C12“)
End Sub
Techniky – Práce s oblastmi Metoda End – očekává jeden parametr, jenž určuje směr, kterým se bude výběr provádět.
Parametry metody End :
xlDown
xlUp
xlToLeft
xlToRight
Příklad
Sub Vyber_oblasti ()
Range(ActiveCell, Activecell.End(xlDown)).Select
End Sub
Nebo
Sub Vyber_oblasti ()
ActiveCell.CurrentRegion.Select
End Sub
Techniky – Práce s oblastmi Zjištění počtu vybraných buněk :
MsgBox Selection.Count
Zjištění počtu vybraných buněk v pojmenované oblasti:
MsgBox Range(“Pokus”).Count
Zjištění počtu vybraných sloupců ve vybrané oblasti :
MsgBox Selection.Columns.Count
Zjištění počtu vybraných řádků ve vybrané oblasti :
MsgBox Selection.Rows.Count
Zjištění počtu sloupců v pojmenované oblasti:
MsgBox Range(“Pokus”).Columns.Count
Zjištění počtu řádků v pojmenované oblasti:
MsgBox Range(“Pokus”).Rows.Count
Techniky – Práce s oblastmi Příklad : Vybarvení všech buněk se zápornou hodnotou červenou barvou
Sub Vybarvi_bunky ()
If TypeName(Selection) “Range” then Exit sub
Const RED = 3
Const Green = 4
For Each Cell in Selection
If Cell.Value < 0 then
Cell.Interior.ColorIndex = RED
Else
Cell.Interior.ColorIndex = Green
End if
Next Cell
End Sub
Techniky – Práce s oblastmi Příklad : Výběr oblasti pomocí metody Union
Private Sub Vyber_oblast()
Dim r1 As Range, r2 As Range, CelaOblast As Range
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
Set CelaOblast = Union(r1, r2)
CelaOblast.Select
***************
Union(Range("A1:B2"), Range(“C3:D4")).Select
***************
End Sub
Techniky – Práce se sešity a listy Příklad : Uložení všech sešitů
Public Sub Ulozit_sesity_1()
Dim Book As WorkBook
For Each Book in WorkBooks
If Book.Path “” Then Book.Save
Next Book
End Sub
Příklad : Uložení a uzavření všech sešitů
Public Sub Ulozit_sesity_2()
Dim Book As WorkBook
For Each Book in WorkBooks
If Book.Name ThisWorkBook.Name then
Book.Close Savechanges = true
End If
Next Book
ThisWorkbook.Close savechanges = true
End Sub
Příklad : Uložení aktuálního sešitu s výpisem cesty a jména souboru
Private Sub Ulozeni_sesitu_3()
MsgBox ThisWorkbook.Path & "\" & ThisWorkbook.Name
ThisWorkbook.Save
End Sub Techniky – užitečné funkce Příklad : Zjištění existence souboru
Private Function File_Exists (file) as Boolean
File_Exists = (Dir(file) “”)
End Function
Příklad : Funkce vrací názav souboru po předchozím zadání celé cesty včetně názvu
Private Function Jmeno_souboru(soubor) as String
Dim I as Integer, delka as integer, temp as String
Delka = len (soubor)
Temp = “”
For I = delka to 1 step -1
If Mid(soubor,I,1) = Application.PathSeparator Then
Jmeno_Souboru = temp
Exit Function
End If
Temp = Mid(soubor,I,1) & Temp
Next I
Jmeno_Souboru = soubor
End Function
Příklad : Funkce vrací TRUE, pokud cesta existuje
Private Function Cesta_existuje(cesta) As Boolean
Dim X As String
Cesta_existuje = false
X = “”
On Error Resume Next
x = GetAttr(cesta)
If Err = 0 Then Cesta_Existuje = true
End Function Tisk Application.Dialogs(xlDialogPrinterSetup).Show – zobrazí dialog pro výběr tiskárny
Argument : printer_text
Application.Dialogs(xlDialogPrint).Show – zobrazí dialog pro tisk
Argumenty : range_num, from, to, copies, draft, preview, print_what, color, feed, quality, y_resolution, selection, printer_text, print_to_file, collate
Application.Dialogs(xlDialogPrint).Show Arg2:=2, Arg3:=5
Application.Dialogs(xlDialogPrintPreview).Show – zobrazí náhled tisku Tisk Appliaction.ActivePrinter – vrací řetězec aktuálně nastavené tiskárny v systému. Pokud je řetězec prázdný, žádná tiskárna není nainstalovaná Tiskarna = Appliaction.ActivePrinter
ActiveSheet.PrintOut – tiskne daný objekt
Parametry : From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName
ActiveSheet.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)
Worksheets(„FAKTURA").PageSetup.PrintArea = "$A$1:$R$50"