Toto je konečný sprievodca pri práci s hárkami / pracovnými listami v programe Excel.
V spodnej časti tejto príručky sme vytvorili podvodný list bežných príkazov na prácu s listami.
Listy vs. Listy
Na listy je možné odkazovať pomocou VBA. Prvá je s objektom Tabuľky:
| 1 | Tabuľky („List1“). Aktivujte |
Druhý je s objektom Pracovné listy:
| 1 | Pracovné listy („List1“). Aktivujte |
V 99% prípadov sú tieto dva objekty identické. V skutočnosti, ak ste online hľadali príklady kódu VBA, pravdepodobne ste videli oba použité objekty. Tu je rozdiel:
Zbierka hárkov obsahuje pracovné hárky A grafy.
Ak chcete zahrnúť bežné pracovné hárky A grafy, použite teda Tabuľky. Ak chcete vylúčiť grafy, použite pracovné listy. Vo zvyšku tejto príručky budeme listy a pracovné listy používať zameniteľne.
Referenčné listy
Na listy je možné odkazovať niekoľkými rôznymi spôsobmi:
- ActiveSheet
- Názov karty hárka
- Indexové číslo listu
- Kódové meno listu
ActiveSheet
ActiveSheet je list, ktorý je aktuálne aktívny. Inými slovami, ak ste pozastavili kód a pozreli ste sa na Excel, je to viditeľný hárok. Nasledujúci príklad kódu zobrazí schránku správ s názvom ActiveSheet.
| 1 | MsgBox ActiveSheet.Name |
Názov listu
Pravdepodobne ste najlepšie oboznámení s odkazovaním na hárky podľa názvu karty:
| 1 | Tabuľky („TabName“). Aktivujte |
Indexové číslo listu
Indexové číslo listu je pozícia listu v zošite. 1 je prvý list. 2 je druhý list atď .:
| 1 | Listy (1). Aktivujte |
Indexové číslo listu - posledný list v zošite
Ak chcete použiť odkaz na posledný hárok v zošite, použite Tabuľky.Číslo, aby ste získali posledné indexové číslo:
| 1 | Tabuľky (Sheets.Count). Aktivujte |
List „Kódové meno“
Kódový názov listu je názov objektu vo VBA:
| 1 | CodeName. Aktivovať |
Referenčné listy v iných zošitoch
Je tiež ľahké odkazovať na hárky v iných zošitoch. Na to musíte použiť Objekt zošitov:
| 1 | Zošity ("VBA_Examples.xlsm"). Pracovné listy ("list1"). Aktivovať |
Dôležité: Zošit musí byť otvorený, než budete môcť odkazovať na jeho listy.
Aktivovať vs. vybrať list
V inom článku diskutujeme o všetkom, čo sa týka aktivácie a výberu listov. Krátka verzia je táto:
Keď list aktivujete, stane sa z neho ActiveSheet. Toto je hárok, ktorý by ste videli, keby ste sa pozreli na svoj program Excel. Naraz môže byť aktivovaný iba jeden hárok.
Aktivujte hárok
| 1 | Tabuľky („List1“). Aktivujte |
Keď vyberiete hárok, stane sa tiež hárkom ActiveSheet. Môžete však vybrať viac hárkov naraz. Keď je vybratých viac hárkov naraz, „horný“ list je ActiveSheet. ActiveSheet však môžete prepínať vo vybratých listoch.
Vyberte list
| 1 | Tabuľky („List1“). Vyberte |
Vyberte položku Viac hárkov
Pomocou poľa vyberte viacero hárkov naraz:
| 1 | Pracovné listy (pole („List2“, „List3“)). Vyberte |
Premenný pracovného listu
Priradenie pracovného hárka k premennej vám umožní odkazovať na pracovný hárok podľa jeho názvu. To môže ušetriť veľa písania a uľahčiť čítanie kódu. Existuje tiež mnoho ďalších dôvodov, prečo by ste mohli chcieť používať premenné.
Deklarácia premennej pracovného hárka:
| 1 | Dim ws ako pracovný list |
Priradiť pracovný hárok k premennej:
| 1 | Nastaviť ws = listy („list1“) |
Teraz môžete vo svojom kóde odkazovať na premennú pracovného hárka:
| 1 | ws.Activate |
Zopakujte všetky listy v zošite
Premenné pracovného hárka sú nevyhnutné, ak si chcete zopakovať všetky pracovné hárky v zošite. Najľahší spôsob, ako to urobiť, je:
| 12345 | Dim ws ako pracovný listZa každé ws v pracovných listochMsgBox ws.nameĎalšie ws |
Tento kód bude prechádzať všetkými pracovnými listami v zošite a v poli so správou zobrazí názov každého pracovného hárka. Opakovanie všetkých listov v zošite je veľmi užitočné pri zamykaní / odomykaní alebo skrývaní / odkrývaní viacerých pracovných hárkov naraz.
Ochrana pracovného listu
Ochrana zošita
Ochrana zošita uzamkne zošit pred štrukturálnymi zmenami, ako je pridávanie, odstraňovanie, presúvanie alebo skrývanie hárkov.
Ochranu zošita môžete zapnúť pomocou VBA:
| 1 | ActiveWorkbook.Protect Heslo: = "Heslo" |
alebo vypnite ochranu zošita:
| 1 | ActiveWorkbook.UnProtect Heslo: = "Heslo" |
Poznámka: Môžete tiež chrániť / odomknúť ochranu bez hesla vynechaním argumentu Heslo:
| 1 | ActiveWorkbook.Protect |
Ochrana pracovného listu
Ochrana na úrovni pracovného hárka zabraňuje zmenám v jednotlivých pracovných hárkoch.
Chráňte pracovný list
| 1 | Pracovné listy („List1“). Chráňte „heslo“ |
Zrušiť ochranu pracovného hárka
| 1 | Pracovné listy („List1“). Zrušte ochranu „hesla“ |
Pri ochrane pracovných hárkov existuje množstvo možností (povoliť zmeny formátovania, povoliť používateľovi vkladať riadky atď.) Na zaznamenanie požadovaných nastavení odporúčame použiť záznamník makier.
Tu podrobnejšie diskutujeme o ochrane pracovného hárka.
Pracovný list Viditeľné vlastníctvo
Možno už viete, že pracovné listy je možné skryť:
V skutočnosti existujú tri nastavenia viditeľnosti pracovného hárka: Viditeľné, Skryté a Veľmi skryté.Skryté hárky môže odkryť každý bežný používateľ Excelu - kliknutím pravým tlačidlom myši na oblasť karty pracovného hárka (zobrazené vyššie). Listy VeryHidden je možné odkryť iba pomocou kódu VBA alebo z editora VBA. Na skrytie / odkrytie pracovných hárkov použite nasledujúce príklady kódu:
Odkryť pracovný hárok
| 1 | Pracovné listy („List1“). Visible = xlSheetVisible |
Skryť pracovný list
| 1 | Pracovné listy („List1“). Visible = xlSheetHidden |
Veľmi skryť pracovný list
| 1 | Pracovné listy („List1“). Viditeľné = xlSheetVeryHidden |
Udalosti na úrovni pracovného hárka
Udalosti sú spúšťače, ktoré môžu spôsobiť spustenie „Procedúr udalostí“. Môžete napríklad spôsobiť, aby sa kód spustil pri každej zmene bunky v hárku alebo pri aktivácii hárka.
Procedúry udalostí pracovného hárka musia byť umiestnené v module pracovného hárka:
Existuje množstvo udalostí pracovného hárka. Ak chcete zobraziť kompletný zoznam, prejdite na modul pracovného hárka, v prvom rozbaľovacom zozname vyberte „Pracovný list“. Potom z druhého rozbaľovacieho zoznamu vyberte postup udalosti a vložte ho do modulu.
Pracovný list Aktivovať udalosť
Udalosti aktivácie pracovného hárka sa spustia pri každom otvorení pracovného hárka.
| 123 | Súkromný čiastkový pracovný hárok_Aktivovať ()Rozsah („A1“). VyberteKoniec pod |
Tento kód vyberie bunku A1 (obnovenie oblasti zobrazenia v ľavom hornom rohu pracovného hárka) pri každom otvorení pracovného hárka.
Udalosť zmeny pracovného hárka
Udalosti zmeny hárka sa spustia vždy, keď sa v hárku zmení hodnota bunky. Ďalšie informácie nájdete v našom návode o udalostiach zmeny pracovného hárka.
Pracovný list Cheat Sheet
Nasleduje podvádzací list, ktorý obsahuje bežné príklady kódov pre prácu s hárkami vo VBA
Podvodný hárok VBA
Pracovné listy VBA Cheatsheet| Popis | Príklad kódu |
|---|---|
| Referenčné a aktivačné listy | |
| Názov karty | Tabuľky („Vstup“). Aktivujte |
| Kódové meno VBA | List 1. Aktivujte |
| Indexová pozícia | Listy (1). Aktivujte |
| Vyberte položku Hárok | |
| Vyberte položku Hárok | Tabuľky ("Vstup"). Vyberte |
| Nastaviť na premennú | Dim ws ako pracovný list Nastaviť ws = ActiveSheet |
| Meno / Premenovať | ActiveSheet.Name = "Nové meno" |
| Ďalší list | ActiveSheet.Next.Activate |
| Smyčka cez všetky listy | Dim ws ako pracovný list Za každé w v pracovných listoch Msgbox ws.name Ďalšie ws |
| Smyčka cez vybrané listy | Dim ws As worksheet Pre každé ws v ActiveWindow.SelectedSheets MsgBox ws.Name Ďalšie ws |
| Získajte ActiveSheet | MsgBox ActiveSheet.Name |
| Pridať hárok | Listy. Pridať |
| Pridajte hárok a meno | Sheets.Add.Name = "Nový list" |
| Pridajte hárok s názvom z bunky | Sheets.Add.Name = range ("a3"). Value |
| Pridajte list za druhým | Sheets.Add After: = Sheets ("Input") |
| Pridajte hárok za a meno | Sheets.Add (After: = Sheets ("Input")). Name = "NewSheet" |
| Pridajte list pred a meno | Sheets.Add (Before: = Sheets ("Input")). Name = "NewSheet" |
| Pridať hárok na koniec zošita | Sheets.Add After: = Sheets (Sheets.Count) |
| Pridať hárok na začiatok zošita | Sheets.Add (Before: = Sheets (1)). Name = "FirstSheet" |
| Pridať hárok do premennej | Dim ws As worksheet Nastaviť ws = Sheets.Add |
| Kopírujte pracovné listy | |
| Presunúť hárok na koniec zošita | Tabuľky ("List1"). Presunúť za: = Tabuľky (Sheets.Count) |
| Do nového zošita | Tabuľky („List1“). Kopírovať |
| Vybrané listy do nového zošita | ActiveWindow.SelectedSheets.Copy |
| Pred ďalším listom | Listy („List1“). Kopírovať pred: = Listy („List2“) |
| Pred prvým listom | Listy („List1“). Kopírovať pred: = listy (1) |
| Po poslednom liste | Tabuľky ("List1"). Kopírovať po: = Listy (Sheets.Count) |
| Skopírujte a pomenujte | Tabuľky ("List1"). Kopírovať po: = Listy (Sheets.Count) ActiveSheet.Name = "Posledný list" |
| Skopírujte a pomenujte podľa hodnoty bunky | Tabuľky ("List1"). Kopírovať po: = Listy (Sheets.Count) ActiveSheet.Name = Rozsah ("A1"). Hodnota |
| Do iného zošita | Listy („List1“). Kopírovať pred: = zošity („Example.xlsm“). Listy (1) |
| Skryť / odkryť listy | |
| Skryť hárok | Tabuľky („List1“). Viditeľné = nepravda alebo Tabuľky ("List1"). Visible = xlSheetHidden |
| Odkryť hárok | Tabuľky („List1“). Viditeľné = Pravda alebo Tabuľky ("List1"). Visible = xlSheetVisible |
| Veľmi skryť list | Tabuľky („List1“). Viditeľné = xlSheetVeryHidden |
| Odstráňte alebo vymažte hárky | |
| Odstrániť hárok | Tabuľky („List1“). Odstrániť |
| Odstrániť hárok (spracovanie chýb) | Pri chybe Pokračovať ďalej Tabuľky („List1“). Odstrániť Pri chybe GoTo 0 |
| Odstrániť hárok (bez výzvy) | Application.DisplayAlerts = False Tabuľky („List1“). Odstrániť Application.DisplayAlerts = Pravda |
| Čistý list | Listy („List1“). Bunky. Vymazať |
| Vymazať iba obsah hárka | Tabuľky („List1“). Bunky. Jasný obsah |
| Clear Sheet UsedRange | Listy („List1“). UsedRange.Clear |
| Chráňte alebo zrušte ochranu listov | |
| Zrušiť ochranu (bez hesla) | Tabuľky („List1“). Nechráňte |
| Zrušiť ochranu (heslo) | Tabuľky („List1“). Zrušte ochranu „hesla“ |
| Chrániť (bez hesla) | Listy („List1“). Chráňte |
| Chrániť (heslo) | Tabuľky („List1“). Chráňte „Heslo“ |
| Chráňte, ale povoľte prístup VBA | Tabuľky („List1“). Protect UserInterfaceOnly: = True |
| Zrušte ochranu všetkých hárkov | Dim ws As worksheet Za každé ws v pracovných listoch ws.Zrušte ochranu „hesla“ Ďalšie ws |
