Categorie archief: Excel

Kunst en Excel

29 april 2016ExcelSOM.ALS, TabelGijs Verbruggen

Een tentoonstelling over het leven en het werk van Grayson Perry in het Bonnefantenmuseum in Maastricht (nog te zien tot en met 5 juni 2016; laat deze kans niet voorbij gaan!) ontroerde mij zeer.
Waarom eigenlijk? Ik weet nog steeds niet of ik zijn (of haar?) vazen nu zo mooi vind. Ook vond ik de wandkleden in eerste instantie niet echt speciaal. Het is toch alleen maar raar om met een roze motor door Europa te toeren met je lievelingsbeer achterop? Een huis bouwen met allemaal vreemde hoekjes en beelden, dat is toch niet normaal?

Maar alles kwam in perspectief te staan, toen ik de films over zijn leven en werk had bekeken: toen kwam het besef, dat iemand probeert ons een spiegel voor te houden door zijn/haar (fantasie)wereld en leven aanschouwelijk te maken. Maar ik blijf het moeilijk vinden om het gevoel, dat de tentoonstelling bij mij opriep, onder woorden te brengen.

Dat wordt dan het bruggetje naar Excel: het gaat mij makkelijker af om (een onderdeel van) Excel uit te leggen (hoop ik!).

De tentoonstelling liet me ook weer zien, dat ik zelf dit soort kunstzinnige creativiteit mis. Maar voor mij is de Kunst van het Programmeren (was een vak op de TH-Eindhoven) ook een vorm van creativiteit is: hoe los je slim (en mooi) bepaalde problemen op.

Laten we overgaan naar Excel. Deze keer ga ik mijn belofte inlossen en iets vertellen over tabellen in Excel (en waarom we die veel meer moeten gebruiken ofwel de 10 voordelen van Tabellen).

Tabel maken

Nu de tentoonstelling van Perry de wereld overgaat (na Maastricht gaat die naar Denemarken), moet alles natuurlijk geïnventariseerd worden, inclusief de grootte van de objecten.

In het Voorbeeldbestand in het tabblad Cat1 heb ik een begin gemaakt met de catalogus: in de kolommen staan de rubrieken die ik wil vastleggen en in de rijen komen dan alle objecten.

Van dit overzicht gaan we een Excel-tabel maken:

selecteer één van de cellen in het overzicht (door er op te klikken), bijvoorbeeld cel C6
kies in de menutab Invoegen binnen het blok Tabellen de optie Tabel
Excel beoordeelt zelf welke gegevens waarschijnlijk in de tabel moeten worden opgenomen. In dit geval klopt dat niet helemaal (de kopregel Vazen nemen we niet mee): $B$2 moet gewijzigd worden in $B$3. Het vinkje bij kopteksten is wel juist.

En daar is ons nieuwe overzicht: automatisch inclusief Filters bij de koppen (voordeel 1), ‘zebra-lijntjes’ voor de leesbaarheid (voordeel 2), een eigen menutab voor diverse instellingen (voordeel 3) en het gegevensgebied heeft direct een naam (hier Tabel4, te vinden in het tabblad Hulpmiddelen voor tabellen en is aanpasbaar; voordeel 4)

Tabel uitbreiden

De grootste voordelen worden pas zichtbaar als we de tabel gaan uitbreiden (zie het Voorbeeldbestand, tabblad Cat2):

de logistiek-medewerker van Perry wil niet alleen de H, L en B van de objecten weten, maar ook hoeveel ruimte deze innemen.
In cel G3 komt de tekst Inhoud (cm3).
Aan de zebra-lijntjes is te zien dat Excel automatisch de tabel uitbreidt met een extra kolom (voordeel 5).
NB selecteer de 3 achter cm, druk op Ctrl-1 (Celeigenschappen) en kies Superscript
start een formule in cel G4 door het =-teken in te tikken, klik dan met de muis in cel D4, tik het *-teken, klik in E4, weer het *-teken, klik in F4 en druk dan op Enter.
In plaats van de standaardverwijzing =D4 * E4 * F4 heeft Excel een beter leesbare formule geplaatst (voordeel 6).
Nog mooier: de formule is direct naar beneden naar alle rijen gekopieerd (voordeel 7).
Wel zelf even de opmaak regelen: kies alle cellen met de formule in kolom G en pas de lay-out aan (bijvoorbeeld via Ctrl-1).
We voegen nog 2 vazen aan de lijst toe: in het tabblad Cat2 staan de gegevens daarvan. Selecteer deze gegevens, kies Kopiëren en Plak ze in B9.
Excel zal de tabel automatisch uitbreiden (zie de zebra-lijntjes) en vult achteraan ook direct de juiste formule toe (voordeel 8).

NB de naamgeving [@Hoogte] is even wennen: de rechte haken geven aan, dat het een naam in een tabel is; door de @ ‘weet’ Excel dat hij niet zomaar een hoogte moet nemen maar die uit dezelfde rij en Hoogte is één van de kopteksten.

Analyse van tabelgegevens

Een betrokken archivaris wil vanuit het overzicht wat extra informatie ophalen (zie het Voorbeeldbestand, tabblad Cat3).

aangezien de archivaris niets kan met cm3, heeft hij een kolom met de inhoud in liters toegevoegd door een koptekst in te tikken en de formule =[@[Inhoud (cm3)]]/1000 (ja, de archivaris kan met Excel omgaan!)
aangezien hij wil weten hoeveel werken er uit de diverse jaren komen maakt hij een draaitabel:
* selecteer één van de cellen in het overzicht (door er op te klikken), bijvoorbeeld cel C6
* kies in de menutab Invoegen binnen het blok Tabellen de optie Draaitabel
* Excel kiest automatisch het juiste bereik (hier tblCat3), klik OK
* sleep Jaar naar de Rijlabels en Omschr naar het Waarden-gebied.
nu hij toch bezig is: misschien is het ook wel handig om te weten hoe hoog de stapel wordt wanneer de werken per jaar in het magazijn moeten worden bewaard:
* sleep ook de Hoogte naar het Waarden-gebied

Maar wat nu als we nog meer vazen aan onze catalogus toevoegen:

