Categorie archief: VBA (Visual Basic for Applications)

VBA-routines versnellen

Excel, VBA (Visual Basic for Applications), ,
Voorbeeldbestand: VbaSnel.xlsmDownload

Wanneer u net begint met het ontwikkelen van VBA-routines om werkzaamheden te automatiseren, zult u versteld staan van de tijdwinst die dit kan opleveren.

Maar ….  als dan de hoeveelheid gegevens, waarmee u werkt, gaat groeien dan kan het zo maar zijn dan zo’n routine tientallen secondes bezig is en dat u denkt: kan dat niet sneller!

Het antwoord is (hoogst waarschijnlijk): JA, dat kan zeker sneller.

In dit artikel enkele tips voor efficiëntere VBA-routines.

Gebruik van Select

Wanneer u bepaalde handelingen in Excel wilt automatiseren, is de macro-recorder van VBA een uiterst handig hulpmiddel. U neemt de toetsaanslagen op en volgende keer kunt u met één druk op een knop diezelfde toetsaanslagen herhalen. Vaak wordt zo’n recorder-opname dan gebruikt als basis voor een uitgebreidere routine.

In dit voorbeeld geldt dat ook (zie Blad1 in het Voorbeeldbestand):

  • we hebben een tabel (met de naam Tabel1) met 2 kolommen: in de eerste staan maanden en in de tweede worden willekeurige bedragen met 2 decimalen gegenereerd, liggend tussen 1 en 2:
    =ASELECTTUSSEN(100;200)/100
  • het eerste bedrag (cel C3) heeft de naam Bedrag1 gekregen, terwijl het bereik van alle bedragen de naam Bedr heeft.
  •  in kolom F worden het aantal regels, het totaalbedrag en het gemiddelde bepaald. Het tweede gemiddelde is gelijk aan het  totaalbedrag gedeeld door het aantal; een simpel voorbeeld van een ingebouwde controle.
  • als voorbeeld heb ik een VBA-routine gemaakt (gebaseerd op een macro-opname), die ook dezelfde berekeningen uitvoert.
    Deze routine selecteert cel Bedrag1  en “loopt” zolang er geen lege cel is naar beneden; ondertussen wordt het aantal geturfd en een lopend totaal bepaald:
  • op het einde van de VBA-routine zorgen we dat de cursor weer in cel A1 komt en worden de resultaten via een pop-up zichtbaar gemaakt.
    Ook de verstreken tijd is in de pop-up opgenomen.
  • klik op de button Select in het Voorbeeldbestand om de VBA-routine uit te voeren.

NB1 door de rechte haken rond een naam weet VBA, dat het een naam in de Excel-sheet is, dus niet de naam van een VBA-variabele.

NB2 zo ziet een info-scherm op de MAC er uit; de PC-versie oogt anders.

NB3 mijn MAC heeft dus ruim 11 seconden nodig voor de 1.000 verplaatsingen en berekeningen! Op een PC duurt het (uiteraard afhankelijk van de soort machine) nog geen seconde.

NB4 wil je de gebruikte routine zien: klik Alt-F11 of ga via de menutab Ontwikkelaars naar Visual Basic.

Gebruik van Select-2

Het grootste gedeelte van de benodigde tijd van de vorige VBA-routine gaat zitten in het fysiek verplaatsen van de cursor (via Offset).

Met behulp van het VBA-commando Application.ScreenUpdating = False kunnen we er voor zorgen, dat het verversen van het scherm niet meer plaats vindt. Het resultaat is, dat de MAC binnen een halve seconde klaar is!

Klik op de button Select2 om de alternatieve VBA-routine uit te voeren.

NB vergeet niet op het einde van zo’n routine de scherm-verversing weer aan te zetten: Application.ScreenUpdating = True

Gebruik van Range

Veel Excel-ontwikkelaars vinden het gebruik van de Select-methode uit den boze. Bij een prima alternatief wordt het belangrijkste gedeelte van de routine:

De berekeningen worden uitgevoerd voor iedere cel in het bereik Bedr. De computer gaat nu in sneltrein-verhaal langs de inhoud van de cellen zonder dat er een verplaatsing van de cursor nodig is.

De MAC is nu binnen 1/100 van een seconde klaar! Test de routine door op de button Range te klikken.

Gebruik van een array

