Arbeide med regneark ved hjelp av Excel VBA (forklart med eksempler)

Bortsett fra celler og områder, er arbeid med regneark et annet område du bør vite om for å bruke VBA effektivt i Excel.

Akkurat som ethvert objekt i VBA, har regneark forskjellige egenskaper og metoder knyttet til det som du kan bruke mens du automatiserer arbeidet ditt med VBA i Excel.

I denne opplæringen vil jeg dekke ‘Arbeidsark’ i detalj og også vise deg noen praktiske eksempler.

Så la oss komme i gang.

Alle kodene jeg nevner i denne opplæringen må plasseres i VB Editor. Gå til delen "Hvor skal du sette VBA -koden" for å vite hvordan den fungerer.

Hvis du er interessert i å lære VBA på den enkle måten, sjekk min Online Excel VBA -opplæring.

Forskjell mellom regneark og ark i VBA

I VBA har du to samlinger som til tider kan være litt forvirrende.

I en arbeidsbok kan du ha regneark og diagrammer. Eksemplet nedenfor har tre regneark og ett diagramark.

I Excel VBA:

• "Regneark" -samlingen vil referere til samlingen av alle regnearkobjektene i en arbeidsbok. I eksemplet ovenfor vil arbeidsarksamlingen bestå av tre regneark.
• "Ark" -samlingen vil referere til alle regnearkene samt diagramarkene i arbeidsboken. I eksemplet ovenfor vil den ha fire elementer - 3 regneark + 1 diagramark.

Hvis du har en arbeidsbok som bare har regneark og ingen diagramark, er samlingen "Regneark" og "Ark" den samme.

Men når du har ett eller flere diagramark, vil "Ark" -samlingen være større enn "Regneark" -samlingen

Ark = Regneark + Diagramark

Nå med dette skillet, anbefaler jeg å være så spesifikk som mulig når du skriver en VBA -kode.

Så hvis du bare må referere til regneark, bruk "Regneark" -samlingen, og hvis du må referere til alle ark (inkludert diagramark), bruk "Ark" -samlingen.

I denne opplæringen bruker jeg bare "Regneark" -samlingen.

Refererer til et regneark i VBA

Det er mange forskjellige måter du kan bruke til å referere til et regneark i VBA.

Å forstå hvordan du refererer til regneark vil hjelpe deg med å skrive bedre kode, spesielt når du bruker sløyfer i VBA -koden.

Bruke regnearkets navn

Den enkleste måten å referere til et regneark er å bruke navnet.

Anta for eksempel at du har en arbeidsbok med tre regneark - Ark 1, Ark 2, Ark 3.

Og du vil aktivere Sheet 2.

Du kan gjøre det ved å bruke følgende kode: Sub ActivateSheet () Regneark ("Sheet2"). Aktiver End Sub

Koden ovenfor ber VBA om å referere til Sheet2 i regnearksamlingen og aktivere den.

Siden vi bruker det nøyaktige arkenavnet, kan du også bruke Sheets -samlingen her. Så koden nedenfor vil også gjøre det samme.

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

Bruke indeksnummeret

Selv om bruk av arkenavnet er en enkel måte å referere til et regneark, kan det hende at du kanskje ikke vet det eksakte navnet på regnearket.

For eksempel, hvis du bruker en VBA -kode for å legge til et nytt regneark i arbeidsboken, og du ikke vet hvor mange regneark som allerede er der, ville du ikke vite navnet på det nye regnearket.

I dette tilfellet kan du bruke indeksnummeret til regnearkene.

Anta at du har følgende ark i en arbeidsbok:

Koden nedenfor vil aktivere Sheet2:

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

Vær oppmerksom på at vi har brukt indeks nummer 2 in Arbeidsark (2). Dette vil referere til det andre objektet i samlingen av regnearkene.

Hva skjer når du bruker 3 som indeksnummer?

Det vil velge Sheet3.

Hvis du lurer på hvorfor det er valgt Sheet3, ettersom det klart er det fjerde objektet.

Dette skjer fordi et diagramark ikke er en del av regnearksamlingen.

Så når vi bruker indeksnumrene i regnearksamlingen, vil det bare referere til regnearkene i arbeidsboken (og ignorere diagramarkene).

Tvert imot, hvis du bruker Sheets, vil Sheets (1) referere til Sheets1, Sheets (2) ville referere til Sheet2, Sheets (3) ville referere til Chart1 og Sheets (4) ville referere til Sheet3.

