✅ Práce s listy pomocí Excelu VBA (vysvětleno na příkladech)

Obsah

Rozdíl mezi pracovními listy a listy ve VBA
Odkazování na pracovní list ve VBA
Přidání pracovního listu
Odstranění listu
Přejmenování pracovních listů
Přiřazení objektu listu k proměnné
Skrýt listy pomocí VBA (skryté + velmi skryté)
Skrýt listy na základě textu v něm
Třídění pracovních listů v abecedním pořadí
Chraňte/odemkněte všechny listy najednou
Vytvoření obsahu všech pracovních listů (s hypertextovými odkazy)
Kam vložit kód VBA

Kromě buněk a rozsahů je práce s listy další oblastí, o které byste měli vědět, jak efektivně používat VBA v Excelu.

Stejně jako jakýkoli objekt ve VBA mají listy různé vlastnosti a metody, které s ním souvisejí a které můžete použít při automatizaci práce s VBA v aplikaci Excel.

V tomto tutoriálu se podrobně věnuji „pracovním listům“ a ukážu vám také několik praktických příkladů.

Začněme tedy.

Všechny kódy, které zmiňuji v tomto tutoriálu, je třeba umístit do editoru VB. Chcete -li zjistit, jak funguje, přejděte do části „Kam umístit kód VBA“.

Pokud máte zájem naučit se VBA jednoduchým způsobem, podívejte se na můj Online školení Excel VBA.

Rozdíl mezi pracovními listy a listy ve VBA

Ve VBA máte dvě kolekce, které mohou být občas trochu matoucí.

V sešitu můžete mít pracovní listy a také grafy. Níže uvedený příklad má tři pracovní listy a jeden list grafu.

V aplikaci Excel VBA:

Kolekce „Pracovní listy“ by odkazovala na shromažďování všech objektů pracovního listu v sešitu. Ve výše uvedeném příkladu by se kolekce pracovních listů skládala ze tří pracovních listů.
Sbírka „Listy“ by se vztahovala na všechny pracovní listy i listy grafů v sešitu. Ve výše uvedeném příkladu by to mělo čtyři prvky - 3 pracovní listy + 1 list grafu.

Pokud máte sešit, který obsahuje pouze pracovní listy a žádné grafy, pak jsou kolekce „Pracovní listy“ a „Listy“ stejné.

Když ale máte jeden nebo více listů s grafy, kolekce „Listy“ by byla větší než kolekce „Pracovní listy“

Listy = Pracovní listy + Tabulky grafů

Nyní s tímto rozlišením doporučuji být při psaní kódu VBA co nejkonkrétnější.

Pokud tedy musíte odkazovat pouze na pracovní listy, použijte kolekci „Pracovní listy“ a pokud musíte odkazovat na všechny listy (včetně listů grafů), použijte kolekci „Listy“.

V tomto tutoriálu budu používat pouze kolekci „Pracovní listy“.

Odkazování na pracovní list ve VBA

Existuje mnoho různých způsobů, jak můžete odkazovat na list ve VBA.

Pochopení toho, jak odkazovat na listy, vám pomůže napsat lepší kód, zvláště když ve svém kódu VBA používáte smyčky.

Použití názvu listu

Nejjednodušší způsob, jak se na list podívat, je použít jeho název.

Předpokládejme například, že máte sešit se třemi pracovními listy - list 1, list 2, list 3.

A chcete aktivovat list 2.

Můžete to provést pomocí následujícího kódu: Sub ActivateSheet () Listy ("Sheet2"). Aktivovat End Sub

Výše uvedený kód požaduje, aby VBA odkazoval na list2 v kolekci pracovních listů a aktivoval jej.

Protože používáme přesný název listu, můžete zde také použít kolekci Listů. Takže níže uvedený kód by také udělal totéž.

Sub ActivateSheet () Sheets ("Sheet2"). Activate End Sub