In het vorige voorbeeld hebben we gebruik gemaakt van een Excel-bereik (in dit geval met de naam Bedr).
Bij heel grote bestanden kan er tijdwinst geboekt worden door dit bereik eerst in een aparte array (rij, reeks, matrix) te plaatsen:

  • het bereik met de naam Bedr (de truc met de rechte haken werkt nu niet meer) wordt cel voor cel in een array aBedr gestopt
  • voor ieder element van de array wordt het lopende totaal bepaald (Ubound=UpperBound=bovengrens)

Sneller dan het geluid!? De routine heeft minder dan 1/10.000 seconde nodig.

Gebruik van Tabel

Een alternatief voor de vorige oplossing is:

  • de kolom Bedrag uit Tabel1 wordt in de array aBedr gestopt
  • de bovengrens van de array plaatsen we in de variabele N (in het vorige voorbeeld moet de bovengrens iedere keer opnieuw worden bepaald; niet efficiënt)

Gebruik van Tabel-2

In een variant van de vorige oplossing schrijven we de resultaten ook nog weg in de Excel-sheet (in cellen van kolom G met de namen Aantal, Totaal en Gem1_):

NB1 cel G5 heeft NIET de naam Gem1. Er bestaat al een cel met die naam, namelijk de cel in kolom GEM, rij 1. Dit wordt vaak opgelost door een underscore er voor of er achter te zetten.

NB2 doordat er cellen in de sheet worden aangepast gaat Excel alle formules doorrekenen; dus alle bedragen in Bedr veranderen NADAT de VBA-berekening is uitgevoerd.

Effect van groter bestand op de berekeningen

Door Tabel1 uit te breiden naar bijvoorbeeld 100.000 regels is het verschil in benodigde tijd beter te beoordelen. Trek daartoe de vulgreep van de tabel rechtsonder naar beneden.

 

Context-menu aanpassen

Excel, VBA (Visual Basic for Applications)
Voorbeeldbestand: ContextMenu.xlsbDownload

We kennen ze allemaal wel, die context-menu’s. Iedere keer als je ergens met de muis rechts-klikt krijg je een pop-up scherm met allemaal verschillende opties. Welke opties dat zijn, hangt af van wat er op dat moment op het scherm staat en/of wat je aan het doen bent. De context dus.
In Excel krijg je bijvoorbeeld een ander context-menu, wanneer je in een ‘gewone’ cel rechts-klikt of wanneer je dat doet in een draaitabel.

NB een context-menu kun je op de PC ook oproepen met behulp van de menu-toets.

Maar bij sommige spreadsheets zou het wel handig zijn om bepaalde opties, die niet standaard in het context-menu staan, wel snel tot je beschikking te hebben. Stel bijvoorbeeld, dat je iedere maand een nieuwe werkmap krijgt, die veel tekst bevat. Telkens merk je dat er spellingfouten in staan en dat er her en der veel extra spaties staan, die je weg wilt hebben. In dit geval zou het handig zijn als je via het context-menu deze zaken snel kunt regelen.

Hieronder is te zien hoe je dat met een stel VBA-programma’s klaar kunt krijgen.

Cel-context-menu

Niet alleen is het menu anders of je nu in een cel rechts-klikt of in een draaitabel, maar de context is ook anders of je in een gewone cel klikt  of in een cel, die bij een Excel-tabel hoort. De context van een cel is óók anders als Excel is ingesteld op Pagina-eindevoorbeeld (kies in de menutab Beeld, in het blok Werkmapweergaven de optie Pagina-eindevoorbeeld).

Dus een cel kent drie verschillende context-menu’s; daar zullen we bij het programmeren dus rekening mee moeten houden.

NB aangezien Office voor de Mac niet helemaal gelijk is aan de Windows-versie werkt de oplossing voor een tabel-cel niet; ook bestaat Pagina-eindevoorbeeld in die versie niet.

Aanpassing cel-context-menu

In dit voorbeeld gaan we het menu dus uitbreiden met 2 opties: de spelling controleren en overbodige spaties wissen.

NB de spelling controleren is misschien wat overbodig: op een PC kan een spellingscontrole ook uitgevoerd worden met behulp van de F7-toets.
Maar het is ook maar een voorbeeld, toch?

