Categorie archief: VBA (Visual Basic for Applications)

Analoge klok in Excel

9 november 2015Excel, VBA (Visual Basic for Applications)NU, PI, Rest, SpreidingsgrafiekGijs Verbruggen

Iedereen die wel eens in Praag is geweest, heeft waarschijnlijk wel een paar keer stil gestaan bij het astronomische uurwerk in het centrum.
Zeker wanneer op het hele uur de 12 apostelen zich vertonen, is het een drukte van jewelste op het Oudestadsplein.

Daar stond ik dus ook, toen we een paar weken geleden een bezoek brachten aan deze prachtige stad.

Het bloed kruipt waar het niet gaan kan: ik dacht meteen, dat moet in Excel na te bouwen zijn!
Maar om alle facetten van de klok te implementeren valt onder de grotere uitdagingen; laten we maar eerst beginnen om een ‘gewone’, analoge klok in Excel op te zetten.

Basis

Uiteraard hebben we de huidige tijd nodig, waar we de klok op gaan baseren: in Excel gebruiken we daar de formule =NU() voor; in het Voorbeeldbestand staat die in cel C2.
LET OP dit is één van de weinige formules, die geen paramaters kent, maar om voor Excel duidelijk te maken dat het een formule betreft zijn wel de 2 haakjes nodig.

LET OP 2 tijdens het schrijven van het artikel veranderde de inhoud van cel C2 constant. In de volgende voorbeelden komen de getallen dan ook niet meer overeen met de tijd hierboven.

Uit de tijd leiden we het uur, de minuten en de secondes af met de formule =UUR(C2), =MINUUT(C2) respectievelijk =SECONDE(C2).
Omdat we een analoge klok gaan maken, die maar 12 uren kent, staat in cel E3 de formule =REST(E2;12). Hiermee berekenen we de restwaarde als we de uren van cel E2 delen door 12. Uiteraard had dit ook met de formule = ALS(E2>12;E2-12;E2) gekund, maar de eerste formule is korter (en interessanter!?).

Wijzers

Maar hoe gaan we nu die wijzers tekenen in Excel?
Daartoe moeten we ons realiseren, dat de tijd (beter gezegd het aantal seconden na 12 uur) de hoek van de wijzers bepaalt.

We gaan eerst de kleine wijzer tekenen:

het aantal seconden na 12 uur berekenen we in cel C9: =(E3*60+H3)*60+K3
om hierna de hoek te kunnen bepalen, moeten we weten hoeveel seconden er maximaal in 24 uur zitten; zie cel C10
door de inhoud van cel C9 te delen door die van cel C10, weten we welk gedeelte van de klok door de kleine wijzer is afgelegd (cel C11)
de zo berekende fractie moeten we nog in een hoek vertalen: in C12 komt de formule =C11*360, omdat een hele cirkel 360 graden is.

Nu we de hoek weten, moeten we dit gaan omzetten naar een punt op de klok (cirkel). Gelukkig hebben we vroeger allemaal goed opgelet bij wiskunde en weten natuurlijk(?) nog dat de x-coördinaat van zo’n punt wordt berekend door de cosinus van de hoek te nemen en de y-coördinaat door de sinus.
Maar we zijn nog niet klaar: in de wiskunde (en in Excel) wordt een hoek bepaald ‘rekenend tegen de klok in’; voor ons rekenwerk gebruiken we dan ook de cosinus en sinus van -C12.
Omdat Excel als basis voor de hoek de positieve x-as neemt (een hoek van 0 graden komt dus overeen met een horizontale lijn naar rechts) en we 0 uur netjes bovenaan willen laten beginnen tellen we bij onze berekende hoek nog 90 graden op.
Daarmee zou de x-coördinaat van de kleine wijzer dus worden =COS(-C12+90). Helaas Excel kent ons graden-systeem niet, maar werkt met radialen (jaja, de middelbare schooltijd is toch niet voor niets geweest!). Aangezien 360 graden overeen komt met 2π radialen, delen we onze berekende graden door 360 en vermenigvuldigen met 2π; de x-coördinaat wordt nu =COS((-C12+90)*2*PI()/360).
LET OP ook de functie PI kent geen parameters, maar de 2 haakjes zijn wel nodig.

Hetzelfde verhaal geldt ook voor de y-coördinaat: =SIN((-C12+90)*2*PI()/360).
Maar het is een kleine wijzer: dus we vermenigvuldigen de coördinaten nog met bijvoorbeeld 0,6 (staat in cel C13 en is dus makkelijk aanpasbaar).
Door nu een lijn te tekenen (en daar wat opmaak aan mee te geven) van de oorsprong van het assenstelsel (met de coördinaten 0,0) naar de zo berekende coördinaten hebben we onze wijzer. Gebruik daarvoor geen lijngrafiek maar een spreidingsgrafiek.

NB Bij een lijngrafiek komen de elementen op de x-as allemaal op dezelfde afstand van elkaar; bij een spreidingsgrafiek geef je ook de exacte positie op de x-as mee, zodat je precies kunt aangeven waar het resultaat terecht zal komen. Een spreidingsgrafiek wordt bijvoorbeeld gebruikt als op de x-as datums worden uitgezet, waarbij niet iedere datum in de bron-gegevens voorkomt. Bij een lijngrafiek worden de datums ‘gewoon’ naast elkaar geplaatst; bij een spreidingsgrafiek heeft iedere datum zijn eigen positie op de x-as en zie je onmiddellijk waar er datums ontbreken.

Het tekenen van de grote en de seconde-wijzer gaat op dezelfde manier. Bedenk daarbij dat het maximale aantal seconden voor de grote wijzer maar 3600 is (60 minuten) en voor de secondewijzer slechts 60.

Om onze klok nog verder aan te kleden, zetten we ook de uren er op: op dezelfde manier als voor de wijzers bepalen we de hoeken en daarmee de coördinaten. De lijnen die daardoor ontstaan maken we ‘onzichtbaar’ (bij Lijnkleur de optie Geen lijn aanvinken); wel geven we de lijnen een label mee, et voilà.

Nu nog even op F9 drukken (Herberekenen) en de tijd in cel C2 en dus op de klok wordt geactualiseerd.

Automatische klok

Om iedere keer op F9 te drukken wanneer we de tijd willen weten, is natuurlijk niet te bedoeling.
Daarom nog een stukje VBA er achteraan (opgeslagen in zogenaamde macro’s, vandaar dat het Voorbeeldbestand de extensie xlsm heeft):

druk op Alt-F11 (openen van de Visual Basic-editor)
kies Invoegen en dan Module
in de module voegen we een globale variabele toe (die is dus voor het gehele project geldig), waarmee we onthouden of de klok moet lopen of niet:
Global KlokAan As Boolean
dan een kleine VBA-routine:
Sub KlokActief()
    If KlokAan Then
        Application.OnTime Now + TimeValue(“00:00:01”), “KlokActief”
        Application.Calculate
    End If
End Sub
Wanneer deze routine aangeroepen wordt, wordt allereerst gecontroleerd of de variabele KlokAan Waar is (True). Zo ja dan wordt er voor gezorgd dat 1 seconde later dezelfde routine opnieuw wordt opgestart; daarna wordt de herberekening uitgevoerd.
Om de klok te kunnen starten (en ook weer uit te zetten) voegen we nog een routine toe:
Sub KlokAanUit()
    If KlokAan Then
        KlokAan = False
    Else
        KlokAan = True
        KlokActief
    End If
End Sub
Als de klok aan staat, wordt die uitgezet en anders gaat de klok aan en wordt de routine KlokActief aangeroepen (die er zelf voor zorgt, dat hij iedere seconde wordt uitgevoerd, zolang KlokAan Waar is).
nog een button toevoegen, die verwijst naar de macro KlokAanUit en we zijn klaar:
ga in Excel naar de menu-optie Ontwikkelaars (eventueel eerst nog activeren via Bestand/Opties/Lint aanpassen en daar rechts bij Hoofdtabbladen een vinkje aan zetten bij Ontwikkelaars) en kies in het blok Besturingselementen de optie Invoegen en kies het eerste symbool, de Knop.

Mastermind

10 juni 2015Excel, VBA (Visual Basic for Applications)Array, Gegevensvalidatie, Index, Knop, Matrix, MsgBox, Namen, voorwaardelijke opmaakGijs Verbruggen

Mastermind! Wie kent het niet?

Dit is een spel, dat waarschijnlijk iedereen wel eens ooit heeft gespeeld.
Ik moest er aan denken, toen ik bij een (gedwongen) opruimronde op zolder één van mijn eerste computers, een TRS-80, tegenkwam.

Nostalgie: ongeveer 40 jaar geleden is mijn “computer-verslaving” begonnen: eerst met een TRS-80 (van de firma Radio Shack) met een intern geheugen van 4Kb en een cassetterecorder als opslagmedium, al vrij snel met een geheugenuitbreiding tot 16Kb.
Tig jaar later was de luxe niet te overzien: een modern apparaat met 2 diskette-stations en 16Mb intern geheugen!
Zo’n diskette kon wel 500 Kb aan data bevatten.
En het gemak: na een avond lang programmeren, gewoon op disk wegschrijven; geen angst meer, dat het niet goed op de cassette was terecht gekomen, waar je pas achter kwam als het te laat was (ik geloof, dat ik in die periode pas echt heb leren vloeken!).

