✅ Werken met werkbladen met Excel VBA (uitgelegd met voorbeelden)

Inhoudsopgave

Verschil tussen werkbladen en werkbladen in VBA
Verwijzen naar een werkblad in VBA
Een werkblad toevoegen
Een werkblad verwijderen
De werkbladen hernoemen
Werkbladobject toewijzen aan een variabele
Verberg werkbladen met VBA (verborgen + zeer verborgen)
Bladen verbergen op basis van de tekst erin
De werkbladen in alfabetische volgorde sorteren
Alle bladen in één keer beveiligen/de beveiliging opheffen
Een inhoudsopgave maken van alle werkbladen (met hyperlinks)
Waar de VBA-code te plaatsen

Afgezien van cellen en bereiken, is het werken met werkbladen een ander gebied dat u moet kennen om VBA efficiënt in Excel te gebruiken.

Net als elk object in VBA, hebben werkbladen verschillende eigenschappen en bijbehorende methoden die u kunt gebruiken terwijl u uw werk met VBA in Excel automatiseert.

In deze tutorial behandel ik 'Werkbladen' in detail en laat ik je ook enkele praktische voorbeelden zien.

Dus laten we beginnen.

Alle codes die ik in deze tutorial noem, moeten in de VB Editor worden geplaatst. Ga naar het gedeelte 'Waar de VBA-code plaatsen' om te weten hoe het werkt.

Als je geïnteresseerd bent om VBA op een gemakkelijke manier te leren, bekijk dan mijn Online Excel VBA-training.

Verschil tussen werkbladen en werkbladen in VBA

In VBA heb je twee verzamelingen die soms een beetje verwarrend kunnen zijn.

In een werkmap kunt u zowel werkbladen als grafiekbladen hebben. Het onderstaande voorbeeld heeft drie werkbladen en één grafiekblad.

In Excel-VBA:

De verzameling 'Werkbladen' zou verwijzen naar de verzameling van alle werkbladobjecten in een werkmap. In het bovenstaande voorbeeld zou de verzameling werkbladen uit drie werkbladen bestaan.
De collectie 'Sheets' zou verwijzen naar alle werkbladen en grafiekbladen in de werkmap. In het bovenstaande voorbeeld zou het vier elementen hebben - 3 werkbladen + 1 grafiekblad.

Als u een werkmap heeft die alleen werkbladen heeft en geen grafiekbladen, dan is de verzameling 'Werkbladen' en 'Bellen' hetzelfde.

Maar als u een of meer grafiekbladen heeft, is de verzameling 'Blad's' groter dan de verzameling 'Werkbladen'

Bladen = Werkbladen + Grafiekbladen

Met dit onderscheid raad ik aan om zo specifiek mogelijk te zijn bij het schrijven van een VBA-code.

Dus als je alleen naar werkbladen moet verwijzen, gebruik dan de verzameling 'Werkbladen', en als je naar alle bladen moet verwijzen (inclusief kaartbladen), gebruik dan de verzameling 'Vellen'.

In deze zelfstudie gebruik ik alleen de verzameling 'Werkbladen'.

Verwijzen naar een werkblad in VBA

Er zijn veel verschillende manieren waarop u in VBA naar een werkblad kunt verwijzen.

Als u begrijpt hoe u naar werkbladen moet verwijzen, kunt u betere code schrijven, vooral wanneer u lussen in uw VBA-code gebruikt.

De werkbladnaam gebruiken

De eenvoudigste manier om naar een werkblad te verwijzen, is door de naam ervan te gebruiken.

Stel dat u een werkmap hebt met drie werkbladen - Blad 1, Blad 2, Blad 3.

En je wilt Blad 2 activeren.

U kunt dat doen met behulp van de volgende code: Sub ActivateSheet() Worksheets("Sheet2").Activate End Sub

De bovenstaande code vraagt VBA om naar Blad2 in de verzameling werkbladen te verwijzen en het te activeren.

Omdat we de exacte bladnaam gebruiken, kunt u hier ook de collectie Sheets gebruiken. Dus de onderstaande code zou hetzelfde doen.

Sub Activeer Blad() Bladen ("Blad2"). Activeer Einde Sub

Het indexnummer gebruiken

