✅ Töölehtedega töötamine Exceli VBA abil (näidetega selgitatud)

Lang L: none (table-of-contents)

Töölehtede ja lehtede erinevus VBA -s
Töölehele viitamine VBA -s
Töölehe lisamine
Töölehe kustutamine
Töölehtede ümbernimetamine
Töölehe objekti määramine muutujale
Peida töölehed VBA abil (peidetud + väga peidetud)
Peida lehed selle teksti põhjal
Töölehtede sortimine tähestikulises järjekorras
Kaitske/eemaldage kõik lehed korraga
Kõigi töölehtede sisukorra loomine (hüperlinkidega)
Kuhu panna VBA kood

Peale lahtrite ja vahemike on töölehtedega töötamine veel üks valdkond, mida peaksite teadma VBA tõhusaks kasutamiseks Excelis.

Nagu igal VBA objektil, on ka töölehtedel erinevad omadused ja meetodid, mida saate kasutada Excelis VBA abil töö automatiseerimisel.

Selles õpetuses käsitlen üksikasjalikult „töölehti” ja näitan teile ka praktilisi näiteid.

Nii et alustame.

Kõik selles juhendis mainitud koodid tuleb paigutada VB redaktorisse. Avage jaotis „Kuhu VBA -kood panna”, et teada saada, kuidas see toimib.

Kui olete huvitatud VBA lihtsast õppimisest, vaadake minu lehte Online VBA koolitus Excelis.

Töölehtede ja lehtede erinevus VBA -s

VBA -s on teil kaks kollektsiooni, mis võivad kohati pisut segadusse ajada.

Töövihikus võib olla töölehti ja diagrammilehti. Allolevas näites on kolm töölehte ja üks diagrammileht.

Exceli VBA -s:

Kogumik „Töölehed” viitab kõigi töövihiku töölehtede objektide kogumisele. Ülaltoodud näites koosneks töölehtede kogu kolmest töölehest.
Kogumik „Sheets” viitaks kõigile töövihiku töölehtedele ja diagrammilehtedele. Ülaltoodud näites oleks sellel neli elementi - 3 töölehte + 1 diagrammileht.

Kui teil on töövihik, millel on ainult töölehed ja mitte diagrammilehti, on kollektsioon „Töölehed” ja „Lehed” sama.

Kuid kui teil on üks või mitu diagrammilehte, oleks kollektsioon „Sheets” suurem kui kollektsioon „Worksheets”

Lehed = töölehed + diagrammilehed

Selle erisusega soovitan VBA koodi kirjutamisel olla võimalikult konkreetne.

Nii et kui peate viitama ainult töölehtedele, kasutage kollektsiooni „Töölehed” ja kui peate viitama kõikidele lehtedele (sh diagrammilehed), kasutage kogumit „Lehed”.

Selles õpetuses kasutan ainult kollektsiooni „Töölehed”.

Töölehele viitamine VBA -s

VBA töölehele viitamiseks on palju erinevaid viise.

Töölehtedele viitamise mõistmine aitaks teil paremat koodi kirjutada, eriti kui kasutate VBA -koodis silmuseid.

Töölehe nime kasutamine

Lihtsaim viis töölehele viitamiseks on selle nime kasutamine.

Oletame näiteks, et teil on töövihik kolme töölehega - leht 1, leht 2, leht 3.

Ja soovite aktiveerida lehe 2.

Seda saate teha järgmise koodi abil: Sub ActivateSheet () Töölehed ("Sheet2"). Aktiveerige End Sub

Ülaltoodud kood palub VBA -l viidata töölehtede kogu lehele 2 ja see aktiveerida.

Kuna kasutame lehe täpset nime, saate siin kasutada ka kogu Arvutustabelid. Nii et ka allpool olev kood teeks sama asja.

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

Indeksinumbri kasutamine

Kuigi lehe nime kasutamine on lihtne viis töölehele viitamiseks, ei pruugi te mõnikord töölehe täpset nime teada.

Näiteks kui kasutate töövihikusse uue töölehe lisamiseks VBA -koodi ja te ei tea, kui palju töölehti on juba olemas, ei tea te uue töölehe nime.