Om aanpassingen aan de drie verschillende cel-context-menu’s flexibeler te kunnen instellen, liggen de gewenste aanpassingen  in het Voorbeeldbestand vast in het tabblad Param.
In het bovenste blok kun je aangeven welk van de menu’s moet worden aangepast. Daaronder geef je het nummer op van de gewenste standaardoptie ( 2 is Spelling, 3 is Opslaan etc. Voor een volledige lijst zie het tabblad CmdIds).
Voor het verwijderen van spaties heb ik een eigen routine gemaakt met de naam SchoonSpaties (zie hieronder); deze naam geef je op in de 5e regel. Welk plaatje er in het context-menu moet komen en welke tekst wordt in de volgende regels vastgelegd.

De 7 cellen met ingestelde opties hebben allemaal een naam gekregen, die we in VBA zullen gaan gebruiken: mnuSt, mnuTbl, mnuPagEind, stOpt, nmSub, plSub respectievelijk txtSub.

De VBA-routine, die de daadwerkelijke aanpassing uitvoert, heeft de naam CelMenuAanpassen.

Deze routine/macro is gekoppeld aan de knop Context-menu aanpassen.

Hoe maak je een knop?

  1. klik op  de menutab Ontwikkelaars (ziet u deze menutab niet? Ga naar Bestand/Opties/Lint aanpassen en zorg dat bij Hoofdtabbladen ook Ontwikkelaars is aangevinkt)
  2. kies in het blok Besturingselementen de optie Invoegen
  3. klik dan onder Formulierbesturingselementen op het eerste figuurtje
  4. de cursor wordt een plusje; teken daarmee de contouren van de gewenste knop
  5. na het tekenen krijg je de mogelijkheid om een bestaande macro aan de knop te koppelen.

Aan de tweede knop is de macro CelMenuSchonen gekoppeld; aan de derde de macro CelMenuReset.
Het verschil tussen deze twee macro’s is, dat de eerste de aanpassingen van CelMenuAanpassen terugdraait, terwijl de laatste de context-menu’s terugzet op de standaard-instellingen.

De drie routines zijn terug te vinden in de VBA-editor. Deze is op te starten via de menutab Ontwikkelaars of door op Alt-F11 te drukken.

‘Speel’ met de verschillende mogelijkheden op het tabblad Param van het Voorbeeldbestand: wijzig de instellingen en klik op de knop Context-menu aanpassen. Probeer dan het context-menu uit in de tabbladen Param, VbTbl, VbSpaties en VbSpaties (2).

Wijzig de laatste optie in: Over&bodige spaties verwijderen
(let op het &-teken)
In de Windows-versie van Excel zal in het context-menu de b worden onderstreept. Dat betekent dat je niet alleen deze optie kunt kiezen door er op te klikken maar ook door de onderstreepte letter in te tikken.

LET OP wanneer een context-menu wordt gewijzigd dan gebeurt dit voor alle geopende werkmappen van Excel. Nog sterker: pas je een menu aan, sluit je Excel en start je deze opnieuw op, dan zijn de aanpassingen nog steeds actief.
Vergeet niet dus vóór het sluiten van Excel de aanpassingen terug te draaien of te resetten!

Routine om spaties te verwijderen

De routine, die je in een context-menu wilt opnemen, moet in een module van het Voorbeeldbestand geplaatst worden. In dit geval heb ik een macro/subroutine gemaakt, die overbodige spaties verwijdert uit geselecteerde cellen (dus dubbele spatie en spaties vooraan of achteraan een tekst).

De eerste regel bevat de naam van de routine (SchoonSpaties).
De tweede en derde regel bevatten declaraties van variabelen die we in ons programma gaan gebruiken.
Het commando in de vierde regel zorgt er voor, dat als er iets mis zou gaan in regel 5, het programma gewoon verder gaat naar regel 6. Het afvangen van een mogelijke fout wordt in regel 6 weer beëindigd.
In regel 5 worden die cellen verzameld in rngAll, die in de selectie van het type Constante zijn (dus geen formules/verwijzingen etc) en dan ook nog specifiek teksten bevatten. De selectie moet al gemaakt zijn, voordat de routine is aangeroepen; dat kan met de muis of met de cursor-toetsen zijn gebeurd.
Om hierna geen foutmeldingen te krijgen, wordt in regel 7 beoordeeld of rngAll niet leeg is (de selectie bevatte geen enkele cel met daarin een tekst).
Nu we weten, dat rngAll minstens 1 tekstcel bevat, wordt in regel 8 ieder los bereik (groepje cellen) apart bekeken via de variabele rng.
In regel 9 vindt de echte bewerking plaats: alle cellen in rng worden met behulp van de functie Trim ontdaan van overbodige spaties.
Via regel 10 wordt een eventueel volgend blokje cellen opgehaald.

