Een macro opnemen in Excel - een stapsgewijze handleiding

Zelfs als je een complete newbie bent in de wereld van Excel VBA, kun je eenvoudig een macro opnemen en een deel van je werk automatiseren.

In deze gedetailleerde handleiding behandel ik alles wat u moet weten om aan de slag te gaan met het opnemen en gebruiken van macro's in Excel.

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

Wat is een macro?

Als je een beginner bent in VBA, zal ik je eerst vertellen wat een macro is - ik zal deze term tenslotte in de hele tutorial blijven gebruiken.

Een macro is een code geschreven in VBA (Visual Basic for Applications) waarmee u een stuk code kunt uitvoeren wanneer het wordt uitgevoerd.

Vaak zul je merken dat mensen (inclusief ikzelf) naar een VBA-code verwijzen als een macro - of deze nu is gegenereerd met behulp van een macrorecorder of handmatig is geschreven.

Wanneer u een macro opneemt, houdt Excel de stappen die u neemt nauwlettend in de gaten en noteert deze in een taal die het begrijpt, namelijk VBA.

En aangezien Excel echt een goede notulist is, maakt het een zeer gedetailleerde code (zoals we later in deze tutorial zullen zien).

Wanneer u nu de opname stopt, de macro opslaat en uitvoert, gaat Excel gewoon terug naar de VBA-code die het heeft gegenereerd en volgt exact dezelfde stappen.

Dit betekent dat zelfs als u niets van VBA weet, u sommige taken kunt automatiseren door Excel uw stappen één keer te laten registreren en deze later opnieuw te gebruiken.

Laten we nu een duik nemen en kijken hoe u een macro in Excel kunt opnemen.

Het tabblad Ontwikkelaar in het lint krijgen

De eerste stap om een ​​macro op te nemen is om het tabblad Ontwikkelaar in het lint te krijgen.

Als u het ontwikkelaarstabblad al in het lint ziet, gaat u naar het volgende gedeelte, anders volgt u de onderstaande stappen:

• Klik met de rechtermuisknop op een van de bestaande tabbladen in het lint en klik op de optie 'Het lint aanpassen'. Het opent het dialoogvenster Excel-opties.
• In het dialoogvenster Excel-opties hebt u de opties Het lint aanpassen. Vink aan de rechterkant, in het deelvenster Hoofdtabbladen, de optie Ontwikkelaar aan.
• Klik OK.

De bovenstaande stappen zouden het tabblad Ontwikkelaar beschikbaar maken in het lintgebied.

Een macro opnemen in Excel

Nu we alles op zijn plaats hebben, laten we leren hoe we een macro in Excel kunnen opnemen.

Laten we een heel eenvoudige macro opnemen - een die een cel selecteert en de tekst 'Excel' erin invoert. Ik gebruik de tekst 'Excel' tijdens het opnemen van deze macro, maar voel je vrij om je naam of een andere tekst in te voeren die je leuk vindt.

Dit zijn de stappen om deze macro op te nemen:

1. Klik op het tabblad Ontwikkelaars.
2. Klik in de groep Code op de knop Macro. Dit opent het dialoogvenster 'Macro opnemen'.
3. Voer in het dialoogvenster Macro opnemen een naam in voor uw macro. ik gebruik de naam Tekst invoeren. Er zijn enkele naamgevingsvoorwaarden waaraan u moet voldoen bij het benoemen van een macro. U kunt bijvoorbeeld geen spaties ertussen gebruiken. Ik geef er meestal de voorkeur aan mijn macronamen als een enkel woord te houden, met verschillende delen met een eerste alfabet met een hoofdletter. U kunt ook onderstrepingstekens gebruiken om twee woorden te scheiden, zoals Enter_Text.
4. (Optionele stap) U kunt desgewenst een sneltoets toewijzen. In dit geval gebruiken we de sneltoets Control + Shift + N. Onthoud dat de sneltoets die u hier toewijst, alle bestaande sneltoetsen in uw werkmap zou overschrijven. Als u bijvoorbeeld de sneltoets Control + S toewijst, kunt u deze niet gebruiken om de werkmap op te slaan (in plaats daarvan wordt elke keer dat u deze gebruikt, de macro uitgevoerd).
5. Zorg ervoor dat bij de optie 'Macro opslaan in' 'Deze werkmap' is geselecteerd. Deze stap zorgt ervoor dat de macro deel uitmaakt van de werkmap. Het zal er zijn wanneer u het opslaat en opnieuw opent, of zelfs als u het met iemand deelt.
6. (Optionele stap) Voer een beschrijving in. Ik doe dit meestal niet, maar als je extreem georganiseerd bent, wil je misschien toevoegen waar de macro over gaat.
7. Klik OK. Zodra u op OK klikt, begint het uw acties in Excel vast te leggen. U kunt de knop 'Opname stoppen' zien op het tabblad Ontwikkelaars, wat aangeeft dat de macro-opname bezig is.
8. Selecteer cel A2.
9. Voer de tekst in Excel (of je kunt je naam gebruiken).
10. Druk op de Enter-toets. Hiermee wordt cel A3 geselecteerd.
11. Klik op de knop Opname stoppen op het tabblad Ontwikkelaar.