Maar even terug naar het onderwerp: Mastermind.
Ik kwam bij het opruimen nog aantekeningen tegen uit die eerste TRS-80-periode: de opzet voor een computer-variant van dit spel; waarschijnlijk gemaakt, zodat ik ook zonder echte tegenstander aan een andere verslaving toe kon komen: spelletjes spelen!

Mastermind in Excel

Een uitdaging dus om te kijken of we Mastermind ook in Excel zouden kunnen spelen. Het kostte toch nog wel een paar avonden ploeteren, maar het is gelukt.

Kijk maar eens in het Voorbeeldbestand.

Als je op de knop Nieuwe ronde drukt, zal Excel 4 kleuren kiezen uit een serie van 8 (dubbelen zijn toegestaan).
Aan jou, als speler, om in de cellen achter Ronde 1, je eerste gok te plaatsen.
Druk dan op de knop Controleer en onder Resultaat komt het resultaat (sic!).
Z(wart) betekent dat er een kleur goed is gekozen EN op de juiste plaats, W(it) geeft aan dat er een kleur goed is, maar niet op de juiste positie.
Vul dan Ronde 2 in, druk op Controleer etc.

Mijn beste spelronde tot nu toe was: geraden in 4 keer binnen 40 seconden. Ben benieuwd naar jullie resultaten!

Hoe werkt het programma?

Dit artikel en het Voorbeeldbestand zijn deze keer vooral ook voor de fun; dus maar een korte, compacte toelichting.
Geïnteresseerden in VBA vinden in Module1 (te bereiken via Alt-F11) de achterliggende routines. Vooral interessant is daar de combinatie tussen variabelen binnen VBA (in de vorm van een array/matrix) en celbereiken in de Excel-sheets.

Op het tabblad Parameters staan de 8 kleuren, waaruit Excel en jij, als speler, mogen kiezen; deze cellen hebben de naam Kleuren gekregen.

Wanneer Excel de opdracht krijgt om aan een Nieuwe ronde te beginnen, dan worden op het tabblad CompKeuze de cellen B3:E3 van willekeurige getallen tussen 1 en 8 voorzien; in de rij daaronder komen dan automatisch de overeenkomende kleuren.

NB wel eerlijk spelen; dus niet gauw hier kijken wat de combinatie is!

Het tabblad Raden bevat het “spelbord”.

Excel-opties

In het Voorbeeldbestand wordt veel met Namen gewerkt om verwijzingen overzichtelijk te houden. Daarnaast zorgt Voorwaardelijke opmaak voor de juiste kleuren. De functie Index wordt her en der gebruikt om kleuren op te zoeken.

En uiteraard wordt er gebruik gemaakt van VBA-routines (Visual Basic for Applications), aangestuurd door knoppen.

Heb je vragen over één van deze toepassingen en het gebruik binnen Mastermind: schroom niet om contact op te nemen met G-Info!

Urenregistratie

13 mei 2015Excel, VBA (Visual Basic for Applications)ALS.FOUT, Datum, Index, Opmaak, Tabel, Tijd, Vergelijken, Verwijzingen, ZoekenGijs Verbruggen

Hoewel een echte prikklok niet vaak meer wordt gebruikt, wordt bij veel bedrijven nog steeds gebruik gemaakt van een bepaalde manier van tijdregistratie.
Dit kan nodig zijn om binnen het bedrijf een kostenallocatiemodel te voeden of om als externe inhuur verantwoording af te leggen over de in rekening gebrachte uren, enzovoort.

Daarom in dit artikel aandacht voor Excel als hulpmiddel voor urenregistratie; inclusief wat VBA om het gebruik wat makkelijker te maken.

Basis

Het is een goede gewoonte om bij de opbouw van een Excel-systeem een scheiding aan te brengen tussen de diverse onderdelen; in dit geval hebben we dan ook de invoer van de gemaakte uren en de rapportage daarover in aparte tabbladen opgenomen.

In het Voorbeeldbestand is dit verder uitgewerkt.
De registratie en rapportage zijn geen complexe items, maar we kunnen wel diverse handige Excel-trucjes gebruiken om het systeem flexibeler en fraaier te maken.

Laten we de kolommen in het tabblad Invoer eens langs lopen:

