24 Handige Excel-macrovoorbeelden voor VBA-beginners (klaar voor gebruik)

Het gebruik van Excel-macro's kan uw werk versnellen en veel tijd besparen.

Een manier om de VBA-code te krijgen, is door een macro op te nemen en de code te krijgen die deze genereert.De code van de macrorecorder zit echter vaak vol met code die niet echt nodig is.De macrorecorder heeft ook enkele beperkingen.

Het is dus de moeite waard om een ​​handige set VBA-macro's te hebben die u in uw achterzak kunt bewaren en kunt gebruiken wanneer u ze nodig hebt.

Hoewel het aanvankelijk enige tijd kan duren om een ​​Excel VBA-macro te coderen, kunt u deze, als u klaar bent, als referentie gebruiken en de volgende keer dat u hem nodig hebt gebruiken.

In dit enorme artikel zal ik enkele nuttige Excel-macrovoorbeelden opsommen die ik vaak nodig heb en in mijn privékluis bewaar.

Ik zal deze tutorial blijven updaten met meer macrovoorbeelden.Laat een reactie achter als je denkt dat iets op de lijst moet.

U kunt een bladwijzer maken voor deze pagina voor toekomstig gebruik.

Voordat ik inga op de macrovoorbeelden en u de VBA-code geef, wil ik u eerst laten zien hoe u de voorbeeldcode gebruikt.

De code uit het Excel-macrovoorbeeld gebruiken

Hier zijn de te volgen stappen om de code uit een van de voorbeelden te gebruiken:

• Open de werkmap waarin u de macro wilt gebruiken.
• Houd de ALT-toets ingedrukt en druk op F11.Dit gaat openVB-editor.
• Klik met de rechtermuisknop op een object in de Projectverkenner.
• Ga naar Invoegen -> Modules.
• Kopieer en plak de code in het modulecodevenster.

Als in het voorbeeld staat dat u de code in het codevenster van het werkblad moet plakken, dubbelklikt u op het werkbladobject en kopieert en plakt u de code in het codevenster.

Nadat u de code in de werkmap hebt ingevoegd, moet u deze opslaan met de extensie .XLSM of .XLS.

Een macro uitvoeren?

Na het kopiëren van de code in de VB-editor, zijn de stappen om de macro uit te voeren als volgt:

• Ga naar het tabblad Ontwikkelaars.
• Klik op Macro.

• Selecteer in het dialoogvenster Macro's de macro die u wilt uitvoeren.
• Klik op de knop Uitvoeren.

Als u het tabblad Ontwikkelaar niet in het lint kunt vinden, leest u deze zelfstudie om te leren hoe u het kunt krijgen.

Als de code in het codevenster van het werkblad is geplakt, hoeft u zich geen zorgen te maken over het uitvoeren van de code.Het wordt automatisch uitgevoerd wanneer de opgegeven actie plaatsvindt.

Laten we nu eens kijken naar nuttige macrovoorbeelden die u kunnen helpen uw werk te automatiseren en tijd te besparen.

Opmerking: u zult veel apostrofs (') vinden, gevolgd door een paar regels.Dit zijn opmerkingen die worden genegeerd bij het uitvoeren van de code en worden geplaatst als opmerkingen van de gebruiker/lezer.

Als je fouten in het artikel of de code vindt, laat het me dan weten.

Excel-macrovoorbeeld

In dit artikel worden de volgende macrovoorbeelden beschreven:

Alle werkbladen tegelijk zichtbaar maken

Als u aan een werkmap met meerdere verborgen bladen werkt, moet u deze bladen één voor één zichtbaar maken.Dit kan enige tijd duren als er veel verborgen bladen zijn.

Hier is de code om alle bladen in de werkmap zichtbaar te maken.

"Deze code maakt alle bladen in de werkmap zichtbaar. Sub UnhideAllWoksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook. Worksheets ws. Visible = xlSheetVisible Volgende ws End Sub

De bovenstaande code gebruikt een VBA-lus (voor elk) om elk werkblad in de werkmap te doorlopen.Dan verandert het de zichtbare eigenschap van het blad in zichtbaar.

Hier is een gedetailleerde zelfstudie over het zichtbaar maken van een werkblad in Excel met behulp van verschillende methoden.

Alle werkbladen verbergen behalve het actieve werkblad

U kunt deze macrocode gebruiken als u aan een rapport of dashboard werkt en alle werkbladen wilt verbergen, behalve het werkblad met het rapport/dashboard.

'Deze macro verbergt al het werkblad behalve het actieve blad Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Dan ws.Visible = xlSheetHidden Volgende ws End Sub

Sorteer werkblad alfabetisch met VBA

Deze macrocode kan handig zijn als u een werkmap met veel bladen heeft en u die bladen alfabetisch wilt sorteren.Dit kan gebeuren als u de bladnaam als jaar- of werknemernaam of productnaam heeft.

'Deze code sorteert de werkbladen alfabetisch 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

Bescherm alle werkbladen tegelijk

Als u veel bladen in uw werkmap heeft en u wilt alle bladen beveiligen, dan kunt u deze macrocode gebruiken.

Hiermee kunt u een wachtwoord in code opgeven.U hebt dit wachtwoord nodig om de beveiliging van het blad op te heffen.

'Deze code beschermt alle bladen in één keer Sub ProtectAllSheets() Dim ws As Worksheet Dim wachtwoord As String password = "Test123" 'vervang Test123 door het wachtwoord dat u wilt Voor elke ws In Worksheets ws.Protect password:=password Volgende ws Einde sub

De beveiliging van alle werkbladen in één keer opheffen

Als u sommige of al uw werkbladen hebt beveiligd, kunt u de beveiliging opheffen met slechts een kleine wijziging van de code die wordt gebruikt om de werkbladen te beschermen.

'Deze code beschermt alle bladen in één keer Sub ProtectAllSheets() Dim ws As Worksheet Dim wachtwoord As String password = "Test123" 'vervang Test123 door het gewenste wachtwoord Voor Each ws In Worksheets ws.Unprotect password:=password Volgende ws Einde sub

Houd er rekening mee dat het wachtwoord hetzelfde wachtwoord moet zijn dat wordt gebruikt om het blad te vergrendelen.Zo niet, dan ziet u een foutmelding.

Alle rijen en kolommen zichtbaar maken

Deze macrocode maakt alle verborgen rijen en kolommen zichtbaar.

Dit kan handig zijn als u het bestand van iemand anders overneemt en er zeker van wilt zijn dat er geen verborgen rijen/kolommen zijn.

'Deze code zal alle rijen en kolommen in de Worksheet Sub zichtbaar maken UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

De samenvoeging van alle samengevoegde cellen ongedaan maken

Het samenvoegen van cellen tot één is een gangbare praktijk.Hoewel het werkt, kunt u de gegevens niet sorteren wanneer de cellen zijn samengevoegd.

Als u een werkblad met samengevoegde cellen gebruikt, gebruikt u de onderstaande code om het samenvoegen van alle samengevoegde cellen in één keer ongedaan te maken.

Met deze code worden alle samengevoegde cellen opgeheven. Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Merk op dat ik aanraad om de optie "Center Across Selection" te gebruiken in plaats van de optie "Samenvoegen en centreren".

Werkmap opslaan met tijdstempel in naam

Het komt vaak voor dat u een werkende versie moet maken.Deze zijn geweldig voor langetermijnprojecten waarbij bestanden in de loop van de tijd worden verwerkt.

Een goede gewoonte is om het bestand op te slaan met een tijdstempel.

Door een tijdstempel te gebruiken, kunt u teruggaan naar een bestand om te zien welke wijzigingen zijn aangebracht of welke gegevens zijn gebruikt.

Hier is de code die de werkmap automatisch opslaat in de opgegeven map en een tijdstempel toevoegt bij het opslaan.

'Deze code slaat het bestand op met een tijdstempel in de naam Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & tijdstempel End Sub

U moet de maplocatie en bestandsnaam opgeven.

In de bovenstaande code is "C:UsersUsernameDesktop de maplocatie die ik gebruik.U moet de maplocatie opgeven waar u het bestand wilt opslaan.Ook heb ik de generieke naam "WorkbookName" als prefix voor de bestandsnaam gebruikt.U kunt inhoud specificeren die betrekking heeft op uw project of bedrijf.

Bewaar elk werkblad als een aparte PDF

Als u gegevens uit verschillende jaren of afdelingen of producten gebruikt, moet u mogelijk verschillende werkbladen opslaan als PDF-bestanden.

Hoewel dit een tijdrovend proces kan zijn als het handmatig wordt gedaan, versnelt VBA de zaken wel.

Hier is een VBA-code die elk werkblad als een afzonderlijke PDF opslaat.

'Deze code slaat elk worsheet op als een aparte PDF Sub SaveWorkshetAsPDF() Dim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Volgende ws End Sub

In de bovenstaande code heb ik het adres gespecificeerd van de maplocatie waar ik de PDF wil opslaan.Elke PDF krijgt ook dezelfde naam als het blad.U moet deze maplocatie wijzigen (tenzij uw naam ook Sumit is en u deze in een testmap op uw bureaublad hebt opgeslagen).

Merk op dat deze code alleen werkt voor werkbladen (niet voor grafiekbladen).

Bewaar elk werkblad als een aparte PDF

Hier is de code om de hele werkmap op te slaan als een PDF in de opgegeven map.

'Deze code slaat de hele werkmap op als PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

U moet de maplocatie wijzigen om deze code te gebruiken.

Converteer alle formules naar waarden

Gebruik deze code als je een werkblad hebt met veel formules en je die formules naar waarden wilt converteren.

'Deze code zet alle formules om in waarden Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub

Deze code herkent automatisch de gebruikte cel en zet deze om in een waarde.

Cellen beveiligen/vergrendelen met formules

Als u veel berekeningen heeft en deze niet per ongeluk wilt verwijderen of wijzigen, wilt u misschien cellen vergrendelen met formules.

Hier is de code die alle cellen met formules vergrendelt en alle andere cellen ontgrendeld laat.

'Deze macrocode vergrendelt alle cellen met formules Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub

Gerelateerde tutorial: Cellen vergrendelen in Excel.

Alle bladen in een werkmap beveiligen

Gebruik de volgende code om alle bladen in een werkmap tegelijk te beveiligen.

'Deze code beschermt alle bladen in de werkmap Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws. Protect Next ws End Sub

Deze code gaat één voor één door alle bladen en beschermt ze.

Als je de beveiliging van alle bladen wilt opheffen, gebruik dan ws.Unprotect in plaats van ws.Protect in je code.

Voeg een rij in na elke rij van de selectie

Gebruik deze code wanneer u een lege rij wilt invoegen na elke rij in het geselecteerde bereik.

'Deze code voegt een rij in na elke rij in de selectie Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. ActiveCell.Offset (2, 0) invoegen. Selecteer Volgende i End Sub

Nogmaals, u kunt deze code wijzigen om een ​​lege kolom in te voegen na elke kolom in het geselecteerde bereik.

Automatisch datum en tijdstempel in aangrenzende cellen invoegen

Tijdstempels zijn wat u gebruikt wanneer u activiteit wilt bijhouden.

U wilt bijvoorbeeld activiteiten bijhouden, zoals wanneer een bepaalde uitgave is gemaakt, wanneer een verkoopfactuur is gemaakt, wanneer gegevens in een cel zijn ingevoerd, wanneer een rapport voor het laatst is bijgewerkt, enzovoort.

Gebruik deze code om een ​​datum- en tijdstempel in aangrenzende cellen in te voegen wanneer u bestaande inhoud typt of bewerkt.

'Deze code zal een tijdstempel invoegen in de aangrenzende cel Private Sub Worksheet_Change (ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value <> "" Dan Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy uu:mm:ss") Application.EnableEvents = True End If Handler: End Sub

Merk op dat u deze code in het codevenster van het werkblad moet invoegen (niet in het modulecodevenster zoals we tot nu toe in andere Excel-macrovoorbeelden hebben gedaan).Dubbelklik hiervoor in de VB-editor op de bladnaam waarvoor u deze functionaliteit wilt.Kopieer en plak deze code vervolgens in het codevenster van dat blad.

Deze code werkt ook wanneer gegevens worden ingevoerd in kolom A (merk op dat de code een Target.Column = 1 regel heeft).U kunt het dienovereenkomstig wijzigen.

Markeer alternatieve rijen in selectie

Het markeren van afwisselende lijnen kan de leesbaarheid van uw gegevens aanzienlijk verbeteren.Dit kan handig zijn wanneer u het moet afdrukken en door de gegevens moet bladeren.

Hier is een code die onmiddellijk alternatieve rijen in de selectie markeert.

'Deze code markeert alternatieve rijen in de selectie Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selectie voor elke Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Als Next Myrow End Sub

Merk op dat ik de kleur als vbCyan in de code heb opgegeven.U kunt ook andere kleuren opgeven (bijv. vbRed, vbGreen, vbBlue).

Markeer verkeerd gespelde cellen

Excel heeft geen spellingcontrole in Word of PowerPoint.Hoewel u de spellingcontrole kunt uitvoeren door op F7 te drukken, is er geen visuele aanwijzing voor spelfouten.

Gebruik deze code om direct alle verkeerd gespelde cellen te markeren.

'Deze code markeert de cellen met verkeerd gespelde woorden Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl Einde Sub

Merk op dat de gemarkeerde cellen de cellen zijn met tekst waarvan Excel denkt dat deze verkeerd gespeld is.In veel gevallen worden ook namen of merktermen benadrukt die het niet begrijpt.

Alle draaitabellen in een werkmap vernieuwen

Als u meerdere draaitabellen in de werkmap hebt, kunt u deze code gebruiken om ze allemaal tegelijk te vernieuwen.

'Deze code vernieuwt alle draaitabels in de werkmap Sub RefreshAllPivotTables() Dim PT As PivotTable For Each PT In ActiveSheet.PivotTables PT.RefreshTable Volgende PT End Sub

U kunt hier meer lezen over het vernieuwen van draaitabellen.

Verander de letter van geselecteerde cellen in hoofdletters

Hoewel Excel formules heeft om het geval van tekstletters te wijzigen, kunt u dit in een andere set cellen doen.

Gebruik deze code om direct de hoofdletter van tekst in de geselecteerde tekst te wijzigen.

'Deze code verandert de selectie in hoofdletters Sub ChangeCase() Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

Merk op dat ik in dit geval UCase gebruik om de tekst in hoofdletters in te stellen.U kunt LCase gebruiken voor kleine letters.

Markeer alle cellen met opmerkingen

Gebruik de volgende code om alle cellen met opmerkingen erin te markeren.

'Deze code markeert cellen met opmerkingen' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

In dit geval gebruik ik vbBlue om de cel een blauwe kleur te geven.U kunt het indien nodig in een andere kleur veranderen.

Markeer lege cellen met VBA

Hoewel u voorwaardelijke opmaak kunt gebruiken of het dialoogvenster Ga naar speciaal kunt gebruiken om lege cellen te markeren, kunt u het beste een macro gebruiken als u dit vaak moet doen.

Eenmaal gemaakt, kunt u deze macro in de werkbalk Snelle toegang plaatsen of opslaan in uw persoonlijke macrowerkmap.

Hier is de VBA-macrocode:

'Deze code markeert alle lege cellen in de dataset Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

In deze code heb ik lege cellen gespecificeerd die in rood zijn gemarkeerd.U kunt andere kleuren kiezen, zoals blauw, geel, cyaan, enz.

Gegevens sorteren op een enkele kolom

U kunt de volgende code gebruiken om gegevens op een opgegeven kolom te sorteren.

Sub SortDataHeader() 
Range("DataRange").Sorteersleutel1:=Bereik("A1"), Order1:=xlAscending, Header:=xlJa 
End Sub

Merk op dat ik een benoemd bereik heb gemaakt met de naam "DataRange" en dat heb gebruikt in plaats van de celverwijzing.

Er zijn ook drie belangrijke parameters die hier worden gebruikt:

• Key1 – Dit is de sleutel waarop u de dataset wilt sorteren.In de voorbeeldcode hierboven worden de gegevens gesorteerd op basis van de waarden in kolom A.
• Volgorde1 – Hier moet u aangeven of u de gegevens in oplopende of aflopende volgorde wilt sorteren.
• Header - Hier moet u specificeren of uw gegevens een header hebben of niet.

Lees meer over het sorteren van gegevens in Excel met VBA.

Gegevens sorteren op meerdere kolommen

Stel dat u een dataset heeft die er als volgt uitziet:

Hier is de code om gegevens te sorteren op basis van meerdere kolommen:

Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlJa .Apply End With End Sub

Merk op dat ik hier heb opgegeven om eerst op kolom A te sorteren en vervolgens op kolom B.

De uitvoer ziet er als volgt uit:

Hoe alleen het numerieke deel van een string in Excel te krijgen

Als u alleen het numerieke gedeelte of het tekstgedeelte uit de tekenreeks wilt halen, kunt u een aangepaste functie maken in VBA.

U kunt deze VBA-functie vervolgens in uw werkblad gebruiken (net als een gewone Excel-functie) en het zal alleen het nummer of het tekstgedeelte uit de tekenreeks halen.

Zoals hieronder getoond:

Hier is de VBA-code die een functie maakt die het numerieke deel uit een string haalt:

'Deze VBA-code maakt een functie om het numerieke deel uit een tekenreeks te halen Functie GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Dan Resultaat = Resultaat & Mid(CellRef, i, 1) Volgende i GetNumeric = Resultaat Eindfunctie

Je moet de code in een module plaatsen en dan kun je de functie =GetNumeric in het werkblad gebruiken.

Deze functie heeft slechts één parameter nodig, de celverwijzing van de cel waaruit het numerieke gedeelte moet worden gehaald.

Evenzo haalt de onderstaande functie alleen het tekstgedeelte uit een tekenreeks in Excel:

'Deze VBA-code maakt een functie om het tekstgedeelte uit een tekenreeks te halen. Functie GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Dan Resultaat = Resultaat & Mid(CellRef, i, 1) Volgende i GetText = Resultaat Eindfunctie

Dit zijn dus enkele handige Excel-macrocodes die u in uw dagelijkse werk kunt gebruiken om taken te automatiseren en uw productiviteit te verhogen.