NB1 regel 5 zou iets compacter kunnen:
Set rngAll = Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
In sommige situaties blijkt dit echter niet goed te werken. Google wist me te vertellen, dat dit met behulp van Intersect dit op te lossen is.

NB2 ook regel 9 zou compacter kunnen:
rng.Value = Trim(rng.Value)
Maar de Trim-functie van VBA verwijdert geen dubbele spaties, dus hebben we gebruik gemaakt van de Excel-functie Trim.

Vragen over de hiervoor gebruikte routines? Neem contact op met G-Info.

 

VBA-routine automatisch starten

Excel, VBA (Visual Basic for Applications), , ,
Voorbeeldbestand: VbaChange.xlsbDownload

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.

  1. start de VBA-editor via het tabblad Ontwikkelaars of Alt-F11
  2. dubbelklik in de Projectverkenner op Blad1 (Vb1)
  3. 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).

  1. start de VBA-editor (Alt-F11)
  2. kies in het VBA/menu Invoegen/Module
  3. 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!

Verkiezingsuitslagen en dynamische draaitabellen

Excel, VBA (Visual Basic for Applications), , , ,
Voorbeeldbestand: DynDraai.xlsbDownload

Deze week las ik een artikel over dynamische draaitabellen gemaakt met PowerPivot (een invoegtoepassing waarmee u krachtige gegevensanalyses in Excel kunt uitvoeren).
De draaitabel liet een overzicht zien, gesplitst naar landen. Werd er echter (met behulp van een slicer) één land geselecteerd, dan veranderde de draaitabel automatisch in een overzicht, gesplitst naar steden.
In PowerPivot kunnen dit soort zaken geïmplementeerd worden met behulp van een eigen programmeertaal.

De uitdaging was om te kijken of dit ook ‘gewoon’ in Excel geregeld kan worden. Ik dacht de verkiezingsuitslagen van vorige week mooi als voorbeeld te kunnen gebruiken (overzicht per provincie verandert automatisch in een overzicht per gemeente), maar helaas . Omdat de uitslagen van 2017 nog niet (op detail-niveau) bekend zijn heb ik de data van 2012 gebruikt. Met een paar kleine aanpassingen kunnen de resultaten van 2017 ingekopieerd worden en kunnen er dan ook vergelijkingen tussen de jaren gemaakt worden. Dat doen we later nog een keer; beloofd is beloofd!

Basis

De gegevens heb ik kunnen downloaden van www.verkiezingsuitslagen.nl in de vorm van een CSV-bestand (Comma Seperated Values). Zie het tabblad Uitslagen 2e Kamer 2012 in het Voorbeeldbestand.

Zoals te zien is, krijg je een tekstbestand in Excel, dat nog wat aanpassingen verdient:

  1. allereerst moeten de teksten over verschillende kolommen verdeeld worden:
    * selecteer alle cellen in kolom A, die omgezet moeten worden (A1 t/mA443)
    * kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Tekst naar kolommen
    * aangezien de kolommen geen standaardbreedte hebben maar door een komma van elkaar zijn gescheiden, kiezen we in het vervolgscherm de optie Gescheiden en klikken op Volgende
    * zorg dat in het volgende scherm de optie Komma staat aangevinkt en klik op Voltooien
  2. even wat regels over de totalen per partij verwijderen (de eerste 22), de gegevens in de vorm van een tabel gieten (kies in de menutab Invoegen de optie Tabel) en de onderste (dreigende) regels verwijderen.
  3. de kopregel aanpassen, zodat de breedte van de kolommen wat beperkt blijft (via Ctrl-1/Uitlijning)
  4. even sorteren op CBS-code
  5. dan blijken veel plaatsen al per provincie bij elkaar te staan: een kolom Provincie toevoegen, aan ieder blok een Provincienaam toevoegen en dan ‘met de hand’ nog wat correcties doorvoeren (wat de systematiek van het CBS voor de codes is, is me niet helemaal duidelijk!)
  6. zo krijgen we een overzicht van de stemmen per gemeente (zie tabblad Uitslagen in het Voorbeeldbestand)

