Práce s oblastmi, buňkami a jejich metody

Teorie Objektů

Reference na objekty v kontextu VBA (Visual Basic for Applications) v Excelu jsou klíčové pro manipulaci s buňkami, rozsahy, listy a dalšími částmi pracovního sešitu.

Hierarchie Objektů:

Reference Vizuálně:

Detailní Hierarchie objektů ve worksheetu:

Workbooks, ThisWorkbook, ActiveWorkbook

Slouží k práci se sešity (celým Excelem).

Vysvětlení

• Workbooks – Kolekce pracovních sešitů v Excelu. Může obsahovat otevřené i uzavřené sešity.

Workbooks(„Excel.xlsx“).Sheets(1).Range(„F1“).Value = „Hello World!“

• ThisWorkbook – Objekt, který odkazuje na pracovní sešit, ve kterém je právě spuštěný VBA kód.

ThisWorkbook.Sheets(1).Range(„F1“).Value = „Hello World!“

• ActiveWorkbook – Odkazuje na aktuálně aktivní pracovní sešit, tedy ten, který je právě v popředí.

ActiveWorkbook.Sheets(1).Range(„F1“).Value = „Hello World!“

Metody

Workbooks.Open „C:\Cesta\KSešitu.xlsx“ – Otevření existujícího sešitu, kde je nutné mít specifikovanou cestu ve stringu.

ThisWorkbook.Save – Uložení tohoto sešitu (pouze uložení, ne uložení jako).

ThisWorkbook.SaveAs „C:\Cílová_cesta\Název_sešitu.xlsx“ – Uložení jako.

Workbooks(„MujSešit.xlsx“).Close – Zavření specifikovaného sešitu

Workbooks.Add – Otevření čistého Excelu.

Workbooks(„MujSešit.xlsx“).Activate – Aktivace Excelu, aby byl v popředí.

Worksheets, Sheets, ActiveSheet, Charts

Slouží k práci s listy v Excelu (konkrétním Workbooku)

Vysvětlení

• Worksheets – Kolekce listů v pracovním sešitu Excelu. Tato kolekce umožňuje přístup k jednotlivým listům pomocí jejich názvů nebo indexů

Worksheets(1).Range(„F1“).Value = „Hello World!“

Worksheets(„Ahoj“).Range(„F1“).Value = „Hello World!“

• Sheets – Obecná kolekce, která zahrnuje různé typy listů, včetně pracovních listů („Worksheet“), grafů („Chart“).

Sheets(1).Range(„F1“).Value = „Hello World!“

Sheets(„Ahoj“).Range(„F1“).Value = „Hello World!“

• Charts – Kolekce grafů v pracovním sešitu Excelu.
• ActiveSheet – Odkazuje na aktuálně aktivní list v pracovním sešitu. Tento list může být typu „Worksheet“ nebo „Chart“.

Activesheet.Range(„F1“).Value = „Hello World!“

• Kódový název listu – Alternativní název, který je přiřazen jednotlivým listům a umožňuje snadněji je identifikovat ve VBA kódu. Kódový název listu slouží k referencování konkrétního listu v kódu bez použití jeho viditelného názvu. Je k nalezení v Projects nalevo vedle názvu listu.

List1.Range(„F1“).Value = „Hello World!“

Metody

Worksheets.Add – Přidání nového listu do Excelu.

Worksheets(„List1“).Activate – Aktivace specifického listu dle názvu.

Worksheets(„List1“).Delete – Smazání listu dle názvu.

Worksheets(„List1“).Copy After:=Worksheets(„List2“) – Zkopírování listu na nové místo v Excelu.

Worksheets(„List1“).Move Before:=Worksheets(„List2“) – Přesunutí listu v Excelu.

Worksheets(„List1″).Protect Password:=“heslo“ – Zadání hesla listu.

Worksheets(„List1″).Unprotect Password:=“heslo“ – Odemknutí listu s příslušným heslem.

