Dit is de ultieme gids voor het werken met Spreadsheets / Werkbladen in Excel.
Onderaan deze handleiding hebben we een spiekbriefje gemaakt met veelvoorkomende opdrachten voor het werken met werkbladen.
Bladen versus Werkbladen
Er zijn twee manieren om naar Spreadsheets te verwijzen met VBA. De eerste is met het Spreadsheets-object:
| 1 | Spreadsheets ("Blad1"). Activeren |
De andere is met het Worksheets-object:
| 1 | Werkbladen ("Blad1"). Activeren |
99% van de tijd zijn deze twee objecten identiek. Als u online naar voorbeelden van VBA-codes hebt gezocht, hebt u waarschijnlijk beide objecten gebruikt. Hier is het verschil:
De Spreadsheets-collectie bevat werkbladen EN grafiekbladen.
Gebruik dus Spreadsheets als u gewone werkbladen EN grafiekbladen wilt opnemen. Gebruik werkbladen als u grafiekbladen wilt uitsluiten. Voor de rest van deze handleiding zullen we bladen en werkbladen door elkaar gebruiken.
Verwijzende bladen
Er zijn verschillende manieren om naar Spreadsheets te verwijzen:
- ActiveSheet
- Naam bladtab
- Bladindexnummer
- Bladcodenaam:
ActiveSheet
De ActiveSheet is de Sheet die momenteel actief is. Met andere woorden, als u uw code hebt onderbroken en naar Excel hebt gekeken, is het blad zichtbaar. In het onderstaande codevoorbeeld wordt een Messagebox weergegeven met de ActiveSheet-naam.
| 1 | MsgBox ActiveSheet.Name |
Bladnaam:
U bent waarschijnlijk het meest bekend met het verwijzen naar Spreadsheets aan de hand van hun tabbladnaam:
| 1 | Spreadsheets ("Tabnaam"). Activeren |
Bladindexnummer
Het bladindexnummer is de bladpositie in de werkmap. 1 is het eerste blad. 2 is het tweede blad enz.:
| 1 | Bladen(1).Activeren |
Bladindexnummer - Laatste blad in werkmap
Als u naar het laatste blad in de werkmap wilt verwijzen, gebruikt u Spreadsheets.Count om het laatste indexnummer te krijgen:
| 1 | Vellen (Vellen. Tellen). Activeren |
Blad “Codenaam”
De bladcodenaam is de objectnaam in VBA:
| 1 | CodeName.Activeren |
Verwijzen naar werkbladen in andere werkmappen
Het is ook gemakkelijk om naar Spreadsheets in andere werkmappen te verwijzen. Om dit te doen, moet u het Workbooks-object gebruiken:
| 1 | Werkmappen ("VBA_Examples.xlsm"). Werkbladen ("Blad1"). Activeren |
Belangrijk: De werkmap moet geopend zijn voordat u naar de Spreadsheets kunt verwijzen.
Activeren versus blad selecteren
In een ander artikel bespreken we alles over het activeren en selecteren van bladen. De korte versie is deze:
Wanneer u een blad activeert, wordt het de ActiveSheet. Dit is het blad dat u zou zien als u naar uw Excel-programma zou kijken. Er kan slechts één blad tegelijk worden geactiveerd.
Activeer een blad
| 1 | Spreadsheets ("Blad1"). Activeren |
Wanneer u een werkblad selecteert, wordt dit ook het ActiveSheet. U kunt echter meerdere bladen tegelijk selecteren. Wanneer meerdere bladen tegelijk zijn geselecteerd, is het "bovenste" blad de ActiveSheet. U kunt de ActiveSheet echter binnen geselecteerde bladen wisselen.
Selecteer een blad
| 1 | Bladen ("Blad1"). Selecteer |
Selecteer meerdere bladen
Gebruik een array om meerdere bladen tegelijk te selecteren:
| 1 | Werkbladen (Array ("Blad2", "Blad3")).Selecteer |
Variabele werkblad
Als u een werkblad aan een variabele toewijst, kunt u naar het werkblad verwijzen met de variabelenaam. Dit kan veel typen besparen en uw code gemakkelijker leesbaar maken. Er zijn ook veel andere redenen waarom u variabelen zou willen gebruiken.
Een werkbladvariabele declareren:
| 1 | Dim ws als werkblad |
Wijs een werkblad toe aan een variabele:
| 1 | Stel ws = Bladen ("Blad1") in |
Nu kunt u verwijzen naar de werkbladvariabele in uw code:
| 1 | ws.Activeren |
Doorloop alle bladen in werkmap
Werkbladvariabelen zijn essentieel wanneer u door alle werkbladen in een werkmap wilt bladeren. De eenvoudigste manier om dit te doen is:
| 12345 | Dim ws als werkbladVoor elke ws in werkbladenMsgBox ws.naamVolgende ws |
Deze code loopt door alle werkbladen in de werkmap en geeft elke werkbladnaam weer in een berichtvenster. Door alle bladen in een werkmap bladeren is erg handig bij het vergrendelen / ontgrendelen of verbergen / zichtbaar maken van meerdere werkbladen tegelijk.
Werkbladbescherming
Werkmapbeveiliging
Werkmapbeveiliging vergrendelt de werkmap tegen structurele wijzigingen, zoals het toevoegen, verwijderen, verplaatsen of verbergen van werkbladen.
U kunt werkmapbeveiliging inschakelen met VBA:
| 1 | ActiveWorkbook.Bescherm wachtwoord:="Wachtwoord" |
of werkmapbeveiliging uitschakelen:
| 1 | ActiveWorkbook.UnProtect Password:="Wachtwoord" |
Opmerking: u kunt de beveiliging ook zonder wachtwoord beveiligen of opheffen door het argument Wachtwoord weg te laten:
| 1 | ActiveWorkbook.Protect |
Werkbladbescherming
Beveiliging op werkbladniveau voorkomt wijzigingen in afzonderlijke werkbladen.
Werkblad beveiligen
| 1 | Werkbladen ("Blad1"). Bescherm "Wachtwoord" |
Werkblad beveiligen opheffen
| 1 | Worksheets ("Blad1"). Beveiliging "Wachtwoord" opheffen |
Er zijn verschillende opties bij het beschermen van werkbladen (sta opmaakwijzigingen toe, sta de gebruiker toe om rijen in te voegen, enz.) We raden aan om de Macro Recorder te gebruiken om de gewenste instellingen vast te leggen.
We bespreken werkbladbeveiliging hier in meer detail.
Werkblad Zichtbare eigenschap
U weet misschien al dat werkbladen verborgen kunnen worden:
Er zijn eigenlijk drie instellingen voor de zichtbaarheid van het werkblad: Zichtbaar, Verborgen en Zeer Verborgen.Verborgen bladen kunnen zichtbaar worden gemaakt door elke gewone Excel-gebruiker - door met de rechtermuisknop te klikken in het werkbladtabgebied (hierboven weergegeven). VeryHidden-bladen kunnen alleen zichtbaar worden gemaakt met VBA-code of vanuit de VBA-editor. Gebruik de volgende codevoorbeelden om werkbladen te verbergen / zichtbaar te maken:
Werkblad zichtbaar maken
| 1 | Werkbladen ("Blad1"). Zichtbaar = xlBlad Zichtbaar |
Werkblad verbergen
| 1 | Werkbladen ("Blad1").visible = xlSheetHidden |
Werkblad zeer verbergen
| 1 | Werkbladen ("Blad1"). Zichtbaar = xlBladVeryHidden |
Evenementen op werkbladniveau
Gebeurtenissen zijn triggers die ervoor kunnen zorgen dat "Gebeurtenisprocedures" worden uitgevoerd. U kunt bijvoorbeeld code laten uitvoeren telkens wanneer een cel op een werkblad wordt gewijzigd of wanneer een werkblad wordt geactiveerd.
Procedures voor werkbladgebeurtenissen moeten in een werkbladmodule worden geplaatst:
Er zijn tal van werkbladevenementen. Om een volledige lijst te zien, gaat u naar een werkbladmodule, selecteert u "Werkblad" in de eerste vervolgkeuzelijst. Selecteer vervolgens een gebeurtenisprocedure in de tweede vervolgkeuzelijst om deze in de module in te voegen.
Werkblad Evenement activeren
Werkbladactiveringsgebeurtenissen worden uitgevoerd telkens wanneer het werkblad wordt geopend.
| 123 | Privé subwerkblad_Activate()Bereik ("A1"). SelecteerEinde sub |
Deze code selecteert cel A1 (het weergavegebied wordt opnieuw ingesteld naar de linkerbovenhoek van het werkblad) telkens wanneer het werkblad wordt geopend.
Werkblad Wijzigingsgebeurtenis
Werkbladwijzigingsgebeurtenissen worden uitgevoerd wanneer een celwaarde op het werkblad wordt gewijzigd. Lees onze tutorial over Worksheet Change Events voor meer informatie.
Werkblad Cheatsheet
Hieronder vindt u een spiekbriefje met veelvoorkomende codevoorbeelden voor het werken met bladen in VBA
VBA-werkbladen Cheatsheet
VBA-werkbladen Cheatsheet| Beschrijving | Codevoorbeeld |
|---|---|
| Bladen raadplegen en activeren | |
| Tabbladnaam | Spreadsheets ("Invoer"). Activeren |
| VBA-codenaam | Blad1.Activeren |
| Indexpositie | Bladen(1).Activeren |
| Selecteer blad | |
| Selecteer blad | Bladen ("Invoer"). Selecteer |
| Instellen op Variabel | Dim ws als werkblad Stel ws = ActiveSheet in |
| Naam / naam wijzigen | ActiveSheet.Name = "NieuweNaam" |
| Volgende blad | ActiveSheet.Volgende.Activeren |
| Doorloop alle bladen | Dim ws als werkblad Voor elke ws in werkbladen Msgbox ws.naam Volgende ws |
| Doorloop geselecteerde bladen | Dim ws als werkblad Voor elke ws in ActiveWindow.SelectedSheets MsgBox ws.Naam Volgende ws |
| ActiveSheet ophalen | MsgBox ActiveSheet.Name |
| Blad toevoegen | Lakens.Toevoegen |
| Blad en naam toevoegen | Spreadsheets.Add.Name = "NieuweBlad" |
| Blad toevoegen met naam uit cel | Spreadsheets.Toevoegen.Naam = bereik ("a3").waarde |
| Blad na elkaar toevoegen | Spreadsheets.Toevoegen na:=Sheets("Input") |
| Blad toevoegen na en naam | Sheets.Add(After:=Sheets("Input")).Name = "NewSheet" |
| Blad toevoegen voor en naam | Sheets.Add(Before:=Sheets("Input")).Name = "NewSheet" |
| Blad toevoegen aan einde van werkmap | Sheets.Add After:=Sheets(Sheets.Count) |
| Blad toevoegen aan begin van werkmap | Sheets.Add(Before:=Sheets(1)).Name = "FirstSheet" |
| Blad toevoegen aan variabele | Dim ws als werkblad Stel ws = Bladen.Toevoegen |
| Werkbladen kopiëren | |
| Blad verplaatsen naar einde van werkmap | Bladen ("Blad1"). Verplaatsen na: = Bladen (Vellen. Aantal) |
| Naar nieuwe werkmap | Bladen ("Blad1"). Kopiëren |
| Geselecteerde bladen naar nieuwe werkmap | ActiveWindow.SelectedSheets.Copy |
| Voor een ander blad | Spreadsheets ("Blad1"). Kopieer eerder:=Sheets ("Blad2") |
| Voor het eerste blad | Bladen ("Blad1"). Kopieer voor:= Bladen(1) |
| Na laatste blad | Bladen ("Blad1"). Kopiëren na: = Bladen (Vellen. Aantal) |
| Kopieer en naam | Bladen ("Blad1"). Kopiëren na: = Bladen (Vellen. Aantal) ActiveSheet.Name = "LastSheet" |
| Kopieer en benoem van celwaarde | Bladen ("Blad1"). Kopiëren na: = Bladen (Vellen. Aantal) ActiveSheet.Name = Bereik ("A1"). Waarde |
| Naar een ander werkboek | Spreadsheets ("Blad1"). Kopieer voor:=Werkboeken ("Voorbeeld.xlsm"). Bladen(1) |
| Bladen verbergen / zichtbaar maken | |
| Blad verbergen | Spreadsheets ("Blad1").zichtbaar = False of Spreadsheets ("Blad1").visible = xlSheetHidden |
| Blad zichtbaar maken | Spreadsheets ("Blad1"). Zichtbaar = True of Bladen ("Blad1"). Zichtbaar = xl Blad Zichtbaar |
| Verberg blad | Bladen ("Blad1"). Zichtbaar = xlSheetVeryHidden |
| Bladen verwijderen of wissen | |
| Blad verwijderen | Bladen ("Blad1").Verwijderen |
| Blad verwijderen (Foutafhandeling) | Bij fout Hervatten volgende Bladen ("Blad1").Verwijderen Bij fout Ga naar 0 |
| Blad verwijderen (geen prompt) | Application.DisplayAlerts = False Bladen ("Blad1").Verwijderen Application.DisplayAlerts = True |
| Blad wissen | Bladen ("Blad1"). Cellen. Wissen |
| Alleen bladinhoud wissen | Bladen ("Blad1"). Cellen.ClearContents |
| Clear Sheet GebruiktBereik | Bladen ("Blad1").UsedRange.Clear |
| Bladen beveiligen of de beveiliging opheffen | |
| Beveiliging opheffen (geen wachtwoord) | Spreadsheets ("Blad1").Bescherming opheffen |
| Beveiliging opheffen (wachtwoord) | Spreadsheets ("Blad1"). Beveiliging "Wachtwoord" opheffen |
| Bescherm (geen wachtwoord) | Bladen ("Blad1").Beschermen |
| Bescherm (wachtwoord) | Spreadsheets ("Blad1"). Bescherm "Wachtwoord" |
| Bescherm maar sta VBA-toegang toe | Sheets("Blad1").Bescherm UserInterfaceOnly:=True |
| Beveiliging van alle bladen opheffen | Dim ws als werkblad Voor elke ws In werkbladen ws.Unprotect "wachtwoord" Volgende ws |
