Excel-macro's - praktische zelfstudie voor beginners met voorbeelden

excel macros hands tutorial

Deze praktische zelfstudie over Excel-macro's legt uit wat een macro is, hoe u VBA-macro's maakt en gebruikt met tal van voorbeelden:

De meesten van ons in de industrie zullen zeker bepaalde taken hebben die bijna elke dag herhaaldelijk moeten worden uitgevoerd. Stel je nu voor dat die taken met slechts een enkele klik worden uitgevoerd. Klinkt spannend? Excel Macros is daar het antwoord op.

In deze tutorial leren we wat een macro is. Hoe u een macro kunt opnemen met behulp van absolute en relatieve referentie en enkele praktische voorbeelden.

​ Bekijk hier de VBA-beginnershandleiding

Wat je leert:

• Wat zijn Excel-macro's
  • Macro's inschakelen in Excel
  • Het tabblad Ontwikkelaar inschakelen
  • Opties van het tabblad Ontwikkelaar
  • Hoe een macro op te nemen
  • Een Excel-werkmap met macro opslaan
  • Een macro uitvoeren
  • Celverwijzing
  • Neem Excel-macro op met behulp van relatieve referentie
  • Veel Gestelde Vragen
• Gevolgtrekking
  • Aanbevolen literatuur

Wat zijn Excel-macro's

Een macro is een reeks acties die u kunt uitvoeren om de gewenste taak uit te voeren.

Stel dat u elke maand een rapport maakt dat de gebruikersaccounts moet markeren met het achterstallige bedrag in vet en rood. Vervolgens kunt u een macro maken en uitvoeren die deze opmaakwijzigingen op elk gewenst moment toepast.

Macro's inschakelen in Excel

Het tabblad Ontwikkelaar geeft ons de mogelijkheid om te werken met functies zoals macro's, invoegtoepassingen, en stelt ons ook in staat om onze eigen VBA-code te schrijven die ons zal helpen om alles wat we willen te automatiseren. Dit tabblad is standaard verborgen.

Volg de onderstaande stappen om het tabblad Ontwikkelaar zichtbaar te maken. Dit werkt op alle versies van Excel voor Windows (Excel 2007,2010, 2013, 2016, 2019).

Notitie: Dit is een eenmalig proces. Zodra u het ontwikkelaarstabblad inschakelt, wordt het altijd weergegeven in een aangepast lint voor elk Excel-exemplaar dat u opent, tenzij u doorgaat en het expliciet uitschakelt.

Het tabblad Ontwikkelaar inschakelen

# 1) Klik op de het dossier tabblad

#twee) Klik Opties

# 3) Klik op Lint aanpassen.

# 4) Schakel onder Lint aanpassen in Ontwikkelaar.

Zodra u het tabblad Ontwikkelaar heeft ingeschakeld, wordt het weergegeven in de lintlijst.

Opties van het tabblad Ontwikkelaar

Hieronder staan ​​de opties vermeld die aanwezig zijn op het tabblad Ontwikkelaar.

• Visual Basic: Geeft een editor om VBA-code te schrijven of te bewerken. Kan ook worden geopend met Alt + F11.
• Macro's: Geeft de lijst met alle reeds opgenomen macro's en wordt ook gebruikt om een ​​nieuwe op te nemen. Alt + F8 opent de lijst met macro's direct.
• Invoegtoepassingen: Maakt het mogelijk om een ​​invoegtoepassing in te voegen en kan deze ook beheren.
• Besturing: Helpt ons bij het gebruik van formulierbesturingselementen en ActiveX-besturingselementen. De eigenschappen van het besturingselement bekijken en bewerken. Ontwerpmodus AAN / UIT wordt hier geregeld.
• XML: Helpt ons een XML-gegevensbestand te importeren / exporteren, XML-uitbreidingspakketten te beheren en ook het taakvenster XML-bron te openen.

Hoe een macro op te nemen

Beschouw eens een voorbeeld , dat uw bedrijf een bepaalde tool heeft die urenstaten genereert voor verschillende afdelingen in Excel. U als manager hebt de verantwoordelijkheid om het blad elke week te beoordelen en naar het financiële team te sturen.

Maar voordat u verzendt, wordt u gevraagd om wat opmaak te maken, zoals:

1. Voeg de titel in voor elk blad dat de teamnaam en het weeknummer bevat, markeer het vetgedrukt en de achtergrond geel.
2. Teken een rand
3. Maak de kolomkoppen vet.
4. Hernoem de bladnaam als de teamnaam.

In plaats van dit elke week handmatig te doen, kunt u gewoon een macro maken en al deze acties met slechts één klik uitvoeren.

Macro opnemen is vrij eenvoudig. Navigeer naar het tabblad Ontwikkelaar en klik op Macro opnemen.

Dit opent een venster waar je naar binnen moet.

# 1) Macronaam: Naam mag geen spaties tussen woorden bevatten. Dit moet beginnen met een alfabet of een onderstrepingsteken.