Sel juhul saate kasutada töölehtede indeksnumbrit.

Oletame, et teil on töövihikus järgmised lehed:

Allolev kood aktiveeriks lehe 2:

Sub ActivateSheet () töölehed (2). Aktiveerige End Sub

Pange tähele, et oleme kasutanud indeksit 2 tolli Töölehed (2). See viitaks töölehtede kogu teisele objektile.

Mis juhtub siis, kui kasutate indeksnumbrina 3?

See valib lehe 3.

Kui te ei tea, miks ta valis lehe 3, kuna see on selgelt neljas objekt.

See juhtub seetõttu, et diagrammileht ei kuulu töölehtede kogusse.

Seega, kui kasutame töölehtede kollektsiooni indeksnumbreid, viitab see ainult töövihiku töölehtedele (ja ignoreerib diagrammilehti).

Vastupidi, kui kasutate arvutustabeleid, viitavad lehed (1) lehtedele1, lehed (2) lehele2, lehed (3) diagrammile1 ja lehed (4) lehele 3.

See indeksnumbri kasutamise tehnika on kasulik, kui soovite töövihiku kõik töölehed läbi vaadata. Saate loendada töölehtede arvu ja neid seejärel selle loenduse abil läbi vaadata (näeme, kuidas seda hiljem selles õpetuses teha).

Märkus. Indeksinumber liigub vasakult paremale. Seega, kui nihutate lehte2 lehelt 1 vasakule, viitavad töölehed (1) lehele2.

Töölehe koodinime kasutamine

Lehe nime kasutamise üks puudusi (nagu nägime ülaltoodud jaotises) on see, et kasutaja saab seda muuta.

Ja kui lehe nime on muudetud, ei tööta teie kood enne, kui muudate ka töölehe nime VBA -koodis.

Selle probleemi lahendamiseks võite kasutada töölehe koodnime (senise tavalise nime asemel). Koodinime saab määrata VB redaktoris ja see ei muutu, kui muudate töölehe alalt lehe nime.

Töölehele koodnime andmiseks toimige järgmiselt.

Klõpsake vahekaarti Arendaja.
Klõpsake nuppu Visual Basic. See avab VB redaktori.
Klõpsake menüü suvandit Vaade ja seejärel projekti akent. See muudab atribuutide paani nähtavaks. Kui atribuutide paan on juba nähtav, jätke see samm vahele.
Klõpsake projekti Exploreris lehe nime, mille soovite ümber nimetada.
Muutke paanil Atribuudid nime (Nimi) ees oleval väljal. Pange tähele, et nimes ei tohi olla tühikuid.

Ülaltoodud sammud muudaksid teie töölehe nime VBA taustaprogrammis. Exceli töölehe vaates saate töölehele nimetada mida iganes soovite, kuid taustaprogrammis vastab see mõlemale nimele - lehe nimele ja koodinimele.

Ülaltoodud pildil on lehe nimi „SheetName” ja koodinimi on „CodeName”. Isegi kui muudate töölehel lehe nime, jääb koodnimi samaks.

Nüüd saate töölehele viitamiseks kasutada kas töölehtede kogu või kasutada koodnime.

Näiteks aktiveerivad mõlemad read töölehte.

Töölehed ("Sheetname"). Aktiveerige CodeName.Activate

Nende kahe erinevus seisneb selles, et kui muudate töölehe nime, siis esimene ei tööta. Kuid teine rida töötaks isegi muudetud nimega. Teine rida (koodnime kasutamine) on samuti lühem ja hõlpsamini kasutatav.

Viide töölehele erinevas töövihikus

Kui soovite viidata mõne teise töövihiku töölehele, peab see töövihik olema koodi töötamise ajal avatud ning määrama töövihiku ja töölehe nime, millele soovite viidata.

Näiteks kui teil on töövihik nimega Näited ja soovite aktiveerida töölehe Näide 1, peate kasutama järgmist koodi:

AlamlehtActivate () Töövihikud ("Examples.xlsx"). Töölehed ("Sheet1"). Aktiveerige lõpp -alam

Pange tähele, et kui töövihik on salvestatud, peate koos laiendiga kasutama ka failinime. Kui te pole kindel, millist nime kasutada, võtke abi Project Explorerist.

