Práca s pracovnými listami pomocou programu Excel VBA (vysvetlené s príkladmi)

Okrem buniek a rozsahov je práca s pracovnými listami ďalšou oblasťou, o ktorej by ste mali vedieť, ako efektívne používať VBA v programe Excel.

Rovnako ako všetky objekty vo VBA majú pracovné hárky rôzne vlastnosti a metódy, ktoré s ním súvisia a ktoré môžete použiť pri automatizácii práce s VBA v programe Excel.

V tomto návode sa podrobne zaoberám „pracovnými listami“ a ukážem vám aj niekoľko praktických príkladov.

Začnime teda.

Všetky kódy, ktoré spomínam v tomto návode, je potrebné vložiť do editora VB. Prejdite do sekcie „Kam umiestniť kód VBA“ a zistite, ako funguje.

Ak máte záujem naučiť sa VBA jednoduchým spôsobom, pozrite sa na moje Online školenie Excel VBA.

Rozdiel medzi pracovnými listami a listami vo VBA

Vo VBA máte dve zbierky, ktoré môžu byť niekedy trochu mätúce.

V zošite môžete mať pracovné hárky a tiež grafy. Nasledujúci príklad má tri pracovné hárky a jeden hárok grafu.

V programe Excel VBA:

• Zbierka „Pracovné listy“ by sa vzťahovala na zbierku všetkých objektov pracovného hárka v zošite. V uvedenom príklade by zbierka pracovných listov pozostávala z troch pracovných listov.
• Zbierka „Tabuľky“ by sa vzťahovala na všetky pracovné listy a listy grafov v zošite. Vo vyššie uvedenom príklade by to malo štyri prvky - 3 pracovné listy + 1 list s grafmi.

Ak máte zošit, ktorý obsahuje iba pracovné hárky a žiadne hárky s grafmi, potom sú kolekcie „Pracovné listy“ a „Tabuľky“ rovnaké.

Keď však máte jeden alebo viac hárkov s grafmi, zbierka „Tabuľky“ by bola väčšia ako zbierka „Pracovné listy“

Tabuľky = Pracovné listy + Tabuľky s grafmi

Teraz s týmto rozlíšením odporúčam byť pri písaní kódu VBA čo najkonkrétnejší.

Ak sa teda musíte odvolávať iba na pracovné listy, použite kolekciu „Pracovné listy“ a ak sa musíte obrátiť na všetky listy (vrátane hárkov s grafmi), použite kolekciu „Tabuľky“.

V tomto návode budem používať iba zbierku „pracovné listy“.

Odkázanie na pracovný list vo VBA

Existuje niekoľko rôznych spôsobov, ako použiť odkaz na pracovný list vo VBA.

Pochopenie toho, ako sa odvolávať na pracovné listy, vám pomôže napísať lepší kód, najmä keď v kóde VBA používate slučky.

Použitie názvu pracovného hárka

Najľahší spôsob, ako sa odvolať na pracovný hárok, je použiť jeho názov.

Predpokladajme napríklad, že máte pracovný zošit s tromi pracovnými listami - list 1, list 2, list 3.

A chcete aktivovať list 2.

Môžete to urobiť pomocou nasledujúceho kódu: Sub ActivateSheet () pracovné listy ("Sheet2"). Aktivovať End Sub

Vyššie uvedený kód žiada VBA, aby sa odvolala na list 2 v zbierke pracovných hárkov a aktivovala ho.

Pretože používame presný názov hárka, môžete tu použiť aj kolekciu Hárky. Nasledujúci kód by teda urobil to isté.

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

Použitie indexového čísla

Aj keď je použitie názvu hárka jednoduchým spôsobom, ako sa odvolať na pracovný hárok, niekedy možno neviete presný názov pracovného hárka.

Ak napríklad používate na pridanie nového pracovného hárka do zošita kód VBA a neviete, koľko pracovných hárkov už existuje, názov nového pracovného hárka by ste nepoznali.

V takom prípade môžete použiť indexové číslo pracovných hárkov.

Predpokladajme, že máte v zošite nasledujúce listy:

Nasledujúci kód by aktivoval Sheet2:

