Arbejde med regneark ved hjælp af Excel VBA (forklaret med eksempler)

Bortset fra celler og områder er arbejde med regneark et andet område, du bør vide om at bruge VBA effektivt i Excel.

Ligesom ethvert objekt i VBA har regneark forskellige egenskaber og metoder forbundet med det, som du kan bruge, mens du automatiserer dit arbejde med VBA i Excel.

I denne vejledning dækker jeg 'Arbejdsark' i detaljer og viser dig også nogle praktiske eksempler.

Så lad os komme i gang.

Alle de koder, jeg nævner i denne vejledning, skal placeres i VB Editor. Gå til afsnittet 'Hvor skal man placere VBA -koden' for at vide, hvordan det fungerer.

Hvis du er interesseret i at lære VBA på den nemme måde, så tjek min Online Excel VBA -træning.

Forskel mellem regneark og ark i VBA

I VBA har du to samlinger, der til tider kan være lidt forvirrende.

I en projektmappe kan du have regneark og diagramark. Eksemplet herunder har tre regneark og et diagramark.

I Excel VBA:

• "Arbejdsark" -samlingen refererer til samlingen af ​​alle regnearksobjekterne i en projektmappe. I eksemplet ovenfor vil regnearkssamlingen bestå af tre regneark.
• "Ark" -samlingen vil referere til alle regnearkene samt diagramarkene i projektmappen. I eksemplet ovenfor ville det have fire elementer - 3 regneark + 1 diagramark.

Hvis du har en projektmappe, der kun har regneark og ingen diagramark, er samlingen "Regneark" og "Ark" den samme.

Men når du har et eller flere diagramark, ville 'Ark' -samlingen være større end 'Arbejdsark' -samlingen

Ark = Regneark + Diagramark

Nu med denne forskel anbefaler jeg at være så specifik som muligt, når du skriver en VBA -kode.

Så hvis du kun skal henvise til regneark, skal du bruge "Regneark" -samlingen, og hvis du skal henvise til alle ark (inklusive diagramark), skal du bruge "Ark" -samlingen.

I denne vejledning bruger jeg kun 'Worksheets' -samlingen.

Henvisning til et regneark i VBA

Der er mange forskellige måder, du kan bruge til at henvise til et regneark i VBA.

At forstå, hvordan man henviser til regneark, ville hjælpe dig med at skrive bedre kode, især når du bruger sløjfer i din VBA -kode.

Brug af regnearksnavnet

Den letteste måde at henvise til et regneark er at bruge dets navn.

Antag f.eks., At du har en projektmappe med tre regneark - Ark 1, Ark 2, Ark 3.

Og du vil aktivere Sheet 2.

Du kan gøre det ved hjælp af følgende kode: Sub ActivateSheet () Worksheets ("Sheet2"). Aktiver End Sub

Ovenstående kode beder VBA om at henvise til Sheet2 i regnearkssamlingen og aktivere den.

Da vi bruger det nøjagtige arknavn, kan du også bruge Sheets -samlingen her. Så nedenstående kode ville også gøre det samme.

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

Brug af indeksnummeret

Selvom det er en let måde at henvise til et regneark ved at bruge bladnavnet, kender du måske nogle gange ikke det nøjagtige navn på regnearket.

Hvis du f.eks. Bruger en VBA -kode til at tilføje et nyt regneark til projektmappen, og du ikke ved, hvor mange regneark der allerede er der, ville du ikke kende navnet på det nye regneark.

I dette tilfælde kan du bruge indeksnummeret på regnearkene.

Antag, at du har følgende ark i en projektmappe:

Nedenstående kode aktiverer Sheet2:

Sub ActivateSheet () Regneark (2) .Aktiver Slut Sub

Bemærk, at vi har brugt indeks nummer 2 in Arbejdsark (2). Dette ville referere til det andet objekt i samlingen af ​​regnearkene.

Hvad sker der nu, når du bruger 3 som indeksnummer?

Det vælger Sheet3.

Hvis du undrer dig over, hvorfor det valgte Sheet3, da det klart er det fjerde objekt.

Dette sker, fordi et diagramark ikke er en del af regnearkssamlingen.

Så når vi bruger indeksnumrene i regnearkssamlingen, refererer det kun til regnearkene i projektmappen (og ignorer diagramarkene).

Tværtimod, hvis du bruger Sheets, refererer Sheets (1) til Sheets1, Sheets (2) henviser til Sheet2, Sheets (3) henviser til Chart1 og Sheets (4) refererer til Sheet3.

Denne teknik til brug af indeksnummer er nyttig, når du vil gå gennem alle regnearkene i en projektmappe. Du kan tælle antallet af regneark og derefter gå igennem disse ved hjælp af dette antal (vi vil se, hvordan du gør dette senere i denne vejledning).

