Dette er den ultimative guide til at arbejde med ark / regneark i Excel.
Nederst i denne vejledning har vi oprettet et snydeblad med almindelige kommandoer til arbejde med ark.
Ark vs. Arbejdsark
Der er to måder at referere til ark ved hjælp af VBA. Den første er med Sheets -objektet:
| 1 | Ark ("Ark1"). Aktiver |
Den anden er med regnearksobjektet:
| 1 | Regneark ("Ark1"). Aktiver |
99% af tiden er disse to objekter identiske. Faktisk, hvis du har søgt online efter eksempler på VBA -kode, har du sandsynligvis set begge objekter brugt. Her er forskellen:
Arksamlingen indeholder regneark OG diagramark.
Så brug Ark, hvis du vil medtage almindelige regneark OG diagramark. Brug regneark, hvis du vil ekskludere diagramark. I resten af denne vejledning vil vi bruge ark og regneark i flæng.
Referenceark
Der er flere forskellige måder at referere til ark på:
- ActiveSheet
- Fanebladets navn
- Bladindeksnummer
- Bladkodens navn
ActiveSheet
ActiveSheet er det ark, der aktuelt er aktivt. Med andre ord, hvis du satte din kode på pause og kiggede på Excel, er det arket, der er synligt. Nedenstående kodeeksempel viser en meddelelsesboks med ActiveSheet -navnet.
| 1 | MsgBox ActiveSheet.Name |
Arknavn
Du er sandsynligvis mest bekendt med at henvise til ark efter deres fanenavn:
| 1 | Ark ("Tabulatornavn"). Aktiver |
Bladindeksnummer
Bladindeksnummeret er arkplaceringen i projektmappen. 1 er det første ark. 2 er det andet ark osv .:
| 1 | Ark (1) .Aktiver |
Arkindeksnummer - Sidste ark i projektmappe
For at henvise til det sidste ark i projektmappen skal du bruge Sheets.Count for at få det sidste indeksnummer:
| 1 | Ark (Sheets.Count) .Aktiver |
Ark "Kodenavn"
Arkkodens navn er dets objektnavn i VBA:
| 1 | Kodenavn.Aktiver |
Referenceark i andre arbejdsbøger
Det er også let at referere til ark i andre projektmapper. For at gøre det skal du bruge Workbooks Object:
| 1 | Arbejdsbøger ("VBA_Examples.xlsm"). Regneark ("Ark1"). Aktiver |
Vigtig: Arbejdsbogen skal være åben, før du kan henvise til dens ark.
Aktiver vs. Vælg ark
I en anden artikel diskuterer vi alt om aktivering og valg af ark. Den korte version er denne:
Når du aktiverer et ark, bliver det til ActiveSheet. Dette er det ark, du ville se, hvis du kiggede på dit Excel -program. Kun et ark kan aktiveres ad gangen.
Aktiver et ark
| 1 | Ark ("Ark1"). Aktiver |
Når du vælger et ark, bliver det også til ActiveSheet. Du kan dog vælge flere ark på én gang. Når der vælges flere ark på én gang, er det "øverste" ark ActiveSheet. Du kan dog skifte ActiveSheet inden for udvalgte ark.
Vælg et ark
| 1 | Ark ("Ark1"). Vælg |
Vælg flere ark
Brug en matrix til at vælge flere ark på én gang:
| 1 | Regneark (Array ("Sheet2", "Sheet3")). Vælg |
Regneark Variabel
Tildel et regneark til en variabel, så du kan referere til regnearket efter dets variabelnavn. Dette kan spare mange indtastninger og gøre din kode lettere at læse. Der er også mange andre grunde til, at du måske vil bruge variabler.
Sådan erklærer du en regnearksvariabel:
| 1 | Dim ws som regneark |
Tildel et regneark til en variabel:
| 1 | Indstil ws = Sheets ("Sheet1") |
Nu kan du referere til regnearksvariablen i din kode:
| 1 | ws.Aktiver |
Sløjfe gennem alle ark i projektmappe
Regnearksvariabler er afgørende, når du vil gå igennem alle regnearkene i en projektmappe. Den nemmeste måde at gøre dette på er:
| 12345 | Dim ws som regnearkFor hver gang i regnearkMsgBox ws.nameNæste ws |
Denne kode går gennem alle regneark i projektmappen og viser hvert regnearks navn i en meddelelsesboks. Looping gennem alle arkene i en projektmappe er meget nyttig, når du låser / låser op eller skjuler / skjuler flere regneark på én gang.
Beskyttelse af regneark
Beskyttelse af projektmappe
Arbejdsbogbeskyttelse låser projektmappen for strukturelle ændringer som tilføjelse, sletning, flytning eller skjulning af regneark.
Du kan aktivere projektmappebeskyttelse ved hjælp af VBA:
| 1 | ActiveWorkbook.Protect Password: = "Password" |
eller deaktiver beskyttelse af projektmapper:
| 1 | ActiveWorkbook.UnProtect Password: = "Password" |
Bemærk: Du kan også beskytte / fjerne beskyttelse uden adgangskode ved at udelade password -argumentet:
| 1 | ActiveWorkbook.Protect |
Beskyttelse af regneark
Beskyttelse på regnearksniveau forhindrer ændringer i individuelle regneark.
Beskyt regneark
| 1 | Regneark ("Ark1"). Beskyt "Adgangskode" |
Afbeskyt regneark
| 1 | Regneark ("Sheet1"). Fjern beskyttelsen af "Password" |
Der er en række muligheder for beskyttelse af regneark (tillad formateringsændringer, tillad brugeren at indsætte rækker osv.) Vi anbefaler at bruge makrooptageren til at registrere de ønskede indstillinger.
Vi diskuterer regnearksbeskyttelse mere detaljeret her.
Regneark Synlig ejendom
Du ved måske allerede, at regneark kan skjules:
Der er faktisk tre indstillinger for regnearks synlighed: Synlig, Skjult og Meget skjult.Skjulte ark kan skjules af enhver almindelig Excel -bruger - ved at højreklikke i fanebladet på regnearket (vist ovenfor). VeryHidden -ark kan kun fjernes med VBA -kode eller fra VBA -editoren. Brug følgende kodeeksempler til at skjule / afsløre regneark:
Vis regneark
| 1 | Regneark ("Ark1"). Synlig = xlSheetVisible |
Skjul regneark
| 1 | Regneark ("Ark1"). Synlig = xlSheetHidden |
Meget skjul regneark
| 1 | Regneark ("Ark1"). Synlig = xlSheetVeryHidden |
Begivenheder på arbejdsark-niveau
Begivenheder er udløsere, der kan få "Hændelsesprocedurer" til at køre. For eksempel kan du få koden til at køre hver gang en celle i et regneark ændres, eller når et regneark aktiveres.
Hændelsesprocedurer for regneark skal placeres i et regnearksmodul:
Der er mange regnearkbegivenheder. For at se en komplet liste skal du gå til et regnearksmodul, vælge "Regneark" fra den første rulleliste. Vælg derefter en hændelsesprocedure fra den anden rulleliste for at indsætte den i modulet.
Regneark Aktiver begivenhed
Regneark aktiverer begivenheder, der køres hver gang regnearket åbnes.
| 123 | Privat under regneark_Aktiver ()Område ("A1"). VælgAfslut Sub |
Denne kode vælger celle A1 (nulstiller visningsområdet øverst til venstre i regnearket) hver gang regnearket åbnes.
Hændelse til ændring af regneark
Regnskabsændringshændelser kører, når en celleværdi ændres i regnearket. Læs vores vejledning om regnearksændringshændelser for mere information.
Regneark Snydeblad
Nedenfor finder du et snydeblad med almindelige kodeeksempler til arbejde med ark i VBA
VBA -regneark Cheatsheet
VBA -regneark Cheatsheet| Beskrivelse | Kodeeksempel |
|---|---|
| Henvisning og aktivering af ark | |
| Fanenavn | Ark ("Input"). Aktiver |
| VBA -kodenavn | Ark 1.Aktiver |
| Indeksposition | Ark (1) .Aktiver |
| Vælg Ark | |
| Vælg Ark | Ark ("Input"). Vælg |
| Indstil til variabel | Dim ws som regneark Indstil ws = ActiveSheet |
| Navn / Omdøb | ActiveSheet.Name = "Nyt navn" |
| Næste ark | ActiveSheet.Next.Activate |
| Sløjfe igennem alle ark | Dim ws som regneark For hver uge i regneark Msgbox ws.name Næste ws |
| Sløjfe gennem udvalgte ark | Dim ws Som regneark For hver gang i ActiveWindow.SelectedSheets MsgBox ws.Name Næste ws |
| Hent ActiveSheet | MsgBox ActiveSheet.Name |
| Tilføj ark | Ark. Tilføj |
| Tilføj ark og navn | Sheets.Add.Name = "NewSheet" |
| Tilføj ark med navn fra celle | Sheets.Add.Name = område ("a3"). Værdi |
| Tilføj ark efter det andet | Ark.Add efter: = Ark ("Input") |
| Tilføj ark efter og navn | Sheets.Add (After: = Sheets ("Input")). Name = "NewSheet" |
| Tilføj ark før og navn | Sheets.Add (Before: = Sheets ("Input")). Name = "NewSheet" |
| Tilføj ark til slut på projektmappe | Ark.Add efter: = Ark (Sheets.Count) |
| Tilføj ark til begyndelsen af projektmappen | Sheets.Add (Before: = Sheets (1)). Name = "FirstSheet" |
| Tilføj ark til variabel | Dim ws Som regneark Indstil ws = Sheets.Add |
| Kopiér regneark | |
| Flyt ark til slut på projektmappe | Ark ("Sheet1"). Flyt efter: = Sheets (Sheets.Count) |
| Til ny arbejdsbog | Ark ("Ark1"). Kopi |
| Udvalgte ark til ny projektmappe | ActiveWindow.SelectedSheets.Copy |
| Før endnu et ark | Ark ("Ark1"). Kopi før: = Ark ("Ark2") |
| Før første ark | Ark ("Ark1"). Kopi før: = Ark (1) |
| Efter sidste ark | Ark ("Sheet1"). Kopiér efter: = Sheets (Sheets.Count) |
| Kopi og navn | Ark ("Sheet1"). Kopiér efter: = Sheets (Sheets.Count) ActiveSheet.Name = "LastSheet" |
| Kopi og navn fra celleværdi | Ark ("Sheet1"). Kopiér efter: = Sheets (Sheets.Count) ActiveSheet.Name = Område ("A1"). Værdi |
| Til en anden arbejdsbog | Ark ("Ark1"). Kopi før: = projektmapper ("eksempel.xlsm"). Ark (1) |
| Skjul / vis ark | |
| Skjul ark | Ark ("Ark1"). Synlig = Falsk eller Ark ("Ark1"). Synlig = xlSheetHidden |
| Skjul ark | Ark ("Ark1"). Synligt = sandt eller Ark ("Ark1"). Synlig = xlSheetVisible |
| Meget skjul ark | Ark ("Ark1"). Synlig = xlSheetVeryHidden |
| Slet eller slet ark | |
| Slet ark | Ark ("Ark1"). Slet |
| Slet ark (fejlhåndtering) | Ved fejl Genoptag næste Ark ("Ark1"). Slet Ved fejl GoTo 0 |
| Slet ark (ingen prompt) | Application.DisplayAlerts = Falsk Ark ("Ark1"). Slet Application.DisplayAlerts = True |
| Klar ark | Ark ("Ark1"). Cellerne er klare |
| Klar indhold kun | Ark ("Ark1"). Cells.ClearContents |
| Clear Sheet UsedRange | Ark ("Ark1"). UsedRange.Clear |
| Beskyt eller fjern beskyttelse af ark | |
| Fjern beskyttelse (ingen adgangskode) | Ark ("Ark1"). Beskyttet |
| Fjern beskyttelse (adgangskode) | Ark ("Ark1"). Fjern beskyttelsen af "Adgangskode" |
| Beskyt (ingen adgangskode) | Ark ("Ark1"). Beskyt |
| Beskyt (adgangskode) | Ark ("Ark1"). Beskyt "Adgangskode" |
| Beskyt, men tillad VBA -adgang | Ark ("Sheet1"). Beskyt UserInterfaceOnly: = True |
| Fjern alle ark | Dim ws Som regneark For hver gang i regneark ws.Beskyt "adgangskode" Næste ws |