# 2) Sneltoets: Dit is handig wanneer u een macro uitvoert. Als u op de sneltoets drukt, wordt deze uitgevoerd. Zorg ervoor dat u een sleutel opgeeft die nog niet in gebruik is, anders zal de macro die overschrijven.

Bijvoorbeeld, als u Ctrl + S als de sneltoets noemt, wordt uw macro elke keer dat u op Ctrl + S drukt, uitgevoerd en wordt de optie voor het opslaan van bestanden genegeerd. Daarom wordt aanbevolen om Shift toe te voegen, zoals Ctrl + Shift + D

# 3) Macro opslaan in: Dit heeft 3 opties zoals hieronder weergegeven.

• Dit werkboek: Alle gemaakte macro's zijn alleen beschikbaar voor de huidige werkmap. Als u een nieuw Excel-bestand opent, is de eerder gemaakte macro niet beschikbaar en kan deze dus niet worden gebruikt.
• Persoonlijke macrowerkmap: Als u dit selecteert, wordt de gemaakte macro opgeslagen en weergegeven wanneer u een nieuw Excel-blad opent.
• Nieuw werkboek: Met deze optie wordt een nieuwe werkmap geopend en worden de acties die in die werkmap zijn uitgevoerd, vastgelegd.

# 4) Beschrijving: Dit beschrijft het doel van de macro. Het wordt aanbevolen om een ​​gedetailleerde beschrijving te geven, zodat iedereen die dat gebruikt, weet waarvoor het precies wordt gebruikt.

Nadat u de details voor de hierboven genoemde velden heeft ingevuld, kunt u doorgaan en de vereiste acties uitvoeren in de Excel-werkmap en wordt alles vastgelegd. Als u klaar bent, gaat u terug naar het tabblad Ontwikkelaar en klikt u op Stop met opnemen.

Een Excel-werkmap met macro opslaan

Selecteer Macro opslaan in als 'Deze werkmap': Bedenk dat u tijdens het opnemen de macro Opslaan in 'Dit werkboek' hebt geselecteerd. Als je klaar bent, ga je gang en sla je het bestand op. Tijdens het opslaan moet u Excel Macro-Enabled Workbook selecteren. U hoeft de macro niet expliciet op te slaan. Het wordt automatisch opgeslagen.

Selecteer Macro opslaan in als 'Persoonlijke macro-werkmap': Overweeg nu om tijdens het opnemen de macro Opslaan in als 'Persoonlijke macro-werkmap' te selecteren. U moet de macro expliciet opslaan. Als u alleen het Excel-bestand opslaat en vervolgens probeert het bestand te sluiten. Vervolgens ontvangt u een pop-upvenster zoals hieronder weergegeven.

Notitie: Als u dit niet opslaat, wordt de macro verwijderd.

Een macro uitvoeren

Nu we klaar zijn met het opnemen en opslaan van het bestand, gaan we proberen het uit te voeren en de gewenste resultaten te bereiken. We zijn doorgegaan en hebben een macro opgenomen met alle stappen die nodig zijn om te bereiken in het voorbeeld van de aanwezigheidsregistratie en deze opgeslagen als een dit werkboek met de sneltoets als Ctrl + Shift + B.

Dus elke week wanneer u een nieuwe Excel ontvangt van de softwaretool, hoeft u alleen maar dat Excel-bestand te openen en op de sneltoets (Ctrl + Shift + B) te drukken en alle wijzigingen worden zoals verwacht verwerkt. De resulterende Excel wordt hieronder gegeven.

Bijgevoegd de Excel-Macro-werkmap

Notitie:

1. Als u de sneltoets bent vergeten, gaat u naar Ontwikkelaar -> Macro's, Selecteer de macro en klik op opties.
2. Als de macro die is opgeslagen als een persoonlijke winkel, niet zichtbaar is onder het tabblad Macro's. Ga naar View -> Unhide en dit toont de lijst met alle macro's.

Celverwijzing

Er zijn 2 manieren om een ​​macro op te nemen, zoals hieronder weergegeven.

1. Absolute celverwijzing
2. Relatieve celverwijzing

Absolute celverwijzing: Absolute verwijzingen zullen altijd verwijzen naar de specifieke cel waarin deze is opgenomen. Bijvoorbeeld: als u een tekst opneemt in A10-cel, wordt die tekst de volgende keer dat u die macro in een andere werkmap gebruikt, die tekst in A10 geplaatst.

Beschouw ons voorbeeld van het urenregistratieformulier. We willen altijd dat de titel op de eerste rij van elk blad staat. We willen de celverwijzing niet wijzigen wanneer deze naar andere bladen of werkmappen wordt gekopieerd. In dat geval is Absolute Cell Referencing handig.

Relatieve celverwijzing: Stel dat u de stappen op verschillende plaatsen in het werkblad moet herhalen. Relatieve verwijzingen zijn handig wanneer u dezelfde berekening of stappen over meerdere rijen of kolommen moet herhalen.