Gefeliciteerd!

Je hebt zojuist je eerste macro opgenomen in Excel. Je bent niet langer een macro-maagd.

Hoewel de macro niets nuttigs doet, zal het zijn doel dienen door uit te leggen hoe een macrorecorder in Excel werkt.

Laten we nu doorgaan en deze macro testen.

Volg de onderstaande stappen om de macro te testen:

1. Verwijder de tekst in cel A2. Dit is om te testen of de macro de tekst in cel A2 invoegt of niet.
2. Selecteer een willekeurige cel - behalve A2. Dit is om te testen of de macro cel A2 selecteert of niet.
3. Klik op het tabblad Ontwikkelaars.
4. Klik in de groep Code op de knop Macro's.
5. Klik in het dialoogvenster Macro op de macro Naam - EnterText.
6. Klik op de knop Uitvoeren.

U zult merken dat zodra u op de knop Uitvoeren klikt, de tekst 'Excel' wordt ingevoegd in cel A2 en cel A3 wordt geselecteerd.

Dit kan allemaal in een fractie van een seconde gebeuren, maar in werkelijkheid volgde de macro - net als een gehoorzame elf - de exacte stappen die je liet zien tijdens het opnemen van de macro.

Dus de macro selecteert eerst de cel A2 en voert vervolgens de tekst in Excel erin en selecteert vervolgens de cel A3.

Opmerking: u kunt de macro ook uitvoeren met de sneltoets Control + Shift + N (houd de Control- en de Shift-toets ingedrukt en druk vervolgens op de N-toets). Dit is dezelfde sneltoets die we aan de macro hebben toegewezen bij het opnemen ervan.

Wat het opnemen van een macro doet in de backend

Laten we nu naar de Excel-backend gaan - de VB Editor - en kijken wat het opnemen van een macro echt doet.

Dit zijn de stappen om de VB-editor in Excel te openen:

1. Klik op het tabblad Ontwikkelaars.
2. Klik in de groep Code op de Visual Basic-knop.

Of u kunt de sneltoets gebruiken - ALT + F11 (houd de ALT-toets ingedrukt en druk op F11), in plaats van de bovenstaande twee stappen. Deze snelkoppeling opent ook dezelfde VB-editor.

Als je nu de VB Editor voor het eerst ziet, wees dan niet overweldigd.

Laat me je snel vertrouwd maken met de anatomie van de VB Editor.

