Het gebruik van Excel-macro's kan het werk versnellen en u veel tijd besparen.
Een manier om de VBA-code te krijgen, is door de macro op te nemen en de code te nemen die deze genereert. Die code van macrorecorder zit echter vaak vol met code die eigenlijk niet nodig is. Ook macrorecorder heeft enkele beperkingen.
Het loont dus de moeite om een verzameling nuttige VBA-macrocodes te hebben die u in uw achterzak kunt hebben en deze kunt gebruiken wanneer dat nodig is.
Hoewel het schrijven van een Excel VBA-macrocode in eerste instantie enige tijd kan duren, kunt u deze beschikbaar houden als referentie en deze gebruiken wanneer u deze de volgende keer nodig hebt.
In dit omvangrijke artikel ga ik een aantal handige Excel-macrovoorbeelden opsommen die ik vaak nodig heb en die ik in mijn privékluis bewaar.
Ik zal deze tutorial blijven updaten met meer macro-voorbeelden. Als je denkt dat iets op de lijst moet, laat dan een reactie achter.
U kunt een bladwijzer maken voor deze pagina voor toekomstig gebruik.
Voordat ik inga op het macrovoorbeeld en u de VBA-code geef, wil ik u eerst laten zien hoe u deze voorbeeldcodes kunt gebruiken.
De code uit Excel-macrovoorbeelden gebruiken
Dit zijn 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. Dit opent de VB-editor.
- Klik met de rechtermuisknop op een van de objecten in de projectverkenner.
- Ga naar Invoegen -> Module.
- 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 u de code in het codevenster.
Nadat u de code in een werkmap hebt ingevoegd, moet u deze opslaan met de extensie .XLSM of .XLS.
Hoe de macro uit te voeren?
Nadat u de code in de VB-editor hebt gekopieerd, volgen hier de stappen om de macro uit te voeren:
- Ga naar het tabblad Ontwikkelaars.
- Klik op Macro's.
- Selecteer in het dialoogvenster Macro de macro die u wilt uitvoeren.
- Klik op de knop Uitvoeren.
Als u het ontwikkelaarstabblad niet in het lint kunt vinden, lees dan deze zelfstudie om te leren hoe u het kunt krijgen.
Verwante zelfstudie: verschillende manieren om een macro in Excel uit te voeren.
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 ingaan op de handige macrovoorbeelden die u kunnen helpen bij het automatiseren van werk en het besparen van tijd.
Opmerking: u zult veel gevallen van een apostrof (') vinden, gevolgd door een paar regels. Dit zijn opmerkingen die tijdens het uitvoeren van de code worden genegeerd en als opmerkingen voor zichzelf/lezer worden geplaatst.
Als je een fout in het artikel of de code vindt, wees alsjeblieft geweldig en laat het me weten.
Excel-macrovoorbeelden
Onderstaande macrovoorbeelden worden in dit artikel behandeld:
Alle werkbladen in één keer zichtbaar maken
Als u in een werkmap werkt die meerdere verborgen bladen heeft, moet u deze bladen een voor een zichtbaar maken. Dit kan enige tijd duren als er veel verborgen bladen zijn.
Hier is de code die alle werkbladen in de werkmap zichtbaar maakt.
'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 alle werkbladen in de werkmap te doorlopen. Het verandert vervolgens de zichtbare eigenschap van het werkblad in zichtbaar.
Hier is een gedetailleerde zelfstudie over het gebruik van verschillende methoden om bladen in Excel zichtbaar te maken.
Alle werkbladen verbergen behalve het actieve werkblad
Als u aan een rapport of dashboard werkt en u wilt het hele werkblad verbergen, behalve het werkblad met het rapport/dashboard, dan kunt u deze macrocode gebruiken.
'Deze macro verbergt alle werkbladen 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 werkbladen alfabetisch met VBA
Als je een werkmap hebt met veel werkbladen en je wilt deze alfabetisch sorteren, dan kan deze macrocode erg handig zijn. Dit kan het geval zijn als u bladnamen als jaartallen of werknemersnamen of productnamen 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 in één keer
Als u veel werkbladen in een werkmap hebt en u wilt alle werkbladen beveiligen, kunt u deze macrocode gebruiken.
Hiermee kunt u het wachtwoord binnen de code specificeren. U hebt dit wachtwoord nodig om de beveiliging van het werkblad op te heffen.
'Deze code zal alle bladen in één keer beveiligen Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'vervang Test123 door het gewenste wachtwoord Voor Each ws In Worksheets ws.Protect password:=password Volgende ws Einde sub
Beveiliging van alle werkbladen in één keer opheffen
Als u sommige of alle werkbladen hebt beveiligd, kunt u een kleine wijziging van de code gebruiken om de bladen te beschermen om de beveiliging op te heffen.
'Deze code beveiligt 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
Merk op dat het wachtwoord hetzelfde moet zijn dat is gebruikt om de werkbladen te vergrendelen. Als dit niet het geval is, ziet u een fout.
Alle rijen en kolommen zichtbaar maken
Deze macrocode maakt alle verborgen rijen en kolommen zichtbaar.
Dit kan erg handig zijn als je een bestand van iemand anders krijgt 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 is gebruikelijk om cellen samen te voegen om er één te maken. Hoewel het het werk doet, kunt u de gegevens niet sorteren wanneer cellen worden samengevoegd.
Als je werkt met een werkblad met samengevoegde cellen, gebruik dan de onderstaande code om alle samengevoegde cellen in één keer weer op te heffen.
Met deze code worden alle samengevoegde cellen opgeheven. Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub
Merk op dat in plaats van Samenvoegen en centreren, ik aanraad om de optie Centreren over selectie te gebruiken.
Werkmap opslaan met tijdstempel in zijn naam
Het kost u veel tijd om versies van uw werk te maken. Deze zijn erg handig bij lange projecten waarbij u in de loop van de tijd met een bestand werkt.
Een goede gewoonte is om het bestand met tijdstempels op te slaan.
Door tijdstempels te gebruiken, kunt u teruggaan naar een bepaald bestand om te zien welke wijzigingen zijn aangebracht of welke gegevens zijn gebruikt.
Hier is de code die de werkmap automatisch in de opgegeven map opslaat en een tijdstempel toevoegt wanneer deze wordt opgeslagen.
'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 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. Ik heb ook een generieke naam "WorkbookName" gebruikt als prefix voor de bestandsnaam. U kunt iets specificeren dat betrekking heeft op uw project of bedrijf.
Bewaar elk werkblad als een aparte PDF
Als u met gegevens voor verschillende jaren, divisies of producten werkt, moet u mogelijk verschillende werkbladen opslaan als PDF-bestanden.
Hoewel het een tijdrovend proces kan zijn als het handmatig wordt gedaan, kan VBA het echt versnellen.
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 waarin ik de PDF's wil opslaan. Ook krijgt elke PDF dezelfde naam als die van het werkblad. U moet deze maplocatie wijzigen (tenzij uw naam ook Sumit is en u deze opslaat in een testmap op het bureaublad).
Merk op dat deze code alleen voor werkbladen werkt (en niet voor grafiekbladen).
Bewaar elk werkblad als een aparte PDF
Hier is de code waarmee uw hele werkmap als PDF in de opgegeven map wordt opgeslagen.
'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.
Zet alle formules om in waarden
Gebruik deze code wanneer u een werkblad heeft dat veel formules bevat en u deze formules naar waarden wilt converteren.
'Deze code converteert alle formules naar waarden Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub
Deze code identificeert automatisch cellen die worden gebruikt en zet deze om in waarden.
Bescherm/vergrendel cellen met formules
Mogelijk wilt u cellen vergrendelen met formules wanneer u veel berekeningen hebt en u deze niet per ongeluk wilt verwijderen of wijzigen.
Hier is de code die alle cellen met formules vergrendelt, terwijl alle andere cellen niet zijn vergrendeld.
'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 werkbladen in de werkmap beveiligen
Gebruik de onderstaande code om alle werkbladen in een werkmap in één keer 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 doorloopt alle werkbladen één voor één en beschermt deze.
Als u de beveiliging van alle werkbladen wilt opheffen, 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.
'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
Op dezelfde manier kunt u deze code wijzigen om een lege kolom in te voegen na elke kolom in het geselecteerde bereik.
Automatisch datum- en tijdstempel invoegen in de aangrenzende cel
Een tijdstempel is iets dat u gebruikt wanneer u activiteiten wilt bijhouden.
U wilt bijvoorbeeld activiteiten bijhouden, zoals wanneer een bepaalde uitgave is gemaakt, hoe laat de verkoopfactuur is gemaakt, wanneer de gegevens in een cel zijn ingevoerd, wanneer het rapport voor het laatst is bijgewerkt, enz.
Gebruik deze code om een datum- en tijdstempel in de aangrenzende cel in te voegen wanneer een invoer wordt gemaakt of de bestaande inhoud wordt bewerkt.
'Deze code voegt een tijdstempel 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 (en niet in het codevenster in de module zoals we tot nu toe in andere Excel-macrovoorbeelden hebben gedaan). Dubbelklik hiervoor in de VB Editor op de bladnaam waarop u deze functionaliteit wilt hebben. Kopieer en plak deze code vervolgens in het codevenster van dat blad.
Deze code wordt ook gemaakt om te werken wanneer de gegevensinvoer wordt gedaan 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 wanneer u een afdruk moet maken en de gegevens moet doornemen.
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 gespecificeerd. U kunt ook andere kleuren opgeven (zoals vbRed, vbGreen, vbBlue).
Markeer cellen met verkeerd gespelde woorden
Excel heeft geen spellingcontrole zoals in Word of PowerPoint. Hoewel u 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 die een spelfout bevatten.
'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
Houd er rekening mee dat de cellen die zijn gemarkeerd, de cellen zijn met tekst die Excel als een spelfout beschouwt. In veel gevallen zou het ook namen of merktermen benadrukken die het niet begrijpt.
Alle draaitabellen in de werkmap vernieuwen
Als u meer dan één draaitabel in de werkmap hebt, kunt u deze code gebruiken om al deze draaitabellen tegelijk te vernieuwen.
'Deze code vernieuwt alle draaitabel 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.
Wijzig de letter van geselecteerde cellen in hoofdletters
Hoewel Excel de formules heeft om de letter van de tekst te wijzigen, zorgt het ervoor dat u dat in een andere reeks cellen doet.
Gebruik deze code om direct de hoofdletter van de 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 heb gebruikt om de tekst Case Upper 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.
'Deze code markeert cellen met opmerkingen' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub
In dit geval heb ik vbBlue gebruikt om de cellen een blauwe kleur te geven. U kunt dit in andere kleuren veranderen als u wilt.
Markeer lege cellen met VBA
Hoewel u lege cellen kunt markeren met voorwaardelijke opmaak of het dialoogvenster Ga naar speciaal kunt gebruiken, is het beter om een macro te 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 de lege cellen gespecificeerd die in de rode kleur moeten worden gemarkeerd. U kunt andere kleuren kiezen, zoals blauw, geel, cyaan, enz.
Gegevens sorteren op enkele kolom
U kunt de onderstaande code gebruiken om gegevens te sorteren op de opgegeven kolom.
Sub SortDataHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes End Sub
Merk op dat ik een benoemd bereik heb gemaakt met de naam 'DataRange' en deze heb gebruikt in plaats van de celverwijzingen.
Er zijn ook drie belangrijke parameters die hier worden gebruikt:
- Key1 - Dit is de code waarop u de dataset wilt sorteren. In de bovenstaande voorbeeldcode worden de gegevens gesorteerd op basis van de waarden in kolom A.
- Volgorde- Hier moet u aangeven of u de gegevens in oplopende of aflopende volgorde wilt sorteren.
- Header - Hier moet u aangeven of uw gegevens headers 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 zoals hieronder weergegeven:
Hieronder vindt u de code die de gegevens sorteert 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 te sorteren op kolom A en vervolgens op kolom B.
De uitvoer zou iets zijn zoals hieronder wordt weergegeven:
Alleen het numerieke deel van een string in Excel halen
Als u alleen het numerieke gedeelte of alleen het tekstgedeelte uit een tekenreeks wilt extraheren, kunt u een aangepaste functie maken in VBA.
U kunt deze VBA-functie vervolgens in het werkblad gebruiken (net als gewone Excel-functies) en het zal alleen het numerieke of tekstgedeelte uit de tekenreeks halen.
Iets zoals hieronder weergegeven:
Hieronder staat de VBA-code die een functie maakt om een numeriek deel uit een string te extraheren:
'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 hebt plaats in code in een module nodig, en dan kun je de functie =GetNumeric in het werkblad gebruiken.
Deze functie heeft slechts één argument nodig, namelijk de celverwijzing van de cel waaruit u het numerieke gedeelte wilt halen.
Evenzo is hieronder de functie waarmee u alleen het tekstgedeelte van een tekenreeks in Excel krijgt:
'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 van de handige Excel-macrocodes die u in uw dagelijkse werk kunt gebruiken om taken te automatiseren en een stuk productiever te zijn.
