9. Tabellen verkleinen en vergroten

Je kan de opgestelde werkbladen met tabellen over annuïteitsleningen in Finalg.xls gemakkelijk aanpassen zodat het beginkapitaal of de rentevoet veranderen. Bij de constante annuïteitslening hoef je hiervoor enkel de getallen in de cellen D3 of D4 te wijzigen.

Wanneer je echter de duur van de lening wilt veranderen volstaat het niet het getal in cel D5 te wijzigen. Je moet ook nog rijen toevoegen of verwijderen in de tabel zelf. Je zal in dit hoofdstuk hiervoor een enigszins andere werkwijze volgen dan in hoofdstuk 3. Daarna zal je het wijzigen van de lengte van de tabel automatiseren met macro's.

Een macro is een opeenvolging van acties, dus een programma. Excel biedt de mogelijkheid macro's op te stellen die de uitgevoerde acties registreren, zodat je ze later kan herhalen. Dergelijke programma's zijn echter meestal weinig of niet algemeen bruikbaar. Daarom moet je meestal de opgestelde macro aanpassen zodat hij in een groot aantal toepassingen kan worden gebruikt.

De volgende zaken worden behandeld:

• De duur van de lening verminderen.
• De duur van de lening vermeerderen.
• De duur van de lening wijzigen met macro's.
• De Visual Basic toolbar.
• Microsoft Visual Basic for Applications (VBA).

De gebruikte werkbladen en modules met programma code worden in het werkboek finalg.xls uit het vorige hoofdstuk geplaatst.

9.1 De duur van de lening verminderen (*)

In het werkblad Constante annuïteit (en ook in CA 1, CA 2 en CA 3) bedraagt de duur van de lening 20 jaar. Wanneer je de duur wil verminderen tot 12 jaar moet je acht rijen uit de tabel verwijderen.

Je hebt in hoofdstuk 3 reeds gezien hoe je rijen uit een tabel verwijdert. Hierbij heb je zowel rijen vooraan als achteraan in de tabel verwijderd. In het hier voorkomende geval zal je rijen in het midden van de tabel verwijderen. De redenen hiervoor zijn de volgende:

• De eerste rij in de tabel bevat andere formules dan de overige rijen. Wanneer je ze verwijdert moet je de formules in de nieuwe eerste rij aanpassen.
• In de samenvattende rijen onderaan in de tabel wordt verwezen naar cellen uit de laatste rij. Wanneer je deze laatste rij verwijdert moet je de formules in de samenvattende rijen opnieuw opstellen.
• Veralgemeend komt het vaak voor dat het verwijderen van de eerste en/of de laatste rij extra problemen veroorzaakt. Daarom verwijder je deze vaak beter niet.
• Omdat er geen invoergegevens zijn is er geen extra werk zoals het verplaatsen van invoergegevens nodig wanneer je rijen in het midden van de tabel verwijdert. Wel zal je rijen uit het midden van de tabel naar beneden moeten kopiëren. Hierbij zullen de jaarnummers in de eerste kolom worden geëxtrapoleerd en de formules correct worden aangepast met een minimale inspanning.

Je zal de acht rijen van jaren 12 tot 19 verwijderen en de rij van jaar 11 daarna over de laatste rij van jaar 20 kopiëren, zodat het jaarnummer opnieuw 12 wordt en de formules in deze laatste rij opnieuw correct zijn.

Voer de volgende acties uit:

21:28:	Edit, Delete
A20:E20:	sleep fill handle naar A20:E21
D5:	12

De tabel in het werkblad heeft nu een tabel voor 12 jaren (figuur 1).

figuur 1: Het werkblad 'Constante annuïteit' - duur verminderd tot 12 jaar

Opmerkingen:

• Je kan de duur van de lening in cel D5 zowel vooraf als achteraf wijzigen.
• Na het verwijderen zullen in de cellen onderaan #REF! foutmeldingen optreden, die door het kopiëren van de rij van jaar 11 worden weggewerkt. Ook het laatste jaartal 20 wordt daarbij door de correcte waarde 12 vervangen.
• Het aantal getoonde jaren in de charts wordt bij deze werkwijze eveneens correct aangepast.
• Bij het verwijderen van rijen, riskeer je dat de afmetingen en/of plaats van de charts verandert. Je kan dit voorkomen door geen volledige rijen te verwijderen, maar slechts ranges in de kolommen A:E. Je moet dan in het Delete dialoogvenster Shift cells up aanstippen.
• Als alternatief kan je vooraf elk van de drie charts dubbelklikken en in het Properties tabblad van het Format Chart Area dialoogvenster Don't move or size with cells aankruisen. Wanneer je dit gedaan hebt, kan je hele rijen verwijderen zonder negatieve gevolgen voor de charts.

9.2 De duur van de lening vermeerderen (*)

Op een analoge wijze kan je de duur van de lening opnieuw verhogen van 12 naar 20 jaar. Ook hier zal je rijen toevoegen in het midden van de tabel. De redenen hiervoor zijn dezelfde als bij het verwijderen van rijen.

Je zal acht rijen toevoegen tussen jaren 11 en 12 en de rij van jaar 11 daarna over de acht toegevoegde rijen en de laatste rij kopiëren, zodat de jaarnummers en de formules in deze rijen opnieuw correct zijn.

Voer de volgende acties uit:

21:28	Insert, Rows
A20:E20:	sleep fill handle naar A20:E21
D5:	20

Het werkblad heeft nu weer zijn oorspronkelijke gedaante.

Opmerkingen:

• Je kan de duur van de lening in cel D5 kan zowel vooraf als achteraf wijzigen.
• Na het kopiëren zullen in cellen onderaan #NUM! foutmeldingen optreden, die door het wijzigen van de duur worden weggewerkt.
• Uiteraard is het niet nodig evenveel rijen toe te voegen als voorheen werden verwijderd.
• Het aantal getoonde jaren in de charts wordt bij deze werkwijze eveneens correct aangepast.
• Bij het toevoegen van rijen, riskeer je dat de afmetingen en/of plaats van de charts verandert. Je kan dit voorkomen door geen volledige rijen te toe te voegen, maar slechts ranges in de kolommen A:E. Je moet dan in het Insert dialoogvenster Shift cells down aanstippen.
• Als alternatief kan je vooraf elk van de drie charts dubbelklikken en in het Properties blad van het Format Chart Area dialoogvenster Don't move or size with cells aankruisen. Wanneer je dit gedaan hebt kan je hele rijen toevoegen zonder negatieve gevolgen voor de charts.

9.3 Het verminderen van de duur van de lening automatiseren

In tegenstelling tot het wijzigen van het bedrag van de lening en de rentevoet vraagt het wijzigen van de duur van de lening meerdere acties. Je kan deze automatiseren via macro's. Hierbij gebruik je de programmeertaal Microsoft Visual Basic for Applications, meestal afgekort tot VBA. Deze taal is een op BASIC gebaseerde programmeertaal die gedeeltelijk object georiënteerd is. VBA is de taal waarmee in alle delen van Microsoft Office kan worden geautomatiseerd, wat de uitwisseling tussen en het verbinden van de verschillende toepassingen en documenten vereenvoudigt.

Er wordt niet te veel op de theoretische achtergronden ingegaan. Je kan VBA, dat een afzonderlijk programma is, oproepen met het commando Tools, Macro, Visual Basic Editor of door de Visual Basic Editor knop te klikken in de Visual Basic toolbar.

Met het commando

Tools, Macro, Record New Macro...

of de Record Macro knop in de Visual Basic toolbar kan je een macro automatisch opstellen door de daaropvolgende acties en commando's te laten registreren.

Bij het registreren van commando's en acties wordt op een vrij slordige wijze een VBA programma gecreëerd in een module. De module wordt niet getoond tussen de andere bladen. Je kan hem bekijken met het commando Tools, Macro, Macros…, Edit knop.

Om dit VBA programma algemeen bruikbaar te maken zal je het grondig moeten aanpassen. Daarom moet je verschillende fases doorlopen.

9.3.1 Voorbereiding: Namen geven aan cellen en ranges

Bij macro's en VBA programma's is het handig met namen te werken. In de macro's gebruik je twee namen, die je op sheet-level niveau moet definiëren, om de macro te kunnen gebruiken in alle werkbladen die dezelfde namen bevatten:

