Munkalapok használata Excel VBA használatával (példákkal magyarázva)

A cellákon és tartományokon kívül a munkalapokkal való munka egy másik terület, amelyet tudnia kell a VBA hatékony használatáról az Excelben.

A VBA bármely objektumához hasonlóan a munkalapokhoz különböző tulajdonságok és módszerek kapcsolódnak, amelyeket használhat, miközben automatizálja a munkát az Excel VBA -val.

Ebben az oktatóanyagban részletesen bemutatom a „Munkalapokat”, és néhány gyakorlati példát is bemutatok.

Kezdjük hát el.

Az ebben az oktatóanyagban említett összes kódot el kell helyezni a VB szerkesztőbe. Lépjen a „Hová tegye a VBA -kódot” szakaszhoz, hogy megtudja, hogyan működik.

Ha érdekli a VBA egyszerű tanulása, nézze meg az enyémet Online Excel VBA képzés.

Különbség a VBA munkalapjai és lapjai között

A VBA -ban két gyűjteménye van, amelyek néha kissé zavaróak lehetnek.

A munkafüzetben lehet munkalap és diagramlap. Az alábbi példa három munkalapot és egy diagramlapot tartalmaz.

Excel VBA -ban:

• A „Munkalapok” gyűjtemény a munkafüzet összes munkalap -objektumának gyűjtésére vonatkozna. A fenti példában a Munkalapok gyűjtemény három munkalapból állna.
• A „Lapok” gyűjtemény a munkafüzet összes munkalapjára és diagramlapjára vonatkozna. A fenti példában négy eleme lenne - 3 feladatlap + 1 diagramlap.

Ha olyan munkafüzete van, amely csak munkalapokat és diagramlapokat nem tartalmaz, akkor a „Munkalapok” és a „Táblázatok” gyűjtemény ugyanaz.

De ha egy vagy több diagramlapja van, a „Táblázatok” gyűjtemény nagyobb lesz, mint a „Munkalapok” gyűjtemény

Táblázatok = Munkalapok + Táblázatlapok

Most ezzel a megkülönböztetéssel azt javaslom, hogy a VBA kód írása során a lehető legkonkrétabb legyen.

Tehát ha csak a munkalapokra kell hivatkoznia, használja a „Munkalapok” gyűjteményt, és ha minden lapra (beleértve a táblázatlapokat is), a „Táblázatok” gyűjteményt kell használnia.

Ebben az oktatóanyagban csak a „Munkalapok” gyűjteményt fogom használni.

Munkalap hivatkozása a VBA -ban

Sokféle módon hivatkozhat a VBA munkalapjára.

A munkalapokra való hivatkozás megértése segíthet a jobb kód írásban, különösen akkor, ha hurkokat használ a VBA -kódban.

A munkalap nevének használata

A munkalapra való hivatkozás legegyszerűbb módja annak neve.

Tegyük fel például, hogy van egy munkafüzete három munkalappal - 1. lap, 2. lap, 3. lap.

És aktiválni szeretné a 2. lapot.

Ezt a következő kód használatával teheti meg: Sub ActivateSheet () Munkalapok ("Sheet2").

A fenti kód arra kéri a VBA -t, hogy hivatkozzon a Munkalapok gyűjtemény 2. lapjára, és aktiválja azt.

Mivel a lap pontos nevét használjuk, itt használhatja a Táblázatok gyűjteményt is. Tehát az alábbi kód is ezt tenné.

Sub ActivateSheet () Sheets ("Sheet2"). Aktiválja a End Sub

Az indexszám használata

Míg a munkalap használata egyszerű módja a munkalapra való hivatkozásnak, néha előfordulhat, hogy nem tudja a munkalap pontos nevét.

Ha például VBA -kóddal új munkalapot ad hozzá a munkafüzethez, és nem tudja, hány munkalap van már ott, akkor nem tudja az új munkalap nevét.

Ebben az esetben használhatja a munkalapok indexszámát.

Tegyük fel, hogy a következő munkalapok vannak a munkafüzetben:

Az alábbi kód aktiválja a Sheet2 -t:

Sub ActivateSheet () Munkalapok (2). Aktiválja a End Sub -t

Ne feledje, hogy a 2 -es indexszámot használtuk Feladatlapok (2). Ez a munkalapok gyűjteményének második tárgyára vonatkozna.

Mi történik, ha a 3 -at használja indexszámként?

