Het gebruik van Excel Macro’s kan het werk versnellen en u veel tijd besparen.
Eén manier om de VBA code te verkrijgen is door de macro op te nemen en de code te nemen die deze genereert. Echter, die code door macro recorder is vaak vol met code die niet echt nodig is. Ook macro recorder heeft een aantal beperkingen.
Dus het loont om een verzameling van nuttige VBA macro codes die je kunt hebben in je achterzak en gebruik het wanneer nodig.
Hoewel het schrijven van een Excel VBA macro code kan enige tijd in beslag nemen in eerste instantie, als het eenmaal gedaan is, kunt u het beschikbaar houden als een referentie en gebruik het wanneer je het nodig hebt de volgende keer.
In dit enorme artikel, ga ik een aantal handige Excel macro voorbeelden opnoemen die ik vaak nodig heb en bewaar in mijn privé kluis.
Ik zal deze tutorial blijven updaten met meer macro voorbeelden. Als je denkt dat iets op de lijst moet staan, laat dan een reactie achter.
Je kunt deze pagina bookmarken voor toekomstige referentie.
Nu voordat ik in de Macro Voorbeeld ga en je de VBA code geef, laat ik je eerst zien hoe je deze voorbeeld codes kunt gebruiken.
De code uit Excel Macro Voorbeelden gebruiken
Hier volgen de stappen die u moet volgen 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. Hierdoor wordt de VB-editor geopend.
- Klik met de rechtermuisknop op een van de objecten in de projectverkenner.
- Ga naar Invoegen -> Module.
- Kopieer en Plak de code in het Module Code Venster.
In het geval dat het voorbeeld zegt dat u de code in het codevenster van het werkblad moet plakken, dubbelklikt u op het werkbladobject en kopieert u de code in het codevenster.
Als u de code in een werkmap hebt ingevoegd, moet u deze opslaan met een .XLSM- of .XLS-extensie.
Hoe voert u de macro uit
Nadat u de code in de VB-editor hebt gekopieerd, volgt u de stappen om de macro uit te voeren:
- Ga naar het tabblad Ontwikkelaar.
- Klik op Macro’s.
- In het dialoogvenster Macro selecteert u de macro die u wilt uitvoeren.
- Klik op de knop Uitvoeren.
In het geval dat u het tabblad Ontwikkelaar niet in het lint kunt vinden, leest u deze tutorial om te leren hoe u het kunt krijgen.
Gerelateerde Tutorial: Verschillende manieren om een macro in Excel uit te voeren.
In het geval dat de code in het werkbladcodevenster is geplakt, hoeft u zich geen zorgen te maken over het uitvoeren van de code. De code wordt automatisch uitgevoerd wanneer de opgegeven actie plaatsvindt.
Nu gaan we naar de nuttige macro-voorbeelden die u kunnen helpen uw werk te automatiseren en tijd te besparen.
Note: U zult veel gevallen aantreffen van een apostrof (‘) gevolgd door een regel of twee. Dit zijn opmerkingen die worden genegeerd tijdens het uitvoeren van de code en zijn geplaatst als notities voor zichzelf/lezer.
In het geval dat u een fout vindt in het artikel of de code, wees dan geweldig en laat het me weten.
Excel Macro Voorbeelden
Onderstaande macro voorbeelden worden in dit artikel behandeld:
Deze Tutorial behandelt:
Alle werkbladen in één keer verbergen
Als u in een werkmap werkt die meerdere verborgen bladen bevat, moet u deze bladen één voor één deactiveren.
Hier volgt de code waarmee u alle werkbladen in de werkmap kunt verbergen.
'This code will unhide all sheets in the workbookSub UnhideAllWoksheets()Dim ws As WorksheetFor Each ws In ActiveWorkbook.Worksheetsws.Visible = xlSheetVisibleNext wsEnd Sub
De bovenstaande code maakt gebruik van een VBA-lus (For Each) om door alle werkbladen in de werkmap te gaan. Vervolgens wordt de eigenschap zichtbaar van het werkblad gewijzigd in zichtbaar.
Hier vindt u een gedetailleerde handleiding over hoe u verschillende methoden kunt gebruiken om werkbladen in Excel te verbergen.
Alle werkbladen verbergen behalve het actieve blad
Als u aan een rapport of dashboard werkt en u wilt alle werkbladen verbergen behalve het werkblad waarop het rapport/dashboard staat, dan kunt u deze macrocode gebruiken.
'This macro will hide all the worksheet except the active sheetSub HideAllExceptActiveSheet()Dim ws As WorksheetFor Each ws In ThisWorkbook.WorksheetsIf ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHiddenNext wsEnd Sub
Werkbladen alfabetisch sorteren met VBA
Als u een werkmap hebt met veel werkbladen en u wilt deze alfabetisch sorteren, dan kan deze macro-code heel goed van pas komen. Dit kan het geval zijn als u bladnamen hebt als jaartallen of namen van werknemers of productnamen.
'This code will sort the worksheets alphabeticallySub SortSheetsTabName()Application.ScreenUpdating = FalseDim ShCount As Integer, i As Integer, j As IntegerShCount = Sheets.CountFor i = 1 To ShCount - 1For j = i + 1 To ShCountIf Sheets(j).Name < Sheets(i).Name ThenSheets(j).Move before:=Sheets(i)End IfNext jNext iApplication.ScreenUpdating = TrueEnd Sub
Alle werkbladen in één keer beveiligen
Als u veel werkbladen in een werkmap hebt en u wilt alle bladen beveiligen, dan kunt u deze macrocode gebruiken.
Hiermee kunt u het wachtwoord binnen de code opgeven. U hebt dit wachtwoord nodig om de beveiliging van het werkblad op te heffen.
'This code will protect all the sheets at one goSub ProtectAllSheets()Dim ws As WorksheetDim password As Stringpassword = "Test123" 'replace Test123 with the password you wantFor Each ws In Worksheets ws.Protect password:=passwordNext wsEnd Sub
Alle werkbladen in één keer beveiligen
Als u sommige of alle werkbladen hebt beveiligd, kunt u een kleine wijziging aanbrengen in de code die wordt gebruikt om de werkbladen te beveiligen om de beveiliging op te heffen.
'This code will protect all the sheets at one goSub ProtectAllSheets()Dim ws As WorksheetDim password As Stringpassword = "Test123" 'replace Test123 with the password you wantFor Each ws In Worksheetsws.Unprotect password:=passwordNext wsEnd Sub
Merk op dat het wachtwoord hetzelfde moet zijn als het wachtwoord waarmee de werkbladen zijn beveiligd. Als dat niet het geval is, krijgt u een foutmelding.
Verberg alle rijen en kolommen
Deze macro-code maakt alle verborgen rijen en kolommen onzichtbaar.
Dit kan erg handig zijn als u een bestand van iemand anders krijgt en er zeker van wilt zijn dat er geen verborgen rijen/kolommen zijn.
'This code will unhide all the rows and columns in the WorksheetSub UnhideRowsColumns()Columns.EntireColumn.Hidden = FalseRows.EntireRow.Hidden = FalseEnd Sub
Unmerge All Merged Cells
Het is gebruikelijk om cellen samen te voegen om er één van te maken. Hoewel dit het werk doet, kun je de gegevens niet sorteren wanneer cellen zijn samengevoegd.
In het geval dat je werkt met een werkblad met samengevoegde cellen, gebruik dan de onderstaande code om alle samengevoegde cellen in één keer te de-samenvoegen.
'This code will unmerge all the merged cellsSub UnmergeAllCells()ActiveSheet.Cells.UnMergeEnd Sub
Merk op dat ik in plaats van Samenvoegen en centreren de optie Door selectie centreren adviseer.
Werkmap opslaan met tijdstempel in de naam
Vaak zult u versies van uw werk moeten maken. Deze zijn heel nuttig bij lange projecten waarbij u in de loop van de tijd met een bestand werkt.
Een goede gewoonte is om het bestand op te slaan met tijdstempels.
Het gebruik van tijdstempels stelt u in staat terug te gaan naar een bepaald bestand om te zien welke wijzigingen er zijn aangebracht of welke gegevens er zijn gebruikt.
Hier volgt de code die de werkmap automatisch opslaat in de opgegeven map en een tijdstempel toevoegt wanneer het wordt opgeslagen.
'This code will Save the File With a Timestamp in its nameSub SaveWorkbookWithTimeStamp()Dim timestamp As Stringtimestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss")ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & timestampEnd Sub
U moet de maplocatie en de bestandsnaam opgeven.
In de bovenstaande code is “C:UsersUsernameDesktop” de maplocatie die ik heb gebruikt. U moet de maplocatie opgeven waar u het bestand wilt opslaan. Ook heb ik een algemene naam “WorkbookName” gebruikt als voorvoegsel voor de bestandsnaam. U kunt iets opgeven dat betrekking heeft op uw project of bedrijf.
Sla elk werkblad op als een aparte PDF
Als u werkt met gegevens voor verschillende jaren of divisies of producten, kunt u de behoefte hebben om verschillende werkbladen op te slaan als PDF-bestanden.
Hoewel dit een tijdrovend proces kan zijn als het handmatig wordt gedaan, kan VBA het echt versnellen.
Hier volgt een VBA-code die elk werkblad als een aparte PDF opslaat.
'This code will save each worsheet as a separate PDFSub SaveWorkshetAsPDF()Dim ws As WorksheetFor Each ws In Worksheetsws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf"Next wsEnd Sub
In de bovenstaande code heb ik het adres van de maplocatie opgegeven waarin ik de PDF’s wil opslaan. Ook zal elke PDF dezelfde naam krijgen als die van het werkblad. U zult deze maplocatie moeten aanpassen (tenzij uw naam ook Sumit is en u opslaat in een testmap op het bureaublad).
Merk op dat deze code alleen werkt voor werkbladen (en niet voor grafiekbladen).
Elk werkblad opslaan als een aparte PDF
Hier volgt de code waarmee u uw hele werkmap opslaat als PDF in de opgegeven map.
'This code will save the entire workbook as PDFSub SaveWorkshetAsPDF()ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf"End Sub
U moet de maplocatie wijzigen om deze code te kunnen gebruiken.
Alle formules omzetten in waarden
Gebruik deze code wanneer u een werkblad hebt dat veel formules bevat en u deze formules wilt omzetten in waarden.
'This code will convert all formulas into valuesSub ConvertToValues()With ActiveSheet.UsedRange.Value = .ValueEnd WithEnd Sub
Deze code identificeert automatisch de cellen die worden gebruikt en zet deze om in waarden.
Cellen met formules beveiligen/vergrendelen
Je wilt cellen met formules misschien vergrendelen als je veel berekeningen hebt en je ze niet per ongeluk wilt verwijderen of wijzigen.
Hier is de code die alle cellen met formules vergrendelt, terwijl alle andere cellen niet vergrendeld zijn.
'This macro code will lock all the cells with formulasSub LockCellsWithFormulas()With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=TrueEnd WithEnd Sub
Gerelateerde tutorial: Cellen vergrendelen in Excel.
Bescherm alle werkbladen in de werkmap
Gebruik de onderstaande code om alle werkbladen in een werkmap in één keer te beschermen.
'This code will protect all sheets in the workbookSub ProtectAllSheets()Dim ws As WorksheetFor Each ws In Worksheetsws.ProtectNext wsEnd Sub
Deze code doorloopt alle werkbladen een voor een en beveiligt ze.
In het geval dat u alle werkbladen wilt deblokkeren, gebruikt u ws.Unprotect in plaats van ws.Protect in de code.
Voeg een rij in na elke andere rij in de selectie
Gebruik deze code wanneer u een lege rij wilt invoegen na elke rij in het geselecteerde bereik.
'This code will insert a row after every row in the selectionSub InsertAlternateRows()Dim rng As RangeDim CountRow As IntegerDim i As IntegerSet rng = SelectionCountRow = rng.EntireRow.CountFor i = 1 To CountRowActiveCell.EntireRow.InsertActiveCell.Offset(2, 0).SelectNext iEnd Sub
Ook kunt u deze code aanpassen om een lege kolom in te voegen na elke kolom in het geselecteerde bereik.
Voeg automatisch datum in & Tijdstempel in de aangrenzende cel
Een tijdstempel is iets dat u gebruikt wanneer u activiteiten wilt bijhouden.
U kunt bijvoorbeeld activiteiten willen bijhouden zoals wanneer een bepaalde uitgave is gedaan, hoe laat de verkoopfactuur is gemaakt, wanneer de gegevens in een cel zijn ingevoerd, wanneer het rapport voor het laatst is bijgewerkt, enzovoort.
Gebruik deze code om een datum en tijd stempel in te voegen in de aangrenzende cel wanneer een invoer wordt gemaakt of de bestaande inhoud wordt bewerkt.
'This code will insert a timestamp in the adjacent cellPrivate Sub Worksheet_Change(ByVal Target As Range)On Error GoTo HandlerIf Target.Column = 1 And Target.Value <> "" ThenApplication.EnableEvents = FalseTarget.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")Application.EnableEvents = TrueEnd IfHandler:End Sub
Merk op dat u deze code moet invoegen in het werkblad code venster (en niet het in module code venster zoals we tot nu toe hebben gedaan in andere Excel macro voorbeelden). Om dit te doen, dubbelklikt u in de VB Editor op de bladnaam waarop u deze functionaliteit wilt hebben. Vervolgens kopieert en plakt u deze code in het codevenster van dat blad.
Ook is deze code zo gemaakt dat hij werkt wanneer de gegevens worden ingevoerd in kolom A (merk op dat de code de regel Target.Column = 1 heeft). U kunt dit dienovereenkomstig wijzigen.
Markeer alternatieve rijen in de selectie
Het markeren van alternatieve rijen kan de leesbaarheid van uw gegevens enorm vergroten. Dit kan handig zijn als je de gegevens moet afdrukken en doornemen.
Hier is een code die direct alternatieve rijen in de selectie markeert.
'This code would highlight alternate rows in the selectionSub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End IfNext MyrowEnd Sub
Merk op dat ik de kleur heb gespecificeerd als vbCyan in de code. Je kunt ook andere kleuren opgeven (zoals vbRood, vbGreen, vbBlue).
Cellen met verkeerd gespelde woorden markeren
Excel heeft geen spellingscontrole zoals Word of PowerPoint die hebben. Hoewel je de spellingcontrole kunt uitvoeren door op de F7-toets te drukken, is er geen visuele aanwijzing wanneer er een spelfout is.
Gebruik deze code om direct alle cellen te markeren waar een spelfout in staat.
'This code will highlight the cells that have misspelled wordsSub HighlightMisspelledCells()Dim cl As RangeFor Each cl In ActiveSheet.UsedRangeIf Not Application.CheckSpelling(word:=cl.Text) Thencl.Interior.Color = vbRedEnd IfNext clEnd Sub
Merk op dat de gemarkeerde cellen de cellen zijn die tekst bevatten die Excel als een spelfout beschouwt. In veel gevallen markeert het ook namen of merktermen die het niet begrijpt.
Vernieuw alle draaitabellen in de werkmap
Als u meer dan één draaitabel in de werkmap hebt, kunt u deze code gebruiken om al deze draaitabellen in één keer te vernieuwen.
'This code will refresh all the Pivot Table in the WorkbookSub RefreshAllPivotTables()Dim PT As PivotTableFor Each PT In ActiveSheet.PivotTablesPT.RefreshTableNext PTEnd Sub
U kunt hier meer lezen over het vernieuwen van draaitabellen.
Verander de hoofdletter van geselecteerde cellen in hoofdletters
Hoewel Excel de formules heeft om de hoofdletter van de tekst te veranderen, laat het u dat doen in een andere set cellen.
Gebruik deze code om direct de letters van de tekst in de geselecteerde tekst te wijzigen.
'This code will change the Selection to Upper CaseSub ChangeCase()Dim Rng As RangeFor Each Rng In Selection.CellsIf Rng.HasFormula = False ThenRng.Value = UCase(Rng.Value)End IfNext RngEnd Sub
Merk op dat ik in dit geval UCase heb gebruikt om de tekst hoofdletters te maken. U kunt LCase gebruiken voor kleine letters.
Markeer alle cellen met opmerkingen
Gebruik de onderstaande code om alle cellen met opmerkingen te markeren.
'This code will highlight cells that have comments`Sub HighlightCellsWithComments()ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlueEnd Sub
In dit geval heb ik vbBlue gebruikt om een blauwe kleur aan de cellen te geven. Je kunt dit desgewenst veranderen in andere kleuren.
Blanco cellen markeren met VBA
Je kunt lege cellen markeren met voorwaardelijke opmaak of met het dialoogvenster Ga naar speciaal, maar als je dat vaak moet doen, is het beter om een macro te gebruiken.
Eenmaal gemaakt, kunt u deze macro in de werkbalk Snelle toegang zetten of opslaan in uw persoonlijke macro-werkmap.
Hier volgt de VBA-macro-code:
'This code will highlight all the blank cells in the datasetSub HighlightBlankCells()Dim Dataset as RangeSet Dataset = SelectionDataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRedEnd Sub
In deze code heb ik aangegeven dat de lege cellen moeten worden gemarkeerd in de kleur rood. U kunt ook andere kleuren kiezen, zoals blauw, geel, cyaan, enz.
Hoe sorteer ik gegevens op één kolom
U kunt de onderstaande code gebruiken om gegevens op de opgegeven kolom te sorteren.
Sub SortDataHeader()Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYesEnd Sub
Merk op dat ik een benoemde reeks heb gemaakt met de naam ‘DataRange’ en deze heb gebruikt in plaats van de celverwijzingen.
Ook zijn er drie belangrijke parameters die hier worden gebruikt:
- Key1 – Dit is de sleutel waarop u de gegevensreeks wilt sorteren. In het bovenstaande voorbeeld worden de gegevens gesorteerd op basis van de waarden in kolom A.
- Order- Hier moet u opgeven of u de gegevens in oplopende of aflopende volgorde wilt sorteren.
- Header – Hier moet u opgeven of uw gegevens al dan niet kopteksten hebben.
Lees meer over hoe u gegevens in Excel kunt sorteren met behulp van VBA.
Hoe sorteer ik gegevens op meerdere kolommen
Voorstel dat u een dataset hebt zoals hieronder is weergegeven:
Hieronder staat de code waarmee de gegevens op basis van meerdere kolommen worden gesorteerd:
Sub SortMultipleColumns()With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1:C13") .Header = xlYes .ApplyEnd WithEnd Sub
Merk op dat ik hier heb opgegeven om eerst op kolom A te sorteren en vervolgens op kolom B.
De uitvoer zou er dan ongeveer zo uitzien als hieronder wordt getoond:
Hoe haal ik alleen het numerieke gedeelte uit een tekenreeks in Excel
Als u alleen het numerieke gedeelte of alleen het tekstgedeelte uit een tekenreeks wilt extraheren, kunt u een aangepaste functie in VBA maken.
U kunt deze VBA-functie vervolgens in het werkblad gebruiken (net als gewone Excel-functies) en alleen het numerieke of tekstgedeelte uit de tekenreeks extraheren.
Zoiets als hieronder wordt weergegeven:
Hieronder staat de VBA-code waarmee een functie wordt gemaakt om het numerieke gedeelte uit een tekenreeks te extraheren:
'This VBA code will create a function to get the numeric part from a stringFunction GetNumeric(CellRef As String)Dim StringLength As IntegerStringLength = Len(CellRef)For i = 1 To StringLengthIf IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)Next iGetNumeric = ResultEnd Function
U moet de code in een module plaatsen, en dan kunt u de functie =GetNumeric in het werkblad gebruiken.
Deze functie neemt slechts één argument, en dat is de celverwijzing van de cel waaruit u het numerieke gedeelte wilt halen.
Op dezelfde manier is hieronder de functie waarmee u alleen het tekstgedeelte uit een tekenreeks in Excel krijgt:
'This VBA code will create a function to get the text part from a stringFunction GetText(CellRef As String)Dim StringLength As IntegerStringLength = Len(CellRef)For i = 1 To StringLengthIf Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)Next iGetText = ResultEnd Function
Dit zijn dus enkele van de nuttige Excel macro codes die u in uw dagelijkse werk kunt gebruiken om taken te automatiseren en een stuk productiever te zijn.
Andere Excel-tutorials die u wellicht interessant vindt:
- Hoe verwijder ik macro’s in Excel