Categorie archief: VBA (Visual Basic for Applications)

Formules, Namen, Eigen functies

De grote kracht van Excel (en ieder ander spreadsheet-programma) is dat je gebruik kunt maken van allerlei formules. Of het nu berekeningen zijn of logische constructies (bijvoorbeeld met behulp van de ALS-functie) of combinaties hiervan: ieder spreadsheet wordt met formules ‘aan elkaar geknoopt’.

Bij de opbouw van formules is het zeer aan te raden om Namen te gebruiken in plaats van cel-verwijzingen als C4 of bereik-aanduidingen als F5:F20. De leesbaarheid van formules gaat daarmee met sprongen vooruit.

In dit artikel daarom (nogmaals) aandacht voor het maken en gebruik van Namen. Ook het opslaan van formules in een Naam komt aan bod. Maar ik zal ook laten zien dat het gebruik van Eigen functies (met behulp van VBA) veel flexibeler is.

Namen definieren

Een naam vastleggen in Excel kan op diverse manieren:

  1. de eerste is een rechtstreekse handmatige invoer (zie tabblad VB1 in het Voorbeeldbestand): selecteer een cel (in dit voorbeeld C2), klik in het Naamvak, links van de Formulebalk, en tik de gewenste naam in (hier dus Korting1) en druk op Enter.
    LET OP een naam mag geen spaties bevatten en geen celverwijzing voorstellen zoals A1 of EXP10. Het is ook af te raden om bestaande functienamen in te voeren (zoals Som); dit is voor Excel geen echt probleem maar voor de gebruiker van het spreadsheet kan het zeer verwarrend zijn.
    NB1 gebruikelijk is om op de plaats waar je een spatie zou willen zetten het _-teken te plaatsen (de underscore; bijvoorbeeld Korting_1) of het tweede gedeelte met een hoofdletter te laten beginnen (bijvoorbeeld KortPerc).
    NB2 worden er meerdere cellen tegelijkertijd geselecteerd, dan krijgt het totale bereik de ingevoerde naam; de afzonderlijke cellen hebben dan geen naam.
  2. plaats een omschrijving links of boven de cel die een naam moet krijgen.
    Selecteer de omschrijving en de betreffende cel (in het voorbeeld B2:C2) en kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie.
    In het pop-up venster heeft Excel in dit geval al ‘gezien’ dat er links van de cel een omschrijving staat; deze zal als naam voor cel C2 worden gebruikt.
    NB1 als er spaties staan in de omschrijving dan vervangt Excel deze automatisch door een underscore.
    NB2 stelt de omschrijving een celverwijzing voor dan plaatst Excel een underscore achter de naam (Rij1 wordt dan Rij1_) .
    NB3 zijn er naast de omschrijving meerdere cellen geselecteerd dan krijgt het totale bereik van die cellen de tekst van de omschrijving als naam.
  3. selecteer de cel (of cellen) die een naam moet krijgen en kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren.
    Voer een naam en eventueel een opmerking in. Controleer of de verwijzing klopt.
    Het Bereik voor een naam staat standaard op Werkmap; de naam kan overal gebruikt worden. In dit geval is deze korting alleen maar geldig op het tabblad Vb1, dus moet het bereik daaraan aangepast worden.
    NB klik je op het ‘vinkje’ achter het Naamvak, dan zie je alle namen die voor dit tabblad geldig zijn (inclusief de namen die voor de hele werkmap gelden).
  4. kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren en klik op de button Nieuw

NB wanneer op een van deze manieren een naam wordt ingevoerd zal Excel de verwijzing standaard ´absoluut´ maken; zie de $-tekens.

Namen beheren

Wil je kijken welke namen er in het Excel-bestand worden gebruikt en/of deze aanpassen kies dan in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren.

Hier is het mogelijk om een nieuwe naam toe te voegen of een bestaande te verwijderen.
Ook kan een naam bewerkt worden.

LET OP Bij bewerken kunnen de naam, opmerkingen en verwijzing aangepast worden, dus NIET het bereik. Wil je deze wijzigen dan moet je een nieuwe naam toevoegen met een ander bereik en de andere verwijderen.

Gebruik van Namen 1

In het tabblad Vb1 van het Voorbeeldbestand zijn netto-bedragen berekend met behulp van een zelf-gedefinieerde naam. In cel D7 staat de formule =C7*(1-Korting1).
Wanneer deze naar beneden gekopieerd wordt, wijzigt C7 automatisch in C8 etcetera. De korting is via de naam absoluut gedefinieerd en verwijst dus ook bij het kopiëren altijd naar cel C2.

Bij het invoeren van de formule laat Excel bij het intikken van de letters ko alle mogelijkheden zien, die voor dit tabblad gelden. De opties met ƒx zijn functies binnen Excel (standaard of eigen functies, zie hierna), de andere zijn namen.
Dubbelklik op de gewenste functie of naam of selecteer deze met de cursor en druk dan op de tab-toets.

Gebruik van Namen 2

Bij het definiëren van namen hoeft u zich niet te beperken tot cel-verwijzingen. Het is ook mogelijk om formules op te nemen.

Voor het tabblad Vb2 in het Voorbeeldbestand is voor de naam Kort2a als verwijzing de formule =1-Vb2!$C$2 ingevoerd. Dat houdt in dat de berekening van het netto-bedrag er dan iets anders kan uitzien:
het oorspronkelijke bedrag wordt vermenigvuldigd met Kort2a. Het mag duidelijk zijn dat vooral bij uitgebreide/complexe formules hierdoor het spreadsheet overzichtelijker blijft.

In kolom E staat een vergelijkbare formule, maar daar wordt gebruik gemaakt van Kort2b, die gedefinieerd is als =1-‘Vb2’!Korting2

LET OP wanneer binnen zo’n formule gebruik wordt gemaakt van een naam, die niet voor de hele werkmap geldig is, dan moet deze naam voorafgegaan worden door de naam van het tabblad, gevolgd door een !-teken. De aanhalingstekens zijn eigenlijk alleen maar nodig als de naam van het tabblad spaties bevat.

Gebruik van Namen 3

In het vorige voorbeeld is het kortingspercentage via een Naam omgezet in een kortingsfactor. Maar ook het netto-bedrag kan via zo’n constructie worden bepaald; zie het tabblad Vb3 in het Voorbeeldbestand.
Netto3a is gedefinieerd als
=’Vb3′!C7*(1-‘Vb3!Korting3)

LET OP C7 is relatief (zonder $-tekens) gedefinieerd. En dit geredeneerd vanuit cel D7. Dit betekent dat dezelfde naam Netto3a vanuit cel D8 verwijst naar cel C8. Selecteer maar eens een cel ergens in kolom D en kies dan Namen beheren. Of kies de optie Broncellen aanwijzen in het blok Formules controleren.

Maar wanneer we de naam Netto3a in een andere kolom plaatsen gaat het mis. Zie cel H21 in het tabblad Vb3. De naam Netto3a hanteert nu als bron de cellen uit kolom G.

Met de formule in Netto3b is dit opgelost: =’Vb3′!$C7*(1-!Korting3)
Dus de kolom is absoluut (altijd C), maar de rij relatief; zie cel H23.

NB de exacte locatie van de cel met de naam Korting3 is weggelaten; alleen het !-teken is voldoende. Een nadeel hiervan is dat Broncellen aanwijzen dan niet meer volledig is. In het Voorbeeldbestand is wel de naam van het tabblad opgenomen omdat Mac-gebruikers anders in de problemen komen.

In het tabblad Vb3 van het Voorbeeldbestand is nog een derde naam (Netto3c) opgenomen met als formule =’Vb3′!$C7:$C40*(1-!Korting3)

De werking is hetzelfde maar het voordeel is dat je nu het totaal van de netto-bedragen kunt bepalen met de formule =SOM(Netto3c). Deze formule moet wel ergens in rij 7 staan; zie het resultaat in de cellen H7 en H8.

LET OP Excel weet niet dat de inhoud van zo’n naam een formule is en zal het resultaat niet automatisch herbereken (bijvoorbeeld bij het wijzigen van het kortingspercentage). Daarom is de SOM nog aangevuld met +0*NU(); aangezien NU ieder moment van waarde verandert zal Excel de totale formule bij iedere wijziging van het spreadsheet wel opnieuw berekenen.

