Wat zijn macro's?
Macro's zijn stukjes programmeertaal waarmee je acties in de Office-pakketten van Microsoft kunt automatiseren. Met de zelf in te typen (of op te nemen) code kun je acties keer op keer weer snel laten uitvoeren.
Waarvoor gebruik je macro's?
Met macro's kun je bijvoorbeeld:
- lange lijsten controleren op de juiste codering
- lange lijsten met elkaar vergelijken (wat heeft de ene lijst wel en de andere niet)
- Mailtjes sturen op basis van een gefilterde lijst in Excel
- Een Tombola opzetten
- Alle formules uit een bestand in een apart werkblad zetten
- Draaitabellen vernieuwen zodra je het werkblad opent
- Alle Excelbestanden op je computer in een lijst weergeven
- Complete "programma's" in Excel maken zonder het werkblad te tonen
- enz. enz.
Wat moet je voorbereiden?
Voordat je met macro's gaat werken moet je even goed nadenken over je instellingen in Excel. Via Bestand > Opties > Vertrouwenscentrum > Instellingen voor het Vertrouwenscentrum > Macro-instellingen kun je aangeven of je macro's wilt toestaan in Excel of niet. Alleen bij de optie "Alle macro's uitschakelen zonder melding" zal je macro zeker niet werken. Bij de andere opties is het de vraag of je gewaarschuwd wordt of niet. Vink ook het onderste vakje aan.
Geef bij "Vertrouwde locaties" de map aan waarin jij je Excel-bestanden met macro's op gaat slaan.
Daarnaast is het handig om het Ontwikkelaars-lint zichtbaar te maken (Bestand > Opties > Lint aanpassen. De optie Ontwikkelaars staat aan de rechterkant maar standaard staat dit niet aangevinkt).
Geef bij "Vertrouwde locaties" de map aan waarin jij je Excel-bestanden met macro's op gaat slaan.
Daarnaast is het handig om het Ontwikkelaars-lint zichtbaar te maken (Bestand > Opties > Lint aanpassen. De optie Ontwikkelaars staat aan de rechterkant maar standaard staat dit niet aangevinkt).
Als je het hebt aangevinkt krijg je een extra lint. Het linkergedeelte wordt vooral veel gebruikt en daarin staan de buttons waarvan enkele in dit blog behandeld worden.
En als je het bestand dat je maakt met macro's wilt opslaan, denk er dan even aan dat je het bestand opslaat als "Excel werkmap met macro's (*.xlsm)" anders blijven de gemaakte macro's niet bewaard.
Visual Basic
De code die je gaat schrijven om een macro automatisch iets te laten uitvoeren wordt geschreven in de taal VBA wat staat voor Visual Basic for Applications. Met deze taal kun je in alle Office-pakketten programmeren al zijn er wel verschillen per pakket. In Word kun je niet naar een bepaalde cel springen, in Excel kun je geen afspraak plannen etc.
Als je op de button Visual Basic klikt, dan krijg je een nieuw scherm te zien. Je kunt switchen tussen dit nieuwe VBA-scherm en het Excel-scherm. Op het VBA-scherm zie je het volgende:
Als je aan de linkerkant niet het vak ziet met de tekst "Project", dan kun je dit aanzetten via Beeld > Projectverkenner. Dit vak toont je de bestandsnaam van het Excel-bestand met het mapje "Microsoft Excel objecten en daaronder de werkbladen die in het bestand zitten en een "ThisWorkbook".
Onder het Projectenvenster staat het venster Eigenschappen dat je via Beeld > Venster Eigenschappen aan kunt zetten. Daarin kun je eigenschappen van een werkblad of een "Userform" (je eigen gemaakte pop-up-scherm) instellen.
Bovenaan staat standaard het lint "Standaard" aan en het is raadzaam om ook het lint "Foutopsporing" aan te zetten.
Daarnaast zijn er nog meer schermen in te stellen maar voor nu is dit voldoende.
Je mag dit scherm sluiten via Bestand > Sluiten en terugkeren naar Excel of via het kruisje rechtsboven. Je mag ook via Alt-Tab switchen naar Excel.
Daarnaast zijn er nog meer schermen in te stellen maar voor nu is dit voldoende.
Je mag dit scherm sluiten via Bestand > Sluiten en terugkeren naar Excel of via het kruisje rechtsboven. Je mag ook via Alt-Tab switchen naar Excel.
Voordat je de macro gaat opnemen
Om straks een macro te kunnen maken moet je even de cijfers 1 t/m 3 invullen in de cellen A1 t/m A3. Selecteer daarna cel A4.
Macro opnemen
Je gaat je eerste macro opnemen. Als je in het lint klikt op "Macro opnemen", dan verschijnt het volgende scherm:
Je kunt de macro alvast een naam geven en het is handig om aan te geven wat de macro doet. Zo kun je, als je veel macro's in een bestand maakt snel de juiste macro terugvinden én kun je in een macro eenvoudig verwijzen naar een andere macro. Er mogen geen spaties in de naam zitten.
Je kunt bij Sneltoets een sneltoets aanmaken waarmee je snel je macro kunt starten zonder dat je macro uit een lijst moet opvragen. Denk er om dat je ook de Shift-toets er bij gaat gebruiken want als je bijvoorbeeld de macro opslaat onder Cntrl+C dan werkt de sneltoets voor het kopiëren in dit Excel-bestand niet meer.In het veld "Macro opslaan in" staat standaard "Deze werkmap" ingevuld. Er zijn nog twee opties, maar dat slaan we in deze beginnersles even over.
In het onderste vak kun je een uitgebreidere beschrijving geven van de macro (maar meestal is dit veld niet gevuld).
Als je op OK klikt, verdwijnt dit scherm maar zie je boven in het lint dat de knop "Macro opnemen"is gewijzigd in de knop "Opname stoppen".
Klik op de toetscombinatie Alt+= waarmee je de ingevulde cijfers optelt en klik op Enter. Waarschijnlijk wordt nu cel A5 geselecteerd. Selecteer cel A4 en geef de cel via het Start-lint een gele achtergrond. Ga terug naar het Ontwikkelaarslint en klik op "Opname stoppen".
Klik nu op de button Visual Basic en je ziet in de Projectverkenner dat er onder "ThisWorkbook" een map "Modules" is bijgekomen en als je op het plusje er voor klikt, wordt de module "Module1" zichtbaar.
Als je 1x op "Module1" klikt, zie je dat de inhoudt van het Eigenschappenvenster wijzigt. Er staat 1 optie "Name" met daarachter de tekst "Module1". In dit vak kun je de naam van de Module aanpassen. Zo kun je een soort mappenstructuur aanmaken met meerdere modules voor als je veel macro's gaat maken.Als je dubbel op "Module1"klikt dan verschijnt aan de rechterkant een venster met je eerste macro wat er waarschijnlijk als volgt uit ziet (niet schrikken als je toch iets anders hebt):
Mijn macro begint met "Sub SomA1A3()". Een opgenomen macro begint altijd met Sub met daarachter de tekst die je hebt ingevuld bij Macronaam en het wordt afgesloten met twee haakjes. Voor de experts: Er is ook nog een "Private Sub" (verborgen macro), "Public Sub" (te gebruiken in meerdere bestanden) en "Function" waarmee je je eigen formules kunt maken.
Onderaan staat altijd een "End Sub" waarmee de macro wordt afgesloten. Daar tussenin staat precies wat de macro doet.
De eerste vijf regels beginnen met een apostrof en zijn bij mij groen gekleurd. De apostrof geeft aan dat alles wat er achter staat als tekst moet worden gezien en dit is ter info. Zie je geen groene kleur, ga dan in het lint naar Extra > Opties > Opmaak in editor en selecteer de optie "Tekst opmerking" en wijzig daarbij de voorgrondkleur in donkergroen (3e kleur van boven) en klik op Ok.
In de tekst wordt de naam van de macro vermeld (vind ik niet echt zinvol) en de ingestelde Sneltoets.
Daaronder begint de echte macro.
Macrocode lezen
De eerste actie die je hebt gedaan nadat je de macro hebt gestart was in cel A4 de Som-formule te plaatsen via de toetscombinatie Alt+=. Dat staat in de code op de 2e regel. Op de eerste regel staat dat Excel de plak-functie uitschakelt. Stel je hebt iets gekopieerd, dan kun je dit in meerdere cellen plakken. Zodra je dit doet, zet Excel de CutCopy-mode aan. Maar zodra je in een cel een nieuwe formule maakt, dan is het plakken afgelopen en dat is wat Excel zelf op de eerste regel zet. Hiermee zie je dat Excel zelf regels toevoegt aan de code en soms is dit helemaal niet nodig. Zou je deze eerste regel verwijderen en de macro opnieuw uitvoeren, dan werkt het nog steeds (zie verderop). Typ voor deze regel een apostrof zodat deze regel niet uitgevoerd wordt.
Op de tweede regel staat "ActiveCell", wat inhoudt dat er iets staat te gebeuren in de cel die geselecteerd is. Daarachter staat ".FormulaR1C1" wat aangeeft dat er in de actieve cel een relatieve SOM-formule wordt geplaatst die de rijen drie hoger in de huidige kolom (R[-3]C) tot en met de rij die 1 hoger is in de huidige kolom (R[-1]C) bij elkaar optelt.
Doordat ik de formule met Enter afsloot selecteerde Excel bij mij automatisch cel A5 (kan ook uitgeschakeld worden). De volgende regel, Range("A4").Select, geeft aan dat ik cel A4 weer geselecteerd heb.Daaronder komen meerdere regels die beginnen met With en eindigen met End With. Dit stuk code is aangemaakt door Excel toen ik de cel een gele achtergrond gaf. Je kunt lezen dat ik de cel geen Patroonstijl heb meegegeven (arcering van de kleur), dat ik de Patroonkleur op Automatisch heb staan, dat ik kleurnummer 65535 heb gebruikt (geel) en dat ik geen opvuleffecten heb gebruikt. Al deze instellingen staan bij Celopmaak op tabblad Opvulling. Excel vult dus ook deze instellingen in terwijl ik alleen maar de kleur geel heb gekozen. Zet voor de regels tussen With en End With ook een apostrof behalve voor de Color-regel.
Mijn aangepaste code ziet er nu als volgt uit:
Macro opnieuw uitvoeren
Het grote voordeel van macro's is dat je één keer een code moet maken en dat je dit steeds opnieuw kunt gebruiken. Wel is het van belang om te weten wat er gebeurd en waar je in Excel de macro uit voert.
Ga naar het Excel-scherm. Verwijder rij 4 waardoor de som-formule en de achtergrondkleur verdwijnen. Selecteer daarna cel C6.
Ga naar het VBA-scherm en maak deze kleiner zodat je de macro-code ziet maar ook het Excel-scherm.
Als je het lint Foutopsporing hebt aangezet, dan zie je in het lint een knop staan met een paar lijntjes met linksboven een omgekeerde pijl. Deze button heeft de naam Stap en daarmee kun je de macro stap voor stap uitvoeren (kan ook door steeds op de F8-toets te klikken).
Selecteer een willekeurige regel in de macro en klik op de Stap-button. Als eerste zal de bovenste regel (Sub...) geel gearceerd worden wat aangeeft dat de macro deze stap, het uitvoeren van deze macro, wil uitvoeren.
Klik nogmaals op de stap-button en je ziet dat de regels met de apostrof worden overgeslagen. Met opmerkingen doet de macro niets. Klik nogmaals op de stap-button en de regel er onder wordt geel maar ook zie je dat in cel C6 de formule =SOM(C3:C5) wordt geplaatst. De macro gaf aan dat in de actieve cel (C6) de waarden van de 3 rijen er boven tot één rij boven de actieve cel moeten worden opgeteld.Klik weer op de stap-button en je ziet dat nu cel A4 wordt geselecteerd. Klik drie keer op de stap-button en je ziet dat cel A4 een gele achtergrond krijgt.
Je hebt je eerste macro uitgevoerd. Gefeliciteerd!
Sluit het VBA-scherm. Ga ergens in een cel staan en klik in het Ontwikkel-lint op de knop Macro's.
Je ziet in het veld Macronaam de naam van de macro staan en daaronder begint een lijst met alle macro's. Omdat er nog maar één macro is gemaakt, zie je alleen dezelfde naam nog een keer. Klik je rechts op de knop Uitvoeren, dan voert Excel deze macro direct uit. Klik je op Bewerken, dan wordt het VBA-scherm geopend. Klik je op Stap dan opent het ook het VBA-scherm en selecteerd direct de eerste regel om door de macro te stappen. Bij Opties kun je zien welke sneltoets je aan de macro hebt gegeven. Let op! als je nu de sneltoets wijzigt, wordt dit niet automatisch in de macro-tekst aangepast.
Klik je op Uitvoeren, dan wordt de macro uitgevoerd. Klik je in Excel op een willekeurige cel en je klikt op de sneltoets dan wordt direct de macro uitgevoerd.
Macro aanpassing
Maar overal een optelling maken van de drie bovenliggende rijen en steeds cel A4 geel kleuren, dat wil je niet. Het zou mooier zijn als Excel elke keer alle ingevulde rijen boven de actieve cel zou optellen en de actieve cel geel zou kleuren.
En nu kom je aan bij het echte programmeren. Je zult zelf de macro moeten aanpassen om dit voor elkaar te krijgen en dat kun je leren uit boeken en op heel veel websites.
Ik leer heel veel door te zoeken op intranet op "Excel VBA" aangevuld met engelse woorden (want VBA is alleen in het engels beschikbaar) waarin ik beschrijf van ik wil. Weet je engelse woord niet, probeer het dan in het nederlands en vaak vind je wel iets waar je verder op kunt zoeken.
Verbeterde macro
In de gemaakte macro staat de regel "ActiveCell.FormulaR1C1= "=SUM(R[-3]C:R[-1]C)". Hoe pas je dit nu aan zodat het altijd alle bovenliggende ingevulde cellen gaat optellen? Dat is nog niet zo eenvoudig. Er is geen simpele formule die altijd de bovenliggende cellen optelt. Je gaat nu direct de diepte in:
Ga naar het Excel-scherm en vul de cellen E4 t/m E9 met de cijfers 1 t/m 6. Nu moet een macro in cel E10 de 6 bovenliggende rijen optellen.
De macro moet dus een optelling maken van de rij boven de actieve rij tot en met de laatst ingevulde rij boven de actieve rij.
Maar hoe leg je in een macro vast wat de rij boven de actieve rij is? Ga daarvoor in cel E10 staan. Voordat je de macro gaat opnemen klik je eerst op de button "Relatieve verwijzingen gebruiken" waardoor deze actief wordt. Klik nu op Macro Opnemen en ga met de pijltje-toetsen één rij omhoog en vul een getal of tekst in in de cel en stop de macro.
Open nu het VBA-scherm (kan ook via de toetscombinatie Alt+F11), dan zie je als eerste regel: "ActiveCell.Offset(-1,0).Range("A1").Select". Hiermee geeft VBA aan dat het ten opzichte van de actieve cell 1 rij omhoog gaat en nul kolommen naar rechts. (Positief is naar beneden en naar rechts, negatief is omhoog en naar links). En VBA geeft aan dat de cel die het geselecteerd heeft zijn nieuwe uitgangspunt is: Range("A1"). Dit laatste is niet noodzakelijk en als je de regel "ActiveCell.Offset(-1,0).Select" invult, dan zal Excel één rij omhoog gaan. Verwijder de andere regels van je macro want die ben je niet meer nodig.
Nu moet geregeld worden dat Excel het betreffende rijnummer onthoudt zodat straks berekent kan worden hoeveel rijen er bij elkaar opgeteld moeten worden. Daarvoor maken we een variabele aan. Een variabele is een naam waaraan we een getal of tekst koppelen. Dit is iets wat je niet kunt opnemen en nu moet je zelf code gaan schrijven. Zet de cursor achteraan de macroregel en klik op Enter waardoor er een nieuwe blanco regel ontstaat. Voer nu de volgende tekst in:MijnLaatsteRij = Activecell.Row
Daarmee geef je aan dat variabele MijnLaatsteRij het rijnummer (9) van de actieve cel krijgt.
Cel E9 is nog geselecteerd (of selecteer het), neem een nieuwe macro op en als deze gestart is, klik dan achter elkaar de End-toets en daarna de pijl-omhoog-toets aan en stop de macro. Cel E4 is nu geselecteerd. Open je het VBA-scherm dan zie je dat de macroregel "Selection.End(xlUp).Select" is aangemaakt. Dat is dus de code om naar de bovenste rij van een reeks ingevulde cellen te gaan. Voeg deze macro-regel toe aan de andere twee die je al gemaakt hebt. Het rijnummer van deze cel moet ook onthouden worden door de macro dus onder deze regel maak je zelf de volgende regel aan:
MijnEersteRij = Activecell.Row
Nu kun je de macro zelf laten uitrekenen hoeveel rijen er opgeteld moeten worden want het aantal rijen is gelijk aan MijnLaatsteRij - MijnEersteRij+1. De uitkomst geef je aan een nieuwe variabele en je voegt daarvoor de volgende regel toe:
MijnAantalRijen = MijnLaatsteRij - MijnEersteRij+1.
De Som-formule moet een optelling maken van de rij boven de actieve cel tot en met het aantal rijen dat staat vermeld bij MijnAantalRijen. Maar bij Activecell.Offset wordt een cel boven de actieve cel aangegeven met -1 en daarom moeten we MijnAantalRijen vermenigvuldigen met -1. Dat kun je doen door dezelfde variabele een andere waarde te geven. Voeg daarvoor de volgende regel toe:
MijnAantalRijen = MijnAantalRijen * -1
De macro gaat er nog steeds van uit dat het in de "bovenste" rij staat en de formule moet onder de onderste rij komen. De macroregel die je moet invullen is alsvolgt:
ActiveCell.End(xlDown).Offset(1, 0).Select
Vanaf de actievecell ga je (via End + pijltje omlaag) naar de laatst ingevulde rij en vanaf dat punt ga je nog 1 rij lager.
Nu sta je in de cel waarin de Som-formule moet komen. Hier geven we aan VBA door dat in de actieve cel een Som-formule moet komen die een optelling maakt van de eerste/bovenste rij tot en met de rij boven de formule-rij. Je gaat hiervoor stukjes formules aan elkaar plakken door teksten te combineren met een variabele. Voeg de volgende regel toe:
ActiveCell = "=Sum(R[" & MijnAantalRijen & "]C:R[-1]C)"
Je hebt nu een macro gebouwd die elke keer als je het start gaat uitrekenen hoeveel rijen er opgeteld moeten worden en dat in de Som-formule gaat vermelden.
Nu nog de gele achtergrond. De regels die tussen With en End With stonden hadden allemaal betrekking op het "interieur" van de geselecteerde cel. Door With..End With te gebruiken kun je meerdere acties achter elkaar op schrijven die allemaal betrekkening hebben op het aangegeven onderdeel. Omdat je slechts één actie wilt uitvoeren (de achtergrond geel maken) kun je dit ook in één regel zetten en daarom voeg je de volgende macro regel toe:
Selection.Interior.Color = 65535
De nieuwe macro ziet er nu als volgt uit:
Sub Optelling()
ActiveCell.Offset(-1, 0).Select
MijnLaatsteRij = ActiveCell.Row
Selection.End(xlUp).Select
MijnEersteRij = ActiveCell.Row
MijnAantalRijen = MijnLaatsteRij - MijnEersteRij + 1
MijnAantalRijen = MijnAantalRijen * -1
ActiveCell.End(xlDown).Offset(1, 0).Select
ActiveCell = "=Sum(R[" & MijnAantalRijen & "]C:R[-1]C)"
Selection.Interior.Color = 65535
End Sub
Sluit het VBA-scherm. Klik op de button "Macro's" en koppel aan deze macro een sneltoets om snel de macro te kunnen uitvoeren.
Selecteer als je het macro-scherm hebt afgesloten cel E10 en voer de macro uit met je sneltoets. Maak nieuwe rijtjes met getallen, selecteer de lege cel er onder en start opnieuw de macro en opnieuw zal de macro de juiste berekening maken.
Maar er zit nog een addertje onder het gras. De macro kijkt steeds naar de cel er boven en daarna naar de laatste ingevulde cel boven de actieve rij. Maar wat als er geen ingevulde cellen zijn? Of als alleen de cel boven de actieve cel is gevuld met een getal en de rij daar weer boven is leeg? Dan gaat de macro niet doen wat we er van verwachten. Er moet een soort van controle worden ingevoerd om uit te zoeken of er wel meerdere rijen zijn die zijn gevuld.
Onderstaande macro lost dit probleem op. Kopieer de tekst naar je VBA-scherm, ga het stap voor stap door om te zien wat het doet.
Sub Optelling2()
If ActiveCell.Offset(-1, 0) = "" Then
mijntekst = MsgBox("De rij boven de te plaatsen formule is leeg. De formule kan niet worden berekend. De macro wordt gestopt.", vbOKOnly, "FOUTJE")
Exit Sub
End If
ActiveCell.Offset(-1, 0).Select
MijnLaatsteRij = ActiveCell.Row
If ActiveCell.Offset(-1, 0) = "" Then
mijntekst = MsgBox("De rij boven de actieve cel is leeg. De formule kan niet worden berekend. De macro wordt gestopt.", vbOKOnly, "FOUTJE")
Exit Sub
End If
Selection.End(xlUp).Select
MijnEersteRij = ActiveCell.Row
MijnAantalRijen = MijnLaatsteRij - MijnEersteRij + 1
MijnAantalRijen = MijnAantalRijen * -1
ActiveCell.End(xlDown).Offset(1, 0).Select
ActiveCell = "=Sum(R[" & MijnAantalRijen & "]C:R[-1]C)"
Selection.Interior.Color = 65535
End Sub
Er zijn nog heel veel meer mogelijkheden met macro's. En macro's kunnen je heel veel tijd besparen als je vaak dezelfde acties moet uitvoeren want macrocode wordt veel sneller gelezen dan wat je zelf handmatig kunt uitvoeren.
Ga op zoek binnen je eigen bedrijf naar bestanden die de extensie .xlsm hebben want dat zijn bestanden met macro's. Open het VBA-scherm en probeer te begrijpen wat de macro doet.
Dubbelklik op de modules om te zien wat er in staat. Dubbelklik op de werkbladnamen om te zien of er macro's in staan die gaan werken als je het werkblad opent of afsluit of als je een cel wijzigt. Dubbelklik op "Thisworkbook" om te zien of er misschien een macro wordt gestart als je het bestand opent of afsluit.
Of kijk op internet. Kijk vooral naar websites die veel informatie verstrekken over macro's. Meld je aan bij internetfora waar je de vragen van anderen kunt lezen en hoe ze beantwoord zijn.
En je mag mij ook altijd via www.henkhuiting.nl een vraag mailen.
Dubbelklik op de modules om te zien wat er in staat. Dubbelklik op de werkbladnamen om te zien of er macro's in staan die gaan werken als je het werkblad opent of afsluit of als je een cel wijzigt. Dubbelklik op "Thisworkbook" om te zien of er misschien een macro wordt gestart als je het bestand opent of afsluit.
Of kijk op internet. Kijk vooral naar websites die veel informatie verstrekken over macro's. Meld je aan bij internetfora waar je de vragen van anderen kunt lezen en hoe ze beantwoord zijn.
En je mag mij ook altijd via www.henkhuiting.nl een vraag mailen.