Verkiezingsuitslagen

In dit artikel gaan we alleen wat doen met de partij-uitslagen, dus allerlei kolommen over geldige stemmen enzovoorts kunnen weg.

Om de overzichten zo flexibel mogelijk te kunnen maken moet de bron nog in een zogenaamde database-structuur worden gegoten: niet alle partijen naast elkaar, maar per plaats een aparte regel voor iedere partij met daarbij het aantal stemmen.
Op deze manier ontstaat een basis-bestand met 8.778 regels; voor een mens niet zo handig maar voor Excel geen punt!
Zie het tabblad Basis in het Voorbeeldbestand.

Overzicht per provincie

Allereerst gaan we een overzicht per provincie maken:

  1. selecteer een cel in het basis-bestand
  2. kies Invoegen/Draaitabel en klik OK (Excel kiest automatisch als bron de totale tabel tblBasis)
  3. op het nieuwe werkblad gaan we de draaitabel vormgeven:
    * sleep het veld Partij naar Kolomlabels
    * sleep het veld Provincie naar Rijlabels
    * sleep Aantal naar het Waarde-gebied
    * sleep nogmaals Aantal naar het Waarde-gebied
    * zorg dat Waarden in de Rijlabels staat
    * klik rechts op een van de cellen in een regel met Som van Aantal2, kies Waardeveldinstellingen en dan bij Waarden weergeven als de optie % van rijtotaal
    * wijzig één van de namen Som van Aantal2 in Perc
  4. en nog een sortering aanbrengen:
    * klik met de muis rechts op één van de partijen
    * kies Sorteren en dan de optie Meer sorteeropties
    * in het vervolgvenster kiest u Aflopend en in het bijbehorende keuzeveld de optie Som van Aantal (of Perc; dat maakt in dit geval niets uit). Op deze manier zullen de partijen altijd gesorteerd worden van meeste stemmen naar minste; ook als de draaitabel van inhoud verandert.

Zo ontstaat het overzicht zoals weergegeven in het tabblad Ovz1 van het Voorbeeldbestand.

Overzicht per gemeente

Als we nu willen weten hoe bijvoorbeeld de uitslag van de provincie Groningen tot stand is gekomen, moeten we de draaitabel aanpassen (of een nieuwe maken):

  1. maak een kopie van het tabblad Ovz1 (‘met de muis vastpakken’ en, met de Ctrl-toets ingedrukt, verslepen)
  2. versleep het veld Provincie van Rijlabels naar het Rapportfilter
  3. sleep het veld Plaats naar Rijlabels (boven Waarden!)
  4. selecteer bij Provincie de keuze Groningen (in het tabblad Ovz2 is dat cel C2)

Dynamische draaitabel

Maar zou het niet mooi zijn, als de draaitabel eerst een overzicht van de provincies vertoont, maar dat deze automatisch de plaatsen laat zien als we slechts 1 provincie kiezen?

