Delo z delovnimi listi z uporabo Excela VBA (razloženo s primeri)

Delo z delovnimi listi je poleg celic in obsegov še eno področje, ki bi ga morali poznati za učinkovito uporabo VBA v Excelu.

Tako kot kateri koli predmet v VBA imajo tudi delovni listi povezane različne lastnosti in metode, ki jih lahko uporabite pri avtomatizaciji dela z VBA v Excelu.

V tej vadnici bom podrobno obravnaval 'Delovne liste' in vam pokazal tudi nekaj praktičnih primerov.

Pa začnimo.

Vse kode, ki jih omenjam v tej vadnici, je treba postaviti v urejevalnik VB. Pojdite na razdelek »Kam postaviti kodo VBA«, če želite izvedeti, kako deluje.

Če vas zanima učenje VBA na enostaven način, si oglejte moj Spletno usposabljanje Excel VBA.

Razlika med delovnimi listi in listi v VBA

V VBA imate dve zbirki, ki sta včasih lahko nekoliko zmedeni.

V delovnem zvezku imate lahko delovne liste in liste z grafikoni. Spodnji primer ima tri delovne liste in en list grafikona.

V Excelu VBA:

• Zbirka "Delovni listi" bi se nanašala na zbirko vseh predmetov delovnega lista v delovnem zvezku. V zgornjem primeru bi zbirko delovnih listov sestavljali trije delovni listi.
• Zbirka "Preglednice" bi se nanašala na vse delovne liste in liste z grafikoni v delovnem zvezku. V zgornjem primeru bi imel štiri elemente - 3 delovne liste + 1 list grafikona.

Če imate delovni zvezek, ki vsebuje le delovne liste in ne listov grafikonov, sta zbirka »Delovni listi« in »List« enaki.

Če pa imate enega ali več listov grafikonov, bi bila zbirka "List" večja od zbirke "Worksheets"

Listi = Delovni listi + Listi grafikonov

Zdaj s to razliko priporočam, da ste pri pisanju kode VBA čim bolj natančni.

Torej, če se morate sklicevati samo na delovne liste, uporabite zbirko "Delovni listi", če pa se morate sklicevati na vse liste (vključno z listi grafikonov), uporabite zbirko "List".

V tej vadnici bom uporabljal samo zbirko "Delovni listi".

Sklicevanje na delovni list v VBA

Za sklicevanje na delovni list v VBA lahko uporabite veliko različnih načinov.

Razumevanje, kako se sklicevati na delovne liste, bi vam pomagalo napisati boljšo kodo, še posebej, če uporabljate zanke v kodi VBA.

Uporaba imena delovnega lista

Najlažji način za sklicevanje na delovni list je uporaba njegovega imena.

Recimo, da imate delovni zvezek s tremi delovnimi listi - list 1, list 2, list 3.

In želite aktivirati List 2.

To lahko storite z naslednjo kodo: Sub ActivateSheet () Delovni listi ("Sheet2"). Aktivirajte End Sub

Zgornja koda od VBA zahteva, da se sklicuje na Sheet2 v zbirki Worksheets in jo aktivira.

Ker uporabljamo natančno ime lista, lahko tukaj uporabite tudi zbirko listov. Torej bi spodnja koda naredila isto stvar.

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

Uporaba indeksne številke

Čeprav je ime lista preprost način za sklicevanje na delovni list, včasih morda ne veste natančnega imena delovnega lista.

Če na primer uporabljate kodo VBA za dodajanje novega delovnega lista v delovni zvezek in ne veste, koliko delovnih listov je že tam, ne boste vedeli imena novega delovnega lista.

V tem primeru lahko uporabite indeksno številko delovnih listov.

Recimo, da imate v delovnem zvezku naslednje liste:

Spodnja koda bi aktivirala Sheet2:

Sub ActivateSheet () Delovni listi (2) .Aktivirajte End Sub

Upoštevajte, da smo uporabili indeks številka 2 v Delovni listi (2). To bi se nanašalo na drugi predmet v zbirki delovnih listov.

Kaj se zgodi, če za indeksno številko uporabite 3?