op het tabblad Cat3 van het Voorbeeldbestand staan nog 2 extra exemplaren: kopieer de gegevens en plak ze in cel B11.
De tabel wordt automatisch uitgebreid en de formules achteraan worden ook naar de nieuwe regels doorgetrokken
Jammer, de draaitabel is niet gewijzigd! Iedereen, die gebruik maakt van dit (verders) formidabele hulpmiddel weet, dat na het aanpassen/uitbreiden van de brongegevens de draaitabel ververst moet worden: klik met de rechter muisknop ergens in de draaitabel en kies Vernieuwen.
Bij ‘gewone’ Excel-overzichten is dit niet voldoende; dan moet ook nog worden aangegeven dat de draaitabel naar een groter bereik moet ‘kijken’.
Is de bron een tabel dan is alleen Vernieuwen van de draaitabel voldoende; intern weet Excel dat de tabel ‘gegroeid’ is (voordeel 9).

Verwijzingen naar tabellen

Een andere archivaris is wat minder handig met Excel en weet niet goed hoe hij met draaitabellen moet omgaan (hij vergeet altijd Vernieuwen te kiezen en komt dus met de verkeerde cijfers).

Hij maakt daarom zijn eigen ‘draaitabel’ (creatief toch?) om de totale hoogte en lengte van de vazen per jaar te krijgen:

in het Voorbeeldbestand in het tabblad Cat4 komt in cel K19 het jaar waarvan hij het overzicht wil hebben (in het voorbeeld 2000).
en daaronder de formule
=SOM.ALS(tblCat4[Jaar];K19;tblCat4[Hoogte])
Ook hier geldt weer: als de tabel wordt uitgebreid met nieuwe vazen dan zal de formule alle nieuwe regels automatisch in het resultaat meenemen.
NB1 gelukkig hoeft dit niet ‘met de hand’ zo ingevoerd te worden: tik het begin van de formule tot en met het haakje-openen in en selecteer met de muis de cellen B4 tot en met B12, tik de punt-komma etc.
NB2 in dit geval staat voor de velden Jaar en Hoogte geen @, omdat niet de gegevens uit de overeenkomende regel moeten worden meegenomen maar alles uit de betreffende kolom.
NB3 omdat de verwijzingen buiten de tabel staan wil Excel wel weten uit welke tabel de gegevens moeten worden opgehaald; vandaar de tblCat4 voor de velden.
voor de lengte in cel K21 tikken we eenzelfde soort formule in (of we kopiëren de formule van K20 en dan op de juiste plaatsen wat aanpassingen)
in J20 en J21 nog een tekst (Hoogte resp. Lengte) en het overzicht is klaar.

NB het voordeel van deze ‘eigen draaitabel’ is dat die bij iedere wijziging van de bron direct wordt aangepast; Vernieuwen is niet nodig.

LET OP In het Voorbeeldbestand staan in J20 en J21 geen harde teksten, maar verwijzingen naar de kopjes in de tabel: =tblCat4[[#Kopteksten];[Hoogte]].
Wanneer de kopjes wijzigen (bijvoorbeeld ipv Hoogte wordt H (cm) gebruikt) dan past het overzicht zich automatisch aan. De draaitabel laat ons dan in de steek!

Een van de vele opties van tabellen, die je terug vindt onder Hulpmiddelen voor tabellen, is het al dan niet zichtbaar maken van een Totaalrij (voordeel 10):

selecteer één van de cellen in de tabel van tabblad Cat4 (door er op te klikken), bijvoorbeeld cel C6
vink in de menutab Hulpmiddelen voor tabellen binnen het blok Opties voor tabelstijlen de optie Totaalrij aan
onderaan iedere kolom kan nu een bepaald soort totaal gekozen worden (Som, Aantal, Gemiddelde etcetera).

NB naar de totalen kunnen ook weer verwijzingen gemaakt worden; in cel K23 wordt de totale hoogte opgehaald:
=tblCat4[[#Totalen];[Hoogte]]

Cadeau: dit was voordeel nummer 11!

Draaitabel opmaken

11 april 2016Excel, VBA (Visual Basic for Applications)Draaitabel, Formulier, OpmaakGijs Verbruggen

Kleine ergernissen kunnen soms uitgroeien tot grote.

Iedereen, die regelmatig een draaitabel maakt, kan dat waarschijnlijk beamen: de draaitabel is snel gemaakt, maar dan moeten er nog diverse handelingen uitgevoerd worden om de lay-out zodanig aan te passen, dat de draaitabel er uitziet, zoals je graag wilt.
Veel van die instellingen zitten niet bij elkaar in de menu-structuur, dus is het zoeken, muizen en klikken geblazen.

Eén van die instellingen is bijvoorbeeld de rapportindeling: iemand bij Microsoft heeft bedacht, dat de standaard-instelling daarvoor Compact moet zijn en deze standaard is niet te wijzigen.
Deze week bleek uit een vraag van een oud-collega, dat ik niet de enige ben die liever met de ‘klassieke’ indeling werkt.

In dit artikel zal ik laten zien, dat het mogelijk is om alle aanpassingen, die je vaak maakt, op één formulier te plaatsen, zodat het lastige zoeken naar de diverse menu-opties achterwege kan blijven.

Hieronder zal ik uitleggen hoe zo’n formulier wordt opgebouwd; de gegevens, die ik als voorbeeld gebruik, de daarbij behorende draaitabel en een reeds gebouwd (uitgebreider) formulier vind je in het Voorbeeldbestand.

Formulier maken

Een formulier voor Excel wordt gemaakt in de VBA-editor.
In de editor kom je, wanneer je in de menutab Ontwikkelaars in het blok Programmacode de optie Visual Basic kiest.
Maar veel makkelijker is natuurlijk de toetscombinatie Alt-F11.

NB Is de menutab Ontwikkelaars niet zichtbaar, pas dan de Menubalk aan bij Bestand/Opties.

In de VBA-editor maken we nu als volgt een formulier met enkele voorbeeld-opties:

zorg in het Project-overzicht dat een Excel-map is geselecteerd door er op te klikken (heb je geen Excel-map open, ga dan terug naar Excel (Alt-F11), klik Ctrl-N en ga weer naar VBA)
kies in de menu-balk Invoegen en daarbinnen de optie UserForm
er opent zich een leeg raster (het nieuwe formulier) waarop we met behulp van de Werkset allerlei besturingselementen kunnen plaatsen
Links onder het Project-overzicht zie je het Eigenschappen-venster van het actieve element; nu staan daar de eigenschappen van het formulier.
Voorlopig zijn alleen de eigenschap Name, waarmee we het formulier binnen VBA kunnen benaderen, en de Caption (de titel bovenaan het formulier) relevant.
De overige eigenschappen zijn vooral bedoeld om de lay-out van het formulier te beïnvloeden.
Bij Name voeren we in UF_DT2 en bij Caption de tekst Draaitabel opmaken.
Nu gaan we 2 opties toevoegen waarmee we kunnen bepalen of er wel of niet rij- en kolomtotalen moeten worden weergegeven in de draaitabel:
* klik in het grijze raster
* sleep vanuit de Werkset het selectievakje naar het raster op de plaats, waar de optie moet komen en herhaal dat een 2e keer (de eerste krijgt automatisch de naam Checkbox1 en de tweede Checkbox2)
* de namen laten we zo, maar de Caption veranderen we via de Eigenschappen: de eerste krijgt wordt Rij-totalen, de tweede Kolom-totalen
Voor de Rapportindeling willen we drie opties aanbieden. In tegenstelling tot de opties van het vorige punt sluiten deze elkaar uit: een draaitabel kan niet tegelijkertijd in de Compact- en in de Klassieke weergave staan, terwijl we natuurlijk wel de rij- en kolom-totalen los van elkaar willen kunnen aan- en uitzetten.
Hiervoor gebruiken we dan ook geen Selectievakjes maar Keuzerondjes:
* sleep drie keer een keuzerondje naar het formulier
* verander de Caption daarvan in Compact, respectievelijk Overzicht en Classic
* de namen van de rondjes laten we gehandhaafd (die blijven dus OptionButton 1, 2 en 3)
Test het formulier even:
* druk op F5
* klik op de diverse opties: de Selectievakjes kunnen los van elkaar aan- en uitgezet worden, terwijl er bij de Keuzerondjes altijd maar één actief is
* het formulier doet verder nog niets; klik rechtsboven op het kruisje om te sluiten
Dan gaan we 2 opdrachtknoppen toevoegen:
* sleep 2 keer een Opdrachtknop vanuit de Werkset naar het raster. Heb je te weinig plaats: maak het raster groter door de randen te verslepen of de reeds geplaatste elementen te verplaatsen.
* de eerste opdrachtknop geven we de volgende eigenschappen: de naam wordt ok en ook de caption wijzigen we daarin; de tweede krijgt de naam cancel en als caption Annuleren
Klik met de rechter muisknop op de 2e opdrachtknop en kies de optie Programmacode weergeven. Alles wat tussen de 2 regels staat (nu nog niets!) zal worden uitgevoerd wanneer we op de Annuleer-knop klikken. Tik in de 2e (lege) regel in: unload me
Dit is voor VBA het commando om het formulier (me) uit het geheugen te verwijderen en dus te sluiten.
NB omdat VBA de 2 woorden herkent zullen er (na ‘Enteren’) automatisch hoofdletters tevoorschijn komen.
Test het formulier opnieuw:
* dubbelklik in het projectoverzicht op UF_DT2
* druk op F5.
* wat doet de ok-knop? En de annuleer-knop?

NB De programmacode, die nodig is wanneer er op de OK-knop wordt geklikt, is wel wat uitgebreider. Door in het Voorbeeldbestand in de VBA-editor rechts op de ok-knop te klikken kan de programma-code bekeken worden.

LET OP heb je nog meer keuzerondjes nodig, maar moeten die niet afhankelijk zijn van de keuze van de overige rondjes, zorg dan dat ieder blok bij elkaar horende keuzerondjes in een zogenaamd Groepsvak (ook een element van de Werkset) wordt geplaatst.
In het Voorbeeldbestand zijn op die manier de instellingen voor de Rapport-indeling en voor de Veld-opmaak afzonderlijk in te stellen.

Formulier activeren

Nu moeten we nog een mogelijkheid maken om het formulier in Excel op te roepen:

we gaan weer naar VBA (Alt-F11)
klik in de projectverkenner op de Excel-map, waarin het formulier UF_DT2 staat
kies in de het VBA-menu Invoegen en daarbinnen de optie Module
tik dan in:
sub Start()
UF_DT2.show
end sub

Het formulier kun je voortaan opstarten door de macro Start aan te roepen: kies in Excel (om vanuit VBA naar Excel te gaan: Alt-F11) in de menutab Ontwikkelaars in het blok Programmacode de optie Macro’s, klik op de macro Start en dan op de knop Uitvoeren.

NB1 het macro-overzicht kun je ook bereiken door op Alt-F8 te drukken.

NB2 de regel UF_DT2.Show is voldoende om het formulier te tonen. In het Voorbeeldbestand is die routine uitgebreid met programmacode, die er voor zorgt dat de keuzerondjes en selectievakjes overeenkomen met de instellingen van de draaitabel op dat moment.

NB3 heb je de macro/routine vaak nodig, dan is het handig om daar een toetscombinatie aan te koppelen:

ga naar de macro’s (Alt-F8)
klik op de macro Start
klik op de knop Opties
tik een letter in (eventueel samen met Shift) waarmee de macro opgestart moet worden (bijvoorbeeld q, want de sneltoets Ctrl-q wordt door Windows niet standaard gebruikt)
klik Ok en dan Annuleren (dus niet Uitvoeren!)
probeer de toetscombinatie uit.

Formulier altijd klaar voor gebruik

In het voorbeeld hiervoor en in het Voorbeeldbestand zijn het formulier en de diverse routines in een Excel-werkmap opgenomen en kunnen dus ook alleen gebruikt worden, wanneer deze werkmap is geopend.

Wil je altijd de beschikking hebben over formulieren of routines dan moeten die worden opgenomen in de persoonlijke, onzichtbare werkmap Personal.xlsb.
Hoe kom je aan die werkmap? Het meest eenvoudige is op de volgende manier:

kies in Excel in de menutab Ontwikkelaars in het blok Programmacode de optie Macro opnemen
Zorg dat in het middenvak de optie Persoonlijke macrowerkmap is geselecteerd
klik OK
klik dan met de muis op een willekeurige cel en stop de opname
ga naar de VBA-editor (Alt-F11) en verwijder in de Module1 van Personal.xlsb de zojuist opgenomen macro.

Formulier en routine kopiëren naar de persoonlijke werkmap:

open het Voorbeeldbestand (als dat nog niet gebeurd is)
ga naar de VBA-editor (Alt-F11)
sleep het formulier UF-DT vanuit PT.xlsm naar Personal.xlsb
idem met de Module1
sluit Excel en bevestig de vraag over het opslaan van de persoonlijke map.

MS-Query: een alternatief voor Vert.Zoeken

19 maart 2016ExcelExterne gegevens, MS-Query, VERT.ZOEKENGijs Verbruggen

Ik had in de kop van dit artikel diverse bijvoeglijke naamwoorden willen gebruiken (stabiel, minder foutgevoelig, flexibel) maar dan werd deze te lang.
Ja, waar Vert.Zoeken, ondanks zijn wijdverbreid gebruik, zijn beperkingen en valkuilen kent (google maar eens op Vert.Zoeken) is een Excel-systeem gebaseerd op Microsoft Query (een standaard-onderdeel van Excel) veel robuuster.
De leercurve is waarschijnlijk iets groter dan bij een gewone Excel-functie, maar je krijgt er wel een krachtig hulpmiddel in je Excel-gereedschapskist voor terug!

Hieronder belanden we via het onderwerp Externe gegevens al snel bij MS-Query.

Externe gegevens

De meeste rapportage-afdelingen in bedrijven zijn voor de aanlevering van gegevens afhankelijk van andere afdelingen, die op hun beurt weer verschillende systemen gebruiken.
Wat gebeurt er dan vaak in de stresserige maand-afsluiting: blokjes data uit diverse overzichten worden gekopieerd en in eigen sheets geplakt met alle risico’s van dien.

Met behulp van de optie Externe gegevens ophalen (zie ook mijn artikel Excel en het WorldWideWeb) kan dit beter gestructureerd worden, waardoor een stabieler rapportage-systeem ontstaat.
Laten we eens een voorbeeld nemen. We krijgen periodiek een overzicht aangeleverd met daarin de verkoopdatum, een productcode en het betreffende bedrag (zie het tabblad Basis1 in het Voorbeeldbestand). Het gebied met gegevens heeft de naam Omzet gekregen.
In plaats van Kopiëren en Plakken gaan we deze gegevens op een alternatieve manier ophalen:

maak een nieuwe werkmap aan (handigste: gebruik Ctrl-N)
selecteer cel B2 (klik met de muis in die cel)
kies in de menutab Gegevens in het blok Ext. geg. ophalen de optie Best. Verbind.

Tsja, naamgeving: we gaan geen bestaande verbinding gebruiken, maar kiezen toch deze optie.
Linksonder in de pop-up kiezen we de optie Bladeren naar meer …
zoek in het directory-overzicht het bestand MSQ_basis.xlsx op (eerst downloaden natuurlijk) en klik Openen
in het tussenscherm

zien we de namen van de drie tabbladen uit het Voorbeeldbestand (met een $-teken als toevoeging) en de naam van het gebied met gegevens. Klik op Omzet en op OK.
Ook in het volgende tussenscherm Gegevens importeren kiezen we OK.
Et voilà: de gegevens zijn opgehaald en, overzichtelijk opgemaakt, in een Excel-tabel geplaatst.
Doe het voorgaande vanaf punt 2 nog een keer, maar plaats de cursor in cel F2 en selecteer als tabel Basis1$.
De resultaten zullen hetzelfde zijn, maar of dat ook zo blijft?
Sla de werkmap met de twee, hiervoor aangemaakte, tabellen op met de naam Resultaat.xlsx en sluit deze.
Open MSQ_basis.xlsx en voeg op het tabblad Basis1 een regel toe en sluit dit bestand weer (natuurlijk de wijzigingen opslaan!).
Open het bestand Resultaat.xlsx en ververs de 2 tabellen (klik met de muis rechts en kies Vernieuwen).
De eerste tabel zal niet veranderen (de grootte van het gebied Omzet is niet veranderd) terwijl de tweede tabel ook de mutatie in de bron weergeeft.

NB Externe gegevens en ook MS-Query zijn ‘stiefkinderen’ van Microsoft: deze functies ondersteunen het gebruik van Excel-tabellen als bron niet volledig. De namen van dit soort tabellen zul je dan ook niet in het selectiescherm tegenkomen.

LET OP1 in de praktijk is het aan te bevelen om bij de selectie altijd de bladnamen te kiezen (en dus geen zelf gedefinieerde namen). Consequentie daarvan is dat ieder blok bij elkaar horende gegevens op een apart tabblad moeten staan.

LET OP2 Excel zal bij het ophalen van gegevens lege kolommen links en rechts en lege rijen boven en onder weglaten. Maar … dit werkt niet altijd goed, dus het is verstandig om een blok gegevens in cel A1 te laten beginnen.

Gegevens ‘verrijken’

Maar in een rapportage willen we meestal codes, zoals hiervoor bij het product, vermijden of aanvullen met een omschrijving.

We gaan onze geïmporteerde gegevens ‘verrijken’ met aanvullende gegevens. Daartoe plaatsen we ergens in de werkmap een overzicht van de gebruikte codes met daarnaast een omschrijving (en eventueel nog andere gegevens); ik heb dit overzicht de naam PrCd gegeven.

Naast de tabel, die we geïmporteerd hebben, zetten we in de eerste rij een opzoek-formule; in cel E3 komt dan de formule
=VERT.ZOEKEN([@ProdCode];PrCd;2;ONWAAR)

Aangezien we te maken hebben met een tabel, zal Excel deze formule direct naar beneden naar alle regels kopiëren. Nog mooier: wijzigt het aantal regels in de bron-sheet en we vernieuwen de tabel dan wordt ook de kolom met onze formule automatisch aangepast!

Nog een verticaal-zoeken er naast en we weten ook bij welk hoofdproduct de regel hoort:

Ook al worden de kolommen van de tabel verplaatst (bijvoorbeeld de kolom HfdProd verslepen naar de positie van bedrag), dan blijft deze indeling gehandhaafd bij het Vernieuwen van de tabel.

Door een draaitabel ‘los te laten’ op de tabel kan snel een overzicht per Fiets en Brommer en/of per maand gemaakt worden. Na aanlevering van nieuwe brongegevens (bestand met dezelfde naam in dezelfde directory plaatsen) hoeven dan alleen de Excel-tabel en de draaitabel vernieuwd te worden!

MS-Query

Hoewel op de vorige manier het rapportageproces al flink gestructureerd en geautomatiseerd is, levert het gebruik van Microsoft Query nog verbeteringen en extra mogelijkheden op.

We gaan uit van hetzelfde Voorbeeldbestand maar gaan de tabbladen Basis2 (qua opzet gelijk aan Basis1, maar met meer records) en ProdCd gebruiken. Dit laatste werkblad bevat het totale productenpalet met codes en omschrijvingen.

Voer de volgende handelingen uit:

maak een nieuwe werkmap aan (Ctrl-N)
selecteer cel B2
kies in de menutab Gegevens in het blok Ext. geg. ophalen de optie Van andere bronnen en klik op Van MS Query
nu kunnen we aangeven welke soort bron we gaan gebruiken; we kiezen in dit voorbeeld Excel-files
Welke mogelijkheden er zijn, hangt af van de individuele Excel-implementatie.
LET OP zorg dat de onderste optie (Query’s maken/bewerken) uitgevinkt is; de Wizard is meestal te beperkt in zijn mogelijkheden.
In de volgende stap zoeken we het Voorbeeldbestand MSQ_Basis.xlsx weer op, waarna de volgende pop-up verschijnt:
NB is alleen Omzet zichtbaar? Zorg via Opties dat ook de systeemtabellen staan aangevinkt.
Voeg Basis2$ en ProdCd$ toe (via dubbelklikken) en kies Sluiten.
Nu komen we in het echte MS-Query-scherm (gebruikers van Access zal dit bekend voorkomen)
Voordat we de query gaan maken moeten we de relatie tussen de twee tabellen aangeven; in dit geval wordt het verband tussen de 2 tabellen gevormd door de ProdCode: klik op ProdCode in de tabel Basis2$, hou de muis-knop ingedrukt en sleep naar ProdCode in de andere tabel en laat dan de muisknop los. Als alles goed is gegaan staat er een lijntje tussen de 2 velden.
Nu gaan we aangeven welke velden we in het resultaat willen hebben: dubbelklik in de tabel Basis2$ op Datum en op ProdCode. Idem in de tabel ProdCd$ op ProdOmschr en HfdProd en als laatste in de tabel Basis2$ op Bedrag.
Het resultaat van de query is direct zichtbaar (zo niet: druk op de button met het uitroepteken en de 2 pijltjes):
Wil je weten hoe deze query met SQL zou moeten worden gemaakt? Druk op de button SQL.
Nu moet het resultaat nog in Excel terecht komen; klik op het ‘deurtje’ (Gegevens retourneren) en klik in het tussenscherm, dat verschijnt, op OK.

That’s it! De MS-Query is klaar, de resultaten overzichtelijk gerangschikt en met één muisklik (klik rechts in de tabel en kies Vernieuwen; sorry, dus 2 klikken) is het overzicht geactualiseerd. Eventueel aangepaste opmaak (waarom Excel in dit geval ook een tijd laat zien bij de datum?) blijft gehandhaafd.

Wanneer je niet geïnteresseerd bent in een detailoverzicht kun je natuurlijk ook direct een draaitabel maken van de query-resultaten: nadat je MS-Query ‘via de achterdeur’ hebt verlaten (stap 10) kies je in het tussenscherm de optie Draaitabelrapport.

Zonder verdere handelingen (alleen de draaitabel Vernieuwen) worden na aanlevering van nieuwe brongegevens de resultaten zichtbaar.

Query aanpassen

Af en toe zal het nodig zijn om de query aan te passen. Waar en hoe de aanpassing moet worden doorgevoerd is helaas niet altijd even makkelijk te vinden en consistent.
Soms is het makkelijker en sneller om de query gewoon opnieuw te maken!

Heb je detail-gegevens binnengehaald in een tabel en wil je de query nog eens bekijken of aanpassen: klik rechts in de tabel en kies bij Tabel de optie Query bewerken.

Heb je direct een draaitabel aangemaakt: klik in de draaitabel, kies op het tabblad Hulpmiddelen voor draaitabellen binnen Opties bij Gegevens de optie Vern. en daarbinnen Eigenschappen van verbinding.
Nog niet klaar: kies het tabblad Definitie en nu kun je de Query bewerken.

In het algemeen kunnen alle koppelingen naar MS-Query bereikt worden via de optie Verbindingen in de menutab Gegevens.

Als je daarop klikt krijg je alle verbindingen te zien, selecteer de gewenste en kies Eigenschappen.

Vooral het Definitie-blad is van belang: niet alleen kun je daar naar Query bewerken maar ook kun je de naam van de bron en de directory aanpassen.

Heb je genoeg kennis van SQL: ook die is aan te passen (in het blok Tekst van opdracht).

Waarschuwing

LET OP LET OP LET OP LET OP LET OP LET OP LET OP LET OP LET OP

De relatie die in stap 6 hierboven is gelegd, is een zogenaamde INNER JOIN. Dat betekent dat alleen die records uit de tabellen worden meegenomen, waarbij de relatievelden (in dit geval de ProdCode) in allebei de tabellen voorkomen. Zou in Basis2 ook een record voorkomen met bijvoorbeeld een ProdCode gelijk aan 121 dan wordt die niet getoond in het resultaat.

Om dit te voorkomen moet je een OUTER JOIN/relatie aanmaken:

kies op één van bovengenoemde manieren de optie Query bewerken
dubbelklik op het relatielijntje
verander optie 1 in optie 2 en kies Toevoegen:
NB misschien moet je 3 kiezen; dit in het geval de relatie van ‘rechts naar links’ getekend is
Na het sluiten van het vorige scherm is de lijn veranderd in een pijl (van Basis2$ naar ProdCd$)
ga ‘via de achterdeur’ terug naar Excel.

Dit was (heel erg in het kort) uitleg over het gebruik van MS-Query. Nog vragen, maar ook voor opmerkingen: neem contact op met G-Info.

Draaitabel.Ophalen

4 maart 2016ExcelALS.FOUT, Draaitabel, Draaitabel.OphalenGijs Verbruggen

Mooie vraag: je hebt een heleboel gegevens, maar hoe maak je daar nu informatie van.

Zoals in het plaatje is weergegeven kun je op diverse manieren met de data gaan “stoeien”. Eén van de meest gehanteerde methodes is het gebruik van draaitabellen. En terecht: menige beroepsgroep zou ontzettend blij zijn met zo’n krachtig, breed inzetbaar hulpmiddel!

Maar draaitabellen kennen (natuurlijk) ook nadelen. Ik kan er nu twee bedenken:

de mogelijkheden en inzetbaarheid zijn ontzettend uitgebreid; waar begin je als je er iets over wilt vertellen!
draaitabellen hebben altijd een vast stramien: als je een verdeling naar product in de rijen hebt staan kun je de volgorde van de producten nog wel makkelijk aanpassen, maar als het eerste resultaat in cel C10 komt dan komt de volgende in cel C11 etc.

Nadeel 1 kunnen we niet met één artikel door G-Info oplossen, maar aan het tweede kunnen we wel wat doen.

Draaitabel maken

Om gegevens uit een draaitabel te kunnen gebruiken moet je natuurlijk wel een draaitabel hebben.
Laten we er even eentje maken (zie Voorbeeldbestand):

in het tabblad Basis1 staat een fictief omzetoverzicht. Per dag ligt de omzet vast, gesplitst naar soort en product.
De gegevens zijn willekeurig door Excel gegenereerd met de formules van het tabblad Formules en daarna ‘hard’ gemaakt door de cellen te kopiëren en op dezelfde plaats te plakken als Waarden.
selecteer een willekeurige cel in dit overzicht en kies dan de optie Draaitabel in de menutab Invoegen
Excel zal automatisch het totale gebied met gegevens selecteren (in dit geval B2 t/m E1001).
In het voorbeeld heb ik voor de locatie van de draaitabel een bestaand werkblad gekozen (namelijk Basis1) en de draaitabel op cel H3 geplaatst …
… en de Lijst met draaitabelvelden ingevuld zoals hiernaast. Resultaat: een mooi overzicht van de verdeling van de omzet naar Soort en Product (zie tabblad Basis1):
NB het gemiddelde bedrag hebben we gekregen door het veld Bedrag opnieuw naar het waarden-blok te slepen en de Waardeinstellingen te veranderen van Som naar Gemiddelde.

De volgende opmerkingen bij dit overzicht:

de kolomnamen zijn groot waardoor alle kolommen automatisch extra breed worden (nog een nadeel van draaitabellen!).
Oplossing: klik op zo’n naam en wijzig die in bijvoorbeeld Aantal of Gemid.
Zie de tweede draaitabel op het tabblad Basis1.
de sortering bij Soort is niet logisch.
Oplossing1: versleep de diverse items naar de juiste plaats door met de cursor “de rand vast te pakken”
Oplossing2: maak een eigen sortering aan en pas die toe (zie artikel doorvoeren-en-sorteren)
wanneer de brongegevens uitgebreid worden met een nieuwe regel dan zal de draaitabel niet automatisch veranderen (ook niet na het Vernieuwen daarvan).
Oplossing1: ga binnen de menutab Hulpmiddelen voor draaitabellen naar Opties en kies dan binnen Gegevens voor Andere Gegevensbron en zorg dat alle gegevens zijn geselecteerd.
Oplossing2: zorg dat de brongegevens als een Excel-tabel zijn opgeslagen; zie hierna (Excel-tabel en draaitabel)
qua structuur is het niet ‘netjes’ om basisgegevens en afgeleide overzichten in hetzelfde tabblad weer te geven. In dit geval heb ik het wel even gedaan om snel het resultaat van de draaitabel te kunnen vergelijken met de bron.

Excel-tabel en draaitabel

Twee soorten tabellen, die niet door elkaar gehaald moeten worden!
In het tabblad Basis2 van het Voorbeeldbestand staan dezelfde gegevens als in blad Basis1. Maar ik heb daar een Excel-tabel van gemaakt:

selecteer een willekeurige cel in dit overzicht en kies dan de optie Tabel in de menutab Invoegen
in het nieuwe tussenvenster wordt automatisch het hele gegevensgebied gekozen en Excel ‘ziet’ dat er een kopregel is. Klik OK.
het resultaat is een tabel, waarbij Excel automatisch de regels een ´zebra´-achtergrond geeft en de koppen van filter-knoppen voorziet:
via de menutab Hulpmiddelen voor tabellen is de naam van de tabel gewijzigd van de standaardwaarde Tabel1 naar tblOmzet.

Wanneer we nu een draaitabel hiervan maken (zie tabblad Draai) en we vullen de brongegevens op het tabblad Basis2 aan met een nieuwe regel dan zal de draaitabel automatisch de nieuwe regel meenemen.
Wel nog Vernieuwen kiezen, bijvoorbeeld door rechts te klikken op de draaitabel!

Gegevens van een draaitabel gebruiken

Genoeg over de voorbereidingen; dit artikel zou gaan over het ophalen van gegevens uit een draaitabel.

Stel dat we om een of andere reden niet alle gegevens willen zien maar bijvoorbeeld alleen het bedrag, dat bij de combinatie Soort=Een en Product=P2 hoort, dan kunnen we in Excel natuurlijk gewoon verwijzen naar cel G6 (zie het tabblad Draai in het Voorbeeldbestand).
Maar wat gebeurt er als de draaitabel wordt gewijzigd omdat er nieuwe soorten of producten bijkomen of als de structuur van de draaitabel wordt gewijzigd (wissel Soort en Product maar eens om van Rijlabels naar Kolomlabels en vice versa)?
Dan staan de gegevens van Een/P2 waarschijnlijk niet meer in dezelfde cel en klopt de verwijzing niet meer. Dit is een veel voorkomende fout in Excel-sheets!

Gelukkig heeft Microsoft daar iets op bedacht (vanaf versie Excel 2007?). Even een voorbeeld:

klik in het tabblad Draai in cel C30
tik het =-teken in en klik dan met de muis op cel M10 en druk op Enter
als in cel C30 nu de formule =M10 staat, moet je nog iets aan de instellingen van de draaitabel veranderen:
* klik ergens in de draaitabel
* in de menutab Hulpmiddelen voor draaitabellen, die nu tevoorschijn komt, kiezen we de tab Opties. In het vak Draaitabel staat nog een keer Opties: zet daar het vinkje bij DraaitabelOphalen genereren aan. Ga dan opnieuw naar punt 1.
Wil je deze optie niet gebruiken dan weet je nu dus ook waar je hem uit kunt zetten.
in cel C30 staat nu de formule =DRAAITABEL.OPHALEN(“Som”;$B$3)
Ofwel: haal het bedrag op dat hoort bij het veld Som (in ons geval is dat eigenlijk het veld Bedrag, maar we hebben Som van Bedrag gewijzigd in Som) uit de draaitabel, die begint in cel B3.
de vorige formule haalt het totaalbedrag op. Voer de vorige procedure eens uit in cel C31 maar klik dan op cel D10 ipv M10. Excel zal dan automatich de volgende formule genereren: =DRAAITABEL.OPHALEN(“Som”;$B$3;”Product”;”P1″)
Aan het ophalen wordt dus een voorwaarde meegegeven: haal niet alle bedragen op, maar alleen die bedragen waarbij het Product gelijk is aan P1 (let op de aanhalingstekens overal).
NB1 Dit soort formules kun je ook met de hand intikken, maar het is lastig om dan geen fouten te maken.
NB2 verwissel Soort en Product en kijk wat de resultaten in C30 en C31 zijn; als het goed is blijven die hetzelfde. We zijn dus niet meer afhankelijk van de structuur van de draaitabel!

Dat in cel D35 de volgende formule staat, zal dan geen verrassing meer zijn:
=DRAAITABEL.OPHALEN(“Som”;$B$3;”Soort”;”Drie”;”Product”;”P3″)

Maar we gaan nog een stapje verder en maken de formule dynamisch: in cel E35 staat de formule =DRAAITABEL.OPHALEN(“Som”;$B$3;”Soort”;E33;”Product”;E34)

Bij de voorwaarden staat geen letterlijke tekst meer maar een verwijzing naar de cellen E33 en E34. Bij het wijzigen van de inhoud van die cellen zal de formule dus ook andere waarden ophalen.

LET OP als je in cel E33 de tekst Vijf intikt levert de formule een foutmelding op! Draaitabel.Ophalen kan alleen maar gegevens ophalen die in de draaitabel voorkomen.

Voorbeeld 2 voor Draaitabel.Ophalen

In tabblad DraaiDatum van het Voorbeeldbestand staat nog een voorbeeld van de functie Draaitabel.Ophalen.

De draaitabel geeft per dag het aantal records en de omzet weer (de automatisch gegenereerde namen Aantal van Datum en Som van Bedrag zijn overschreven door Aantal respectievelijk Subtotaal).

Willen we alleen het resultaat van een specifieke dag zien dan gebruiken we weer de functie Draaitabel.Ophalen (zie tabblad DraaiDatum):

Maar omdat de datum 2-1-2015 niet voorkomt krijgen we een foutmelding; in cel G5 staat dan ook een iets uitgebreidere formule:
=ALS.FOUT(DRAAITABEL.OPHALEN(“SubTotaal”;$A$3;”Datum”;G3);0)

Dus als de functie Draaitabel.Ophalen een fout genereert dan wordt het resultaat gelijk aan 0 (nul).

Voorbeeld 3 voor Draaitabel.Ophalen

Een van de vele mogelijkheden van een draaitabel is dat je rubrieken kunt groeperen; helemaal interessant is dit voor datums.

In het tabblad DraaiMnd van het Voorbeeldbestand is een indeling naar Jaar, kwartaal en maand te zien. Hoe is dit gemaakt?
Op basis van de gegevens in tabblad Basis3 is een draaitabel gemaakt met in de Rijlabels de Datum, aan het vak Waarden is nog een keer de Datum toegevoegd en ook het Bedrag.
Maar nu komt het: wanneer je nu in de draaitabel op één van de datums met de muis rechts klikt, krijg je de optie Groeperen: Excel ‘ziet’ dat het datums zijn en heeft al de optie Maanden geselecteerd; klik ook nog op Kwartalen en Jaren en OK.

Standaard zal Excel geen subtotalen voor de nieuwe groepen bepalen: klik rechts op het jaar 2015 en zet de optie Subtotaal Jaren aan; zo ook voor de kwartalen.

Omdat het tabblad Basis3 nog formules bevat, waarbij alle velden willekeurig worden gevuld, zal het Vernieuwen van de draaitabel ook iedere keer andere resultaten opleveren.

Wanneer je in het tabblad Formules de Einddatum verandert in bijvoorbeeld 29-2-2016, zal het resultaat daarvan meteen zichtbaar zijn in de draaitabel (na Vernieuwen).

Bekijk nu alle formules die in het tabblad DraaiMnd in het blok G3:I13 staan.
Hopelijk spreken die voor zichzelf! Zo niet: neem contact op met G-Info.

Elf-proef

19 februari 2016Excel, VBA (Visual Basic for Applications)Gegevensvalidatie, Herhaling, Rest, SOMPRODUCT, voorwaardelijke opmaakGijs Verbruggen

BSN: iedereen heeft er een, maar weinig mensen zullen dit nummer uit hun hoofd kennen.
Bij het invullen worden dan ook vaak fouten gemaakt. Iedere instantie wil daarom graag geautomatiseerd checken of het ingevoerde getal juist is.

De controle die je op een BSN kunt uitvoeren is een variant van de elf-proef. Vóór het IBAN-tijdperk werd deze gebruikt om bankrekeningnummers te controleren.

Hoewel de toepassing van de elf-proef voor bankrekeningnummers niet meer geldt, komt deze in de praktijk toch nog regelmatig voor. In Excel is die in te bouwen, maar vergt nogal wat hulpkolommen; de elf-proef is dan ook bij uitstek geschikt om via VBA in een eigen formule te verwerken.
Hieronder (na uitleg van de elf-proef) dan ook voorbeelden in Excel en met behulp van VBA (deze functie is dan ook zonder meer bruikbaar in Access).
Daarna behandelen we een alternatieve functie die ook geschikt is voor de controle van een BSN.

Elf-proef

Wikipedia: “De elfproef (11-proef) is een test die in het Nederlandse elektronische betalingsverkeer werd uitgevoerd op negen- en tiencijferige Nederlandse bankrekeningnummers, voor de invoering van het IBAN, om te controleren of het nummer een geldig rekeningnummer kan zijn. Varianten van de elfproef die gebruikmaken van een controlecijfer, worden toegepast bij andere belangrijke nummers, zoals het burgerservicenummer en het betalingskenmerk op een acceptgiro.
…
Het laatste cijfer van het rekeningnummer wordt met 1 vermenigvuldigd, het voorlaatste met 2, het op twee na laatste met 3, enzovoorts. De producten worden bij elkaar opgeteld en vervolgens wordt de som gedeeld door 11. Het resultaat van deze deling moet voor een geldig rekeningnummer een geheel getal zijn.”

Duidelijk toch: ieder cijfer van het rekeningnummer moeten we vermenigvuldigen met zijn gewicht, die producten optellen, de som moet dan deelbaar zijn door 11 oftewel de rest (na deling; bijvoorbeeld bij het delen van 24 door 11 is de rest 2) moet dan nul zijn.

Excel-voorbeeld 1

Ik bedacht me dat ik ooit eens voor een toepassing de functie Elfproef had gemaakt en die later ook bij cursussen gebruikt had. Even zoeken en ja hoor gevonden.

Maar voordat we die gaan gebruiken kijken we eerst in Excel hoe de elfproef werkt: ziet tabblad Vb1 van het Voorbeeldbestand.
Van het banknummer in kolom A wordt telkens, van achter af, een cijfer geïsoleerd (kolommen D, F, H etcetera; bekijk ook de formules in de kolommen E, G enzovoort) en die cijfers worden in kolom C met hun gewicht vermenigvuldigd. In kolom C worden die producten ook nog opgeteld en wordt de Rest bepaald bij deling door 11. Als daar 0 uitkomt dan is het antwoord ok, anders Geen juist nr.

Deze elfproef is bedoeld voor bankrekeningnummers bestaande uit 9 of 10 cijfers. Kolom A bevat dan ook een input-controle, zodat we in de berekeningen niet allemaal controles hoeven in te bouwen.
De input-controle is geïmplementeerd met behulp van Gegevens-validatie: kies de menutab Gegevens en dan in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie. De drie tabbladen zijn als volgt ingevuld:

VBA-voorbeeld 1

Om al die hulpkolommen te vermijden is in kolom B gebruik gemaakt van een eigen functie ElfProef1, die dezelfde resultaten oplevert:
In het Voorbeeldbestand is de functie te vinden door de VBA-editor te starten (Alt-F11 of via het tabblad Ontwikkelaars of door onderaan rechts te klikken op de naam van het tabblad Vb1 en Module1 te kiezen).

Enige uitleg is op zijn plaats:

In Excel wordt de functie aangeroepen met als parameter (de verwijzing tussen de haakjes) een bankrekeningnummer (in het voorbeeld door te verwijzen naar de cellen in kolom A). In de definitie van de Function ElfProef1 staat dan ook dat er een parameter getal wordt doorgegeven van het type Long (een groot geheel getal). Consequentie hiervan is wel, dat als de functie in Excel verwijst naar een tekst (bijvoorbeeld naar cel B1) de functie niets doet en als resultaat #WAARDE! teruggeeft.
Achter de functie staat As String: het resultaat van de functie is een tekst (string).
‘groene’ teksten is voor VBA commentaar; hebben verder geen functie. Invoeren door een enkele aanhalingsteken in te tikken.
met Dim geven we aan welke variabelen we in het programma/de functie gaan gebruiken
om hierna makkelijk de cijfers één voor één te kunnen ‘losknippen’ maken we met behulp van de VBA-functie Str van het getal een tekst (string)
door middel van de For-loop wordt de berekening uitgevoerd: de eerste keer wordt i gelijk aan 1, via Next wordt die 2 enzovoort net zolang tot i groter is dan de lengte van de tekst; dan gaat het programma verder met de opdracht na Next
in de loop wordt de waarde (Val) bepaald van de diverse cijfers en die waarde wordt met i vermenigvuldigd en bij het (reeds bestaande) lngResult opgeteld.
Omdat het laatste cijfer het gewicht 1 krijgt etc moeten we met het losknippen achteraan beginnen: dus we nemen het gedeelte (Mid) van de tekst dat start op positie lengte + 1 – i; de laatste parameter van de Mid-functie (het getal 1) geeft aan dat we 1 positie uit de tekst knippen.
we gaan er van uit, dat het bankrekeningnummer voldoet, dus geven standaard als output voor de functie ok mee
maar we moeten nog testen wat de deling door 11 oplevert: hier delen we de som door 11, nemen dan het gehele deel daarvan (de functie Int), vermenigvuldigen dan weer met 11 en kijken of dit weer gelijk is aan de oorspronkelijke som. Zo niet (<> betekent niet gelijk) dan wordt de output van de functie ONGELDIG.

NB de kolommen B en C zijn van een voorwaardelijke opmaak voorzien, zodat snel duidelijk is wanneer een banknummer aan de elfproef voldoet.

Excel-voorbeeld 2

In het Voorbeeldbestand is een tweede voorbeeld opgenomen (zie tabblad Vb2).
We maken van het bankrekeningnummer uit cel A3 (met dezelfde gegevensvalidatie als in voorbeeld 1) eerst een tekst van 10 tekens: we zetten daartoe vóór het rekeningnummer eerst 10 nullen (mbv het &-teken) en nemen dan de 10 rechtse tekens daarvan (cel B3).

NB in cel B4 staat een alternatief met de functie Herhaling, waardoor het duidelijker is dat we 10 keer de nul er voor zetten.

In de kolommen ontleden we de tekst weer in 10 losse gedeelten (van achter naar voren); er wordt een 0 bij opgeteld om Excel automatisch de tekst te laten omzetten naar een getal. In rij 4 wordt de vermenigvuldiging uitgevoerd, waarna in cel D4 de Som daarvan wordt bepaald. In cel D3 bepalen we dan of een deling door 11 als rest 0 oplevert.

In cel D5 wordt de vermenigvuldiging, optelling en check in één formule uitgevoerd:

=ALS(REST(SOMPRODUCT(E2:N2;E3:N3);11)=0;”ok”;”ONGELDIG”)

Voor uitleg over de functie SOMPRODUCT zie mijn vorige artikel.

VBA-voorbeeld 2

In de VBA-editor is ook een functie ElfProef2 opgenomen (alles met isBSN even overslaan).

De verschillen met de vorige functie:

de input-parameter getal is gedeclareerd als String (tekst)
een voordeel daarvan is, dat we de functie exacter kunnen laten aangeven wanneer de invoer niet aan onze eisen voldoet:
If getal = “” Or IsNull(getal) Or Not IsNumeric(getal) Then
        ElfProef2 = “Geen juiste invoer”
        Exit Function
    End If
Dus als de invoer leeg is (op 2 manieren) of als het geen numerieke waarde (getal) is dan wordt de output van de functie Geen juiste invoer en wordt deze afgebroken.
NB deze test kan uitgebreid en nog concreter gemaakt worden afhankelijk van de behoefte.
in de functie vertalen we het getal naar een tekst van 10 tekens (vergelijkbaar met wat we in Excel hebben gedaan)
de constructie lngResult Mod 11 is vergelijkbaar met de functie Rest in Excel

BSN

Zoals hiervoor aangegeven wordt voor de controle van het BSN een vorm van de elfproef wordt gebruikt: het enige verschil met de standaard is, dat het laatste cijfer niet met +1 maar met -1 wordt vermenigvuldigd.

Bij de functie ElfProef2 is daar al rekening mee gehouden:

optioneel (dus niet verplicht) kan aan de functie een tweede parameter isBSN worden meegegeven (Optional isBSN As Boolean = False). Een variabele van het type Boolean kan alleen de waarde WAAR of ONWAAR (True of False) hebben; als de parameter niet wordt meegegeven dan krijgt deze de waarde ONWAAR/False.
dmv de regel
If i = 1 And isBSN Then hulp = – hulp
zorgen we er voor, dat als i gelijk is aan 1 EN isBSN waar is dat dan het tegenovergestelde van hulp wordt opgeteld

In het tabblad BSN van het Voorbeeldbestand zien we dat het standaard-gebruik van de functie ElfProef2 in cel C3 aangeeft dat we te maken zouden hebben met een ongeldig BSN.

Gebruiken we echter de vorm =ElfProef2(B3;WAAR), zoals in cel D3, dan levert dit als resultaat ok op.