Bemærk: Indeksnummeret går fra venstre mod højre. Så hvis du flytter Sheet2 til venstre for Sheet1, vil regneark (1) referere til Sheet2.

Brug af regnearkets kodenavn

En af ulemperne ved at bruge arknavnet (som vi så i afsnittet ovenfor) er, at en bruger kan ændre det.

Og hvis arknavnet er blevet ændret, fungerer din kode ikke, før du også ændrer navnet på regnearket i VBA -koden.

For at løse dette problem kan du bruge kodenavnet på regnearket (i stedet for det normale navn, som vi hidtil har brugt). Et kodenavn kan tildeles i VB Editor og ændres ikke, når du ændrer navnet på arket fra regnearksområdet.

Følg nedenstående trin for at give dit regneark et kodenavn:

1. Klik på fanen Udvikler.
2. Klik på knappen Visual Basic. Dette åbner VB Editor.
3. Klik på indstillingen Vis i menuen, og klik på Projektvindue. Dette vil gøre ruden Egenskaber synlig. Hvis ruden Egenskaber allerede er synlig, skal du springe dette trin over.
4. Klik på arknavnet i den projektudforsker, du vil omdøbe.
5. I ruden Egenskaber skal du ændre navnet i feltet foran (Navn). Bemærk, at du ikke kan have mellemrum i navnet.

Ovenstående trin ændrer navnet på dit regneark i VBA -backend. I Excel -regnearksvisning kan du navngive regnearket, hvad du vil, men i backend reagerer det på både navnene - arknavnet og kodenavnet.

I billedet ovenfor er arknavnet 'Arknavn' og kodenavnet er 'Kodenavn'. Selvom du ændrer arknavnet på regnearket, forbliver kodenavnet stadig det samme.

Nu kan du enten bruge regnearkssamlingen til at henvise til regnearket eller bruge kodenavnet.

For eksempel vil begge linier aktivere regnearket.

Regneark ("Arknavn"). Aktiver Kodenavn.Aktiver

Forskellen i disse to er, at hvis du ændrer navnet på regnearket, ville det første ikke fungere. Men den anden linje ville fortsætte med at fungere, selv med det ændrede navn. Den anden linje (ved hjælp af kodenavnet) er også kortere og lettere at bruge.

Henvisning til et regneark i en anden arbejdsbog

Hvis du vil henvise til et regneark i en anden projektmappe, skal denne projektmappe være åben, mens koden kører, og du skal angive navnet på den projektmappe og det regneark, du vil henvise til.

Hvis du f.eks. Har en projektmappe med navnet Eksempler, og du vil aktivere Ark1 i projektmappen Eksempel, skal du bruge nedenstående kode:

Sub SheetActivate () Workbooks ("Eksempler.xlsx"). Regneark ("Sheet1"). Aktiver End Sub

Bemærk, at hvis projektmappen er gemt, skal du bruge filnavnet sammen med udvidelsen. Hvis du ikke er sikker på, hvilket navn du skal bruge, skal du tage hjælp fra Project Explorer.

Hvis projektmappen ikke er gemt, behøver du ikke bruge filtypen.

Tilføjelse af et regneark

Nedenstående kode ville tilføje et regneark (som det første regneark - dvs. som det ark, der sidder længst til venstre i arkfanen).

Sub AddSheet () -ark. Tilføj tilføjelsessub

Det tager standardnavnet Sheet2 (eller et andet nummer baseret på hvor mange ark der allerede er der).

Hvis du vil have et regneark tilføjet før et specifikt regneark (f.eks. Ark2), kan du bruge nedenstående kode.

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

Ovenstående kode fortæller VBA at tilføje et ark og bruger derefter 'Før' -sætningen til at angive det regneark, inden hvilket det nye regneark skal indsættes.

På samme måde kan du også tilføje et ark efter et regneark (f.eks. Sheet2) ved hjælp af nedenstående kode:

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

Hvis du vil have det nye ark tilføjet til slutningen af ​​arkene, skal du først vide, hvor mange ark der er. Den følgende kode tæller først antallet af ark, og den tilføjer det nye ark efter det sidste ark (som vi henviser til ved hjælp af indeksnummeret).

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

Sletning af et regneark

Nedenstående kode vil slette det aktive ark fra projektmappen.

Sub DeleteSheet () ActiveSheet.Delete End Sub

Ovenstående kode viser en advarselsmeddelelse, før regnearket slettes.

Hvis du ikke vil se advarselsmeddelelsen, skal du bruge nedenstående kode:

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

