Darbs ar darblapām, izmantojot Excel VBA (izskaidrots ar piemēriem)

Papildus šūnām un diapazoniem darbs ar darblapām ir vēl viena joma, kas jums jāzina par efektīvu VBA izmantošanu programmā Excel.

Tāpat kā jebkuram objektam VBA, darblapām ir ar to saistītas dažādas īpašības un metodes, kuras varat izmantot, automatizējot darbu ar VBA programmā Excel.

Šajā apmācībā es detalizēti aplūkošu “darblapas” un parādīšu dažus praktiskus piemērus.

Tātad sāksim darbu.

Visi kodi, kurus es minēju šajā apmācībā, ir jāievieto VB redaktorā. Dodieties uz sadaļu “Kur ievietot VBA kodu”, lai uzzinātu, kā tas darbojas.

Ja jūs interesē vienkāršs VBA apguves veids, apskatiet manu Tiešsaistes Excel VBA apmācība.

Atšķirība starp darblapām un lapām VBA

VBA jums ir divas kolekcijas, kas reizēm var būt nedaudz mulsinošas.

Darbgrāmatā var būt darblapas un diagrammu lapas. Tālāk redzamajā piemērā ir trīs darblapas un viena diagrammas lapa.

Programmā Excel VBA:

• Kolekcija “Darblapas” attiecas uz visu darbgrāmatas darblapu objektu kolekciju. Iepriekš minētajā piemērā darblapu kolekcija sastāv no trim darblapām.
• Kolekcija “Loksnes” attiektos uz visām darblapām, kā arī uz darbgrāmatas diagrammu lapām. Iepriekš minētajā piemērā tam būtu četri elementi - 3 darblapas + 1 diagrammas lapa.

Ja jums ir darbgrāmata, kurā ir tikai darblapas un nav diagrammu lapu, tad kolekcija “Darblapas” un “Izklājlapas” ir vienāda.

Bet, ja jums ir viena vai vairākas diagrammu lapas, “Sheets” kolekcija būtu lielāka nekā “Worksheets” kolekcija

Lapas = darblapas + diagrammu lapas

Tagad ar šo atšķirību iesaku rakstīt VBA kodu pēc iespējas precīzāk.

Tātad, ja jums ir jāattiecas tikai uz darblapām, izmantojiet kolekciju “Darblapas”, un, ja jums ir jāatsaucas uz visām lapām (ieskaitot diagrammu lapas), izmantojiet kolekciju “Izklājlapas”.

Šajā apmācībā es izmantošu tikai darblapu kolekciju.

Atsauces uz darblapu VBA

Ir daudz dažādu veidu, kā varat atsaukties uz darblapu VBA.

Izpratne par to, kā atsaukties uz darblapām, palīdzētu jums uzrakstīt labāku kodu, it īpaši, ja VBA kodā izmantojat cilpas.

Izmantojot darblapas nosaukumu

Vienkāršākais veids, kā atsaukties uz darblapu, ir izmantot tā nosaukumu.

Piemēram, pieņemsim, ka jums ir darbgrāmata ar trim darblapām - 1. lapa, 2. lapa, 3. lapa.

Un jūs vēlaties aktivizēt 2. lapu.

To var izdarīt, izmantojot šādu kodu: Sub ActivateSheet () Darblapas ("Sheet2").

Iepriekš minētais kods lūdz VBA atsaukties uz lapu Sheet2 darblapu kolekcijā un to aktivizēt.

Tā kā mēs izmantojam precīzu lapas nosaukumu, šeit varat izmantot arī izklājlapu kolekciju. Tātad zemāk esošais kods darītu to pašu.

Sub ActivateSheet () Sheets ("Sheet2"). Aktivizējiet Beigu apakš

Indeksa numura izmantošana

Lai gan lapas nosaukuma izmantošana ir vienkāršs veids, kā atsaukties uz darblapu, dažreiz jūs, iespējams, nezināt precīzu darblapas nosaukumu.