Gebruik van Namen 4

Wanneer gebruik wordt gemaakt van Excel-tabellen (en zoals we allemaal weten heeft dat grote voordelen) dan moet goed opgelet worden bij het definiëren van Namen.

In het tabblad Vb4 van het Voorbeeldbestand bestaat het systeem uit de tabel tblNetto. Binnen dat tabblad is de naam Netto4a gedefinieerd als =tblNetto[Bedrag]*(1-!Korting4)

De opzet van de formule is vergelijkbaar met de naam Netto3c. De werking is nu echter anders: in iedere rij komt nu hetzelfde resultaat, namelijk het nettobedrag dat hoort bij de eerste regel. De SOM-formule werkt wel goed en is nu onafhankelijk van de plaats.

Voor de juiste berekening in de tabel hebben we een andere naam nodig:
=’Vb4′!$C7*(1-!Korting4) of =tblNetto[@Bedrag]*(1-!Korting4)

NB de @ zorgt er voor dat met het bedrag in de betreffende regel wordt gerekend.

Eigen functie

Zoals hiervoor duidelijk mag zijn geworden kleven er aan het gebruik van formules in Namen wel nogal wat problemen of laten we zeggen aandachtspunten.
Wel is een voordeel van die systematiek dat beginnende gebruikers niet zomaar formules kunnen aanpassen.

Met een klein beetje VBA lossen we voorgaande aandachtspunten dat snel en vakkundig op:

  1. druk op Alt-F11 of kies de optie Visual Basic in het blok Programmacode van de menutab Ontwikkelaars
  2. zoek het betreffende project (in dit geval Formules.xlsm) en klik daarop
  3. als er nog geen Modules zijn, kies dan eerst Invoegen/Module
  4. dubbelklik op een module en plaats daar de volgende functie-definitie
    Function BerekenNetto(Bedr, KortPerc)
          BerekenNetto = Bedr * (1 – KortPerc)
    End Function
    De eigen functie BerekenNetto kent 2 parameters: een bedrag en een kortingspercentage. Het resultaat van de gewenste berekening wordt aan de functienaam gekoppeld.

In het tabblad Vb5 van het Voorbeeldbestand wordt deze eigen functie gebruikt. Omdat we binnen een Excel-tabel werken kunnen we weer naar de Bedrag-kolom wijzen (denk aan de @).

Maar de formule kan ook ergens anders in het tabblad staan, en wordt dan iets als =BerekenNetto(C7;Korting5)

Overzicht Namen

Zeker als er veel namen gebruikt worden in een Excel-systeem is het belangrijk om het systeem goed te documenteren.
Eén van de onderdelen van de documentatie moet een goede beschrijving van de verschillende Namen zijn (zie het tabblad Docu in het Voorbeeldbestand).

Gelukkig kunnen we een groot gedeelte automatisch laten genereren:

  1. selecteer een tabblad met één of meer geldige namen
  2. selecteer ergens een lege cel; zorg dat rechts en daaronder genoeg lege cellen zijn
  3. kies in de menutab Formules in het blok Gedefinieerde namen de optie Gebruiken in formule; in het vervolgscherm kiest u Namen plakken
  4. wanneer u dan op Lijst plakken klikt, wordt er een overzicht in het tabblad geplaatst
  5. kopieer de gegevens naar een documentatie-tabblad
  6. herhaal alle stappen voor ieder tabblad

NB alle namen die geldig zijn voor de hele werkmap komen dan meerdere keren voor; dus is er nog wat schoning vereist.


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

Sudoku in Excel

Ik kreeg onlangs de volgende vraag: kun je geen artikel schrijven waarin je uitlegt hoe je met behulp van Excel Sudoku-puzzels op kunt lossen.
Mijn antwoord bestond uit 2 woorden ‘Ja’ en ‘maar’.

De Ja, omdat met Excel ‘alles’ mogelijk is en de maar omdat het oplossen van zo’n puzzel meer een programmeer-uitdaging is en niet zozeer iets om de mogelijkheden van Excel voor de dagelijkse praktijk te laten zien.

Maar toen ik er wat langer over nadacht, zag ik wel wat mogelijkheden.
Dus nu toch een Sudoku in Excel, maar niet een machine die de oplossing genereert (wat zou je daar ook mee moeten), wel een systeem dat het zoeken van een oplossing ondersteunt.