A Sheet3 lesz kiválasztva.

Ha kíváncsi, miért választotta a Sheet3 -at, mivel egyértelműen a negyedik objektum.

Ez azért történik, mert egy diagramlap nem része a munkalapgyűjteménynek.

Tehát amikor a Munkalapok gyűjteményben szereplő indexszámokat használjuk, az csak a munkafüzet munkalapjaira fog utalni (és figyelmen kívül hagyja a diagramlapokat).

Éppen ellenkezőleg, ha a Táblázatokat használja, a Táblázatok (1) a Táblázatok1, a Táblázatok (2) a Második, A Táblázatok (3) az Ábra1, a Táblázatok (4) pedig a 3 -as lapra vonatkozna.

Az indexszám használatának ez a technikája akkor hasznos, ha a munkafüzet összes munkalapját át akarja tekinteni. Megszámolhatja a munkalapok számát, majd ezt a számot használhatja (ezt a bemutatót később megtudjuk).

Megjegyzés: Az indexszám balról jobbra halad. Tehát ha a Sheet2 -t a Sheet1 bal oldalára tolja, akkor az (1) munkalap a Sheet2 -re hivatkozik.

A munkalap kódjának használata

A lapnév használatának egyik hátránya (amint azt a fenti részben láttuk) az, hogy a felhasználó megváltoztathatja azt.

És ha a lap nevét megváltoztatták, a kód nem működik, amíg meg nem változtatja a munkalap nevét a VBA -kódban is.

A probléma megoldásához használhatja a munkalap kódnevét (az eddig használt szokásos név helyett). A kódnév hozzárendelhető a VB szerkesztőben, és nem változik, ha megváltoztatja a munkalap területéről a munkalap nevét.

A munkalap kódnevének megadásához kövesse az alábbi lépéseket:

1. Kattintson a Fejlesztő fülre.
2. Kattintson a Visual Basic gombra. Ez megnyitja a VB szerkesztőt.
3. Kattintson a Nézet lehetőségre a menüben, majd kattintson a Projektablak elemre. Ezzel láthatóvá válik a Tulajdonságok panel. Ha a Tulajdonságok panel már látható, hagyja ki ezt a lépést.
4. Kattintson az átnevezni kívánt projektfelfedező lap nevére.
5. A Tulajdonságok panelen módosítsa a nevet a (Név) előtti mezőben. Ne feledje, hogy a névben nem lehet szóköz.

A fenti lépések megváltoztatnák a munkalap nevét a VBA háttérben. Az Excel munkalap nézetben a munkalapot bármikor elnevezheti, de a háttérben mind a nevekre - a lap nevére és a kódnévre - válaszolni fog.

A fenti képen a lap neve „SheetName”, a kódnév pedig „CodeName”. Még ha megváltoztatja a munkalapon a munkalap nevét, a kódnév változatlan marad.

Most a Munkalapok gyűjteményével hivatkozhat a munkalapra, vagy használhatja a kódnevet.

Például mindkét sor aktiválja a munkalapot.

Munkalapok ("Lapnév"). Aktiválja a CodeName.Activate lehetőséget

A kettő közötti különbség az, hogy ha megváltoztatja a munkalap nevét, az első nem működik. De a második sor a megváltozott név mellett is tovább működne. A második sor (a kódnév használatával) szintén rövidebb és könnyebben használható.

Hivatkozás egy munkalapra egy másik munkafüzetben

Ha egy másik munkafüzet munkalapjára szeretne hivatkozni, a munkafüzetnek nyitva kell lennie, amíg a kód fut, és meg kell adnia a munkafüzet nevét és a hivatkozni kívánt munkalapot.

Például, ha van egy Példaképek nevű munkafüzete, és aktiválni szeretné a 1. lapot a Példa munkafüzetben, akkor az alábbi kódot kell használnia:

Sub SheetActivate () Munkafüzetek ("Examples.xlsx"). Munkalapok ("Sheet1"). Act End End

Ne feledje, hogy ha a munkafüzetet elmentette, akkor a fájl nevét a kiterjesztéssel együtt kell használnia. Ha nem tudja, milyen nevet használjon, kérjen segítséget a Project Explorer -től.

Ha a munkafüzetet nem mentette el, akkor nem kell a fájlkiterjesztést használni.

Munkalap hozzáadása

Az alábbi kód hozzáadna egy munkalapot (első munkalapként - azaz a lap bal szélső lapjaként).

