Arbeta med kalkylblad med Excel VBA (förklaras med exempel)

Förutom celler och intervall är arbetet med kalkylblad ett annat område du bör veta om för att använda VBA effektivt i Excel.

Precis som alla objekt i VBA har kalkylblad olika egenskaper och metoder associerade med det som du kan använda när du automatiserar ditt arbete med VBA i Excel.

I den här självstudien kommer jag att täcka 'Arbetsblad' i detalj och också visa dig några praktiska exempel.

Så låt oss börja.

Alla koder jag nämner i den här självstudien måste placeras i VB Editor. Gå till avsnittet "Var ska du placera VBA -koden" för att veta hur det fungerar.

Om du är intresserad av att lära dig VBA på det enkla sättet, kolla in min Online Excel VBA -utbildning.

Skillnad mellan kalkylblad och blad i VBA

I VBA har du två samlingar som ibland kan vara lite förvirrande.

I en arbetsbok kan du ha kalkylblad och diagramblad. Exemplet nedan har tre kalkylblad och ett diagramblad.

I Excel VBA:

• "Arbetsblad" -samlingen hänvisar till samlingen av alla kalkylbladsobjekt i en arbetsbok. I exemplet ovan består arbetsbladssamlingen av tre kalkylblad.
• "Ark" -samlingen hänvisar till alla kalkylblad samt diagramark i arbetsboken. I exemplet ovan skulle det ha fyra element - 3 kalkylblad + 1 diagramark.

Om du har en arbetsbok som bara har kalkylblad och inga diagramblad är samlingen "Arbetsblad" och "Ark" densamma.

Men när du har ett eller flera sjökort, skulle "Ark" -samlingen vara större än "Arbetsblad" -samlingen

Ark = Arbetsblad + Diagramblad

Med denna skillnad rekommenderar jag att vara så specifik som möjligt när du skriver en VBA -kod.

Så om du bara måste hänvisa till kalkylblad använder du "Arbetsblad" -samlingen, och om du måste hänvisa till alla blad (inklusive diagramark), använd "Ark" -samlingen.

I den här självstudien kommer jag bara att använda samlingen "Arbetsblad".

Hänvisning till ett arbetsblad i VBA

Det finns många olika sätt du kan använda för att hänvisa till ett kalkylblad i VBA.

Att förstå hur man hänvisar till kalkylblad skulle hjälpa dig att skriva bättre kod, särskilt när du använder loopar i din VBA -kod.

Använda arbetsbladets namn

Det enklaste sättet att hänvisa till ett kalkylblad är att använda dess namn.

Anta till exempel att du har en arbetsbok med tre kalkylblad - Ark 1, Ark 2, Ark 3.

Och du vill aktivera Sheet 2.

Du kan göra det med följande kod: Sub ActivateSheet () Worksheets ("Sheet2"). Aktivera End Sub

Ovanstående kod ber VBA att hänvisa till Sheet2 i arbetsbladssamlingen och aktivera den.

Eftersom vi använder det exakta bladnamnet kan du också använda Sheets -samlingen här. Så nedanstående kod skulle också göra samma sak.

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

Använda indexnumret

Även om det är ett enkelt sätt att hänvisa till ett kalkylblad kan du ibland inte veta det exakta namnet på kalkylbladet.

Om du till exempel använder en VBA -kod för att lägga till ett nytt kalkylblad i arbetsboken och du inte vet hur många kalkylblad som redan finns där, skulle du inte veta namnet på det nya kalkylbladet.

I det här fallet kan du använda indexnumret för kalkylblad.

Anta att du har följande blad i en arbetsbok:

Koden nedan aktiverar Sheet2:

Sub ActivateSheet () Worksheets (2) .Activate End Sub

Observera att vi har använt index nummer 2 in Arbetsblad (2). Detta skulle hänvisa till det andra objektet i samlingen av kalkylblad.

Vad händer nu när du använder 3 som indexnummer?

Det kommer att välja Sheet3.

Om du undrar varför det valde Sheet3, eftersom det helt klart är det fjärde objektet.

Detta händer eftersom ett diagramark inte är en del av kalkylbladssamlingen.

Så när vi använder indexnumren i arbetsbladssamlingen kommer det bara att hänvisa till kalkylbladen i arbetsboken (och ignorera diagramarken).

Tvärtom, om du använder Sheets, skulle Sheets (1) hänvisa till Sheets1, Sheets (2) till Sheet2, Sheets (3) skulle hänvisa till Chart1 och Sheets (4) skulle referera till Sheet3.

Denna teknik för att använda indexnummer är användbar när du vill bläddra igenom alla kalkylblad i en arbetsbok. Du kan räkna antalet kalkylblad och sedan gå igenom dessa med hjälp av denna räkning (vi får se hur du gör detta senare i den här självstudien).