Met behulp van Voorwaardelijke opmaak laat Excel zien op welke plaatsen een cijfer geplaatst kan worden. Dubbele invoer in een rij, kolom of blok wordt ook door Voorwaardelijk opmaak gesignaleerd. Bij deze opmaak wordt veelvuldig gebruik gemaakt van de Namen-optie van Excel; niet alleen de standaard-toepassing daarvan maar ook enkele meer ingewikkelde en dus interessante mogelijkheden!

Basis van het G-Info/Sudoku-systeem

Allereerst hebben we natuurlijk een blok van 9 bij 9 nodig, die dan verder is onderverdeeld in kleinere blokken van 3 bij 3 en in rijen en kolommen. Daar gebruiken we een eigenschap van cellen voor; we kunnen de (dikte van de) randen aanpassen (druk op Ctrl-1). Het bereik van de 81 cellen heeft de naam Sudoku gekregen.

Om straks te weten over welke kolom of rij we het hebben, zijn in regel 3 en in kolom B de cijfers 1 t/m 9 ingevuld. De kruising van die twee, dus cel B3, heeft de naam Ref gekregen. Straks moeten we ook weten wat de inhoud van een geselecteerde cel is; die informatie komt in cel O3 met de naam Selectie.
Regel 3 en de kolommen B en O zijn ‘onzichtbaar’ gemaakt . Dit is te zien aan de +-jes; door daar op te klikken, open en sluit je een bepaalde groep cellen (zie het tabblad Sudoku in het Voorbeeldbestand).

Om het geheel wat mooier te maken is het standaard-raster weggehaald (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rasterlijnen weergeven uit).
Alle regels en kolommen, die we niet nodig hebben, zijn verborgen (selecteer ze eerst en dan rechts klikken).
En als laatste zijn de klom- en rijnamen weggelaten (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rij- en kolomkoppen weergeven uit).

Voor een soepele werking van het spel zijn nog een paar knoppen toegevoegd die bepaalde VBA-routines opstarten; hier komen we later op terug.

Controle 1

Het eerste wat we willen weten als we een Sudoku invullen, is waar overal (als voorbeeld) een 1 staat of waar een 2 etc.
Dat kunnen we met Voorwaardelijke opmaak goed zichtbaar maken; de enige voorwaarde is dat we weten welk cijfer we willen laten oplichten. Daarvoor hebben we een klein VBA-programmaatje nodig:

De subroutine Worksheet_SelectionChange is gekoppeld aan het werkblad Sudoku. Als de selectie in dit werkblad wordt gewijzigd, wordt deze routine automatisch door Excel aangeroepen. Op deze manier bevat de cel Selectie (O3) altijd de laatst geselecteerde waarde uit het bereik Sudoku.

NB bekijk de VBA-routine in het Voorbeeldbestand door bijvoorbeeld met de muis rechts te klikken op de tab Sudoku en dan te kiezen Programmacode weergeven. Eventueel nog dubbelklikken op het blad Sudoku in de Projectverkenner.

Nu kunnen we de Voorwaardelijke opmaak toevoegen:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en Fselecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we de 2e optie (Alleen cellen opmaken met) en geven dan aan dat de opmaak moet worden doorgevoerd als de celwaarde gelijk is aan de inhoud van de cel Selectie (denk aan het =-teken vóór Selectie)
  4. nog even de gewenste Opmaak instellen en OK

Controle 2

De volgende controle zorgt er voor dat we weten of er geen dubbele cijfers in de kolommen, rijen of kleine blokken voorkomen.

Voordat we dit makkelijk en flexibel kunnen doen moeten we wat voorwerk verrichten.
Excel moet automatisch beoordelen bij welke rij, kolom en blok een bepaalde cel hoort. Dan heb ik gedaan door enkele nieuwe Namen in te voeren:

  1. plaats de cursor in de eerste cel van de Sudoku
  2. een nieuwe naam creëren: kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  3. in het vervolgscherm kiezen we als Naam een handige omschrijving, in dit geval RijSel en bij Verwijst naar=Sudoku!$B5
    Denk aan het dollar-teken voor de kolom-letter.
    Afhankelijk waar de cursor staat (nu in regel 5) zal deze naam de waarde uit kolom B van die regel ophalen.
  4. op gelijkwaardige manier bestaat er ook de naam KolomSel met als formule =Sudoku!D$3
    Deze naam geeft dus altijd de waarde uit regel 3 van de kolom waar de cursor staat.
  5. om te bepalen in welk blok de cursor staat is wat ingewikkelder:
    =GEHEEL((Sudoku!RijSel-1)/3)*3+GEHEEL((Sudoku!KolomSel-1)/3)+1
    Hier is de naam BlokSel aan gekoppeld.
  6. nu moeten we nog de inhoud van zo’n rij, kolom en blok kunnen ophalen om de inhoud te kunnen testen. Daartoe zijn nog 3 extra namen gedefinieerd:
    RijTest met de formule =VERSCHUIVING(Sudoku!Ref;Sudoku!RijSel+1;2;1;9)
    KolomTest met =VERSCHUIVING(Sudoku!Ref;2;Sudoku!KolomSel+1;9;1) en
    BlokTest met =VERSCHUIVING(Sudoku!Ref;GEHEEL((Sudoku!BlokSel-1)/3)*3+2;REST((Sudoku!BlokSel-1);3)*3+2;3;3)

Nu kunnen we een nieuwe Voorwaardelijke opmaak toevoegen:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
    =OF(AANTAL.ALS(BlokTest;D5)>1;
    OF(AANTAL.ALS(RijTest;D5)>1;
    AANTAL.ALS(KolomTest;D5)>1))
    Aantal.Als kijkt hoe vaak in een bereik (de eerste parameter) de waarde van de tweede parameter voorkomt.
  4. nog even een Opmaak instellen en OK

NB1 wil je de gebruikte opmaak bekijken kies dan Regels beheren binnen de optie Voorwaardelijke opmaak

NB2 achteraf gezien was het waarschijnlijker makkelijker geweest om voor het testen de functie INDEX te gebruiken in plaats van VERSCHUIVING. Misschien een andere keer.

Controle 3

Als laatste controle voegen we nog wat opmaak toe, die aangeeft op welke plaatsen een bepaald geselecteerd cijfer nog kan worden ingevuld:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
    =EN(D5<=0;
    EN(Selectie>0;
    EN(AANTAL.ALS(BlokTest;Selectie)=0;
    EN(AANTAL.ALS(RijTest;Selectie)=0;
    AANTAL.ALS(KolomTest;Selectie)=0))))
    Dus als de betreffende cel, waarvan de opmaak moet worden ingesteld, een waarde kleiner of gelijk aan nul heeft (dus er is nog geen cijfer ingevuld) EN via Selectie weten we welk cijfer we onder de loep nemen EN in het betreffende blok komt het cijfer nog niet voor EN ook niet in de rin EN niet in de kolom dan moet de opmaak toegepast worden.

Buttons/knoppen

Onder de Sudoku zijn een paar knoppen opgenomen, die het opzetten van een spel kunnen vergemakkelijken (zie het Voorbeeldbestand):

  1. Wissen: de beveiliging van het tabblad wordt verwijderd, het speelveld leeg gemaakt, lettertypes en eventuele kleuren worden gereset. Ook de Selectie en de Starttijd worden leeg gemaakt.
  2. de volgende stap is dat een nieuw spel moet worden opgezet. Doe dit op het tabblad Makkelijk of Moeilijk door onder een bestaand spel ‘met de hand’ cijfers op de juiste plaats te zetten.
    Kies dan op het tabblad Sudoku de knop Kopieer. Er komt een pop-up waarmee kan worden aangegeven welk spel uit de voorraad moet worden opgezet. Selecteer daartoe het betreffende blok met de muis en klik dan op OK.
  3. Als je klaar bent om te gaan spelen, klik dan op de knop Start. Allereerst worden alle opgezette cijfers geblokkeerd en vet gemaakt, het tabblad beveiligd (zonder wachtwoord) en de Starttijd ingevuld.
  4. Wil je tussentijds wat zetten gaan proberen, bewaar dan de tussenstand via de knop Save. De stand van dat moment wordt gekopieerd naar het tabblad SaveGame.