Sub AddSheet () feladatlapok. Add End Sub

A Sheet2 alapértelmezett nevet veszi fel (vagy bármely más számot, attól függően, hogy hány lap van már ott).

Ha azt szeretné, hogy egy munkalapot egy adott munkalap (például a Sheet2) előtt adjunk hozzá, akkor használhatja az alábbi kódot.

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

A fenti kód arra utasítja a VBA -t, hogy adjon hozzá egy lapot, majd az „Előtte” utasítás segítségével határozza meg azt a munkalapot, amely elé az új munkalapot be kell illeszteni.

Hasonlóképpen, egy lapot is hozzáadhat a munkalap (mondjuk a Sheet2) után az alábbi kód használatával:

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

Ha azt szeretné, hogy az új lap a lapok végére kerüljön, először tudnia kell, hány lap van. A következő kód először megszámolja a lapok számát, és hozzáadja az új lapot az utolsó lap után (amelyre az indexszám segítségével hivatkozunk).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Munkalapok. Munkalapok számlálása. Add After: = Munkalapok (SheetCount) End Sub

Munkalap törlése

Az alábbi kód törli az aktív lapot a munkafüzetből.

Sub DeleteSheet () ActiveSheet. Delete End Sub

A fenti kód figyelmeztető üzenetet jelenít meg a munkalap törlése előtt.

Ha nem szeretné látni a figyelmeztető üzenetet, használja az alábbi kódot:

Sub DeleteSheet () Application.DisplayAlerts = Hamis ActiveSheet.Delete Application.DisplayAlerts = Igaz vég al

Ha az Application.DisplayAlerts értéke False, akkor nem jelenik meg a figyelmeztető üzenet. Ha használja, ne felejtse el visszaállítani True értékre a kód végén.

Ne feledje, hogy ezt a törlést nem vonhatja vissza, ezért használja a fenti kódot, ha teljesen biztos benne.

Ha törölni szeretne egy adott lapot, akkor ezt a következő kóddal teheti meg:

Sub DeleteSheet () Munkalapok ("Sheet2"). Törlés End Sub

A lap kódnevével is törölheti.

Sub DeleteSheet () Sheet 5. Delete End Sub

A munkalapok átnevezése

A Munkalap névtulajdonságával módosíthatja a nevét.

A következő kód megváltoztatja az 1. lap nevét „Összefoglaló” -ra.

Al RenameSheet () Munkalapok ("Sheet1"). Name = "Summary" End Sub

Ezt kombinálhatja a lap hozzáadása módszerrel, hogy meghatározott nevű lapkészletet kapjon.

Például, ha négy lapot szeretne beszúrni a 2021-2022 Q1, 2021-2022 Q2, 2021-2022 Q3 és 2021-2022 Q4 névvel, akkor használja az alábbi kódot.

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

A fenti kódban először megszámoljuk a lapok számát, majd egy For Next ciklus segítségével új lapokat illesztünk a végére. A munkalap hozzáadásával a kód azt is átnevezi.

Munkalap -objektum hozzárendelése egy változóhoz

Amikor munkalapokkal dolgozik, hozzárendelhet egy munkalapot egy objektumváltozóhoz, majd a változót használja a munkalap hivatkozásai helyett.

Például, ha egy év előtagot szeretne hozzáadni az összes munkalaphoz, ahelyett, hogy számolná a lapokat és a ciklusokat annyiszor futtatná, használhatja az objektumváltozót.

Itt található a kód, amely hozzáadja a 2021-2022 előtagot a munkalap összes nevéhez.

Al RenameSheet () Dim Ws munkalapként minden munkalaphoz Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

A fenti kód a Ws változót deklarálja munkalap típusaként (a „Dim Ws as Workheet” sor segítségével).

Most nem kell számolnunk a lapok számát, hogy ezeket végigcsináljuk. Ehelyett használhatjuk a „Minden munkalapon lévő W -hez” hurkot. Ez lehetővé teszi számunkra, hogy végigolvassa a munkalapgyűjtemény összes lapját. Nem mindegy, hogy 2 vagy 20 lap van.

Bár a fenti kód lehetővé teszi számunkra, hogy végiglapozzuk az összes lapot, egy adott lapot is hozzárendelhet egy változóhoz.

