16 Macro’s
Een taak die je in Microsoft Excel regelmatig moet uitvoeren kun je met een macro automatiseren. Een macro bestaat uit een reeks opdrachten en functies die door één commando of druk op een knop achter elkaar worden uitgevoerd.
Een macro is dus vooral bedoeld om te gebruiken voor veel voorkomende handelingen of voor complexe handelingen. Door van een macro gebruik te maken kan de efficiency verhoogd en de kans op fouten verminderd worden.
Eenvoudige macro’s welke uit een aantal achter elkaar uitgevoerde handelingen bestaan, kunnen met de ingebouwde macrorecorder worden opgenomen. Complexe macro’s worden geprogrammeerd in de programmeertaal VBA. Alleen eenvoudige macro’s komen hier aan bod, hiervoor heb je geen programmeerkennis nodig.
De taal waarin de Excel macro’s geschreven worden is Visual Basic for Applications (VBA). De opdrachten en functies van de macro worden in een Visual Basic module opgeslagen. En met de Visual Basic editor kun je macro’s bewerken, macro’s van de ene module naar de andere kopiëren, macro’s tussen verschillende werkmappen kopiëren en de modules en macro’s een andere naam een andere naam geven.
16.1 Tab Ontwikkelaars
Om macro’s op te kunnen nemen, te bewerken en uit te voeren moet het tabblad Ontwikkelaars zichtbaar gemaakt worden.
Een aantal functies van Excel, waaronder die voor Macro’s en VBA, zitten onder het tabblad Ontwikkelaars. Dit tabblad wordt bij de installatie van Excel standaard niet getoond en zal dus eerst zichtbaar gemaakt moeten worden. Dit is een eenmalige actie.
Controleer eerst of op het lint de tab Ontwikkelaars aanwezig is. Zoja, dan kun je deze taak overslaan.
Taak 16.1
- Kies Bestand > Opties > Lint aanpassen.
- Selecteer onder Hoofdtabbladen Ontwikkelaars en klik op OK.
Op het lint moet nu het tabblad Ontwikkelaars (zie Figuur 16.1) zichtbaar zijn.
16.2 Macro-beveiligingsinstellingen
Macro’s kunnen worden gebruikt voor kwaadaardige doeleinden, zoals het installeren van een virus. Dit kan worden geblokkeerd met de beveiligingsinstellingen. Echter als de instellingen te hoog staan, kun je geen macro’s uitvoeren en als ze te laag staan ben je niet beschermd. Geen van beide is een goede optie. Een goed compromis is de instelling die je steeds de mogelijkheid geeft om te beslissen of je macro’s wilt toestaan of niet. Dit is ook de standaardinstelling.
Instellingen wijzigen
Wanneer je de beveiligingsinstellingen van macro’s wilt bekijken of wijzigen dan moet je klikken op tab Ontwikkelaars > Macrobeveiliging (groep Programmacode).
Dan verschijnt het dialoogvenster Vertrouwenscentrum.
| Instelling | Toelichting |
|---|---|
| Macro’s zonder meldingen uitschakelen | Kies deze optie wanneer je macro’s niet wilt toestaan. Alle macro’s in documenten worden uitgeschakeld, evenals de beveiligingswaarschuwingen over macro’s. Bestanden die macro’s bevatten die je wel vertrouwt, kun je eventueel opslaan op een vertrouwde locatie. Bestanden op vertrouwde locaties worden uitgevoerd zonder controle door het beveiligingssysteem. |
| Macro’s met meldingen uitschakelen | Dit is de standaardinstelling en de beste keuze. Met deze instelling krijg je een melding waarbij je kunt kiezen om macro’s macro’s wel of niet uit te voeren. |
| Macro’s uitschakelen, behalve digitaal ondertekende | De maker van een macro kan deze digitaal ondertekenen. Wanneer je deze maker dan als een vertrouwde instantie aanmerkt dan wordt de macro zonder waarschuwingen uitgevoerd. Alle niet-ondertekende macro´s worden zonder melding uitgeschakeld. |
| Macro’s inschakelen | Kies deze optie wanneer je alle macro’s wilt uitvoeren. Deze optie wordt niet aanbevolen want hierdoor ben je niet meer beschermd tegen schadelijke macro’s. |
16.3 Macro’s opnemen
Eenvoudige macro’s kunnen gemaakt worden door de handelingen op te nemen met de ingebouwde macrorecorder. Na het starten van de recorder worden alle handelingen opgenomen en op de achtergrond omgezet in VBA code. Dit gaat door totdat de recorder gestopt wordt.
Om een goed werkende macro te krijgen is het belangrijk om eerst de te verrichten handelingen zonder opname uit te voeren en in volgorde te noteren. Dit script kun je daarna gebruiken om de handelingen met de macrorecorder op te nemen.
De knop waarmee je het opnemen van macro’s kunt starten zit op de tab Ontwikkelaars in de groep Programmacode, zie Figuur 16.1.
Wanneer op deze knop geklikt wordt om de opname van een macro te starten verschijnt eerst een dialoogvenster waarin een aantal eigenschappen van de macro kunnen worden ingevuld.
Macronaam
Elke macro moet verplicht een naam hebben. Standaard vult Excel hiervoor in de tekst Macro gevolgd door een cijfer. Deze naam kun je wijzigen door hiervoor een andere naam in te typen.
Het eerste teken van de macronaam moet verplicht een letter zijn. Daarna kun je andere tekens gebruiken zoals letters, cijfers of het onderstrepingsteken. Spaties zijn in een macronaam niet toegestaan. Geef macro’s altijd passende namen waaruit blijkt wat de macro gaat doen. Het onderstrepingsteken kan als scheidingsteken tussen woorden gebruikt worden.
Sneltoets
Macro’s kun je starten via het menu, maar desgewenst ook via zogenaamde sneltoetsen. Dit zijn toetscombinaties van de CTRL toets met een lettertoets. Wanneer je aan de macro een sneltoets wilt toewijzen dan kun je de te gebruiken letter in het vak typen. Zet je daar bijvoorbeeld de letter A in, dan is de toetscombinatie CTRL A de sneltoets waarmee je de macro kunt starten.
Het is niet verplicht om aan een macro een sneltoets toe te wijzen, maar voor veel gebruikte macro’s kan dit wel handig zijn.
Macro opslaan in
De plekken waar een macro kan worden opgeslagen zijn:
- Deze werkmap
- Nieuwe werkmap
- Persoonlijke Macrowerkmap
Het is wel belangrijk om hier van te voren goed over na te denken. Wanneer de macro alleen in de actieve werkmap gebruikt gaat worden, dan kies je voor Deze werkmap. Moet de macro ook in andere werkmappen gebruikt worden, dus eigenlijk altijd beschikbaar zijn, dan is Persoonlijke Macrowerkmap de beste keuze. De keuze Nieuwe werkmap wordt eigenlijk nooit gebruikt.
Je moet de opslagplek altijd specificeren. De standaardwaarde is Deze werkmap.
Beschrijving
Het is aan te bevelen om een korte beschrijving te geven van wat de macro doet. Bij het maken van de macro weet je dit wel, maar enige tijd later ben je het misschien vergeten. Het is niet verplicht om een beschrijving op te geven.
Alle gemaakte keuzes kunnen in een later stadium altijd gewijzigd worden.
16.4 Macro Celopmaak
Informatiebehoefte
Maak een macro die een geselecteerde cel opmaakt met het lettertype Comic Sans MS, vet, cursief, 16 pt en rood. Verder moet de kolombreedte aangepast worden aan de breedte van de tekst in de cel. De naam van de macro moet worden Celopmaak.
Taak 16.2 Macro opnemen
Begin met een nieuwe werkmap en voer in een willekeurige cel een tekst in.
Selecteer de cel met de tekst.
Kies tab Ontwikkelaars > Macro opnemen (groep Programmacode).
Geef in het dialoogvenster de naam voor de macro en voeg een beschrijving toe.
Klik op OK.
Voer de volgende acties uit op de geselecteerde cel: Rechter muisklik > Celeigenschappen > tab Lettertype en stel het volgende in:
- Lettertype: Comic Sans MS
- Tekenstijl: Vet en cursief
- Punten: 16
- Kleur: Rood
Kies tab Start > Opmaak (groep Cellen) > Kolombreedte AutoAanpassen.
Kies tab Ontwikkelaars > Opname stoppen (groep Programmacode)
Nu kan de macro getest worden op de juiste werking.
Taak 16.3 Macro testen
Voer in een andere cel een tekst in en selecteer deze cel.
Kies tab Ontwikkelaars > Macro’s (groep Programmacode).
Selecteer macro Celopmaak en klik Uitvoeren.
Controleer of de cel correct is opgemaakt.
Sla de werkmap op als Excel-werkmap met macro’s (*.xslm) op onder de naam
Celopmaak.xlsm.
Wanneer je een werkmap met macro’s wilt opslaan, dan probeert Excel deze standaard op te slaan als een Excel-werkmap met de extensie .xlsx. Echter in dit formaat kunnen geen macro’s worden opgeslagen. Je moet dit zelf wijzigen in een Excel-werkmap met macro’s met de extensie .xlsm.
16.5 Wijzigen van een macro
Om deze taak uit te kunnen voeren is het nodig dat de macro [Celopmaak]{varname} in Taak 16.3 gemaakt is.
Een eenmaal gemaakte macro kan gewijzigd worden zonder de macro weer helemaal opnieuw op te nemen. Zowel de opties van de macro als de programmacode kunnen bekeken en gewijzigd worden.
Informatiebehoefte
Breng de volgende wijzigingen aan in de macro Celopmaak:
- Ken sneltoets Control t toe.
- Wijzig lettergrootte in 18 pt.
Taak 16.4 Ga verder met het bestand uit Taak 16.3.
Open indien nodig het bestand.
Kies tab Ontwikkelaars > Macro’s (groep Programmacode).
Je krijgt nu weer het dialoogvenster met beschikbare macro’s, zie Figuur 16.8.
- Selecteer macro Celopmaak en kies Opties.
Zet in het vakje voor de sneltoets de letter
ten wijzig in de beschrijving 16pt in 18pt. Klik daarna op OK.Kies nu in het dialoogvenster voor Bewerken.
Een nieuw venster Microsoft Visual Basic verschijnt nu met in het codescherm de programmacode van de macro. In deze code kom je op twee plaatsen tegen dat de tekstgrootte 16 wordt:
Maakt cel op in Comic Sans, 16pt, vet, cursief en rood.Size = 16
Wijzig dit op beide plaatsen in
18.Sluit het venster Microsoft Visual Basic via de sluitknop in de rechterbovenhoek.
Test de macro op een cel met tekst via de sneltoets Ctrl t.
16.6 Opdrachtknop maken
Je kunt een macro op een van de volgende manieren uitvoeren:
- Via het menu tab Ontwikkelaars > Macro’s > macro selecteren > Uitvoeren.
- Via een sneltoets wanneer er eentje aan de macro is toegewezen.
- Via een opdrachtknop voor de macro.
In deze taak wordt voor de eerder gemaakte macro Celopmaak een opdrachtknop op het werkblad gemaakt.
Taak 16.5 Ga verder met het bestand uit Taak 16.4.
Open indien nodig het bestand.
Kies tab Ontwikkelaars > Invoegen (groep Besturingselementen).
Klik op het besturingselement Knop (formulierbesturingselement).
De cursor verandert nu in een + waarmee je een rechthoek voor de knop op het werkblad kunt tekenen.
- Druk de linkermuisknop in en teken hiermee ergens op het werkblad een rechthoek voor de gewenste afmetingen van de knop. Laat daarna de linkermuisknop los. Het dialoogvenster Macro toewijzen aan object verschijnt dan.
- Selecteer macro Celopmaak en klik op OK.
De getekende rechthoek verandert in een knop met daarop een tekst als Knop 1.
- Selecteer de tekst op de knop en wijzig deze in een toepasselijker tekst, bijvoorbeeld
Cel opmaken.
Hef de selectie van de knop op door op een willekeurige plek op het werkblad te klikken.
Test de werking van de macro op een cel met tekst via de opdrachtknop.
16.7 Tips
16.7.1 Relatieve verwijzingen
In opgenomen macro’s wordt standaard gebruikgemaakt van absolute celverwijzingen. Dit houdt in dat de exacte locaties van cellen, rijen en kolommen in de programmacode van de macro worden opgenomen. De macro werkt dan altijd op dezelfde adressen.
Wanneer je relatieve verwijzingen in de macro wilt gebruiken dan moet je klikken op de knop Relatieve verwijzingen.
Deze instelling blijft actief totdat je de instelling ongedaan maakt door weer op deze knop te klikken.
16.7.2 Macro’s verwijderen
Macro’s kunnen verwijderd worden via tab Ontwikkelaars > Macro’s (groep Programmacode) > Macro selecteren > Verwijderen.
16.8 Opgaven
Oefening 16.1 Achtergrondkleur (macr001)
Neem een macro op die de actieve cel een grijze achtergrondkleur geeft.
Een mogelijke oplossing.
Sub Achtergrond_Grijs()
'
' Lichtgrijze achtergrond voor cel
'
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
.PatternTintAndShade = 0
End With
End SubOefening 16.2 Procentuele opmaak (macr002)
Neem een macro op met de naam ProcentOpmaak die een getal opmaakt als procenten met 1 decimaal en het getal in de cel centreert.
Een mogelijke oplossing.
Sub Procentopmaak()
'
' Getal als percentage met 1 dec. en horizontaal gecentreerd
'
Selection.NumberFormat = "0.0%"
With Selection
.HorizontalAlignment = xlCenter
End With
End SubOefening 16.3 Opmaak cel A1 (macr003)
Neem een macro op die het volgende uitvoert:
- Rijhoogte rij 1 wordt 18
- Kolombreedte van de eerste kolom wordt 3
- Tekst in cel A1 wordt Tahoma 14, vet, blauwe kleur
Een mogelijke oplossing.
Sub OpmaakA1()
'
' opgave macr003
'
Rows("1:1").Select
Selection.RowHeight = 18
Columns("A:A").Select
Selection.ColumnWidth = 3
Range("A1").Select
With Selection.Font
.Name = "Tahoma"
.Size = 14
.Bold = True
.Color = -4165632
End With
End SubOefening 16.4 Rasterlijnen afdrukken (macr004)
Neem een macro op die er voor zorgt dat bij het afdrukken ook de rasterlijnen worden afgedrukt.
Deze eigenschap is te vinden bij tab Pagina-indeling > Pagina-instelling > tab Blad.
Een mogelijke oplossing.
Sub Print_Rasterlijnen()
'
' opgave macr004
'
With ActiveSheet.PageSetup
.PrintGridlines = True
End With
End Sub