MS Office 2007 VBA

Nahrávání makra

Word

Excel

Spuštění makra

Jinou možností je vytvořit si certifikát.

Změna zabezpečení se projeví až po zavření a otevření aplikace Word nebo Excel.

Přiřazení makra klávesové zkratce

Word

Excel

Přiřazení makra tlačítku na panelu nástrojů Rychlý přístup

Word

Excel

Přiřazení makra objektu

Spuštění makra událostí

Další možnosti spouštění maker

Ribbon

Word

Excel

Formuláře

Word

Excel

Zamknou se jednotlivé listy s formulářem pomocí nabídky na kartě Revize.

Po zamknutí není možné vkládat informace do buněk kromě tech, které jsou propojené s ovládacími prvky, a ty se mění podle toho, jak uživatel nastaví propojený ovládací prvek.
Formuláře vrácené od respondentů se zpracují tak, aby vznikla excelovská tabulka s řádky patřícími jednotlivým respondentům a sloupky patřícími jednotlivým otázkám. Pro tuto úlohu existuje doplněk aplikace Excel zvaný Průvodce šablonou se sledováním dat, který již není dodáván s programovým balíkem MS Office a od roku 2012 již ani není oficiálně dostupný na stránkách firmy Microsoft.
Doplněk se jmenuje WZTEMPLT.XLA a je ve formě samorozbalovacího programu TMPLWIZ.EXE, ale po jeho spuštění kliknutím na něj se soubory v něm zabalené nikde neobjeví kvůli nekompatibilitě s novými operačními systémy Windows. Proto je nutné soubory z něj vyjmout nějakou externí aplikací, například programem 7-Zip.

V adresáři, kde Excel hledá dodatečné doplňky, se vytvoří adresář TemplateWizard a do něj se zkopírují soubory z archivu TMPLWIZ.EXE.

Použití je zhruba takové, že se doplněk připojí k aplikci Excel,

potom se na pásu karet objeví nová karta Doplňky s tlačítkem Template Wizard.

Po kliknutí na tlačítko Template Wizard se určí název a umístění šablony, kterou průvodce vytvoří,

název a umístění databáze (výsledné tabulky s daty formulářů), kterou průvodce vytvoří,

nadefinují se buňky ve formuláři, které obsahují data formulářů.

Průvodce také umožňuje více souborů s vyplněnými dotazníky uložit do databáze najednou.

Výsledná databáze je umístěna v adresáři, který byl nastaven pomocí průvodce.

Ve starších verzích Excelu fungovalo ještě otevření formuláře na základě šablony vytvořené Průvodcem šablonou se sledováním dat (pomocí nabídky Nový pod Tlačítkem Office),

po jehož vyplnění a uložení se data automaticky odeslala do databáze. V případě, že budou data sbírána rozesláním formulářů a jejich následným hromadným zpracováním, je to však funkčnost, kterou nepotřebujeme.

Programovací jazyk VBA

Práce s nápovědou

Makro

Vhodné akce pro makra:

• Vícenásobná nahrazování
• Akce spojené s otevíráním nových dokumentů
• Práce s více dokumenty najednou

Objektový model aplikace

Vlastnost, metoda a událost objektu

• Lze číst a měnit vlastnosti objektu.
• Lze přimět objekt k vykonání akce aktivací metody, kterou objekt umí.
• Lze napsat program, který se spustí, kdykoli se objektu stane určitá událost.

Vlastnost objektu

Metoda objektu

Například

Range("a1").Select

Událost objektu

Přístup k objektu

Jen málokdy je nutné přistupovat k objektu vypsáním celé hierarchie:

Application.ActiveWorkbook.ActiveSheet.Range("a1").Value = "ahoj"

Range("a1").Value = "ahoj"

• Indexem listu

  Worksheets(1).Range("a1").Value = "ahoj"

• Názvem listu

  Worksheets("List1").Range("a1").Value = "ahoj"

Kolekce objektů

Kolekce Sheets versus Worksheets v Excelu

Základní prvky programů v jazyce VBA

• sub procedury (Sub Procedures nebo subprocedures začínající slovem Sub.)
• funkce (User-Defined Functions - UDF začínající slovem Function.)

Range("a1").Select

i = Len("ahoj")

Konstanty

• vestavěné (built-in)
• definované programátorem (user-defined)

Vestavěné konstanty

ActiveWindow.WindowState = xlMaximized

Konstanty definované programátorem

Const DISCOUNT As Single = 0.4

Proměnné

Do proměnné lze přiřadit

• vlastnost objektu způsobem Proměnná = Objekt.Vlastnost
• objekt způsobem Set Proměnná = Objekt

Deklarace proměnných

Option Explicit

Dim i

Dim i As Integer

• při psaní kódu bude fungovat automatické dokončování (když je datovým typem objekt),
• při ladění kódu se bude kontrolovat, zda se do proměnné přiřazují hodnoty očekávaného typu a
• interpret VBA nemusí určovat typ proměnné za běhu programu a proto program bude běžet trochu rychleji.

Na co dát pozor

Dim a, b As Integer

Dim a As Integer, b As Integer

Řídící struktury

• rozhodování a
• cyklus.

     "první řádek" & _
     "druhý řádek"

Komentáře

Argumenty neboli parametry procedur VBA

Specifikace argumentů pozicí nebo jménem

Příklad s funkcí MsgBox

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