Kui töövihikut pole salvestatud, ei pea te faililaiendit kasutama.

Töölehe lisamine

Allolev kood lisaks töölehe (esimese töölehena - st lehekaardi vasakpoolseima lehena).

Sub AddSheet () töölehed. Lisa End Sub

See võtab vaikenime Sheet2 (või mis tahes muu numbri vastavalt sellele, kui palju lehti on juba olemas).

Kui soovite, et tööleht lisataks enne konkreetset töölehte (nt Sheet2), saate kasutada allolevat koodi.

Sub AddSheet () Töölehed.Add Before: = Töölehed ("Sheet2") End Sub

Ülaltoodud kood käsib VBA -l lisada lehe ja kasutab seejärel lauset „Enne”, et määrata tööleht, mille ette uus tööleht tuleb sisestada.

Samamoodi saate lehe lisada ka pärast töölehte (nt Sheet2), kasutades järgmist koodi:

Sub AddSheet () töölehed.Add After: = Töölehed ("Sheet2") End Sub

Kui soovite, et uus leht lisataks lehtede lõppu, peate kõigepealt teadma, kui palju lehti on. Järgmine kood loendab kõigepealt lehtede arvu ja lisab uue lehe pärast viimast lehte (millele viitame indeksinumbri abil).

Sub AddSheet () Dim SheetCount Asger SheetCount = Töölehed. Arvutage töölehed. Lisage pärast: = Töölehed (SheetCount) Lõpu alam

Töölehe kustutamine

Allolev kood kustutaks aktiivse lehe töövihikust.

Alam DeleteSheet () ActiveSheet. Kustuta End Sub

Ülaltoodud kood kuvab enne töölehe kustutamist hoiatusviiba.

Kui te ei soovi hoiatusteadet näha, kasutage allolevat koodi:

Alam DeleteSheet () Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = Tõeline lõpp

Kui Application.DisplayAlerts on seatud väärtusele False, ei kuvata teile hoiatusviiba. Kui kasutate seda, pidage meeles, et määrate selle koodi lõpus tagasi väärtusele Tõene.

Pidage meeles, et seda kustutamist ei saa tagasi võtta, seega kasutage ülaltoodud koodi, kui olete selles täiesti kindel.

Kui soovite konkreetse lehe kustutada, saate seda teha järgmise koodi abil:

Alam DeleteSheet () Töölehed ("Sheet2"). Kustuta End Sub

Lehe kustutamiseks võite kasutada ka selle koodnime.

Sub DeleteSheet () Sheet 5. Kustuta End Sub

Töölehtede ümbernimetamine

Töölehe nime omadust saate muuta selle nime muutmiseks.

Järgmine kood muudab lehe 1 nimeks „Kokkuvõte”.

Sub RenameSheet () Töölehed ("Sheet1"). Name = "Summary" End Sub

Saate selle kombineerida lehe lisamise meetodiga, et saada konkreetsete nimedega lehtede komplekt.

Näiteks kui soovite sisestada neli lehte nimega 2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 ja 2021-2022 Q4, võite kasutada allolevat koodi.

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

Ülaltoodud koodis loendame kõigepealt lehtede arvu ja seejärel kasutame lõpuga Järgmine silmus, et lisada uued lehed lõppu. Lehe lisamisel nimetab kood selle ümber.

Töölehe objekti määramine muutujale

Töölehtedega töötamisel saate määrata töölehe objekti muutujale ja seejärel kasutada muutujat töölehe viitade asemel.

Näiteks kui soovite kõikidele töölehtedele lisada aasta eesliite, saate lehtede loendamise ja silmuse mitu korda käivitamise asemel kasutada objekti muutujat.

Siin on kood, mis lisab kõigi töölehtede nimedele eesliitena 2021-2022.

Sub RenameSheet () Dim Ws kui tööleht iga töölehe Ws jaoks Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

Ülaltoodud kood kuulutab töölehe tüübiks muutuja Ws (kasutades rida „Dim Ws kui tööleht”).