in kolom A komt de datum: in cel A4 staat de eerste datum waarop de registratie is begonnen; in A5 staat de formule =A4+1; in A6 de formule =A5+1 etc.
Het voordeel hiervan is, dat als we met een nieuwe registratie willen beginnen, we alleen de datum in cel A4 hoeven te wijzigen
voor een snel inzicht tonen we in kolom B de dag van de week; niet via een Excel-functie Weekdag (die geeft alleen het volgnummer in de week) maar door opmaak.
De formule in B4 is =A4.
NB De invoer is als tabel in Excel opgezet; dat houdt onder andere in, dat als je zo’n formule in B4 intypt deze automatisch in de rest van de kolom wordt overgenomen (maar wel met een verwijzing naar A5, A6 etc).
Via de celeigenschappen hebben de cellen in kolom B een speciale datumopmaak meegekregen, namelijk dddd. Deze zorgt er voor, dat de datum als volledige weekdag wordt weergegeven.
NB experimenteer met het aantal d’s en kijk wat dit voor invloed heeft op de opmaak.
in kolom C wordt per dag de begintijd ingevoerd
we gaan er van uit, dat een dag uit 2 blokken bestaat; in D komt dan de eindtijd van blok1, in E een eventueel begin van blok2 en in F een eventueel einde.
De opmaak van de kolommen C, D, E en F is u:mm (dus minstens 1 positie voor het uur en altijd 2 voor de minuten).
dan wordt het spannender: in kolom G komt een formule, die de gewerkte tijd (nou ja, de aanwezigheid) berekent. Dit gebeurt in 2 gedeeltes: in de eerste Als berekenen we het verschil tussen begin- en eindtijd van het eerste blok (als er nog geen eindtijd is (D4=””) dan maken we dat verschil gelijk aan 0); en we tellen daar het resultaat van de tweede Als bij op, die een eventueel verschil van blok2 bepaalt.
Ook deze kolom krijgt als opmaak u:mm, omdat we natuurlijk het resultaat in uren en minuten willen weten.
Wanneer het resultaat nul is (in het weekend of op andere (nog) niet gewerkte dagen), willen we in kolom G niets zien: de opmaak is dan ook uitgebreid met een extra voorwaarde:
Kolom H bevat ook het aantal uren, maar nu uitgedrukt als een decimaal getal. Om de gehanteerde formule in die kolom te begrijpen, moet je weten, dat Excel een tijd als een deel van een gehele dag opslaat: 24 uur is het getal 1, 12 uur is 1/2, 6 uur wordt vastgelegd als 1/4 etc.
Andersom: willen we de tijd uit kolom G (die dus als fractie van een hele dag is opgeslagen) weergeven als uren, dan moeten we die tijd met 24 vermenigvuldigen. Aangezien we met een Excel-tabel werken, wordt dit via een zogenaamde gestructureerde verwijzing in de formule weergegeven:
(Op tabellen en gestructureerde verwijzingen zal ik een andere keer terugkomen)
in kolom I kunnen (relevante) opmerkingen, die de registratie verduidelijken, worden opgenomen.

Rapportage

De maandrapportage van de tijdsbesteding is in het Voorbeeldbestand in het tabblad MndOvz opgenomen.
Door de datum van de eerste van een maand op te geven, worden op deze pagina de bij die maand behorende gegevens uit de database opgehaald. Hierbij wordt een alternatief voor verticaal zoeken gebruikt.

van de bovenste 6 rijen is alleen cel D5 echt van belang: deze geeft aan van welke maand de gegevens worden weergegeven (de eerste van de maand moet worden ingetikt; de opmaak laat alleen maar maand en jaar zien).
De rest is verfraaiing/toelichting.
in kolom B staan vanaf regel 11 de dagen van de betreffende maand weergegeven; althans het volgnummer van de dagen.
in kolom C staat de werkelijke dag, via de formule =$D$5+B11-1 (dus bij de eerste van de maand (D5) wordt het volgnummer opgeteld; omdat we dan altijd 1 dag te ver uitkomen trekken we er nog 1 vanaf).
NB1 Kolom C is niet verborgen, maar via groepering ‘dichtgeklapt’. Klik op het +-teken boven D om kolom C zichtbaar te maken. Groeperen zit in de menutab Gegevens, in het blok Overzicht.
NB2 aangezien we natuurlijk alleen maar datums uit de betreffende maand willen zien (en bijvoorbeeld geen 31 april) is de formule vanaf C12 iets ingewikkelder.
laten we dan eens kijken wat de formule in D11 doet:
=ALS.FOUT(INDEX(Uren;VERGELIJKEN($C11;Uren[Datum];0);D$10);””)
Uren is de naam van de tabel uit het invoerblad.
Uren[Datum] is de datum-kolom in die tabel.
VERGELIJKEN($C11;Uren[Datum];0) kijkt op welke plaats de waarde uit C11 (in dit geval dus 1 april) in die kolom staat; de 0 zorgt er voor, dat Excel de waarde zoekt, onafhankelijk in welke volgorde die ook zouden staan (een exacte match dus).
INDEX(Uren; ‘plaats van datum’ ;D$10) geeft de waarde van díe cel in de tabel Uren, die in de rij ‘plaats van datum’ staat en in de kolom, die overeenkomt met de waarde in cel D10.
ALS.FOUT geeft een lege waarde (“”) als één van de formules INDEX of VERGELIJKEN een fout oplevert (bijvoorbeeld als de datum uit C11 niet in de tabel Uren voorkomt).
NB in het overzicht staat ook een dichtgeklapte rij 10; daar staat in welke kolom Index moet zoeken.
de overige cellen in het overzicht zijn op eenzelfde manier opgezet
onderaan staat nog een totaaltelling: in cel I42 worden de ‘decimale’ uren opgeteld. In H42 de ‘normale’ uren en minuten; wanneer het aantal uren boven de 24 komt, zal Excel standaard weer opnieuw bij 0 beginnen. Willen we die uren boven de 24 zichtbaar maken dan dienen we de betreffende cel een andere opmaak mee te geven: . Let op de vierkante haken!

VBA

Om het gebruik van het spreadsheet wat te vergemakkelijken is nog een VBA-routine toegevoegd.
De bedoeling van de routine is om bij het openen van het bestand de cursor op de juiste plaats te hebben staan om snel nieuwe invoer te kunnen doen.

De VBA-routine gaat automatisch naar het Invoer-blad en zoekt de regel op net onder de laatste invoer.

VBA-routines worden veelal opgeslagen in zogenaamde Modules; omdat deze routine direct actief moet worden wanneer de werkmap (in het Engels Workbook) wordt geopend staat deze routine in de map ThisWorkbook en heeft de naam Workbook_Open gekregen.

Laten we de routine even stapsgewijs doorlopen:

open het Voorbeeldbestand
ga naar Visual Basic (bijvoorbeeld via de toetscombinatie Alt-F11)
dubbelklik op de map ThisWorkbook
de routine begint met wat toelichtende commentaarregels (de groene regels na de apostrof)
dan worden 2 variabelen gedeclareerd, die we hierna nodig hebben (strGebrNaam en strDagDeel)
de 2 variabelen worden gevuld; de bedoeling lijkt me duidelijk
en dan het ‘echte’ werk:

Eerst selecteren we het tabblad (de sheet) Invoer; dan selecteren we in de kop (Header) van de tabel Uren de cel met het woord Datum.
Vervolgens wordt de toets-combinatie Ctrl-Pijl-naar-beneden nagebootst en dus de onderste gevulde datum geselecteerd.
Dan 2 kolommen naar rechts om in de kolom Begin1 te komen, waar we via Ctrl-Pijl-naar-boven de laatst gevulde cel zoeken.
Die is al gevuld, dus selecteren we de cel daaronder.
Als laatste wordt een pop-up op het scherm getoond.
NB vbCrLf is de code, die er voor zorgt dat de volgende tekst, die via het &-teken aan het voorgaande wordt ‘geplakt’, 1 regel lager zal komen (een zogenaamde harde-return). Zie Teksten samenvoegen voor uitleg.

Als de VBA-routine niet duidelijk is, laat dan commentaar of een vraag achter op de website.

Teksten samenvoegen

28 maart 2015Excel, VBA (Visual Basic for Applications)Datum, eigen functie, Namen, Tekst, Tekst.SamenvoegenGijs Verbruggen

Het is weer tijd voor het doen van aangifte voor de belastingen. Dan is het altijd handig om Excel achter de hand te hebben om bedragen op te tellen etc.
Maar wist u, dat Excel ook teksten kan ‘optellen’? Nou ja, beter gezegd samenvoegen.

Het komt in Excel nogal eens voor, dat de inhoud van verschillende cellen moet worden samengevoegd.
Zeker wanneer de gegevens uit een ander systeem of uit een Pdf-bestand worden overgenomen, komen bijvoorbeeld dubbele voor- en achternamen vaak in verschillende cellen terecht.

TEKST.SAMENVOEGEN

In het Voorbeeldbestand staat op het werkblad Namen een klein lijstje namen. Al snel is te zien dat de regels 4, 6 en 7 nog aanpassing nodig hebben.

Gelukkig heeft Excel een ingebouwde functie voor het samenvoegen van teksten: TEKST.SAMENVOEGEN (klinkt logisch, toch?).

Op basis van de geïmporteerde namen maken we als volgt een nieuw overzicht:

iets onder het bestaande overzicht (in het voorbeeld in cel B10) verwijzen we naar de eerste cel van de kop door in te tikken =B2
deze formule kopiëren we één cel naar rechts en net zo vaak naar beneden als er regels zijn in het oorspronkelijke bestand. Gebruik hiervoor de vulgreep van cel B10; zie het artikel Doorvoeren en sorteren.
de meeste cellen zijn natuurlijk meteen goed, maar bijvoorbeeld de tweede naam kan nog wel een aanpassing gebruiken. In de betreffende cel (in het voorbeeld B11) moeten de 2 voornamen gecombineerd worden: =TEKST.SAMENVOEGEN(B4;C4)
het resultaat is niet precies wat we willen; er moet nog een spatie tussen de 2 voornamen: =TEKST.SAMENVOEGEN(B4;” “;C4)
deze formule kopiëren we naar alle cellen waar namen moeten worden samengevoegd (in het voorbeeld B13, B14 en C14)
maar nu kloppen de achternamen van Marie Jose nog niet; de formule voegt de verkeerde cellen samen. Dit lossen we snel op:
* selecteer de betreffende cel (in dit geval C13)
* klik in de formulebalk of druk op F2 om de formule aan te passen
* de cellen, die in de formule gebruikt worden hebben dan een gekleurde rand, overeenkomend met de celverwijzing in de formule:
* ‘pak’ met de muis de groene rand vast en sleep 1 cel naar rechts
* idem met de blauwe rand; door dit slepen wordt de formule automatisch aangepast
de achternaam van de tweede persoon is nog niet juist; de celverwijzing dient nog aangepast te worden.

Is het bestand veel groter, dan moet per regel bekeken worden welke formule waar moet komen; het handigste gaat dit door de 2 cellen van de tweede naam te Kopiëren en overal waar dezelfde situatie zich voordoet te Plakken. Idem voor de vierde en de vijfde naam (dan hebben we alle mogelijkheden gehad).

Alternatieve samenvoeging

Waar we in Excel een optelling van getallen regelen via het +-teken, kunnen we ook teksten ‘optellen’. Alleen wordt daar een ander teken voor gebruikt: & (ampersand; zie het interessante artikel op Wikipedia).

Een alternatieve (simpelere) manier om de naam Jan Willem te maken krijgen we dan als volgt: =B4&” “&C4

Zie verder het Voorbeeldbestand.

Flexibele overzichten

Tekst-samenvoegingen kunnen goed gebruikt worden om rapportages/overzichten flexibeler te maken.
Hoe vaak gebeurt het niet dat je een overzicht van een vorig jaar of vorige maand kopieert en vergeet om alle ‘harde’ jaar- en maand-aanduiding aan te passen.

Het is dan ook een goede gewoonte om harde gegevens (vaak parameters genoemd) apart vast te leggen en daar in formules naar te verwijzen.

In het Voorbeeldbestand op het werkblad Parameters is dit in het kort uitgewerkt:

leg het jaar vast in cel C2 en de maand in C3
typ de volgende formule in cel B7:
=”Overzicht van “&C3&” “&C2
(denk aan de spaties op de juiste plaatsen!)
verander een input-variabele en bekijk het resultaat.

In plaats van 2 variabelen kunnen we ook eenzelfde resultaat bereiken door uit te gaan van één datum (cel C4).
In cel F7 voeren we de volgende formule in: =”Overzicht van “&C4
Helaas, het resultaat is niet precies wat we verwachten! Excel “vergeet” nu dat we te maken hebben met een datum. We moeten zelf nog wat denkwerk verrichten: =”Overzicht van “&TEKST(C4;”mmmm jjjj”). Door opmaak aan de broncel mee te geven, krijgen we wel het gehoopte resultaat.

NB1 omdat we weten, dat cel C4 een datum voorstelt, geven we die cel met behulp van de functie TEKST een datum-opmaak; ‘speel’ met de notatie-instellingen: het aantal m‘s, zet er ook eens een d in etc.

NB2 met dezelfde functie TEKST kan ook de opmaak van een getal worden aangepast. De opmaak volgt dezelfde notatie als bij Celeigenschappen.

De formules in de Excel-sheet worden beter leesbaar wanneer de cellen met parameters een Naam krijgen. Geef je aan cel C2 de naam Jaar mee en aan C3 de naam Maand dan wordt de formule in B7 =”Overzicht van “&Maand&” “&Jaar
Zeker wanneer verwijzingen zich op andere werkbladen bevinden is een dergelijke formule veel duidelijker.

In het kader van flexibiliteit: om bij een volgende maand niet allerlei wijzigingen te hoeven doorvoeren, moeten de datums natuurlijk ook afhankelijk zijn van een parameter:
via de functies DATUM, JAAR en MAAND wordt gebruik gemaakt van de waarde in cel C4.
NB met DATUM zijn allerlei mogelijkheden denkbaar. Voorbeeld: wil je de laatste dag hebben van de maand, waarin de datum in C4 valt?
=DATUM(JAAR(C4);MAAND(C4)+1;1)-1
ofwel maak een datum door het jaar van C4 te nemen, de maand van C4 plus 1 (de volgende maand dus) en de eerste dag. Door dan van het resultaat 1 af te trekken weten we zeker dat we op de laatste van de betreffende maand uitkomen!

Eigen functies

In het eerste voorbeeld waren we al even bezig om alle namen goed te krijgen, ook al betrof het maar een bestandje met enkele regels.
Zeker wanneer het meer regels betreft en met meer variaties dan wordt het netjes samenvoegen van teksten een behoorlijk karwei.

In het Voorbeeldbestand heb ik in het tabblad Tennis een overzicht opgenomen van een tenniscompetitie, zoals die binnen is gehaald vanuit een PDF. Zoals te zien is moet er flink ‘gepoetst’ worden. Voor dit soort werk heb ik een eigen TekstSamenvoeg-functie geschreven. Laten we die eerst aan het werk zetten:

voeg op de plaats van kolom C een nieuwe kolom in
in de nieuwe cel C3 plaatsen we de volgende functie:
=TekstSamenvoegenRange(D3:E3)
kopieer cel C3 naar beneden (dubbelklik op de vulgreep van cel C3)
ook nu klopt nog niet alles: in regel 7 moeten 3 cellen samengevoegd worden. Daar moet de formule dus worden: =TekstSamenvoegenRange(D7:F7)
kopieer deze cel naar alle regels waar drie cellen moeten worden samengevoegd
selecteer cel D2, ‘pak’ de rand vast en sleep naar C2
selecteer de hele kolom C (klik op de C boven de kolom)
kies kopiëren en daarna direct Plakken/waarden; op deze manier is de inhoud van kolom C ‘hard’ gemaakt
voeg op de plaats van kolom D een nieuwe kolom in
in de nieuwe cel D3 plaatsen we de volgende functie:
=TekstSamenvoegenRange(G3:I3)
kopieer cel D3 naar beneden (dubbelklik op de vulgreep van cel D3)
op verschillende plaatsen klopt het nu niet, bijvoorbeeld in regel 4. Selecteer cel D4 en druk op de functietoets F2. De cellen G4 t/m I4 hebben nu een blauwe rand. Pak de hoekpunt linksonder en sleep deze één cel naar rechts en druk op Enter.
pas op dezelfde manier de overige formules aan, waar dat nodig is.
sleep het kopje uit cel F2 naar D2
maak kolom D hard en verwijder alle kolommen E t/m J

De VBA-routine van deze functie is te vinden in Module1 van het Voorbeeldbestand. Druk daartoe op Alt-F11 (dus de Alt-toets inhouden en op de functietoets F11 drukken). De commentaarregels (groene tekst) lichten de bedoeling van de functie en de diverse stappen toe.

De functie kent een optionele parameter, de KoppelTekst (die hoeft dus niet ingevuld te worden; in dat geval wordt hier dan een spatie gekozen).
wanneer we in cel C3 hadden ingevoerd =TekstSamenvoegenRange(D3:E3;” “) dan was het resultaat dus hetzelfde geweest (denk aan de aanhalingstekens om de spatie!). Maar het had ook =TekstSamenvoegenRange(D3:E3;” | “) kunnen zijn etc.
Door =TekstSamenvoegenRange(D3:E3;TEKEN(10)) worden de elementen gescheiden door een ‘harde-return’ (ofwel nieuwe-regel-opdracht). Vink bij Celeigenschappen/Uitlijning dan wel de optie Terugloop aan.

Ik heb nog een andere functie toegevoegd: TekstSamenvoegenTeller.
Kies opnieuw het tabblad Tennis van het originele Voorbeeldbestand en voer het volgende uit:

voeg op de plaats van kolom C TWEE nieuwe kolommen in
in cel D3 tikken we het getal 2 in; in de nieuwe cel C3 plaatsen we de volgende functie:
=TekstSamenvoegenTeller(E3;D3)
kopieer de cellen C3 en D3 naar beneden
in sommige cellen zal de waarde 2 gewijzigd moeten worden in 3

Nijntje Schuif 2.0

29 oktober 2014Excel, VBA (Visual Basic for Applications)Namen, SelectGijs Verbruggen

Deze keer een vreemde Excel-toepassing; het bekende schuifspelletje: schuif vakjes op en neer net zolang totdat het oorspronkelijke plaatje tevoorschijn komt.

Net zo vreemd als de toepassing is ook de aanleiding: op een feestje, een paar weken geleden, kwam ik na lange tijd een vriend van een zoon van mij tegen en zoals het dan gaat begonnen we herinneringen op te halen.
We kregen het ook over onze gezamenlijke programmeer-activiteiten: meer dan 15 jaar terug hadden we als LKG-software (Ludo, Koen, Gijs) een website waar spelletjes konden worden gedownload. Deze hadden allemaal als thema het bekende Nijntje.
Eén van de spelletjes was het verschuiven van vierkantjes op het scherm, zodanig dat er een mooi plaatje ontstond (uiteraard met het Nijntje-figuur; vanuit royalty-problemen mogen we die helaas niet meer gebruiken, maar de naam Nijntje Schuif laten we nog voortleven!).