Insert, Name, Define...
'Constante annuïteit'!n, Refers to: ='Constante annuïteit'!$D$5, Add,
'Constante annuïteit'!tabel, Refers to: ='Constante annuïteit'!$A$10$E$29, Add, Close

9.3.2 De VBA macro VerminderDuur registreren

Nu moet je de hierboven beschreven acties om de tabel in te krimpen tot 12 jaar opnieuw uitvoeren en in een macro registreren:

1. Definieer de macro:

Tools, Macro, Record New Macro...

Geef VerminderDuur als Macro name op in het Record Macro dialoogvenster (figuur 2).


figuur 2: Het Record Macro dialoogvenster

De naam van een macro moet juist zoals een andere naam aan bepaalde regels voldoen: Hij moet met een letter beginnen en mag geen spaties bevatten. Je kan de macro met een ctrl toetsencombinatie associëren (Shortcut key). Je kan aangeven in welk werkboek de macro wordt bewaard (Store macro in: Personal Macro Workbook, New Workbook of This Workbook – standaard). Je kan eveneens de voorgestelde beschrijving wijzigen (Description). Nadat je op OK hebt gedrukt, verschijnt het bericht Recording in de statusbalk en is de Stop Recording toolbar zichtbaar met twee knoppen: Stop Recording en Relative Reference.

2. Voer de nodige acties uit in het werkblad:

Selecteer de range van de acht te verwijderen rijen A21:E28 (bij een macro is het zeker veiliger ranges dan rijen te verwijderen).

Verwijder deze range met het commando Edit, Delete..., Shift cells up.

Selecteer de range van het voorlaatste jaar A20:E20 en extrapoleer die naar A20:E21.

Selecteer de duur n in de name list links in de formulebalk (D5) en vul in deze cel de nieuwe duur 12 in.

3. Klik de Stop Recording knop in de Stop Recording toolbar of kies het commando

Bekijk nu de geregistreerde macro. Dit doe je als volgt:

1. Kies het commando

Tools, Macro, Macros…

Selecteer de macro VerminderDuur in het Macro dialoogvenster (figuur 3) in de lijst.


figuur 3: Het Macro dialoogvenster

Je kan er nu een aantal bewerkingen mee uitvoeren:

• Voer de macro uit (Run).
• Voer de macro stapsgewijze uit (Step Into).
• Bekijk en wijzig de macro (Edit).
• Maak een nieuwe macro (Create). Dit kan je enkel wanneer je een Macro name hebt ingetypt, die nog niet in de lijst staat.
• Verwijder de macro (Delete).
• Toon het Macro Options dialoogvenster (Options...) en associeer de macro met een toetsencombinatie (Shortcut key), of geef een beschrijving voor de macro (Description).

Selecteer de macro VerminderDuur en klik de Edit knop.

2. Nu wordt het VBA programma geactiveerd. Dit heeft een afzonderlijke Standard menubalk en Standard toolbar. In het Module venster Finalg.xls – Module1 (Code) wordt de macro getoond. Vergroot dit venster totdat de hele tekst zichtbaar is (figuur 4).

figuur 4: De macro 'VerminderDuur'

Hierna volgt een bespreking van de inhoud van het venster Module1 (Code):

• Bovenaan in het document venster bevinden zich twee lijsten. In de linkse lijst kan je de code voor een Object klasse kiezen (standaard: (General) ). In de rechtse lijst kan je een Procedure kiezen (VerminderDuur).
• In VBA wordt de macro een macro procedure genoemd, waarvan het begin wordt aangeduid met het woord Sub (in blauw), gevolgd door de naam van de macro procedure Verminderuur en een paar haakjes (). Het einde wordt aangeduid met de woorden End Sub (eveneens in blauw). Tussen deze regels staan commentaarregels (in groen), voorafgegaan door een ' ) en instructies, die een weergave zijn van de geregistreerde acties.
• De commentaarregels (in groen) bevatten de naam van de macro en commentaar opgegeven bij het definiëren van de macro. Dergelijke commentaarregels beginnen steeds met een ' en worden niet uitgevoerd. Zolang je dit ' niet verwijdert kan je ze naar eigen smaak wijzigen zonder dat dit invloed zal hebben op de macro bij zijn uitvoering.
• Elke instructie begint op een nieuwe regel. Indien een instructie te lang is om op één regel te worden geplaatst, wordt de eerste regel beëindigd met een spatie en een _ en gaat daarna verder op de volgende regel. Het inspringen van de instructies maakt de procedure beter leesbaar, maar is niet vereist.

De inhoud van de procedure VerminderDuur is:

Sub VerminderDuur()
'
' VerminderDuur Macro
' Macro recorded 11/09/97 by Marc Andries
'

'
    Range("A21:E28").Select
    Selection.Delete Shift:=xlUp
    Range("A20:E20").Select
    Selection.AutoFill Destination:=Range("A20:E21"), Type:= _
        xlFillDefault
    Range("A20:E21").Select
    Application.Goto Reference:="n"
    ActiveCell.FormulaR1C1 = "12"
End Sub

Vele VBA instructies zijn samengesteld uit objects, properties en methods. Een object is een voorwerp dat eigenschappen (properties) kan hebben en waarop acties (methods) kunnen worden uitgevoerd. Objects, hun properties en methods worden in een instructie van elkaar gescheiden door een punt . . Hierbij worden de volgende syntaxisregels gebruikt:

Het resultaat van een object.method bewerking kan opnieuw een object zijn. In vele gevallen is het zonder meer duidelijk over welk object het gaat en wordt enkel de naam van de method vermeld.

Methods kunnen argumenten hebben, net zoals functies. Deze argumenten worden door komma's gescheiden.

• Indien het resultaat van een method niet verder gebruikt wordt, zullen de argumenten, eventueel voorafgegaan door hun naam en de tekens :=, door een spatie gescheiden worden van de naam van de method.
• Indien het resultaat van een method wel verder gebruikt wordt, zullen de argumenten tussen haakjes staan. Dit is ook steeds het geval bij functies.

Hierna volgt de bespreking van de opeenvolgende instructies (met uitzondering van de commentaarregels):

1. Sub VerminderDuur()

duidt het begin aan van een macro procedure en geeft ze een naam.

2. Range("A21:E28").Select

selecteert de range A21:E28. De Range method bepaalt het range object, aangeduid door het argument tussen haakjes ("A21:E28"). Dit object wordt geselecteerd door de Select method. Hierbij wordt de eerste cel uit de range geactiveerd.

3. Selection.Delete Shift:=xlUp

verwijdert de cellen in de geselecteerde range. De Selection property van het niet vermelde Application object (Excel) bepaalt een range object. De Delete method verwijdert deze geselecteerde cellen. Het argument Shift geeft aan dat de eronder liggende cellen naar boven moeten opschuiven. Dit wordt aangeduid door de Excel constante xlUp.

Deze en de vorige instructie kunnen worden ingekort tot:

Range("A21:E28").Delete Shift:=xlUp

4. Range("A20:E20").Select

selecteert de range A20:E20.

5. Selection.AutoFill Destination:=Range("A20:E21"), Type:= _
xlFillDefault

extrapoleert de geselecteerde range naar de range A20:E21. De Selection property van het niet vermelde Application object (Excel) bepaalt een range object. De AutoFill method extrapoleert deze geselecteerde cellen. Het eerste argument Destination geeft aan dat de selectie moet worden uitgebreid naar het object dat door het tussen haakjes staande argument ("A20:E21") van de Range method wordt bepaald. Het tweede argument Type geeft aan dat de extrapolatie op de standaard wijze moet gebeuren. Dit wordt aangeduid door de Excel constante xlFillDefault.

Deze en de vorige instructie kunnen worden ingekort tot:

Range("A20:E20").AutoFill _
Destination:=Range("A20:E21"), Type:=xlFillDefault

6. Range("A20:E21").Select

selecteert de geëxtrapoleerde range A20:E21. Ze kan worden weggelaten.

7. Application.Goto Reference:="n"

selecteert de range aangeduid door de naam n. Application (Excel) is het object en Goto de method met argument Reference.

Deze instructie kan worden vervangen door de equivalente instructie

Range("n").Select

8. ActiveCell.FormulaR1C1 = "12"

plaatst de formule ="12" in de geactiveerde cel (met naam n). ActiveCell is een property van het niet vermelde Application object (Excel) en bepaalt de geactiveerde cel. FormulaR1C1 is een property van de (geactiveerde) cel die de formule van de cel aangeeft. Deze wordt hier als tekst opgegeven, maar kan beter als getal worden aangegeven.

Deze en de vorige instructie kunnen worden ingekort tot:

Range("n").FormulaR1C1 = 12

9. End Sub

Samenvatting:

• De VBA macro procedure VerminderDuur wordt begrensd door het instructiepaar Sub VerminderDuur() en End Sub.
• Een instructie in een VBA macro procedure kan onder meer de vorm hebben:
  object.method argument1:=expression1, argument2:=expression2 ...
object.property=expression
• In de macro procedure VerminderDuur komen als objects voor: de Application Excel en ranges.
• In de macro procedure VerminderDuur komen als methods voor: Range, Select, Delete, AutoFill, en Goto.
• In de macro procedure VerminderDuur komen als properties voor: Selection, ActiveCell en FormulaR1C1.

9.3.3 De VBA macro VerminderDuur veralgemenen

Het heeft geen zin de geregistreerde macro nu uit te voeren. Hij zal enkel correct functioneren wanneer je de tabel A10:E29 wil terugbrengen naar een tabel A10:E21, d.w.z. wanneer je het aantal jaren in de tabel wil verminderen van 20 naar 12. Ook wanneer de tabel op een andere plaats zou beginnen of een ander aantal kolommen zou bevatten zou de procedure niet werken.

Je moet de procedure zodanig maken dat ze zal functioneren bij het verminderen van de duur tot een willekeurige (gehele) waarde. Ze moet ook bruikbaar zijn om het aflossingsplan met constante aflossingen (één kolom meer!) te verkleinen.

Hierbij ga je in een aantal stappen te werk.

1. Activeer VBA en open de macro VerminderDuur om ze te bewerken (dit is reeds gedaan):

Tools, Macro, Macros…, VerminderDuur, Edit

2. Omdat het wijzigen van de macro een delicate operatie is, is het aangewezen de macro eerst te dupliceren, de naam van de oorspronkelijke versie te wijzigen in VerminderDuur1 en vervolgens de gekopieerde versie te wijzigen. Aangezien de modules tekstdocumenten zijn kan je de gewone tekstverwerkingstechnieken toepassen om de procedure VerminderDuur te kopiëren:

Selecteer (sleep over) de tekst.

Kopieer de tekst naar het clipboard met de Copy knop.

Ga naar het einde van de module (klik onder het einde van de procedure).

Plak de tekst vanuit het clipboard met de Paste knop. Er zijn nu twee gelijknamige procedures VerminderDuur in de Procedure lijst.

Wijzig in het venster de naam van de oorspronkelijke of gekopieerde procedure in VerminderDuur1 in de Sub instructie en in de commentaarregel. In de Procedure lijst zijn er nu twee procedures VerminderDuur en VerminderDuur1.

3. Begin met de macro procedure VerminderDuur te vereenvoudigen zoals in de bovenstaande bespreking:

   Sub VerminderDuur()
       Range("A21:E28").Delete Shift:=xlUp
       Range("A20:E20").AutoFill _
           Destination:=Range("A20:E21"), Type:=xlFillDefault
       Range("n").FormulaR1C1 = 12
   End Sub
   

4. Veralgemeen daarna de macro procedure VerminderDuur tot:

   Sub VerminderDuur()
       lengte = Range("tabel").Rows.Count
       breedte = Range("tabel").Columns.Count
       duur = CInt(InputBox( _
           Prompt:="Geef een nieuwe waarde voor de duur", _
           Title:="Duur", _
           Default:=lengte))
       Range("tabel") _
           .Range(Cells(duur,1), Cells(lengte-1,breedte)) _
           .Delete Shift:=xlUp
       Range("tabel") _
           .Range(Cells(duur-1,1), Cells(duur-1,breedte)) _
           .AutoFill _
               Destination:=Range("tabel") _
                   .Range(Cells(duur-1,1), Cells(duur,breedte)), _
               Type:=xlFillDefault
       Range("n").FormulaR1C1 = duur
       Application.Goto Reference:=Range("A1")
   End Sub
   

Deze wijzigingen vergen enige uitleg:

1. Sub VerminderDuur()
2. lengte = Range("tabel").Rows.Count
3. breedte = Range("tabel").Columns.Count

   Deze twee instructies wijzen het aantal rijen en kolommen van het range object tabel toe aan de variables lengte en breedte. Range, Rows en Columns zijn methods, Count is een property.

4. duur = CInt(InputBox( _
       Prompt:="Geef een nieuwe waarde voor de duur", _
       Title:="Duur", _
       Default:=lengte))

   doet een InputBox dialoogvenster verschijnen, waarin je de nieuwe duur kan opgeven. Deze wordt als een tekst beschouwd en door de Cint(text) functie omgezet tot een geheel getal en daarna aan de variable duur toegewezen. InputBox(prompt,title,default) is een functie met drie argumenten, die om de leesbaarheid te bevorderen telkens op een nieuwe en ingesprongen regel worden vermeld.

   
   figuur 5: De Object Browser

   Wanneer de cursor juist gepositioneerd is (na het = teken) kan je de InputBox functie aanbrengen met behulp van de Object Browser knop in de Standard toolbar van Microsoft Visual Basic. De werkwijze is enigszins te vergelijken met die van het opstellen van een functie m.b.v. het Paste Function dialoogvenster, maar de mogelijkheden zijn veel meer uitgebreid. In het Object Browser venster (figuur 5) bevinden zich de elementen waarmee je een VBA instructie kunnen opstellen:

   • De Project/Library lijst: Je kan een library (.olb) bestand kiezen. In de twee onderste lijsten worden dan enkel de elementen uit de gekozen library getoond.

     Wanneer je een Library kiest wordt links de Classes lijst getoond, die de classes van de gekozen library bevat. Je kan een class vergelijken met een Function Category in het Paste Function dialoogvenster.

     Wanneer je een class kiest wordt rechts de Members of lijst getoond, die de elementen (members) van de gekozen class bevat. Dit kunnen objecten en functies zijn.

     Onderaan staat de syntaxis van de gekozen member.

   • De Search Text lijst bevat de vier laatst ingetypte teksten. Je kan er één kiezen of zelf een tekst intypen, waarbij je wildcards kan gebruiken. Daarna wordt deze tekst in het Module venster gezocht.

     Met de Go Back, Go Forward, Copy to Clipboard, Show Definition, Help, Search en Show/Hide Search Results knoppen kan je het zoekproces beheren en de gevonden resultaten naar het Module venster kopiëren.

     Wanneer je de Search of de Show/Hide Search Results knopt indrukt, wordt het Object Browser venster boven de Classes en Members of lijsten uitgebreid met een Search Results lijst die drie kolommen bevat: Library, Class en Member. Hierin staat aangegeven in welke libraries, classes en members de gezochte tekst voorkomt.

   Kies de VBA library, de Interaction class (die bevat functies voor interactieve invoer en uitvoer) en de InputBox member. De syntaxis van de InputBox functie is:

   Function InputBox(Prompt, [Title], [Default], [XPos], [YPos], [HelpFile], [Context]) As String

   Deze functie bevat zeven argumenten en heeft als resultaat een tekst (string). De vijf argumenten tussen [ ] zijn optioneel. De komma's duiden de plaats aan. Je mag ze weggelaten wanneer alle volgende argumenten ontbreken.

   De betekenis van de zeven argumenten van de InputBox functie is:

   Prompt	De hierna volgende tekst wordt getoond in het dialoogvenster.
   Title	De hierna volgende tekst vormt de titel van het dialoogvenster.
   Default	De hierna volgende waarde wordt als antwoord voorgesteld.
   XPos en YPos	De positie op het scherm van (de linkerbovenhoek van) het dialoogvenster.
   HelpFile	Een bestand met Help uitleg over de functie.
   Context	Een volgnummer (index), dat aangeeft de hoeveelste uitleg in het Help bestand over deze functie gaat.

   Kopieer de InputBox functie met de Copy knop, sluit de Object Browser en plak de InputBox functie met de Paste knop. Enkel de functienaam wordt geplakt. Je moet de haakjes en gewenste argumenten zelf aanbrengen. Nadat je het ( hebt ingetypt, verschijnt echter de syntaxis van de argumenten in een kadertje.

   Vul voor de eerste drie argumenten de gewenste waarden in. Let er op dat letterlijk af te drukken teksten tussen " " moeten staan.

   Je kan de argumenten van de InputBox functie op twee wijzen aanbrengen:

   • In volgorde, waarbij je de namen van de argumenten en de := symbolen weglaat:

     InputBox("Geef een nieuwe waarde voor de duur", _
        "Duur" , lengte)
     

   • Niet in volgorde, waarbij je de betekenis van de argumenten aangeeft door hun naam en de := symbolen:

     InputBox(Title:="Duur" , _
        Prompt:="Geef een nieuwe waarde voor de duur", _
        Default:=lengte)
     

   De tweede methode is langer, maar duidelijker voor de lezer. Er is daarenboven geen reden om de volgorde te wijzigen (wanneer je die kent).

   Het resultaat is een tekst, maar moet tot een geheel getal worden omgezet. Dit kan je doen met de CInt functie, die je in de Conversion class van de VBA library kan vinden. De InputBox functie vormt het argument van de CInt functie.

   Splits voor de duidelijkheid de instructie op in vier regels zodat elk argument van de InputBox functie op een nieuwe regel begint.

5. Range("tabel") _
       .Range(Cells(duur,1), Cells(lengte-1,breedte)) _
       .Delete Shift:=xlUp

   Het argument "A21:E28" van de Range method in de instructie

   Range("A21:E28").Delete Shift:=xlUp
   

   is aangepast, zodat het wordt uitgedrukt in functie van de range met naam tabel en de variabelen lengte, breedte en duur.

   • De eerste rij in de range is rij 12 in de tabel (A10:E29) en wordt aangegeven door duur.
   • De eerste kolom in de range is kolom 1 in de tabel.
   • De laatste rij in de range is rij 19 in de tabel en wordt aangegeven door lengte–1.
   • De laatste kolom in de range is kolom 5 in de tabel en wordt aangegeven door breedte.

   In plaats van de absolute notatie "A21:E28" voor het argument van de Range method, is de range relatief aangeduid t.o.v. de eerste cel in de tabel (A10). In de nieuwe gedaante zijn Range, Cells en Delete methods. De eerste Range method bepaalt de tabel als object; de tweede Range method bepaalt de uit tabel te verwijderen rijen als object. Met de Cells method worden de relatieve rij- en kolomnummers van de linkerboven- en rechterbenedencel van de range A21:E28 als getallen uitgedrukt in functie van de variabelen duur, lengte en breedte t.o.v. de eerste cel A10 in de range A10:E29. De nummering begint bij (1,1).

   De betekenis van de gewijzigde instructie is dus:

   Range("tabel").Range(Cells(duur,1), Cells(lengte-1,breedte)) _ .Delete Shift:=xlUp

   of Range("A10:E29").Range(Cells(12,1), Cells(19,5)) _
   .Delete Shift:=xlUp

   of Range("A10").Range("A12:E19")).Delete Shift:=xlUp

   of Range("A21:E28").Delete Shift:=xlUp

   De nieuwe formulering is ogenschijnlijk ingewikkelder dan de oude, maar veel meer algemeen bruikbaar.

6. Range("tabel") _
      .Range(Cells(duur-1,1), Cells(duur-1,breedte)) _
      .AutoFill _
          Destination:=Range("tabel") _
              .Range(Cells(duur-1,1), Cells(duur,breedte)), _
          Type:=xlFillDefault
   

   De instructie

   Range("A20:E20").AutoFill _
       Destination:=Range("A20:E21"), Type:=xlFillDefault
   

   is op een analoge wijze gewijzigd.

   • De eerste en laatste rij in de range is rij 11 in de tabel (A10:E21) en wordt aangegeven door duur–1.
   • De eerste kolom in de range is kolom 1 in de tabel.
   • De laatste kolom in de range is kolom 5 in de tabel en wordt aangegeven door breedte.

   Voor het Destination argument van de AutoFill method is er één verschil:

   • De laatste rij is rij 12 in de tabel en wordt aangegeven door duur.

   In plaats van de absolute notatie "A20:E20" voor het argument van de Range method, is de range relatief aangeduid t.o.v. de eerste cel in de tabel (A10). In de nieuwe gedaante zijn Range, Cells en Delete methods. De eerste Range method bepaalt de tabel als object; de tweede Range method bepaalt de te extrapoleren rij in de tabel als object. Hetzelfde geldt voor het Destination argument.

   De betekenis van de gewijzigde instructie is dus:

        Range("tabel") _
            .Range(Cells(duur-1,1), Cells(duur-1,breedte)) _
            .AutoFill _
                Destination:=Range("tabel") _
                    .Range(Cells(duur-1,1), Cells(duur,breedte)), _
                Type:=xlFillDefault
   

   of

        Range("A10:E21").Range(Cells(11,1), Cells(11,5)) _
               .AutoFill _
                   Destination:=Range("A10:E21") _
                       .Range(Cells(11,1), Cells(12,5)), _
                   Type:=xlFillDefault
   

   of

        Range("A10").Range("A11:E11")).AutoFill _
               Destination:=Range("A10").Range("A11:E12")), _
               Type:=xlFillDefault
   

   of

        Range("A20:E20").AutoFill _
               Destination:=Range("A20:E21") _
               Type:=xlFillDefault
   

7. Range("n").FormulaR1C1 = duur

   In deze instructie werd het getal 12 vervangen door de variabele duur.

8. Application.Goto Reference:=Range("A1")

   Deze instructie werd toegevoegd. Ze brengt op het einde van de macro opnieuw het begin van het werkblad (cel A1) in beeld. Hierin is de Application (Excel) het object en Goto de method met als argument het range object, bepaald met de method Range("A1").

9. End Sub

Het voordeel van deze veralgemeende procedure is dat ze toepasbaar is op alle aflossingstabellen waarbij de namen tabel en n gedefinieerd zijn zoals in de tabel Constante annuïteit. Wij zullen deze macro dus niet enkel kunnen toepassen op het werkblad Constante annuïteit, maar ook op het werkblad Constante aflossing!

Opmerkingen:

• Bij het uitvoeren van het commando Edit, Delete of de corresponderende instructie in de macro worden acht rijen geschrapt. Hierdoor zal het label tabel vóór het uitvoeren van dit commando naar de oorspronkelijke tabel (A10:E29) en erna naar de ingekrompen tabel (A10:E21) verwijzen. Dit is juist zoals gewenst. Wanneer je niet met een label, maar met een referentie werkt, moet je deze wijziging in alle instructies zelf programmeren met Range en Cells methods. Hierdoor wordt de macro heel wat complexer, minder duidelijk en meer vatbaar voor fouten.
• Wanneer je als nieuwe duur de waarde 1 of 2 opgeeft zal er een fout optreden omdat de rij vóór het laatste jaar na het AutoFill commando niet meer de correcte formules zal bevatten. Je moet de macro verder bewerken om hiervoor een oplossing te zoeken (zie infra).

9.3.4 De VBA macro VerminderDuur testen en uitvoeren

Je kan nu de uitvoering van de procedure testen, door bijvoorbeeld het aantal jaren (nu 12) verder te verminderen tot 9. Het is aan te bevelen dit stapsgewijs te doen. Ga als volgt te werk:

1. Activeer het werkblad Constante annuïteit.
2. Voer de macro uit met het commando

Tools, Macro, Macros...

Kies in het Macro dialoogvenster de macro VerminderDuur. Bij een eerste test zal je de macro best stapsgewijs uitvoeren. Klik daarom de Step Into knop.

3. De stapsgewijze uitvoering van de macro gebeurt door het programma VBA. In het Finalg.xls – Module1 (Code) venster wordt de eerste regel

   Sub VerminderDuur()

   met een gele achtergrond aangegeven en door een gele pijl in de linkerkantlijn aangeduid. Je bevindt je in de Break mode. Dit wordt aangeduid in de titelbalk van VBA.