Obs! Indexnumret går från vänster till höger. Så om du flyttar Sheet2 till vänster om Sheet1, skulle Arbetsblad (1) hänvisa till Sheet2.

Använda kalkylbladets kodnamn

En av nackdelarna med att använda bladnamnet (som vi såg i avsnittet ovan) är att en användare kan ändra det.

Och om bladnamnet har ändrats skulle din kod inte fungera förrän du också ändrade namnet på kalkylbladet i VBA -koden.

För att lösa detta problem kan du använda kodnamnet på kalkylbladet (istället för det vanliga namn som vi har använt hittills). Ett kodnamn kan tilldelas i VB Editor och ändras inte när du ändrar namnet på arket från kalkylbladsområdet.

Följ stegen nedan för att ge ditt kalkylblad ett kodnamn:

1. Klicka på fliken Utvecklare.
2. Klicka på Visual Basic -knappen. Detta öppnar VB Editor.
3. Klicka på alternativet Visa i menyn och klicka på Projektfönster. Detta gör fönstret Egenskaper synligt. Om fönstret Egenskaper redan är synligt hoppar du över det här steget.
4. Klicka på bladnamnet i projektutforskaren som du vill byta namn på.
5. I fönstret Egenskaper ändrar du namnet i fältet framför (Namn). Observera att du inte kan ha mellanslag i namnet.

Stegen ovan skulle ändra namnet på ditt kalkylblad i VBA -backend. I Excel -kalkylbladvyn kan du namnge kalkylbladet vad du vill, men i backend kommer det att svara på både namnen - bladets namn och kodnamnet.

I bilden ovan är bladets namn 'Arknamn' och kodnamnet är 'Kodnamn'. Även om du ändrar bladnamnet på kalkylbladet förblir kodnamnet fortfarande detsamma.

Nu kan du använda antingen arbetsbladssamlingen för att hänvisa till kalkylbladet eller använda kodnamnet.

Till exempel kommer båda raden att aktivera kalkylbladet.

Arbetsblad ("Arknamn"). Aktivera kodnamn.Aktivera

Skillnaden i dessa två är att om du ändrar namnet på kalkylbladet skulle det första inte fungera. Men den andra raden skulle fortsätta att fungera även med det ändrade namnet. Den andra raden (med kodnamnet) är också kortare och lättare att använda.

Hänvisning till ett kalkylblad i en annan arbetsbok

Om du vill hänvisa till ett kalkylblad i en annan arbetsbok måste arbetsboken vara öppen medan koden körs och du måste ange namnet på arbetsboken och det kalkylblad som du vill referera till.

Om du till exempel har en arbetsbok med namnet Exempel och du vill aktivera Ark1 i arbetsboken Exempel, måste du använda koden nedan:

Sub SheetActivate () Workbooks ("Exempel.xlsx"). Arbetsblad ("Sheet1"). Aktivera End Sub

Observera att om arbetsboken har sparats måste du använda filnamnet tillsammans med tillägget. Om du inte är säker på vilket namn du ska använda, ta hjälp av Project Explorer.

Om arbetsboken inte har sparats behöver du inte använda filtillägget.

Lägga till ett kalkylblad

Koden nedan skulle lägga till ett kalkylblad (som det första kalkylbladet - dvs. som det längst till vänster på bladfliken).

Sub AddSheet () Worksheets.Add End Sub

Det tar standardnamnet Sheet2 (eller något annat nummer baserat på hur många ark som redan finns där).

Om du vill att ett kalkylblad ska läggas till före ett specifikt kalkylblad (säg Sheet2), kan du använda koden nedan.

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

Ovanstående kod uppmanar VBA att lägga till ett blad och använder sedan "Före" -uttalandet för att ange det kalkylblad innan det nya kalkylbladet ska infogas.

På samma sätt kan du också lägga till ett blad efter ett kalkylblad (säg Sheet2) med hjälp av koden nedan:

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

Om du vill att det nya arket ska läggas till i slutet av arken måste du först veta hur många ark det finns. Följande kod räknar först antalet ark, och läggs till det nya bladet efter det sista bladet (till vilket vi hänvisar med indexnumret).

Sub AddSheet () Dim SheetCount As Integer SheetCount = Worksheets.Count Worksheets.Add After: = Worksheets (SheetCount) End Sub

Radera ett kalkylblad

Koden nedan tar bort det aktiva arket från arbetsboken.

Sub DeleteSheet () ActiveSheet.Delete End Sub

Koden ovan visar en varningsmeddelande innan du tar bort kalkylbladet.

Om du inte vill se varningsmeddelandet använder du koden nedan:

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