Piemēram, ja izmantojat VBA kodu jaunas darblapas pievienošanai darbgrāmatai un nezināt, cik darblapu jau ir, jūs nezināt jaunās darblapas nosaukumu.

Šajā gadījumā varat izmantot darblapu indeksa numuru.

Pieņemsim, ka darbgrāmatā ir šādas lapas:

Zemāk esošais kods aktivizēs lapu Sheet2:

Sub ActivateSheet () Darblapas (2). Aktivizējiet End Sub

Ņemiet vērā, ka mēs esam izmantojuši indeksa numuru 2 collas Darba lapas (2). Tas attiektos uz otro darblapu kolekcijas objektu.

Kas notiek, ja kā indeksa numuru izmantojat 3?

Tas atlasīs lapu Sheet3.

Ja jums rodas jautājums, kāpēc tā izvēlējās lapu 3, jo tas acīmredzami ir ceturtais objekts.

Tas notiek tāpēc, ka diagrammu lapa nav darblapu kolekcijas sastāvdaļa.

Tātad, ja mēs izmantojam indeksu numurus darblapu kolekcijā, tas attiecas tikai uz darbgrāmatas darblapām (un ignorē diagrammu lapas).

Gluži pretēji, ja izmantojat izklājlapas, lapas (1) attiecas uz izklājlapām1, lapas (2) uz lapu 2, lapas (3) attiecas uz 1. diagrammu un lapas (4) attiecas uz lapu 3.

Šī indeksa numura izmantošanas metode ir noderīga, ja vēlaties apskatīt visas darbgrāmatas darblapas. Jūs varat saskaitīt darblapu skaitu un pēc tam tos izmantot, izmantojot šo skaitli (kā to izdarīt, mēs redzēsim vēlāk šajā apmācībā).

Piezīme. Indeksa numurs iet no kreisās uz labo. Tātad, ja jūs pārvietojat lapu 2 pa kreisi no lapas 1, tad darblapas (1) attiecas uz lapu 2.

Izmantojot darblapas koda nosaukumu

Viens no lapas nosaukuma izmantošanas trūkumiem (kā redzējām iepriekšējā sadaļā) ir tas, ka lietotājs to var mainīt.

Un, ja lapas nosaukums ir mainīts, jūsu kods nedarbosies, kamēr nemainīsit darblapas nosaukumu arī VBA kodā.

Lai atrisinātu šo problēmu, varat izmantot darblapas koda nosaukumu (līdz šim izmantotā parastā nosaukuma vietā). Koda nosaukumu var piešķirt VB redaktorā, un tas nemainās, mainot lapas nosaukumu no darblapas apgabala.

Lai savai darblapai piešķirtu koda nosaukumu, veiciet tālāk norādītās darbības.

1. Noklikšķiniet uz cilnes Izstrādātājs.
2. Noklikšķiniet uz pogas Visual Basic. Tas atvērs VB redaktoru.
3. Izvēlnē noklikšķiniet uz opcijas Skatīt un noklikšķiniet uz Projekta logs. Tādējādi īpašumu rūts būs redzama. Ja rekvizītu rūts jau ir redzama, izlaidiet šo darbību.
4. Projekta izpētē noklikšķiniet uz lapas nosaukuma, kuru vēlaties pārdēvēt.
5. Rūtī Rekvizīti mainiet nosaukumu laukā, kas atrodas pirms (Nosaukums). Ņemiet vērā, ka nosaukumā nedrīkst būt atstarpes.

Iepriekš minētās darbības mainītu darblapas nosaukumu VBA aizmugurē. Excel darblapas skatā jūs varat nosaukt darblapu, kā vien vēlaties, bet aizmugurē tā atbildēs gan uz nosaukumiem - lapas nosaukumu, gan koda nosaukumu.

Iepriekš redzamajā attēlā lapas nosaukums ir “SheetName” un koda nosaukums ir “CodeName”. Pat ja darblapā maināt lapas nosaukumu, koda nosaukums paliek nemainīgs.

Tagad varat izmantot darblapu kolekciju, lai atsauktos uz darblapu, vai izmantot koda nosaukumu.

Piemēram, abas rindas aktivizēs darblapu.

Darblapas ("Lapas nosaukums"). Aktivizēt CodeName.Activate

Šo divu atšķirība ir tāda, ka, mainot darblapas nosaukumu, pirmā nedarbosies. Bet otrā rinda turpinātu darboties pat ar mainīto nosaukumu. Arī otrā rinda (izmantojot koda nosaukumu) ir īsāka un vieglāk lietojama.

Atsauce uz darblapu citā darbgrāmatā

Ja vēlaties atsaukties uz darblapu citā darbgrāmatā, šai darbgrāmatai ir jābūt atvērtai, kamēr kods darbojas, un jānorāda darbgrāmatas nosaukums un darblapa, uz kuru vēlaties atsaukties.

Piemēram, ja jums ir darbgrāmata ar nosaukumu Piemēri un vēlaties aktivizēt lapu 1. darbgrāmatā, jums jāizmanto tālāk norādītais kods.

ApakšlapaAktivēt () Darbgrāmatas ("Piemēri.xlsx"). Darblapas ("Lapa1"). Aktivizēt beigu apakšdaļu

Ņemiet vērā: ja darbgrāmata ir saglabāta, kopā ar paplašinājumu jāizmanto faila nosaukums. Ja neesat pārliecināts, kādu vārdu izmantot, lūdziet palīdzību no Project Explorer.

Ja darbgrāmata nav saglabāta, jums nav jāizmanto faila paplašinājums.

Darblapas pievienošana

Zemāk esošais kods pievienotu darblapu (kā pirmo darblapu - t.i., kā lapas cilnes kreisāko lapu).

Sub AddSheet () darblapas. Pievienot End Sub

Tam ir noklusējuma nosaukums Sheet2 (vai jebkurš cits numurs, pamatojoties uz to, cik lapas jau ir).

Ja vēlaties, lai darblapa tiktu pievienota pirms konkrētas darblapas (piemēram, Sheet2), varat izmantot tālāk norādīto kodu.

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

Iepriekš minētais kods liek VBA pievienot lapu un pēc tam izmanto paziņojumu “Pirms”, lai norādītu darblapu, pirms kuras jāievieto jaunā darblapa.

Līdzīgi jūs varat arī pievienot lapu pēc darblapas (piemēram, Sheet2), izmantojot tālāk norādīto kodu.

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

Ja vēlaties, lai jaunā lapa tiktu pievienota lapu beigās, vispirms jāzina, cik lapu ir. Šis kods vispirms uzskaita lapu skaitu un pievieno jauno lapu pēc pēdējās lapas (uz kuru mēs atsaucamies, izmantojot indeksa numuru).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Darblapas. Darblapu skaits. Pievienot pēc: = Darblapas (SheetCount) Beigu apakšdaļa

Darblapas dzēšana

Zemāk esošais kods izdzēsīs aktīvo lapu no darbgrāmatas.

Sub DeleteSheet () ActiveSheet. Dzēst beigu apakšdaļu

Iepriekš minētais kods pirms darblapas dzēšanas parādītu brīdinājuma uzvedni.

Ja nevēlaties redzēt brīdinājuma uzvedni, izmantojiet tālāk norādīto kodu.

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

Ja lietojumprogrammai Application.DisplayAlerts ir iestatīta vērtība False, tā nerādīs brīdinājuma uzvedni. Ja to izmantojat, koda beigās noteikti iestatiet to uz True.

Atcerieties, ka šo dzēšanu nevar atsaukt, tāpēc izmantojiet iepriekš minēto kodu, kad esat pilnīgi pārliecināts.

Ja vēlaties izdzēst noteiktu lapu, varat to izdarīt, izmantojot šādu kodu:

Sub DeleteSheet () Darblapas ("Sheet2"). Dzēst End Sub

Varat arī izmantot lapas koda nosaukumu, lai to izdzēstu.

Sub DeleteSheet () Sheet 5. Dzēst End Sub

Darblapu pārdēvēšana

Jūs varat mainīt darblapas rekvizīta nosaukumu, lai mainītu tā nosaukumu.

Šis kods mainīs lapas 1 nosaukumu uz “Kopsavilkums”.

Apakšnosaukums RenameSheet () Darblapas ("Sheet1"). Name = "Summary" End Sub

Varat to apvienot ar lapas pievienošanas metodi, lai iegūtu lapu kopu ar konkrētiem nosaukumiem.

Piemēram, ja vēlaties ievietot četras lapas ar nosaukumu2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 un 2021-2022 Q4, varat izmantot tālāk norādīto kodu.

Apakšnosaukums 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 Beigu apakš

Iepriekš minētajā kodā mēs vispirms saskaitām lapu skaitu un pēc tam izmantojam cilpu Tālāk, lai beigās ievietotu jaunas lapas. Kad lapa tiek pievienota, kods to arī pārdēvē.

Darblapas objekta piešķiršana mainīgajam

Strādājot ar darblapām, varat piešķirt darblapu objekta mainīgajam un pēc tam izmantot mainīgo, nevis darblapas atsauces.

Piemēram, ja vēlaties visām darba lapām pievienot gada prefiksu, tā vietā, lai skaitītu lapas un daudzkārt palaistu cilpu, varat izmantot objekta mainīgo.

Šeit ir kods, kas pievienos 2021-2022 kā priedēkli visiem darblapas nosaukumiem.

Sub RenameSheet () Dim Ws kā darblapa katram Ws darblapās Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

Iepriekš minētais kods deklarē mainīgo Ws kā darblapas veidu (izmantojot rindu “Dim Ws kā darblapa”).

Tagad mums nav jāuzskaita lapu skaits, lai tās pārvietotu. Tā vietā mēs varam izmantot cilpu “Katram darblapu W”. Tas ļaus mums iziet visas darba lapu kolekcijas lapas. Nav svarīgi, vai ir 2 lapas vai 20 lapas.

Lai gan iepriekš minētais kods ļauj mums apskatīt visas lapas, mainīgajam varat piešķirt arī noteiktu lapu.

Zemāk esošajā kodā mēs piešķiram mainīgo Ws lapai2 un izmantojam to, lai piekļūtu visiem lapas 2 īpašumiem.

Apakšnosaukums RenameSheet () Dim Ws kā darblapas kopa Ws = Darblapas ("Sheet2") Ws.Name = "Kopsavilkums" Ws.Protect End Sub

Kad esat iestatījis darblapas atsauci uz objekta mainīgo (izmantojot SET paziņojumu), šo objektu var izmantot darblapas atsauces vietā. Tas var būt noderīgi, ja jums ir garš sarežģīts kods un vēlaties mainīt atsauci. Tā vietā, lai veiktu izmaiņas visur, varat vienkārši veikt izmaiņas SET paziņojumā.

Ņemiet vērā, ka kods deklarē Ws objektu kā darblapas tipa mainīgo (kā rindu Dim Ws kā darblapu).

Paslēpt darblapas, izmantojot VBA (slēpts + ļoti slēpts)

Darblapu paslēpšana un parādīšana programmā Excel ir vienkāršs uzdevums.

Jūs varat paslēpt darblapu, un lietotājs to neredzēs, atverot darbgrāmatu. Tomēr viņi var viegli parādīt darblapu, ar peles labo pogu noklikšķinot uz jebkuras lapas cilnes.

Bet ko darīt, ja nevēlaties, lai viņi varētu parādīt darblapu (-as)?

To var izdarīt, izmantojot VBA.

Tālāk esošais kods paslēptu visas darbgrāmatas darblapas (izņemot aktīvo lapu), lai nevarētu to parādīt, ar peles labo pogu noklikšķinot uz lapas nosaukuma.

Sub HideAllExcetActiveSheet () Dim Ws kā darblapa katrai WS šajā darbgrāmatā.

Iepriekš minētajā kodā īpašums Ws.Visible tiek mainīts uz xlSheetVeryHidden.

• Ja rekvizīts Visible ir iestatīts uz xlSheetVisible, lapa ir redzama darblapas apgabalā (kā darblapas cilnes).
• Ja rekvizīts Visible ir iestatīts uz xlSheetHidden, lapa ir paslēpta, bet lietotājs to var paslēpt, ar peles labo pogu noklikšķinot uz jebkuras lapas cilnes.
• Ja rekvizīts Visible ir iestatīts uz xlSheetVeryHidden, lapa ir paslēpta un to nevar atcelt darblapas apgabalā. Lai to parādītu, jums jāizmanto VBA kods vai rekvizītu logs.

Ja vēlaties vienkārši paslēpt lapas, kuras var viegli paslēpt, izmantojiet tālāk norādīto kodu.

Sub HideAllExceptActiveSheet () Dim Ws kā darblapa katrai WS šajā darbgrāmatā.

Zemāk esošais kods parādītu visas darblapas (gan slēptās, gan ļoti slēptās).

Sub UnhideAllWoksheets () Dim Ws kā darblapa katram WS šajā darba grāmatā. Darblapas Ws.Visible = xlSheetVisible Next Ws End Sub

Saistīts raksts: Parādīt visas Excel lapas (vienā reizē)

Slēpt lapas, pamatojoties uz tajā esošo tekstu

Pieņemsim, ka jums ir vairākas lapas ar dažādu nodaļu vai gadu nosaukumu un vēlaties paslēpt visas lapas, izņemot tās, kurās ir gads 2021–2022.

To var izdarīt, izmantojot funkciju VBA INSTR.

Zemāk esošais kods slēptu visas lapas, izņemot tās, kurās ir teksts2021-2022.

Sub HideWithMatchingText () Dim Ws kā darblapa katram darblapā esošajam WS

Iepriekš minētajā kodā funkcija INSTR atgriež rakstzīmes pozīciju, kurā tā atrod atbilstošo virkni. Ja tā neatrod atbilstošo virkni, tā atgriež 0.

Iepriekš minētais kods pārbauda, ​​vai nosaukumā ir teksts2021-2022. Ja tā notiek, nekas nenotiek, pretējā gadījumā darblapa ir paslēpta.

Jūs varat spert šo soli tālāk, ievietojot tekstu šūnā un izmantojot šo šūnu kodā. Tādējādi šūnā būs vērtība, un tad, palaižot makro, visas lapas, izņemot lapu, kurā ir atbilstošais teksts, paliek redzamas (kopā ar lapām, kurās ievadāt vērtību šūna).

Darblapu kārtošana alfabētiskā secībā

Izmantojot VBA, varat ātri kārtot darblapas, pamatojoties uz to nosaukumiem.

Piemēram, ja jums ir darbgrāmata, kurā ir dažādu nodaļu vai gadu lapas, varat izmantot zemāk esošo kodu, lai ātri kārtotu šīs lapas augošā secībā.

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). Nosaukums < Izklājlapas (i). Nosaukums Pēc tam Izklājlapas (j). Pārvietot pirms: = Izklājlapas (i) Beigt, ja nākamā j Nākamā i lietojumprogramma. ScreenUpdating = Patiesa beigu apakšdaļa

Ņemiet vērā, ka šis kods labi darbojas ar teksta nosaukumiem un vairumā gadījumu arī ar gadiem un cipariem. Bet tas var dot jums nepareizus rezultātus, ja lapu nosaukumi ir 1,2,11. Tas sakārtos un sniegs jums secību 1, 11, 2. Tas ir tāpēc, ka salīdzinājums tiek veikts kā teksts un 2 uzskata par lielāku par 11.

Aizsargājiet/noņemiet visu lapu aizsardzību vienā reizē

Ja darbgrāmatā ir daudz darblapu un vēlaties aizsargāt visas lapas, varat izmantot tālāk norādīto VBA kodu.

Tas ļauj kodā norādīt paroli. Šī parole būs nepieciešama, lai noņemtu darblapas aizsardzību.

Sub ProtectAllSheets () Dim ws kā darblapa Dim parole kā virknes parole = "Test123" "aizstājiet Test123 ar paroli, kuru vēlaties katram ws darblapās ws. Aizsargāt paroli: = parole Next ws End Sub

Šis kods noņemtu visu lapu aizsardzību vienā piegājienā.

Sub ProtectAllSheets () Dim ws kā darblapa Dim parole kā virknes parole = "Test123" "aizstājiet Test123 ar paroli, kuru izmantojāt, aizsargājot Katram ws darblapās ws.Aizsargāt paroli: = parole Next ws End Sub

Visu darblapu satura rādītāja izveide (ar hipersaitēm)

Ja darbgrāmatā ir darblapu komplekts un vēlaties ātri ievietot kopsavilkuma lapu, kurā ir saites uz visām lapām, varat izmantot tālāk norādīto kodu.

Sub AddIndexSheet () Worksheets.Add ActiveSheet.Name = "Index" For i = 2 to Worksheets.Count ActiveSheet.Hipersaites.Add Anchor: = Šūnas (i - 1, 1), _ Address: = "", SubAddress: = Worksheets. (i) .Nosaukums & "! A1", _ TextToDisplay: = Darblapas (i). Nosaukums Tālāk i Beigu apakšdaļa

Iepriekš minētais kods ievieto jaunu darblapu un nosauc to par indeksu.

Pēc tam tas pārmeklē visas darblapas un izveido hipersaiti visām rādītāja lapas darblapām.

Kur ievietot VBA kodu

Vai vēlaties uzzināt, kur jūsu Excel darbgrāmatā atrodas VBA kods?

Programmai Excel ir VBA aizmugure, ko sauc par VBA redaktoru. Jums ir nepieciešams kopēt un ielīmēt kodu VB redaktora moduļa koda logā.

Lai to izdarītu, veiciet tālāk norādītās darbības.

1. Dodieties uz cilni Izstrādātājs.
2. Noklikšķiniet uz opcijas Visual Basic. Tas atvērs VB redaktoru aizmugurē.
3. VB redaktora Project Explorer rūtī ar peles labo pogu noklikšķiniet uz jebkura darbgrāmatas objekta, kurā vēlaties ievietot kodu. Ja neredzat Project Explorer, dodieties uz cilni Skats un noklikšķiniet uz Project Explorer.
4. Dodieties uz Ievietot un noklikšķiniet uz moduļa. Tādējādi jūsu darbgrāmatai tiks ievietots moduļa objekts.
5. Kopējiet un ielīmējiet kodu moduļa logā.

Jums varētu patikt arī šādas Excel VBA apmācības:

• Darbs ar darbgrāmatām, izmantojot VBA.
• IF un citu paziņojumu izmantošana VBA.
• Nākamajai cilpai VBA.
• Lietotāja definētas funkcijas izveide programmā Excel.
• Kā ierakstīt makro programmā Excel.
• Kā palaist makro programmā Excel.
• Excel VBA notikumi - vienkāršs (un pilnīgs) ceļvedis.
• Kā izveidot pievienojumprogrammu programmā Excel.
• Kā saglabāt un atkārtoti izmantot makro, izmantojot Excel personīgo makro darbgrāmatu.