Denne teknikken for å bruke indeksnummer er nyttig når du vil gå gjennom alle regnearkene i en arbeidsbok. Du kan telle antall regneark og deretter gå gjennom disse ved hjelp av denne tellingen (vi får se hvordan du gjør dette senere i denne opplæringen).

Merk: Indeksnummeret går fra venstre til høyre. Så hvis du flytter Ark2 til venstre for Ark1, vil Arbeidsark (1) referere til Ark2.

Bruke regnearkets kodenavn

En av ulempene med å bruke arknavnet (som vi så i avsnittet ovenfor) er at en bruker kan endre det.

Og hvis arknavnet er endret, fungerer ikke koden din før du endrer navnet på regnearket i VBA -koden også.

For å løse dette problemet kan du bruke kodenavnet til regnearket (i stedet for det vanlige navnet som vi har brukt så langt). Et kodenavn kan tilordnes i VB Editor og endres ikke når du endrer navnet på arket fra regnearkområdet.

Følg trinnene nedenfor for å gi regnearket et kodenavn:

1. Klikk på Utvikler -fanen.
2. Klikk på Visual Basic -knappen. Dette åpner VB Editor.
3. Klikk på Vis -alternativet i menyen og klikk på Prosjektvindu. Dette vil gjøre ruten Egenskaper synlig. Hvis egenskapsruten allerede er synlig, hopper du over dette trinnet.
4. Klikk på arkenavnet i prosjektutforskeren du vil gi nytt navn til.
5. I egenskapsruten endrer du navnet i feltet foran (Navn). Vær oppmerksom på at du ikke kan ha mellomrom i navnet.

Trinnene ovenfor vil endre navnet på regnearket i VBA -backend. I Excel -regnearkvisningen kan du navngi regnearket hva du vil, men i backend vil det svare på både navnene - arknavnet og kodenavnet.

I bildet ovenfor er arkenavnet 'Arknavn' og kodenavnet er 'Kodenavn'. Selv om du endrer arknavnet på regnearket, forblir kodenavnet det samme.

Nå kan du enten bruke regnearksamlingen til å referere til regnearket eller bruke kodenavnet.

For eksempel vil begge linjene aktivere regnearket.

Regneark ("Arknavn"). Aktiver Kodenavn.Aktiver

Forskjellen på disse to er at hvis du endrer navnet på regnearket, ville det første ikke fungere. Men den andre linjen vil fortsette å fungere selv med det endrede navnet. Den andre linjen (ved hjelp av kodenavnet) er også kortere og enklere å bruke.

Henvisning til et regneark i en annen arbeidsbok

Hvis du vil referere til et regneark i en annen arbeidsbok, må arbeidsboken være åpen mens koden kjører, og du må angi navnet på arbeidsboken og regnearket du vil referere til.

For eksempel, hvis du har en arbeidsbok med navnet Eksempler og du vil aktivere Ark1 i Eksempel -arbeidsboken, må du bruke koden nedenfor:

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

Vær oppmerksom på at hvis arbeidsboken er lagret, må du bruke filnavnet sammen med utvidelsen. Hvis du ikke er sikker på hvilket navn du skal bruke, ta hjelp fra Project Explorer.

Hvis arbeidsboken ikke er lagret, trenger du ikke å bruke filtypen.

Legge til et regneark

Koden nedenfor vil legge til et regneark (som det første regnearket - det vil si som arket lengst til venstre i arkfanen).

Sub AddSheet () Regneark. Legg til End Sub

Det tar standardnavnet Sheet2 (eller et annet nummer basert på hvor mange ark som allerede er der).

Hvis du vil at et regneark skal legges til før et bestemt regneark (si Ark2), kan du bruke koden nedenfor.

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

Koden ovenfor forteller VBA å legge til et ark og bruker deretter "Før" -setningen til å spesifisere regnearket som det nye regnearket skal settes inn før.

På samme måte kan du også legge til et ark etter et regneark (si Sheet2) ved å bruke koden nedenfor:

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

Hvis du vil at det nye arket skal legges til slutten av arkene, må du først vite hvor mange ark det er. Følgende kode teller først antall ark, og legger til det nye arket etter det siste arket (som vi refererer til ved hjelp av indeksnummeret).

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

Slette et regneark

Koden nedenfor vil slette det aktive arket fra arbeidsboken.

Sub DeleteSheet () ActiveSheet.Delete End Sub