Pomocí indexového čísla

Při používání názvu listu je snadný způsob, jak odkazovat na list, někdy možná neznáte přesný název listu.

Pokud například používáte kód VBA k přidání nového listu do sešitu a nevíte, kolik listů již existuje, neznáte název nového listu.

V takovém případě můžete použít indexové číslo listů.

Předpokládejme, že máte v sešitu následující listy:

Níže uvedený kód by aktivoval Sheet2:

Pracovní listy Sub ActivateSheet () (2). Aktivovat End Sub

Všimli jsme si, že jsme použili index číslo 2 v Pracovní listy (2). To by odkazovalo na druhý objekt ve sbírce pracovních listů.

Co se stane, když jako indexové číslo použijete 3?

Vybírá List3.

Pokud vás zajímá, proč vybrala Sheet3, protože je to jednoznačně čtvrtý objekt.

K tomu dochází, protože list grafu není součástí kolekce pracovních listů.

Když tedy použijeme čísla indexů v kolekci Listy, bude odkazovat pouze na listy v sešitu (a ignorovat listy grafů).

Naopak, pokud používáte Tabulky, Tabulky (1) by odkazovaly na Listy1, Listy (2) by odkazovaly na List2, Listy (3) by odkazovaly na Graf1 a Listy (4) by odkazovaly na List3.

Tato technika použití indexového čísla je užitečná, když chcete procházet všechny listy v sešitu. Můžete spočítat počet pracovních listů a poté je procházet pomocí tohoto počtu (uvidíme, jak to udělat později v tomto kurzu).

Poznámka: Indexové číslo jde zleva doprava. Pokud tedy posunete List2 doleva od Listu1, pak by Pracovní listy (1) odkazovaly na List2.

Pomocí kódového názvu listu

Jednou z nevýhod používání názvu listu (jak jsme viděli v sekci výše) je, že jej uživatel může změnit.

A pokud byl změněn název listu, váš kód nebude fungovat, dokud nezměníte název listu také v kódu VBA.

K řešení tohoto problému můžete použít kódový název listu (namísto běžného názvu, který jsme dosud používali). Kódové jméno lze přiřadit v editoru VB a nezmění se, když změníte název listu z oblasti listu.

Chcete -li svému listu dát kódový název, postupujte podle následujících kroků:

Klikněte na kartu Vývojář.
Klikněte na tlačítko Visual Basic. Tím se otevře editor VB.
V nabídce klikněte na možnost Zobrazit a poté klikněte na Okno projektu. Tím se podokno Vlastnosti zviditelní. Pokud je podokno Vlastnosti již viditelné, tento krok přeskočte.
Klikněte na název listu v průzkumníku projektu, který chcete přejmenovat.
V podokně Vlastnosti změňte název v poli před (Název). V názvu nemůžete mít mezery.

Výše uvedené kroky by změnily název vašeho listu v backendu VBA. V zobrazení listu aplikace Excel můžete list pojmenovat, jak chcete, ale v backendu bude reagovat na názvy - název listu i kódové jméno.

Na výše uvedeném obrázku je název listu „SheetName“ a kódové jméno je „CodeName“. I když změníte název listu na listu, kódový název zůstane stejný.

Nyní můžete použít buď kolekci pracovních listů k odkazu na list, nebo použít kódové jméno.

Například oba řádky aktivují list.

Pracovní listy („Název listu“). Aktivujte CodeName.Activate

Rozdíl v těchto dvou je, že pokud změníte název listu, první by nefungoval. Ale druhý řádek by fungoval i se změněným názvem. Druhý řádek (pomocí CodeName) je také kratší a jednodušší na použití.

Odkaz na list v jiném sešitu

Pokud chcete odkazovat na list v jiném sešitu, musí být tento sešit otevřený při spuštění kódu a je třeba zadat název sešitu a listu, na který chcete odkazovat.