Specifikace argumentů pozicí

MsgBox "Ahoj", , "Pozdrav"

Specifikace argumentů jménem

MsgBox Title:="Pozdrav", prompt:="Ahoj"

Argumenty v závorkách

Funkce MsgBox

Parametr buttons ve funkci MsgBox

a = MsgBox("Ahoj", vbAbortRetryIgnore + vbExclamation + vbDefaultButton3, "Pozdrav")

a = MsgBox("Ahoj", 562, "Pozdrav")

• z názvů konstant lze vyčíst podobu message boxu,
• když je v programu zavedena pojmenovaná konstanta, je chyba ji do některých míst programu napsat jako hodnotou a ne jako jméno.

Návratové hodnoty funkce MsgBox

Funkce InputBox

b = InputBox("Zadej něco", "Zadávání", "něco")

• Kliknutí na tlačítko OK uloží do proměnné b zadanou hodnotu.
• Kliknutí na tlačítko Cancel uloží do proměnné b prázdný řetězec, takže se přepíše předchozí hodnota b.

     a = InputBox("Zadej číslo", "Zadávání")
     b = InputBox("Zadej číslo", "Zadávání")
     MsgBox a + b

     a = Val(InputBox("Zadej něco", "Zadávání"))
     b = Val(InputBox("Zadej něco", "Zadávání"))
     MsgBox a + b

     a = Val(InputBox("Zadej něco", "Zadávání"))
     b = Val(InputBox("Zadej něco", "Zadávání"))
     MsgBox a & b

     Dim a As Integer, b As Integer
     a = InputBox("Zadej něco", "Zadávání")
     b = InputBox("Zadej něco", "Zadávání")
     MsgBox a + b

Metoda Application.InputBox

c = Application.InputBox("Zadej číslo:", Type:=1)

c = Application.InputBox("Zadej číslo", Type:=1 + 2)

Předávání argumentů mezi procedurami

     Option Explicit
     Dim a As Integer

     Sub Hlavní()
     a = Application.InputBox("Zadej číslo:", Type:=1)
     Ztrojnásob
     MsgBox a, , "Hlavní"
     End Sub

     Sub Ztrojnásob()
     a = a * 3
     MsgBox a, , "Ztrojnásob"
     End Sub

     Option Explicit
     Dim a As Integer, b As Integer

     Sub Hlavní()
     a = Application.InputBox("Zadej číslo:", Type:=1)
     b = Application.InputBox("Zadej číslo:", Type:=1)
     Ztrojnásob_a
     Ztrojnásob_b
     MsgBox "a = " & a & Chr(13) & Chr(10) & "b = " & b, , "Hlavní"
     End Sub

     Sub Ztrojnásob_a()
     a = a * 3
     MsgBox a, , "Ztrojnásob_a"
     End Sub

     Sub Ztrojnásob_b()
     b = b * 3
     MsgBox b, , "Ztrojnásob_b"
     End Sub

     Option Explicit

     Sub Hlavní()
     Dim a As Integer, b As Integer
     a = Application.InputBox("Zadej číslo:", Type:=1)
     b = Application.InputBox("Zadej číslo:", Type:=1)
     Ztrojnásob a
     Ztrojnásob b
     MsgBox "a = " & a & Chr(13) & Chr(10) & "b = " & b, , "Hlavní"
     End Sub

     Sub Ztrojnásob(x As Integer)
     x = x * 3
     MsgBox x, , "Ztrojnásob"
     End Sub

     Option Explicit

     Sub Hlavní()
     Dim a As Integer, b As Integer
     a = Application.InputBox("Zadej číslo:", Type:=1)
     b = Application.InputBox("Zadej číslo:", Type:=1)
     Ztrojnásob (a)
     Ztrojnásob (b)
     MsgBox "a = " & a & Chr(13) & Chr(10) & "b = " & b, , "Hlavní"
     End Sub

     Sub Ztrojnásob(x As Integer)
     x = x * 3
     MsgBox x, , "Ztrojnásob"
     End Sub

     Option Explicit

     Sub Hlavní()
     Dim a As Integer, b As Integer
     a = Application.InputBox("Zadej číslo:", Type:=1)
     b = Application.InputBox("Zadej číslo:", Type:=1)
     Ztrojnásob a
     Ztrojnásob b
     MsgBox "a = " & a & Chr(13) & Chr(10) & "b = " & b, , "Hlavní"
     End Sub

     Sub Ztrojnásob(ByVal x As Integer)
     x = x * 3
     MsgBox x, , "Ztrojnásob"
     End Sub

     Option Explicit

     Sub Hlavní()
     Dim a As Integer, b As Integer
     a = Application.InputBox("Zadej číslo:", Type:=1)
     b = Application.InputBox("Zadej číslo:", Type:=1)
     Ztrojnásob a, b
     MsgBox "a = " & a & Chr(13) & Chr(10) & "b = " & b, , "Hlavní"
     End Sub

     Sub Ztrojnásob(ByVal x As Integer, ByRef y As Integer)
     x = x * 3
     y = y * 3
     MsgBox "x = " & x & Chr(13) & Chr(10) & "y = " & y, , "Ztrojnásob"
     End Sub