• Menubalk: Hier heb je alle opties van VB Editor. Beschouw dit als het lint van VBA. Het bevat opdrachten die u kunt gebruiken terwijl u met de VB Editor werkt.
• Werkbalk - Dit is vergelijkbaar met de werkbalk Snelle toegang van de VB-editor. Het wordt geleverd met een aantal handige opties en u kunt er meer opties aan toevoegen. Het voordeel is dat een optie in de werkbalk slechts een klik verwijderd is.
• Venster Projectverkenner - Dit is waar Excel alle werkmappen en alle objecten in elke werkmap vermeldt. Als we bijvoorbeeld een werkmap hebben met 3 werkbladen, wordt deze weergegeven in de Projectverkenner. Er zijn hier enkele extra objecten zoals modules, gebruikersformulieren en klassenmodules.
• Codevenster - Hier wordt de VBA-code vastgelegd of geschreven. Er is een codevenster voor elk object dat in de Projectverkenner wordt vermeld - zoals werkbladen, werkmappen, modules, enz. We zullen later in deze tutorial zien dat de opgenomen macro in het codevenster van een module gaat.
• Eigenschappenvenster - U kunt de eigenschappen van elk object in dit venster zien. Ik gebruik dit venster vaak om objecten een naam te geven of de verborgen eigenschappen te wijzigen. Mogelijk ziet u dit venster niet wanneer u de VB-editor opent. Om dit weer te geven, klikt u op het tabblad Weergave en selecteert u Eigenschappenvenster.
• Onmiddellijk venster - Ik gebruik vaak het directe venster tijdens het schrijven van code. Het is handig wanneer u enkele uitspraken wilt testen of tijdens het debuggen. Het is mogelijk niet standaard zichtbaar en u kunt het laten verschijnen door op het tabblad Weergave te klikken en de optie Direct venster te selecteren.

Toen we de macro opnamen - Tekst invoeren, gebeurden de volgende dingen in de VB Editor:

• Er is een nieuwe module geplaatst.
• Er is een macro opgenomen met de naam die we hebben opgegeven - Tekst invoeren
• De code is geschreven in het codevenster van de module.

Als u dus dubbelklikt op de module (in dit geval Module 1), verschijnt een codevenster zoals hieronder weergegeven.

Hier is de code die de macrorecorder ons heeft gegeven:

Sub EnterText() ' ' EnterText Macro ' ' Sneltoets: Ctrl+Shift+N ' Range("A2").Select ActiveCell.FormulaR1C1 = "Excel" Range("A3").Select End Sub

In VBA is elke regel die volgt op de ‘ (apostrofteken) wordt niet uitgevoerd. Het is een opmerking die alleen ter informatie is geplaatst. Als u de eerste vijf regels van deze code verwijdert, werkt de macro nog steeds zoals verwacht.

Laat me nu snel bespreken wat elke regel code doet:

De code begint met Sub gevolgd door de naam van de macro en lege haakjes. Sub is een afkorting voor Subroutine. Elke subroutine (ook wel Procedure genoemd) in VBA begint met Sub en eindigt met End Sub.

• Bereik ("A2"). Selecteer - Deze regel selecteert de cel A2.
• ActiveCell.FormulaR1C1 = "Excel" - deze regel voert de tekst Excel in de actieve cel in. Omdat we A2 als de eerste stap hebben geselecteerd, wordt het onze actieve cel.
• Bereik ("A3"). Selecteer - Dit selecteert cel A3. Dit gebeurt als we op de Enter-toets drukken na het invoeren van de tekst, met als resultaat het selecteren van cel A3.

Ik hoop dat je nu een basiskennis hebt van het opnemen van een macro in Excel.

Houd er rekening mee dat de code die door een macrorecorder is geschreven, geenszins een efficiënte code is.

Macrorecorder voegt soms veel pluis toe aan de code wat soms niet nodig is. Maar dit betekent niet dat het niet nuttig is. Voor iemand die VBA leert, kan een macrorecorder een geweldige manier zijn om te analyseren hoe dingen in VBA werken.

Absoluut versus relatief macro-opname

U kent al absolute en relatieve verwijzingen in Excel … toch?

Als dat niet het geval is, lees dan eerst deze tutorial over referenties.

Lees het? Laten we verder gaan.

We zullen later in deze sectie zien hoe u macro's opneemt in absolute en relatieve referenties. Maar laat me eerst snel het verschil tussen absolute en relatieve referentie in VBA samenvatten (voor het geval je lui werd en de link die ik een paar regels geleden gaf niet hebt gelezen):

Als u een absolute verwijzingsoptie gebruikt om een ​​macro op te nemen, zou de VBA-code altijd verwijzen naar dezelfde cellen die u hebt gebruikt. Als u bijvoorbeeld cel A2 selecteert, voert u de tekst Excel in en drukt u elke keer op Enter - ongeacht waar u zich in het werkblad bevindt en ongeacht welke cel is geselecteerd, uw code zou eerst cel A2 selecteren, de tekst Excel invoeren en ga dan naar cel A3.