4. Je kan nu met het commando Debug, Step Into of functietoets F8 de instructies één na één uitvoeren. Je hebt hierbij echter geen zicht op het werkblad zelf. Met de eerste knop View Excel Worksheet uit de Standard toolbar van VBA, kan je het Excel venster terug in beeld brengen. Doe dit. Zorg er daarna voor dat Excel en VBA samen in beeld zijn. Klik daartoe in beide vensters de Restore knop en sleep de randen van de vensters. Laat Excel zowat twee derde van het scherm innemen (links) en VBA het overige derde (rechts). Zorg ervoor dat het begin van het werkblad Constante annuïteit in het Excel venster wordt getoond en het begin van alle instructies van de macro VerminderDuur in het VBA venster.
5. Het is interessant dat tijdens de uitvoering van de macro procedure niet alleen het werkblad, maar ook de evolutie van de variabelen lengte, breedte en duur worden getoond. Dit kan je doen in het Watches venster van VBA. Plaats eerst de drie variabelen in het Watches venster met het commando (in VBA)

Debug, Add Watch…

Vul in het Expression vak van het Add Watch dialoogvenster de naam van de variabele lengte in en klik OK. Het Watches venster wordt nu getoond met daarin de variabele lengte. Herhaal daarna het commando Debug, Add Watch… voor de variabelen breedte en duur. Pas tot slot de afmetingen van het Watches dialoogvenster aan (sleep met de randen van het venster en de titels) en plaats het venster ergens waar het zo weinig mogelijk hindert.


figuur 6: Het Add Watch dialoogvenster
6. Klik nu in het VBA venster en voer de instructies stapsgewijs uit (met functietoets F8 of het commando Debug, Step Into). Volg hierbij de wijzigingen van het werkblad in het Excel venster en van de variabelen in het Watches venster (figuur 7). Indien er geen fouten optreden kan je de uitvoering van de macro stap na stap volgen. Indien een fout optreedt, krijg je hierover een bericht in een dialoogvenster. Afhankelijk van de aard van de fout heb je een aantal mogelijkheden, zoals verdergaan (Continue), de macro beëindigen (End), de fout bekijken in het VBA venster (Debug).

figuur 7: de Excel, VBA en Watches vensters tijdens het uitvoeren van de macro 'VerminderDuur'
7. De procedure wordt beëindigd wanneer je aan End Sub komt. In de titelbalk van het VBA venster wordt dan geen (Break) meer vermeld. Sluit nu het Visual Basic venster en maximaliseer het Excel venster opnieuw.

De tabel bevat nu nog slechts 9 jaren.

Nadat je de macro een eerste maal stapsgewijs hebt getest, kan je hem nu een tweede maal testen, waarbij je hem in één ruk uitvoert. Dit doe je met het commando

Nu wordt onmiddellijk het InputBox dialoogvenster Duur getoond. Nadat je de nieuwe duur (bijvoorbeeld 7) hebt opgegeven, wordt het werkblad onmiddellijk aangepast. Bij deze uitvoering zie je de achtereenvolgende resultaten van de instructies van de macro in het werkblad, maar je ziet de instructies zelf niet.

Wanneer de macro op deze wijze tweemaal is getest en er geen fouten zijn opgetreden, kan je het werkboek met de macro bewaren.

Opmerkingen:

• Bij het uitvoeren van de instructie duur= CInt(InputBox(...)) verschijnt het InputBox dialoogvenster dat als naam Duur heeft gekregen (figuur 8).

figuur 8: Het InputBox dialoogvenster 'Duur'
• Met het commando

Debug, Edit Watch...

• Wanneer je het invoegpunt in het VBA venster op een watch variabele plaatst, kan je met het commando

  Debug, Quick Watch...

  de waarde daarvan bekijken in het Quick Watch dialoogvenster (figuur 9). Wanneer je de betreffende variabele nog niet als watch variabele hebben gedefinieerd kan je hem met de Add knop in de lijst in het Watches venster opnemen.


figuur 9: Het Quick Watch dialoogvenster
• Wanneer je een werkboek opent, dat een macro bevat, verschijnt een waarschuwing tegen virussen, die zich in de macro kunnen hebben genesteld en schade kunnen berokkenen wanneer je de macro uitvoert (figuur 10). Wanneer je de macro zelf hebt opgesteld, kan je de macro's bruikbaar maken met Enable Macros. Wanneer je een werkboek met macro van een vreemde hebt gekregen, doe je er best aan de macro onbruikbaar te maken (voor deze sessie) met Disable Macros of zelfs helemaal niet te openen met Do Not Open, tot je het bestand met een anti-virus programma veilig hebt bevonden.

figuur 10: Het dialoogvenster dat waarschuwt tegen macro-virussen

9.4 Het vermeerderen van de duur van de lening automatiseren

Stel nu op dezelfde wijze als de macro VerminderDuur ook een macro VermeerderDuur op die het aantal jaren in de tabel vergroot. Breid als voorbeeld de tabel opnieuw uit van 7 (of 9) tot 20 jaar.

9.4.1 De VBA macro VermeerderDuur registreren

Registreer de commando's voor de macro VermeerderDuur:

1. Definieer de macro:

Tools, Macro, Record New Macro...

Geef VermeerderDuur als Macro name op in het Record Macro dialoogvenster. Nadat je op OK hebt gedrukt, verschijnt het bericht Recording in de statusbalk en is de Stop Recording toolbar zichtbaar met twee knoppen.

2. Voer de nodige acties uit in het werkblad:

Selecteer de range van de dertien tussen te voegen rijen A16:E28 (bij een macro is het zeker veiliger ranges dan rijen toe te voegen).

Voeg op de plaats van deze range lege cellen toe met het commando Insert, Cells..., Shift cells down.

Selecteer de range van het jaar boven de toegevoegde cellen A15:E15 en extrapoleer die naar A15:E29.

Selecteer de duur n in de name list links in de formulebalk (D5) en vul in deze cel de nieuwe duur 20 in.

3. Klik de Stop Recording knop of kies het commando

Tools, Macro, Stop Recording

Het registreren wordt beëindigd.

4. Bekijk de macro VermeerderDuur met het commando

9.4.2 De VBA macro VermeerderDuur veralgemenen

Pas de procedure VermeerderDuur op analoge wijze aan als VerminderDuur:

1. Activeer VBA en open de macro VermeerderDuur om ze te bewerken (dit is reeds gedaan):

Tools, Macro, Macros…, VermeerderDuur, Edit

2. Dupliceer de macro, wijzig de naam van de oorspronkelijke versie in VermeerderDuur1 en wijzig vervolgens de gekopieerde versie:

Selecteer (sleep over) de tekst van de oorspronkelijke VermeerderDuur.

Kopieer de tekst naar het clipboard met de Copy knop.

Ga naar het einde van de module (klik onder het einde van de procedure).

Plak de tekst vanuit het clipboard met de Paste knop. Er zijn nu twee gelijknamige procedures VermeerderDuur in de Procedure lijst.

Wijzig in het venster de naam van de oorspronkelijke of gekopieerde procedure in VermeerderDuur1 in de Sub instructie en in de commentaarregel. In de Procedure lijst zijn er nu twee procedures VermeerderDuur en VermeerderDuur1.

3. Vereenvoudig de macro procedure VermeerderDuur eerst tot:

Sub VermeerderDuur()

Range("A16:E28").Insert Shift:=xlDown

Range("A15:E15").AutoFill _

Destination:=Range("A15:E29"), Type:=xlFillDefault

Range("n").FormulaR1C1 = 20

End Sub

4. Veralgemeen de macro procedure VermeerderDuur tot:

De betekenis van de twee instructies met de Cells methods is:

• Range("tabel") _
  .Range(Cells(lengte,1), Cells(duur-1,breedte)) _
  .Insert Shift:=xlDown

• Range("tabel") _
  .Range(Cells(lengte-1,1), Cells(lengte-1,breedte)) _
  .AutoFill _
  Destination:=Range("tabel") _
  .Range(Cells(lengte-1,1), Cells(duur,breedte)), _
  Type:=xlFillDefault

9.4.3 De VBA macro VermeerderDuur testen en uitvoeren

Je kan nu deze nieuwe macro VermeerderDuur eveneens tweemaal testen en uitvoeren met het commando

Tools, Macro, Macros…, kies VermeerderDuur, Step Into (1ste test) en Run (2de test)

Vermeerder hierbij het aantal jaren tot 23 en 25. Verminder daarna opnieuw het aantal jaren tot 20 met de macro VerminderDuur.

9.5 Het verminderen en vermeerderen van de duur in één macro automatiseren

Het is aangewezen de twee procedures VerminderDuur en VermeerderDuur te integreren in één procedure WijzigDuur en deze aan een knop in het werkblad te koppelen.

9.5.1 De VBA macro WijzigDuur opstellen

Je stelt de procedure WijzigDuur als volgt op:

1. Open het VBA venster met het commando

Tools, Macro, Visual Basic Editor

en maximaliseer dit venster. VBA is een afzonderlijk programma en bevat reeds twee documenten, FINALG.XLS – Module1 (Code) met de procedures VerminderDuur en VerminderDuur1, en FINALG.XLS – Module2 (Code) met de procedures VermeerderDuur en VermeerderDuur1.

2. Voeg een derde module toe met het commando

Insert, Module

of met de Insert Module knop (klap de lijst van de 2de knop in de Standard toolbar van VBA open en kies Insert Module). Dit nieuwe document krijgt de naam FINALG.XLS – Modulen (Code)

3. Selecteer de volledige tekst van de procedure VerminderDuur en kopieer die via het Clipboard naar de nieuwe module.
4. Wijzig in de nieuwe module de naam van de macro in WijzigDuur (tweemaal: in de Sub instructie en de commentaarregel).
5. Voeg drie instructies toe:

vóór de eerste Range instructie:	If duur<lengte Then
na de tweede Range instructie:	ElseIf duur>lengte Then
	End If

6. Selecteer de tekst van de eerste twee Range instructies van de procedure VermeerderDuur en kopieer die via het Clipboard tot tussen de ElseIf en de End If instructies in de nieuwe module.
7. Laat de vier Range instructies tussen If en End If verder inspringen.

Het resultaat is:

'

' Macro recorded 11/09/97 by Marc Andries

'

'

lengte = Range("tabel").Rows.Count

breedte = Range("tabel").Columns.Count

duur = CInt(InputBox( _

prompt:="Geef een nieuwe waarde voor de duur", _

Title:="Duur", _

Default:=lengte))

Range("tabel") _

.Range(Cells(duur, 1), Cells(lengte-1, breedte)) _

.Delete Shift:=xlUp

Range("tabel") _

.Range(Cells(duur-1, 1), Cells(duur-1, breedte)) _

.AutoFill _

Destination:=Range("tabel") _

.Range(Cells(duur-1, 1), Cells(duur, breedte)), _

Type:=xlFillDefault

Range("tabel") _

.Range(Cells(lengte, 1), Cells(duur-1, breedte)) _

.Insert Shift:=xlDown

Range("tabel") _

.Range(Cells(lengte-1, 1), Cells(lengte-1, breedte)) _

.AutoFill _

Destination:=Range("tabel") _

.Range(Cells(lengte-1, 1), Cells(duur, breedte)), _

Type:=xlFillDefault

Range("n").FormulaR1C1 = duur

Application.Goto reference:=Range("A1")

End Sub

De toegevoegde en gewijzigde instructies worden in vetjes weergegeven.

Bespreking:

• Het gedeelte vóór de If en na de End If instructie is gemeenschappelijk. Het komt in de procedures VerminderDuur en VermeerderDuur voor en wordt steeds uitgevoerd.
• Het gedeelte tussen If duur<lengte Then en ElseIf komt uit de procedure VerminderDuur en wordt enkel uitgevoerd indien de voorwaarde duur<lengte voldaan is (het aantal rijen in de tabel wordt verminderd).
• Het gedeelte tussen ElseIf duur>lengte Then en End If komt uit de procedure VermeerderDuur en wordt enkel uitgevoerd indien de voorwaarde duur>lengte voldaan is (het aantal rijen in de tabel wordt vermeerderd).
• Indien duur=lengte wordt het gedeelte tussen If en End If niet uitgevoerd en blijft de tabel onveranderd.

Opmerkingen:

• Je kan de procedure WijzigDuur eveneens uittesten en uitvoeren. Ze zal echter tot foutieve resultaten leiden wanneer je het aantal jaren vermindert tot minder dan 3. Je kan dit voorkomen door de procedure zodanig aan te passen dat enkel waarden voor de duur die 3 of meer bedragen worden aanvaard:

• De CInt functie was niet essentieel in de procedures VerminderDuur en VermeerderDuur. In de procedure WijzigDuur wordt het tekstresultaat van de InputBox functie vergeleken met het getal uit de variabele lengte. Indien je geen CInt functie voorziet zal vóór de vergelijking het getal uit de variabele lengte worden omgezet naar een tekst. Vervolgens zullen dan de tekstwaarden (bijvoorbeeld "9" en "12") worden vergeleken volgens hun ASCII waarden, wat tot een verkeerd besluit kan leiden ("9">"12" !), zodat de verkeerde instructies worden uitgevoerd. Die kunnen tot een fout leiden. Door de CInt functie toe te voegen zullen twee getallen worden vergeleken en zal het resultaat steeds zoals gewenst zijn.

Sluit daarna het VBA venster.

9.5.2 De VBA macro WijzigDuur aan een knop in het werkblad koppelen

Wanneer je nu de macro WijzigDuur wilt uitvoeren moet je telkens het commando Tools, Macro, Macros... uitvoeren. Je kan het proces versnellen en vereenvoudigen door in het werkblad Constante annuïteit een knop aan te brengen en deze met de macro te verbinden. Dit gaat op een bijzonder eenvoudige wijze:

1. Toon de Forms toolbar met het commando

View, Toolbars, Forms


2. Klik de Button knop in de Forms toolbar.
3. In de statusbalk verschijnt de tekst: Drag in document to create a button. Sleep een gebied uit in cellen E4:E5 voor de aan te brengen knop. Deze knop krijgt de voorlopige naam Button 1.
4. Het Assign Macro dialoogvenster wordt getoond (figuur 12). Kies hierin de naam WijzigDuur van de macro die met de knop verbonden moet worden. Je hebt de mogelijkheid een nieuwe macro te registreren met de Record... knop of de gekozen macro te wijzigen met de Edit knop. Sluit het Assign Macro dialoogvenster met OK.
5. Sleep over de tekst Button 1 in de knop en vervang deze door Wijzig Duur (figuur 13). Verplaats eventueel de knop (sleep een rand) of wijzig zijn afmetingen (sleep een handle) indien de tekst niet volledig in de knop past.
6. Klik buiten de knop.
7. Sluit de Forms toolbar.

Je kan nu de duur van de lening wijzigen door de gecreëerde knop Wijzig Duur te klikken. De ermee verbonden macro wordt automatisch uitgevoerd.

Opmerkingen:

• Om een bestaande knop te verplaatsen, vergroten, verkleinen of zijn naam te wijzigen moet je hem eerst selecteren door hem te ctrl+klikken. Wanneer je de knop gewoon klikt wordt de macro uitgevoerd.
• Je kan een macro met een geselecteerd object (zoals een knop) verbinden door het object te rechtsklikken en in het snelmenu het commando

Assign Macro...

te kiezen. Hierdoor wordt het Assign Macro dialoogvenster getoond.

• Je kan een macro met een toetsencombinatie verbinden met het commando

Tools, Macro...

In het Macro dialoogvenster klik je de Options… knop, waardoor het Macro Options dialoogvenster verschijnt, en vult de Shortcut key en eventueel de Description in.

9.6 De Visual Basic toolbar in Excel

In Excel kan je met de Visual Basic toolbar o.a. macro's beheren. Ze bevat de volgende knoppen:

Visual Basic toolbar
Run Macro	Start de uitvoering van een macro procedure.
Record Macro	Registreer commando's in een macro procedure.
Resume Macro	Schakel over van stapsgewijze (Step) naar gewone (Run) uitvoering van een macro procedure.
Visual Basic Editor	Wijzig een macro met Microsoft Visual Basic.
Control Toolbox	Toon de Control Toolbox.
Design Mode	Ga naar Design Mode (wanneer een macro is onderbroken).

9.7 De Standard menu bar en Standard toolbar van VBA