Pole jako argumenty předávané mezi procedurami

     Option Explicit

     Sub PracujSListy(Listy As Variant, Hodnota As Variant)
     Dim IndexListu As Byte
     Dim ListSešitu As Worksheet
     Dim Zpráva As String
     Zpráva = ""
     IndexListu = LBound(Hodnota)
     For Each ListSešitu In Listy
         ListSešitu.Cells(1) = Hodnota(IndexListu)
         Zpráva = Zpráva & ListSešitu.Name & vbCrLf
         IndexListu = IndexListu + 1
     Next ListSešitu
     MsgBox Zpráva
     Listy.Select
     End Sub

     Sub PráceSeSkupinouListů()
     PracujSListy Worksheets(Array(1, 3)), Array(5, 10)
     End Sub

Uživatelské formuláře UserForms

     Sub Ukaž_Formulář()
     UserForm1.Show
     End Sub

Ovládací tlačítka uživatelských formulářů

	Label - Popis Slouží pro zobrazování informací pro uživatele.
	TextBox - Textové pole Slouží pro zadávání informací uživatelem a zobrazování informací, které může uživatel vybrat a kopírovat jinam.
	ComboBox - Pole se seznamem Slouží pro výběr položky ze seznamu a zadání položky uživatelem.
	ListBox - Seznam Slouží pro výběr položky ze seznamu. Pole se seznamem a Seznam mají vestavěný Posuvník, viz níže, který se na nich objeví, přesáhne-li počet položek hodnotu vlastnosti ListRows.
	CheckBox - Zaškrtávací políčko Slouží pro zadávání informace o hodnotě jeden bit.
	OptionButton - Přepínač Slouží pro výběr položky ze seznamu, neboli jednoho přepínače ze skupiny přepínačů. Musí se vyskytovat ve skupinách nikoli jednotlivě. Skupiny jsou tvořeny vlastností GroupName nebo jejich umístěním do rámečku. Volně umístěné přepínače se také chovají jako jedna skupina.
	Frame - Rámeček Tvoří skupinu z přepínačů. Graficky odděluje části formuláře pro jeho lepší přehlednost. Při tvorbě formuláře umožňuje manipulovat s prvky v něm umístěnými jako s jedním celkem.
	ScrollBar - Posuvník Slouží pro plynulé zadávání hodnot z daného rozpětí.
	SpinButton - Číselník Slouží pro zadávání hodnot z daného rozpětí měněných po určitém kroku, například je tak vhodné realizovat zvýšení ceny o 50 haléřů. Inkrementace v oboru jiném než celočíselném se však musí odvozovat z celočíselných hodnot, které jediné jsou přípustné pro vlastnost Value prvku Číselník i Posuvník. Číselník musí být kódem makra propojen s nějakým prvkem zobrazujícím výsledek kliknutí na něj, typicky s Textovým polem. Některé produkty mají tento prvek již v této kombinaci a nazývají jej Spinner.
	CommandButton - Příkazové tlačítko Slouží pro spuštění maker.
	ToggleButton - Přepínací tlačítko Zobrazuje, zda je tlačítko zamáčklé nebo vymáčklé.
	TabStrip - Karty Slouží pro zobrazování datových vět na jednotlivých kartách. Ovládací prvky se na něj přetáhnou jen jednou a potom se zobrazují na všech kartách stejně. Musí se k němu napsat kód propojující jej s tabulkou dat.
	MultiPage - Vícenásobná stránka Slouží pro organizaci ovládacích prvků do více stránek, ze kterých si může uživatel vybírat. Na rozdíl od prvku Karty může být na každé stránce jiný obsah.
	Image - Obrázek Slouží pro zobrazování obrázků.
	RefEdit Tento prvek se musí na soupravu nástrojů přidat dodatečně, viz výše. Slouží pro zadávání oblastí buněk, se kterými potom mohou pracovat makra.

Funkce

• vestavěné (built-in)
• z jazyka Visual Basic
• funkce listu (Worksheet Functions)
• definované programátorem (User-Defined Functions - UDF)

Vestavěné funkce z jazyka Visual Basic

Funkce listu (Worksheet Functions)

ActiveCell.FormulaR1C1 = "=UPPER(RC[-1])"

ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"

     Sub Pročisti()
     Dim a As String, b As String
     a = "    a       a    "
     a = Trim(a)
     b = "    b       b    "
     b = WorksheetFunction.Trim(b)
     End Sub

Funkce definované programátorem (User-Defined Functions - UDF)

     Function Fahrenheit(Celsius As Double) As Double
     Fahrenheit = Celsius * 9 / 5 + 32
     End Function

     Sub CelsiusToFahrenheit()
     Dim c As Double
     c = Application.InputBox("Zadej stupně Celsia", Type:=1)
     MsgBox c & " stupňů Celsia = " & Fahrenheit(c) & " stupňů Fahrenheita."
     End Sub

Metoda Volatile

     Function Fahrenheit_A1() As Double
     Fahrenheit_A1 = Range("a1") * 9 / 5 + 32
     End Function

     Function Fahrenheit_A1() As Double
     Application.Volatile
     Fahrenheit_A1 = Range("a1") * 9 / 5 + 32
     End Function

Co UDF neumějí

Vybrané typy UDF

Funkce, která prochází oblast

     Function Kolikrát(Co As String, Kde As Range) As Integer
     Dim Buňka As Range
     Kolikrát = 0
     For Each Buňka In Kde
         If Buňka.Value = Co Then Kolikrát = Kolikrát + 1
     Next Buňka
     End Function

