16 Macro’s

  • Uitleg macro’s en het gebruik ervan.
  • Activeren tabblad Ontwikkelaars.
  • Opnemen van een macro met de macrorecorder.
  • Wijzigen van een macro.
  • Opdrachtknop maken.
  • Beveiligingsinstellingen.

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 Taak: 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.

Tab ontwikkelaars.

Figuur 16.1: Tab ontwikkelaars.

  1. Kies Bestand > Opties > Lint aanpassen.
Opties Excel voor weergeven tabblad ontwikkelaars.

Figuur 16.2: Opties Excel voor weergeven tabblad ontwikkelaars.

  1. 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’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.

Dialoogvenster Macro opnemen.

Figuur 16.3: Dialoogvenster Macro opnemen.

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.3 Taak: 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.

Macro opnemen

  1. Begin met een nieuwe werkmap en voer in een willekeurige cel een tekst in.

  2. Selecteer de cel met de tekst.

  3. Kies tab Ontwikkelaars > Macro opnemen (groep Programmacode).

  4. Geef in het dialoogvenster de naam voor de macro en voeg een beschrijving toe.

Eigenschappen macro Celopmaak.

Figuur 16.4: Eigenschappen macro Celopmaak.

  1. Klik op OK.

  2. 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
  3. Kies tab Start > Opmaak (groep Cellen) > Kolombreedte AutoAanpassen.

  4. Kies tab Ontwikkelaars > Opname stoppen (groep Programmacode)

Knop om te stoppen met het opnemen van de macro.

Figuur 16.5: Knop om te stoppen met het opnemen van de macro.

Nu kan de macro getest worden op de juiste werking.

Macro testen

  1. Voer in een andere cel een tekst in en selecteer deze cel.

  2. Kies tab Ontwikkelaars > Macro’s (groep Programmacode).

  3. Selecteer macro Celopmaak en klik Uitvoeren.

Dialoogvenster macro celopmaak.

Figuur 16.6: Dialoogvenster macro celopmaak.

  1. Controleer of de cel correct is opgemaakt.

  2. Sla de werkmap op als Excel-werkmap met macro’s (*.xslm) op onder de naam Celopmaak.xlsm.

Opslaan van het werkblad met macro's.

Figuur 16.7: Opslaan van het werkblad met macro’s.

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.4 Taak: Wijzigen van een macro

Om deze taak uit te kunnen voeren is het nodig dat de macro [Celopmaak]{varname} in 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.
  1. Open indien nodig het bestand Celopmaak.xlsm.

  2. Kies tab Ontwikkelaars > Macro’s (groep Programmacode).

Je krijgt nu weer het dialoogvenster met beschikbare macro’s, zie figuur 16.6

  1. Selecteer macro Celopmaak en kies Opties.
Opties voor macro celopmaak.

Figuur 16.8: Opties voor macro celopmaak.

  1. Zet in het vakje voor de sneltoets de letter t en wijzig in de beschrijving 16pt in 18pt. Klik daarna op OK.

  2. 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
  1. Wijzig dit op beide plaatsen in 18.

  2. Sluit het venster Microsoft Visual Basic via de sluitknop in de rechterbovenhoek.

  3. Test de macro op een cel met tekst via de sneltoets Ctrl t.

16.5 Taak: Opdrachtknop maken

Je kunt een macro op een van de volgende manieren uitvoeren:

  • Via het menu tab Ontwikkelaars > Macro’s > macro selecteren > Uitvoeren, zie 16.3.
  • Via een sneltoets wanneer er eentje aan de macro is toegewezen, zie 16.4
  • Via een opdrachtknop voor de macro.

In deze taak wordt voor de eerder gemaakte macro Celopmaak een opdrachtknop op het werkblad gemaakt.

  1. Open indien nodig het bestand Celopmaak.xlsm.

  2. Kies tab Ontwikkelaars > Invoegen (groep Besturingselementen).

  3. Klik op het besturingselement Knop (formulierbesturingselement).

Besturingselementen, de gemarkeerde is de Knop (formulierbesturingselement).

Figuur 16.9: Besturingselementen, de gemarkeerde is de Knop (formulierbesturingselement).

De cursor verandert nu in een + waarmee je een rechthoek voor de knop op het werkblad kunt tekenen.

  1. 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.
Macro celopmaak koppelen aan knop.

Figuur 16.10: Macro celopmaak koppelen aan knop.

  1. Selecteer macro Celopmaak en klik op OK.

De getekende rechthoek verandert in een knop met daarop een tekst als Knop 1.

  1. Selecteer de tekst op de knop en wijzig deze in een toepasselijker tekst, bijvoorbeeld Cel opmaken.
Opdrachtknop, links de standaardknop, rechts met de gewijzigde tekst op de knop.

Figuur 16.11: Opdrachtknop, links de standaardknop, rechts met de gewijzigde tekst op de knop.

  1. Hef de selectie van de knop op door op een willekeurige plek op het werkblad te klikken.

  2. Test de werking van de macro op een cel met tekst via de opdrachtknop.

16.6 Beveiliging macro’s

Een overzicht van de beveiligingsinstellingen en hoe deze te wijzigen.

Instellingen wijzigen

Wanneer je de beveiligingsinstellingen van macro’s wilt bekijken of wijzigen dan moet je klikken op tab Ontwikkelaars > Macrobeveiliging (groep Programmacode).

Opdrachtknop macrobeveiling op het lint.

Figuur 16.12: Opdrachtknop macrobeveiling op het lint.

Dan verschijnt het dialoogvenster Vertrouwenscentrum.

Macro instellingen in het vertrouwenscentrum.

Figuur 16.13: Macro instellingen in het vertrouwenscentrum.

Tabel 16.1: Overzicht Macro instellingen.
Instelling Toelichting
Alle macro’s uitschakelen, zonder melding Kies deze optie wanneer je macro’s niet vertrouwt. Alle macro’s in documenten worden uitgeschakeld, evenals de beveiligingswaarschuwingen over macro’s. Documenten die niet-ondertekende macro’s bevatten die je wel vertrouwt, kun je opslaan op een vertrouwde locatie. Documenten op vertrouwde locaties worden uitgevoerd zonder controle door het beveiligingssysteem.
Alle macro’s uitschakelen, met melding Dit is de standaardinstelling. Kies deze optie wanneer je macro’s wilt uitschakelen, maar wel beveiligingswaarschuwingen wilt ontvangen als er macro’s aanwezig zijn. Op deze manier kun je per macro kiezen of je de desbetreffende macro wilt inschakelen.
Alle macro’s uitschakelen, behalve digitaal ondertekende De maker van een macro kan deze digitaal ondertekenen. Wanneer je deze als een vertrouwde instantie aanmerkt dan wordt de macro zonder waarschuwingen uitgevoerd. Alle niet-ondertekende macro´s worden zonder melding uitgeschakeld.
Alle macro’s inschakelen Kies deze optie wanneer je alle macro’s wilt uitvoeren. Deze optie wordt niet aanbevolen want de computer wordt hierdoor kwetsbaar schadelijke macro’s.

16.7 Tips

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.

Relatieve verwijzingen in macro's.

Figuur 16.14: Relatieve verwijzingen in macro’s.

Deze instelling blijft actief totdat je de instelling ongedaan maakt door weer op deze knop te klikken.

Macro’s verwijderen

Macro’s kunnen verwijderd worden via tab Ontwikkelaars > Macro’s (groep Programmacode) > Macro selecteren > Verwijderen.

16.8 Opgaven

macr001 - Achtergrondkleur

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 Sub

macr002 - Procentuele opmaak

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 Sub

macr003 - Opmaak cel A1

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 Sub

macr004 - Rasterlijnen afdrukken

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