Hoewel het gebruik van de bladnaam een gemakkelijke manier is om naar een werkblad te verwijzen, weet u soms niet de exacte naam van het werkblad.

Als u bijvoorbeeld een VBA-code gebruikt om een nieuw werkblad aan de werkmap toe te voegen en u weet niet hoeveel werkbladen er al zijn, zou u de naam van het nieuwe werkblad niet weten.

In dit geval kunt u het indexnummer van de werkbladen gebruiken.

Stel dat u de volgende bladen in een werkmap hebt:

De onderstaande code zou Sheet2 activeren:

Sub-ActiveerSheet()-werkbladen(2).Activeer End Sub

Merk op dat we index nummer 2 hebben gebruikt in Werkbladen(2). Dit zou verwijzen naar het tweede object in de verzameling van de werkbladen.

Wat gebeurt er als u 3 als indexnummer gebruikt?

Het zal Blad3 selecteren.

Als je je afvraagt waarom het Sheet3 heeft geselecteerd, want het is duidelijk het vierde object.

Dit gebeurt omdat een grafiekblad geen deel uitmaakt van de verzameling werkbladen.

Dus wanneer we de indexnummers in de verzameling werkbladen gebruiken, verwijst deze alleen naar de werkbladen in de werkmap (en negeert de grafiekbladen).

Integendeel, als u Spreadsheets gebruikt, verwijst Spreadsheets(1) naar Spreadsheets1, Spreadsheets(2) verwijst naar Sheet2, Spreadsheets(3) verwijst naar Chart1 en Spreadsheets(4) verwijst naar Sheet3.

Deze techniek van het gebruik van indexnummers is handig wanneer u alle werkbladen in een werkmap wilt doorlopen. U kunt het aantal werkbladen tellen en deze vervolgens doorlopen met behulp van deze telling (we zullen later in deze zelfstudie zien hoe u dit kunt doen).

Opmerking: Het indexnummer gaat van links naar rechts. Dus als u Blad2 naar links van Blad1 schuift, dan zou Werkbladen(1) verwijzen naar Blad2.

De codenaam van het werkblad gebruiken

Een van de nadelen van het gebruik van de bladnaam (zoals we in het bovenstaande gedeelte hebben gezien) is dat een gebruiker deze kan wijzigen.

En als de bladnaam is gewijzigd, werkt uw code pas als u de naam van het werkblad ook in de VBA-code wijzigt.

Om dit probleem aan te pakken, kun je de codenaam van het werkblad gebruiken (in plaats van de reguliere naam die we tot nu toe hebben gebruikt). Een codenaam kan worden toegewezen in de VB Editor en verandert niet wanneer u de naam van het werkblad wijzigt vanuit het werkbladgebied.

Volg de onderstaande stappen om uw werkblad een codenaam te geven:

Klik op het tabblad Ontwikkelaars.
Klik op de Visual Basic-knop. Hiermee wordt de VB-editor geopend.
Klik op de optie Bekijken in het menu en klik op Projectvenster. Hierdoor wordt het deelvenster Eigenschappen zichtbaar. Als het deelvenster Eigenschappen al zichtbaar is, slaat u deze stap over.
Klik op de bladnaam in de projectverkenner die u wilt hernoemen.
Wijzig in het deelvenster Eigenschappen de naam in het veld voor (Naam). Houd er rekening mee dat u geen spaties in de naam kunt hebben.

De bovenstaande stappen zouden de naam van uw werkblad in de VBA-backend veranderen. In de Excel-werkbladweergave kunt u het werkblad een naam geven die u maar wilt, maar in de backend reageert het op beide namen - de bladnaam en de codenaam.

In de bovenstaande afbeelding is de bladnaam 'Bladnaam' en de codenaam is 'CodeNaam'. Zelfs als u de bladnaam op het werkblad wijzigt, blijft de codenaam hetzelfde.

Nu kunt u de verzameling Worksheets gebruiken om naar het werkblad te verwijzen of de codenaam gebruiken.

Beide lijnen activeren bijvoorbeeld het werkblad.

Werkbladen ("Bladnaam"). Codenaam activeren. Activeren

Het verschil tussen deze twee is dat als u de naam van het werkblad wijzigt, de eerste niet zou werken. Maar de tweede regel zou blijven werken, zelfs met de gewijzigde naam. De tweede regel (met behulp van de CodeName) is ook korter en gemakkelijker te gebruiken.