Voorbeeld: Stel dat u een Excel-blad heeft met de volledige namen, telefoonnummers en de geboortedatums van 1000 werknemers. (Formaat is zoals hieronder getoond)

Uw manager verwacht dat u:

SQL scenario gebaseerde vragen en antwoorden

1. Scheid voornaam en achternaam.
2. Voeg Landcode Voorbeeld (+91) toe aan het telefoonnummer.
3. Toon DOB in de vorm van dd-mon-jj, Voorbeeld: 10 januari 87.

Aangezien er 1000 records zijn, zou het tijd kosten om dit handmatig te doen. Dus u besluit om een ​​macro te maken. Maar het gebruik van absolute referentie lost uw probleem niet op, omdat u wilt dat het in meerdere rijen en kolommen werkt. In dit geval is relatieve verwijzing handig.

Neem Excel-macro op met behulp van relatieve referentie

Om op te nemen met behulp van een relatieve referentie, selecteert u eerst de cel waarin u de opname wilt starten.

Ga naar ontwikkelaar -> klik op Relatieve referentie gebruiken -> Macro opnemen ​Neem alles op wat je maar wilt en druk op stop met opnemen.

Volg deze stappen voor het bovenstaande voorbeeld.

1. Eerst moeten we een kolom invoegen naast Emp FullName en de kolomkop wijzigen in FirstName en LastName.
2. Selecteer B2 cel-> Ga naar ontwikkelaar -> Gebruik relatieve referentie -> Record macro ​
3. Gebruik tekstscheidingsteken om voornaam en achternaam te scheiden. Zodra u klaar bent, stopt u met opnemen.
4. Maak op dezelfde manier nog twee macro's voor telefoonnummer en geboortedatum.
5. Sla het bestand op.
6. Om uit te voeren, selecteert u alle Emp FullName, d.w.z. B3 tot het laatste emp dat B1001 is en voert u de 1 uit^stMacro.
7. Volg vergelijkbare stappen voor telefoonnummer en geboortedatum. De resulterende Excel wordt hieronder weergegeven.

Bijgevoegd bestand ter referentie

Veel Gestelde Vragen

V # 1) Wat is een voorbeeld van macro's in Excel?

Antwoord: Een macro is een reeks acties die u kunt uitvoeren om de gewenste taak uit te voeren.

Stel dat u elke maand een rapport maakt dat de gebruikersaccounts moet markeren met het achterstallige bedrag in vet en rood. U kunt een macro maken en uitvoeren die deze opmaakwijzigingen elke keer dat u wilt met slechts een enkele klik toepast.

Vraag 2) Waar zijn macro's in Excel?

Antwoord: Alle opgenomen macro's zijn beschikbaar onder Ontwikkelaarstabblad -> Macro's

Als u geen persoonlijke macro kunt vinden, gaat u naar Bekijken -> zichtbaar maken ​

V # 3) Wat zijn de soorten celverwijzingen in Excel?

Antwoord:

• Absoluut: Absolute verwijzingen zullen altijd verwijzen naar de specifieke cel waarin deze is opgenomen. Bijvoorbeeld, als u een tekst in cel D10 opneemt, wijst elke keer dat de macro wordt gebruikt altijd naar D10.
• Familielid: Deze zijn handig wanneer u dezelfde berekening of stappen over meerdere rijen of kolommen moet herhalen.

V # 4) Hoe sla ik een macro in Excel op in alle werkmappen?

Antwoord: Selecteer tijdens het opnemen van een macro Persoonlijke macro-werkmap onder macro opslaan in, hierdoor is uw macro beschikbaar voor alle werkmappen. Zie je de optie nog steeds niet, ga dan naar Bekijken -> zichtbaar maken ​

Gevolgtrekking

In deze tutorial hebben we Excel-macro's geleerd die ons helpen bij het automatiseren van de routinetaken in Excel.

We hebben gezien wat een macro is? Hoe u de macro kunt weergeven in Excel. We hebben ook onderzocht hoe een macro kan worden opgenomen met behulp van absolute en relatieve celverwijzingen met voorbeelden.

​ Lees de Easy VBA-trainingsserie door

Aanbevolen literatuur

• Excel VBA-zelfstudie - Inleiding tot VBA in Excel
• VBA-gegevenstypen - numerieke en niet-numerieke gegevenstypen in VBA
• 35+ meest voorkomende vragen en antwoorden voor Microsoft Excel-sollicitatiegesprekken
• Herhaalde QA-taken automatiseren met Excel-macro's (voorbeelden)
• Hoe XML-bestand te openen in Excel, Chrome en MS Word
• Gegevens lezen of schrijven vanuit Excel-blad in Selenium Web Driver
• Top 5 dingen die een tester moet hebben om te Excel (en de veranderende perspectieven van de softwaretester)
• Werken met VBScript Excel-objecten