Izbral bo list 3.

Če se sprašujete, zakaj je izbral Sheet3, saj je očitno četrti predmet.

To se zgodi, ker list z grafikonom ni del zbirke delovnih listov.

Ko torej uporabljamo indeksne številke v zbirki Delovni listi, se bo to nanašalo le na delovne liste v delovnem zvezku (in prezrlo liste z grafikoni).

Nasprotno, če uporabljate liste, bi se listi (1) nanašali na liste 1, listi (2) bi se nanašali na list 2, listi (3) bi se nanašali na grafikon 1 in listi (4) bi se nanašali na list 3.

Ta tehnika uporabe indeksne številke je uporabna, če želite prelistati vse delovne liste v delovnem zvezku. Lahko preštejete število delovnih listov in jih nato prelistate s tem štetjem (kasneje bomo v tem vodiču videli, kako to storiti).

Opomba: Indeksna številka gre od leve proti desni. Če torej list 2 premaknete levo od lista 1, se bodo delovni listi (1) nanašali na list 2.

Uporaba kodnega imena delovnega lista

Ena od pomanjkljivosti uporabe imena lista (kot smo videli v zgornjem razdelku) je, da ga lahko uporabnik spremeni.

Če je bilo ime lista spremenjeno, vaša koda ne bi delovala, dokler ne spremenite tudi imena delovnega lista v kodi VBA.

Za reševanje te težave lahko uporabite kodno ime delovnega lista (namesto običajnega imena, ki smo ga uporabljali doslej). Kodo ime lahko dodelite v urejevalniku VB in se ne spremeni, če spremenite ime lista iz območja delovnega lista.

Če želite svojemu delovnemu listu dati kodno ime, sledite spodnjim korakom:

1. Kliknite zavihek Razvijalec.
2. Kliknite gumb Visual Basic. S tem se odpre urejevalnik VB.
3. V meniju kliknite možnost Pogled in nato okno projekta. Tako bo podokno Lastnosti vidno. Če je podokno Lastnosti že vidno, preskočite ta korak.
4. V raziskovalcu projektov kliknite ime lista, ki ga želite preimenovati.
5. V podoknu Lastnosti spremenite ime v polju pred (Ime). Upoštevajte, da v imenu ne morete imeti presledkov.

Zgornji koraki bi spremenili ime vašega delovnega lista v ozadju VBA. V pogledu Excelovega delovnega lista lahko delovni list poimenujete, kar želite, v zaledju pa se bo odzval na obe imeni - ime lista in kodno ime.

Na zgornji sliki je ime lista "SheetName", kodno ime pa "CodeName". Tudi če spremenite ime lista na delovnem listu, kodno ime ostane enako.

Zdaj lahko uporabite zbirko delovnih listov za sklicevanje na delovni list ali uporabite kodno ime.

Na primer, obe vrstici aktivirata delovni list.

Delovni listi ("Ime lista"). Aktivirajte CodeName.Aktivirajte

Razlika v teh dveh je v tem, da če spremenite ime delovnega lista, prvi ne bi deloval. Toda druga vrstica bi delovala tudi s spremenjenim imenom. Druga vrstica (z uporabo kodnega imena) je tudi krajša in enostavnejša za uporabo.

Sklicevanje na delovni list v drugem delovnem zvezku

Če se želite sklicevati na delovni list v drugem delovnem zvezku, mora biti ta delovni zvezek odprt, medtem ko se koda izvaja, podati pa morate ime delovnega zvezka in delovnega lista, na katerega se želite sklicevati.

Na primer, če imate delovni zvezek z imenom Primeri in želite aktivirati Sheet1 v Primernem delovnem zvezku, morate uporabiti spodnjo kodo:

Sub SheetActivate () Delovni zvezki ("Primeri.xlsx"). Delovni listi ("Sheet1"). Aktiviraj End Sub

Če je delovni zvezek shranjen, morate skupaj z razširitvijo uporabiti ime datoteke. Če niste prepričani, katero ime uporabiti, poiščite pomoč v Project Explorerju.