Hierna volgt een overzicht van de commando's en knoppenbalken in verband met macro's.

9.7.1 De Visual Basic menubalk

Wanneer het VBA venster is geactiveerd wordt de Visual Basic menu bar getoond. De Visual Basic menu bar bevat de volgende menu's en commando's:

Visual Basic menu bar
Control menu	Restore, Move, Size, Minimize, Maximize, Close
File menu	Save workbook name, Import File…, Export File…, Remove module name…, Print…, Close and Return to Microsoft Excel
Edit menu	Undo/Redo action, Repeat action, Cut, Copy, Paste, Clear, Select All, Find…, Find Next, Replace…, Indent, Outdent, List Properties/Methods, List Constants, Quick Info, Parameter Info, Complete Word, Bookmarks (Toggle Bookmark, Next Bookmark, Previous Bookmark, Clear All Bookmarks
View menu	Code, Object, Definition, Last Position, Object Browser, Immediate Window, Locals Window, Watch Window, Call Stack…, Project Explorer, Properties Window, Toolbox, Tab Order, Toolbars (Debug, Edit, Standard, UserForm), Microsoft Excel
Insert menu	Procedure…, UserForm, Module, Class Module, File…
Format menu	Align (Lefts, Centers, Rights, Tops, Middles, Bottoms, to Grid), Make Same Size (Width, Height, Both), Size to Fit, Size to Grid, Horizontal Spacing (Make Equal, Increase, Decrease, Remove), Vertical Spacing (Make Equal, Increase, Decrease, Remove), Center in Form (Horizontally, Vertically), Arrange Buttons (Bottom, Right), Group, Ungroup, Order (Bring to Front, Send to Back, Bring Forward, Send Backward)
Debug menu	Compile VBAProject, Step Into, Step Over, Step Out, Run To Cursor, Add Watch…, Edit Watch…, Quick Watch…, Toggle Breakpoint, Clear All Breakpoints, Set Next Statement, Show Next Statement
Run menu	Run Sub/UserForm, Break, Reset, Design Mode
Tools menu	References…, Additional Controls…, Macros…, Options…, VBAProject Properties…
Window menu	Split, Tile Horizontally, Tile Vertically, Cascade, Arrange Icons, Modulen
Help menu	Microsoft Visual Basic Help, Contents and Index, Microsoft on the Web (Free Stuff, Product News, Freaquently Asked Questions, Online Support, For Developers Only Home Page, Send Feedback…, Best of the Web, Search the Web…, Web Tutorial, Microsoft Home Page), About Microsoft Visual Basic

Daarnaast zijn er een aantal snelmenu's, die verschijnen wanneer je een object rechtsklikt.

9.7.2 Een aantal commando's uit de Standard toolbar van VBA

Commando's om procedures te beheren bevinden zich vooral in de View, Insert, Debug, Run en Tools menu's in de Standard menu bar van Microsoft Visual Basic. Deze zijn:

View menu
Code	Toon het Code venster met o.a. de procedures).
Object	Schakel over naar het object dat in het Project venster is geselecteerd.
Definition	Toon de definitie van een identifier.
Last Position	Ga naar de vorige positie van het invoegpunt.
Object Browser	Toon het Object Browser venster.
Immediate Window	Toon het Immediate venster.
Locals Window	Toon het Locals venster.
Watch Window	Toon het Watches venster.
Call Stack…	Roep de stack op.
Project Explorer	Toon het Project Explorer venster.
Properties Window	Toon het Properties venster van het object dat in het Project venster is geselecteerd.
Toolbox	Toon de Toolbox.
Tab Order	Toon het Tab Order dialoogvenster.
Toolbars	Toon een werkbalk die uit het submenu (Debug, Edit, Standard, UserForm).
Microsoft Excel	Schakel over naar het Excel venster.

Insert menu
Procedure…	Voeg een procedure toe in een Code of Module venster. In het Add Procedure dialoogvenster kan je o.a. het type (Sub, Function, Property) kiezen.
UserForm	Voeg een invulformulier toe in een UserForm venster.
Module	Voeg een module toe in een Module venster.
Class Module	Voeg een class module toe in een Class venster.
File…	Voeg tekst uit een bestand toe in een venster.

Debug menu
Compile VBA Project	Compileer het project (dat meerdere modules en procedures kan bevatten). Hierdoor wordt de uitvoering versneld.
Step Into	Ga naar de volgende instructie.
Step Over	Sla deze instructie over (bv. wanneer ze een fout bevat).
Step Out	Voer de resterende instructies van de huidige procedure uit.
Run To Cursor	Voer alle instructies uit tot aan het insertion point.
Add Watch…	Toon het Watch venster en voeg een uitdrukking of variabele toe aan de lijst. Tijdens de uitvoering van de macro procedure kan je dan de evolutie van de waarden van deze grootheden volgen.
Edit Watch…	Wijzig een uitdrukking of variabele in het Watch venster.
Quick Watch…	Toon de waarde van de variabele die door het insertion point wordt aangeduid.
Toggle Breakpoint	Plaats of verwijder een onderbrekingspunt in een macro procedure. Op deze plaats zal de uitvoering van de procedure steeds worden onderbroken. Je kan ze verderzetten met Debug, Step Into/Over/Out en Run, Continue.
Clear All Breakpoints	Verwijder alle onderbrekingspunten uit een macro procedure.
Set Next Statement	Duid een instructie aan als eerstvolgende uit te voeren instructie.
Show Next Statement	Toon de als eerstvolgend uit te voeren instructie.

Run menu
Run Sub/UserForm	Voer een procedure uit of open een invulformulier.
Break	Onderbreek de uitvoering van een procedure.
Reset	Onderbreek de uitvoering van een procedure en maak indien mogelijk alle acties ongedaan.
Design Mode	Onderbreek de uitvoering van een procedure en ga naar Design Mode, waarin je de procedure kan wijzigen.

Tools menu
References…	Geef aan uit welke bibliotheken (DLL bestanden) referenties kunnen worden gehaald voor een VBA project.
Additional Controls…	Maak niet standaard controles.
Macros…	Toon het Macros dialoogvenster, waarmee je macro's kan uitvoeren en wijzigen.
Options…	Toon het Options dialoogvenster van Microsoft Visual Basic. Dit bevat vier tabbladen (Editor, Editor Format, General en Docking).
VBAProject Properties…	Toon het VBAProject Properties dialoogvenster. Dit bevat twee tabbladen (General en Protection).

9.7.3 De Standard toolbar van VBA

De knoppen van de Standard toolbar van VBA zijn:

Standard toolbar van Microsoft Visual Basic
View Microsoft Excel	Schakel over naar het Excel venster.
Insert	Open een nieuw document in het Microsoft Visual Basic venster. Kies dit uit de lijst UserForm, Module, Class Module en Procedure….
Save	Bewaar het Excel document met de bijbehorende modules.
Cut	Knip (verplaats) de geselecteerde tekst naar het Clipboard.
Copy	Kopieer de geselecteerde tekst naar het Clipboard.
Paste	Plak (kopieer) de inhoud van het Clipboard op de plaats van het invoegpunt.
Find	Zoek een tekst.
Undo	Maak de laatst uitgevoerde actie ongedaan.
Redo	Voer de laatst ongedane actie opnieuw uit.
Run Sub/UserForm	Voer een procedure uit of open een invulformulier.
Break	Onderbreek de uitvoering van een procedure.
Reset	Onderbreek de uitvoering van een procedure en maak indien mogelijk alle acties ongedaan.
Design Mode	Onderbreek de uitvoering van een procedure en ga naar Design Mode, waarin je de procedure kan wijzigen.
Project Explorer	Open het Project Explorer venster, waarmee je projecten kan beheren.
Properties Window	Open het Properties venster waarin je de eigenschappen van een object kan bekijken.
Object Browser	Open het Object Broser venster, waarin je een object kan kiezen.
Toolbox	Toon de Toolbox toolbar.
Office Assistant	Roep de Office Assistant op.
Ln i, Col i	Aanduiding van de positie van het invoegpunt.

9.8 Programmastructuren in Visual Basic

Hierna volgt een samenvatting van de belangrijkste Visual Basic instructies.

9.8.1 Macro procedures en functie procedures