Verwijzen naar een werkblad in een ander werkboek

Als u naar een werkblad in een andere werkmap wilt verwijzen, moet die werkmap geopend zijn terwijl de code wordt uitgevoerd en moet u de naam van de werkmap en het werkblad waarnaar u wilt verwijzen opgeven.

Als je bijvoorbeeld een werkmap hebt met de naam Voorbeelden en je wilt Blad1 activeren in de Voorbeeldwerkmap, dan moet je onderstaande code gebruiken:

Sub SheetActivate() Workbooks("Examples.xlsx").Worksheets("Sheet1").Activate End Sub

Merk op dat als de werkmap is opgeslagen, u de bestandsnaam samen met de extensie moet gebruiken. Als u niet zeker weet welke naam u moet gebruiken, kunt u hulp krijgen van Project Explorer.

Als de werkmap niet is opgeslagen, hoeft u de bestandsextensie niet te gebruiken.

Een werkblad toevoegen

De onderstaande code zou een werkblad toevoegen (als het eerste werkblad - d.w.z. als het meest linkse blad op de bladtab).

Sub AddSheet() Worksheets.Add End Sub

Het heeft de standaardnaam Blad2 (of een ander nummer op basis van het aantal bladen dat er al is).

Als u wilt dat een werkblad vóór een specifiek werkblad wordt toegevoegd (bijvoorbeeld Sheet2), kunt u de onderstaande code gebruiken.

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

De bovenstaande code vertelt VBA om een blad toe te voegen en gebruikt vervolgens de 'Before'-instructie om het werkblad op te geven waarvoor het nieuwe werkblad moet worden ingevoegd.

Op dezelfde manier kunt u ook een blad toevoegen na een werkblad (zeg Sheet2), met behulp van de onderstaande code:

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

Als u wilt dat het nieuwe blad aan het einde van de bladen wordt toegevoegd, moet u eerst weten hoeveel bladen er zijn. De volgende code telt eerst het aantal bladen en voegt het nieuwe blad toe na het laatste blad (waarnaar we verwijzen met behulp van het indexnummer).

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

Een werkblad verwijderen

De onderstaande code zou het actieve blad uit de werkmap verwijderen.

Sub DeleteSheet() ActiveSheet.Delete End Sub

De bovenstaande code zou een waarschuwingsprompt weergeven voordat het werkblad wordt verwijderd.

Als u de waarschuwingsprompt niet wilt zien, gebruikt u de onderstaande code:

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

Wanneer Application.DisplayAlerts is ingesteld op False, wordt de waarschuwingsprompt niet weergegeven. Als u het gebruikt, vergeet dan niet om het aan het einde van de code weer in te stellen op True.

Onthoud dat u deze verwijdering niet ongedaan kunt maken, dus gebruik de bovenstaande code als u er absoluut zeker van bent.

Als u een specifiek blad wilt verwijderen, kunt u dat doen met de volgende code:

Sub DeleteSheet() Worksheets("Blad2").Delete End Sub

U kunt ook de codenaam van het blad gebruiken om het te verwijderen.

Sub DeleteSheet() Sheet5.Delete End Sub

De werkbladen hernoemen

U kunt de eigenschap name van het werkblad wijzigen om de naam te wijzigen.

De volgende code verandert de naam van Blad1 in 'Samenvatting'.

Sub RenameSheet() Worksheets("Blad1").Name = "Samenvatting" End Sub

U kunt dit combineren met de methode bladen toevoegen om een set bladen met specifieke namen te krijgen.

Als u bijvoorbeeld vier vellen wilt invoegen met de naam2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 en2021-2022 Q4, kunt u de onderstaande code gebruiken.

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 Einde sub

In de bovenstaande code tellen we eerst het aantal vellen en gebruiken we vervolgens een For Next-lus om aan het einde nieuwe vellen in te voegen. Als het blad wordt toegevoegd, hernoemt de code het ook.

Werkbladobject toewijzen aan een variabele

Wanneer u met werkbladen werkt, kunt u een werkblad toewijzen aan een objectvariabele en vervolgens de variabele gebruiken in plaats van de werkbladverwijzingen.