Funkce vracející hodnotu PRAVDA / NEPRAVDA

     Function Podmnožina(x As Range, y As Range) As Boolean
     Dim BuňkaX As Range, BuňkaY As Range
     Podmnožina = False
     For Each BuňkaX In x
         For Each BuňkaY In y
             If BuňkaX = BuňkaY And BuňkaX <> "" Then
                 Podmnožina = True
                 Exit Function
             End If
         Next BuňkaY
     Next BuňkaX
     End Function

Funkce manipulující s jednotlivými znaky

     Function Pozpátku(x As String) As String
     Dim i As Integer
     Pozpátku = ""
     For i = 1 To Len(x)
         Pozpátku = Pozpátku & Mid(x, Len(x) - i + 1, 1)
     Next i
     End Function

Funkce vracející matici

     Function MocninaMatice(x As Range, y As Byte) As Variant
     Dim i As Byte
     MocninaMatice = x
     For i = 1 To y - 1
         MocninaMatice = WorksheetFunction.MMult(MocninaMatice, x)
     Next i
     End Function

Funkce vracející vektor

     Function DnyHodinyMinutySekundy(x As Long) As Variant
     Dim b(1 To 4) As Long, SekundVeDni As Long
     SekundVeDni = CLng(60) * 60 * 24 '= 86400. Bez CLng (typová konverze) to přeteče.
     b(1) = x \ SekundVeDni  'Početní úkony pro každou proměnnou: zpětné lomítko znamená v jazyce VBA opearci DIV (celočíselné dělení bez zbytku).
     b(2) = (x - SekundVeDni * b(1)) \ 3600
     b(3) = (x - SekundVeDni * b(1) - 3600 * b(2)) \ 60
     b(4) = x - SekundVeDni * b(1) - 3600 * b(2) - 60 * b(3)
     DnyHodinyMinutySekundy = b
     End Function

Funkce volající jinou funkci

     Function NejbližšíPátek13(ByVal Datum As Date) As Date 'Pokud není uvedeno ByVal, je předáváno jako ByRef.
     While Not (Day(Datum) = 13 And Weekday(Datum) = vbFriday)
         Datum = Datum + 1
     Wend
     NejbližšíPátek13 = Datum
     End Function

     Function ZaKolikDníJeNejbližšíPátek13(Datum As Date) As Integer
     Dim DatumNejbližšíhoPátku13 As Date
     DatumNejbližšíhoPátku13 = NejbližšíPátek13(Datum) 'Volání předchozí funkce
     ZaKolikDníJeNejbližšíPátek13 = DatumNejbližšíhoPátku13 - Datum
     End Function

Zachytávání běhových chyb

Zachytávání běhových chyb v makrech

     Sub MakroChyba1()
     Dim Oblast As Range
     Dim a As Integer, b As Integer
     On Error Resume Next
     Set Oblast = Application.InputBox("Vyber buněčnou oblast:", Type:=8)
     If Oblast Is Nothing Then MsgBox "Nebyla vybána buněčná oblast." Else Oblast.Select
     a = Application.InputBox("Zadej čitatel:", Type:=1)
     b = Application.InputBox("Zadej jmenovatel různý od 0:", Type:=1)
     MsgBox a & " / " & b & " = " & a / b
     End Sub

     Sub MakroChyba2()
     Dim Oblast As Range
     Dim a As Integer, b As Integer
     On Error Resume Next
     Set Oblast = Application.InputBox("Vyber buněčnou oblast:", Type:=8)
     If Oblast Is Nothing Then MsgBox "Nebyla vybána buněčná oblast." Else Oblast.Select
     On Error GoTo 0
     a = Application.InputBox("Zadej čitatel:", Type:=1)
     b = Application.InputBox("Zadej jmenovatel různý od 0:", Type:=1)
     MsgBox a & " / " & b & " = " & a / b
     End Sub

     Sub MakroChyba3()
     Dim Oblast As Range
     Dim a As Integer, b As Integer
     On Error Resume Next
     Set Oblast = Application.InputBox("Vyber buněčnou oblast:", Type:=8)
     If Oblast Is Nothing Then MsgBox "Nebyla vybána buněčná oblast." Else Oblast.Select
     On Error GoTo 0
     a = Application.InputBox("Zadej čitatel:", Type:=1)
     Do
         b = Application.InputBox("Zadej jmenovatel různý od 0:", Type:=1)
     Loop Until b <> 0
     MsgBox a & " / " & b & " = " & a / b
     End Sub

     Sub MakroChyba4()
     Dim Oblast As Range
     Dim a As Integer, b As Integer
     On Error GoTo ErrorHandler
     Set Oblast = Application.InputBox("Vyber buněčnou oblast:", Type:=8)
     On Error GoTo 0
     Oblast.Select
     a = Application.InputBox("Zadej čitatel:", Type:=1)
     Do
         b = Application.InputBox("Zadej jmenovatel různý od 0:", Type:=1)
     Loop Until b <> 0
     MsgBox a & " / " & b & " = " & a / b
     Exit Sub

     ErrorHandler:
     MsgBox "Nebyla vybána buněčná oblast." & Chr(13) & Chr(10) & "Bude vybána buněčná oblast automaticky."
     Set Oblast = Range("a1:b2")
     Resume Next

     End Sub

     Sub MakroChyba5()
     Dim Oblast As Range
     Dim a As Integer, b As Integer
     On Error Resume Next
     Set Oblast = Application.InputBox("Vyber buněčnou oblast:", Type:=8)
     If Err.Number <> 0 Then
         MsgBox "Nebyla vybána buněčná oblast." & Chr(13) & Chr(10) & "Bude vybána buněčná oblast automaticky."
         Set Oblast = Range("a1:b2")
     End If
     On Error GoTo 0
     Oblast.Select
     a = Application.InputBox("Zadej čitatel:", Type:=1)
     Do
         b = Application.InputBox("Zadej jmenovatel různý od 0:", Type:=1)
     Loop Until b <> 0
     MsgBox a & " / " & b & " = " & a / b
     End Sub

Následující kód lze přiřadit tlačítku na formuláři typu UserForm, které po kliknutí na něj posune vybranou oblast na listu i s formátováním o řádek nahoru.

     Option Explicit
     Private Sub CommandButton1_Click()
     Dim Oblast As Range
     Dim List As Worksheet
     On Error Resume Next 'Můžeme zapomenout vybrat oblast.
     Set Oblast = Range(RefEdit1.Value)
     On Error GoTo 0
     If Oblast Is Nothing Then
         MsgBox "Musíte vybrat oblast!"
     Else
         Set list = Oblast.Worksheet
         List.Activate 'Musíme vybrat list, abychom na něm mohli použít metodu Select.
         Oblast.Cut
         On Error Resume Next 'Můžeme se posunout za okraj listu.
         Oblast.Offset(-1, 0).Select
         If Err.Number = 0 Then
             ActiveSheet.Paste
             RefEdit1.Value = Selection.Address
         End If
         On Error GoTo 0
     End If
     End Sub

Zachytávání běhových chyb v UDF

     Function MojeDěleníNeošetřené(Čitatel As Double, Jmenovatel As Double) As Double
     MojeDěleníNeošetřené = Čitatel / Jmenovatel
     End Function

     Function MojeDěleníOšetřené(Čitatel As Double, Jmenovatel As Double) As Variant 'Funkce vracející též chybovou hodnotu musí mít návratový typ Variant.
     'MojeDěleníOšetřené = IIf(Jmenovatel = 0, CVErr(xlErrDiv0), Čitatel / Jmenovatel) 'Funkci IIf nelze použít, protože dle nápovědy vyhodnocuje vždy všechny 3 své části.
     If Jmenovatel = 0 Then MojeDěleníOšetřené = CVErr(xlErrDiv0) Else MojeDěleníOšetřené = Čitatel / Jmenovatel
     End Function

     Sub TestChyby()
     Dim a As Variant
     'If IsError(MojeDěleníNeošetřené(3, 0)) Then If CVErr(MojeDěleníNeošetřené(3, 0)) = CVErr(xlErrValue) Then MsgBox "Chyba!" 'Volání funkce s neošetřenou chybou skončí běhovou chybou při vykonávání funkce.
     'Výsledek funkce CVErr se musí porovnávat s chybou, jinak nastane chyba typu Type mismatch, proto se musí porovnávaná hodnota předem otestovat na chybu funkcí IsError.
     If IsError(Range("a1")) Then MsgBox "Nějaká chyba v buňce!"
     If IsError(Range("a1")) Then If Range("a1") = CVErr(xlErrValue) Then MsgBox "Chyba!" 'V buňce A1 je vzorec "=MojeDěleníNeošetřené(3;0)"
     If IsError(Range("a2")) Then If Range("a2") = CVErr(xlErrDiv0) Then MsgBox "Dělení nulou!" 'V buňce A2 je vzorec "=MojeDěleníOšetřené(3;0)"
     If IsError(MojeDěleníOšetřené(3, 0)) Then MsgBox "Nějaká chyba ve volané funkci!"
     If IsError(MojeDěleníOšetřené(3, 0)) Then If MojeDěleníOšetřené(3, 0) = CVErr(xlErrDiv0) Then MsgBox "Dělení nulou!"
     a = MojeDěleníOšetřené(3, 0) 'Lepší než předchozí řádek.
     If IsError(a) Then If a = CVErr(xlErrDiv0) Then MsgBox "Dělení nulou!"
     End Sub

     Sub TestChybyVBuňce()
     Dim HodnotaVBuňce As Variant
     HodnotaVBuňce = ActiveCell.Value
     If IsError(HodnotaVBuňce) Then
         Select Case HodnotaVBuňce
             Case CVErr(xlErrDiv0):  MsgBox "Dělení nulou!"
             Case CVErr(xlErrNA):    MsgBox "Nedostupná hodnota!" 'Například chyba funkce SVYHLEDAT
             Case CVErr(xlErrName):  MsgBox "Chyba ve jménu funkce nebo argumentu!"
             Case CVErr(xlErrNull):  MsgBox "Chybně zadaná oblast!" 'http://spreadsheets.about.com/od/formulatips/qt/null_error.htm
             Case CVErr(xlErrNum):   MsgBox "Výsledek přetekl!" 'http://www.databison.com/index.php/excel-na-ref-name-div0-null-value-num-error/
             Case CVErr(xlErrRef):   MsgBox "Oblast v odkazu byla vymazána!" 'http://spreadsheets.about.com/od/formulatips/qt/REF_error.htm
             Case CVErr(xlErrValue): MsgBox "Chybná hodnota!"
             Case Else:              MsgBox "Jiná chyba!"
         End Select
     Else: MsgBox "Ve vybrané buňce není chyba."
     End If
     End Sub

If Range("a1").Errors.Item(xlEvaluateToError).Value = True Then MsgBox "Chybová hodnota!"

     Sub TestováníChyb()
     Dim Zpráva As String
     Zpráva = ""
     If ActiveCell.Errors.Item(xlEvaluateToError).Value = True Then Zpráva = Zpráva & "The cell evaluates to an error value. - Chybová hodnota!" & vbCrLf
     If ActiveCell.Errors.Item(xlTextDate).Value = True Then Zpráva = Zpráva & "Date entered as text." & vbCrLf
     If ActiveCell.Errors.Item(xlNumberAsText).Value = True Then Zpráva = Zpráva & "Number entered as text." & vbCrLf
     If ActiveCell.Errors.Item(xlInconsistentFormula).Value = True Then Zpráva = Zpráva & "The cell contains an inconsistent formula for a region. - Nekonzistentní vzorec v oblasti!" & vbCrLf
     If ActiveCell.Errors.Item(xlOmittedCells).Value = True Then Zpráva = Zpráva & "Cells omitted. - Vzorec odkazuje na oblast, s níž sousedí další čísla." & vbCrLf
     If ActiveCell.Errors.Item(xlUnlockedFormulaCells).Value = True Then Zpráva = Zpráva & "Formula cells are unlocked. - Buňka se vzorcem je odemčená." & vbCrLf
     If ActiveCell.Errors.Item(xlEmptyCellReferences).Value = True Then Zpráva = Zpráva & "The cell contains a formula referring to empty cells. - Odkaz na prázdnou buňku!" & vbCrLf
     If ActiveCell.Errors.Item(xlListDataValidation).Value = True Then Zpráva = Zpráva & "Data in the list contains a validation error. - Ověřovaná buňka není v seznamu" & vbCrLf
     If ActiveCell.Errors.Item(xlInconsistentListFormula).Value = True Then Zpráva = Zpráva & "The cell contains an inconsistent formula for a list. - Nekonzistentní vzorec v seznamu!" & vbCrLf 'Od verze 2007.
     MsgBox Zpráva
     End Sub

Jak udělat chyby v buňce

Buňky v Excelu

Metoda Activate a Select

Worksheets(3).Range("a3") = 3

Adresování buněčných oblastí

Styl A1

     Range("a3").Select
     Range("a1:b2").Select
     Range("a1:a2,c1:c2,e1:e2").Select
     Range("a1,e2").Select
     Range("a1", "e2").Select
     Range("MojeData").Select 'Funguje za předpokladu, že byl definován název "MojeData" pro nějakou oblast.
     Range("a1", Selection).Select 'Projeví se, když je před tím vybrána jiná buňka než A1. Vlastnost Range zde má argument Selection, což je objekt.
     Range(Range("a1"), Range("e2")).Select 'Druhý příklad využití objektu Range jako argumentu vlastnosti Range

     [a3].Select
     [a1:b2].Select
     [a1:a2,c1:c2,e1:e2].Select
     [a1,e2].Select
     [MojeData].Select

Range("a3")

     I = 3
     Řetězec = "a" & I
     Range(Řetězec).Select

Range("a1", Selection)

Styl umožňující indexování čísly

Range("a1:b3").Cells(3).Activate

Range("a1:b3").Cells(2, 1).Activate

     Cells(2, 2).Select
     Cells(2, "B").Select

Range(Cells(1, 1), Cells(3, 2)).Select

Worksheets(3).Range(Cells(1, 1), Cells(3, 2)) = "vyplněno"

Worksheets(3).Range(Worksheets(3).Cells(1, 1), Worksheets(3).Cells(3, 2)) = "vyplněno"

     With Worksheets(3)
         .Range(.Cells(1, 1), .Cells(3, 2)) = "vyplněno"
     End With

Vlastnosti Rows a Columns

     Sub SumyŘádků()
     Dim Oblast As Range, Řádek As Range
     Set Oblast = Selection
     For Each Řádek In Oblast.Rows
         Řádek.Select
         MsgBox WorksheetFunction.Sum(Řádek)
     Next Řádek
     Oblast.Select
     End Sub

Vlastnost Offset objektu Range

     Selection.Offset(0, 0).Select
     Selection.Offset(1, 1).Select
     Selection.Offset(-1, -1).Select

Vlastnost Resize objektu Range

     Selection.Resize(5, 4).Select
     Selection.Resize(, 4).Select

     Const Názevoblasti As String = "MojeData"
     With Range(Názevoblasti)
         .Resize(.Rows.Count + 1).Name = Názevoblasti
     End With

Vlastnost CurrentRegion

Dialog Přejít na - Jinak...

Vlastnost End

Range("A1").End(xlDown).Select

Cells(Rows.Count, "A").End(xlUp).Select

     With Range("A1")
         Range(.Cells(1), .End(xlDown)).Select
     End With

Range("A1").CurrentRegion.Columns(1).Select

     With Worksheets(1).Range("A1")
         .Range(.Cells(1), .End(xlToRight)).Copy Destination:=Worksheets(2).Range("A1")
     End With

