Toto je konečný průvodce pro práci s Tabulkami / Listy v Excelu.
Ve spodní části této příručky jsme vytvořili podvodný list běžných příkazů pro práci s listy.
Listy vs. Pracovní listy
Na listy lze pomocí VBA odkazovat dvěma způsoby. První je s objektem Sheets:
| 1 | Tabulky („List1“). Aktivujte |
Druhý je s objektem pracovních listů:
| 1 | Pracovní listy („List1“). Aktivujte |
V 99% případů jsou tyto dva objekty totožné. Ve skutečnosti, pokud jste online hledali příklady kódu VBA, pravděpodobně jste viděli oba použité objekty. Zde je rozdíl:
Sbírka listů obsahuje pracovní listy a tabulky s grafy.
Pokud tedy chcete zahrnout běžné pracovní listy A tabulky s grafy, použijte Tabulky. Pokud chcete vyloučit tabulky grafů, použijte pracovní listy. Ve zbytku této příručky budeme listy a listy používat zaměnitelně.
Referenční listy
Na listy lze odkazovat několika různými způsoby:
- ActiveSheet
- Název záložky listu
- Indexové číslo listu
- List Code Name
ActiveSheet
ActiveSheet je list, který je aktuálně aktivní. Jinými slovy, pokud jste pozastavili kód a podívali se na Excel, je to list, který je viditelný. Níže uvedený příklad kódu zobrazí Messagebox s názvem ActiveSheet.
| 1 | MsgBox ActiveSheet.Name |
Název listu
Pravděpodobně jste nejvíce obeznámeni s odkazováním na listy podle názvu karty:
| 1 | Tabulky („TabName“). Aktivujte |
Indexové číslo listu
Indexové číslo listu je pozice listu v sešitu. 1 je první list. 2 je druhý list atd .:
| 1 | Listy (1). Aktivujte |
Indexové číslo listu - poslední list v sešitu
Chcete -li odkazovat na poslední list v sešitu, použijte Sheets.Count k získání posledního indexového čísla:
| 1 | Tabulky (Sheets.Count). Aktivujte |
List „Kódové jméno“
Kódový název listu je název objektu ve VBA:
| 1 | CodeName.Activate |
Referenční listy v jiných sešitech
Je také snadné odkazovat na listy v jiných sešitech. Chcete -li to provést, musíte použít objekt sešitů:
| 1 | Sešity ("VBA_Examples.xlsm"). Pracovní listy ("List1"). Aktivovat |
Důležité: Než budete moci odkazovat na jeho listy, musí být sešit otevřený.
Aktivovat vs. vybrat list
V dalším článku probereme vše o aktivaci a výběru listů. Krátká verze je tato:
Když list aktivujete, stane se z něj ActiveSheet. Toto je list, který byste viděli, kdybyste se podívali do programu Excel. Současně může být aktivován pouze jeden list.
Aktivujte list
| 1 | Tabulky („List1“). Aktivujte |
Když vyberete list, stane se také ActiveSheet. Můžete však vybrat více listů najednou. Pokud je vybráno více listů najednou, „horní“ list je ActiveSheet. ActiveSheet však můžete přepínat ve vybraných listech.
Vyberte list
| 1 | Tabulky („List1“). Vyberte |
Vyberte více listů
Pomocí pole vyberte více listů najednou:
| 1 | Pracovní listy (pole („List2“, „List3“)). Vyberte |
Pracovní list proměnná
Přiřazení listu k proměnné vám umožní odkazovat na list podle jeho názvu. To může ušetřit spoustu psaní a usnadnit čtení kódu. Existuje také mnoho dalších důvodů, proč byste chtěli použít proměnné.
Deklarace proměnné listu:
| 1 | Dim ws jako list |
Přiřadit list proměnné:
| 1 | Set ws = Sheets ("Sheet1") |
Nyní můžete v kódu odkazovat na proměnnou listu:
| 1 | ws.Activate |
Projděte všechny listy v sešitu
Proměnné listu jsou zásadní, pokud chcete procházet všechny listy v sešitu. Nejjednodušší způsob, jak to udělat, je:
| 12345 | Dim ws jako pracovní listZa každé ws v pracovních listechMsgBox ws.nameDalší ws |
Tento kód bude procházet všemi listy v sešitu a zobrazí každý název listu v okně zprávy. Smyčka přes všechny listy v sešitu je velmi užitečná při zamykání / odemykání nebo skrývání / odkrývání více listů najednou.
Ochrana pracovního listu
Ochrana sešitu
Ochrana sešitu uzamkne sešit před strukturálními změnami, jako je přidání, odstranění, přesunutí nebo skrytí listů.
Ochranu sešitu můžete zapnout pomocí VBA:
| 1 | ActiveWorkbook.Protect Heslo: = "Heslo" |
nebo deaktivujte ochranu sešitu:
| 1 | ActiveWorkbook.UnProtect Heslo: = "Heslo" |
Poznámka: Můžete také chránit / odemknout bez hesla vynecháním argumentu Heslo:
| 1 | ActiveWorkbook.Protect |
Ochrana pracovního listu
Ochrana na úrovni listu zabraňuje změnám v jednotlivých listech.
Chraňte pracovní list
| 1 | Pracovní listy („List1“). Chraňte „Heslo“ |
Zrušte ochranu listu
| 1 | Pracovní listy („List1“). Zrušte ochranu „hesla“ |
Při ochraně listů existuje celá řada možností (povolit změny formátování, umožnit uživateli vkládat řádky atd.) K záznamu požadovaných nastavení doporučujeme použít záznam makra.
O ochraně listu diskutujeme podrobněji zde.
Pracovní list Viditelná vlastnost
Možná už víte, že pracovní listy lze skrýt:
Ve skutečnosti existují tři nastavení viditelnosti listu: Visible, Hidden a Velmi skryté.Skryté listy může zobrazit každý běžný uživatel aplikace Excel - kliknutím pravým tlačítkem na oblast karty listu (viz výše). Listy VeryHidden lze odkrýt pouze pomocí kódu VBA nebo z editoru VBA. K skrytí / odkrytí listů použijte následující příklady kódu:
Odkrýt pracovní list
| 1 | Pracovní listy („List1“). Visible = xlSheetVisible |
Skrýt pracovní list
| 1 | Pracovní listy („List1“). Visible = xlSheetHidden |
Velmi skrýt pracovní list
| 1 | Pracovní listy („List1“). Visible = xlSheetVeryHidden |
Události na úrovni pracovního listu
Události jsou spouštěče, které mohou způsobit spuštění „Procedur událostí“. Můžete například způsobit spuštění kódu při každé změně jakékoli buňky na listu nebo při aktivaci listu.
Procedury událostí pracovního listu musí být umístěny v modulu listu:
Existuje mnoho událostí pracovního listu. Chcete-li zobrazit úplný seznam, přejděte na modul listu, z prvního rozevíracího seznamu vyberte „Pracovní list“. Poté výběrem postupu události z druhého rozevíracího seznamu jej vložte do modulu.
Pracovní list Aktivovat událost
Události aktivace listu se spustí při každém otevření listu.
| 123 | Soukromý dílčí list_aktivovat ()Rozsah („A1“). VyberteEnd Sub |
Tento kód vybere buňku A1 (resetování oblasti zobrazení v levé horní části listu) při každém otevření listu.
Událost změny pracovního listu
Události změny listu se spustí vždy, když se na listu změní hodnota buňky. Další informace najdete v našem kurzu o událostech změny pracovního listu.
Pracovní list Cheat Sheet
Níže najdete cheat list obsahující běžné příklady kódů pro práci s listy ve VBA
Podvodní listy VBA
Pracovní listy VBA Cheatsheet| Popis | Příklad kódu |
|---|---|
| Referenční a aktivační listy | |
| Název záložky | Listy („Vstup“). Aktivujte |
| Kódové jméno VBA | List 1. Aktivujte |
| Index pozice | Listy (1). Aktivujte |
| Vyberte List | |
| Vyberte List | Listy („Vstup“). Vyberte |
| Nastaveno na proměnnou | Dim ws jako pracovní list Nastavit ws = ActiveSheet |
| Jméno / Přejmenovat | ActiveSheet.Name = "Nové jméno" |
| Další list | ActiveSheet.Next.Activate |
| Projděte všechny listy | Dim ws jako pracovní list Za každé ws v pracovních listech Msgbox ws.name Další ws |
| Projděte vybrané listy | Dim ws As Worksheet Pro každý ws v ActiveWindow.SelectedSheets MsgBox ws.Name Další ws |
| Získejte ActiveSheet | MsgBox ActiveSheet.Name |
| Přidat list | Listy.Přidat |
| Přidejte list a jméno | Sheets.Add.Name = "Nový list" |
| Přidejte list s názvem z buňky | Sheets.Add.Name = range ("a3"). Hodnota |
| Přidat list za druhým | Sheets.Add After: = Sheets ("Input") |
| Přidejte list za a název | Sheets.Add (After: = Sheets ("Input")). Name = "NewSheet" |
| Přidejte list před a jméno | Sheets.Add (Before: = Sheets ("Input")). Name = "NewSheet" |
| Přidat list na konec sešitu | Sheets.Add After: = Sheets (Sheets.Count) |
| Přidat list na začátek sešitu | Sheets.Add (Before: = Sheets (1)). Name = "FirstSheet" |
| Přidat list do proměnné | Dim ws As Worksheet Nastavit ws = Sheets.Add |
| Kopírujte pracovní listy | |
| Přesunout list na konec sešitu | Tabulky ("List1"). Přesunout za: = Listy (Sheets.Count) |
| Do nového sešitu | Listy („List1“). Zkopírujte |
| Vybrané listy do nového sešitu | ActiveWindow.SelectedSheets.Copy |
| Před dalším listem | Listy („List1“). Kopírovat před: = Listy („List2“) |
| Před prvním listem | Listy („List1“). Kopírovat před: = Listy (1) |
| Po posledním listu | Listy ("List1"). Kopírovat po: = Listy (Sheets.Count) |
| Kopírovat a pojmenovat | Listy ("List1"). Kopírovat po: = Listy (Sheets.Count) ActiveSheet.Name = "Poslední list" |
| Zkopírujte a pojmenujte z hodnoty buňky | Listy ("List1"). Kopírovat po: = Listy (Sheets.Count) ActiveSheet.Name = Rozsah ("A1"). Hodnota |
| Do jiného sešitu | Listy ("List1"). Kopírovat před: = Sešity ("Example.xlsm"). Listy (1) |
| Skrýt / Odkrýt listy | |
| Skrýt list | Listy („List1“). Viditelný = Nepravda nebo Listy ("List1"). Visible = xlSheetHidden |
| Odkrýt list | Listy („List1“). Viditelné = Pravda nebo Listy („List1“). Visible = xlSheetVisible |
| Velmi skrýt list | Listy („List1“). Visible = xlSheetVeryHidden |
| Smazat nebo vymazat listy | |
| Smazat list | Tabulky („List1“). Smazat |
| Odstranit list (zpracování chyb) | Při chybě Pokračovat Další Tabulky („List1“). Smazat Při chybě GoTo 0 |
| Odstranit list (bez výzvy) | Application.DisplayAlerts = False Tabulky („List1“). Smazat Application.DisplayAlerts = True |
| Čistý list | Listy („List1“). Buňky. Vymazat |
| Vymazat pouze obsah listu | Listy („List1“). Cells.ClearContents |
| Clear Sheet UsedRange | Listy („List1“). UsedRange.Clear |
| Chraňte nebo nechraňte listy | |
| Zrušit ochranu (bez hesla) | Tabulky („List1“). Odemknout |
| Odemknout (heslo) | Tabulky („List1“). Zrušte ochranu „hesla“ |
| Chránit (bez hesla) | Listy („List1“). Chraňte |
| Chránit (heslo) | Tabulky („List1“). Chraňte „Heslo“ |
| Chraňte, ale povolte přístup VBA | Listy („List1“). Protect UserInterfaceOnly: = True |
| Zrušte ochranu všech listů | Dim ws As Worksheet Za každé ws v pracovních listech ws.Onprotect "heslo" Další ws |