Når Application.DisplayAlerts er indstillet til Falsk, viser det dig ikke advarselsmeddelelsen. Hvis du bruger det, skal du huske at sætte det tilbage til True i slutningen af ​​koden.

Husk, at du ikke kan fortryde denne sletning, så brug ovenstående kode, når du er helt sikker.

Hvis du vil slette et bestemt ark, kan du gøre det ved hjælp af følgende kode:

Sub DeleteSheet () Regneark ("Sheet2"). Slet End Sub

Du kan også bruge arkets kodenavn til at slette det.

Sub DeleteSheet () Sheet 5. Slet End Sub

Omdøbning af regneark

Du kan ændre egenskabens navn i regnearket for at ændre dets navn.

Den følgende kode ændrer navnet på Sheet1 til 'Resumé'.

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

Du kan kombinere dette med tilføjelsesarkmetoden for at have et sæt ark med bestemte navne.

Hvis du f.eks. Vil indsætte fire ark med navnet2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 og2021-2022 Q4, kan du bruge nedenstående kode.

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 Næste i Afslut Sub

I ovenstående kode tæller vi først antallet af ark og bruger derefter en For Next loop til at indsætte nye ark i slutningen. Når arket tilføjes, omdøber koden det også.

Tildeling af regnearksobjekt til en variabel

Når du arbejder med regneark, kan du tildele et regneark til en objektvariabel og derefter bruge variablen i stedet for regnearksreferencer.

For eksempel, hvis du vil tilføje et års præfiks til alle regnearkene, kan du bruge objektvariablen i stedet for at tælle arkene og løbe løkken så mange gange.

Her er koden, der tilføjer2021-2022 som et præfiks til alle regnearkets navne.

Sub RenameSheet () Dim Ws som regneark for hver Ws i regneark Ws.Name = "2018 -" & Ws.Name Næste Ws End Sub

Ovenstående kode erklærer en variabel Ws som regnearkstypen (ved hjælp af linjen 'Dim Ws As Worksheet').

Nu behøver vi ikke tælle antallet af ark til at gå igennem disse. I stedet kan vi bruge 'For hver Ws i regneark' -loop. Dette giver os mulighed for at gennemgå alle arkene i regnearkssamlingen. Det er ligegyldigt om der er 2 eller 20 ark.

Selvom ovenstående kode giver os mulighed for at gennemse alle arkene, kan du også tildele et variabelt et specifikt ark.

I nedenstående kode tildeler vi variablen Ws til Sheet2 og bruger den til at få adgang til alle Sheet2's egenskaber.

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

Når du har angivet en regnearksreference til en objektvariabel (ved hjælp af SET -sætningen), kan objektet bruges i stedet for regnearksreferencen. Dette kan være nyttigt, når du har en lang kompliceret kode, og du vil ændre referencen. I stedet for at foretage ændringen overalt, kan du blot foretage ændringen i SET -sætningen.

Bemærk, at koden erklærer Ws -objektet som variabel i regnearkstypen (ved hjælp af linjen Dim Ws som regneark).

Skjul regneark ved hjælp af VBA (skjult + meget skjult)

At skjule og skjule regneark i Excel er en ligetil opgave.

Du kan skjule et regneark, og brugeren ville ikke se det, når han/hun åbner projektmappen. De kan dog nemt vise regnearket ved at højreklikke på en hvilken som helst faneblad.

Men hvad nu hvis du ikke vil have dem til at kunne skjule regnearket / regnearkene.

Du kan gøre dette ved hjælp af VBA.

Koden herunder ville skjule alle regnearkene i projektmappen (undtagen det aktive ark), så du ikke kan fjerne det ved at højreklikke på arknavnet.

Sub HideAllExcetActiveSheet () Dim Ws som regneark for hver Ws i ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden Next Ws End Sub

I ovenstående kode ændres egenskaben Ws.Visible til xlSheetVeryHidden.

• Når egenskaben Synlig er angivet til xlSheetVisible, er arket synligt i regnearksområdet (som faner i regneark).
• Når egenskaben Synlig er angivet til xlSheetHidden, er arket skjult, men brugeren kan fjerne det ved at højreklikke på en vilkårlig arkfane.
• Når egenskaben Synlig er angivet til xlSheetVeryHidden, er arket skjult og kan ikke fjernes fra regnearkområdet. Du skal bruge en VBA -kode eller vinduet egenskaber for at vise det.

Hvis du blot vil skjule ark, der let kan skjules, skal du bruge nedenstående kode:

Sub HideAllExceptActiveSheet () Dim Ws som regneark for hver Ws i ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetHidden Next Ws End Sub

Nedenstående kode ville fjerne alle regneark (både skjult og meget skjult).

Sub UnhideAllWoksheets () Dim Ws som regneark for hver Ws i ThisWorkbook.Worksheets Ws.Visible = xlSheetVisible Næste Ws End Sub