Om dit voor elkaar te krijgen, moeten we de volgende stappen doorlopen:

  1. maak eerst een nieuwe draaitabel aan vergelijkbaar met hiervoor (zie tabblad Ovz3 in het Voorbeeldbestand)
  2. cel E3 (waar een bepaalde provincie kan worden gekozen) heeft de naam Prov gekregen
  3. aan het basisbestand is een kolom PrPl toegevoegd met de volgende formule:
    =ALS(LINKS(Prov;4)=”(All”;[@Provincie];[@Plaats])
    Dus als in E3 de optie alle provincies is gekozen, dan komt er in de toegevoegde kolom de Provincie te staan, anders de Plaats.
    NB1 @Provincie betekent in een tabel: haal de inhoud op uit de kolom Provincie in dezelfde regel
    NB2 we controleren of de eerste 4 karakters van de cel Prov gelijk is aan (All, omdat Excel soms (Alles), soms (Alle) en in de Engelstalige versie (All) laat zien
  4. sleep nu het veld PrPl naar de Rijlabels van de draaitabel in Ovz3 (eerst even de draaitabel Vernieuwen, omdat anders de nieuwe kolom nog niet in de bron is opgenomen)
  5. voeg nog een slicer toe voor de Provincie (zie het artikel Slicers in Excel)

Wanneer we nu ‘spelen’ met de opties in de cel Prov (of met de slicer) zien we, dat het systeem nog niet vlekkeloos werkt: bij het veranderen van 1 naar alle provincies of andersom moet de draaitabel eerst vernieuwd worden, voordat de gewenste resultaten zichtbaar zijn. Dit komt omdat dan wel de kolom PrPl in de bron wordt aangepast, maar de draaitabel nog de oorspronkelijke inhoud in het geheugen heeft.
Met VBA kunnen we dit automatiseren.

NB Vernieuwen van een draaitabel kan door ergens rechts te klikken in een draaitabel en dan Vernieuwen te kiezen of (als een cel van een draaitabel is geselecteerd) op Alt-F5 te drukken.

VBA

Met de volgende code is het actualiseren van de draaitabel te automatiseren:

Public ProvOud
Public Vernieuwen As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If [Prov] <> ProvOud And Not Vernieuwen Then
Vernieuwen = True
ActiveSheet.PivotTables(“Draaitabel3”).PivotCache.Refresh
End If
ProvOud = [Prov]
Vernieuwen = False

End Sub

Doe nu het volgende:

  1. open (zonodig) het Voorbeeldbestand
  2. klik met de rechter muisknop op het tabblad Ovz3
  3. kies de optie Programmacode weergeven
  4. zorg dat (links) in het projectenoverzicht het blad Ovz3 is geselecteerd
  5. plak dan in het code-scherm de bovenstaande code
  6. sluit de VBA-editor
  7. Sla het bestand op als Excel-werkmap met macro’s; dus met de extensie xlsm
  8. wissel nu tussen provincies afzonderlijk en alle provincies

Bel-grafiek of -diagram

Excel, VBA (Visual Basic for Applications), , , , ,

bel1In het artikel Eerbetoon aan Rosling is gebruik gemaakt van zogenaamde bel-diagrammen, ook vaak bel-grafiek genoemd.
Met behulp van dit soort grafieken is het mogelijk om 3 dimensies weer te geven: via de x- en y-as en door de grootte van de bellen.

Het artikel was aanleiding voor een vraag, die er op neer kwam of er ook nog een vierde dimensie mogelijk was door de kleur van de bellen te variëren.
Met de hand is dit mogelijk maar er is geen standaard-optie, die bij wijzigingen in de sheet de kleuren automatisch zal aanpassen (wat wel gebeurt met de grootte van de bellen).

Kleuren in Excel

Voordat we verder gaan met een mogelijke oplossing voor het probleem van Sander moeten we eerst iets meer weten over de codering van de kleuren in Excel.

Alle kleuren worden opgebouwd door een combinatie van Rood, Groen en Blauw (de zogenaamde RGB-codering). Ieder van die 3 basis-kleuren kan in 256 stappen worden toegevoegd (lopend van 0 tot 255).
bel2Zwart is dan opgebouwd uit 0 delen van iedere kleur, terwijl wit ontstaat door het mengen van alle drie de kleuren met een intensiteit van 255. Door alleen Rood en Groen te mengen (met een intensiteit van 255) ontstaat geel.

Door de blauw-code met 65536 te vermenigvuldigen, de groen-code met 256 en deze 2 dan op te tellen met de rood-code krijgen we een decimale kleurcode.
In het schema hierboven en op het tabblad Param van het Voorbeeldbestand staan enkele voorbeelden.

De decimale codes zullen we hierna gaan gebruiken.

Probleem

Als je een bellengrafiek maakt krijgen de bellen standaard allemaal dezelfde kleur. Deze kunnen gewijzigd worden door één voor één de bellen te selecteren (klik op een bel, dan wordt de hele serie geselecteerd; klik nogmaals op de bel dan wordt de selectie beperkt tot de betreffende bel) en dan via rechts-klikken de opmaak aan te passen.

Maar de vraag is: kan dit geautomatiseerd, waarbij de kleur afhankelijk is van de inhoud van bepaalde Excel-cellen?

Hieronder volgt een oplossing waarbij gebruik wordt gemaakt van VBA; een zelf-geschreven routine controleert van iedere bel wat de corresponderende kleurcode moet zijn en past die aan. Wel moet de routine na iedere wijziging handmatig aangeroepen worden; daar hebben we dan weer een button voor gecreëerd.

Oplossing

bel3Zoals voor ieder bel-diagram hebben we naast waarden voor de x- en y-as ook waarden nodig die de grootte van de bellen zullen bepalen.

NB1 een bel-grafiek is een speciale vorm van een spreiding- of xy-grafiek en kan dus op de assen alleen maar met getallen werken, geen teksten.

NB2 we kunnen niet exact de grootte van de bellen bepalen; Excel bepaalt zelf de grootte relatief ten opzichte van  elkaar.

In het tabblad Data van het Voorbeeldbestand is dit tabelletje opgenomen en is een bel-grafiek gemaakt.

Maar nu komt de vraag van Sander: kunnen de bellen een eigen kleur krijgen afhankelijk van het aantal keren, dat een combinatie van x en y voorkomt. Hij zou graag zien, dat een bel rood is als het aantal 1 tot 5 is, geel bij 5 tot 10 en groen bij 10 of meer.

Daarom eerst even een hulptabel opgezet (zie tabblad Param in het Voorbeeldbestand):

bel4

  1. een kolom voor de ondergrens voor een bepaalde kleur
  2. een kolom met de decimale code voor de gewenste kleur (zie hierboven)
  3. de 8 cellen zijn in een Excel-tabel geplaatst (via Invoegen/Tabel) en deze tabel heeft de naam tbKleur gekregen.

bel5Aan de gegevens heb ik een vierde kolom toegevoegd met een fictief aantal en nog een kolom die uit Param afleidt welke kleurcode de bel moet krijgen. De gegevens zijn in een Excel-tabel opgenomen met de naam tbData.

De formule in de kolom Kleur ziet er als volgt uit:
=VERT.ZOEKEN([@Aantal];tbKleur;2;WAAR)

Ofwel: neem de waarde uit de corresponderende kolom Aantal, zoek in de Excel-tabel tbKleur deze waarde op en geeft de waarde uit de 2e kolom terug (de kleurcode). We zoeken geen exacte match, maar een ‘benadering’ (de grootste waarde die nog voldoet); vandaar WAAR als laatste parameter in de functie VERT.ZOEKEN.

Alles staat nu klaar om de bellen de juiste kleurcodes te geven.

VBA-routine

Zoals hiervoor aangegeven, kent Excel geen automatische koppeling tussen de kleur van de bellen en de waarde in bepaalde cellen. Dus zullen we het zelf moeten doen:

bel6

  1. in de eerste regel geven we ons programma (subroutine) een naam: BelKleur
  2. in de 2e en 3e regel zorgen we er voor dat de variabelen pts en x netjes worden gedefinieerd (mbv Dim): in de eerste variabele gaan we punten bewaren, in de tweede gehele getallen.
  3.  in de 4e regel vullen we de variabele pts met de punten (Points) uit de eerste serie (SeriesCollection(1)) van de grafiek (Chart) met de naam grBel (ChartObjects(“grBel”)) in het actieve Excel-tabblad (ActiveSheet)
  4. dan komt er een For-Next-loop waarbij x loopt van 1 tot het aantal bellen in de grafiek (pts.Count). Alles tussen For en Next wordt dus net zo vaak herhaald als er bellen zijn.
  5. regel 6 en 7 horen bij elkaar (door de Underscore en een spatie op het einde van regel 6).
    De opmaak (Format) van punt x (pts(x)) wordt ingesteld; de bel wordt gevuld (Fill) met een kleurcode (ForeColor.RGB) gelijk aan de inhoud van de cel x+1 (de kop telt ook mee, vandaar +1) uit de kolom Kleur van de tabel tbData uit de actieve sheet (ActiveSheet)

Bellen kleuren

Iedere keer als er iets aan de gegevens wordt gewijzigd (nieuwe regels toegevoegd, aantallen aangepast) zal bovenstaande routine moeten worden uitgevoerd.

Dat kan op verschillende manieren:

  1. klik op de button Bellen kleuren op het tabblad Data van het Voorbeeldbestand
  2. kies op de menu-tab Ontwikkelaars de optie Macro’s, selecteer de routine BelKleur en klik op Uitvoeren
  3. Druk op Alt-F8, selecteer de routine BelKleur en klik op Uitvoeren