Koden ovenfor viser en advarsel før du sletter regnearket.

Hvis du ikke vil se advarselen, bruker du koden nedenfor:

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

Når Application.DisplayAlerts er satt til False, vil det ikke vise deg en advarsel. Hvis du bruker den, husk å sette den tilbake til True på slutten av koden.

Husk at du ikke kan angre denne slettingen, så bruk koden ovenfor når du er helt sikker.

Hvis du vil slette et bestemt ark, kan du gjøre det ved å bruke følgende kode:

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

Du kan også bruke kodenavnet til arket for å slette det.

Sub DeleteSheet () Sheet 5. Slett End Sub

Gi nytt navn til regnearkene

Du kan endre navnegenskapen til regnearket for å endre navnet.

Følgende kode vil endre navnet på Sheet1 til 'Summary'.

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

Du kan kombinere dette med metoden for å legge til ark for å ha et sett med ark med spesifikke navn.

Hvis du for eksempel vil sette inn fire ark med navnet2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 og2021-2022 Q4, kan du bruke koden nedenfor.

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

I koden ovenfor teller vi først antall ark og bruker deretter en For Next loop til å sette inn nye ark på slutten. Etter hvert som arket legges til, gir koden det også nytt navn.

Tilordne regnearkobjekt til en variabel

Når du arbeider med regneark, kan du tilordne et regneark til en objektvariabel, og deretter bruke variabelen i stedet for regnearkreferansene.

For eksempel, hvis du vil legge til et års prefiks i alle regnearkene, kan du bruke objektvariabelen i stedet for å telle arkene og løpe løkken så mange ganger.

Her er koden som vil legge til2021-2022 som et prefiks til alle regnearkets navn.

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

Koden ovenfor erklærer en variabel Ws som regnearketypen (ved hjelp av linjen 'Dim Ws As Worksheet').

Nå trenger vi ikke telle antall ark for å gå gjennom disse. I stedet kan vi bruke loop for ‘For each Ws in Worksheets’. Dette vil tillate oss å gå gjennom alle arkene i regnearksamlingen. Det spiller ingen rolle om det er 2 eller 20 ark.

Selv om koden ovenfor lar oss gå gjennom alle arkene, kan du også tilordne et bestemt ark til en variabel.

I koden nedenfor tildeler vi variabelen Ws til Sheet2 og bruker den til å få tilgang til alle Sheet2s egenskaper.

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

Når du har angitt en regnearksreferanse til en objektvariabel (ved bruk av SET -setningen), kan objektet brukes i stedet for regnearkreferansen. Dette kan være nyttig når du har en lang komplisert kode og du vil endre referansen. I stedet for å gjøre endringen overalt, kan du ganske enkelt gjøre endringen i SET -setningen.

Vær oppmerksom på at koden deklarerer Ws -objektet som regnearkstypevariabel (ved hjelp av linjen Dim Ws som regneark).

Skjul regneark ved hjelp av VBA (skjult + veldig skjult)

Skjule og skjule regneark i Excel er en grei oppgave.

Du kan skjule et regneark, og brukeren vil ikke se det når han/hun åpner arbeidsboken. Imidlertid kan de enkelt vise regnearket ved å høyreklikke på en arkfane.

Men hva om du ikke vil at de skal kunne skjule regnearket (e).

Du kan gjøre dette ved hjelp av VBA.

Koden nedenfor vil skjule alle regnearkene i arbeidsboken (unntatt det aktive arket), slik at du ikke kan vise det ved å høyreklikke på arkenavnet.

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 koden ovenfor endres Ws.Visible -egenskapen til xlSheetVeryHidden.

• Når den synlige egenskapen er angitt til xlSheetVisible, er arket synlig i regnearkområdet (som regnearkfaner).
• Når den synlige egenskapen er satt til xlSheetHidden, er arket skjult, men brukeren kan vise det ved å høyreklikke på en arkfane.
• Når den synlige egenskapen er satt til xlSheetVeryHidden, er arket skjult og kan ikke fjernes fra regnearkområdet. Du må bruke en VBA -kode eller egenskapsvinduet for å vise det.

Hvis du bare vil skjule ark som enkelt kan skjules, bruker du koden nedenfor:

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

Koden nedenfor vil vise alle regnearkene (både skjult og veldig skjult).

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

Relatert artikkel: Vis alle ark i Excel (på en gang)

Skjul ark basert på teksten i den