Als u bijvoorbeeld een jaarprefix aan alle werkbladen wilt toevoegen, in plaats van de vellen te tellen en de lus zo vaak te laten lopen, kunt u de objectvariabele gebruiken.

Hier is de code die 2021-2022 als voorvoegsel aan alle namen van het werkblad zal toevoegen.

Sub RenameSheet() Dim Ws als werkblad voor elke Ws in werkbladen Ws.Name = "2018 - " & Ws.Name Volgende Ws End Sub

De bovenstaande code declareert een variabele Ws als het werkbladtype (met behulp van de regel 'Dim Ws As Worksheet').

Nu hoeven we het aantal vellen niet te tellen om hier doorheen te bladeren. In plaats daarvan kunnen we de lus 'Voor elke Ws in werkbladen' gebruiken. Hiermee kunnen we alle bladen in de verzameling werkbladen doornemen. Het maakt niet uit of er 2 bladen of 20 bladen zijn.

Hoewel we met de bovenstaande code door alle bladen kunnen lopen, kunt u ook een specifiek blad aan een variabele toewijzen.

In de onderstaande code wijzen we de variabele Ws toe aan Sheet2 en gebruiken deze om toegang te krijgen tot alle eigenschappen van Sheet2.

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

Nadat u een werkbladverwijzing naar een objectvariabele hebt ingesteld (met behulp van de SET-instructie), kan dat object worden gebruikt in plaats van de werkbladverwijzing. Dit kan handig zijn als u een lange ingewikkelde code heeft en u de referentie wilt wijzigen. In plaats van overal de wijziging aan te brengen, kunt u de wijziging eenvoudig aanbrengen in de SET-instructie.

Merk op dat de code het Ws-object declareert als de variabele werkbladtype (met behulp van de regel Dim Ws als werkblad).

Verberg werkbladen met VBA (verborgen + zeer verborgen)

Het verbergen en zichtbaar maken van werkbladen in Excel is een eenvoudige taak.

U kunt een werkblad verbergen en de gebruiker zou het niet zien wanneer hij/zij de werkmap opent. Ze kunnen het werkblad echter gemakkelijk zichtbaar maken door met de rechtermuisknop op een bladtab te klikken.

Maar wat als u niet wilt dat ze de werkblad(en) zichtbaar kunnen maken?

U kunt dit doen met behulp van VBA.

De onderstaande code zou alle werkbladen in de werkmap verbergen (behalve het actieve blad), zodat u het niet zichtbaar kunt maken door met de rechtermuisknop op de bladnaam te klikken.

Sub HideAllExcetActiveSheet() Dim Ws als werkblad voor elke Ws in ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden Volgende Ws End Sub

In de bovenstaande code is de eigenschap Ws.Visible gewijzigd in xlBladZeerVerborgen.

Wanneer de eigenschap Visible is ingesteld op xlSheetVisible, is het werkblad zichtbaar in het werkbladgebied (als werkbladtabbladen).
Wanneer de eigenschap Visible is ingesteld op xlSheetHidden, is het blad verborgen, maar de gebruiker kan het zichtbaar maken door met de rechtermuisknop op een bladtab te klikken.
Wanneer de eigenschap Visible is ingesteld op xlSheetVeryHidden, is het blad verborgen en kan het niet zichtbaar worden gemaakt in het werkbladgebied. U moet een VBA-code of het eigenschappenvenster gebruiken om deze zichtbaar te maken.

Als u eenvoudig bladen wilt verbergen, die gemakkelijk zichtbaar kunnen worden gemaakt, gebruikt u de onderstaande code:

Sub HideAllExceptActiveSheet() Dim Ws als werkblad voor elke Ws in ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetHidden Volgende Ws End Sub

De onderstaande code zou alle werkbladen zichtbaar maken (zowel verborgen als erg verborgen).

Sub UnhideAllWoksheets() Dim Ws als werkblad voor elke Ws in ThisWorkbook.Worksheets Ws.Visible = xlSheetVisible Volgende Ws End Sub

Gerelateerd artikel: Alle bladen in Excel zichtbaar maken (in één keer)

Bladen verbergen op basis van de tekst erin

Stel dat u meerdere bladen heeft met de naam van verschillende afdelingen of jaren en u wilt alle bladen verbergen behalve die met het jaar 2021-2022 erin.