Worksheets(„List1“).Calculate – Přepočítání vzorců na listu.

Worksheets(„List1“).Name = „NovýNázev“ – Nastavení nového názvu listu.

Range, Cells ActiveCell

Range slouží k práci s danou buňkou nebo oblastí buněk. Aktivní buňka pracuje s právě aktivní vybranou buňkou v Excelu. Cells slouží k práci s danou buňkou, případně lze použít vhodně při cyklech.

Vysvětlení

• Range – Objekt představující buňku nebo skupinu buněk v listu Excelu. Je vhodný pro rozsah buněk, méně pro cykly.

Range(„F1“).Value = „Hello World!“

Range(„F1:H13“).Value = „Hello World!“

• Cells – Objekt, který představuje jednu konkrétní buňku v listu Excelu. Je vhodný pro cykly v rámci VBA. První atribut je pozice řádku, druhý atribut pozice sloupce.

Cells(1, 3).Value = „Hello World!“ ß První řádek, třetí sloupec

• ActiveCell – Objekt, který odkazuje na aktuálně aktivní buňku v listu Excelu.

Activecell.Value = „Hello World!“

• SpecialCells – Metoda objektu Range, která umožňuje identifikovat a zpracovat speciální typy buněk v daném rozsahu (filtrované řádky).

Metody

Range(„A1:B2“).Select – Vybrání buňky nebo oblasti buněk.

Range(„A1:B2“).Copy Destination:=Range(„C1“) – Kopírování buňky nebo oblasti buněk.

Range(„C1“).PasteSpecial Paste:=xlPasteValues – Vložení obsahu s volitelnými specifikacemi, např. pouze hodnoty jako zde.

Range(„A1:B2“).Clear – Smazání obsahu.

Range(„A1:B2“).Delete Shift:=xlShiftUp – Odstranění buněk.

Range(„A1:B10“).Sort Key1:=Range(„A1“), Order1:=xlAscending – Seřazení dat podle specifikovaných kritérií.

Set FoundCell = Range(„A1:A10“).Find(What:=“Hodnota“) – Hledání konkrétní hodnoty v oblasti.

Stejné možnosti lze dělat i pomocí metody Cells. Další možnosti práce s Cells:

Cells(1, 1).Resize(2, 3).Value = „Test“ – Změna velikosti oblasti buněk. Zde oblast 2×3 od buňky A1.

Cells(1, 1).Offset(1, 1).Value = „Posunuto“ – Posunutí odkazu na buňku o specifikovaný počet řádků a sloupců.

Formula

Definice

Pomocí příkazu „Formula“ je možné ve VBA použít jakoukoliv Excel funkci a tato funkce se propíše i do stanované buňky. Argument „Formula“ se píše za buňku specifikovanou metodou „Range“, „Cells“ popř. „ActiveCel“ za rovnítko. Je nutné tuto funkci specifikovat do textového řetězce a přitom vždy v textovém řetězci začínat rovnítkem. Funkce ve VBA musí být napsána v anglickém jazyce. Pokud se jedná o funkci s více argumenty, jsou ve VBA vždy odděleny čárkou a ne středníkem. Do buňky je funkce zapsána v jazyce, ve kterém je Excel nastaven. Pokud je např. Excel nastaven v češtině, do VBA se funkce napíše v angličtině a v buňce bude funkce viděna v češtině. Zároveň lze propojit více funkcí najednou.

Použití

Range(„B1“).Formula = „=SUM(A1:A10)“ – Spočítá sumu hodnot v buňkách od A1 do A10. Výsledek bude v buňce B1 přímo s funkcí:

Range(„B1“).Formula = „=LEFT(A1, 3)“ – Extrahuje 3 znaky zleva z buňky A1 a výstup bude zapsán do buňky B1 jako funkce. Argumenty jsou odděleny ve funkci vždy čárkou bez ohledu na nastavení Excelu.

Range(„B1:B10“).Formula = „=LEFT(A1, 3)“ – Extrahuje 3 znaky zleva a výsledek je zapsán do buněk B1 až B10. Ačkoliv se může zdát, že do buňky B1:B10 bude zapsána vždy stejná hodnota extrahovaná z buňky A1, není tomu tak. Funkce funguje dynamicky, tedy do buňky B1 se zapíše extrakce textu z buňky A1, do buňky B2 extrakce z buňky A2 atd., ačkoliv to není ve funkce v textovém řetězci stanoveno. Pokud má být použita stále stejná buňka „A1“ pro funkci Left, je nutné buňku zamknout jako s klasickou prací v Excelu.

Range(„B1“).Formula = „=LEFT(A1, FIND(„“_““, A1)-1)“ – Použití více funkcí najednou. Bude extrahováno zleva tolik znaků, kolik je před podtržítkem, který je zjištěn Excelovou funkcí „Find“. Jelikož musí být textový řetězec zadán do funkce „Find“, je nutné ho zadat do dvojitých uvozovek, aby bylo VBA jasně stanoveno, že se jedná o textový řetězec v rámci funkce a nevychází se z funkce stanovené ve VBA.

Práce s daty

V této části je pracováno s následujícím daty:

Vkládání dat

Range(„A1“).Value = „Text nebo hodnota“ – Vkládání hodnoty do jedné buňky.

Range(„A1:B2“).Value = „Hromadná hodnota“ – Vkládání hodnoty do oblasti buněk.

For i = 1 To 10

Cells(i, 1).Value = i – Vkládání textu do buněk pomocí cyklu.

Next i

Mazání dat

Range(„A1:B2“).ClearContents – Vymaže pouze obsah v buňce.

Range(„A1:B2“).Clear – Vymaže obsah a formátování v buňce.

Range(„A1:A5“).Delete Shift:=xlShiftUp – Odstranění buněk a posunutí dalších nahoru.

Kopírování dat

Range(„A1“).Copy Destination:=Range(„B1“) – Kopírování na konkrétní místo hodnoty z jedné buňky.

Kopírování pouze hodnoty bez formátování:

Range(„A1“).Copy

Range(„B1“).PasteSpecial Paste:=xlPasteValues

Range(„A1:A10“).Copy Destination:=Range(„B1:B10“) – Kopírování oblasti.

Filtrování (Autofilter)

Definice

Vyfiltruje hodnoty v sloupci na základě podmínky.

Praktické vysvětlení

Filtr zvládne v jednom sloupci vyfiltrovat maximálně 2 textové hodnoty. V rámci čísel zvládá filtrovat konkrétní číslo, rozsah, datumy, rozsah datumů a další. Pokud je potřebné vyfiltrovat více než 2 textové hodnoty, je nutné použít funkci array.

Argumenty

Range.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

Field (Long, povinný) – Číslo sloupce v oblasti, která má být filtrována.

Criteria1 (Variant, povinný) – Hodnota nebo kritérium, podle kterého má být filtrováno. Může obsahovat =, <>, <, >, <=, >=.

Operator (xlAutoFilterOperator, volitelný) – Určuje logický operátor, který se použije, pokud jsou specifikována dvě kritéria (Criteria1 a Criteria2).

Criteria2 (Variant, volitelný) – Druhá hodnota nebo kritérium, podle které má být filtrováno.

VisibleDropDown (bool, volitelný) – Určuje, zda bude v záhlaví sloupce viditelný rozbalovací seznam filtru.

Použití

**Range(“A1:E1”).**Autofilter – Pokud je filtr vypnutý, zapne se. Pokud je zapnutý, vypne se.

Range(„A1:E1“).Autofilter, Field:=2, Criteria1:=“Praha“ – Filtrování v 2. sloupci textu „Praha“.

Range(„A1:E1“).Autofilter, Field:=2, Criteria1:=“Praha“, operator:=xlOr,Criteria2:=“Ostrava“ – Filtrování více hodnot v jednom sloupci (alespoň jedna z podmínek musí být splněna).

Range(„A1:E1″).AutoFilter Field:=5, Criteria1:=“>=1000″, operator:=xlAnd, Criteria2:=“<=3000″ – Filtrování více hodnot v jednom sloupci (obě podmínky musí být splněný zároveň.

Range(„A1:E1“).Autofilter, Field:=2, Criteria1:=Array(“Praha”, “Ostrava”, “Brno”), operator:= xlFilterValues – Filtrování více jak 2 hodnot v jednom sloupci.

Filtrování ve více sloupcích najednou (Je nutné použít autofilter 2x):

Range(„A1:E1“).Autofilter, Field:=2, Criteria1:=“Praha“ Range(„A1:E1“).Autofilter, Field:=1, Criteria1:=“>20000“

Range(„A1:B1“).AutoFilter Field:=2, Criteria1:=RGB(0, 0, 0), Operator:=xlFilterCellColor – Filtr barvy.

Range(„A1:A20″).AutoFilter Field:=2, Criteria1:=“O*“ – Filtrování podle částečného textu (Za písmenem „O“ může být cokoliv).

ActiveSheet.AutoFilterMode = False – Vypnutí filtru.

Řazení

Definice

Řadí data dle stanoveného sloupce nebo více sloupců.

Praktické vysvětlení

Pokud se mají data řadit dle více sloupců. Prvně je nutné zadat sloupec, který má být řazen jako první a poté další sloupec, na základě kterého se má řadit. Řazení se provádí pomocí klíče.

Argumenty

Range.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, DataOption1, DataOption2, DataOption3)

Key1 (Range, volitelný) – První klíč (buňka nebo oblast), podle které se mají data řadit.

Order1 (XlSortOrder, volitelný) – Směr řazení, zdali vzestupně (xlAscending) nebo sestupně (xlDescending).

Key2 (Range, volitelný) – Druhý klíč.

Order2 (XlSortOrder, volitelný) – Směr řazení druhého klíče.

Key3 (Range, volitelný) – Třetí klíč.

Order3 (XlSortOrder, volitelný) – Směr řazení třetího klíče.

Header (volitelný) – Zdali má oblast záhlaví (xlYes) nebo nikoliv (xlNo).

OrderCustom (Long, volitelný) – Určení vlastního pořadí řazení.

MatchCase (Bool, volitelný) – Zdali se mají rozlišovat velké a malá písmena.

Orientation (volitelný) – Směr řazení dat (jestli po řádcích nebo po sloupcích).

DataOption1, DataOption2, DataOption3 (volitelný) – Určuje, jak Excel zpracovává data během řazení, např. pokud je nastaveno xlSortTextAsNumbers , čísla jako text jsou považována za skutečná čísla.

Použití

Range(„A1:E12“).Sort Key1:=Range(„A1“), Header:=xlYes – Řazení dle prvního sloupce, data mají záhlaví.

Range(„A1:E12“).Sort Key1:=Range(„A1“), order1:=xlDescending, Header:=xlYes – Řazení sestupně dle prvního sloupce.

Range(„A1:E12“).Sort Key1:=Range(„C1“), order1:=xlAscending, key2:=Range(„E1“), order2:=xlDescending, Header:=xlYes – Řazení dle více sloupců. Nejprve dle sloupce „C“ vzestupně a následně sloupce „E“ sestupně

Řazení dle více sloupců za pomocí použití with:

With Sheets(„Data“).Sort .SetRange Range(„A1:E12“) – nastavení oblasti .Header = xlYes – argument, zdali se zde nachází záhlaví .SortFields.Add Key:=Range(„A1“), Order:=xlAscending – 1. řazení .SortFields.Add Key:=Range(„B1“), Order:=xlDescending – 2. řazení .Apply – Aplikace řazení** End With**