Anta at du har flere ark med navnet på forskjellige avdelinger eller år, og at du vil skjule alle arkene bortsett fra de som har år2021-2022.

Du kan gjøre dette ved å bruke en VBA INSTR -funksjon.

Koden nedenfor vil skjule alle arkene bortsett fra de 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 koden ovenfor returnerer INSTR -funksjonen posisjonen til tegnet der den finner den matchende strengen. Hvis den ikke finner den matchende strengen, returnerer den 0.

Koden ovenfor kontrollerer om navnet har teksten2021-2022 i seg. Hvis det gjør det, skjer ingenting, ellers er regnearket skjult.

Du kan ta dette et skritt videre ved å ha teksten i en celle og bruke den cellen i koden. Dette lar deg ha en verdi i cellen, og når du kjører makroen, vil alle arkene, bortsett fra det med den matchende teksten i, forbli synlige (sammen med arkene der du skriver inn verdien i celle).

Sortere regnearkene i en alfabetisk rekkefølge

Ved å bruke VBA kan du raskt sortere regnearkene basert på navnene deres.

For eksempel, hvis du har en arbeidsbok som har ark for forskjellige avdelinger eller år, kan du bruke koden nedenfor for raskt å sortere disse arkene i stigende rekkefø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 Then Sheets (j) .Flytt før: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub

Vær oppmerksom på at denne koden fungerer godt med tekstnavn og i de fleste tilfeller med år og tall også. Men det kan gi deg feil resultat hvis du har bladnavnene som 1,2,11. Den vil sortere og gi deg sekvensen 1, 11, 2. Dette er fordi den gjør sammenligningen som tekst og anser 2 større enn 11.

Beskytt/fjern beskyttelsen av alle arkene på en gang

Hvis du har mange regneark i en arbeidsbok og du vil beskytte alle arkene, kan du bruke VBA -koden nedenfor.

Den lar deg spesifisere passordet i koden. Du trenger dette passordet for å fjerne beskyttelsen av regnearket.

Sub ProtectAllSheets () Dim ws As Worksheet Dim passord As String password = "Test123" 'erstatt Test123 med passordet du vil ha For hver ws I regneark ws.Protect password: = password Next ws End Sub

Følgende kode vil fjerne beskyttelsen av alle arkene på en gang.

Sub ProtectAllSheets () Dim ws As Worksheet Dim passord As String password = "Test123" 'erstatt Test123 med passordet du brukte mens du beskyttet For hver ws I regneark ws.Unbeskytt passord: = passord Neste ws End Sub

Opprette en innholdsfortegnelse for alle regneark (med hyperkoblinger)

Hvis du har et sett med regneark i arbeidsboken og du raskt vil sette inn et sammendragsark som har koblingene til alle arkene, kan du bruke koden nedenfor.

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: = Worksheets (i) .Name Next i End Sub

Koden ovenfor setter inn et nytt regneark og gir det navnet Index.

Den går deretter gjennom alle regnearkene og oppretter en hyperkobling for alle regnearkene i indeksarket.

Hvor å plassere VBA -koden

Lurer du på hvor VBA -koden går i Excel -arbeidsboken din?

Excel har en VBA -backend kalt VBA -redaktøren. Du må kopiere og lime inn koden i VB Editor -modulvinduet.

Her er trinnene for å gjøre dette:

1. Gå til kategorien Utvikler.
2. Klikk på Visual Basic -alternativet. Dette vil åpne VB -redaktøren i backend.
3. I Project Explorer-ruten i VB Editor, høyreklikker du på et hvilket som helst objekt for arbeidsboken der du vil sette inn koden. Hvis du ikke ser Project Explorer, går du til kategorien View og klikker på Project Explorer.
4. Gå til Sett inn og klikk på Modul. Dette vil sette inn et modulobjekt for arbeidsboken din.
5. Kopier og lim inn koden i modulvinduet.

Du kan også like følgende Excel VBA -opplæringsprogrammer:

• Arbeide med arbeidsbøker ved hjelp av VBA.
• Bruke IF Then Else Statements i VBA.
• For Next Loop i VBA.
• Opprette en brukerdefinert funksjon i Excel.
• Slik spiller du inn en makro i Excel.
• Hvordan kjøre en makro i Excel.
• Excel VBA Events - En enkel (og komplett) guide.
• Hvordan lage et tillegg i Excel.
• Hvordan lagre og gjenbruke makro ved hjelp av Excel Personal Macro Workbook.