U kunt dit doen met behulp van een VBA INSTR-functie.

De onderstaande code zou alle bladen verbergen, behalve die met de tekst2021-2022 erin.

Sub HideWithMatchingText() Dim Ws als werkblad voor elke Ws in werkbladen If InStr(1, Ws.Name, "2018", vbBinaryCompare) = 0 Then Ws.Visible = xlSheetHidden End If Next Ws End Sub

In de bovenstaande code retourneert de functie INSTR de positie van het teken waar het de overeenkomende tekenreeks vindt. Als het de overeenkomende tekenreeks niet vindt, retourneert het 0.

Bovenstaande code controleert of de naam de tekst2021-2022 bevat. Als dit het geval is, gebeurt er niets, anders is het werkblad verborgen.

U kunt een stap verder gaan door de tekst in een cel te plaatsen en die cel in de code te gebruiken. Hierdoor kunt u een waarde in de cel hebben en wanneer u de macro uitvoert, blijven alle bladen, behalve die met de overeenkomende tekst erin, zichtbaar (samen met de bladen waar u de waarde invoert in de cel).

De werkbladen in alfabetische volgorde sorteren

Met VBA kunt u de werkbladen snel sorteren op basis van hun naam.

Als u bijvoorbeeld een werkmap heeft met bladen voor verschillende afdelingen of jaren, kunt u de onderstaande code gebruiken om deze bladen snel in oplopende volgorde te sorteren.

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).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

Merk op dat deze code goed werkt met tekstnamen en in de meeste gevallen ook met jaren en cijfers. Maar het kan u de verkeerde resultaten geven als u de bladnamen als 1,2,11 heeft. Het sorteert en geeft je de reeks 1, 11, 2. Dit komt omdat het de vergelijking als tekst doet en 2 groter dan 11 beschouwt.

Alle bladen in één keer beveiligen/de beveiliging opheffen

Als je veel werkbladen in een werkmap hebt en je wilt alle werkbladen beschermen, dan kun je onderstaande VBA-code gebruiken.

Hiermee kunt u het wachtwoord binnen de code specificeren. U hebt dit wachtwoord nodig om de beveiliging van het werkblad op te heffen.

Sub ProtectAllSheets() Dim ws As Worksheet Dim wachtwoord As String password = "Test123" 'vervang Test123 door het gewenste wachtwoord For Each ws In Worksheets ws.Protect password:=password Volgende ws End Sub

De volgende code zou de beveiliging van alle bladen in één keer opheffen.

Sub ProtectAllSheets() Dim ws As Worksheet Dim wachtwoord As String password = "Test123" 'vervang Test123 door het wachtwoord dat u gebruikte tijdens het beschermen For Each ws In Worksheets ws.Unprotect password:=password Volgende ws End Sub

Een inhoudsopgave maken van alle werkbladen (met hyperlinks)

Als u een set werkbladen in de werkmap hebt en u snel een overzichtsblad wilt invoegen met de koppelingen naar alle bladen, kunt u de onderstaande code gebruiken.

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).Naam & "!A1", _ TextToDisplay:=Werkbladen(i).Naam Volgende i End Sub

De bovenstaande code voegt een nieuw werkblad in en noemt het Index.

Vervolgens doorloopt het alle werkbladen en maakt een hyperlink voor alle werkbladen in het indexblad.

Waar de VBA-code te plaatsen

Vraagt u zich af waar de VBA-code naartoe gaat in uw Excel-werkmap?

Excel heeft een VBA-backend, de VBA-editor. U moet de code kopiëren en plakken in het codevenster van de VB Editor-module.

Hier zijn de stappen om dit te doen:

Ga naar het tabblad Ontwikkelaars.
Klik op de Visual Basic-optie. Dit opent de VB-editor in de backend.
Klik in het deelvenster Projectverkenner in de VB-editor met de rechtermuisknop op een object voor de werkmap waarin u de code wilt invoegen. Als u de Projectverkenner niet ziet, gaat u naar het tabblad Weergave en klikt u op Projectverkenner.
Ga naar Invoegen en klik op Module. Hiermee wordt een moduleobject voor uw werkmap ingevoegd.
Kopieer en plak de code in het modulevenster.