Doplnění součtů do tabulky

     Sub SumyŘádků()
     Set PočátečníVýběr = Selection 'Zapamatujeme si počáteční výběr.
     Selection.CurrentRegion.Select 'Vybereme celou tabulku
     With Selection 'Vybereme data v tabulce.
         Selection.Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1).Select
     End With
     Set Data = Selection 'Oblast s daty uložíme do proměnné Data.
     With Selection 'Vybereme horní buňku, do které se má doplnit součet.
         .Cells(1, .Columns.Count + 1).Select
     End With
     Selection.Formula = "=sum(" & Data.Rows(1).Address(0, 0) & ")"
     With Selection 'Zkopírujeme obsah horní buňky se součtem do buněk pod ní.
         .Copy Destination:=.Offset(1, 0).Resize(Data.Rows.Count - 1)
     End With
     PočátečníVýběr.Select 'Přesuneme se na počáteční výběr.
     End Sub

Vzorce

Prázdné buňky

ActiveSheet.UsedRange.Select

ActiveCell.SpecialCells(xlCellTypeLastCell).Select

3 způsoby, jak vybrat následující řádek pod tabulkou

Offset a Rows.Count

Selection.Offset(1, 0).Rows(Selection.Rows.Count).Select

End, Columns.Count, Offset a Resize

     PočetSloupků = Selection.Columns.Count
     Selection.End(xlDown).Select
     Selection.Offset(1, 0).Select
     Selection.Resize(1, PočetSloupků).Select

     With Selection
         .End(xlDown).Offset(1, 0).Resize(1, .Columns.Count).Select
     End With

IsEmpty, Columns.Count, Offset a Resize

     PočetSloupků = Selection.Columns.Count
     With Selection
         Do Until IsEmpty(ActiveCell)
             ActiveCell.Offset(1, 0).Activate
         Loop
     End With
     Selection.Resize(1, PočetSloupků).Select

Přenos hodnot mezi datovou strukturou pole a oblastí buněk

Sub PromazáníTabulky()
Const Vymazat As String = "Vymazat"
Const PořadíSloupkuVymazat As Integer = 4
Dim Řádek As Range
Dim AdresaPrvníBuňky As String
Dim PočetDatovýchŘádků As Integer
Dim PočetVymazanýchŘádků As Integer
Dim Hotovo As Boolean
Selection.CurrentRegion.Select
PočetDatovýchŘádků = Selection.Rows.Count - 1
AdresaPrvníBuňky = Selection.Cells(1).Address
PočetVymazanýchŘádků = 0
Hotovo = False
For Each Řádek In Selection.Rows
    Řádek.Select
    If Selection.Cells(PořadíSloupkuVymazat) = Vymazat Then
        Selection.ClearContents
        PočetVymazanýchŘádků = PočetVymazanýchŘádků + 1
    End If
Next Řádek
If PočetVymazanýchŘádků > 0 And PočetVymazanýchŘádků < PočetDatovýchŘádků Then
    Do
        Selection.CurrentRegion.Select
        Selection.Cut
        Selection.End(xlUp).Select
        Hotovo = Selection.Address = AdresaPrvníBuňky
        Selection.Offset(1, 0).Select
        ActiveSheet.Paste
    Loop Until Hotovo
    Selection.Cut
    Selection.Offset(-1, 0).Select
    ActiveSheet.Paste
End If
End Sub

Motivací pro makra, která přenášejí hodnoty mezi datovou strukturou pole a oblastí buněk, je zrychlení zpracování. Odstraňování řádků z velmi velkých tabulek by mohlo mít měřitelný rozdíl v rychlosti maker výše uvedeného typu oproti makrům níže uvedeného typu.
Makro příkazem Data = Selection uloží do proměnné Data všechny datové řádky tabulky (tedy vše kromě záhlaví). Tímto příkazem se z proměnné Data stane dvourozměrné pole s hodnotami z tabulky. První rozměr reprezentuje počet řádků a druhý rozměr reprezentuje počet sloupků, takže je možné přistupovat k jednotlivým hodnotám z buněk tabulky pomocí indexů pole rovných číslům řádků a sloupků v původní tabulce. Když jsou hodnoty v oblasti takto uloženy do proměnné typu Variant, indexy vzniklého pole jsou vždy číslovány od jedné, nikoli od nuly, bez ohledu na příkaz Option Base v deklarační části modulu. Vzniklé pole má také vždy dva rozměry, i když by oblast měla jen jeden řádek nebo sloupek.
Příkaz Data = Selection je jiný než příkaz Set Data = Selection, který by do proměnné Data uložil oblast s daty tabulky jako objekt.
Na začátku jsou v makru deklarovány 2 proměnné typu pole PromazanáData a TransponovanáData bez udání rozměrů, protože není předem známo, jak velká tabulka bude makrem zpracovávána. Vzhledem k tomu, že se do nich bude přenášet obsah buněk, musí být datovým typem v těchto polích Variant.
Příkazem ReDim se upraví velikost polí podle velikosti tabulky. Přitom se využívá pro zjištění počtu řádků a sloupků tabulky funkce UBound.
Ve For cyklu se do pole PromazanáData uloží data z těch rádků tabulky, které nemají být vymazány, a zjistí se počet takových řádků.
V poli PromazanáData jsou po této operaci na prvních řádcích řádky, které mají v tabulce zůstat, a na ostatních jsou prázdné hodnoty. Toto pole se proto může vložit na původní místo datové části tabulky.
Kdybychom potřebovali pole PromazanáData původně dimenzované na celou tabulku zkrátit na počet zachovaných řádků, budeme potřebovat řádky kódu, které jsou zakomentovány. Při této operaci je nutné pole PromazanáData transponovat, protože funkce ReDim může měnit pouze počet sloupků (poslední rozměr) pole, pokud je volána s klíčovým slovem Preserve, se kterým musí být volána, aby byla v poli zachována data. Potom se změní počet řádků původního pole PromazanáData a to bez klíčového slova Preserve, aby bylo možné změnit i počet řádků (nejen počet sloupků), a tím se vynulují data. Data se do pole PromazanáData zkopírují jako výsledek transponování zkráceného pole.