Če delovni zvezek ni shranjen, vam ni treba uporabiti razširitve datoteke.

Dodajanje delovnega lista

Spodnja koda bi dodala delovni list (kot prvi delovni list - torej kot skrajni levi list na zavihku lista).

Sub AddSheet () delovni listi. Dodajte End Sub

Vzame privzeto ime Sheet2 (ali katero koli drugo številko glede na to, koliko listov je že tam).

Če želite, da se delovni list doda pred določen delovni list (recimo Sheet2), lahko uporabite spodnjo kodo.

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

Zgornja koda pove VBA, naj doda list, nato pa z izjavo 'Pred' določi delovni list, pred katerega naj se vstavi nov delovni list.

Podobno lahko dodate list po delovnem listu (recimo Sheet2) z uporabo spodnje kode:

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

Če želite, da se nov list doda na konec listov, morate najprej vedeti, koliko listov je tam. Naslednja koda najprej šteje število listov in doda nov list po zadnjem listu (na katerega se nanašamo s številko indeksa).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Delovni listi. Štetje delovnih listov. Dodaj po: = Delovni listi (SheetCount) Konec pod

Brisanje delovnega lista

Spodnja koda bi izbrisala aktivni list iz delovnega zvezka.

Sub DeleteSheet () ActiveSheet.Delete End Sub

Zgornja koda bi pred izbrisom delovnega lista prikazala opozorilo.

Če ne želite videti opozorila, uporabite spodnjo kodo:

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

Ko je Application.DisplayAlerts nastavljeno na False, se ne prikaže opozorilo. Če ga uporabljate, ga ne pozabite nastaviti na True na koncu kode.

Ne pozabite, da tega brisanja ne morete razveljaviti, zato uporabite zgornjo kodo, ko ste popolnoma prepričani.

Če želite izbrisati določen list, lahko to storite z naslednjo kodo:

Sub DeleteSheet () Delovni listi ("Sheet2"). Izbriši End Sub

Za brisanje lahko uporabite tudi kodno ime lista.

Sub DeleteSheet () Sheet 5. Izbriši End Sub

Preimenovanje delovnih listov

Lastnost imena delovnega lista lahko spremenite, da spremenite njegovo ime.

Naslednja koda bo spremenila ime lista 1 v „Povzetek“.

Sub RenameSheet () Delovni listi ("Sheet1"). Name = "Summary" End Sub

To lahko kombinirate z metodo dodajanja listov, da dobite nabor listov s posebnimi imeni.

Če želite na primer vstaviti štiri liste z imenom2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 in 2021-2022 Q4, lahko uporabite spodnjo kodo.

Sub RenameSheet () Dim Countheets As Integer Countheets = Worksheets.Count For i = 1 To 4 Worksheets.Added after: = Worksheets (Countheets + i - 1) Worksheets (Countheets + i) .Name = "2018 Q" & i Naprej i End Sub

V zgornji kodi najprej preštejemo število listov in nato z zanko For Next vstavimo nove liste na koncu. Ko se list doda, ga koda tudi preimenuje.

Dodelitev predmeta delovnega lista spremenljivki

Pri delu z delovnimi listi lahko delovni list dodelite spremenljivki objekta in nato uporabite spremenljivko namesto sklicev na delovni list.

Če želite na primer vsem delovnim listom dodati predpono za leto, lahko namesto štetja listov in izvajanja zanke tolikokrat uporabite spremenljivko objekta.

Tu je koda, ki bo dodala 2021-2022 kot predpono vsem imenom delovnega lista.

Sub RenameSheet () Dim Ws kot delovni list za vsak Ws na delovnih listih Ws.Name = "2018 -" & Ws.Name Naslednji Ws End Sub

Zgornja koda razglasi spremenljivko Ws kot vrsto delovnega lista (z uporabo vrstice »Dim Ws kot delovni list«).

Zdaj nam ni treba šteti števila listov, da bi jih prebrali. Namesto tega lahko uporabimo zanko »Za vsak W v delovnih listih«. Tako bomo lahko pregledali vse liste v zbirki delovnih listov. Ni važno, ali sta 2 lista ali 20 listov.

Medtem ko nam zgornja koda omogoča prehod skozi vse liste, lahko spremenljivki dodelite tudi določen list.

V spodnji kodi spremenljivko Ws dodelimo Sheet2 in z njo dostopamo do vseh lastnosti Sheet2.

Sub RenameSheet () Zatemni Ws kot nastavitev delovnega lista Ws = delovni listi ("List2") Ws.Name = "Povzetek" Ws. Zaščiti konec pod

Ko nastavite sklic na delovni list na spremenljivko objekta (z uporabo stavka SET), lahko ta predmet uporabite namesto sklica na delovni list. To je lahko v pomoč, če imate dolgo zapleteno kodo in želite spremeniti sklic. Namesto da bi spreminjali povsod, lahko preprosto naredite spremembo v stavku SET.

Upoštevajte, da koda razglasi objekt Ws kot spremenljivko tipa delovnega lista (z uporabo vrstice Dim Ws kot delovni list).

Skrij delovne liste z uporabo VBA (skrito + zelo skrito)

Skrivanje in odkrivanje delovnih listov v Excelu je enostavna naloga.

Delovni list lahko skrijete in uporabnik ga ne vidi, ko odpre delovni zvezek. Lahko pa preprosto razkrijejo delovni list tako, da z desno miškino tipko kliknejo kateri koli zavihek lista.

Kaj pa, če ne želite, da lahko razkrijejo delovne liste.

To lahko storite z uporabo VBA.

Spodnja koda bi skrila vse delovne liste v delovnem zvezku (razen aktivnega lista), tako da jih ne morete razkriti z desnim klikom na ime lista.

Sub HideAllExcetActiveSheet () Zatemni W kot delovni list za vsako WS v tem delovnem zvezku.

V zgornji kodi se lastnost Ws.Visible spremeni v xlSheetVeryHidden.

• Ko je lastnost Visible nastavljena na xlSheetVisible, je list viden v območju delovnega lista (kot zavihki delovnega lista).
• Ko je lastnost Visible nastavljena na xlSheetHidden, je list skrit, vendar ga lahko uporabnik razkrije tako, da z desno tipko miške klikne kateri koli zavihek lista.
• Ko je lastnost Visible nastavljena na xlSheetVeryHidden, je list skrit in ga ni mogoče skriti iz območja delovnega lista. Za razkritje morate uporabiti kodo VBA ali okno lastnosti.

Če želite preprosto skriti liste, ki jih je mogoče enostavno skriti, uporabite spodnjo kodo:

Sub HideAllExceptActiveSheet () Zatemni W kot delovni list za vsako WS v tem delovnem zvezku.

Spodnja koda bi razkrila vse delovne liste (skrite in zelo skrite).

Sub UnhideAllWoksheets () Zatemni W kot delovni list za vsak Ws v tem delovnem zvezku. Delovni listi Ws.Visible = xlSheetVisible Naslednji Ws End Sub

Sorodni članek: Razkrij vse liste v Excelu (naenkrat)

Skrij liste na podlagi besedila v njem

Recimo, da imate več listov z imeni različnih oddelkov ali let in želite skriti vse liste, razen tistih, v katerih je leto 2021-2022.

To lahko storite s funkcijo VBA INSTR.

Spodnja koda bi skrila vse liste, razen tistih z besedilom 2021-2022.

Sub HideWithMatchingText () Zatemni W kot delovni list za vsak W na delovnih listih Če je InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Potem je Ws.Visible = xlSheetHidden End Če naslednji Ws End Sub

V zgornji kodi funkcija INSTR vrne položaj znaka, kjer najde ujemajoči se niz. Če ne najde ujemajočega se niza, vrne 0.

Zgornja koda preveri, ali ime vsebuje besedilo 2021-2022. Če se to zgodi, se nič ne zgodi, drugače je delovni list skrit.

To lahko naredite še korak dlje, tako da imate besedilo v celici in uporabite to celico v kodi. To vam bo omogočilo, da imate vrednost v celici in potem, ko zaženete makro, bodo vsi listi, razen tistega z ustreznim besedilom, ostali vidni (skupaj z listi, kjer vnesete vrednost v polje celica).

Razvrščanje delovnih listov po abecednem redu

Z uporabo VBA lahko hitro razvrstite delovne liste glede na njihova imena.

Če imate na primer delovni zvezek z listi za različne oddelke ali leta, lahko s spodnjo kodo hitro razvrstite te liste v naraščajočem vrstnem redu.

Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 Za ShCount - 1 Za j = i + 1 Za ShCount Če listi (j). Ime < Sheets (i) .Name then Sheets (j) .Premakni se prej: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub

Upoštevajte, da ta koda dobro deluje z imeni besedil in v večini primerov tudi z leti in številkami. Lahko pa vam da napačne rezultate, če imate imena listov 1,2,11. Razvrstil in dal vam bo zaporedje 1, 11, 2. To je zato, ker primerja kot besedilo in meni, da je 2 večje od 11.

Zaščitite/odstranite zaščito vseh listov naenkrat

Če imate v delovnem zvezku veliko delovnih listov in želite zaščititi vse liste, lahko uporabite spodnjo kodo VBA.

Omogoča vam, da v kodi določite geslo. To geslo boste potrebovali za odstranitev zaščite delovnega lista.

Sub ProtectAllSheets () Zatemni ws kot delovni list Zatemni geslo kot geslo niza = "Test123" 'zamenjajte Test123 z geslom, ki ga želite Za vsak ws na delovnih listih ws.Zaščitite geslo: = geslo Naslednji ws Konec pod

Naslednja koda bi odstranila zaščito vseh listov naenkrat.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'nadomestite Test123 z geslom, ki ste ga uporabili pri zaščiti Za vsak ws na delovnih listih ws.Unprotect password: = geslo Next ws End Sub

Ustvarjanje kazala vseh delovnih listov (s hiperpovezavami)

Če imate v delovnem zvezku nabor delovnih listov in želite hitro vstaviti povzetek, ki vsebuje povezave do vseh listov, lahko uporabite spodnjo kodo.

Sub AddIndexSheet () delovni listi.Add ActiveSheet.Name = "Index" For i = 2 to Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor: = Cells (i - 1, 1), _ Address: = "", SubAddress: = Worksheets (i) .Ime & "! A1", _ TextToDisplay: = Delovni listi (i). Ime Naprej i Konec pod

Zgornja koda vstavi nov delovni list in ga poimenuje Indeks.

Nato ponovi vse delovne liste in ustvari hiperpovezavo za vse delovne liste na listu kazala.

Kam vstaviti kodo VBA

Se sprašujete, kje je koda VBA v vašem Excelovem delovnem zvezku?

Excel ima zaledje VBA, imenovano urejevalnik VBA. Kodo morate kopirati in prilepiti v okno kode modula VB Editor.

Tu so naslednji koraki:

1. Pojdite na zavihek Razvijalec.
2. Kliknite možnost Visual Basic. To bo odprlo urejevalnik VB v zaledju.
3. V podoknu Raziskovalec projektov v urejevalniku VB z desno tipko miške kliknite kateri koli predmet delovnega zvezka, v katerega želite vstaviti kodo. Če ne vidite Raziskovalca, pojdite na zavihek Pogled in kliknite Raziskovalec projektov.
4. Pojdite na Vstavi in ​​kliknite Modul. S tem boste v svoj delovni zvezek vstavili objekt modula.
5. Kodo kopirajte in prilepite v okno modula.

Morda vam bodo všeč tudi naslednji vadnici Excel VBA:

• Delo z delovnimi zvezki z uporabo VBA.
• Uporaba IF Nato drugih stavkov v VBA.
• Za naslednjo zanko v VBA.
• Ustvarjanje uporabniško določene funkcije v Excelu.
• Kako posneti makro v Excelu.
• Kako zagnati makro v Excelu.
• Dogodki Excel VBA - enostaven (in popoln) vodnik.
• Kako ustvariti dodatek v Excelu.
• Kako shraniti in znova uporabiti makro z Excelovim osebnim delovnim zvezkom za makre.