Als u een relatieve verwijzingsoptie gebruikt om een ​​macro op te nemen, zou VBA de celverwijzingen niet hardcoderen. In plaats daarvan zou het zich concentreren op de beweging in vergelijking met de actieve cel. Stel bijvoorbeeld dat u al cel A1 hebt geselecteerd en u begint met het opnemen van de macro in de relatieve referentiemodus.

Nu selecteert u cel A2, voert u de tekst Excel in en drukt u op de enter-toets. Wanneer u deze macro nu uitvoert, gaat deze niet terug naar cel A2, maar wordt verplaatst ten opzichte van de actieve cel. Als cel K3 bijvoorbeeld is geselecteerd, wordt deze verplaatst naar K4, voert u de tekst Excel in en selecteert u ten slotte cel K5.

Laat me je nu vertellen hoe je een macro opneemt in de modus voor relatieve referenties:

1. Selecteer cel A1.
2. Klik op het tabblad Ontwikkelaars.
3. Klik in de groep Code op de knop 'Relatieve verwijzingen gebruiken'. Het wordt groen, wat aangeeft dat het is ingeschakeld.
4. Klik op de knop Macro opnemen.
5. Voer in het dialoogvenster Macro opnemen een naam in voor uw macro. ik gebruik de naam EnterTextRelRef.
6. Zorg ervoor dat bij de optie Macro opslaan in 'Deze werkmap' is geselecteerd.
7. Klik OK.
8. Selecteer cel A2.
9. Voer de tekst in Excel (of u kunt uw naam invullen).
10. Druk op de Enter-toets. Dit brengt de cursor naar cel A3.
11. Klik op de knop Opname stoppen op het tabblad Ontwikkelaar.

Dit zou de macro opnemen in de relatieve referentiemodus.

Doe dit nu.

1. Selecteer een willekeurige cel (behalve A1).
2. Klik op het tabblad Ontwikkelaars.
3. Klik in de groep Code op de knop Macro's.
4. Klik in het dialoogvenster Macro op de naam van de macro - EnterTextRelRef.
5. Klik op de knop Uitvoeren.

Wat gebeurt er? Ging de cursor terug naar cel A3.

Het zou niet - omdat u de macro hebt opgenomen in de relatieve referentiemodus. Dus de cursor zou relatief ten opzichte van de actieve cel bewegen. Als u dit bijvoorbeeld doet wanneer cel K3 is geselecteerd, wordt de tekst Excel is cel K4 ingevoerd en wordt uiteindelijk cel K5 geselecteerd.

Hier is de code die wordt opgenomen in de backend (codevenster van de VB Editor-module):

Sub EnterTextRelRef() ' ' EnterTextRelRef Macro ' ' ActiveCell.Offset(1, 0).Bereik("A1").Selecteer ActiveCell.FormulaR1C1 = "Excel" ActiveCell.Offset(1, 0).Bereik("A1"). Selecteer Einde Sub

Merk op dat deze hele code nergens naar de cellen K3 of K4 verwijst. In plaats daarvan gebruikt het de Activecell om naar de geselecteerde cel te verwijzen en Offset om te verplaatsen ten opzichte van de actieve cel.

Maak je geen zorgen over het gedeelte Range ("A1") dat de code heeft. Het is een van die onnodige codes die de macrorecorder toevoegt die geen doel heeft en kan worden verwijderd. Zonder zou de code prima werken.

De knop 'Relatieve referentie gebruiken' op het tabblad Ontwikkelaar is een schakelknop. U kunt het uitschakelen (en terugschakelen naar absolute referentie) door erop te klikken.

Wat een macrorecorder niet kan doen?

Macrorecorder is geweldig in het volgen van u in Excel en het vastleggen van uw exacte stappen, maar het kan u mislukken wanneer u het nodig heeft om meer te doen.

