Categorie archief: VBA (Visual Basic for Applications)

Bulk-berekeningen


LET OP: na het downloaden de extensie wijzigen in xlsm


Vroeger…. toen was het allemaal anders (en beter?).
Je had toen speciale bureaus, die niets anders deden dan (standaard)berekeningen uitvoeren … “met de hand”.

Tegenwoordig wordt Excel vaak ingezet voor berekeningen. Maar in de praktijk zie ik daar vaak nog veel hand-arbeid aan te pas komen. Daar is op zich niets mis mee, maar het is wel veel foutgevoeliger dan wanneer dit soort processen worden geautomatiseerd.
Daarom deze keer aan de hand van een hypotheek-voorbeeld uitleg hoe met (een beetje) VBA bulk-processen binnen Excel kunnen worden uitgevoerd.

Hypotheek-berekening

De basis van dit artikel is een hypotheekberekening (zie het tabblad BerekHyp van het Voorbeeldbestand). Aan de hand van enkele input-gegevens wordt een overzicht van rente en aflossing gegenereerd; voor de overzichtelijkheid op jaar-basis.

Alle input-cellen (C2:C7) hebben een naam gekregen gelijk aan de tekst in de kolom links er van (via Formules/Namen/Maken obv selectie en dan de optie Linkerkolom).
Via Gegevens/Gegevensvalidatie zijn de invoer-mogelijkheden van enkele cellen ingeperkt.
Het schema van rente en aflossing is in een Excel-tabel weergegeven (met de naam tblBerek).

Cel B11 bevat een harde waarde, gelijk aan 1, cel C11 heeft als formule =Bedrag (het gewenste hypotheekbedrag dus).
In cel D11 wordt de verschuldigde rente voor dat jaar bepaald (voor een annuïteiten- of lineaire hypotheek is dat hetzelfde):
=[@BeginBedr]*Rente, dus het BeginBedr uit dezelfde regel vermenigvuldigd met het verschuldigde rentepercentage (cel C5).

De berekening van de aflossing het eerste jaar (cel E11) is iets ingewikkelder en afhankelijk of het een lineaire of annuïteiten-hypotheek betreft:
=ALS(Soort=”Lin”;
Bedrag/Looptijd;
-BET(Rente;Looptijd;Bedrag) – [@Rente])

Dus: als het een een lineaire hypotheek is dan is de aflossing gelijk aan het Bedrag gedeeld door de beoogde totale Looptijd. Anders (dus bij een annuïteit) is het gelijk aan het resultaat van de functie BET(Rente;Looptijd;Bedrag) minus de verschuldigde Rente van dat jaar.

NB1 de functie BET berekent aan de hand van de parameters rentepercentage, aantal termijnen en beginbedrag het verschuldigde termijnbedrag

NB2 als het beginbedrag in de functie BET positief is, dan is het resultaat van de functie negatief; vandaar het min-teken voor BET.

NB3 moeten de berekeningen exacter dan zul je het schema op maandbasis moeten doorrekenen; de Rente door 12 delen en de Looptijd en de rentevastperiode (RVP) met 12 vermenigvuldigen.

Het volgende Jaar-nummer (cel B12) is gelijk aan het vorige Jaar plus 1; het BeginBedr in cel C12 is gelijk aan het resultaat van cel F11. De formules in de overige cellen van rij 12 zijn gelijk aan die in de vorige regel. De formules uit rij 12 kunnen daarna allemaal naar beneden gekopieerd worden.

NB het schema mag alleen maar gevuld worden voor die jaren, die binnen de rentevastperiode (RVP) vallen. Dat is met behulp van de ALS-functie makkelijk op te lossen (zie Voorbeeldbestand).

In hetzelfde tabblad BerekHyp staat ook een samenvatting van de resultaten: de totale bedragen aan rente en aflossing en de restant-schuld op het einde van de RVP.

De eerste 2 berekeningen zijn rechttoe rechtaan: neem de som van de betreffende kolom in de tabel tblBerek. De bepaling van het eindbedrag gaat via de formule =MIN(tblBerek[EindBedr]), ofwel bepaal het minimum van de kolom EindBedr in de tabel tblBerek.

Bulk-invoer

Moet je nu 10 verschillende berekeningen uitvoeren, dan zullen per berekening de betreffende gegevens moeten worden gewijzigd, Excel berekent de resultaten en deze moeten overgenomen worden of geprint.
Maar wat te doen als er 100 berekeningen nodig zijn of misschien 2.000? Het overnemen van invoer-gegevens is behoorlijk foutgevoelig; de meest voorkomende fouten zijn:
* een 0 te veel of te weinig bij het bedrag
* een decimaal fout in het rentepercentage
* vergeten Ann te veranderen in Lin of andersom

Dus het is zaak om zo min mogelijk gegevens handmatig in te voeren.
Zorg bijvoorbeeld, dat gegevens door een ander systeem in Excel-formaat worden aangeleverd óf laat (als je zelf een overzicht in Excel hebt gevuld) iemand anders het overzicht aan de hand van brongegevens controleren.
Een voorbeeldbestand met 2.000 regels (met de naam tblInput) is opgenomen in het tabblad Input van het Voorbeeldbestand.

Bulk-berekeningen (handmatig)

Als de invoer-gegevens gecontroleerd zijn kunnen die verwerkt worden tot resultaat-gegevens.

In het tabblad Bereken van het Voorbeeldbestand staat een vergelijkbaar schema als in het tabblad BerekHyp.
Het enige verschil is, dat de invoergegevens met behulp van VERT.ZOEKEN-formules worden opgehaald uit de tabel tblInput op basis van het corresponderende Nr.

NB voor het verticaal zoeken hebben we een Hulp-kolom gebruikt om aan te geven uit welke kolom van tblInput het betreffende veld gelezen moet worden.