Visual Basic Modules kunnen macro procedures en functie procedures bevatten.

• Een macro procedure heeft de vorm:

Sub procedurename()

...

End Sub

• Een functie procedure heeft de vorm:

Function functionname(parameterlist)

...

functionname=expression

End Function

Beide soorten procedures kunnen argumenten hebben, die tussen ( ) worden vermeld. De ( ) zijn verplicht, zelfs als er geen argumenten zijn.

• Een macro procedure zal in het algemeen acties uitvoeren. Ze kan als een macro worden geregistreerd, met een knop worden verbonden en als commando in een menu worden aangebracht.
• Een functie procedure zal als een resultaat een waarde hebben die aan de functienaam wordt gehecht en verder kan worden gebruikt. Ze kan niet als een macro worden geregistreerd, maar moet worden ingetypt.

Opmerkingen:

• Binnen een procedure kan je een andere macro procedure op roepen door zijn naam en eventueel argumenten te vermelden. Na het uitvoeren van deze macro procedure wordt dan met de eerstvolgende instructie uit de oproepende procedure verder gegaan.
• Binnen een procedure kan je naast ingebouwde functies ook functies gebruiken die met een functie procedure werden opgesteld. Deze functies kunnen ook in een formule in een cel worden gebruikt.
• De volgorde van macro procedures en functie procedures binnen een module heeft geen belang.

9.8.2 De toewijzing instructies

Zowel in procedures als in functies kunnen aan variables of aan objects waarden worden toegewezen, die het resultaat van een berekening kunnen zijn. Dergelijke instructies hebben de vorm

Bij een functie procedure moet aan de naam van de functie een waarde worden toegewezen, gewoonlijk in de laatste instructie.

9.8.3 De selectie structuren

Standaard worden de instructies uitgevoerd in de volgorde waarin ze in de procedure of functie voorkomen. Met selectie structuren kan je de uitvoering laten afhangen van het voldaan zijn van een voorwaarde. Er zijn twee grote types van selectie: de If ... End If en de Select Case ... End Select structuren. Het gaat hier over een combinatie van instructies die samen moeten voorkomen.

1. De If ... End If structuur laat in essentie een waar / niet waar keuze maken. Zijn algemene syntaxis is:

If condition1 Then

...

[[ElseIf condition2 Then

...]

Else

...]

End If

• In de combinatie If condition Then ... End If worden de instructies tussen Then en End If enkel uitgevoerd indien de voorwaarde (condition) voldaan is. Afhankelijk van de voorwaarde worden de instructies dus wel of niet uitgevoerd.
• In de combinatie If condition Then ... Else ... End If worden de instructies tussen Then en Else enkel uitgevoerd indien de voorwaarde (condition) voldaan is, en worden de instructies tussen Else en End enkel uitgevoerd indien de voorwaarde (condition) niet voldaan is. Afhankelijk van de voorwaarde wordt dus de éne of de andere groep instructies uitgevoerd.
• De combinatie If condition1 Then ... ElseIf condition2 Then ... Else ... End If vormt een uitbreiding op de vorige combinatie. Indien condition1 voldaan is worden enkel de instructies tussen (de eerste) Then en ElseIf uitgevoerd. Indien condition1 niet voldaan is wordt condition2 onderzocht. Indien condition2 voldaan is worden enkel de instructies tussen de tweede Then en Else uitgevoerd; indien condition2 niet voldaan is worden enkel de instructies tussen Else en End If uitgevoerd. Wij hebben hier dus te doen met wat men in elkaar geneste voorwaarden noemt. Een dergelijke constructie is vaak niet erg duidelijk.

1. De Select Case ... End Select structuur laat in essentie een meervoudige keuze maken. Zijn algemene syntaxis is:

Select Case testexpression

Case expressionlist1

...

Case expressionlist2

...

[Case Else

...]

End Select

• De uitdrukking testexpression wordt onderzocht. De instructies na de eerste Case expressionlist die door de textexpresion voldaan is, worden uitgevoerd, de andere niet.

• Indien geen enkele expressionlist voldaan is worden de instructies na Case Else uitgevoerd. Indien dit gedeelte ontbreekt wordt geen enkele instructie uitgevoerd

• Er zijn nog enkele varianten van Case gedaantes, die hier niet verder worden behandeld.

Binnen een selectiestructuur kunnen andere gelijkaardige of verschillende structuren kunnen voorkomen.

9.8.4 De herhaling structuren

Naast uitvoering van instructies afhankelijk van een voorwaarde kunnen instructies ook herhaaldelijk worden uitgevoerd. Er zijn twee grote types herhalingen: de Do ... Loop en de For ... Next structuren. Het gaat ook hier over een combinatie van instructies die samen moeten voorkomen.

1. De Do ... Loop structuur zal het herhaald uitvoeren laten afhangen van het wel of niet voldaan zijn van een voorwaarde. Er zijn vier vormen van syntaxis:

1. De herhaling zolang een voorwaarde voldaan is – de voorwaarde wordt vooraf getest.

Do While condition

...

[Exit Do]

...

Loop

2. De herhaling zolang een voorwaarde voldaan is – de voorwaarde wordt achteraf getest.

Do

...

[Exit Do]

...

Loop While condition

3. De herhaling totdat een voorwaarde voldaan is (zolang de voorwaarde niet voldaan is) – de voorwaarde wordt vooraf getest.

Do Until condition

...

[Exit Do]

...

Loop

4. De herhaling totdat een voorwaarde voldaan is (zolang de voorwaarde niet voldaan is) – de voorwaarde wordt achteraf getest.

Do

...

[Exit Do]

...

Loop Until condition

• De voorwaarde zal in het algemeen een uitdrukking zijn die op een zeker ogenblik tijdens de uitvoering van waarde verandert, zodat ze (meestal) een aantal keren wel en een aantal keren niet voldaan zal zijn.
• Indien de voorwaarde vooraf wordt getest is het mogelijk dat de instructies helemaal niet worden uitgevoerd.
• Indien de voorwaarde achteraf wordt getest zullen de instructies minstens eenmaal worden uitgevoerd.
• De termen While en Until zijn elkaars tegengestelde. Je kan Until vervangen door While Not en While door Not Until.
• Het is mogelijk dat de voorwaarde steeds voldaan is (of steeds niet voldaan), zodat de instructies voortdurend uitgevoerd worden. Men noemt dit verschijnsel een oneindige lus. Een dergelijke toestand moet je uiteraard trachten te voorkomen.
• Je kan vaak een oneindige lus tegengaan door ergens tussen Do en Loop een Exit Do instructie te plaatsen. Deze zorgt ervoor dat de herhaling wordt beëindigd. In het algemeen wordt zulk een Exit Do instructie binnen een If of Select Case structuur geplaatst, zodat het beëindigen van de herhaling opnieuw van een voorwaarde afhangt.
• Na het beëindigen van de herhaling wordt verder gegaan met de eerste instructie na Loop.

1. De For ... Next structuur zal de instructies een vooropgegeven aantal keren doen uitvoeren. Er zijn twee vormen van syntaxis:

1. De herhaling op basis van een teller (counter), die varieert tussen een beginwaarde (start) en eindwaarde (end). De teller neemt bij elke herhaling toe met een stapwaarde (increment), die standaard gelijk is aan 1. De instructies tussen For en Next worden herhaald totdat de eindwaarde van de teller bereikt is.

For counter = start To end [Step increment]

...

[Exit For]

...

Next counter

2. De herhaling op basis van een lijst. De instructies tussen For Each en Next worden herhaald voor elke waarde van de variabele element in de lijst gevormd door group.

For Each element In group

...

[Exit For]

...

Next element

• Hier is er geen gevaar voor een oneindige lus (tenzij wij een stapwaarde 0 opgeven) omdat er een eindig aantal waarden tussen de begin- en eindwaarde of in de lijst zijn. Nochtans kan het ook hier wenselijk zijn de herhaling af te breken voor de eindwaarde of het einde van de lijst is bereikt. Hiervoor wordt de Exit For instructie gebruikt die de herhaling beëindigt (juist zoals Exit Do).
• Na het beëindigen van de herhaling wordt verder gegaan met de eerste instructie na Next.

Binnen een herhalingsstructuur kunnen andere gelijkaardige of verschillende structuren voorkomen.