Az alábbi kódban hozzárendeljük a Ws változót a Sheet2 -hez, és ezzel elérjük a Sheet2 összes tulajdonságát.

Al RenameSheet () Dim Ws munkalapkészletként Ws = Munkalapok ("Sheet2") Ws.Name = "Summary" Ws.Protect End Sub

Miután beállított egy munkalap hivatkozást egy objektumváltozóra (a SET utasítás használatával), az adott objektum használható a munkalap hivatkozása helyett. Ez akkor lehet hasznos, ha hosszú, bonyolult kódja van, és módosítani szeretné a hivatkozást. Ahelyett, hogy mindenhol elvégezné a változtatást, egyszerűen elvégezheti a módosítást a SET utasításban.

Ne feledje, hogy a kód a Ws objektumot a Munkalap típusú változóként deklarálja (a Dim Ws sort használva munkalapként).

Munkalapok elrejtése VBA használatával (rejtett + nagyon rejtett)

A munkalapok elrejtése és elrejtése Excelben egyszerű feladat.

Elrejthet egy munkalapot, és a felhasználó nem látja azt, amikor megnyitja a munkafüzetet. Azonban könnyen megjeleníthetik a munkalapot, ha jobb gombbal kattintanak bármelyik lap fülére.

De mi van akkor, ha nem akarja, hogy fel tudják tárni a munkalap (ok) t.

Ezt megteheti a VBA segítségével.

Az alábbi kód elrejti a munkafüzet összes munkalapját (kivéve az aktív lapot), így nem tudja feloldani a lap elrejtését a jobb egérgombbal a lap nevére kattintva.

Sub HideAllExcetActiveSheet () Dim Ws munkalapként minden munkalaphoz ebben a munkafüzetben.

A fenti kódban a Ws.Visible tulajdonság erre változik xlSheetVeryHidden.

• Ha a Látható tulajdonság xlSheetVisible értékre van állítva, a munkalap látható a munkalap területen (munkalap lapjai).
• Ha a Látható tulajdonság xlSheetHidden értékre van állítva, a lap el van rejtve, de a felhasználó feloldhatja azt, ha jobb gombbal kattint bármelyik lap lapjára.
• Ha a Látható tulajdonság xlSheetVeryHidden értékre van állítva, a lap el van rejtve, és nem lehet feloldani a munkalap területéről. VBA -kódot vagy a tulajdonságok ablakot kell használnia annak megjelenítéséhez.

Ha egyszerűen el szeretné rejteni a lapokat, amelyek könnyen elrejthetők, használja az alábbi kódot:

Sub HideAllExceptActiveSheet () Dim Ws munkalapként minden munkalaphoz ebben a munkafüzetben. Munkalapok Ha Ws.Name ActiveSheet.Name then Ws.Visible = xlSheetHidden Next Ws End Sub

Az alábbi kód megjelenítené az összes munkalapot (mind rejtett, mind nagyon rejtett).

Sub UnhideAllWoksheets () Dim Ws munkalapként minden munkalaphoz ebben a munkafüzetben. Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

Kapcsolódó cikk: Az összes munkalap megjelenítése Excelben (egyszerre)

Táblázatok elrejtése a benne található szöveg alapján

Tegyük fel, hogy több lapja van a különböző osztályok vagy évek nevével, és el szeretné rejteni az összes lapot, kivéve azokat, amelyekben szerepel a 2021–2022.

Ezt a VBA INSTR függvénnyel teheti meg.

Az alábbi kód elrejti az összes lapot, kivéve azokat, amelyeken a 2021-2022 szöveg szerepel.

Sub HideWithMatchingText () Dim Ws munkalapként a munkalapok minden egyes W -jéhez Ha InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Akkor Ws.Visible = xlSheetHidden End If Next Ws End Sub

A fenti kódban az INSTR függvény visszaadja a karakter pozícióját, ahol megtalálja a megfelelő karakterláncot. Ha nem találja a megfelelő karakterláncot, 0 -t ad vissza.

A fenti kód ellenőrzi, hogy a név tartalmazza-e a 2021-2022 szöveget. Ha igen, akkor nem történik semmi, különben a munkalap rejtve van.

Ezzel egy lépéssel tovább léphet, ha a szöveget egy cellában tartja, és ezt a cellát használja a kódban. Ez lehetővé teszi, hogy legyen érték a cellában, majd a makró futtatásakor az összes munkalap, kivéve azt, amelyiken a megfelelő szöveg szerepel, látható marad (a lapokkal együtt, ahol az értéket a sejt).