Handleiding

In het tabblad Handleiding staat de werkwijze van het systeem ook uitgelegd en ik heb enkele tips toegevoegd.

Veel speel-plezier!


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

VBA-routines versnellen

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

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

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

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

Gebruik van Select

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

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

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

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

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

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

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

Gebruik van Select-2

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

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

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

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

Gebruik van Range

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

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

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

Gebruik van een array

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

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

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

Gebruik van Tabel

Een alternatief voor de vorige oplossing is:

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

Gebruik van Tabel-2

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

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

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

Effect van groter bestand op de berekeningen

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


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

Context-menu aanpassen

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

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

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

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

Cel-context-menu

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

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

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

Aanpassing cel-context-menu

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

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

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

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

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

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

Hoe maak je een knop?

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

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

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

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

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

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

Routine om spaties te verwijderen

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

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

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

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

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


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

VBA-routine automatisch starten

Af en toe is het handig, dat Excel automatisch de invoer in een bepaalde cel valideert of dat er automatisch een melding komt wanneer een bepaalde cel wordt gewijzigd of …

Wat ik bedoel is: soms zou je willen, dat wanneer een bepaalde cel wordt gewijzigd, er dan automatisch een actie wordt getriggerd.
Validaties kunnen vaak eenvoudig met de optie Gegevensvalidatie worden uitgevoerd; andere routines kunnen met behulp van VBA (eventueel door een macro op te nemen) worden geprogrammeerd.
Maar hoe zorg je er voor dat zo’n routine automatisch opstart?

In dit artikel zal ik aan de hand van enkele eenvoudige voorbeelden laten zien hoe dit in zijn werk gaat.

Invoer valideren

In het Voorbeeldbestand op het tabblad Vb1 staat een klein tabelletje, waarmee het kwadraat van een ingevoerd getal wordt berekend.

Wanneer in cel C2 (met de naam Invoer) een getal wordt ingetikt, dan wordt in cel C3 het kwadraat bepaald. Maar ook start er een VBA-routine die een controle uitvoert en wordt er een pop-up-scherm getoond, waarvan de inhoud afhankelijk is van het controle-resultaat.

NB de cellen C2, C3 en C4 hebben een naam gekregen: selecteer het bereik B2:C4, kies  in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie; zorg dat alleen Linkerkolom staat aangevinkt.

  1. start de VBA-editor via het tabblad Ontwikkelaars of Alt-F11
  2. dubbelklik in de Projectverkenner op Blad1 (Vb1)
  3. daar staat de volgende routine:

    Door de naam van de subroutine (Worksheet_Change) zal Excel deze routine automatisch opstarten als er ergens in dit werkblad (Vb1 dus) iets wordt gewijzigd. Welke cel(len) er is/zijn gewijzigd wordt dan via de variabele Target doorgegeven.
    Allereerst tellen we het aantal cellen in Target; als dat niet gelijk is aan 1, dan zijn we klaar.
    Dan testen we of het adres (de celverwijzing) van Target gelijk is aan die van Invoer; zo niet dan zijn we ook klaar.
    Via Int([Invoer]) bepalen we het Integer-gedeelte (het gedeelte vóór de decimale komma) van de cel Invoer en kijken of dat gelijk is aan de Invoer  zelf; zo niet dan zorgen we via Msgbox dat er een pop-up verschijnt met die melding.
    Is de invoer wel een geheel getal, dan komt er een Msgbox, die laat zien wat er is ingevoerd en wat het resultaat in de cel Uitvoer is. Wanneer op OK wordt geklikt in de pop-up, dan wordt de cel met de naam VBA_result gevuld met het kwadraat van Invoer.

NB1 Denk aan de vierkante haken om de naam van een cel(-bereik). Anders ziet VBA dat als een interne variabele.

NB2 Via Format zorgen we er voor, dat bij grote getallen ieder blokje van 3 cijfers gescheiden wordt. Aangezien de VBA-editor Engelstalig is, krijgen we als scheiding een komma; met het commando Replace vervangen we die door een punt.

NB3 verschillende stukken teksten koppelen we aan elkaar met het &-teken (zorg dat er spaties omheen staan); vbCrLf zorgt er voor, dat de rest van de tekst op een volgende regel komt. De naam van de code (Carriage Return, Linefeed; wagen terug en 1 regel naar beneden) stamt nog uit het typemachine-tijdperk!.

NB4 met de spatie en het _-teken (underscore) kun je in de VBA-editor lange regels in ‘stukken knippen’; Excel weet dan dat ze bij elkaar horen.

Draaitabel vernieuwen

In het tabblad Vb2 van het Voorbeeldbestand staan een (simpele) Excel-tabel (met de naam tblData2), een draaitabel, die daarop gebaseerd is, en een controle-blokje. Wanneer in dat laatste gebied de inhoud van cel F2 gelijk wordt aan Ja (via gegevensvalidatie zijn alleen Nee en Ja toegestaan), dan wordt de draaitabel automatisch vernieuwd.

De VBA-routine, die deze klus voor zijn rekening neemt, vindt u in de VBA-projectverkenner in het object Blad2 (Vb2). Vergeet niet te dubbelklikken!
Waarschijnlijk behoeft de routine geen verdere uitleg; hij lijkt veel op die in het tabblad Vb1.

NB cel F3 wordt met behulp van de functie DRAAITABEL.OPHALEN gevuld; zie ook het artikel over Draaitabel.Ophalen.

Draaitabel vernieuwen 2

De 2 VBA-routines uit Vb1 en Vb2 zijn redelijk rechttoe-rechtaan geprogrammeerd; om ze makkelijker te kunnen begrijpen zijn niet alle officiële regels gehanteerd en zijn er weinig   fout-controles ingebouwd.

Het voorbeeld in tabblad Vb3 is al iets netter: het vernieuwen van de draaitabel is in een aparte subroutine opgenomen (met de naam DraaiVernieuwen).

  1. start de VBA-editor (Alt-F11)
  2. kies in het VBA/menu Invoegen/Module
  3. en plaats in de nieuwe Module de volgende routine:

LET OP in Vb2 werd de draaitabel (in het Engels Pivottable) met de naam Draaitabel1 vernieuwd. Deze routine vernieuwt de draaitabel met het volgnummer 1 in het actieve werkblad.

De nieuwe subroutine kan ook op andere plaatsen en op andere manieren worden aangeroepen. In Vb3 is een knop gemaakt, die de routine aanroept.
Klik rechts op de knop en kies Macro toewijzen.

NB alleen macro’s/subroutines die in een module zijn opgenomen kunnen aan een knop worden toegewezen; vandaar dat de routine DraaiVernieuwen niet in het object Vb3 staat.

Draaitabel vernieuwen bij wijzigen bron

Maar het zou natuurlijk nog mooier zijn als de draaitabel automatisch zou worden vernieuwd bij het wijzigen van de bron-gegevens (waaronder ook wordt verstaan het toevoegen van nieuwe gegevens).

In het Voorbeeldbestand is in het tabblad Vb4 een nieuwe Excel-tabel opgenomen (met de naam tblData4) en een daarop gebaseerde draaitabel.

Verander je de Soort of het Aantal in een bestaande regel dan start automatisch de VBA-routine in het object Blad4 (Vb4). Beter gezegd: de routine start bij iedere wijziging van een cel in dit werkblad, maar er wordt slechts daadwerkelijk iets uitgevoerd als een cel in tblData4 wordt gewijzigd.

Omdat in regel 3 de Target maar 1 cel kan omvatten kun je die niet zomaar met tblData4 vergelijken. We willen weten of die ene Target-cel in tblData4 voorkomt.
De VBA-functie Intersect bepaalt wat de overlap tussen de twee bereiken is. Is die overlap leeg dan is Intersect gelijk aan Nothing, dus als die Not Nothing is dan is er wel overlap.

NB1 de truc met Not Nothing kom je op veel plaatsen op Internet tegen en is algemeen toepasbaar voor allerlei soorten ranges. In dit geval is de Target maar 1 cel; we kunnen regel 3 dan vervangen door een meer simpele constructie:
If Intersect(Target, [tblData4]) = Target Then

NB2 de Msgbox levert nu wel een vreemd resultaat; toch niet goed over de routine DraaiVernieuwen nagedacht, tja!

 


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