Nüüd ei pea me loendama lehtede arvu, et neid läbi vaadata. Selle asemel võime kasutada tsüklit „Iga töölehtede töö jaoks”. See võimaldab meil läbi vaadata kõik töölehtede kogu lehed. Pole tähtis, kas on 2 lehte või 20 lehte.

Kuigi ülaltoodud kood võimaldab meil kõiki lehti läbi vaadata, saate muutujale määrata ka konkreetse lehe.

Allolevas koodis määrame Sheet2 -le muutuja Ws ja kasutame seda kõigi Sheet2 atribuutide juurde pääsemiseks.

Sub RenameSheet () Dim Ws as Workheet Set Ws = Töölehed ("Sheet2") Ws.Name = "Kokkuvõte" Ws.Protect End Sub

Kui olete töölehe viite seadistanud objekti muutujale (kasutades lauset SET), saab seda objekti kasutada töölehe viite asemel. See võib olla kasulik, kui teil on pikk keeruline kood ja soovite viidet muuta. Selle asemel, et igal pool muudatust teha, saate muudatuse teha lihtsalt SET -avalduses.

Pange tähele, et kood deklareerib Ws -objekti töölehe tüübi muutujana (kasutades töölehena rida Dim Ws).

Peida töölehed VBA abil (peidetud + väga peidetud)

Töölehtede peitmine ja peitmine Excelis on lihtne ülesanne.

Saate töölehe peita ja kasutaja ei näe seda töövihiku avamisel. Siiski saavad nad hõlpsalt töölehe peita, kui paremklõpsavad mis tahes lehe vahekaarti.

Aga mis siis, kui te ei soovi, et nad saaksid töölehte (lehti) peita.

Seda saate teha VBA abil.

Allolev kood peidaks kõik töövihiku töölehed (välja arvatud aktiivne leht), nii et te ei saa selle peitmist tühistada, paremklõpsates lehe nimel.

Sub HideAllExcetActiveSheet () Dim Ws kui tööleht iga töö jaoks selles töövihikus. Töölehed kui Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden Next Ws End Sub

Ülaltoodud koodis muudetakse atribuut Ws.Visible väärtuseks xlSheetVeryHidden.

Kui atribuudi Visible väärtuseks on määratud xlSheetVisible, on leht nähtav töölehe alal (töölehe vahekaartidena).
Kui atribuudi Visible väärtuseks on määratud xlSheetHidden, on leht peidetud, kuid kasutaja saab selle peitmise tühistada, paremklõpsates mis tahes lehe vahekaardil.
Kui atribuudi Visible väärtuseks on määratud xlSheetVeryHidden, on leht peidetud ja seda ei saa töölehe alalt peita. Selle peitmiseks peate kasutama VBA -koodi või atribuutide akent.

Kui soovite lihtsalt peita lehti, mida saab hõlpsasti peita, kasutage allolevat koodi:

Sub HideAllExceptActiveSheet () Dim Ws kui tööleht iga töö jaoks selles töövihikus. Töölehed kui Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetHidden Next Ws End Sub

Allolev kood avaldaks kõik töölehed (nii peidetud kui ka väga peidetud).

Sub UnhideAllWoksheets () Dim Ws kui tööleht iga töö jaoks selles töövihikus. Töölehed Ws.Visible = xlSheetVisible Next Ws End Sub

Seotud artikkel: Avage Excelis kõik lehed (korraga)

Peida lehed selle teksti põhjal

Oletame, et teil on mitu lehte erinevate osakondade või aastate nimega ja soovite peita kõik lehed, välja arvatud need, millel on aasta 2021–2022.

Seda saate teha funktsiooni VBA INSTR abil.

Allolev kood peidaks kõik lehed, välja arvatud need, millel on tekst 2021-2022.

Sub HideWithMatchingText () Dim Ws töölehena iga töölehe Ws jaoks Kui InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Siis Ws.Visible = xlSheetHidden End If Next Ws End Sub

Ülaltoodud koodis tagastab funktsioon INSTR tähemärgi asukoha, kust see leiab sobiva stringi. Kui see ei leia sobivat stringi, tagastab see 0.

Ülaltoodud kood kontrollib, kas nimes on tekst 2021-2022. Kui see juhtub, ei juhtu midagi, vastasel juhul on tööleht peidetud.

Saate selle sammu võrra edasi minna, kui hoiate teksti lahtris ja kasutate seda lahtrit koodis. See võimaldab teil lahtris väärtust omada ja makro käivitamisel jäävad kõik lehed, välja arvatud leht, millel on vastav tekst, nähtaval (koos lehtedega, kuhu sisestate väärtuse kamber).

Töölehtede sortimine tähestikulises järjekorras

VBA abil saate töölehti kiiresti sortida nende nimede alusel.

Näiteks kui teil on töövihik, mis sisaldab lehti erinevate osakondade või aastate kohta, saate alloleva koodi abil neid lehti kiiresti sortida kasvavas järjekorras.

Alam SortSheetsTabName () Application.ScreenUpdating = Vale Dim ShCount täisarvuna, i täisarvuna, j As Integer ShCount = Sheets.Count For i = 1 to ShCount - 1 j = i + 1 ShCount if Sheets (j). Nimi < Arvutustabelid (i). Nimi Seejärel Arvutustabelid (j). Liigu enne: = Lehed (i) Lõpeta, kui järgmine j Järgmine i rakendus. ScreenUpdating = Tõeline lõpp

Pange tähele, et see kood sobib hästi tekstinimedega ning enamikul juhtudel ka aastate ja numbritega. Kuid see võib anda teile valesid tulemusi, kui teil on lehtede nimed 1,2, 11. See sorteerib ja annab teile järjestuse 1, 11, 2. Seda seetõttu, et see võrdleb teksti ja peab 2 suuremaks kui 11.

Kaitske/eemaldage kõik lehed korraga

Kui teil on töövihikus palju töölehti ja soovite kõiki lehti kaitsta, saate kasutada allolevat VBA -koodi.

See võimaldab määrata koodi parooli. Seda parooli vajate töölehe kaitse tühistamiseks.

Sub ProtectAllSheets () Dim ws kui tööleht Dim parooli hämardamine kui String password = "Test123" 'asendage Test123 soovitud parooliga Iga ws töölehel ws.Kaitse parool: = parool Next ws End Sub

Järgmine kood tühistaks kõigi lehtede kaitse korraga.

Sub ProtectAllSheets () Dim ws kui tööleht Dim dim kui String password = "Test123" 'asendage Test123 parooliga, mida kasutasite kaitses Iga ws töölehtedel ws.Kaitse parool: = parool Järgmine ws End Sub

Kõigi töölehtede sisukorra loomine (hüperlinkidega)

Kui teil on töövihikus töölehtede komplekt ja soovite kiiresti lisada kokkuvõtte lehe, millel on lingid kõikidele lehtedele, võite kasutada allolevat koodi.

Sub AddIndexSheet () Töölehed.Add ActiveSheet.Name = "Index" I = 2 jaoks Töölehed.Loe ActiveSheet.Hyperlinks.Add Ankur: = Lahtrid (i - 1, 1), _ Aadress: = "", Alam aadress: = Töölehed (i) .Nimi & "! A1", _ TextToDisplay: = Töölehed (i). Nimi Järgmine i Lõpp Alam

Ülaltoodud kood lisab uue töölehe ja nimetab selle indeksiks.

Seejärel loeb see läbi kõik töölehed ja loob hüperlingi kõikidele indekslehe töölehtedele.

Kuhu panna VBA kood

Huvitav, kuhu läheb teie Exceli töövihikus VBA -kood?

Excelil on VBA taustaprogramm, mida nimetatakse VBA redaktoriks. Peate koodi kopeerima ja kleepima VB Editori mooduli koodi aknasse.

Selleks toimige järgmiselt.

Minge vahekaardile Arendaja.
Klõpsake suvandil Visual Basic. See avab taustaprogrammis VB redaktori.
Paremklõpsake VB redaktori Project Exploreri paanil töövihiku mis tahes objekti, kuhu soovite koodi sisestada. Kui te ei näe Project Explorerit, minge vahekaardile Vaade ja klõpsake nuppu Project Explorer.
Minge sisesta ja klõpsake moodulit. See lisab teie töövihikusse mooduliobjekti.
Kopeerige ja kleepige kood mooduli aknasse.