När Application.DisplayAlerts är inställt på False kommer det inte att visa dig varningsmeddelandet. Om du använder den, kom ihåg att ställa tillbaka den till True i slutet av koden.

Kom ihåg att du inte kan ångra denna radering, så använd koden ovan när du är helt säker.

Om du vill ta bort ett visst blad kan du göra det med följande kod:

Sub DeleteSheet () Worksheets ("Sheet2"). Radera End Sub

Du kan också använda arkets kodnamn för att radera det.

Sub DeleteSheet () Sheet5.Delete End Sub

Byt namn på arbetsblad

Du kan ändra namnegenskapen för kalkylbladet för att ändra dess namn.

Följande kod ändrar namnet på Sheet1 till "Sammanfattning".

Sub RenameSheet () Worksheets ("Sheet1"). Name = "Summary" End Sub

Du kan kombinera detta med metoden för att lägga till ark för att ha en uppsättning ark med specifika namn.

Om du till exempel vill infoga fyra blad med namnet2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 och2021-2022 Q4 kan du använda koden nedan.

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

I koden ovan räknar vi först antalet ark och använder sedan en For Next -slinga för att infoga nya ark i slutet. När arket läggs till, byter koden också namn på det.

Tilldela kalkylbladsobjekt till en variabel

När du arbetar med kalkylblad kan du tilldela ett kalkylblad till en objektvariabel och sedan använda variabeln istället för kalkylbladets referenser.

Om du till exempel vill lägga till ett årsprefix till alla kalkylblad, kan du använda objektvariabeln istället för att räkna arken och löpningen så många gånger.

Här är koden som kommer att lägga till2021-2022 som ett prefix till alla kalkylbladets namn.

Sub RenameSheet () Dim Ws som arbetsblad för varje Ws i kalkylblad Ws.Name = "2018 -" & Ws.Name Next Ws End Sub

Ovanstående kod deklarerar en variabel Ws som kalkylbladstyp (med raden 'Dim Ws As Worksheet').

Nu behöver vi inte räkna antalet ark för att gå igenom dessa. Istället kan vi använda slingan "För varje Ws i kalkylblad". Detta gör att vi kan gå igenom alla blad i kalkylbladssamlingen. Det spelar ingen roll om det finns 2 eller 20 ark.

Även om ovanstående kod tillåter oss att bläddra igenom alla blad, kan du också tilldela ett specifikt blad till en variabel.

I koden nedan tilldelar vi variabeln Ws till Sheet2 och använder den för att komma åt alla Sheet2s egenskaper.

Sub RenameSheet () Dim Ws As Worksheet Set Ws = Worksheets ("Sheet2") Ws.Name = "Summary" Ws.Protect End Sub

När du har ställt in en kalkylbladreferens till en objektvariabel (med SET -satsen) kan det objektet användas istället för kalkylbladets referens. Detta kan vara till hjälp när du har en lång komplicerad kod och du vill ändra referensen. Istället för att göra ändringen överallt kan du helt enkelt göra ändringen i SET -satsen.

Observera att koden deklarerar Ws -objektet som variabel för kalkylbladstyp (med raden Dim Ws som kalkylblad).

Dölj kalkylblad med VBA (dold + mycket dold)

Att dölja och dölja kalkylblad i Excel är en enkel uppgift.

Du kan dölja ett kalkylblad och användaren skulle inte se det när han/hon öppnar arbetsboken. De kan dock enkelt ta bort kalkylbladet genom att högerklicka på valfri arkflik.

Men vad händer om du inte vill att de ska kunna avslöja kalkylbladet (arna).

Du kan göra detta med VBA.

Koden nedan skulle dölja alla kalkylblad i arbetsboken (utom det aktiva bladet), så att du inte kan ta bort det genom att högerklicka på bladets namn.

Sub HideAllExcetActiveSheet () Dim Ws som arbetsblad för varje Ws i ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Sedan Ws.Visible = xlSheetVeryHidden Nästa Ws Avsluta Sub

I koden ovan ändras egenskapen Ws.Visible till xlSheetVeryHidden.

• När egenskapen Synlig är inställd på xlSheetVisible är arket synligt i kalkylbladsområdet (som flikar i kalkylblad).
• När den synliga egenskapen är inställd på xlSheetHidden är arket dolt men användaren kan dölja det genom att högerklicka på valfri arkflik.
• När egenskapen Synlig är inställd på xlSheetVeryHidden döljs arket och kan inte döljas från kalkylbladsområdet. Du måste använda en VBA -kod eller egenskapsfönstret för att visa den.

Om du bara vill dölja ark som enkelt kan döljas, använd nedanstående kod:

Sub HideAllExceptActiveSheet () Dim Ws som arbetsblad för varje Ws i ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Sedan Ws.Visible = xlSheetHidden Next Ws End Sub