Sub PromazáníTabulkyVPoli()
Const Vymazat As String = "Vymazat"
Const PořadíSloupkuVymazat As Integer = 4
Dim AdresaPrvníBuňky As String
'Dim PočetDatovýchŘádků As Integer
'Dim PočetVymazanýchŘádků As Integer
Dim Data As Variant
Dim PromazanáData() As Variant
'Dim TransponovanáData() As Variant
Dim i As Integer, j As Integer, k As Integer
Selection.CurrentRegion.Select
AdresaPrvníBuňky = Selection.Cells(1).Address
Selection.Offset(1, 0).Select
Selection.Resize(Selection.Rows.Count - 1).Select
'PočetDatovýchŘádků = Selection.Rows.Count
Data = Selection
ReDim PromazanáData(1 To UBound(Data, 1), 1 To UBound(Data, 2))
'ReDim TransponovanáData(1 To UBound(Data, 2), 1 To UBound(Data, 1))
k = 0
For i = 1 To UBound(Data, 1)
    If Data(i, PořadíSloupkuVymazat) <> Vymazat Then
        k = k + 1
        For j = 1 To UBound(Data, 2)
            PromazanáData(k, j) = Data(i, j)
        Next j
    End If
Next i
'PočetVymazanýchŘádků = PočetDatovýchŘádků - k
'If PočetVymazanýchŘádků > 0 And PočetVymazanýchŘádků < PočetDatovýchŘádků Then
'    TransponovanáData = WorksheetFunction.Transpose(PromazanáData)
'    ReDim Preserve TransponovanáData(1 To UBound(Data, 2), 1 To k)
'    ReDim PromazanáData(1 To k, 1 To UBound(Data, 2))
'    PromazanáData = WorksheetFunction.Transpose(TransponovanáData)
'End If
Selection.ClearContents
'Range(AdresaPrvníBuňky).Offset(1, 0).Select
'Selection.Resize(UBound(PromazanáData, 1), UBound(PromazanáData, 2)).Select
Selection = PromazanáData
End Sub

Zrychlení vykonávání maker

Metoda Select a Activate

ScreenUpdating

DisplayAlerts

     Sub VymažList()
     ThisWorkbook.Worksheets.Add , Worksheets(Worksheets.Count), 10 'Přidání 10 listů za poslední list
     Application.DisplayAlerts = False
     Worksheets(Worksheets.Count).Delete 'Odstranění posledního listu
     Application.DisplayAlerts = True
     End Sub

Calculation

Evaluate

ExecuteExcel4Macro

a = ExecuteExcel4Macro("'C:\Data\[Moje data.xls]List1'!R1C1")

• Na buňky se lze odkazovat pouze ve stylu R1C1
• Na listy se lze odkazovat pouze pomocí jejich vlastnosti Name, nikoli například Worksheets(1), takže jejich jména musí být předem známa.

     Sub NapišHodnoty()
     Dim i As Integer, j As Integer
     Dim Počátek As Single, Konec As Single
     Počátek = Timer
     For i = 1 To 50
         For j = 1 To 50
             Cells(i, j) = ExecuteExcel4Macro("'C:\Data\[Moje data.xls]List1'!R" & i & "C" & j)
             If Cells(i, j) = 0 Then Cells(i, j) = ""
                 'Prázdné buňky (ty co nemají data) vrátí metoda ExecuteExcel4Macro jako nulu.
                 'Takto tomu lze zabránit, ale potom budou datové buňky s hodnotou nula prázdné.
                 'Buňky se vzorcem vrátí metoda ExecuteExcel4Macro jako výslednou hodnotu.
         Next j
     Next i
     Konec = Timer
     MsgBox Konec - Počátek, , "Trvání v sekundách"
     End Sub

a = ExecuteExcel4Macro("'C:\Data\Moje data.xls'!MojeOblast")

='C:\Data\Moje data.xls'!MojeOblast

     Sub NapišHodnotyLink()
     Dim i As Integer, j As Integer
     Dim Počátek As Single, Konec As Single
     Počátek = Timer
     For i = 1 To 50
         For j = 1 To 50
             Cells(i, j).FormulaR1C1 = "='C:\Data\[Moje data.xls]List1'!RC"
             If Cells(i, j) = 0 Then Cells(i, j) = ""
             Cells(i, j).FormulaR1C1 = Cells(i, j).Value 'Zruší se propojení.
         Next j
     Next i
     Konec = Timer
     MsgBox Konec - Počátek, , "Trvání v sekundách"
     End Sub

Další tipy na zrychlení vzorců a maker