Sub ActivateSheet () pracovné listy (2). Aktivovať End Sub

Upozorňujeme, že sme použili index číslo 2 v Pracovné listy (2). Toto by sa vzťahovalo na druhý objekt v zbierke pracovných hárkov.

Čo sa teraz stane, keď ako indexové číslo použijete 3?

Vyberie list3.

Ak sa pýtate, prečo vybral Sheet3, pretože je to jednoznačne štvrtý objekt.

Stáva sa to preto, že hárok grafu nie je súčasťou zbierky pracovných hárkov.

Keď teda použijeme čísla indexov v kolekcii pracovných hárkov, bude sa vzťahovať iba na pracovné hárky v zošite (a listy grafov ignorovať).

Naopak, ak používate Tabuľky, Tabuľky (1) by odkazovali na Tabuľky1, Tabuľky (2) by odkazovali na Tabuľku2, Tabuľky (3) by odkazovali na Graf1 a Tabuľky (4) by odkazovali na Tabuľku3.

Táto technika používania indexového čísla je užitočná, keď chcete prechádzať všetkými pracovnými listami v zošite. Môžete spočítať počet pracovných hárkov a potom ich prechádzať pomocou tohto počtu (uvidíme, ako to urobiť neskôr v tomto návode).

Poznámka: Indexové číslo ide zľava doprava. Ak teda posuniete hárok 2 doľava od hárka 1, pracovné hárky (1) budú odkazovať na hárok 2.

Použitie kódového názvu pracovného hárka

Jednou z nevýhod používania názvu hárka (ako sme videli v sekcii vyššie) je to, že ho používateľ môže zmeniť.

A pokiaľ bol názov hárka zmenený, váš kód nebude fungovať, kým nezmeníte aj názov pracovného hárka v kóde VBA.

Na vyriešenie tohto problému môžete použiť kódový názov pracovného hárka (namiesto bežného názvu, ktorý sme doteraz používali). Kódový názov je možné priradiť v editore VB a nezmení sa, keď zmeníte názov hárka z oblasti pracovného hárka.

Ak chcete pomenovať pracovný hárok kódovým názvom, postupujte podľa týchto krokov:

1. Kliknite na kartu Vývojár.
2. Kliknite na tlačidlo Visual Basic. Tým sa otvorí editor VB.
3. V ponuke kliknite na možnosť Zobraziť a potom kliknite na položku Okno projektu. Vďaka tomu bude tabla Vlastnosti viditeľná. Ak je tabla Vlastnosti už viditeľná, tento krok preskočte.
4. V prieskumníkovi projektu kliknite na názov hárka, ktorý chcete premenovať.
5. Na table Vlastnosti zmeňte názov v poli pred položkou (Názov). V názve nemôžete mať medzery.

Vyššie uvedené kroky by zmenili názov vášho pracovného hárka v backende VBA. V zobrazení pracovného hárka programu Excel môžete pracovný hárok pomenovať ľubovoľne, ale v backende bude reagovať na názvy - názov hárka aj kódové meno.

Na vyššie uvedenom obrázku je názov listu „SheetName“ a kódový názov „CodeName“. Aj keď zmeníte názov hárka v hárku, kódový názov zostane rovnaký.

Teraz môžete použiť kolekciu pracovných hárkov na odkaz na pracovný hárok alebo použiť kódové meno.

Napríklad obidva riadky aktivujú pracovný hárok.

Pracovné listy („Názov hárku“). Aktivujte CodeName.Activate

Rozdiel v týchto dvoch je, že ak zmeníte názov pracovného hárka, prvý nebude fungovať. Ale druhý riadok by pokračoval v práci aj so zmeneným názvom. Druhý riadok (pomocou CodeName) je tiež kratší a používanie je jednoduchšie.

Odkaz na pracovný list v inom zošite

Ak chcete odkazovať na pracovný hárok v inom zošite, tento zošit musí byť počas spustenia kódu otvorený a musíte zadať názov zošita a pracovného hárka, na ktoré sa chcete obrátiť.

Ak máte napríklad zošit s názvom Príklady a chcete si aktivovať list 1 v zošite Príklad, musíte použiť nasledujúci kód:

ZošityActivate () zošity ("examples.xlsx"). Pracovné listy ("Sheet1"). Aktivovať End Sub

Ak bol zošit uložený, musíte spolu s príponou použiť aj názov súboru. Ak si nie ste istí, aké meno použiť, požiadajte o pomoc aplikáciu Project Explorer.

V prípade, že zošit nebol uložený, nemusíte používať príponu súboru.

Pridanie pracovného hárka

Nasledujúci kód by pridal pracovný hárok (ako prvý pracovný hárok - t.j. ako hárok úplne vľavo na karte hárku).

Sub pracovné listy AddSheet (). Pridať koniec pod

Potrebuje predvolený názov List2 (alebo akékoľvek iné číslo podľa toho, koľko listov už existuje).

Ak chcete, aby bol pracovný hárok pridaný pred konkrétny pracovný hárok (povedzme Hárok2), môžete použiť nasledujúci kód.

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

Vyššie uvedený kód hovorí VBA, aby pridal hárok, a potom pomocou príkazu „Pred“ špecifikuje pracovný hárok, pred ktorý by sa mal vložiť nový pracovný hárok.

Podobne môžete tiež pridať hárok za pracovný hárok (povedzme Hárok2) pomocou nižšie uvedeného kódu:

Vedľajšie listy pracovného hárka (). Pridať po: = pracovné hárky („hárok 2“) Koniec pod

Ak chcete, aby bol nový list pridaný na koniec listov, musíte najskôr vedieť, koľko listov je. Nasledujúci kód najskôr spočíta počet listov a nový list pridá za posledný list (na ktorý odkazujeme pomocou indexového čísla).

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

Odstránenie pracovného hárka

Nasledujúci kód by odstránil aktívny hárok zo zošita.

Podstavec DeleteSheet () ActiveSheet.Delete End Sub

Vyššie uvedený kód by pred odstránením pracovného hárka zobrazil výzvu s upozornením.

Ak sa vám varovná výzva nechce zobrazovať, použite nasledujúci kód:

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

Keď je Application.DisplayAlerts nastavený na hodnotu False, nezobrazí sa vám upozornenie. Ak ho používate, na konci kódu ho nastavte späť na hodnotu True.

Pamätajte si, že toto odstránenie nemôžete vrátiť späť, preto použite uvedený kód, ak ste si úplne istí.

Ak chcete odstrániť konkrétny hárok, môžete to urobiť pomocou nasledujúceho kódu:

Vedľajšie hárky DeleteSheet () („List2“). Odstrániť koncový pod

Na vymazanie listu môžete použiť aj kódový názov.

Sub Delete Sheet () Sheet5. Delete End Sub

Premenovanie pracovných listov

Vlastnosť názvu pracovného hárka môžete zmeniť a zmeniť tak jeho názov.

Nasledujúci kód zmení názov listu 1 na „Súhrn“.

Pracovné hárky čiastkového premenovaného listu () ("List1"). Názov = "Súhrn" Koncový pod

Môžete to skombinovať s metódou pridávania hárkov a získať tak sadu hárkov s konkrétnymi názvami.

Ak napríklad chcete vložiť štyri listy s názvom 2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 a2021-2022 Q4, môžete použiť nižšie 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 Koniec pod

Vo vyššie uvedenom kóde najskôr spočítame počet listov a potom pomocou slučky For Next vložíme nové listy na koniec. Keď sa list pridá, kód ho tiež premenuje.

Priradenie objektu pracovného hárka k premennej

Pri práci s listami môžete pracovný hárok priradiť k objektovej premennej a potom použiť premennú namiesto odkazov na pracovný hárok.

Ak napríklad chcete do všetkých hárkov pridať predponu roku, namiesto počítania hárkov a mnohonásobného spustenia cyklu môžete použiť premennú objektu.

Tu je kód, ktorý pridá 2021-2022 ako predponu k všetkým názvom pracovného hárka.

Pod premenný list () Dim Ws ako pracovný list pre každé Ws v pracovných listoch Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

Vyššie uvedený kód deklaruje ako typ pracovného hárka premennú Ws (pomocou riadka „Dim Ws As Worksheet“).