Koden nedan skulle avslöja alla kalkylblad (både dolda och mycket dolda).

Sub UnhideAllWoksheets () Dim Ws som arbetsblad för varje Ws i ThisWorkbook.Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

Relaterad artikel: Visa alla ark i Excel (på en gång)

Dölj ark baserat på texten i den

Anta att du har flera blad med namnet på olika avdelningar eller år och du vill dölja alla blad utom de som har år2021-2022 i det.

Du kan göra detta med en VBA INSTR -funktion.

Koden nedan skulle dölja alla blad utom de med texten2021-2022 i.

Sub HideWithMatchingText () Dim Ws som arbetsblad för varje Ws i kalkylblad Om InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Då Ws.Visible = xlSheetHidden End Om nästa Ws slutar Sub

I koden ovan returnerar INSTR -funktionen positionen för tecknet där den hittar matchande sträng. Om den inte hittar matchande sträng returnerar den 0.

Ovanstående kod kontrollerar om namnet innehåller texten2021-2022. Om det gör det händer ingenting, annars är kalkylbladet dolt.

Du kan ta detta ett steg längre genom att ha texten i en cell och använda den cellen i koden. Detta gör att du kan ha ett värde i cellen och sedan när du kör makrot skulle alla blad, utom det med matchande text i, förbli synliga (tillsammans med de ark där du anger värdet i cell).

Sortera kalkylblad i alfabetisk ordning

Med VBA kan du snabbt sortera kalkylblad baserat på deras namn.

Om du till exempel har en arbetsbok som har blad för olika avdelningar eller år, kan du använda koden nedan för att snabbt sortera dessa blad i stigande ordning.

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 < Sheets (i) .Name Sedan Sheets (j) .Flytta innan: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub

Observera att denna kod fungerar bra med textnamn och i de flesta fall med år och nummer också. Men det kan ge dig fel resultat om du har bladnamnen som 1,2,11. Det kommer att sortera och ge dig sekvensen 1, 11, 2. Detta beror på att den gör jämförelsen som text och anser att 2 är större än 11.

Skydda/avskydda alla ark på en gång

Om du har många kalkylblad i en arbetsbok och du vill skydda alla blad kan du använda VBA -koden nedan.

Det låter dig ange lösenordet i koden. Du behöver det här lösenordet för att avskydda kalkylbladet.

Sub ProtectAllSheets () Dim ws As Worksheet Dim lösenord As String password = "Test123" 'ersätt Test123 med det lösenord du vill ha För varje ws i Worksheets ws.Protect password: = password Next ws End Sub

Följande kod skulle avskydda alla ark på en gång.

Sub ProtectAllSheets () Dim ws As Worksheet Dim lösenord As String password = "Test123" 'ersätt Test123 med lösenordet som du använde när du skyddade För varje ws I Worksheets ws.Unprotect password: = password Next ws End Sub

Skapa en innehållsförteckning för alla kalkylblad (med hyperlänkar)

Om du har en uppsättning kalkylblad i arbetsboken och du snabbt vill infoga ett sammanfattningsblad som har länkar till alla blad kan du använda koden nedan.

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

Ovanstående kod infogar ett nytt kalkylblad och heter det Index.

Den går sedan igenom alla kalkylblad och skapar en hyperlänk för alla kalkylblad i indexbladet.

Var ska man lägga VBA -koden

Undrar du vart VBA -koden går i din Excel -arbetsbok?

Excel har en VBA -backend som kallas VBA -editor. Du måste kopiera och klistra in koden i fönstret VB Editor -modulkod.

Här är stegen för att göra detta:

1. Gå till fliken Utvecklare.
2. Klicka på alternativet Visual Basic. Detta öppnar VB -redigeraren i backend.
3. I fönstret Projektutforskare i VB-redigeraren högerklickar du på valfritt objekt för arbetsboken där du vill infoga koden. Om du inte ser Project Explorer går du till fliken View och klickar på Project Explorer.
4. Gå till Infoga och klicka på Modul. Detta kommer att infoga ett modulobjekt för din arbetsbok.
5. Kopiera och klistra in koden i modulfönstret.

Du kanske också gillar följande Excel VBA -självstudier:

• Arbeta med arbetsböcker med VBA.
• Använda IF Then Else Statements i VBA.
• För Next Loop i VBA.
• Skapa en användardefinierad funktion i Excel.
• Hur man spelar in ett makro i Excel.
• Hur man kör ett makro i Excel.
• Excel VBA -evenemang - En enkel (och komplett) guide.
• Hur man skapar ett tillägg i Excel.
• Hur man sparar och återanvänder makro med hjälp av Excel Personal Macro Workbook.