Pokud například máte sešit s názvem Příklady a chcete List1 aktivovat v příkladu sešitu, musíte použít níže uvedený kód:

Sub SheetActivate () Sešity ("examples.xlsx"). Pracovní listy ("Sheet1"). Aktivovat End Sub

Pokud byl sešit uložen, musíte spolu s příponou použít název souboru. Pokud si nejste jisti, jaké jméno použít, požádejte o pomoc aplikaci Project Explorer.

V případě, že sešit nebyl uložen, nemusíte používat příponu souboru.

Přidání pracovního listu

Níže uvedený kód by přidal list (jako první list - tj. Jako list úplně vlevo na kartě listu).

Sub pracovní listy AddSheet (). Přidat End Sub

Trvá výchozí název List2 (nebo jakékoli jiné číslo podle toho, kolik listů již existuje).

Pokud chcete, aby byl list přidán před konkrétní list (řekněme List2), můžete použít níže uvedený kód.

Sub AddSheet () Worksheets.Add Before: = Worksheets ("Sheet2") End Sub

Výše uvedený kód říká VBA, aby přidala list, a poté pomocí příkazu „Před“ specifikuje list, před který má být vložen nový list.

Podobně můžete také přidat list za list (řekněme List2) pomocí níže uvedeného kódu:

Sub AddSheet () Worksheets.Add After: = Worksheets ("Sheet2") End Sub

Pokud chcete, aby byl nový list přidán na konec listů, musíte nejprve vědět, kolik listů existuje. Následující kód nejprve spočítá počet listů a přidá nový list za poslední list (na který odkazujeme pomocí indexového čísla).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Worksheets.Count Worksheets.Add After: = Worksheets (SheetCount) End Sub

Odstranění listu

Níže uvedený kód by odstranil aktivní list ze sešitu.

Sub DeleteSheet () ActiveSheet.Delete End Sub

Výše uvedený kód by před odstraněním listu zobrazil varovnou výzvu.

Pokud nechcete, aby se varovná výzva zobrazovala, použijte následující kód:

Sub DeleteSheet () Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Když je Application.DisplayAlerts nastaven na False, nezobrazí se vám varovná výzva. Pokud ho používáte, nezapomeňte jej na konci kódu nastavit zpět na True.

Pamatujte, že toto smazání nelze vrátit zpět, použijte tedy výše uvedený kód, až si budete naprosto jisti.

Pokud chcete odstranit konkrétní list, můžete to provést pomocí následujícího kódu:

Dílčí listy listu DeleteSheet () („List2“). Odstranit konec Sub

K odstranění listu můžete také použít kódový název.

Sub Delete Sheet () Sheet 5. Vymazat End Sub

Přejmenování pracovních listů

Můžete změnit vlastnost name listu a změnit jeho název.

Následující kód změní název List1 na „Souhrn“.

Dílčí přejmenovací list () Pracovní listy ("List1"). Název = "Souhrn" Konec Pod

Můžete to zkombinovat s metodou přidání listu, abyste měli sadu listů s konkrétními názvy.

Pokud například chcete vložit čtyři listy s názvem 2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 a2021-2022 Q4, můžete použít níže uvedený kód.

Sub RenameSheet () Dim Countsheets As Integer Countsheets = Worksheets.Count For i = 1 to 4 worksheets.Add after: = Worksheets (Countsheets + i - 1) worksheets (Countsheets + i) .Name = "2018 Q" & i Next i End Sub

Ve výše uvedeném kódu nejprve spočítáme počet listů a poté pomocí smyčky For Next vložíme nové listy na konec. Když je list přidán, kód jej také přejmenuje.

Přiřazení objektu listu k proměnné

Při práci s listy můžete přiřadit list k proměnné objektu a poté použít proměnnou místo odkazů na list.

Pokud například chcete ke všem listům přidat předponu roku, můžete místo počítání listů a spouštění cyklu tolikrát použít proměnnou objektu.

