Rad s radnim listovima pomoću programa Excel VBA (objašnjeno s primjerima)

Osim ćelija i raspona, rad s radnim listovima još je jedno područje o kojem biste trebali znati da biste učinkovito koristili VBA u Excelu.

Kao i svaki objekt u VBA -u, radni listovi imaju različita svojstva i metode povezane s njima koje možete koristiti dok automatizirate svoj rad s VBA -om u Excelu.

U ovom ću vodiču detaljno obraditi 'Radne listove' i također vam pokazati neke praktične primjere.

Pa krenimo.

Svi kodovi koje spominjem u ovom vodiču moraju se staviti u VB Editor. Idite na odjeljak "Gdje staviti VBA kôd" da biste saznali kako to funkcionira.

Ako ste zainteresirani za učenje VBA na jednostavan način, provjerite moj Mrežna Excel VBA obuka.

Razlika između radnih listova i listova u VBA -i

U VBA -u imate dvije zbirke koje ponekad mogu biti pomalo zbunjujuće.

U radnoj knjizi možete imati radne listove, kao i listove grafikona. Donji primjer ima tri radna lista i jedan list grafikona.

U Excelu VBA:

• Zbirka "Radni listovi" odnosila bi se na zbirku svih objekata radnog lista u radnoj knjizi. U gornjem primjeru zbirka radnih listova sastojala bi se od tri radna lista.
• Zbirka "Tablice" odnosila bi se na sve radne listove, kao i listove grafikona u radnoj knjizi. U gornjem primjeru imao bi četiri elementa - 3 radna lista + 1 list grafikona.

Ako imate radnu knjigu koja ima samo radne listove, a nema listova grafikona, tada su zbirke "Radni listovi" i "Tablice" iste.

No, kada imate jedan ili više listova grafikona, zbirka "Tablice" bila bi veća od zbirke "Radni listovi"

Listovi = Radni listovi + Listovi grafikona

S ovom razlikom, preporučujem da budete što precizniji pri pisanju VBA koda.

Dakle, ako se morate pozivati ​​samo na radne listove, upotrijebite zbirku "Radni listovi", a ako se morate odnositi na sve listove (uključujući listove grafikona), upotrijebite zbirku "Tablice".

U ovom ću vodiču koristiti samo zbirku "Radni listovi".

Referenciranje radnog lista u VBA

Postoji mnogo različitih načina na koje se možete pozvati na radni list u VBA -i.

Razumijevanje upućivanja na radne listove pomoglo bi vam u pisanju boljeg koda, osobito kada koristite petlje u svom VBA kodu.

Korištenje naziva radnog lista

Najjednostavniji način pozivanja na radni list je korištenje njegovog naziva.

Na primjer, pretpostavimo da imate radnu knjigu s tri radna lista - list 1, list 2, list 3.

I želite aktivirati List 2.

To možete učiniti pomoću sljedećeg koda: Sub ActivateSheet () Radni listovi ("Sheet2"). Aktivirajte End Sub

Gornji kod traži od VBA da se pozove na Sheet2 u zbirci Worksheets i aktivira ga.

Budući da koristimo točan naziv lista, ovdje možete koristiti i zbirku Sheets. Dakle, donji kod bi također učinio istu stvar.

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

Korištenje indeksnog broja

Iako je korištenje naziva lista jednostavan način pozivanja na radni list, ponekad možda ne znate točan naziv radnog lista.

Na primjer, ako koristite VBA kôd za dodavanje novog radnog lista u radnu knjigu, a ne znate koliko radnih listova već postoji, ne biste znali naziv novog radnog lista.

U tom slučaju možete koristiti indeksni broj radnih listova.

Pretpostavimo da imate sljedeće listove u radnoj knjizi:

Kod u nastavku aktivirao bi Sheet2:

Sub ActivateSheet () Radni listovi (2) .Aktivirajte End Sub

Imajte na umu da smo koristili indeks broj 2 u Radni listovi (2). To bi se odnosilo na drugi objekt u zbirci radnih listova.

Što se događa kada koristite 3 kao indeksni broj?

Odabrat će List 3.

Ako se pitate zašto je odabrao Sheet3, jer je očito četvrti objekt.

