Darbas su darbalapiais naudojant „Excel VBA“ (paaiškinta pavyzdžiais)

Be langelių ir diapazonų, darbas su darbalapiais yra dar viena sritis, kurią turėtumėte žinoti apie efektyvų VBA naudojimą „Excel“.

Kaip ir bet kuris VBA objektas, darbalapiai turi skirtingas su juo susijusias savybes ir metodus, kuriuos galite naudoti automatizuodami savo darbą su VBA programoje „Excel“.

Šioje pamokoje išsamiai aprašysiu „darbalapius“ ir parodysiu keletą praktinių pavyzdžių.

Taigi pradėkime.

Visi kodai, kuriuos paminėjau šioje pamokoje, turi būti dedami į VB redaktorių. Eikite į skyrių „Kur įdėti VBA kodą“, kad sužinotumėte, kaip jis veikia.

Jei jus domina lengvas VBA mokymasis, patikrinkite mano Internetinis „Excel VBA“ mokymas.

Skirtumas tarp darbalapių ir lakštų VBA

VBA turite dvi kolekcijas, kurios kartais gali būti šiek tiek painios.

Darbaknygėje galite turėti darbalapius ir diagramų lapus. Žemiau pateiktame pavyzdyje yra trys darbalapiai ir vienas diagramos lapas.

„Excel VBA“:

• Kolekcija „Darbo lapai“ reiškia visų darbalapio darbalapio objektų kolekciją. Pirmiau pateiktame pavyzdyje darbalapių kolekciją sudarytų trys darbalapiai.
• Kolekcija „Lakštai“ apimtų visus darbaknygės darbalapius ir diagramų lapus. Anksčiau pateiktame pavyzdyje jis turėtų keturis elementus - 3 darbalapius + 1 diagramos lapą.

Jei turite darbaknygę, kurioje yra tik darbalapiai ir nėra diagramų lapų, tada „darbalapių“ ir „lapų“ kolekcija yra ta pati.

Bet jei turite vieną ar daugiau diagramų lapų, „Lakštų“ kolekcija būtų didesnė nei „Darbo lapų“ kolekcija

Lakštai = darbalapiai + diagramų lapai

Dabar, turėdamas šį skirtumą, rekomenduoju būti kuo konkretesnis rašant VBA kodą.

Taigi, jei turite nurodyti tik darbalapius, naudokite „Darbo lapų“ kolekciją, o jei turite nurodyti visus lapus (įskaitant diagramų lapus), naudokite „Lakštų“ kolekciją.

Šioje pamokoje naudosiu tik „Darbo lapų“ kolekciją.

Nuoroda į darbalapį VBA

Yra daug skirtingų būdų, kuriais galite kreiptis į darbalapį VBA.

Supratimas, kaip kreiptis į darbalapius, padėtų parašyti geresnį kodą, ypač kai VBA kode naudojate kilpas.

Naudojant darbalapio pavadinimą

Lengviausias būdas kreiptis į darbalapį yra naudoti jo pavadinimą.

Pvz., Tarkime, kad turite darbo knygą su trimis darbalapiais - 1 lapas, 2 lapas, 3 lapas.

Ir norite suaktyvinti 2 lapą.

Tai galite padaryti naudodami šį kodą: Sub ActivateSheet () Worksheets ("Sheet2"). Activate End Sub

Aukščiau pateiktas kodas prašo VBA kreiptis į „Sheet2“ darbalapių kolekcijoje ir ją suaktyvinti.

Kadangi naudojame tikslų lapo pavadinimą, čia taip pat galite naudoti Skaičiuoklių kolekciją. Taigi žemiau pateiktas kodas taip pat atliktų tą patį.

Antrasis „ActivateSheet“ () skaičiuoklės („Sheet2“). Suaktyvinkite „End Sub“

Indekso numerio naudojimas

Nors lapo pavadinimo naudojimas yra paprastas būdas kreiptis į darbalapį, kartais gali būti, kad nežinote tikslaus darbalapio pavadinimo.

Pvz., Jei naudojate VBA kodą, kad pridėtumėte naują darbalapį prie darbaknygės ir nežinote, kiek darbalapių jau yra, jūs nežinotumėte naujo darbalapio pavadinimo.

Tokiu atveju galite naudoti darbalapių rodyklės numerį.

Tarkime, kad darbaknygėje yra šie lapai:

Žemiau pateiktas kodas suaktyvins „Sheet2“:

„Sub ActivateSheet“ () darbalapiai (2). Aktyvinkite „End Sub“

Atminkite, kad mes naudojome indekso numerį 2 coliuose Darbo lapai (2). Tai būtų antrasis darbo lapų kolekcijos objektas.

Kas atsitiks, kai rodyklės numeriu naudosite 3?

Jis pasirinks „Sheet3“.

Jei jums įdomu, kodėl jis pasirinko „Sheet3“, nes tai aiškiai ketvirtas objektas.

Taip atsitinka todėl, kad diagramos lapas nėra darbo lapų kolekcijos dalis.

Taigi, kai mes naudojame darbalapių kolekcijos indeksų numerius, tai bus nuoroda tik į darbaknygės darbalapius (ir nepaisys diagramų lapų).

Priešingai, jei naudojate Skaičiuokles, Skaičiuoklės (1) nurodytų Lakštus1, Lakštai (2) - Lentas2, Lakštai (3) - 1 diagramą, o Lakštai (4) - Lapą 3.

Ši rodyklės numerio naudojimo technika yra naudinga, kai norite peržiūrėti visus darbaknygės darbalapius. Galite suskaičiuoti darbalapių skaičių ir tada juos peržiūrėti naudodami šį skaičių (kaip tai padaryti, pamatysime vėliau šioje pamokoje).

Pastaba: Indekso numeris eina iš kairės į dešinę. Taigi, jei perkelsite „Sheet2“ į „Sheet1“ kairę, tada darbalapiai (1) bus susiję su „Sheet2“.

Naudojant darbalapio kodo pavadinimą

Vienas iš lapo pavadinimo naudojimo trūkumų (kaip matėme aukščiau esančiame skyriuje) yra tas, kad vartotojas gali jį pakeisti.

Ir jei lapo pavadinimas buvo pakeistas, jūsų kodas neveiks, kol nepakeisite darbalapio pavadinimo ir VBA kode.

Norėdami išspręsti šią problemą, galite naudoti darbalapio kodinį pavadinimą (vietoj įprasto pavadinimo, kurį naudojome iki šiol). Kodo pavadinimą galima priskirti VB redaktoriuje ir jis nesikeičia, kai pakeičiate lapo pavadinimą iš darbalapio srities.

Norėdami savo darbalapiui suteikti kodo pavadinimą, atlikite šiuos veiksmus:

1. Spustelėkite skirtuką Kūrėjas.
2. Spustelėkite mygtuką „Visual Basic“. Tai atidarys VB redaktorių.
3. Meniu spustelėkite parinktį Rodinys ir spustelėkite Projekto langas. Taip bus matoma ypatybių sritis. Jei ypatybių sritis jau matoma, praleiskite šį veiksmą.
4. Projekto naršyklėje, kurią norite pervardyti, spustelėkite lapo pavadinimą.
5. Srityje „Ypatybės“ pakeiskite pavadinimą lauke, esančiame priešais (Pavadinimas). Atminkite, kad pavadinime negali būti tarpų.

Pirmiau minėti veiksmai pakeistų jūsų darbalapio pavadinimą VBA vidinėje sistemoje. „Excel“ darbalapio rodinyje galite pavadinti darbalapį, kaip norite, tačiau užpakalinėje sistemoje jis atsilieps tiek pavadinimams - lapo pavadinimui, tiek kodo pavadinimui.

Aukščiau esančiame paveikslėlyje lapo pavadinimas yra „SheetName“, o kodo pavadinimas - „CodeName“. Net jei pakeisite lapo pavadinimą darbalapyje, kodo pavadinimas vis tiek išlieka tas pats.

Dabar galite naudoti darbalapių kolekciją, kad nurodytumėte darbalapį, arba naudoti kodinį pavadinimą.

Pavyzdžiui, abi eilutės suaktyvins darbalapį.

Darbo lapai („Lakšto pavadinimas“). Aktyvinti „CodeName.Activate“

Šių dviejų skirtumas yra tas, kad jei pakeisite darbalapio pavadinimą, pirmasis neveiks. Tačiau antroji eilutė ir toliau veiktų net ir pakeitus pavadinimą. Antroji eilutė (naudojant kodo pavadinimą) taip pat yra trumpesnė ir lengviau naudojama.

Nuoroda į darbalapį kitoje darbaknygėje

Jei norite nurodyti darbalapį kitoje darbaknygėje, tą darbaknygę reikia atidaryti, kol vykdomas kodas, ir turite nurodyti darbaknygės pavadinimą ir darbalapį, kurį norite nurodyti.

Pvz., Jei turite darbaknygę pavadinimu „Pavyzdžiai“ ir norite suaktyvinti „Sheet1“ darbaknygėje „Pavyzdys“, turite naudoti žemiau esantį kodą:

Sub SheetActivate () Darbaknygės ("Examples.xlsx"). Darbo lapai ("Sheet1"). Suaktyvinti Pabaigos antraštę

Atminkite, kad jei darbaknygė buvo išsaugota, kartu su plėtiniu turite naudoti failo pavadinimą. Jei nežinote, kokį pavadinimą naudoti, kreipkitės pagalbos į „Project Explorer“.

Jei darbo knyga nebuvo išsaugota, jums nereikia naudoti failo plėtinio.

Darbo lapo pridėjimas

Žemiau pateiktas kodas pridėtų darbalapį (kaip pirmąjį darbalapį, t. Y. Kaip kairįjį lapą skirtuko lape).

Sub AddSheet () darbalapiai. Pridėti pabaigos sub

Jis turi numatytąjį pavadinimą „Sheet2“ (arba bet kurį kitą skaičių, atsižvelgiant į tai, kiek lapų jau yra).

Jei norite, kad darbalapis būtų pridėtas prieš konkretų darbalapį (pvz., „Sheet2“), galite naudoti žemiau esantį kodą.

Sub AddSheet () Worksheets.Add Before: = Darbai („Sheet2“) Pabaigos antraštė

Aukščiau pateiktas kodas nurodo VBA pridėti lapą ir tada naudoja sakinį „Prieš“, kad nurodytų darbalapį, prieš kurį reikia įterpti naują darbalapį.

Panašiai taip pat galite pridėti lapą po darbalapio (pvz., „Sheet2“) naudodami toliau pateiktą kodą:

Sub AddSheet () Worksheets.Add After: = Darbo lapai ("Sheet2") Pabaigos antraštė

Jei norite, kad naujas lapas būtų pridėtas lapų pabaigoje, pirmiausia turite žinoti, kiek lapų yra. Šis kodas pirmiausia skaičiuoja lapų skaičių, o po paskutinio lapo prideda naują lapą (į kurį nurodome naudodami indekso numerį).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Darbai. Skaičiuoti darbalapius. Pridėti po: = Darbai (SheetCount) Pabaigos antraštė

Darbo lapo ištrynimas

Žemiau pateiktas kodas ištrins aktyvų lapą iš darbaknygės.

Sub DeleteSheet () ActiveSheet. Ištrinti pabaigos sub

Pirmiau pateiktas kodas rodytų įspėjamąjį raginimą prieš ištrinant darbalapį.

Jei nenorite matyti įspėjimo raginimo, naudokite žemiau esantį kodą:

Sub DeleteSheet () Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = Tikrosios pabaigos sub

Kai „Application.DisplayAlerts“ nustatyta kaip „False“, įspėjimo raginimas nebus rodomas. Jei jį naudojate, kodo pabaigoje nepamirškite jį nustatyti į „True“.

Atminkite, kad negalite anuliuoti šio ištrynimo, todėl naudokite aukščiau pateiktą kodą, kai esate visiškai tikri.

Jei norite ištrinti konkretų lapą, tai galite padaryti naudodami šį kodą:

Sub DeleteSheet () darbalapiai ("Sheet2"). Ištrinti pabaigos sub

Taip pat galite naudoti lapo kodinį pavadinimą, kad jį ištrintumėte.

Sub DeleteSheet () Sheet 5. Ištrinti pabaigos sub

Darbo lapų pervadinimas

Galite pakeisti darbalapio pavadinimo ypatybę, kad pakeistumėte jo pavadinimą.

Šis kodas pakeis 1 lapo pavadinimą į „Santrauka“.

Sub RenameSheet () darbalapiai ("Sheet1"). Pavadinimas = "Santrauka" Pabaiga

Tai galite derinti su lapo pridėjimo metodu, kad gautumėte lapų rinkinį su konkrečiais pavadinimais.

Pvz., Jei norite įterpti keturis lapus pavadinimu2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 ir 2021-2022 Q4, galite naudoti žemiau esantį kodą.

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 Pabaiga Sub

Pirmiau pateiktame kode pirmiausia suskaičiuojame lapų skaičių, o paskui naudodami ciklą „Kitas“ įterpiame naujus lapus pabaigoje. Pridėjus lapą, kodas taip pat jį pervadina.

Darbo lapo objekto priskyrimas kintamajam

Dirbdami su darbalapiais, galite priskirti darbalapį objekto kintamajam, o tada naudoti kintamąjį vietoj darbalapio nuorodų.

Pvz., Jei norite prie visų darbalapių pridėti metų priešdėlį, užuot skaičiavę lapus ir daugybę kartų vykdę ciklą, galite naudoti objekto kintamąjį.

Čia yra kodas, kuris pridės 2021-2022 kaip priešdėlį prie visų darbalapio pavadinimų.

Sub RenameSheet () Dim Ws kaip darbalapis kiekvienam Ws darbalapyje Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

Aukščiau pateiktas kodas deklaruoja kintamąjį Ws kaip darbalapio tipą (naudojant eilutę „Dim Ws kaip darbalapis“).

Dabar mums nereikia skaičiuoti lapų skaičiaus, kad juos būtų galima peržiūrėti. Vietoj to galime naudoti kilpą „Kiekvienam darbalapio W“. Tai leis mums peržiūrėti visus darbo lapų kolekcijos lapus. Nesvarbu, ar yra 2 lapai, ar 20 lapų.

Nors aukščiau pateiktas kodas leidžia mums peržiūrėti visus lapus, jūs taip pat galite priskirti tam tikrą lapą kintamajam.

Žemiau esančiame kode mes priskiriame kintamąjį Ws į „Sheet2“ ir naudojame jį norėdami pasiekti visas „Sheet2“ ypatybes.

Sub RenameSheet () Dim Ws as Workheet Set Ws = Worksheets ("Sheet2") Ws.Name = "Santrauka" Ws.Protect End Sub

Nustačius darbalapio nuorodą į objekto kintamąjį (naudojant SET sakinį), tą objektą galima naudoti vietoj darbalapio nuorodos. Tai gali būti naudinga, kai turite ilgą sudėtingą kodą ir norite pakeisti nuorodą. Užuot pakeitę visur, galite tiesiog pakeisti SET teiginį.

Atminkite, kad kodas deklaruoja Ws objektą kaip darbalapio tipo kintamąjį (kaip eilutę Dim Ws kaip darbalapį).

Slėpti darbalapius naudojant VBA (paslėptas + labai paslėptas)

„Excel“ darbalapių slėpimas ir slėpimas yra paprasta užduotis.

Galite paslėpti darbalapį ir vartotojas jo nematys atidaręs darbaknygę. Tačiau jie gali lengvai atskleisti darbalapį, dešiniuoju pelės klavišu spustelėdami bet kurį lapo skirtuką.

Bet ką daryti, jei nenorite, kad jie galėtų atskleisti darbalapį (-ius).

Tai galite padaryti naudodami VBA.

Toliau pateiktas kodas paslėptų visus darbaknygės darbalapius (išskyrus aktyvų lapą), kad negalėtumėte jo atskleisti, dešiniuoju pelės klavišu spustelėdami lapo pavadinimą.

Sub HideAllExcetActiveSheet () Dim Ws kaip darbalapis kiekvienam šios darbo knygos Ws.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden Next Ws End Sub

Pirmiau pateiktame kode Ws.Visible ypatybė pakeista į xlSheetVeryHidden.

• Kai ypatybė Visible nustatyta į xlSheetVisible, lapas matomas darbalapio srityje (kaip darbalapio skirtukai).
• Kai ypatybė „Matomas“ nustatyta į „xlSheetHidden“, lapas yra paslėptas, tačiau vartotojas gali jį slėpti, dešiniuoju pelės klavišu spustelėdamas bet kurį lapo skirtuką.
• Kai ypatybė „Matomas“ nustatyta į „xlSheetVeryHidden“, lapas yra paslėptas ir jo negalima paslėpti darbalapio srityje. Norėdami jį atskleisti, turite naudoti VBA kodą arba ypatybių langą.

Jei norite tiesiog paslėpti lapus, kuriuos galima lengvai paslėpti, naudokite žemiau esantį kodą:

Sub HideAllExceptActiveSheet () Dim Ws kaip darbalapis kiekvienam šios darbo knygos Ws.Worksheets If Ws.Name ActiveSheet.Name then Ws.Visible = xlSheetHidden Next Ws End Sub

Žemiau pateiktas kodas parodys visus darbalapius (tiek paslėptus, tiek labai paslėptus).

Sub UnhideAllWoksheets () Dim Ws kaip darbalapis kiekvienam šios darbo knygos Ws.Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

Susijęs straipsnis: Rodyti visus „Excel“ lapus (vienu metu)

Slėpti lapus pagal jame esantį tekstą

Tarkime, kad turite kelis lapus su skirtingų skyrių ar metų pavadinimais ir norite paslėpti visus lapus, išskyrus tuos, kuriuose yra 2021–2022 metai.

Tai galite padaryti naudodami funkciją VBA INSTR.

Žemiau pateiktas kodas paslėptų visus lapus, išskyrus tuos, kuriuose yra tekstas2021-2022.

Sub HideWithMatchingText () Dim Ws kaip darbalapis kiekvienam WS darbalapyje Jei InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Tada Ws.Visible = xlSheetHidden End If Next Ws End Sub

Pirmiau pateiktame kode INSTR funkcija grąžina simbolio vietą, kurioje ji randa atitinkančią eilutę. Jei ji neranda atitinkamos eilutės, ji grąžina 0.

Aukščiau pateiktas kodas tikrina, ar pavadinime yra tekstas2021-2022. Jei taip atsitiks, nieko neįvyks, kitaip darbalapis bus paslėptas.

Tai galite padaryti dar vieną žingsnį, turėdami tekstą langelyje ir naudodami tą langelį kode. Tai leis jums turėti reikšmę langelyje ir tada, kai paleisite makrokomandą, visi lapai, išskyrus tą, kuriame yra atitinkamas tekstas, liks matomi (kartu su lapais, kuriuose įvedate vertę ląstelė).

Darbo lapų rūšiavimas abėcėlės tvarka

Naudodami VBA galite greitai surūšiuoti darbalapius pagal jų pavadinimus.

Pvz., Jei turite darbaknygę, kurioje yra skirtingų skyrių ar metų lapai, galite naudoti toliau pateiktą kodą, kad greitai surūšiuotumėte šiuos lapus didėjančia tvarka.

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). Pavadinimas < Skaičiuoklės (i). Pavadinimas Tada Skaičiuoklės (j). Perkelkite anksčiau: = Lakštai (i) Pabaiga, jei kita j Kita i programa. ScreenUpdating = Tikroji pabaiga