Zde je kód, který přidá 2021-2022 jako předponu ke všem názvům listu.

Sub RenameSheet () Dim Ws as worksheet for each Ws in worksheets Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

Výše uvedený kód deklaruje jako typ listu proměnnou Ws (pomocí řádku „Dim Ws As Worksheet“).

Nyní nemusíme počítat počet listů, které jimi projdeme. Místo toho můžeme použít smyčku „Pro každé W v pracovních listech“. To nám umožní projít všechny listy ve sbírce pracovních listů. Nezáleží na tom, zda jsou 2 listy nebo 20 listů.

Zatímco výše uvedený kód nám umožňuje procházet všechny listy, můžete také přiřadit konkrétní list proměnné.

V níže uvedeném kódu přiřadíme proměnnou Ws k Sheet2 a použijeme ji pro přístup ke všem vlastnostem Sheet2.

Sub RenameSheet () Dim Ws As Worksheet Set Ws = Worksheets ("Sheet2") Ws.Name = "Summary" Ws.Protect End Sub

Jakmile nastavíte odkaz na list na proměnnou objektu (pomocí příkazu SET), lze tento objekt použít místo odkazu na list. To může být užitečné, pokud máte dlouhý komplikovaný kód a chcete změnit odkaz. Namísto změny všude můžete jednoduše provést změnu v příkazu SET.

Všimněte si, že kód deklaruje objekt Ws jako proměnnou typu listu (pomocí řádku Dim Ws jako list).

Skrýt listy pomocí VBA (skryté + velmi skryté)

Skrytí a odkrytí listů v aplikaci Excel je jednoduchý úkol.

Můžete skrýt list a uživatel by ho neviděl, když otevře sešit. List však mohou snadno zobrazit kliknutím pravým tlačítkem na libovolnou kartu listu.

Ale co když nechcete, aby mohli zobrazit pracovní listy.

Můžete to udělat pomocí VBA.

Níže uvedený kód by skrýval všechny listy v sešitu (kromě aktivního listu), takže jej nemůžete odkrýt kliknutím pravým tlačítkem na název listu.

Sub HideAllExcetActiveSheet () Dim Ws jako pracovní list pro každý Ws v ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden Next Ws End Sub

Ve výše uvedeném kódu je vlastnost Ws.Visible změněna na xlSheetVeryHidden.

Když je vlastnost Visible nastavena na xlSheetVisible, list je viditelný v oblasti listu (jako karty listu).
Když je vlastnost Visible nastavena na xlSheetHidden, list je skrytý, ale uživatel jej může zobrazit kliknutím pravým tlačítkem na libovolnou kartu listu.
Když je vlastnost Visible nastavena na xlSheetVeryHidden, list je skrytý a nelze jej zobrazit z oblasti listu. K odkrytí je třeba použít kód VBA nebo okno vlastností.

Pokud chcete jednoduše skrýt listy, které lze snadno odkrýt, použijte níže uvedený kód:

Sub HideAllExceptActiveSheet () Dim Ws jako pracovní list pro každý Ws v ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetHidden Next Ws End Sub

Níže uvedený kód by odkryl všechny listy (skryté i velmi skryté).

Sub UnhideAllWoksheets () Dim Ws as worksheet for each Ws in ThisWorkbook.Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

Související článek: Odkrýt všechny listy v aplikaci Excel (najednou)

Skrýt listy na základě textu v něm

Předpokládejme, že máte více listů s názvy různých oddělení nebo let a chcete skrýt všechny listy kromě těch, které obsahují rok 2021-2022.

To lze provést pomocí funkce VBA INSTR.

Níže uvedený kód skryje všechny listy kromě těch, v nichž je text2021-2022.

Sub HideWithMatchingText () Dim Ws jako list pro každý Ws v listech If InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Then Ws.Visible = xlSheetHidden End If Next Ws End Sub