Relateret artikel: Vis alle ark i Excel (på én gang)

Skjul ark baseret på teksten i den

Antag, at du har flere ark med navnet på forskellige afdelinger eller år, og du vil skjule alle arkene undtagen dem, der har år2021-2022 i det.

Du kan gøre dette ved hjælp af en VBA INSTR -funktion.

Nedenstående kode vil skjule alle arkene undtagen dem med teksten2021-2022 i den.

Sub HideWithMatchingText () Dim Ws som regneark for hver Ws i regneark If InStr (1, Ws.Name, "2018", vbBinaryCompare) = 0 Then Ws.Visible = xlSheetHidden End If Next Ws End Sub

I ovenstående kode returnerer INSTR -funktionen placeringen af ​​tegnet, hvor den finder den matchende streng. Hvis den ikke finder den matchende streng, returnerer den 0.

Ovenstående kode kontrollerer, om navnet har teksten2021-2022 i sig. Hvis det sker, sker der ikke noget, ellers er regnearket skjult.

Du kan tage dette et skridt videre ved at have teksten i en celle og bruge den celle i koden. Dette giver dig mulighed for at have en værdi i cellen, og når du kører makroen, forbliver alle arkene undtagen det med den matchende tekst i det (sammen med de ark, hvor du indtaster værdien i celle).

Sortering af regneark i alfabetisk rækkefølge

Ved hjælp af VBA kan du hurtigt sortere regneark baseret på deres navne.

For eksempel, hvis du har en projektmappe, der har ark til forskellige afdelinger eller år, kan du bruge nedenstående kode til hurtigt at sortere disse ark i en stigende rækkefølge.

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 Herefter Sheets (j) .Flyt før: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub

Bemærk, at denne kode fungerer godt med tekstnavne og i de fleste tilfælde også med årstal og tal. Men det kan give dig de forkerte resultater, hvis du har arknavnene som 1,2,11. Det vil sortere og give dig sekvensen 1, 11, 2. Dette er fordi den gør sammenligningen som tekst og anser 2 større end 11.

Beskyt/fjern beskyttelsen af ​​alle arkene på én gang

Hvis du har mange regneark i en projektmappe, og du vil beskytte alle arkene, kan du bruge VBA -koden herunder.

Det giver dig mulighed for at angive adgangskoden i koden. Du skal bruge denne adgangskode for at afbeskytte regnearket.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'erstat Test123 med den adgangskode, du ønsker For hver ws I regneark ws.Protect password: = password Næste ws End Sub

Følgende kode ville fjerne alle arkene på én gang.

Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123" 'erstat Test123 med den adgangskode, du brugte, mens du beskyttede For hver ws I regneark ws.Unotot password: = password Næste ws End Sub

Oprettelse af en indholdsfortegnelse for alle regneark (med hyperlinks)

Hvis du har et sæt regneark i projektmappen, og du hurtigt vil indsætte et resuméark, der har links til alle arkene, kan du bruge nedenstående kode.

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) .Name & "! A1", _ TextToDisplay: = Regneark (i) .Name Næste i End Sub

Ovenstående kode indsætter et nyt regneark og navngiver det Indeks.

Det går derefter gennem alle regnearkene og opretter et hyperlink til alle regnearkene i indeksarket.

Hvor skal man placere VBA -koden

Gad vide, hvor VBA -koden går i din Excel -projektmappe?

Excel har en VBA -backend kaldet VBA -editoren. Du skal kopiere og indsætte koden i vinduet VB Editor -modulkode.

Her er trinene til at gøre dette:

1. Gå til fanen Udvikler.
2. Klik på indstillingen Visual Basic. Dette åbner VB -editoren i backend.
3. Højreklik på et objekt til projektmappen, hvor du vil indsætte koden, i ruden Project Explorer i VB Editor. Hvis du ikke kan se Project Explorer, skal du gå til fanen View og klikke på Project Explorer.
4. Gå til Indsæt og klik på Modul. Dette vil indsætte et modulobjekt til din projektmappe.
5. Kopier og indsæt koden i modulvinduet.

Du kan også lide følgende Excel VBA -vejledninger:

• Arbejde med projektmapper ved hjælp af VBA.
• Brug af IF Then Else Statements i VBA.
• Til Next Loop i VBA.
• Oprettelse af en brugerdefineret funktion i Excel.
• Sådan optages en makro i Excel.
• Sådan køres en makro i Excel.
• Excel VBA Events - En nem (og komplet) vejledning.
• Sådan opretter du et tilføjelsesprogram i Excel.
• Sådan gemmes og genbruges makro ved hjælp af Excel Personal Macro Workbook.