Atminkite, kad šis kodas gerai veikia su teksto pavadinimais ir daugeliu atvejų su metais ir skaičiais. Tačiau tai gali duoti neteisingus rezultatus, jei lapų pavadinimai yra 1,2,11. Jis surūšiuos ir suteiks jums seką 1, 11, 2. Taip yra todėl, kad jis palyginamas kaip tekstas ir 2 yra didesnis nei 11.

Apsaugokite/panaikinkite visų lapų apsaugą vienu metu

Jei darbaknygėje yra daug darbalapių ir norite apsaugoti visus lapus, galite naudoti žemiau esantį VBA kodą.

Tai leidžia nurodyti kodo slaptažodį. Šio slaptažodžio jums reikės norint panaikinti darbalapio apsaugą.

Sub ProtectAllSheets () Dim ws kaip darbalapis Dim Dimension as String password = "Test123" 'pakeisti Test123 norimu slaptažodžiu Kiekvienam ws darbalapyje ws.Apsaugoti slaptažodį: = slaptažodis Next ws End Sub

Šis kodas vienu metu panaikins visų lapų apsaugą.

Sub ProtectAllSheets () Dim ws kaip darbalapis Dim Dimension as String password = "Test123" 'pakeisti Test123 slaptažodžiu, kurį naudojote saugodami Kiekvienam ws darbalapyje ws.Neapsaugoti slaptažodžio: = slaptažodis Next ws End Sub

Visų darbalapių (su hipersaitais) turinio sudarymas

Jei darbaknygėje yra darbalapių rinkinys ir norite greitai įterpti suvestinės lapą, kuriame yra nuorodos į visus lapus, galite naudoti žemiau esantį kodą.

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). Pavadinimas & "! A1", _ TextToDisplay: = Darbo lapai (i). Pavadinimas Kitas i Pabaiga

Aukščiau pateiktas kodas įterpia naują darbalapį ir pavadina jį indeksu.

Tada jis peržiūri visus darbalapius ir sukuria hipersaitą visiems rodyklės lapo darbalapiams.

Kur įdėti VBA kodą

Įdomu, kur „Excel“ darbaknygėje patenka VBA kodas?

„Excel“ turi VBA vidinę programą, vadinamą VBA redaktoriumi. Turite nukopijuoti ir įklijuoti kodą į „VB Editor“ modulio kodo langą.

Štai žingsniai, kaip tai padaryti:

1. Eikite į skirtuką Kūrėjas.
2. Spustelėkite „Visual Basic“ parinktį. Tai atidarys VB redaktorių užpakalinėje sistemoje.
3. VB redaktoriaus „Project Explorer“ srityje dešiniuoju pelės mygtuku spustelėkite bet kurį darbaknygės objektą, į kurį norite įterpti kodą. Jei nematote „Project Explorer“, eikite į skirtuką „View“ ir spustelėkite „Project Explorer“.
4. Eikite į „Įterpti“ ir spustelėkite „Modulis“. Tai įterps jūsų darbo knygos modulio objektą.
5. Nukopijuokite ir įklijuokite kodą modulio lange.

Jums taip pat gali patikti šios „Excel VBA“ pamokos:

• Darbas su darbaknygėmis naudojant VBA.
• Naudojant IF tada kitus teiginius VBA.
• Dėl kitos kilpos VBA.
• Naudotojo nustatytos funkcijos kūrimas „Excel“.
• Kaip įrašyti makrokomandą „Excel“.
• Kaip paleisti makrokomandą „Excel“.
• „Excel VBA Events“ - lengvas (ir išsamus) vadovas.
• Kaip sukurti priedą „Excel“.
• Kaip išsaugoti ir pakartotinai naudoti makrokomandą naudojant „Excel“ asmeninės makrokomandos darbaknygę.