Ve výše uvedeném kódu funkce INSTR vrací pozici znaku, kde najde odpovídající řetězec. Pokud nenajde odpovídající řetězec, vrátí 0.

Výše uvedený kód kontroluje, zda název obsahuje text2021-2022. Pokud ano, nic se nestane, jinak je list skrytý.

Můžete to udělat o krok dále tím, že budete mít text v buňce a tuto buňku použijete v kódu. To vám umožní mít v buňce hodnotu a poté při spuštění makra zůstanou viditelné všechny listy, kromě listu s odpovídajícím textem (spolu s listy, kde zadáváte hodnotu v buňka).

Třídění pracovních listů v abecedním pořadí

Pomocí VBA můžete rychle seřadit listy podle jejich názvů.

Pokud máte například sešit, který obsahuje listy pro jiné oddělení nebo roky, můžete pomocí níže uvedeného kódu rychle seřadit tyto listy ve vzestupném pořadí.

Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets (j) .Name < Listy (i) .Název Potom Listy (j). Přesunout před: = Listy (i) Konec Pokud Další j Další i Application.ScreenUpdating = True End Sub

Všimněte si, že tento kód funguje dobře s názvy textů a ve většině případů také s roky a čísly. Ale může vám poskytnout špatné výsledky v případě, že máte názvy listů jako 1,2,11. Seřadí a poskytne vám sekvenci 1, 11, 2. Důvodem je, že porovnává jako text a považuje 2 za větší než 11.

Chraňte/odemkněte všechny listy najednou

Pokud máte v sešitu mnoho listů a chcete chránit všechny listy, můžete použít níže uvedený kód VBA.

Umožňuje zadat heslo v kódu. Toto heslo budete potřebovat k odemknutí listu.

Sub ProtectAllSheets () Dim ws As Worksheet Dim heslo As String heslo = "Test123" 'nahraďte Test123 heslem, které chcete Pro každé ws v pracovních listech ws.Chraňte heslo: = heslo Další ws Konec Sub

Následující kód by odemkl všechny listy najednou.

Sub ProtectAllSheets () Dim ws As Worksheet Dim heslo As String heslo = "Test123" 'nahraďte Test123 heslem, které jste použili při ochraně Pro každý ws V pracovních listech ws. Odemknout heslo: = heslo Další ws Konec Sub

Vytvoření obsahu všech pracovních listů (s hypertextovými odkazy)

Pokud máte v sešitu sadu listů a chcete rychle vložit souhrnný list, který obsahuje odkazy na všechny listy, můžete použít níže uvedený kód.

Sub AddIndexSheet () Worksheets.Add ActiveSheet.Name = "Index" For i = 2 To Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor: = Cells (i - 1, 1), _ Address: = "", SubAddress: = Worksheets (i) .Název & "! A1", _ TextToDisplay: = Pracovní listy (i). Název Další i Konec Sub

Výše uvedený kód vloží nový list a pojmenuje jej Index.

Poté prochází všemi listy a vytvoří hypertextový odkaz pro všechny listy v listu Rejstřík.

Kam vložit kód VBA

Zajímá vás, kam jde kód VBA do vašeho sešitu v Excelu?

Excel má VBA backend nazvaný VBA editor. Musíte zkopírovat a vložit kód do okna kódu modulu VB Editor.

Zde jsou kroky, jak toho dosáhnout:

Přejděte na kartu Vývojář.
Klikněte na možnost Visual Basic. Tím se otevře editor VB v backendu.
V podokně Průzkumník projektů v editoru VB klikněte pravým tlačítkem na libovolný objekt sešitu, do kterého chcete vložit kód. Pokud Průzkumníka nevidíte, přejděte na kartu Zobrazit a klikněte na Průzkumník projektů.
Přejděte na Vložit a klikněte na Modul. Tím se vloží objekt modulu pro váš sešit.
Zkopírujte a vložte kód do okna modulu.