A munkalapok rendezése ábécé sorrendben

A VBA használatával gyorsan rendezheti a munkalapokat a nevük alapján.

Például, ha olyan munkafüzete van, amely különböző osztályokra vagy évekre vonatkozó lapokat tartalmaz, akkor az alábbi kód segítségével gyorsan rendezheti ezeket a lapokat növekvő sorrendben.

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) .Name < Táblázatok (i). Név, majd Táblázatok (j). Mozgás előtt: = Táblázatok (i) Vége, ha Következő j Következő i Alkalmazás. ScreenUpdating = Igaz végrész

Ne feledje, hogy ez a kód jól működik a szöveges nevekkel, és a legtöbb esetben az évekkel és számokkal is. De rossz eredményeket adhat, ha a lapok neve 1,2,11. Ez rendezi és megadja az 1, 11, 2 sorozatot. Ez azért van, mert az összehasonlítást szövegként végzi, és 2 -t nagyobbnak tart 11 -nél.

Védje/távolítsa el az összes lapot egyszerre

Ha sok munkalap van egy munkafüzetben, és meg szeretné védeni az összes lapot, akkor használja az alábbi VBA -kódot.

Lehetővé teszi a jelszó megadását a kódon belül. Erre a jelszóra lesz szüksége a munkalap védelmének feloldásához.

Sub ProtectAllSheets () Dim ws munkalapként Halvány jelszó mint karakterlánc jelszó = "Test123" 'cserélje ki a Test123 -at a kívánt jelszóval Minden ws -hez a munkalapokon ws.Védje a jelszót: = jelszó Következő ws End Sub

A következő kód egy menetben feloldja az összes lap védelmét.

Sub ProtectAllSheets () Dim ws munkalapként Halvány jelszó mint karakterlánc jelszó = "Test123" 'cserélje ki a Test123 jelszót, amelyet a védelem során használt

Az összes munkalap tartalomjegyzékének létrehozása (hiperhivatkozásokkal)

Ha a munkafüzetben van egy munkalapkészlet, és gyorsan be szeretne illeszteni egy összefoglaló lapot, amely az összes lapra mutató hivatkozásokat tartalmaz, akkor használja az alábbi kódot.

Sub AddIndexSheet () Worksheets.Add ActiveSheet.Name = "Index" For i = 2 To Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor: = Cells (i - 1, 1), _ Address: = "", SubAddress: = Munkalapok (i) .Name & "! A1", _ TextToDisplay: = Munkalapok (i) .Name Next i End Sub

A fenti kód új munkalapot szúr be, és indexnek nevezi.

Ezután végiglapozza az összes munkalapot, és hiperhivatkozást hoz létre az indexlap összes munkalapjához.

Hová tegye a VBA kódot

Kíváncsi vagy, hova kerül a VBA -kód az Excel -munkafüzetben?

Az Excel rendelkezik VBA háttérprogrammal, amelyet VBA -szerkesztőnek hívnak. Be kell másolnia és beillesztenie a kódot a VB Editor modul ablakába.

Ehhez tegye a következő lépéseket:

1. Lépjen a Fejlesztő fülre.
2. Kattintson a Visual Basic lehetőségre. Ez megnyitja a VB szerkesztőt a háttérben.
3. A VB szerkesztő Project Explorer ablaktáblájában kattintson jobb gombbal a munkafüzet bármely olyan objektumára, amelybe be szeretné illeszteni a kódot. Ha nem látja a Project Explorer programot, lépjen a Nézet fülre, és kattintson a Project Explorer elemre.
4. Lépjen a Beszúrás elemre, és kattintson a Modul elemre. Ezzel beszúr egy modulobjektumot a munkafüzetébe.
5. Másolja ki és illessze be a kódot a modul ablakába.

A következő Excel VBA oktatóanyagok is tetszhetnek:

• Munkafüzetek használata VBA használatával.
• Az IF majd más állítások használata a VBA -ban.
• Következő hurok a VBA -ban.
• Felhasználó által definiált függvény létrehozása Excelben.
• Makró rögzítése Excelben.
• Makró futtatása Excelben.
• Excel VBA események - Egyszerű (és teljes) útmutató.
• Bővítmény létrehozása Excelben.
• A makró mentése és újrafelhasználása az Excel Personal Macro Workbook segítségével.