Af en toe is het handig, dat Excel automatisch de invoer in een bepaalde cel valideert of dat er automatisch een melding komt wanneer een bepaalde cel wordt gewijzigd of …
Wat ik bedoel is: soms zou je willen, dat wanneer een bepaalde cel wordt gewijzigd, er dan automatisch een actie wordt getriggerd.
Validaties kunnen vaak eenvoudig met de optie Gegevensvalidatie worden uitgevoerd; andere routines kunnen met behulp van VBA (eventueel door een macro op te nemen) worden geprogrammeerd.
Maar hoe zorg je er voor dat zo’n routine automatisch opstart?
In dit artikel zal ik aan de hand van enkele eenvoudige voorbeelden laten zien hoe dit in zijn werk gaat.
Invoer valideren
In het Voorbeeldbestand op het tabblad Vb1 staat een klein tabelletje, waarmee het kwadraat van een ingevoerd getal wordt berekend.
Wanneer in cel C2 (met de naam Invoer) een getal wordt ingetikt, dan wordt in cel C3 het kwadraat bepaald. Maar ook start er een VBA-routine die een controle uitvoert en wordt er een pop-up-scherm getoond, waarvan de inhoud afhankelijk is van het controle-resultaat.
NB de cellen C2, C3 en C4 hebben een naam gekregen: selecteer het bereik B2:C4, kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie; zorg dat alleen Linkerkolom staat aangevinkt.
- start de VBA-editor via het tabblad Ontwikkelaars of Alt-F11
- dubbelklik in de Projectverkenner op Blad1 (Vb1)
- daar staat de volgende routine:
Door de naam van de subroutine (Worksheet_Change) zal Excel deze routine automatisch opstarten als er ergens in dit werkblad (Vb1 dus) iets wordt gewijzigd. Welke cel(len) er is/zijn gewijzigd wordt dan via de variabele Target doorgegeven.
Allereerst tellen we het aantal cellen in Target; als dat niet gelijk is aan 1, dan zijn we klaar.
Dan testen we of het adres (de celverwijzing) van Target gelijk is aan die van Invoer; zo niet dan zijn we ook klaar.
Via Int([Invoer]) bepalen we het Integer-gedeelte (het gedeelte vóór de decimale komma) van de cel Invoer en kijken of dat gelijk is aan de Invoer zelf; zo niet dan zorgen we via Msgbox dat er een pop-up verschijnt met die melding.
Is de invoer wel een geheel getal, dan komt er een Msgbox, die laat zien wat er is ingevoerd en wat het resultaat in de cel Uitvoer is. Wanneer op OK wordt geklikt in de pop-up, dan wordt de cel met de naam VBA_result gevuld met het kwadraat van Invoer.
NB1 Denk aan de vierkante haken om de naam van een cel(-bereik). Anders ziet VBA dat als een interne variabele.
NB2 Via Format zorgen we er voor, dat bij grote getallen ieder blokje van 3 cijfers gescheiden wordt. Aangezien de VBA-editor Engelstalig is, krijgen we als scheiding een komma; met het commando Replace vervangen we die door een punt.
NB3 verschillende stukken teksten koppelen we aan elkaar met het &-teken (zorg dat er spaties omheen staan); vbCrLf zorgt er voor, dat de rest van de tekst op een volgende regel komt. De naam van de code (Carriage Return, Linefeed; wagen terug en 1 regel naar beneden) stamt nog uit het typemachine-tijdperk!.
NB4 met de spatie en het _-teken (underscore) kun je in de VBA-editor lange regels in ‘stukken knippen’; Excel weet dan dat ze bij elkaar horen.
Draaitabel vernieuwen
In het tabblad Vb2 van het Voorbeeldbestand staan een (simpele) Excel-tabel (met de naam tblData2), een draaitabel, die daarop gebaseerd is, en een controle-blokje. Wanneer in dat laatste gebied de inhoud van cel F2 gelijk wordt aan Ja (via gegevensvalidatie zijn alleen Nee en Ja toegestaan), dan wordt de draaitabel automatisch vernieuwd.
De VBA-routine, die deze klus voor zijn rekening neemt, vindt u in de VBA-projectverkenner in het object Blad2 (Vb2). Vergeet niet te dubbelklikken!
Waarschijnlijk behoeft de routine geen verdere uitleg; hij lijkt veel op die in het tabblad Vb1.
NB cel F3 wordt met behulp van de functie DRAAITABEL.OPHALEN gevuld; zie ook het artikel over Draaitabel.Ophalen.
Draaitabel vernieuwen 2
De 2 VBA-routines uit Vb1 en Vb2 zijn redelijk rechttoe-rechtaan geprogrammeerd; om ze makkelijker te kunnen begrijpen zijn niet alle officiële regels gehanteerd en zijn er weinig fout-controles ingebouwd.
Het voorbeeld in tabblad Vb3 is al iets netter: het vernieuwen van de draaitabel is in een aparte subroutine opgenomen (met de naam DraaiVernieuwen).
- start de VBA-editor (Alt-F11)
- kies in het VBA/menu Invoegen/Module
- en plaats in de nieuwe Module de volgende routine:
LET OP in Vb2 werd de draaitabel (in het Engels Pivottable) met de naam Draaitabel1 vernieuwd. Deze routine vernieuwt de draaitabel met het volgnummer 1 in het actieve werkblad.
De nieuwe subroutine kan ook op andere plaatsen en op andere manieren worden aangeroepen. In Vb3 is een knop gemaakt, die de routine aanroept.
Klik rechts op de knop en kies Macro toewijzen.
NB alleen macro’s/subroutines die in een module zijn opgenomen kunnen aan een knop worden toegewezen; vandaar dat de routine DraaiVernieuwen niet in het object Vb3 staat.
Draaitabel vernieuwen bij wijzigen bron
Maar het zou natuurlijk nog mooier zijn als de draaitabel automatisch zou worden vernieuwd bij het wijzigen van de bron-gegevens (waaronder ook wordt verstaan het toevoegen van nieuwe gegevens).
In het Voorbeeldbestand is in het tabblad Vb4 een nieuwe Excel-tabel opgenomen (met de naam tblData4) en een daarop gebaseerde draaitabel.
Verander je de Soort of het Aantal in een bestaande regel dan start automatisch de VBA-routine in het object Blad4 (Vb4). Beter gezegd: de routine start bij iedere wijziging van een cel in dit werkblad, maar er wordt slechts daadwerkelijk iets uitgevoerd als een cel in tblData4 wordt gewijzigd.
Omdat in regel 3 de Target maar 1 cel kan omvatten kun je die niet zomaar met tblData4 vergelijken. We willen weten of die ene Target-cel in tblData4 voorkomt.
De VBA-functie Intersect bepaalt wat de overlap tussen de twee bereiken is. Is die overlap leeg dan is Intersect gelijk aan Nothing, dus als die Not Nothing is dan is er wel overlap.
NB1 de truc met Not Nothing kom je op veel plaatsen op Internet tegen en is algemeen toepasbaar voor allerlei soorten ranges. In dit geval is de Target maar 1 cel; we kunnen regel 3 dan vervangen door een meer simpele constructie:
If Intersect(Target, [tblData4]) = Target Then
NB2 de Msgbox levert nu wel een vreemd resultaat; toch niet goed over de routine DraaiVernieuwen nagedacht, tja!
Stel je hebt een overzicht van verkochte aantallen per maand en je wilt weten welke maand het beste is geweest?
In het tabblad Top5 van het 
klik rechts op één van de getallen in de tweede kolom en kies de optie Sorteren en dan Sorteren van hoog naar laag
Zorg dat in het tweede veld in plaats van de standaard 10 een 5 komt, de rest is OK.
Dit Top-10-filter kent nog 2 andere opties. Wanneer u in plaats van Items kiest voor Procent dan zal Excel die maanden laten zien, die er samen voor zorgen, dat het ingestelde percentage minimaal wordt bereikt.
De derde mogelijkheid is om een harde grens meet te geven; in dit voorbeeld willen we die maanden zien die samen minimaal 100 opleveren.
De functie GROOTSTE kent 2 parameters:
Op het tabblad Top5_2 van het 
Maken we hierop een draaitabel met een Top-5, dan lost Excel het probleem voor ons simpel op: hij maakt automatisch een Top-6!
Ieder resultaat van de functie GROOTSTE krijgt in kolom K een SubNr mee. In cel K4 staat daartoe de formule: