Inleiding
Deze pagina is speciaal bedoeld voor programmeurs die programmeren in een andere taal.
Op deze pagina gaan we in het kort in op de belangrijkste zaken die met Excel VBA hebben te maken.
Echter is het ook meteen een handreiking voor vba in de andere office pakketten.
VBA staat voor Visual Basic for Applications en is de programmeer-taal voor MS-Ofiice.
Alle voorbeeld-code in dit document zijn uitgewerkt in VBA voor Excel.
Om in Excel met eigen gemaakte code te kunnen werken moeten de volgende zakel in acht worden genomen:
Beveiliging
Kies het menu Extra –> Macro –> Beveiliging…
Kies in het tab-blad “Beveiligingsniveau” voor de optie ‘Gemiddeld’ of ‘Laag’. Standaard staat de optie ‘Hoog’ geselecteerd, wat tot gevolg heeft dat er in het geheel niet met macro’s en met eigen gemaakte code kan worden gewerkt.
Handig
Verder is het handig om de volgende menu-opties op een van de werkbalken te plaatsen:
Extra –> Macro –>
Macro’s… Alt+F8
Nieuwe macro opnemen…
Visual Basic Editor Alt+F11
Dit kun je doen door te kiezen voor de menuoptie Extra –> Aanpassen…
Activeer het tab-blad “Opdrachten”.
Kies onder “Categorie” de optie ‘Extra’.
Zoek onder “Opdrachten” naar de juiste commando’s en sleur&pleur ze naar de juiste plaats op een werkbalk.
Afsluiten door op de Sluiten-knop te klikken.
Modules
De (algemene) codes die je zelf maakt komen in een zogenaamde module te staan.
Je kunt per categorie commando’s een aparte module maken, maar je mag ook alles in 1 module proppen.
Alle code in een module is overal te gebruiken. De scope is dus globaal.
Om een module te maken ga je als volgt te werk:
Open de VBA-editor
Kies het menuitem: Invoegen –> Module
Kijk meteen of de eerste regel ‘Option Explicit’ is. Zo niet tik dit dan in.
Deze opdracht zorgt ervoor dat alle te gebruiken variabelen eerst moeten worden gedeclareerd.
VBA-editor
De VBA-editor kan grofweg in drie delen worden opgesplitst:
De menu- en werkbalken
Het project- en Eigenschappen-venster (aan de linker-kant)
Het code-venster
Door in het project-venster te dubbelklikken op een item wordt het bijbehorende code-venster geactiveerd.
Het code-venster heeft als eigenschap dat er na iedere regel die wordt verlaten er meteen een syntax-check plaats vindt. Wanneer er iets niet goed is komt er meteen een melding van.
Alles kan in kleine letters worden getikt. Wanneer VBA het herkent als een VBA-commando, dan zet hij hierzelf hoofdletters in.
Wanneer je variabelen declareerd met een of meer hoofdletters en bij het gebruik ze verder intikt in kleine letters zal VBA hier ook de hoofdletters inzetten. Erg handig!
Subs en Functions
Een procedure in VBA heet een Sub.
Een functie heet in VBA een Function.
Declaratie Sub
Om een Sub te declareren tik je in:
sub <naam van de sub> met, indien van toepassing, direct daarachter tussen haakjes de (formele) parameters. Hierna druk je op de Enter-toets.
VBA toont hierna de volgende regels:
Sub <naam>(<params>)
End Sub
De cursor staat keurig tussen de twee regels.
Declaratie Function
Om een Function te declareren tik je in:
function <naam> met, indien van toepassing, direct daarachter tussen haakjes de (formele) parameters, met, indien van toepassing, daarachter het type van de functie.
VBA toont hierna de volgende regels:
Function <naam>(<params>)
End Function
De cursor staat keurig tussen de twee regels.
Om een waarde aan een functie te geven moet er een toekenningsopdracht binnen de definitie staan als volgt:
<naam> = <waarde>, waarbij <naam> de naam van de Function is.
Gebruik van Sub
Wanneer je een Sub ergens anders wilt gerbuiken dan moet de volgende aanroep worden gebruikt:
<naam> <param1> <param2> …, waarbij <naam> de naam van de Sub is en <paramx> de xe parameter. De parameters staan dus niet tussen haakjes.
Gebruik van Function
Wanneer je een Function ergens anders wilt gebruiken dan moet de volgende aanroep worden gebruikt:
<var> = <naam>(<param1>,<param2>,…), waarbij <var> een variabele is, <naam> de naam van de Function en <paramx> de xe parameter. De parameters staan hier wel tussen haakjes.
Het is niet toegestaan een Function als een Sub te gebruiken.
Variabelen
Declareren
Wanneer ‘Option Explicit’ in de module is gedeclareerd, dan moeten alle te gebruiken variabelen worden gedeclareerd.
Om een variabele te delcareren moet gebruik worden gemaakt van het gereserveerde woord Dim als volgt:
Dim <naam-variabele>
Het is toegestaan om achter het woord Dim meerdere variabelen te declareren, deze moeten dan worden gescheiden door een komma (,).
Een variabele moet gedeclareerd zijn voordat deze voor het eerst wordt gebruikt.
Scope
Wordt een variabele buiten een Sub of Function gedeclareerd dan heeft deze een globaal karakter binnen de module.
Wordt een variabele binnen een Sub of Function gedeclareerd dan heeft deze een lokaal karakter.
Een lokale variabele heeft een hogere prioriteit dan een globale variabele.
Type
In tegenstelling tot de meeste andere talen hoef je in VBA niet aan te geven van welk type een variabele is.
Als het type ontbreekt dan maakt VBA hier standaard (default) het type Variant van. Het voordeel hiervan is dat een variabele afhankelijk van de situatie van een ander type kan zijn. Het nadeel is dat dit tijdens de uitvoer moet worden bepaald.
Wanneer van te voren duidelijk is van welk type een variabele is, dan kan deze het beste worden gedeclareerd met een type aanduiding.
Dit gaat als volgt:
Dim <variabele-naam> As <type>
In onderstaande tabel staan de meest gebruikte types:
| type | bereik |
| Byte | 0..127 |
| Integer | -32.768..32.767 |
| Long | -2.147.483.648..2.147.483.647 |
| Single | -3,402823E38 tot -1,401298E-45, voor negatieve waarden en 1,401298E-45 tot 3,402823E38 voor positieve waarden |
| Double | -1,79769313486232E308 tot -4,94065645841247E-324 voor negatieve waarden en 4,94065645841247E-324 tot 1,79769313486232E308 voor positieve waarden |
| Date | 1-1-100 t/m 31-12-9999 |
| String | tekenreeks |
| Boolean | TRUE/FALSE |
Voorbeeld:
Dim teller As Integer
Dim Regel As String, Getrouwd as Boolean
Valkuil
Een veel voorkomende fout is de volgende:
Dim a, b, c As Integer
in de veronderstelling dat er nu 3 variabelen (a, b en c) als Integer zijn gedeclareerd. Dat is NIET het geval! Alleen variabele c is een Integer, de variabelen a en b zijn van het type Variant. Iedere variabele moet dus worden voorzien van een type-aanduiding.
Bovenstaande voorbeeld moet dus zijn:
Dim a As Integer, b As Integer, c As Integer
Parameters
Bij het declareren van formele parameters mag ook een type-aanduiding worden toegevoegd aan de parameter. Dit gaat op dezelfde mannier als bij de declaratie van variabelen.
Wordt dit niet expliciet gedaan, dan wordt het type Variant gebruikt voor de parameter.
Voorbeeld:
Sub TelOp(Getal1 As Integer, Getal2 As Integer, Som As Integer)
Om een Function van een type te voorzien moet ook gebruik worden gemaakt van het gereserveerde woord As met daarachter een type-aanduiding.
Voorbeeld:
Function TelOp(Getal1 As Integer, Getal2 As Integer) As Integer
Als dit niet expliciet gebeurt, dan wordt de Function als Variant gedeclareerd.
Value- en Reference-parameters
De doorgewinterde programmeur zal zich bij het voorbeeld van de Sub Telop hierboven zich onmiddellijk hebben afgevraagd of dit wel goed gaat.
Per slot is er geen taal waarin formele parameters default als reference-parameter worden gedeclareerd. Dit is echter een foute conclusie. Er is wel zo’n taal! Te weten: VBA (en ook VB).
Wie dat heeft bedacht, geen idee, maar krankzinnig is dit wel en iets om ter dege rekening mee te houden.
Wanneer een formele parameter niet expliciet als value-parameter wordt gedeclareerd is het dus een reference-parameter.
Het is het handigst als je je aanleert om altijd de soort expliciet aan te geven.
Voor een value-parameter gebruik je het gereserveerde woord ByVal, en voor een reference-parameter het gereserveerde woord ByRef.
De laatste twee voorbeelden worden dan:
Sub TelOp(ByVal Getal1 As Integer, ByVal Getal2 As Integer, ByRef Som As Integer)
Function TelOp(ByVal Getal1 As Integer, ByVal Getal2 As Integer) As Integer
Array’s
Het is in VBA mogelijk om Array’s te gebruiken. Deze mogen ook meer-dimensionaal zijn.
De declaratie is als volgt:
Dim <var>(<start-index> To <eind-index>) As <type>
of
Dim <var>(<start-index> To <eind-index>, <start-index> To <eind-index>, …) As <type>
Voorbeeld:
Dim Rij(1 To 10) As Integer
Dim Matrix(1 To 5, 1 To 10) As Single
Dim EinsteinRuimte(0 To 2, 1 To 10, 5 To 6, 3 To 8) As Variant
LBound en UBound
Met de functie LBound(<var>, <dimensie>) kun je de start-index van een Array krijgen, waarbij de optionele <dimensie> aangeeft van welke dimensie je de start-index wilt weten.
Met de functie UBound(<var>, <dimensie>) kun je de eind-index van een Array krijgen, waarbij de optionele <dimensie> aangeeft van welke dimensie je de eind-index wilt weten.
Als <dimensie> wordt weggelaten dan krijg je het resultaat van de 1e dimensie.
Dynamische array’s
In VBA is het ook mogelijk om met dynamische array’s te werken.
Wanneer je dit wilt dan moet je de variabele als volgt declareren:
Dim <var>() As <type>
De haakjes doen hier het werk. Door de twee haakjes met niets daartussen weet VBA dat het hier om een dynamische array gaat.
Voorbeeld:
Dim Naam() As String
Voordat je een dynamische variabele gaat gebruiken moet deze eerst, minimaal 1 keer, ge-re-dimensioneerd zijn, zodat VBA weet hoeveel elementen er gebruikt kunnen worden. Dat re-dimensioneren gaat met het gereserveerde woord ReDim <var>(<start-index> To <eind-index>). Let op: Na een ReDim mag nooit een type-declaratie komen!
Voorbeeld:
ReDim Naam(1 To 1)
Naam(1) = “Henk”
Wanneer nu een tweede naam moet worden toegevoegd zou je de volgende code kunnen gebruiken:
ReDim Naam(1 To 2)
Naam(2) = “Jan”
Het resultaat zal echter niet gewenst zijn, want na deze regels is Naam(1) leeg.
Wanneer je de reeds bestaande waardes in een dynamische array wilt behouden moet je gebruik maken van het gereserveerde woord Preserve. Preserve komt na ReDim.
Voorbeeld:
ReDim Preserve Naam(1 to 2)
Naam(2) = “Jan”
Na deze regels zal Naam(1) nog steeds de waarde “Henk” hebben.
Wil je echt dynamisch te werk gaan dan maak je natuurlijk veelvuldig gebruik van de functie UBound.
Het laatste voorbeeld wordt dan:
ReDim Preserve Naam(1 To UBound(Naam) + 1)
Naam(UBound(Naam)) = “Jan”
Opmerking: Als je een dynamische array als parameter wilt gebruiken dan moet deze als reference parameter worden gedeclareerd!!!
Talige zaken
Hieronder enkele talige zaken.
For-lus
De For-lus gaat als volgt:
For <var> = <start> To <eind> Step <stap>
<opdracht>
Next <var>
waarbij <var> een Integer-variabele is, <start> de start-waarde van de lus is, <eind> de eind-waarde van de lus is, en <stap> de stap-grootte van de lus is en <opdracht> een (samengestelde) opdracht is die binnen de lus moet worden uitgevoerd. Het Step-gedeelte is optioneel. Als <start> groter is dan <eind>, dan moet <stap> een negatieve waarde hebben.
De For-lus mag worden genest.
Voorbeeld:
Dim t As Integer, s As Integer
s = 0
For t = 1 to 10
s = s + t
Next t
Do-Loop-lus
De Do-Loop-lus is er in diverse smaken, waarvan we er hier twee zullen bekijken.
Do-While-lus
De Do-While-lus gaat als volgt:
Do While <voorwaarde>
<opdracht>
Loop
waarbij <voorwaarde> van alles kan zijn, dus ook meervoudig, en <opdracht> een (samengestelde) opdracht is die binnen de lus moet worden uitgevoerd.
De Do-While-lus mag worden genest.
Voorbeeld:
Dim t As Integer, s As Integer
t = 1
s = 0
Do While t <= 10
s = s + t
t = t + 1
Loop
Do-Loop-Until-lus
De Do-Loop-Until-lus gaat als volgt:
Do
<opdracht>
Loop Until <voorwaarde>
waarbij <voorwaarde> van alles kan zijn, dus ook meervoudig, en <opdracht> een (samengestelde) opdracht is die binnen de lus moet worden uitgevoerd.
De Do-Loop-Until-lus mag worden genest.
Voorbeeld:
Dim t As Integer, s As Integer
t = 1
s = 0
Do
s = s + t
t = t + 1
Loop Until t > 10
If-opdracht
De If-opdracht gaat als volgt:
If <voorwaarde> Then
<opdracht>
Else
<opdracht>
End If
waarbij <voorwaarde> van alles kan zijn, dus ook meervoudig en <opdracht> een (samengestelde) opdracht die wordt uitgeoverd als aan de voorwaarde wordt voldaan, of wordt uitgevoerd in het optionele Else-gedeelte als er niet aan de voorwaarde wordt voldaan.
De If-opdracht mag genest worden, maar ook is de volgende constructie toegestaan:
If <voorwaarde1> Then
<opdracht>
ElseIf <voorwaarde2> Then
<opdracht>
Else
<opdracht>
End If
waarbij ElseIf meerdere malen gebruikt mag worden.
Voorbeeld:
If maand = 2 Then
If Schrikkel Then
ad = 29
Else
ad = 28
End If
ElseIf (maand = 4) Or (maand = 6) Or (maand = 9) Or (maand = 11) Then
ad = 30
Else
ad = 31
End If
Select-opdracht
De Select-opdracht gaat als volgt:
Select Case <var>
Case <waarde1>
<opdracht>
Case <waarde2>, <waarde3>, <waarde4>
<opdracht>
Case <waarde5> To <waarde6>
<opdracht>
Case Else
<opdracht>
End Select
waarbij <var> een variabele is, <waarden> een mogelijke waarde van de <var> is en <opdracht> een (samengestelde) opdracht is die wordt uitgevoerd als <var> de waarde <waarden> heeft. Het Else gedeelte is optioneel en wordt uitgevoerd als <var> geen enekele waarde heeft die voorkomt binnen de Select-opdracht.
De Select-opdracht mag worden genest.
Voorbeeld:
Select Case maand
Case 2
If Schrikkel Then
ad = 29
Else
ad = 28
Case 4, 6, 9, 11
ad = 30
Case Else
ad = 31
End Select
Tot slot moet hier nog verteld worden dat vba zogenaamd object-based is. Dat merk je wanneer je gebruik gaat maken van de applicatie-objecten. Tevens kun je zelf klasses definiëren en gebruiken. Maar denk eraan dat vba niet object-oriënted is!
Ondanks dat deze pagina is geschreven voor programmeurs hoop ik dat ook anderen hier iets mee zijn opgeschoten.