Teraz nemusíme počítať počet listov, ktoré nimi prejdeme. Namiesto toho môžeme použiť slučku „Pre každé W v pracovných listoch“. To nám umožní prejsť všetky listy v zbierke pracovných listov. Nezáleží na tom, či existujú 2 listy alebo 20 listov.

Aj keď nám vyššie uvedený kód umožňuje prechádzať všetkými listami, premennej môžete priradiť aj konkrétny list.

V nižšie uvedenom kóde priradíme premennú W k listu 2 a použijeme ho na prístup k všetkým vlastnostiam listu 2.

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

Akonáhle nastavíte odkaz na pracovný hárok na premennú objektu (pomocou príkazu SET), tento objekt bude možné použiť namiesto odkazu na pracovný hárok. To môže byť užitočné, ak máte dlhý komplikovaný kód a chcete zmeniť referenciu. Namiesto toho, aby ste zmenu vykonali kdekoľvek, môžete ju jednoducho vykonať v príkaze SET.

Všimnite si toho, že kód deklaruje objekt Ws ako premennú typu pracovného hárka (pomocou riadku Dim Ws ako pracovného hárka).

Skryť pracovné listy pomocou jazyka VBA (skryté + veľmi skryté)

Skrytie a odkrytie pracovných hárkov v programe Excel je jednoduchá úloha.

Môžete skryť pracovný hárok a používateľ by ho nevidel, keď otvorí zošit. Hárok však môžu ľahko odkryť kliknutím pravým tlačidlom myši na ľubovoľnú kartu listu.

Ale čo keď nechcete, aby mohli odkryť pracovné hárky.

Môžete to urobiť pomocou VBA.

Nasledujúci kód skryje všetky pracovné hárky v zošite (okrem aktívneho hárka), takže ho nemôžete odkryť kliknutím pravým tlačidlom myši na názov hárka.

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

Vo vyššie uvedenom kóde sa vlastnosť Ws.Visible zmení na xlSheetVeryHidden.

• Keď je vlastnosť Visible nastavená na xlSheetVisible, hárok je viditeľný v oblasti pracovného hárka (ako karty pracovného hárka).
• Keď je vlastnosť Visible nastavená na xlSheetHidden, hárok je skrytý, ale používateľ ho môže odkryť kliknutím pravým tlačidlom myši na ľubovoľnú kartu listu.
• Keď je vlastnosť Visible nastavená na xlSheetVeryHidden, hárok je skrytý a nedá sa skryť z oblasti pracovného hárka. Na jeho odkrytie musíte použiť kód VBA alebo okno vlastností.

Ak chcete jednoducho skryť hárky, ktoré sa dajú ľahko skryť, použite nasledujúci kód:

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

Nasledujúci kód by odkryl všetky pracovné hárky (skryté aj veľmi skryté).

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

Súvisiaci článok: Odkryte všetky hárky v programe Excel (naraz)

Skryť hárky podľa textu v ňom

Predpokladajme, že máte viacero hárkov s názvom rôznych oddelení alebo rokov a chcete skryť všetky hárky okrem tých, v ktorých je uvedený rok 2021-2022.

Môžete to urobiť pomocou funkcie VBA INSTR.

Nasledujúci kód skryje všetky hárky okrem tých, v ktorých je text2021-2022.

Sub HideWithMatchingText () Dim Ws ako pracovný list pre každé Ws v pracovných listoch If InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Then Ws.Visible = xlSheetHidden End If Next Ws End Sub

Vo vyššie uvedenom kóde funkcia INSTR vráti pozíciu znaku, kde nájde zodpovedajúci reťazec. Ak nenájde zodpovedajúci reťazec, vráti 0.

Vyššie uvedený kód kontroluje, či je v názve text2021-2022. Ak sa tak stane, nič sa nestane, inak je pracovný hárok skrytý.

Môžete to urobiť ešte o krok ďalej tým, že budete mať text v bunke a túto bunku použijete v kóde. To vám umožní mať v bunke hodnotu a potom, keď spustíte makro, zostanú viditeľné všetky listy, okrem toho, v ktorom je zodpovedajúci text (spolu s listami, do ktorých zadávate hodnotu v bunka).