Op deze manier kunnen redelijk vlot alle berekening doorlopen worden. Maar wat doe je met de resultaten. Eén mogelijkheid is om deze te verwerken in een apart output-tabblad (zie Output1 van het Voorbeeldbestand):

  • Vul in cel D2 van het tabblad Bereken een nummer in
  • Kopieer de cellen D2:D7 van het tabblad Bereken
  • Kies Plakken Speciaal/Waarden en Transponeren in een nieuwe regel in het tabblad Output1
  • Kopieer de cellen H2:H4
  • Plak die op een vergelijkbare manier achter de vorige gegevens
  • herhaal bovenstaande voor alle nummers uit Input

Bulk-berekeningen (met VBA)

Maar we kunnen het hele proces natuurlijk nog verder automatiseren; daar komt dan wat VBA (programmeerwerk) om de hoek kijken.
In het tabblad Bereken staan enkele buttons; de eerste met de titel Vullen 1 start de subroutine OutputVullen1 (rechtsklikken op de button en dan kiezen Macro toewijzen):

Een korte toelichting:

  • Allereerst worden met behulp van het commando Dim de variabelen gedeclareerd, die we binnen de routine gaan gebruiken.
  • Na de declaraties wordt de variabele MaxHyp gevuld met de waarde van de Range met de naam AantHyp (cel I2 in het tabblad Input)
  • Dan een vreemd blok (met de #-tekens): dit is een stuk met zogenaamde Conditional Compilation. De regel, die begint met If MaxHyp> 500, wordt alleen uitgevoerd op een MAC-computer. Omdat een MAC veel langzamer is dan een Windows-machine, beperken we het aantal uit te voeren berekeningen tot maximaal 500.
  • De regel na het #-blok plaatst een pop-up op het scherm, die aangeeft hoeveel berekeningen er uitgevoerd zullen gaan worden. Wordt binnen deze pop-up op Cancel (of Annuleren) gedrukt dan wordt de subroutine afgebroken.
  • Om straks te kunnen bepalen hoeveel tijd de routine heeft gekost, wordt de starttijd vastgelegd in de variabele StartTijd (beter gezegd de datum en tijd worden bewaard).
  • Dan wordt een andere subroutine (OutputLeegMaken1) uitgevoerd (zie hieronder).
  • Het belangrijkste gedeelte van de subroutine wordt gevormd door een zogenaamde For-Next-loop. Het stukje programma tussen For en Next wordt een bepaald aantal keren uitgevoerd, afhankelijk van de inhoud van (in dit geval) MaxHyp. De teller i houdt bij welke hypotheek-berekening wordt uitgevoerd.
  • Binnen de loop wordt allereerst de waarde van de teller i in de cel met de naam Nr geplaatst (zoals we hiervoor handmatig deden).
  • Excel herberekent op dat moment direct alle relevante cellen.
  • Het volgende gedeelte van het programma plaatst dan alle relevante waardes in de resultaat-tabel tblOutput1. Dit wordt gedaan door telkens, geredeneerd vanuit de tabelkop (Header) met de naam Nr, een aantal regels i naar beneden en 0, 1 et cetera kolommen naar rechts te gaan.
  • De overige regels hebben geen functionele bijdrage.

In bovenstaand programma wordt de routine OutputLeegMaken1 aangeroepen:

Via de Range-opdracht wordt de uitvoer-tabel leeg gemaakt. Als deze toevalligerwijs al leeg zou zijn, zou het programma stoppen met een foutmelding. De regels er omheen zorgen er voor, dat bij een eventuele fout het programma gewoon doorgaat.

Op mijn oude laptop heeft Excel ruim een minuut nodig om de berekeningen uit te voeren en de resultaten weg te schrijven.
Op Windows-computers is goed te zien, dat Excel aan het werk is; cellen veranderen continu van inhoud.

In de praktijk blijkt dat het bijwerken van het scherm het merendeel van de benodigde tijd in beslag neemt.
In de routine OutputVullen1b is daarom een regel toegevoegd: Application.ScreenUpdating = False

Via de button Vullen 1b start u deze routine; nu blijkt er nog maar 13 seconden nodig te zijn voor de verwerking van de 2.000 hypotheken!

LET OP vergeet niet in de VBA-routine de schermverversing weer aan te zetten. Onderaan komt dan Application.ScreenUpdating = True

Bulk-berekeningen (met VBA) vervolg

Maar het kan nog sneller. Wanneer we de For-Next-Loop vervangen door:

dan duurt het totale proces nog maar een paar seconden (klik op de button Vullen 2). De bijbehorende VBA-routine heeft de naam OutputVullen2.

Om dit mogelijk te maken is in het tabblad Output2 van het Voorbeeldbestand een aantal extra cellen toegevoegd, die samen de naam ResultRng hebben gekregen. De inhoud van deze cellen wordt in één keer in de Range vanaf (i,0) tot (i,8) geplaatst.

De cellen van ResultRng bevatten twee matrix-formules TRANSPONEREN, waarmee de benodigde gegevens uit het tabblad Bereken worden opgehaald (zie het artikel Transponeren).
Matrix-formules worden ingevoerd door in plaats van af te sluiten met Enter, de toetscombinatie Control-Shift-Enter te gebruiken (de CSE-methode). Excel plaatst dan automatisch accolades rond de formule.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:


Scrollen in dashboard


LET OP: na het downloaden de extensie wijzigen in xlsb


Excel wordt steeds vaker gebruikt om het management via dashboards van actuele informatie te voorzien.
Maar ook in dit soort rapportages is de ruimte beperkt. Daarnaast is niet iedere manager geïnteresseerd in dezelfde informatie.

Dit soort problemen is handig op te lossen door gebruikers in de digitale rapportages door de overzichten te laten scrollen.

In dit artikel komen enkele methoden aan bod hoe dit te realiseren. En als we toch bezig zijn: met een beetje VBA kunnen we de overzichten ook makkelijk sorteren.

Basis-materiaal

We hebben een overzicht van afdelingen (het tabblad Data in het Voorbeeldbestand bevat 30 regels) met per afdeling het toegekende kosten-budget (altijd 100.000), de werkelijk gemaakte kosten, het aantal uitgebrachte offertes en het gerealiseerde aantal contracten. Daarnaast bevat het overzicht nog enkele KPI’s: kosten/budget, kosten/offertes en kosten/contracten.

De gegevens zijn opgeslagen in de vorm van een Excel-tabel met de naam tblAfd. In cel L2 (met de naam AantAfd) wordt het aantal afdelingen in de tabel geteld:
=AANTALARG(tblAfd[Afdeling])
Dit aantal kunnen we later goed gebruiken om bepaalde zaken te automatiseren.

NB de nummering van de afdelingen is zodanig dat er altijd 2 cijfers gebruikt worden; dit om een juiste sortering te krijgen. Anders krijg je Afd1, Afd10, Afd11, … , Afd2, Afd20 etc.

Overzicht 1

Wanneer nou blijkt, dat we in een bepaalde rapportage slechts ruimte voor 10 regels hebben; welke afdelingen (van de 30) moeten we dan laten zien? Dat hangt natuurlijk af van de wensen van de ontvangers en die kunnen wel eens tegenstrijdig zijn.
Daarom gaan we nu het overzicht met 10 regels zo aanpassen, dat hierbij gekozen kan worden welke regels zichtbaar zijn.

In het tabblad SelIndex van het Voorbeeldbestand geven we in cel C2 (met de naam Start) aan, welke regel van de 30 als bovenste moet worden weergegeven; de volgende 9 worden ook getoond.

In cel F3 staat de formule:
=INDEX(tblAfd[Afdeling];RIJ()-RIJ(AfdKop)+Start-1)
Ofwel: zoek met de functie INDEX in de kolom Afdeling van de tabel tblAfd die rij op die overeenkomt met de RIJ() van (in dit geval) cel F3, minus het rij-nummer van de kop van het overzicht plus de waarde van de cel Start.

NB1 cel F2 heeft de naam AfdKop gekregen.

NB2 voor het bepalen van de juiste regel hadden we natuurlijk ook een hulpkolom kunnen gebruiken met daarin de waardes 1 t/m 10. De gebruikte berekening maakt het mogelijk om de hulpkolom weg te laten en is flexibel genoeg om het overzicht eventueel later nog te verplaatsen. Ook kunnen we deze formule zonder verdere aanpassingen naar beneden kopiëren.

NB3 de formule kan ook naar rechts gekopieerd worden; Excel wijzigt de kolom-verwijzing Afdeling automatisch naar Budget etc.

NB4 om de juiste regel te selecteren moeten we de berekening nog corrigeren met -1.

Wanneer nu de waarde in de cel Start wordt gewijzigd zal het overzicht zich automatisch aanpassen.

Dat aanpassen kan natuurlijk nog veel mooier en makkelijker met behulp van een schuifbalk:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  2. in het vervolgmenu kiest u binnen het blok Formulierbesturingselementen de optie Schuifbalk
  3. de cursor wordt dan een kruisje; teken, met de linkerknop ingedrukt, de gewenste vorm van de schuifbalk op de gewenste plaats (dit kan allemaal later nog aangepast worden).
  4. klik met de rechtermuisknop op de nieuwe schuifbalk en kies Besturingselement opmaken
  5. zorg dat een koppeling met cel C2 wordt gemaakt (u kunt hier ook de naam Start gebruiken), de minimumwaarde wordt 1 en het maximum 21.

LET OP kies NIET een ActiveX-besturingselement; deze leveren in de praktijk nogal eens crashes van Excel op.

NB als je de schuifbalk wilt verplaatsen of de grootte corrigeren, klik dan eerst rechts op de schuifbalk.

Overzicht 2

Een andere methode om zo’n overzicht met de juiste gegevens te vullen is door gebruik te maken van de functie VERSCHUIVING.

In het tabblad SelVersch1 van het Voorbeeldbestand ziet u in cel F3 de formule:
=VERSCHUIVING(tblAfd[[#Kopteksten];[Afdeling]];
RIJ()-RIJ(AfdKop)+Start-1;
KOLOM()-KOLOM(AfdKop))
ofwel: haal de waarde op uit de cel, die gevonden wordt door vanuit de Afdelings-koptekst van de tabel tblAfd een aantal rijen naar beneden te gaan en een aantal kolommen naar rechts.

NB1 de constructie na het eerste (-haakje hoeft u niet zelf in te tikken; klik gewoon op de betreffende cel en Excel vult de formule vanzelf aan.

NB2 de ingegeven minimum- en maximumwaardes voor de schuifbalk zorgen er voor dat het overzicht geen blanco regels zal bevatten. Helaas is dat niet het geval als in de cel Start een te grote of te kleine waarde wordt ingevoerd.

Overzicht 3

De functie VERSCHUIVING kent nog meer parameters/argumenten. Daar hebben we in het overzicht op het tabblad SelVersch2 van het Voorbeeldbestand gebruik van gemaakt. Cel F3 bevat de formule:
=VERSCHUIVING(tblAfd[[#Kopteksten];[Afdeling]];Start;0;10;8)
Ofwel: selecteer een bereik van cellen, die, gerekend vanaf de koptekst Afdeling, een aantal rijen gelijk aan Start lager begint. Het begin is 0 kolommen verschoven. Het resulterende bereik moet 10 regels hoog en 8 kolommen breed zijn.

LET OP deze formule levert een blok van 80 cellen op. Daarom moet de formule op een speciale manier worden ingevoerd:

  • selecteer eerst met de muis alle cellen waar het overzicht moet komen (in het voorbeeld de cellen F3 tot en met M12)
  • voer dan bovenstaande formule in
  • druk in plaats van op Enter tegelijkertijd op Ctrl-Shift-Enter (de zogenaamde CSE-methode).
  • Excel plaats dan accolades rond de formule

We willen er ook voor zorgen, dat er geen lege regels komen.
De cel Start heeft daartoe een gegevens-validatie gekregen: bij Toestaan is de optie Aangepast ingevuld en bij Formule:
=EN(Start>0;Start<=AantAfd-9)
Dus de waarde in de cel Start moet aan 2 voorwaarden voldoen: groter dan nul EN kleiner of gelijk aan het aantal regels in de bron (minus 9).

LET OP vergeet het eerste =-teken niet

Maximum in schuifbalk

Maar wat als er nu een afdeling bij komt? De tabel tblAfd wordt automatisch aangepast, de teller van het aantal afdelingen zal ook direct opgehoogd worden, de gegevensvalidatie uit het vorige overzicht zal daarom ook goed werken.
Maar … de schuifbalken hebben nog steeds een maximum van 21.

Alleen met behulp van een (kleine) VBA-routine kunnen we het maximum van de schuifbalk automatisch laten meelopen met het aantal regels in de bron-gegevens.

In het overzicht van tabblad SelVersch3 van het Voorbeeldbestand is deze routine geïmplementeerd. Probeer maar eens uit: voeg een regel in het tabblad Data toe en beweeg de schuifbalk op en neer.

Hoe kun je de eigenschappen van de schuifbalk door VBA laten aanpassen?

  1. klik rechts op de schuifbalk
  2. kies de optie Macro toewijzen
  3. als er nog geen macro aan de schuifbalk gekoppeld is dan kiest u Nieuw, anders Bewerken
  4. vul onderstaande programmacode in en sluit de Visual Basic-editor

NB1 afhankelijk van de Excel-versie kan de omschrijving van de subroutine iets anders zijn, bijvoorbeeld Sub Schuifbalk1_BijWijzigen. Laat de naam staan zoals die door Excel is gegenereerd.

NB2 afhankelijk van de situatie kan er ook sprake zijn van Schuifbalk2 etc.

NB3 VBA is altijd Engelstalig. Dus binnen de subroutine is sprake van een ‘shape’ met de naam Scroll Bar 1.

NB4 met de constructie Range(“AantAfd”).Value wordt de waarde uit de cel met de naam AantAfd opgehaald.

‘Automatisch’ sorteren

Zoals al eerder aangegeven zal niet ieder ontvanger van de rapportage de focus op dezelfde KPI leggen. We maken de rapportage zodanig dat de gebruiker zelf kan aangeven welke sortering de gegevens moeten hebben.

Klik op één van de keuzerondjes en de gegevens worden op de betreffende kolom gesorteerd (zie het tabblad Sortering in het Voorbeeldbestand).

  1. keuzerondjes worden op een vergelijkbare manier als schuifbalken aan het tabblad toegevoegd.
  2. klik rechts op één van de keuzerondjes en kies de optie Besturingselement opmaken
  3. koppel het besturingselement aan een cel in Excel; in het voorbeeld C4 ofwel KolomNr
  4. klik rechts op het eerste keuzerondje en kies de optie Macro toewijzen en zorg dat de volgende routine gekoppeld wordt:

NB in dit geval heeft het eerste rondje al het volgnummer 2.

De macro-toewijzing moet voor ieder keuzerondje apart worden uitgevoerd.

Bovenstaand subroutine roept een andere routine aan, Sortering. Deze ziet er als volgt uit (de basis is gemaakt door een macro op te nemen terwijl de sortering handmatig wordt uitgevoerd):


  • als eerste wordt de variabele Kolom gevuld: op basis van de waarde in cel KolomNr wordt één van de kolomkoppen gekozen
  • met het commando With wordt er voor gezorgd dat alle volgende opdrachten betrekking hebben op een Sort van de tabel (ListObject) tblAfd
  • vorige sorteringen verwijderen
  • nieuwe sortering toevoegen
  • het te sorteren bereik heeft een Header/kop
  • de sortering is niet gevoelig voor hoofd- en kleine letters
  • de sortering-orientatie is verticaal (inhoud van een kolom is bepalend)
  • de PinYin-regel mag ook weggelaten worden; alleen interessant bij Chinese tekens
  • en als laatste wordt de sortering toegepast

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:


Grafiek automatisch schalen en labels toevoegen



Herkent u de frustratie? Heb je net een grafiek ‘netjes’ gemaakt door de assen zodanig te schalen dat de gegevens mooi verdeeld zijn over de grafiek, klopt er niets meer van als er een andere of uitgebreidere reeks gegevens worden weergegeven.
Of je hebt ‘met de hand’  alle labels bij de punten aangepast (ja, ik gebruik nog een oude Excel-versie), dan gaan die bij een andere reeks de mist in.

Dus deze keer een anti-frustratie-artikel: 2 VBA-routines, die bovenstaande problemen oplossen.

Test-gegevens

Om een voorbeeld te kunnen maken had ik wat test-gegevens nodig. Meestal maak ik die door wat random-getallen of teksten te genereren.
Maar ik herinnerde me ineens dat ik ooit eens ergens gelezen had dat je ook test-gegevens op een site kon laten maken.
Na wat zoeken kwam ik de website mockaroo.com tegen.

Na een paar pogingen had ik een mooi lijstje met voorbeeld-gegevens (zie het tabblad Basis in het Voorbeeldbestand).

  1. bij Soort heb ik gekozen voor financiële markten (Mockaroo kiest dan willekeurig uit 2 mogelijkheden)
  2. de Datum ligt tussen 1 jan 2018 en 31 dec 2018
  3. bij Omschr heb ik voor willekeurige voornamen gekozen
  4. het Bedrag ligt tussen 1000 en 2000

Om de rest wat overzichtelijk te houden heb ik 20 regels daaruit geselecteerd (zie het tabblad Basis2) en een beetje aangepast. Deze test-gegevens staan klaar in een Excel-tabel met de naam Tabel1.

Grafiek

Om het verloop van de bedragen per Soort handig te kunnen weergeven, heb ik een draaitabel gemaakt op basis van de gegevens in Tabel1 (zie het tabblad Result in het Voorbeeldbestand).

Op basis van deze draaitabel kun je makkelijk een Draaigrafiek maken, maar dan komt Omschr automatisch op de x-as en ik wil deze gebruiken als Labels in de grafiek.

Dus maar een ‘gewone’ grafiek gemaakt met op de x-as de datums en op de y-as de bedragen.

Hiernaast staat de grafiek waarbij er geen Soort is geselecteerd (dus 20 punten). De x-as begint automatisch bij 19 januari omdat dat de kleinste datum is; iets vergelijkbaars geldt voor het einde van de as.
Excel heeft ook de y-as automatisch geschaald.

Assen handmatig aanpassen

Om de assen handmatig te schalen klik je met de rechtermuisknop op een as en pas je het minimum en het maximum aan (het vinkje daarvoor zal dan verdwijnen ten teken dat er geen automatische schaling hoeft plaats te vinden).

Maar iedere keer als er in de draaitabel een andere Soort wordt gekozen moeten er aanpassingen worden doorgevoerd.

Assen automatisch aanpassen

Om deze schaal-aanpassingen met VBA geautomatiseerd te kunnen doorvoeren moeten we ergens vastleggen wat het gewenste minimum en maximum is.

In het Voorbeeldbestand op het tabblad Result staat in cel G2 de formule =MIN(B:B)
Oftewel bepaal de kleinste waarde/datum in kolom B. In G3 wordt met de functie MAX de grootste waarde vastgelegd. Maar het is mooier als de x-as op de eerste van een maand begint: in cel H2 staat daartoe de formule =DATUM(JAAR(G2);MAAND(G2);1). Deze cel heeft de naam x_min gekregen.

Het maximum van de x-as moet de eerste dag van de volgende maand zijn: cel H3 (met de naam x_max) heeft daarom de formule =DATUM(JAAR(G3);MAAND(G3)+1;1)

NB ook als G3 een datum in december bevat, werkt deze formule goed; Excel zal automatisch het jaar ophogen en de maand gelijk maken aan januari.

In kolom J worden de minimum- en maximum-bedragen uit kolom D bepaald. Cel K2 bevat de formule =GEHEEL(J2/100)*100, zodat het minimum voor de y-as het eerste honderdtal is, kleiner dan de waarde in J2.
In cel K3 bepalen we de bovengrens voor de y-as: =(GEHEEL(J3/100)+1)*100

Nu hebben we nog een VBA-routine nodig, die de handelingen voor het aanpassen van de assen van ons overneemt.

In het tabblad Result is een knop met de naam Assen schalen toegevoegd waarmee deze macro/subroutine kan worden uitgevoerd.

Labels toevoegen

In de oudere versies van Excel is het niet mogelijk om automatisch extra informatie (zoals de Omschr uit het voorbeeld) als Labels aan een punt toe te voegen. Handmatig kan dit wel:

  1. klik rechts op de lijn in de grafiek
  2. kies de optie Gegevenslabels toevoegen
  3. klik op één van de labels; als dan alle labels zijn geselecteerd nogmaals op het label klikken
  4. de tekst van dit label kan nu aangepast worden

Maar als er nu in de draaitabel een andere Soort wordt gekozen dan kloppen de handmatig ingevoerde labels niet meer!

Daar komt VBA weer goed van pas:

Het spannende is hier wat er gebeurt met de variabele xVals.

Wanneer je in de grafiek op de lijn klikt verschijnt er in de formulebalk een formule die begint met =REEKS(
Daarna komt de cel, die de naam van de reeks bevat, dan de x-waarden en daarna de y-waarden.

Aangezien VBA Engelstalig is bevat xVals na het uitlezen van de formule van de Collection: =SERIES(Result!$B$4,Result!$B$6:$B$25,Result!$D$6:$D$25,1)

NB1 binnen Excel (met Nederlandse instellingen) worden de parameters gescheiden door een ; (punt-komma), maar binnen VBA is dit een , (komma).

NB2 de 1 op het einde geeft aan, dat deze lijn de eerste reeks is die in de grafiek geplot moet worden. We hebben maar één lijn, dus deze parameter heeft hier geen invloed.

We hebben in het vervolg van de routine alleen de reeks van de x-waarden nodig, de B6:B25.
Daarom nemen we eerst alles wat rechts van de eerste komma staat:
xVals = Mid(xVals, InStr(xVals, “,”) + 1)
en dan alles links van de volgende, dan weer eerste, komma:
xVals = Left(xVals, InStr(xVals, “,”) – 1)

xVals bevat nu  het bereik Result!$B$6:$B$25

LET OP als de naam van het tabblad zelf ook een komma bevat dan wordt de formule wel wat ingewikkelder (zie VBA).

De For-Next-loop gaat dan alle elementen van dit bereik langs en plaatst de waarde rechts van de datum in het betreffende label.

Uiteraard is deze routine ook via een knop uit te voeren op het tabblad Result van het Voorbeeldbestand.

Titel van grafiek

De titel van de grafiek is dynamisch gemaakt:

  1. in cel N2 staat de formule:  =”Bedragen van “&C2
    Oftewel koppel de inhoud van cel C2 aan de tekst Bedragen van.
  2. zorg dat de grafiek een of andere titel heeft
  3. klik ergens in de titel
  4. tik dan in de formulebalk =N2
  5. en druk op Enter

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

 

 

Tabbladen en VBA



Het werken met tabbladen in Excel is eenvoudig: je kunt ze een kleur geven (via rechtsklikken), verplaatsen (door ze met de muis “vast te pakken”), verbergen/zichtbaar maken (via rechtsklikken) etc.

Maar wat te doen, als je werkmap uit tientallen werkbladen bestaat? Dan kan het nogal wat muisklikken kosten om één van bovenstaande handelingen te verrichten.

Daarom in dit artikel enkele VBA-routines, die ons werk flink kunnen vereenvoudigen.

Overzicht van tabbladen (1)

We beginnen met het maken van een overzicht van alle tabbladen, die in een werkmap voorkomen (zie Voorbeeldbestand).
Niet alleen de naam van het tabblad plaatsen we daarin, maar ook of het tabblad verborgen is of niet, de tabkleur en de positie er van.

NB1 de kleur-code -4142 geeft aan, dat het tabblad geen kleur heeft meegekregen.

NB2 het is een goede gewoonte om tabbladen met dezelfde functie eenzelfde kleur te geven; er ontstaat dan niet zo’n kermis als in mijn voorbeeld 😉

In het tabblad Param van het Voorbeeldbestand heeft de cel met de tekst BladNamen de naam BladStart gekregen; binnen de VBA-routine zullen we daar veelvuldig naar refereren (let op de rechte haken rond een naam).

De VBA-routine BladReset genereert een nieuw overzicht:Deze routine staat in Module1 van het VBAproject BladenVBA.xlsm. Via Alt-F11 komt u in de Visual Basic editor (dit kan natuurlijk ook via de menukeuzes Ontwikkelaars/Programmacode/Visual Basic).
Een module toevoegen doet u in de editor via de menukeuzes Invoegen/Module.

Uitleg van enkele belangrijke onderdelen van bovenstaande routine:

  1. na de naam van de subroutine worden 3 variabelen gedeclareerd met het commando Dim. Deze variabelen hebben we dadelijk nodig.
    NB String betekent dat de variabele een tekst zal bevatten, Integer dat het een ‘gewoon’ getal is.
  2. om later enkele tabbladen op een aparte manier te kunnen behandelen zijn die in een Excel-tabel met de naam tblNiet opgenomen.
    In vorige versies van Excel werden tabellen Lijsten genoemd; in de VBA-syntax is nog steeds sprake van een ListObject.
    Wanneer we in VBA de verschillende cellen uit de tabel zonder meer  in een variabele zouden plaatsen, dan zou dat in de vorm van een 2-dimensionale array zijn; via de functie Application.Transpose wordt de ingelezen kolom Niet verbergen omgezet in een 1-dimensionale array.
    Met de functie Join worden de array-elementen in één tekst-variabele geplaatst met (in dit geval) als scheiding een ; (punt-komma).
    NB1 vanwege lay-out-technische redenen is de totale opdracht verdeeld  over 2 regels; dit door middel van een spatie en een underscore (_).
    NB2 we gebruiken DataBodyRange in plaats van Range omdat we de kop van de kolom niet mee willen nemen.
  3. voordat we een nieuw overzicht genereren moeten we een eventueel reeds bestaand overzicht verwijderen:
    [BladStart].Offset(1, 0).Resize([aantBladen], 4).Clear
    Vanuit de cel met de naam BladStart verschuiven (Offset) we 1 regel naar beneden. Deze selectie breiden we uit (Resize) met het aantal rijen in het bestaande overzicht; dat ligt in cel F2 van het tabblad Param vast. Deze cel heeft de naam aantBladen. De selectie moet 4 kolommen breed zijn. De inhoud van deze selectie wordt gewist (Clear).
  4. De For-Next-lus loopt dan alle tabbladen langs. De teller i loopt van 1 tot en met het aantal werkbladen.
    Eerst wordt de naam van het werkblad in rij i geplaatst (met behulp van Offset).
    Standaard is een blad niet verborgen; er wordt wel gecontroleerd of dit toch het geval is. De status (wel of niet verborgen) wordt 1 kolom naar rechts in rij i vastgelegd.
    Met behulp van de functie InStr wordt gecontroleerd op welke positie de sheet-naam in de uitzonderingslijst voorkomt; komt die niet voor dan is het resultaat gelijk aan 0. In dat geval wordt de huidige tabkleur weggeschreven; komt de naam wel voor in de uitzonderingslijst dan plaatsen we de code voor de kleur rood in het overzicht.

Om de routine makkelijk te kunnen uitvoeren is deze aan een knop gekoppeld:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  2. klik dan op het eerste symbool (de Knop)
  3. wijs met de muis de gewenste positie van de nieuwe knop aan
  4. koppel in het vervolgscherm een macro/subroutine aan de knop
  5. wijzig de tekst van de knop

Overzicht van tabbladen (2)

Overzichten in Excel zijn handiger als ze in tabel-formaat staan (zie menig ander artikel van G-Info). Of dat ook geldt wanneer we via VBA daarmee aan de slag gaan, laat ik aan u zelf over.

Om eenzelfde overzicht als hierboven in tabel-vorm te maken, moeten we eerst de kop van de tabel aanmaken. De tabel op het tabblad Param van het Voorbeeldbestand heeft de naam tblKeus gekregen.

De bijbehorende subroutine BladReset2 lijkt veel op de voorgaande. De volgende afwijkingen behoeven een toelichting:

  1. het aantal regels in het bestaande overzicht laten we niet in een cel bepalen, maar we berekenen die in VBA zelf via ListRows.Count
  2. als er regels zijn (dus aantBladOud niet gelijk aan nul) dan worden de rijen uit de DataBodyRange verwijderd (de tabel wordt dus kleiner)
  3. informatie aan het overzicht toevoegen gaat nu niet via Offset, maar door een variabele nwRij te ‘setten’; deze is van het type ListRow

Tabbladen aanpassen (1)

De overzichten zoals hiervoor aangemaakt kunnen we ook andersom gebruiken:  we veranderen de Verberg-status, de kleur of positie van een tabblad en laten VBA deze wijziging voor het betreffende tabblad uitvoeren.

De volgende opmerkingen:

  1. als het aantal bladen in de werkmap (aantBlad) niet gelijk is aan het aantal rijen in het overzicht (de cel met de naam aantBladen) dan verschijnt er een melding op het scherm en stopt de routine (Exit Sub)
    LET OP de routine controleert alleen het aantal; als de namen niet overeenkomen zal de routine daarop stuk lopen.
  2. ieder tabblad wordt eerst zichtbaar gemaakt.
    Dan, als het geen uitzondering is, en in de 2e kolom van het overzicht staat JA dan wordt het betreffende tabblad verborgen.
    NB de inhoud van de 2e kolom wordt omgezet naar hoofdletters (UCase, uppercase), dus ook ja, Ja of jA zullen een verberging tot gevolg hebben

Ook deze routine is aan een knop gekoppeld.

LET OP de volgorde veranderen van 1 of 2 tabbladen gaat op deze manier (meestal) zonder problemen. Bij heel veel wijzigingen kan het zijn, dat de volgorde niet meteen goed is. Herhaal de procedure dan nogmaals en ….

Tabbladen aanpassen (2)

Ook via de tabel tblKeus kunnen de aanpassingen doorgevoerd worden:

  1. om de routine leesbaar te houden hebben we gebruik gemaakt van de VBA-optie With-End With
    With Worksheets(“Param”).ListObjects(“tblKeus”)
    Als in de regels tussen With en End With een optie begint met een . (punt) dan weet VBA dat dit betrekking heeft op het ListObject tblKeus
  2. binnen de For-Next-lus wordt nog een keer With gebruikt. Alle .Cells-verwijzingen hebben daardoor betrekking op de Range in rij i (van tblKeus).

Kleur tabbladen

Met de toewijzing .Tab.Colorindex kunnen slechts 56 kleuren gebruikt worden (nou ja eigenlijk 57; de code -4142 zorgt er voor dat het tabblad geen kleur krijgt).

In het tabblad Param van het Voorbeeldbestand staat een overzicht van codes en bijbehorende kleuren.

 


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Voetbalcompetities



Je hoeft niet per se van voetballen te houden om dit artikel te kunnen waarderen.
Ik kreeg van Nico van der Meer een mooi Excel-systeem toegestuurd, dat hij graag met anderen wil delen: het verwerken van voetbaluitslagen tot overzichten en standen, inclusief consequenties voor nacompetities, Champions League etcetera.

In eerste instantie dacht ik: deze sheet bevat wel veel formules, daar zullen we snel wat fouten of mogelijke problemen uit kunnen halen.
Maar niets was minder waar; dus ik geef de werkmap door met maar een paar kleine aanpassingen (verbeteringen?).

Met het systeem van Nico kun je de hele competitie 2018-2019 plezier hebben; alles is al voorbereid, inclusief speeldata en zelfs tijden!
Maar, zoals uit het plaatje hierboven mag blijken, zijn dit soort competities behoorlijk onderhevig aan wijzigingen, dus voor volgend jaar zul je wel weer flink wat aanpassingen moeten doen.

Hieronder zal ik de werking van het systeem kort toelichten en ook een handreiking geven over de aanpassingen voor een volgende competitie-ronde.
Uiteraard zal ik ook de meest gebruikte Excel-functies en de gebruikte VBA-routines toelichten.

Voetbal-competitie

Voordat er zo’n mooi overzicht ontstaat moet er natuurlijk wat voorwerk verricht worden.
In het tabblad Info van het Voorbeeldbestand heeft Nico al diverse aanwijzingen gegeven.

Het systeem omvat 3 competities, Ere-, Eerste – en Tweede Divisie (de EersteDivisie heeft nu de naam KeukenKampioen).
In het tabblad Data liggen de gegevens van de competitieronde en van de teams per divisie vast. Ook kan daar aangegeven worden of de beveiliging (ter voorkoming van het overschrijven van formules) wel of niet moet worden aangezet.

Het tabblad Speelschema bevat per divisie een overzicht van alle rondes en de wedstrijden die daarbinnen gespeeld moeten worden; dit alles inclusief de betreffende datum en tijd. Deze gegevens zijn op internet terug te vinden; via de hyperlink boven iedere divisie zijn de relevante zaken te benaderen.

Klik rechts op een Hyperlink en dan Hyperlink bewerken om te zien hoe deze opgebouwd is.

LET OP om een hyperlink te bekijken moet de beveiliging van het tabblad verwijderd zijn. Dit kan via rechtsklikken op de tab onderaan of door in het tabblad Data aan te geven dat alle beveiligingen verwijderd moeten worden.

Wilt u met dit systeem de resultaten van een competitie volgen dan moeten op dit tabblad Speelschema de uitslagen handmatig ingevoerd worden (voor de Eredivisie in de kolommen C en D).

Resultaten en stand Eredivisie

In het tabblad Eredivisie van het Voorbeeldbestand worden de ingevulde wedstrijdresultaten automatisch verwerkt.

In cel G6 wordt bijvoorbeeld het aantal doelpunten opgehaald voor de thuisclub van de wedstrijd PSV – AZ Alkmaar mbv de formule
=ALS(ISLEEG(VERT.ZOEKEN(TEKST($B6;)&” – ” & TEKST($G$5;);SchemaED;2;0)); “”;VERT.ZOEKEN(TEKST($B6;)&” – “&TEKST($G$5;);SchemaED;2;0))

Via Verticaal Zoeken wordt de wedstrijd (B6, dus PSV, tegen G5, dus AZ) opgezocht in het bereik met de naam SchemaED. De inhoud van de 2e kolom wordt als resultaat teruggegeven. De derde parameter (een 0, nul) geeft aan dat een exacte zoekactie naar de wedstrijdnaam moet worden uitgevoerd.
Als de zoekactie niets oplevert (er is nog niets ingevuld) dan komt er in dit schema ook niets te staan (de dubbele aanhalingstekens), anders wordt het resultaat van de zoekactie in de cel geplaatst.

NB1 de namen van de teams worden door een spatie, een min-teken en opnieuw een spatie van elkaar gescheiden. Bij de opvoer in Speelschema moet dit ook exact zo zijn gedaan.

NB2 Nico heeft de team-namen niet rechtstreeks aan elkaar gekoppeld, maar heeft de functie Tekst gebruikt. Waarschijnlijk om er zeker van te zijn dat Excel deze betreffende cellen zeker als tekst zal zien. Naar mijn idee overbodig.

De overige formules onder en naast het schema wijzen zichzelf.

Ook de standentabel wordt automatisch ingevuld.
Via Vergelijken wordt gekeken op welke positie het betreffende team in de vorige tabel staat en dan wordt via Index het gewenste aantal winst-punten opgehaald (uit en thuis opgeteld).

De formules in de overige kolommen zijn vergelijkbaar of ‘rechttoe-rechtaan’.

In kolom BW staat een code, die gebruikt wordt om de teams in de juiste volgorde te kunnen sorteren en de Plaats in de stand te bepalen.
Wanneer namelijk nieuwe resultaten worden toegevoegd in Speelschema, dan worden wel alle berekeningen direct uitgevoerd en ook de Plaats wordt bepaald, maar de volgorde in de standenlijst verandert niet. Wil je ook de volgorde juist hebben, klik dan op de knop Standen bijwerken. De betreffende VBA-routine (zie hierna) zal dan de sortering uitvoeren.

In cel BW6 staat de formule =WAARDE( TEKST(BS6;”000″)&TEKST(100+BT6;”000″)&TEKST(BU6;”000″)&TEKST(100-BN6;”00″)&TEKST(AANTAL.ALS($BM$6:$BM$23;”>=”&BM6);”00″))

Ook hier wordt de functie Tekst gebruikt; in dit geval om zeker te weten dat het betreffende getal altijd met exact hetzelfde aantal cijfers wordt weergegeven.
Met de functie Waarde wordt van de resulterende tekst weer een getal gemaakt.

Nico heeft in een opmerking bij Code netjes vermeld hoe de code is opgebouwd.

NB bij de Eredivisie zijn na 3 speelrondes al geen gelijke standen meer; bij de KK-divisie na 2 speelrondes nog wel.

In kolom BL wordt de plaats van het team in de stand bepaald: =RANG(BW6;$BW$6:$BW$23)
Met behulp van Voorwaardelijke opmaak krijgen bepaalde Plaatsen ook nog een kleurcode; dit is dus onafhankelijk van de sortering.

Overige divisies en nacompetitie

Bij de KK-divisie heeft Nico ook nog overzichten gemaakt ten behoeve van de bepaling van de periode-kampioenen. Zover ik nu heb kunnen zien heeft hij alle lastige afwijkingen en problemen daarmee uitstekend opgelost.

Wat de nacompetitie betreft: de maker van het systeem is nog niet helemaal zeker over de juistheid daarvan.

“De nacompetitie is voor mij nog experimenteel gezien de complexe regels.
Ik had niet verwacht, dat ik tot iets zou kunnen komen, maar al werkende daaraan ontstond er toch iets”.

Nieuwe competitieronde

Wilt u dit systeem ook een volgend seizoen gebruiken, dat moet er nogal wat aangepast worden:

  1. in het tabblad Data van het Voorbeeldbestand het seizoen aanpassen
  2. ook in Data de teams in de drie divisies aanpassen (hoeven niet per se gesorteerd te zijn)
  3. op het tabblad Data de teams van de Eredivisie selecteren en Ctrl-C (kopiëren) drukken; in het tabblad Eredivisie op de cel BM6 rechts klikken en Plakken speciaal/Waarden kiezen
  4. dezelfde werkwijze hanteren voor de teams van de eerste en tweede divisie
    LET OP bij de eerste divisie moeten de teams vijf keer geplakt worden.
  5. in het tabblad Speelschema alle uitslagen wissen en de wedstrijden die gespeeld zullen gaan worden overschrijven.
    LET OP zorg dat de namen van de clubs exact gelijk zijn aan die in het tabblad Data en dat de 2 ploegen gescheiden worden door een spatie, min-teken en nog een spatie

VBA

Met behulp van de knoppen Standen bijwerken worden bijbehorende VBA-routines uitgevoerd.
Voor de Eredivisie ziet die er als volgt uit:
Het bereik met de naam StandEredivisie wordt gesorteerd op kolom BW  en wel in Aflopende volgorde (grootste getal eerst) en het bereik heeft geen Kop.

Alle andere sorteringen gaan op dezelfde manier. Bij de KK-divisie worden 5 sorteringen achter elkaar uitgevoerd; dus voor alle schema’s van het betreffende tabblad.

In het Excel-object ThisWorkbook staat de volgende routine:

De For-Next-loop loopt alle tabbladen langs en afhankelijk van de inhoud van de cel met de naam BevJN (deze staat in het tabblad Data) wordt de beveiliging aan of uit gezet (zonder wachtwoord).

Application.ScreenUpdating zorgt er voor, dat het scherm niet gaat ‘flikkeren’ bij het selecteren van een tabblad.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: