Dette er den ultimate guiden for å jobbe med ark / regneark i Excel.
Nederst i denne veiledningen har vi laget et jukseark med vanlige kommandoer for arbeid med ark.
Ark vs. Arbeidsark
Det er to måter å referere til ark ved hjelp av VBA. Den første er med Sheets -objektet:
| 1 | Ark ("Ark1"). Aktiver |
Den andre er med regnearkobjektet:
| 1 | Regneark ("Ark1"). Aktiver |
99% av tiden er disse to objektene identiske. Faktisk, hvis du har søkt på nettet etter eksempler på VBA -kode, har du sannsynligvis sett begge objektene brukt. Her er forskjellen:
Arksamlingen inneholder regneark OG diagramark.
Så bruk ark hvis du vil inkludere vanlige regneark og diagramark. Bruk regneark hvis du vil ekskludere diagramark. For resten av denne veiledningen bruker vi ark og regneark om hverandre.
Refererende ark
Det er flere forskjellige måter å referere til ark:
- Aktivt ark
- Arkfanenavn
- Arkindeksnummer
- Arkets kodenavn
Aktivt ark
ActiveSheet er arket som er aktivt. Med andre ord, hvis du satte koden din på pause og så på Excel, er det arket som er synlig. Kodeeksemplet nedenfor viser en meldingsboks med ActiveSheet -navnet.
| 1 | MsgBox ActiveSheet.Name |
Arkets navn
Du er sannsynligvis mest kjent med å referere til ark ved navn på fanen:
| 1 | Ark ("TabNavn"). Aktiver |
Arkindeksnummer
Bladindeksnummeret er arkposisjonen i arbeidsboken. 1 er det første arket. 2 er det andre arket osv.:
| 1 | Ark (1). Aktiver |
Arkindeksnummer - Siste ark i arbeidsbok
For å referere til det siste arket i arbeidsboken, bruk Sheets.Count for å få det siste indeksnummeret:
| 1 | Ark (Sheets.Count) .Aktiver |
Ark "Kodenavn"
Arkkodenavnet er objektnavnet i VBA:
| 1 | Kodenavn. Aktiver |
Refererende ark i andre arbeidsbøker
Det er også lett å referere til ark i andre arbeidsbøker. For å gjøre det må du bruke Workbooks Object:
| 1 | Arbeidsbøker ("VBA_Examples.xlsm"). Regneark ("Ark1"). Aktiver |
Viktig: Arbeidsboken må være åpen før du kan referere til arket.
Aktiver vs. Velg ark
I en annen artikkel diskuterer vi alt om aktivering og valg av ark. Kortversjonen er denne:
Når du aktiverer et ark, blir det ActiveSheet. Dette er arket du ville se hvis du så på Excel -programmet. Bare ett ark kan aktiveres om gangen.
Aktiver et ark
| 1 | Ark ("Ark1"). Aktiver |
Når du velger et ark, blir det også ActiveSheet. Du kan imidlertid velge flere ark samtidig. Når flere ark velges samtidig, er det "øverste" arket ActiveSheet. Du kan imidlertid bytte ActiveSheet innenfor utvalgte ark.
Velg et ark
| 1 | Ark ("Ark1"). Velg |
Velg flere ark
Bruk en matrise for å velge flere ark samtidig:
| 1 | Regneark (Array ("Sheet2", "Sheet3")). Velg |
Regnearkvariabel
Ved å tilordne et regneark til en variabel kan du referere til regnearket etter variabelnavnet. Dette kan spare mye skriving og gjøre koden lettere å lese. Det er også mange andre grunner til at du vil bruke variabler.
Slik erklærer du en regnearksvariabel:
| 1 | Dim ws som regneark |
Tilordne et regneark til en variabel:
| 1 | Sett ws = Sheets ("Sheet1") |
Nå kan du referere til regnearksvariabelen i koden din:
| 1 | ws.Aktivere |
Sløyfe gjennom alle ark i arbeidsbok
Regnearksvariabler er viktige når du vil gå gjennom alle regnearkene i en arbeidsbok. Den enkleste måten å gjøre dette på er:
| 12345 | Dim ws som regnearkFor hver uke i regnearkMsgBox ws.nameNeste ws |
Denne koden går gjennom alle regnearkene i arbeidsboken og viser hvert regnearknavn i en meldingsboks. Å gå gjennom alle arkene i en arbeidsbok er veldig nyttig når du låser / låser opp eller skjuler / skjuler flere regneark samtidig.
Arbeidsarkbeskyttelse
Arbeidsbokbeskyttelse
Arbeidsbokbeskyttelse låser arbeidsboken for strukturelle endringer som å legge til, slette, flytte eller skjule regneark.
Du kan slå på arbeidsbokbeskyttelse ved å bruke VBA:
| 1 | ActiveWorkbook.Protect Password: = "Passord" |
eller deaktiver arbeidsbokbeskyttelse:
| 1 | ActiveWorkbook.UnProtect Password: = "Passord" |
Merk: Du kan også beskytte / fjerne beskyttelsen uten passord ved å utelate passordargumentet:
| 1 | ActiveWorkbook.Protect |
Arbeidsarkbeskyttelse
Beskyttelse på regnearknivå forhindrer endringer i individuelle regneark.
Beskytt regneark
| 1 | Regneark ("Ark1"). Beskytt "Passord" |
Avbeskytt regnearket
| 1 | Regneark ("Ark1"). Fjern beskyttelsen av "Passord" |
Det er en rekke alternativer for beskyttelse av regneark (tillat formateringsendringer, tillat bruker å sette inn rader, etc.) Vi anbefaler å bruke makroopptakeren til å registrere ønskede innstillinger.
Vi diskuterer regnearkbeskyttelse mer detaljert her.
Regneark Synlig eiendom
Du vet kanskje allerede at regneark kan skjules:
Det er faktisk tre synlighetsinnstillinger for regnearket: Synlig, Skjult og Veldig skjult.Skjulte ark kan skjules av alle vanlige Excel -brukere - ved å høyreklikke i regnearkfanearealet (vist ovenfor). VeryHidden -ark kan bare skjules med VBA -kode eller fra VBA Editor. Bruk følgende kodeeksempler til å skjule / skjule regneark:
Skjul regnearket
| 1 | Regneark ("Ark1"). Synlig = xlSheetVisible |
Skjul regneark
| 1 | Regneark ("Ark1"). Synlig = xlSheetHidden |
Veldig skjul regneark
| 1 | Regneark ("Ark1"). Synlig = xlSheetVeryHidden |
Hendelser på arbeidsarknivå
Hendelser er utløsere som kan føre til at "hendelsesprosedyrer" kjøres. For eksempel kan du forårsake at kode kjøres hver gang en celle i et regneark endres eller når et regneark aktiveres.
Arbeidsarkhendelsesprosedyrer må plasseres i en regnearkmodul:
Det er mange regnearkhendelser. For å se en fullstendig liste, gå til en regnearkmodul, velg "Regneark" fra den første rullegardinlisten. Deretter velger du en hendelsesprosedyre fra den andre rullegardinmenyen for å sette den inn i modulen.
Regneark Aktiver hendelse
Regneark aktiverer hendelser som kjøres hver gang regnearket åpnes.
| 123 | Private Sub Worksheet_Activate ()Område ("A1"). VelgSlutt Sub |
Denne koden vil velge celle A1 (tilbakestille visningsområdet øverst til venstre i regnearket) hver gang regnearket åpnes.
Endring av regneark
Hendelsesendringshendelser kjøres når en celleverdi endres i regnearket. Les vår opplæring om endringer i regneark for mer informasjon.
Regneark Jukseark
Nedenfor finner du et jukseark som inneholder vanlige kodeeksempler for arbeid med ark i VBA
VBA -regneark Cheatsheet
VBA -regneark Cheatsheet| Beskrivelse | Kodeksempel |
|---|---|
| Referere og aktivere ark | |
| Fanenavn | Ark ("Inndata"). Aktiver |
| VBA -kodenavn | Ark 1.Aktiver |
| Indeksposisjon | Ark (1). Aktiver |
| Velg Ark | |
| Velg Ark | Ark ("Inndata"). Velg |
| Sett til variabel | Dim ws som regneark Sett ws = ActiveSheet |
| Navn / Gi nytt navn | ActiveSheet.Name = "Nytt navn" |
| Neste ark | ActiveSheet.Next.Activate |
| Sløyfe gjennom alle ark | Dim ws som regneark For hver uke i regneark Msgbox ws.name Neste ws |
| Sløyfe gjennom utvalgte ark | Dim ws Som regneark For hver gang i ActiveWindow.SelectedSheets MsgBox ws.Name Neste ws |
| Få ActiveSheet | MsgBox ActiveSheet.Name |
| Legg til ark | Ark. Legg til |
| Legg til ark og navn | Sheets.Add.Name = "NewSheet" |
| Legg til ark med navn fra celle | Sheets.Add.Name = range ("a3"). Verdi |
| Legg til ark etter det andre | Sheets.Add After: = Sheets ("Input") |
| Legg til ark etter og navn | Sheets.Add (After: = Sheets ("Input")). Name = "NewSheet" |
| Legg til ark før og navn | Sheets.Add (Before: = Sheets ("Input")). Name = "NewSheet" |
| Legg til ark i slutten av arbeidsboken | Legg til etter: = Ark (Sheets.Count) |
| Legg til ark i begynnelsen av arbeidsboken | Sheets.Add (Before: = Sheets (1)). Name = "FirstSheet" |
| Legg til ark i variabel | Dim ws Som regneark Sett ws = Sheets.Add |
| Kopier regneark | |
| Flytt arket til slutten av arbeidsboken | Ark ("Sheet1"). Flytt etter: = Sheets (Sheets.Count) |
| Til ny arbeidsbok | Ark ("Ark1"). Kopi |
| Utvalgte ark til ny arbeidsbok | ActiveWindow.SelectedSheets.Copy |
| Før et nytt ark | Ark ("Ark1"). Kopier før: = Ark ("Ark2") |
| Før første ark | Ark ("Ark1"). Kopier før: = Ark (1) |
| Etter siste ark | Ark ("Sheet1"). Kopier etter: = Sheets (Sheets.Count) |
| Kopi og navn | Ark ("Sheet1"). Kopier etter: = Sheets (Sheets.Count) ActiveSheet.Name = "LastSheet" |
| Kopier og navn fra celleverdi | Ark ("Sheet1"). Kopier etter: = Sheets (Sheets.Count) ActiveSheet.Name = Område ("A1"). Verdi |
| Til en annen arbeidsbok | Ark ("Ark1"). Kopier før: = Arbeidsbøker ("Eksempel.xlsm"). Ark (1) |
| Skjul / skjul ark | |
| Skjul ark | Ark ("Ark1"). Synlig = Feil eller Ark ("Ark1"). Synlig = xlSheetHidden |
| Skjul ark | Ark ("Ark1"). Synlig = sant eller Ark ("Ark1"). Synlig = xlSheetVisible |
| Very Hide Sheet | Ark ("Ark1"). Synlig = xlSheetVeryHidden |
| Slett eller fjern ark | |
| Slett ark | Ark ("Ark1"). Slett |
| Slett ark (feilhåndtering) | Ved feil Fortsett neste Ark ("Ark1"). Slett Ved feil GoTo 0 |
| Slett ark (ingen melding) | Application.DisplayAlerts = Falsk Ark ("Ark1"). Slett Application.DisplayAlerts = True |
| Klar ark | Ark ("Sheet1"). Cells.Clear |
| Bare innhold i klart ark | Ark ("Ark1"). Cells.ClearContents |
| Klar ark bruktRange | Ark ("Ark1"). UsedRange.Clear |
| Beskytt eller fjern beskyttelsen av ark | |
| Fjern beskyttelsen (intet passord) | Ark ("Ark1"). Beskytter ikke |
| Fjern beskyttelsen (passord) | Ark ("Ark1"). Fjern beskyttelsen av "passord" |
| Beskytt (ikke passord) | Ark ("Ark1"). Beskytt |
| Beskytt (passord) | Ark ("Ark1"). Beskytt "Passord" |
| Beskytt, men tillat VBA -tilgang | Ark ("Ark1"). Beskytt UserInterfaceOnly: = True |
| Fjern beskyttelsen av alle ark | Dim ws Som regneark For hver uke i regneark ws.Unbeskytt "passord" Neste ws |