Zoradenie pracovných listov podľa abecedy

Pomocou VBA môžete pracovné listy rýchlo triediť podľa ich názvov.

Ak máte napríklad zošit, ktorý obsahuje hárky pre rôzne oddelenia alebo roky, potom pomocou nižšie uvedeného kódu môžete tieto hárky rýchlo zoradiť vzostupne.

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 < Tabuľky (i). Názov potom Listy (j). Presunúť pred: = Listy (i) Končiť Ak ďalšie j Ďalej i Application.ScreenUpdating = True End Sub

Tento kód funguje dobre s textovými názvami a vo väčšine prípadov aj s rokmi a číslami. V prípade, že máte názvy hárkov 1,2,11, môže vám však poskytnúť nesprávne výsledky. Zoradí a poskytne vám postupnosť 1, 11, 2. Dôvodom je, že porovnáva ako text a 2 považuje za väčšie ako 11.

Chráňte/zrušte ochranu všetkých hárkov naraz

Ak máte v pracovnom zošite veľa pracovných hárkov a chcete chrániť všetky hárky, môžete použiť kód VBA nižšie.

Umožňuje vám zadať heslo do kódu. Toto heslo budete potrebovať na zrušenie ochrany pracovného hárka.

Sub ProtectAllSheets () Dim ws As Worksheet Dim heslo As String heslo = "Test123" 'nahraďte Test123 heslom, ktoré chcete Pre každé ws v pracovných listoch ws.Chráňte heslo: = heslo Ďalej ws Koniec Sub

Nasledujúci kód by nechránil všetky listy naraz.

Sub ProtectAllSheets () Dim ws As worksheet Dim heslo As String heslo = "Test123" 'nahraďte Test123 heslom, ktoré ste použili pri ochrane Za každé ws V pracovných listoch ws.Onprotect heslo: = heslo Ďalej ws Koniec Sub

Vytvorenie obsahu všetkých pracovných hárkov (s hypertextovými odkazmi)

Ak máte v zošite sadu pracovných hárkov a chcete rýchlo vložiť súhrnný hárok, ktorý obsahuje odkazy na všetky hárky, môžete použiť nižšie 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ázov & "! A1", _ TextToDisplay: = Pracovné listy (i). Názov Ďalej i Koniec Sub

Vyššie uvedený kód vloží nový pracovný hárok a pomenuje ho Register.

Potom sa prejde všetkými pracovnými listami a vytvorí hypertextový odkaz pre všetky pracovné hárky v hárku Register.

Kam vložiť kód VBA

Zaujíma vás, kam smeruje kód VBA vo vašom zošite programu Excel?

Excel má backend VBA nazývaný editor VBA. Kód musíte skopírovať a vložiť do okna kódu modulu VB Editor.

Tu sú kroky, ako to urobiť:

1. Prejdite na kartu Vývojár.
2. Kliknite na možnosť Visual Basic. Tým sa otvorí editor VB v backende.
3. Na table Project Explorer v editore VB kliknite pravým tlačidlom myši na ľubovoľný objekt zošita, do ktorého chcete vložiť kód. Ak sa Project Explorer nezobrazuje, choďte na kartu Zobraziť a kliknite na Project Explorer.
4. Prejdite na položku Vložiť a kliknite na modul. Tým sa vloží objekt modulu do vášho zošita.
5. Skopírujte a prilepte kód do okna modulu.

Tiež by sa vám mohli páčiť nasledujúce návody na Excel VBA:

• Práca so zošitmi pomocou VBA.
• Použitie príkazov IF Then Else vo VBA.
• Pre ďalšiu slučku vo VBA.
• Vytvorenie funkcie definovanej používateľom v programe Excel.
• Ako zaznamenať makro v programe Excel.
• Ako spustiť makro v programe Excel.
• Udalosti Excel VBA - ľahký (a kompletný) sprievodca.
• Ako vytvoriť doplnok v programe Excel.
• Ako uložiť a znova použiť makro pomocou osobného zošita makier programu Excel.