Het leek me wel een uitdaging om dit in Excel na te bouwen.
Nou, een uitdaging was het! Qua principe had ik een prototype snel aan de praat, maar om dit leuk met plaatjes te doen, dat viel nog niet mee.

Deze keer eerst een opzet waarbij het principe en de programmeersystematiek van het schuifspel duidelijk wordt; een volgende keer een mooiere uitvoering.

Nijntje Schuif 2.0

De basis voor deze eerste versie van de moderne uitvoering van Nijntje Schuif vormen de 16 cellen C4:F7.
Dit blokje cellen heeft de naam cNijn gekregen, zodat we daar binnen VBA makkelijk naar kunnen verwijzen.

Verder heb ik ook de cellen I7 en I8 een naam gegeven: cReset respectievelijk cNwRonde. Deze cellen gebruiken we om de cijfers direct in de juiste volgorde te zetten (een Reset dus) en om een nieuw spelletje te beginnen (de cijfers worden door elkaar gehutseld).
Om een VBA-routine (een macro) te starten wordt meestal een button gebruikt; deze keer gebruik ik daarvoor cellen. Wanneer op de cellen I7 of I8 geklikt wordt (of met de cursor deze cellen wordt geselecteerd) dan start de betreffende routine op.
Ditzelfde idee (reageren op het veranderen van de selectie in een werkblad) gebruiken we ook om cijfers in het vierkant te verplaatsen. Klik op een genummerde cel en het programma zal controleren of deze cel verwisseld kan worden met de lege.

De mogelijkheid om met behulp van VBA te reageren op een selectiewijziging kan uiteraard voor diverse doeleinden gebruikt worden.

VBA-reactie bij wijziging van selectie

Belangrijk is dat een dergelijke routine (Sub Worksheet_SelectionChange) aan het betreffende werkblad wordt toegevoegd (in dit geval het tabblad Nijntje).

In het voorbeeld hierboven zal een Reset worden uitgevoerd, wanneer de cel die geselecteerd wordt (de Target) overeenkomt met de cel cReset.
Misschien wat vreemd geconstrueerd (als de overlap tussen Target en cReset niet leeg is), maar ik zie geen andere oplossing.

Op dezelfde manier wordt gecontroleerd of er een nieuwe speelronde moet worden opgestart (via de cel cNwRonde). In dat geval wordt een andere routine in de module NijnSchuif uitgevoerd, namelijk NieuweRonde.

‘Verplaatsen’ van een vierkantje

Op dezelfde manier testen we of één van de 16 vierkantjes van cNijn wordt geselecteerd:

Als dat zo is, dan leggen we de positie vast in intRij en intKolom en controleren dan eerst in de array/matrix Nijntje of de betreffende cel leeg is; zo ja dan moet die cel even rood gekleurd worden en verder niets.

Hopelijk is de uitleg in het voorbeeldbestand genoeg om de overige controles te begrijpen.

Variabelen declareren

In diverse routines worden variabelen gebruikt om tussenwaarden op te slaan, die voor de werking van de routine nodig zijn. Het is een goede gewoonte om deze vooraan in de routine te declareren met behulp van het statement Dim. Deze variabelen zijn dan alleen geldig in de betreffende routine.

Voor de goede werking van ons NijntjeSchuif-programma is het echter ook nodig, dat sommige variabelen door alle routines kunnen worden gebruikt (bijvoorbeeld in welke rij staat de lege cel, intLegeCelRij).
Deze variabelen worden niet door Dim gedeclareerd, maar door Public ergens ‘buiten’ de routines; in dit geval in de module Algemeen.

Opstart-routine

Om ons spel te kunnen gebruiken is het van belang, dat diverse variabelen een waarde hebben. De routine Reset neemt dit allemaal voor zijn rekening.
Om zeker te weten, dat het spel meteen gespeeld kan worden (nadat een Nieuwe ronde is gekozen), zorgen we dat de reset-routine direct wordt uitgevoerd wanneer we de werkmap openen.
Dit gebeurt door in de subroutine Workbook_Open de reset-opdracht op te nemen.

LET OP: deze subroutine moet u altijd plaatsen in het speciale Excel-object ThisWorkbook.

De routine Workbook_Open is heel handig. U kunt er bijvoorbeeld voor zorgen, dat een bepaald tabblad altijd actief wordt bij het openen (Sheets(“Nijntje”).Select) of dat de eerste cel wordt geselecteerd (Range(“A1”).Select) of een combinatie.

Veel plezier met het spelen van Nijntje Schuif 2.0!

Zoals gezegd: volgende keer een fraaiere uitvoering; dus versie 2.1 komt er aan.