To se događa jer list grafikona nije dio zbirke radnih listova.

Dakle, kada koristimo indeksne brojeve u zbirci Radni listovi, to će se odnositi samo na radne listove u radnoj knjizi (i zanemariti listove grafikona).

Naprotiv, ako koristite tablice, tablice (1) bi se odnosile na tablice1, tablice (2) bi se odnosile na tablicu2, tablice (3) bi se odnosile na tablicu1, a tablice (4) bi se odnosile na tablicu3.

Ova tehnika korištenja indeksnog broja korisna je kada želite proći kroz sve radne listove u radnoj knjizi. Možete izbrojati broj radnih listova, a zatim ih pregledati pomoću ovog brojača (vidjet ćemo kako to učiniti kasnije u ovom vodiču).

Napomena: Broj indeksa ide slijeva nadesno. Dakle, ako pomaknete List2 lijevo od lista 1, tada bi se radni listovi (1) odnosili na list 2.

Korištenje šifriranog naziva radnog lista

Jedan od nedostataka korištenja naziva lista (kao što smo vidjeli u gornjem odjeljku) je to što ga korisnik može promijeniti.

A ako je promijenjen naziv lista, vaš kôd ne bi radio dok ne promijenite naziv radnog lista i u VBA kodu.

Da biste riješili ovaj problem, možete koristiti kodni naziv radnog lista (umjesto uobičajenog naziva koje smo dosad koristili). Kodni naziv može se dodijeliti u VB Editoru i ne mijenja se ako promijenite naziv lista iz područja radnog lista.

Da biste svom radnom listu dali kôdni naziv, slijedite korake u nastavku:

1. Kliknite karticu Developer.
2. Pritisnite gumb Visual Basic. Ovo će otvoriti VB Editor.
3. Na izborniku kliknite opciju Pogled i kliknite na prozor Projekt. Ovo će okno Svojstva učiniti vidljivim. Ako je okno Svojstva već vidljivo, preskočite ovaj korak.
4. Kliknite na naziv lista u istraživaču projekata koje želite preimenovati.
5. U oknu Svojstva promijenite naziv u polju ispred (Naziv). Imajte na umu da u nazivu ne možete imati razmake.

Gore navedeni koraci promijenili bi naziv vašeg radnog lista u VBA pozadini. U prikazu radnog lista programa Excel radnom listu možete dati ime kako god želite, ali u pozadini će odgovoriti na oba imena - naziv lista i kodni naziv.

Na gornjoj slici naziv lista je 'SheetName', a kodni naziv je 'CodeName'. Čak i ako promijenite naziv lista na radnom listu, kodni naziv ostaje isti.

Sada možete upotrijebiti bilo zbirku Radni listovi za pozivanje na radni list ili koristiti kodno ime.

Na primjer, oba retka aktivirat će radni list.

Radni listovi ("Naziv lista"). Aktivirajte CodeName.Aktivirajte

Razlika u ova dva je u tome što ako promijenite naziv radnog lista, prvi neće funkcionirati. No, drugi redak nastavio bi raditi čak i s promijenjenim imenom. Drugi redak (koristeći CodeName) također je kraći i lakši za korištenje.

Pozivanje na radni list u drugoj radnoj knjizi

Ako se želite uputiti na radni list u drugoj radnoj knjizi, ta radna knjiga mora biti otvorena dok se kôd izvodi, a vi morate navesti naziv radne knjige i radni list na koji se želite uputiti.

Na primjer, ako imate radnu knjigu s imenom Primjeri i želite aktivirati Sheet1 u Primjernoj radnoj knjizi, morate upotrijebiti donji kod:

Sub SheetActivate () Radne knjige ("Primjeri.xlsx"). Radni listovi ("Sheet1"). Aktiviraj End Sub

Imajte na umu da ako je radna knjiga spremljena, morate koristiti naziv datoteke zajedno s nastavkom. Ako niste sigurni koje ime koristiti, potražite pomoć od Project Explorera.

U slučaju da radna knjiga nije spremljena, ne morate koristiti proširenje datoteke.

Dodavanje radnog lista

Kod u nastavku dodao bi radni list (kao prvi radni list - tj. Kao krajnji lijevi list na kartici lista).

Sub AddSheet () Radni listovi.Dodaj End Sub

Uzima zadani naziv Sheet2 (ili bilo koji drugi broj ovisno o tome koliko listova već postoji).

Ako želite da se radni list doda prije određenog radnog lista (recimo Sheet2), tada možete upotrijebiti donji kod.

Sub AddSheet () Radni listovi. Dodaj prije: = Radni listovi ("Sheet2") Kraj Sub

Gornji kôd govori VBA -i da doda list, a zatim koristi izraz 'Prije' za navođenje radnog lista prije kojeg treba umetnuti novi radni list.

Slično, možete dodati i list nakon radnog lista (recimo Sheet2), koristeći donji kod:

Sub AddSheet () Radni listovi. Dodajte poslije: = Radni listovi ("Sheet2") Kraj pod

Ako želite da se novi list dodaje na kraj listova, najprije morate znati koliko ima listova. Sljedeći kôd prvo broji broj listova i dodaje novi list nakon posljednjeg lista (na koji se pozivamo pomoću indeksnog broja).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Radni listovi.Broj radnih listova.Dodaj poslije: = Radni listovi (SheetCount) Kraj pod

Brisanje radnog lista

Kod u nastavku izbrisao bi aktivni list iz radne knjige.

Sub Izbriši tablicu () ActiveSheet.Delete End Sub

Gornji kôd bi prikazao upozorenje prije brisanja radnog lista.

Ako ne želite vidjeti upozorenje, upotrijebite donji kod:

Sub DeleteSheet () Application.DisplayAlerts = Netačno ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Kad je Application.DisplayAlerts postavljeno na False, neće vam prikazati upit upozorenja. Ako ga koristite, ne zaboravite ga vratiti na True na kraju koda.

Zapamtite da ovo brisanje ne možete poništiti, pa upotrijebite gornji kôd kada ste potpuno sigurni.

Ako želite izbrisati određeni list, to možete učiniti pomoću sljedećeg koda:

Sub DeleteSheet () Radni listovi ("Sheet2"). Delete End Sub

Za brisanje možete upotrijebiti i kodni naziv lista.

Sub Izbriši list () List5.Delete End Sub

Preimenovanje radnih listova

Svojstvo naziva radnog lista možete izmijeniti da biste promijenili njegov naziv.

Sljedeći kôd promijenit će naziv lista 1 u 'Sažetak'.

Sub RenameSheet () Radni listovi ("Sheet1"). Name = "Summary" End Sub

To možete kombinirati s metodom dodavanja listova kako biste imali skup listova s ​​određenim imenima.

Na primjer, ako želite umetnuti četiri lista s imenom2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 i 2021-2022 Q4, možete upotrijebiti donji kod.

Podimenovanje tablice () Zatamnjivanje tablica kao cjelobrojnih tablica = Radni listovi.Prebrojavanje za i = 1 do 4 radnih listova. Dodano nakon: = Radni listovi (tablice + i - 1) Radni listovi (tablice + i). Ime = "2018 Q" & i Sljedeće i Kraj podm

U gornjem kodu prvo brojimo broj listova, a zatim pomoću petlje For Next za umetanje novih listova na kraju. Kako se list dodaje, kôd ga također mijenja.

Dodjeljivanje objekta radnog lista varijabli

Kada radite s radnim listovima, radni list možete dodijeliti varijabli objekta, a zatim koristiti varijablu umjesto referenci radnog lista.

Na primjer, ako želite dodati prefiks godine na sve radne listove, umjesto brojanja listova i pokretanja petlje toliko puta, možete koristiti varijablu objekta.

Ovdje je kôd koji će dodati 2021-2022 kao prefiks svim nazivima radnog lista.

Sub RenameSheet () Dim Ws kao radni list za svaki Ws u radnim listovima Ws.Name = "2018 -" & Ws.Name Sljedeći Ws Završni pod

Gornji kod deklarira varijablu Ws kao tip radnog lista (koristeći redak "Dim Ws As Worksheet").

Ne moramo brojati broj listova da bismo ih prošli. Umjesto toga, možemo koristiti petlju "Za svaki W u radnim listovima". To će nam omogućiti pregled svih listova u zbirci radnih listova. Nije važno postoje li 2 lista ili 20 listova.

Iako nam gornji kôd omogućuje da prođemo kroz sve listove, varijabli možete dodijeliti i određeni list.

U donjem kodu dodjeljujemo varijablu Ws Sheet2 i koristimo je za pristup svim svojstvima Sheet2.

Sub RenameSheet () Zatamni W kao skup radnih listova Ws = Radni listovi ("List2") Ws.Name = "Sažetak" Ws.Zaštita završne podvrste

Nakon što postavite referencu radnog lista na varijablu objekta (pomoću izraza SET), taj se objekt može koristiti umjesto reference radnog lista. To može biti korisno ako imate dugačak kompliciran kôd i želite promijeniti referencu. Umjesto da mijenjate svugdje, jednostavno možete unijeti promjenu u izraz SET.

Imajte na umu da kod deklarira objekt Ws kao varijablu tipa Radni list (koristeći redak Dim Ws kao Radni list).

Sakrij radne listove pomoću VBA (skriveno + vrlo skriveno)

Skrivanje i otkrivanje radnih listova u Excelu jednostavan je zadatak.

Radni list možete sakriti i korisnik ga neće vidjeti kada otvori radnu knjigu. Međutim, oni mogu lako otkriti radni list desnim klikom na bilo koju karticu lista.

Ali što ako ne želite da mogu otkriti radne listove.

To možete učiniti pomoću VBA.

Donji kôd sakrio bi sve radne listove u radnoj knjizi (osim aktivnog lista), tako da ga ne možete otkriti desnim klikom na naziv lista.

Sub HideAllExcetActiveSheet () Zatamni W kao radni list za svaki Ws u ovoj radnoj knjizi.Radni listovi Ako Ws.Name ActiveSheet.Name Zatim Ws.Visible = xlSheetVeryHidden Sljedeći WS End Sub

U gornjem kodu svojstvo Ws.Visible mijenja se u xlSheetVeryHidden.

• Kad je svojstvo Visible postavljeno na xlSheetVisible, list je vidljiv u području radnog lista (kao kartice radnog lista).
• Kad je svojstvo Vidljivo postavljeno na xlSheetHidden, list je skriven, ali ga korisnik može otkriti desnim klikom na bilo koju karticu lista.
• Kad je svojstvo Vidljivo postavljeno na xlSheetVeryHidden, list je skriven i ne može se sakriti iz područja radnog lista. Morate upotrijebiti VBA kôd ili prozor svojstava da biste ga otkrili.

Ako želite jednostavno sakriti listove koji se mogu lako otkriti, upotrijebite donji kod:

Pod HideAllExceptActiveSheet () Zatamni W kao radni list za svaki W u ovoj radnoj knjizi.Radni listovi Ako Ws.Ime ActiveSheet.Name Zatim Ws.Visible = xlSheetHidden Sljedeći Ws End Sub

Kod ispod bi otkrio sve radne listove (skrivene i vrlo skrivene).

Sub UnhideAllWoksheets () Zatamni W kao radni list za svaki Ws u ovoj radnoj knjizi.Radni listovi Ws.Visible = xlSheetVisible Sljedeći Ws End Sub

Vezani članak: Otkrivanje svih listova u Excelu (odjednom)

Sakrij tablice na temelju teksta u njoj

Pretpostavimo da imate više listova s ​​nazivima različitih odjela ili godina i želite sakriti sve listove osim onih u kojima je godina 2021-2022.

To možete učiniti pomoću funkcije VBA INSTR.

Kod ispod bi sakrio sve listove osim onih s tekstom2021-2022.

Sub HideWithMatchingText () Zatamni W kao radni list za svaki W u radnim listovima Ako je InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Tada je Ws.Visible = xlSheetHidden End Ako Sljedeći WS završi pod

U gornjem kodu, funkcija INSTR vraća poziciju znaka gdje nalazi odgovarajući niz. Ako ne pronađe odgovarajući niz, vraća 0.

Gornji kod provjerava ima li naziv tekst 2021-2022. Ako se to dogodi, ništa se neće dogoditi, inače je radni list skriven.

Ovo možete učiniti korak dalje tako što ćete tekst staviti u ćeliju i koristiti tu ćeliju u kodu. To će vam omogućiti da imate vrijednost u ćeliji, a zatim će, kada pokrenete makronaredbu, svi listovi, osim onog s odgovarajućim tekstom, ostati vidljivi (zajedno s listovima u koje unosite vrijednost u stanica).

Sortiranje radnih listova po abecednom redu

Pomoću VBA možete brzo sortirati radne listove prema njihovim nazivima.

Na primjer, ako imate radnu knjigu koja ima listove za različite odjele ili godine, tada možete upotrijebiti donji kod za brzo sortiranje ovih listova uzlaznim redoslijedom.

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). Naziv < Sheets (i) .Naziv Zatim Sheets (j) .Prijemjesti prije: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub

Imajte na umu da ovaj kôd dobro funkcionira s nazivima teksta, a u većini slučajeva i s godinama i brojevima. Ali može vam dati pogrešne rezultate u slučaju da imate nazive listova kao 1,2,11. Sortirat će vam i dati niz 1, 11, 2. To je zato što uspoređuje tekst i smatra 2 većim od 11.

Zaštitite/uklonite zaštitu svih listova odjednom

Ako imate mnogo radnih listova u radnoj knjizi i želite zaštititi sve listove, možete upotrijebiti donji VBA kôd.

Omogućuje vam da odredite lozinku unutar koda. Ova će vam lozinka trebati za uklanjanje zaštite radnog lista.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'zamijenite Test123 lozinkom koju želite Za svaki ws U radnim listovima ws.Zaštita lozinke: = lozinka Sljedeća ws Kraj pod

Sljedeći kôd uklonio bi zaštitu svih listova odjednom.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'zamijenite Test123 lozinkom koju ste koristili pri zaštiti Za svaki ws U radnim listovima ws.Unprotect lozinka: = lozinka Sljedeća ws Krajnja pod

Izrada sadržaja svih radnih listova (s hipervezama)

Ako imate skup radnih listova u radnoj knjizi i želite brzo umetnuti sažetak s poveznicama na sve listove, možete upotrijebiti donji kod.

Sub AddIndexSheet () Radni listovi.Dodajte ActiveSheet.Name = "Indeks" Za i = 2 na radne listove. Prebrojite ActiveSheet.Hyperlinks.Dodajte sidro: = Ćelije (i - 1, 1), _ Adresa: = "", Podadresa: = Radni listovi (i). Naziv & "! A1", _ TextToDisplay: = Radni listovi (i). Naziv Sljedeći i Kraj pod

Gornji kôd ubacuje novi radni list i daje mu naziv Index.

Zatim se petlja kroz sve radne listove i stvara hipervezu za sve radne listove na indeksnom listu.

Gdje staviti VBA kod

Pitate se gdje se VBA kôd nalazi u vašoj radnoj knjizi programa Excel?

Excel ima VBA pozadinu koja se naziva VBA editor. Morate kopirati i zalijepiti kôd u prozor koda modula VB Editor.

Evo koraka za to:

1. Idite na karticu Developer.
2. Kliknite na opciju Visual Basic. Ovo će otvoriti VB editor u pozadini.
3. U oknu Project Explorer u VB Editoru desnom tipkom miša kliknite bilo koji objekt radne knjige u koji želite umetnuti kôd. Ako ne vidite Project Explorer, idite na karticu View i kliknite na Project Explorer.
4. Idite na Umetni i kliknite na Modul. Ovo će umetnuti objekt modula u vašu radnu knjigu.
5. Kopirajte i zalijepite kôd u prozor modula.

Možda će vam se svidjeti i sljedeći Excel VBA vodiči:

• Rad s radnim bilježnicama pomoću VBA.
• Korištenje IF Onda drugih izjava u VBA.
• Za sljedeću petlju u VBA.
• Stvaranje korisnički definirane funkcije u Excelu.
• Kako snimiti makro u Excelu.
• Kako pokrenuti makro u Excelu.
• Excel VBA događaji - jednostavan (i potpun) vodič.
• Kako stvoriti dodatak u Excelu.
• Kako spremiti i ponovno upotrijebiti makro pomoću Excel osobne radne knjige za makroe.