• U kunt geen code uitvoeren zonder het object te selecteren. Als u wilt dat de macrorecorder naar het volgende werkblad gaat en alle gevulde cellen in kolom A markeert, zonder het huidige werkblad te verlaten, kan dit niet. Het is omdat als ik je vraag om dit te doen, zelfs jij dat niet kunt doen (zonder het huidige blad te verlaten). En als u het niet zelf kunt doen, hoe zal de macrorecorder uw acties dan vastleggen. In dergelijke gevallen moet u de code handmatig gaan maken/bewerken.
• U kunt geen aangepaste functie maken met een macrorecorder. Met VBA kunt u aangepaste functies maken die u in het werkblad als normale functies kunt gebruiken. U kunt dit maken door de code handmatig te schrijven.
• U kunt geen codes uitvoeren op basis van gebeurtenissen: In VBA kun je veel gebeurtenissen gebruiken, zoals het openen van een werkmap, het toevoegen van een werkblad, dubbelklikken op een cel, enz., om een ​​code uit te voeren die aan die gebeurtenis is gekoppeld. U kunt hiervoor een macrorecorder gebruiken.
• U kunt geen loops maken met een macrorecorder. Wanneer u de code handmatig invoert, kunt u gebruikmaken van de kracht van lussen in VBA (zoals For Next, For Each Next, Do While, Do Until). Maar u kunt dit niet doen wanneer u een macro opneemt.
• U kunt de voorwaarden niet analyseren: U kunt controleren op voorwaarden binnen de code met behulp van macrorecorder. Als u handmatig een VBA-code schrijft, kunt u de IF Then Else-instructies gebruiken om een ​​voorwaarde te analyseren en een code uit te voeren als deze waar is (of een andere code als deze onwaar is).
• U kunt geen argumenten doorgeven in een macroprocedure: Wanneer u een macro opneemt, heeft deze nooit argumenten. Een subroutine kan invoerargumenten aannemen die binnen de macro kunnen worden gebruikt om een ​​taak uit te voeren. Tijdens het opnemen van een macro kan dit niet worden gedaan omdat de opgenomen macro's onafhankelijk zijn en niet verbonden zijn met andere bestaande macro's.

Bestandsextensies met ingeschakelde macro's

Wanneer u een macro opneemt of handmatig VBA-code schrijft in Excel, moet u het bestand opslaan met een macro-enabled bestandsextensie (.xlsm).

Vóór Excel 2007 was er één enkele bestandsindeling die volstond - .xls.

Maar vanaf 2007 werd .xlsx geïntroduceerd als de standaard bestandsextensie. De bestanden die zijn opgeslagen als .xlsx kunnen geen macro bevatten. Dus als u een bestand heeft met de extensie .xlsx en u neemt/schrijft een macro en slaat deze op, dan zal het u waarschuwen om het op te slaan in een macro-enabled formaat, en u een dialoogvenster tonen (zoals hieronder getoond):

Als u Nee selecteert, kunt u het in Excel opslaan in een macro-enabled formaat. Maar als u op Ja klikt, verwijdert Excel automatisch alle code uit uw werkmap en slaat deze op als een .xlsx-werkmap.

Dus als u een macro in uw werkmap hebt, moet u deze opslaan in de .xlsm-indeling om die macro te behouden.

Verschillende manieren om een ​​macro in Excel uit te voeren

Tot nu toe hebben we maar één manier gezien om een ​​macro in Excel uit te voeren - en dat is met behulp van het dialoogvenster Macro.

Maar er zijn een aantal manieren waarop u macro's kunt uitvoeren.

1. Voer een macro uit vanaf het lint (tabblad Ontwikkelaar)
2. Een sneltoets gebruiken (die u moet toewijzen)
3. De macro toewijzen aan een vorm
4. De macro toewijzen aan een knop
5. Voer een macro uit vanuit de VB Editor

Conclusie - Neem een ​​macro op wanneer deze vastzit

Ik zei al dat een macrorecorder een handig hulpmiddel is voor iedereen die met VBA in Excel werkt.

Als je de macrorecorder een paar keer gebruikt, zul je merken dat hij veel onnodige code uitspuugt. Het is echter nog steeds nuttig en geeft u enkele ideeën over waar u moet beginnen. Als ik je bijvoorbeeld vraag om een ​​kolom met cellen te filteren met VBA en je hebt geen idee wat de syntaxis is, kun je snel een macro opnemen en de code bekijken.

Een macrorecorder is een onmisbaar hulpmiddel en zelfs na jarenlange VBA-coderingservaring onder mijn riem, neem ik vaak mijn toevlucht tot de macrorecorder voor hulp.