English

Cursus MS Excel VBA (Visual Basic for Applications)

Deze cursus leert je programmeren in Excel/VBA. Dit gaat veel verder dan een macro iets kunnen aanpassen. De cursus is eenvoudiger voor mensen die al enige programmeerervaring hebben.

Deze webpagina is printervriendelijk.

Deel I is meer generiek en theoretisch


Deel II gaat specifiek over VBA in combinatie met Excel


Inhoudsopgave

Deel I - VBA "theorie"

  • Cursusdoel
  • Opdrachtknop en gebruikersmelding
  • Procedure syntax
  • Variabelen
  • Beslissingen
  • Loops - onvoorwaardelijke
  • Loops - voorwaardelijke
  • Loops - direct beëindiging
  • Integrale oefening
  • Debuggen

Deel II - VBA en Excel

  • Lezen van en schrijven naar Range van werkblad
  • Range eigenschappen / methoden
  • Range en loops
  • Projectverkenner
  • Macro opname
  • Gebruikersgedefinieerde functie
  • Keuzerondjes en selectievakjes
  • Code versnellen

Deel III - Appendices

  • String functies
  • Foutafhandeling
  • Variabelen: constanten & gebruikersgedefinieerde
  • Gevarieerd
  • Uitwerking van de integrale opgave van Deel I

Cursusdoel

Na deze cursus ben je in staat VBA procedures te maken in MS Excel.

VBA kan worden gebruikt om Benut altijd standaardfunctionaliteit van MS Excel ten volle en probeer de hoeveelheid VBA code te minimaliseren (binnen redelijk grenzen).

Oefening: maak een opdrachtknop aan

We gaan leren VBA-code te schrijven. Die code moet worden uitgevoerd. Dat gaat doorgaans via een druk op een opdrachtknop. Daarom gaan we eerst een opdrachtknop aanmaken. Hieronder zie je het eindresultaat van deze oefening.


Maak de Ontwikkelaars tab zichtbaar, als dit nog niet zo is. Ga naar menu Bestand → Opties → Lint aanpassen. Vink Ontwikkelaars aan in het rechterscherm.


Ga naar de Ontwikkelaar tab → Invoegen → Opdrachtknop, linksboven onder sectie ActiveX-besturingselementen.


Trek een rechthoek op je werkblad terwijl je de linkermuisknop ingedrukt houdt. Laat dan de muisknop los.


Geef de opdrachtknop een logische naam (Name, de koppeling naar VBA-code) en tekst (Caption, tekst op de knop). Dit doe je in het scherm eigenschappen, via rechtermuisklik op de opdrachtknop. Gebruik enige hoofdletters in de naam en begin deze met
cmd
. Enige naamconventies: opdrachtknoppen beginnen met
cmd
(command button), keuzerondjes met
opt
(option button), keuzevakjes beginnen met
chk
(check box), etc.


Dubbelklik (links) op de opdrachtknop. Je komt dan in de VBA omgeving terecht (gaat ook via ALT-F11). Merk op dat 'header en footer van de opdrachtknop' automatisch zijn aangemaakt. Je herkent daarin de knopnaam en dat het gaat om een procedure (Sub) die uitgevoerd wordt na een muisklik (_Click).


Geef nu de volgende code in bij de knop: MsgBox "Hello world. Wil je doorgaan?", vbYesNo, "Doorgaan?". Merk op dat zodra je de komma intypt, een lijstje verschijnt van mogelijke opties van het schermpje (die je kunt combineren met +). vbYesNo is veelgebruikt. Het geeft een Yes en No knop bij de melding.


Sluit nu de VBA mode af (druk x rechtsboven), en sluit ook de Ontwerpmodus.


Druk nu op je opdrachtknop. Als je de 'Hello World' melding ziet verschijnen dan heb je je eerste studiepunt behaald.
Bewaard je bestand als *.xlsm of als *.xlsb bestand!

Een melding gebruik je vaak om de gebruiker te laten weten dat een procedure is voltooid. Hieronder een ander veel voorkomend voorbeeld: als de gebruiker nee kiest, wordt de procedure beëindigd. Dit komt later nog terug in de oefening.



Sub procedures en functies - syntax

Een Sub procedure voert een taak uit, een Function procedure levert daarnaast ook een waarde op (de functie uitkomst). De syntax van beide is zeer vergelijkbaar. Beide maken gebruik van invoervariabelen, en lokale variabelen (tijdelijk geheugen binnen de procedure).
Syntax Subprocedure
Sub NaamProcedure(InvoerVariabele1 As Type,...,InvoerVariabeleN As Type)
   Dim LokaleVariabele1 As Type
   Dim LokaleVariabeleN As Type
   instructie(s)
End Sub
Syntax functieprocedure
Function NaamProcedure(InvoerVariable1 As Type,...,InvoerVariableN As Type) As Type
   Dim LokaleVariabele1 As Type
   Dim LokaleVariabeleN As Type
   instructie(s)
   NaamProcedure = LokaleVariabeleM
End Function

Tip: Het gebruik van enige hoofdletters in de naam van een procedure en variabele helpt typefouten voorkomen. Want als je die ergens anders in je code intypt - zonder hoofdletters - dan zal die automatisch veranderen in de naam inclusief hoofdletters. Zo niet, dan weet je dat je een typefout hebt gemaakt.

Variabelen

Variabelen vormen invoer en lokaal geheugen van procedures. Het woordje
Dim
betekent 'maak aan'. Hieronder een functie die twee gehele getallen bij elkaar optelt.

Function Plus(ByVal NumberOne As Integer, ByVal NumberTwo As Integer) As Integer
   Dim SumBoth As Integer
   SumBoth = NumberOne + NumberTwo
   Plus = SumBoth
End Function

Geef variabelen logische namen en declareer hun type zoveel mogelijk expliciet.

Type variabeleUitlegConversiefunctie
IntegerHele getallen tussen -32.768 en 32.767CInt   
CInt("10")→10
LongHele getallen tussen -2.147.483.648 en 2.147.483.647CLong
SingleFractionele getallen tussen -3,402823 E38 en 3,40283 E38CSng
DoubleFractionele getallen tussen -1,7976931486232 E308 en 1,7976931486232 E308CDbl
CurrencyFractionele getallen tussen -922.337.203.685.477,5807 en 922.337.203.685.477,5807 (altijd 4 decimalen, geen afrondfouten)CCur
StringTot 65.000 karaktersCStr   
CStr(10)→"10"
BooleanTrue (waar) of False (onwaar)CBool
Date1 Januari 100 tot 31 December 9999CDate
Object/RangeElk object / werkblad cel of range
VariantVariabele die automatisch detecteert welk type het moet worden gebaseerd op de invoer. Kan een matrix zijn, of Null (leeg). Default type, als het type niet expliciet is gedefinieerd.CVar

Intermezzo: ga naar de VBA omgeving (via ALT-F11), menu Extra → Opties, en check Variabelen declareren. De regel
Option Explicit
zal dan voortaan automatisch verschijnen telkens als je een nieuw bestand aanmaakt. Je bent dan verplicht elke variabele expliciet te declareren. Dit vermindert de kans op codefouten.



Matrixvariabelen

Een matrixvariabele kun je beschouwen als een lijst of multidimensionele tabel. Hieronder een voorbeeld hoe je een tabel met 100 willekeurige getallen maakt. Elk element ervan benader je via de index, hier genaamd
iNumber
. Standaard VBA functie
Rnd()
trekt een willekeurig fractioneel getal tussen 0 en 1.

Dim RandomNumber(100) as Single
Dim iNumber as Integer
For iNumber = 1 to 100
   RandomNumber(iNumber)= Rnd()
Next iNumber
Een voorbeeld van een 2-dimensionele tabel met 3 sets van 100 willekeurige getallen.

Dim RandomNumber(1 to 3, 1 to 100) as Single
Dim iSet as Integer, iNumber as Integer
For iSet = 1 to 3
   For iNumber = 1 to 100
      RandomNumber(iSet, iNumber)= Rnd()
   Next iNumber
Next iSet
RandomNumber(100) bevat in feite 101 elementen, omdat 0 de eerste index is (zoals in de meeste programmeertalen). Als je de index altijd met 1 wilt te laten starten, zet dan helemaal bovenaan je code
Option Explicit

Option Base 1


Alternatieve methode is om bij het declareren de ondergrens van de matrix expliciet mee te geven
as Dim RandomNumber(1 to 100) as Single
Je kunt matrixvariabelen dynamisch laten groeien, als van tevoren niet bekend is hoe groot ze worden. Specificeer dan geen dimensies bij de aanmaak van de matrixvariabele:

Dim RandomNumber() as Integer


Stel je hebt het eerste getal om op te slaan:

Redim RandomNumber(1)
RandomNumber(1) = 7


Gebruik
Preserve
als je waarden wilt bewaren bij het redimensioneren:

Redim Preserve RandomNumber(2)
RandomNumber(2) = 28


Functie
UBound
geeft de lengte van de matrixvariabele, bijvoorbeeld
NumberOfRandomNumbers = Ubound(RandomNumber)

En bij multidimensionele matrixvariabelen
NumberOfRandomNumbersInSet2 = Ubound(RandomNumber,2)

Beslissingen

Beslissingen (conditioneel programmaverloop) maak je met If Then Else of met Select Case

If voorwaarde then
   instructies
End If
If voorwaarde Then
   instructies1
Else
   instructies2
End If
If invoerGetal<10 Then
   msgBox "Het ingegeven getal is kleiner dan 10"
Else
   msgBox "Het ingegeven getal is groter of gelijk aan 10"
End If

If voorwaarde1 Then
   instructies1
ElseIf voorwaarde2 Then
   instructies2
ElseIf voorwaarde3 Then
   instructies3
End If

In bovenstaande ElseIf-constructie kun je in de voorwaarden variabelen van verschillende type gebruiken. In onderstaande Select Case-alternatief kun je dat niet.

Select Case VariabeleWaarde
   Case 1: instructies1
   Case 2,3: instructies2
   Case 4 to 10: instructies3
   Case Else: instructies4
End Select
Select Case strDay
   Case "MON": dayNr = 1
   Case "TUE": dayNr = 2
   Case "WED": dayNr = 3
   Case "THU": dayNr = 4
   Case "FRI": dayNr = 5
   Case "SAT": dayNr = 6
   Case "SUN": dayNr = 7
End Select

Gebruik
And
en
Or
en
( )
om samengestelde voorwaarden te maken.
If ((i=1) And (t="abc")) Then ...
If ((i=1) Or (t="abc")) Then ...

Gebruik
Not
om de tegenovergestelde voorwaarde te maken.
If Not((i=1) and ((b=false) Or (t="abc"))) Then ...

Onvoorwaardelijke loops - For Next

Een loop (Engels) is een programmeerlus of herhaling (Nederlands). In deze cursus hanteren we de Engelse term.

Een loop zorgt ervoor dat dezelfde instructies meermaals worden uitgevoerd. Gebruik For Next als vooraf bekend is hoeveel keer, bijvoorbeeld 10 keer.

For i=1 to 10
   instructies
Next i
Als de teller niet standaard moet ophogen met 1, specificeer dan de stapgrootte met step (merk op dat je ook kunt laten aflopen)

For i=2 to 10 step 2
   instructies
Next i
For i=20 to 2 step -4
   instructies
Next i
Gebruik van For Next in combinatie met matrixvariabelen (Ubound geeft de lengte van een matrix).

For i = 1 to Ubound(matrix)
   instructies
Next i
For Each element In matrix
   instructies
Next element

Voorwaardelijke loops - Do While Loop / Do Until Loop

Een loop zorgt ervoor dat dezelfde instructies meermaals worden uitgevoerd. Gebruik Do While of Do Until als vooraf onbekend is hoeveel keer de instructies moeten worden uitgevoerd.

Do While voorwaarde
   instructies
Loop
Do Until voorwaarde
   instructies
Loop
Of als de instructies minstens één keer moeten worden uitgevoerd:

Do
   instructies
Loop While voorwaarde
Do
   instructies
Loop Until voorwaarde

Loops halverwege beëindigen - Exits

Hoewel codetechnisch misschien minder fraai, kan het toch heel handig zijn
exit
te gebruiken om loops en procedures direct te beëindigen (shortcut).
  • Beëindig loops met
    Exit For
    bij onvoorwaardelijke loop en met
    Exit Do
    bij voorwaardelijke loop
  • Beëindig procedures met
    Exit Sub
    bij sub procedure en met
    Exit Function
    bij functie procedure
  • Beëindig programma met
    End

For i = 1 to 100
   ...
   If ThisValue = ValueToFind then Exit For
   ContinueSearching
   ...
Next i
Public Sub deleteRecords()
   ...
   If MsgBox("Delete all records?", vbYesNo) = vbNo Then Exit Sub
   DeleteAllRecords
   ...
End Sub

Integrale oefening deel I: priemgetal-generator

In deze oefening ga je een priemgetal-generator maken. Priemgetallen zijn die getallen die alleen door 1 en zichzelf deelbaar zijn (1, 2, 3, 5, 7, 11, 13, 17, 19, 23, etc.).

De oefening bevat veel elementen van bovenstaande gedeelte en is bedoeld als een integrale oefening. Handig hierbij is de aanwezigheid van een instructeur.

Volg hierbij strikt de stap-bij-stap aanpak met de aanwijzingen daarbij en bekijk ook eerdere voorbeelden in bovenstaande. Lees eerst alle stappen door om een idee te krijgen van de aanpak, en begin dan pas met stap 1. Ga niet proberen bepaalde stappen over te slaan. De resulterende code niet de meest efficiënte, maar het draait hier om de oefening als zodanig.

Als je programma op een gegeven moment niet correct blijkt te werken - dit gaat waarschijnlijk het geval zijn - dan moet je codefouten gaan opsporen en verwijderen, oftewel debuggen. Lees daarom ook alvast het volgende hoofdstuk.

De programmacode na elke stap vind je helemaal onderaan deze pagina. Van eerst echt zelf proberen leer/ervaar je het meest!
  1. Maak een opdrachtknop die opvolgende getallen 1,2,3,4,etc. laat zien in een melding, totdat de gebruiker daarop aangeeft de loop te willen stoppen
    • Maak een tellertje (een variabele) genaamd iNumber dat oploopt. Pas een Do While loop toe.
    • Pas een Msgbox en exit sub toe om te stoppen
    • Combineer tekst en een variabele in een melding als volgt "Getal = " & str(iNumber)

  2. Maak een 'is oneven' check
    • Gebruik If Then
    • Functie Mod geeft de rest na een deling, bijvoorbeeld: (41 Mod 3) geeft 2.

  3. Maak een matrix genaamd primeNumber aan en zorg ervoor dat daarin alle oneven getallen worden bewaard
    • Gebruik een tellertje genaamd nPrime.
    • Optioneel: maak een dynamisch groeiende matrix.

  4. Vervang de 'is oneven'-check van stap 2 door een 'is priemgetal'-check. Bewaar enkel nog de priemgetallen.
    • Gebruik een boolean genaamd isPrimeNumber en initieer deze telkens met isPrimeNumber = true.
    • Gebruik een For Next loop om te controleren of het huidige getal deelbaar is door een van de voorgaande priemgetallen.
    • Optioneel: gebruik Exit For om deze controle direct te beëindigen zodra blijkt dat het getal deelbaar is.

Debuggen

Debuggen is het opsporen en verwijderen van codefouten (bug = codefout).
  • Debuggen doe je vaak door toevoeging van zogeheten 'break points'/breekpunten, punten waarop de code onderbroken wordt.
  • Klik in de grijze vertikale balk links van de code om een breekpunt in te geven.


  • Zet de cursor 'in' de eerste executeerbare coderegel (dus niet op een variabele declaratieregel startend met Dim) en druk dan F5 of op het groene "play"-driehoekje. Het programma wordt dan tot aan het eerste breekpunt uitgevoerd.


  • Druk F8 om vervolgens één volgende regel uit te laten voeren.
  • Druk F5 om alle regels tot aan het volgende breekpunt uit te laten voeren.
  • Druk op het blauwe vierkantje om te stoppen.

  • Druk [BREAK] om oneindige loops te 'killen'.
  • Debug door de waarden van variabelen te controleren tijdens de uitvoering van het programma.


  • Methode 1. Zet de muis bovenop een variabele om direct zijn waarde te zien in een klein popup schermpje.


  • Methode 2. Schrijf waarden van variabelen weg naar een debug scherm via
    Debug.Print
    naam_variabele
    Open het Venster direct (menu Beeld → Venster direct; of druk CTRL + G).
    Elke keer als het programma langs de Debug.Print komt, wordt Venster direct aangevuld met de dan geldende waarde van naam_variabele.


  • Methode 3. Open een debug scherm (menu Foutopsporing → Controle toevoegen) en voeg variables toe. Je kunt zelfs matrixs (of complete objecten) toevoegen en die in het debug scherm uitklappen.

Lezen van en schrijven naar werkblad

Lezen - En directe celverwijzing versus celverwijzing via bereiknaam

Directe celverwijzing
Twee alternatieven
X = Range("D5").Value
→ X wordt 76

X = Cells(5,4).Value
→ X wordt 76
met 5 = rijnummer, 4 = kolomnummer


Als je de tabel in zijn geheel verplaatst,
levert de code een fout antwoord op
dus vermijd directe celverwijzing!

Celverwijzing via bereiknaam (cel met naam)
Verwijzing naar de cel met naam
X = Range("UpperLeft").Value
→ X wordt 67.

Verwijzing naar een andere cel via de cel met naam
X = Range("Upperleft").Cells(5,4).Value
→ X wordt 20
X = Range("Upperleft").Offset(4,3).Value
→ X wordt 20
X = Range("Table").Cells(5,4).Value
→ X wordt 20

Als je de tabel in zijn geheel verplaatst, blijft de code correct werken,
zolang bereiknaam upperLeft en Table verwijst naar het juiste bereik.

Schrijven

Schrijven naar werkblad werkt precies andersom, dus bijvoorbeeld
Range("Table").Cells(5,4).Value = X


In geval van meerdere werkbladen geef dan eventueel ook de sheetnaam mee
Sheets("Sheet1").Range("UpperLeft").Value = X


Oefening


Maak een programma van één regel dat de som van twee invoercellen genaamd InOne en InTwo in een cel genaamd OUT geeft, na een druk op opdrachtknop Sum.


Recapitulatie eerste dag met behulp van demobestand


Download MS Excel recapitulatie demo bestand

Oefening


Ga terug naar de priemgetal-generator. Schrijf de priemgetallen naar het werkblad gedurende de uitvoering. Maak dit zo dat de gebruiker de getallen kan verplaatsen en dat een volgende keer de getallen daar verschijnen.



Veelgebruikte Range eigenschappen en methoden

Range("TABLE").Value = 7
Geeft alle cellen in bereik TABLE de waarde 7
Range("TABLE") = 7
.Value is default dus kun je ook weglaten
Range("TABLE").Value2 = Range("Copy").Value2
Value2 neemt enkel waarde over, terwijl value de waarde plus financiele opmaak plus datum opmaak overneemt. Value2 werkt sneller.
Range("TABLE").Copy
Kopieert alles
Range("TABLE").Paste
Plakt alles
Range("TABLE").PasteSpecial xlPasteValues
Plakt enkel waarden
Range("TABLE").PasteSpecial xlPasteFormulas
Plakt enkel formules
Range("TABLE").PasteSpecial xlPasteFormats
Plakt enkel formats
Range("TABLE").Value = ""
Verwijdert alle waarden
Range("TABLE").ClearContents
Verwijdert alle waarden
Range("TABLE").Clear
Verwijdert alles. Dus ook formats en beveiligingsinstelling (soms ongewenst)
Range("TABLE").Rows.Count / .Columns.Count
Geeft het aantal rijen / kolommen in een bereik
Range("B:B").EntireColumn.Hidden = true/false
Verbergt/toon kolom(men)
Range("5:9").EntireRow.Hidden = true/false

For Each C In Range("ShowHide")
   C.EntireRow.Hidden = (C.Value=0)
Next C
Verbergt/toon rij(en)

Handig om een range te maken met een formule die 0 wordt als een rij onzichtbaar moet zijn, en 1 als zichtbaar.
Vervolgens kan je eenvoudig door die formule range heenlopen om rijen (on)zichtbaar te maken.
(Noot: snellere VBA methode mogelijk, maar dat vereist langere en geavanceerdere code.)
Range("TABLE").SpecialCells(xlCellTypeVisible).Value = 7
Geeft alle zichtbare cellen waarde 7. Handig bij gebruik van autofilter.
Range("A1").CurrentRegion = 7
Geeft alle cellen in het aaneengesloten bereik van cel A1 een waarde 7
Range("A1:A100").Offset(rij,kolom).value = 7
Werkt zoals de standaard functie VERSCHUIVING. Met offset spring je naar andere cel(len) rij rijen verder en kolom kolommen verderop. Negatieve waarden betekent een sprong terug.
Range("A1").Cells(rij,kolom).value = 7
Cells lijkt op offset maar start vanaf cel zelf: Range("A1").Cells(1,1) verwijst naar Range("A1")
Range("A:A").Delete
Range("A1:A100,C1:C100").Delete
Verwijdert een range.
Meerdere ranges tegelijkertijd, kan ook.
For i = 1 To Sheets.Count
   Sheets(i).Range("A1") = 9
Next i1
Door alle werkbladen heenlopen


De grootte van een range dynamisch aanpassen kan met 
Range("A1").Resize(aantal_rijen,aantal_kolommen)

Range en Loops

Methode I
Dim iRow As Long
Dim iCol As Long
Dim R As Range
Set R = Range("MyTable")

For iRow = 1 to R.Rows.Count
   For iCol = 1 to R.Columns.Count
      R.Cells(iRow,iCol).Value="hallo"
   Next iCol
Next iRow
Methode II
Dim C As Range
Dim R As Range
Set R = Range("MyTable")

For Each C in R
   C.Value = "hallo"
Next C

Methode I gebruikt een rij en kolomteller om door alle cellen van het bereik te lopen; dit geeft controle over de volgorde. Methode II is slanker.

Oefening

Vul een range van 5 rijen en 5 kolommen met willekeurige getallen tussen 1 en 10. Maak een knop die elke rij en kolom verbergt waarvan de som kleiner dan 15 is. Maak een ander knop die de hele tabel weer zichtbaar maakt. Beide moeten nog altijd correct functioneren als het aantal rijen of kolommen wordt gewijzigd.

Projectverkenner

Ga naar de VBA-editor. Als je de projectverkenner links niet in beeld zien, ga dan naar menu Beeld → Projectverkenner (of druk CTRL+R).

VBA code kun je aantreffen op verschillende plaatsen:
  • Blad1, Blad2, etc. bevat ('private') procedures die logisch thuishoren bij het werkblad (onder opdrachtknoppen in het werkblad)
  • ThisWorkbook bevat procedures die worden uitgevoerd bij openen of sluiten van het bestand (Private Sub Workbook_Open en Workbook_Close).


  • Module1, Module2, etc. bevat procedures die beschikbaar zijn voor het hele bestand. Macro opnames staan in een module (volgende hoofdstuk). Als je een macro opneemt, wordt automatisch een module aangemaakt. Gebruikersgedefinieerde functies moeten in een module staan. Modules voeg je handmatig toe via VBA-editor menu Invoegen.


Macro opname

Als je niet weet hoe je een bepaalde handeling moet omzetten in code, is het handig om een macro op te nemen (Excel kent te veel methoden/objecten/eigenschappen om die uit je hoofd te leren). Alle handmatige handelingen die je tijdens de opname uitvoert, worden automatisch als een sub procedure opgeslagen in een module. Vervolgens kun je die code bestuderen (en vervolgens naar wens aanpassen of anderszins herbruiken).


Macro opname genereert overigens wel vaak 'overtollige' code
Range("B3:C14").Select
Selection.Copy
Range("J5").Select
ActiveSheet.Paste
Application.CutCopyMode=false

versus
Range("B3:C14").Copy Destination:=Range("J5")
Application.CutCopyMode=false

Application.CutCopyMode=false zorgt ervoor dat het klembord wordt geleegd.

Als je enkel data wilt overhalen zonder opmaak en er nooit filters actief zijn, dan kan je beter het volgende doen:
Range("J5:J16").value2=Range("B3:C14").value2

Of als je daarbij wel datum-format of currency-format wilt behouden:
Range("J5:J16").value=Range("B3:C14").value

Dit werkt sneller en heeft als bijkomend voordeel dat je buiten het klembord om werkt, dus minder RAM verbruikt.

Oefening

Maak een tabel met data aan. Maak vervolgens een opdrachknop aan die automatisch het autofilter op een heel specifieke manier instelt. Maak ook een opdrachtknop aan die alle filterinstellingen wist (zonder het filter zelf te verwijderen).

Gebruikersgedefinieerde functie

Je eigen Excel functie maak je aan als
Public Function
die je plaatst in een module. Modules voeg je toe via VBA-editor menu Invoegen. Voor het overige zijn ze precies gelijk aan andere VBA functie procedures.
  • Voordelen: je kunt missende functionaliteit toevoegen. Je kunt bestanden kleiner/leesbaarder maken.
  • Nadelen: gebruikersgedefinieerde functies zijn trager dan standaardfuncties. Gebruikers moeten macro's inschakelen. Soms weten ze niet dat ze gedeactiveerd zijn, of weten ze niet hoe je ze inschakelt (via Bestand → Opties → Vertrouwenscentrum → Macro-instellingen).



Hierboven staat een voorbeeld van een functie die de hemelsbrede afstand tussen twee geografische locaties uitrekent.
Op deze website staat ook een uitgebreid voorbeeld (met downloadbaar bestand) van een gebruikersgedefineerde functie die interpoleert binnen een 2-dimenionele tabel: interpolateXY voorbeeld

Oefening: maak een gebruikersgedefinieerde functie closestLocation

Maak een tabel aan met als velden: Nummer, X, Y. Maak daarin 10 willekeurige locaties aan. Maak boven de tabel twee gebruikersinvoercellen aan: X en Y. Maak nu een functie die voor de ingevoerde locatie de dichtsbijzijnde uit de tabel zoekt en daarvan het nummer geeft.


De opdracht was puur bedoeld ter opdracht. Want het kan zonder VBA, met de volgende matrixformule:
{=VERGELIJKEN(MIN(WORTEL((C7:C16-C3)^2+(D7:D16-D3)^2));WORTEL((C7:C16-C3)^2+(D7:D16-D3)^2);0)}

Matrixformules zijn niet heel eenvoudig te bedenken en worden al snel 'onleesbaar'. Ze zijn wel standaard Excel én doorgaans sneller dan een VBA-functie.

Keuzerondjes en selectievakjes

Naast knoppen kom je met name keuzerondjes en selectievakjes tegen in user interfaces.

Ze bestaan in twee 'smaken': Forms en ActiveX.
  • Forms: kun je alleen indirect lezen en aansturen via de aan het keuzerondje/selectievakje gekoppelde cel op het werkblad (zie plaatje).
  • ActiveX: kun je daarnaast ook direct lezen en aansturen met VBA code (bijvoorbeeld
    Me.chkDieren = true
    ).
Toch raden wij Forms aan, want ActiveX werkt niet op Apple computers en hapert op veel computers die worden aangesloten op een beamer (waardoor je demo mislukt).

Hoe een ActiveX knop om te zetten naar Forms knop

  1. Gegegeven een ActiveX knop en code
    Private Sub cmdHelloMsg_Click()
       MsgBox "hello"
    End Sub

  2. Verander het woord Private in Public
    Public Sub cmdHelloMsg_Click()
       MsgBox "hello"
    End Sub

  3. Creëer een Forms knop en wijs de code toe. Dit Assign macro scherm opent automatisch gedurende de aanmaak van de knop. Verwijder daarna de ActiveX knop.


Oefening - VBA code koppelen aan Forms opdrachtknop

Merk op dat we bij de eerste opdracht een ActiveX opdrachtknop hebben aangemaakt. Dat was handig omdat de syntax van de sub procedure automatisch werd aangemaakt. Maak nu een Forms opdrachtknop aan en koppel die aan de code. Verander daartoe de Sub procedure van Private in Public. Selecteer vervolgens de Forms knop → rechtermuisklik → Macro toewijzen → selecteer de subprocedure.

Oefening - VBA code koppelen aan Forms selectievakje en keuzerondje

Maak een opdrachtknop die een melding geeft met daarin de waarde van een selectievakje en van een keuzerondje.

Code versnellen

  • Gebruik een variabele om meermaals naar een bepaalde range te verwijzen. Dat houdt de code ook korter.
    dim R as Range
    Set R = Sheets("SheetXYZ").Range("InvoerXYZ")
    R.value=7

  • Voorkom verversen van het scherm tijdens uitvoering van een procedure / het schrijven van waarden naar het werkblad
    Application.ScreenUpdating = false

  • Voorkom herberekenen tijdens het schrijven van waarden naar het werkblad
    Application.Calculation = xlCalculationManual
  • Do While ...

       Schrijf eerst alles weg
       Herbereken dan eenmalig:
    Calculate

       Sla uitkomsten op
       Toon eventueel de voortgang:
    Application.StatusBar = "Bezig met berekenen van item " & str(iItem)

    Loop


    Activeer regulier Excel gedrag bij verlaten van de procedure:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = true
    Application.StatusBar = ""

  • Voorkom page break 'bug'
    ActiveSheet.DisplayPageBreaks = False

  • Verwijder gelinkte plaatjes, want deze veroorzaken enorme vertraging.

  • Gebruik (matrix)variabelen in plaats van het werkblad om data (tijdelijk) in op te slaan tijdens executie. Werk deze optie pas uit als rekentijd te lang blijft, nadat je bovenstaande al hebt gedaan, want deze optie vergt aanzienlijk meer werk dan bovenstaande.

  • Merk op dat het ook de formules kunnen zijn die het bestand (te) traag maken!!
    Vaak gezien zijn teveel, niet-geoptimaliseerde VERT.ZOEKEN om data over te halen. Als je VERT.ZOEKEN wilt versnellen, sorteer dan je data en gebruik VERT.ZOEKEN met argument 'niet-geheel exacte overeenkomst' ingesteld op WAAR, met een twee-stappen-aanpak: doe eerst een VERT.ZOEKEN op de key-kolom van de zoektabel en controleer of de VERT.ZOEKEN uitkomst overeenkomt met de key (dan is er dus data over te halen vanuit die zoektabel), en als dat zo is doe dan nogmaals een VERT.ZOEKEN om de data zelf over te halen. Ter illustratie: het doorzoeken van 50.000 records vergt dan maar 2 (twee-stappen-aanpak) x 16 stappen (want 2^16 > 50.000) i.p.v. gemiddeld 25.000, dus duizend keer sneller.

  • Let op: als je bestand erg traag is vanwege de hoeveelheid formules, dan kan het gebeuren dat de VBA al verder gaat voordat alle berekeningen klaar zijn, met foute uitkomsten tot gevolg. Om VBA te laten wachten totdat alle berekeningen volledig zijn uitgevoerd, kan je de volgende code gebruiken:
    Application.Calculation = xlCalculationAutomatic
    Application.Calculate
    Do Until (Application.CalculationState = xlDone)
       DoEvents
    Loop

String functies

  • left("hello",3)
    → "hel" = geef 3 linker karakters
  • right("hello",2)
    → "lo" = geef 2 rechter karakters
  • mid("hello",3,2)
    → "ll" = geef 2 karakters, startend met de 3e
  • instr("hello","el")
    → 2 = geef de startpositie van gezochte substring binnen de string. Als het resultaat 0 is, dan is de substring niet gevonden.
  • "hello" & " and good bye"
    → "hello and good bye" = voeg tekst samen
  • trim(" hello ")
    → "hello" = verwijder spaties rondom een woord
  • Ucase("hello")
    → "HELLO" = zet alles in hoofletters
  • Lcase("HELLO")
    → "hello" = zet alles in kleine letters
  • Format(0.8954,"00.00%")
    → 89.45% = stel specifieke tekstopmaak in

Foutafhandeling

Je kunt
GoTo
gebruiken om te 'springen' (een stuk code over te slaan) naar een ander plek binnen je code. Dit wordt vaak gebruikt in combinatie met
On Error
(en
Exit Sub
) om specifiek foutafhandelingsverloop te programmeren.



Autofilters

Activeren van autofilter van actieve sheet (filter op twee kolommen)
ActiveSheet.Range("$B$3:$D$8").AutoFilter Field:=2, Criteria1:="2"
ActiveSheet.Range("$B$3:$D$8").AutoFilter Field:=3, Criteria1:="5"


Activeren van autofilter van een table
ActiveSheet.ListObjects("tabTest").Range.AutoFilter Field:=3, Criteria1:="3"


Deactiveren van autofilters is nodig als je alle data wilt kopiëren en plakken.

Deactiveren van autofilter van het huidige werkblad. Let op: dit deactiveert niet de filters van tabellen op het werkblad!
If Me.FilterMode Then
   Me.ShowAllData
End If

Deactiveren van autofilter van een specifieke tabel op het huidige werkblad
If Me.ListObjects("tableTest").ShowAutoFilter Then
   Me.ListObjects("tabTest").AutoFilter.ShowAllData
End If

Verwijderen van autofilter (indien aanwezig) van een bepaald werkblad
If Sheets("test").AutoFilterMode Then Sheets("test").Cells.AutoFilter


Inlezen van bestanden & schrijven naar bestanden

Inlezen vanuit ander MS Excel bestand


  • Deactiveer autofilters als je .copy .paste gebruikt en alle data wilt kopiëren.
  • Verwijder eerst alle oude waarden van de copy-to-range als je gebruik maakt van flexibele ranges (anders blijft er achteraan oude data staan als de nieuwe set aan data korter is).

Dim ThisApp As String
Dim ThatApp As String
Dim fileName as string

ThisApp = ActiveWorkbook.Name
fileName="C:\test.xlsb"
Workbooks.Open fileName
ThatApp = ActiveWorkbook.Name
Workbooks(ThisApp).Sheets("Sheet1").Range("A1:C10").Value2 = Workbooks(ThatApp).Sheets("Sheet1").Range("A1:C10").Value2
Windows(ThatApp).Close


Schrijven naar nieuw MS Excel bestand


Dim wb
Dim dest
Set wb = ActiveWorkbook
Set dest = Workbooks.Add(xlWBATWorksheet)
wb.Sheets("Sheet1").Range("A1:M1000").Copy
dest.ActiveSheet.Paste
Application.CutCopyMode = False
Application.DisplayAlerts = False 'do not show message that asks if you want to replace an existing file
dest.SaveAs "C:\test.xlsb", FileFormat:=50 '50=xlsb / 51=xlsx
dest.Close
Application.DisplayAlerts = True

Inlezen van .csv bestand(en)


Dim fileName as string
Dim fileNumber
Dim textLine as string
Dim i as long

fileName="C:\test.csv"
fileNumber = FreeFile
Open fileName For Input As #fileNumber
i = 1
Do Until EOF(fileNumber)
   Line Input #fileNumber, textLine
   Sheets("CSV").Cells(i, 1) = textLine
   i = i + 1
Loop
Close #fileNumber

Sheets("CSV").Columns("A:A").Select
Selection.TextToColumns Destination:=Sheets("CSV").Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True


Meerdere bestanden inlezen

Dim thisFile As String
thisFile = Dir("C:\*.xlsx")
Do While (thisFile <> "")
   MsgBox thisFile
   '...process the file...
   thisFile = Dir
Loop

Schrijven naar een .csv bestand


Dim FileNumber
Dim ExportFilename as String
Dim textLine as String
Dim iRow as Long

FileNumber = FreeFile
ExportFilename = "c:\test.csv"
Open ExportFilename For Output As #FileNumber
For iRow = 1 To Range("A1").CurrentRegion.Rows.Count
   textLine = Cells(iRow, 1).Value2
   Print #FileNumber, textLine
Next
Close #FileNumber

Een dubbelklik procedure met bestand selectiescherm


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Cells(1, 1).Address = Range("inputfile1").Address Then
      Dim FilesToRead
      On Error GoTo exitSub
      FilesToRead = Application.GetOpenFilename("(*.xlsb),*.xlsb", MultiSelect:=False)
      If FilesToRead = False Then Exit Sub
      Range("inputfile1").Value = FilesToRead
   End If
exitSub:
End Sub


Oefening: inlezen van meerdere bestanden, manipuleren data, en exporteren naar bestand

  1. Exporteren naar CSV bestand
    • Maak een sheet “S1” aan met in kolom A honderd willekeurige getallen tussen 0 en 365.
    • Maak een sheet “UI” aan, met een knop Export en een genaamde cel waarin je pad+naam van het exportbestand (type .csv) kunt ingeven.
    • Maak een procedure “Export” aan die een csv-bestand aanmaakt met daarin de de data van kolom A van sheet S1.
    • Voeg
      Shell "Notepad.exe " & bestandsnaam, vbNormalNoFocus
      als je het aangemaakte bestand direct na aanmaak wilt openen, ter controle.

  2. Pas nu handmatig het bestand als volgt aan
    • Voeg aan sheet “S1” in kolom B een formule toe, die gerekend vanaf 1-1-2018 het aantal dagen van kolom A erbij optelt en dus een willekeurige datum in 2018 oplevert.
    • Kopieer sheet “S1” drie maal: S2,S3,S4.

  3. Maak een procedure “cmdCollect” aan die alle data uit de B kolommen van sheets S1t/m4 onder elkaar plakt in een nieuwe sheet SALL
    • Begin eerst met kopiëren van data vanuit 1 sheet.
    • Voeg dan een for next toe waarin je alle 4 sheets doorloopt.
    • Maak gebruik van macro recording om te achterhalen hoe je code eruit zou kunnen zien v.w.b. springen naar laatste regel springen. Of zoek op Internet. MrExcel.com en StackOverflow.com en zijn uitstekende websites. En bedenk daarbij ook dat er altijd meerdere wegen naar Rome leiden.

  4. Maak een procedure “cmdRemove” die alle regels met datum > vandaag uit sheet SALL verwijderd

  5. Maak een procedure “cmdFormat” aan die datum formats instelt
    • Voeg aan sheet UI een veld toe waarin je een datumformat kunt ingeven, zoals bijvoorbeeld "yyyy-mm-dd" of "ddd.M.yyy"
    • De procedure past alle datums in sheet SALL naar het ingegeven format m.b.v. de standaard VBA functie
      Format
      • Gebruik hierbij
        .value2
        (dus géén
        .value
        , want die bevat nog eventuele datum en/of currency format waardoor 11-12-2018 abusievelijk converteert naar “12-11-2018” i.p.v. “11-12-2018”)
      • Format is wat anders dan
        Range.NumberFormat
        instellen. Dat laatste past alleen de weergave aan, maar laat de achterliggende waarde intact.
        Format
        converteert de datum naar een string die als zodanig in het .csv bestand verschijnt.

  6. Pas de exportprocedure aan. Het csv bestand moet nu de gegevens van sheet SALL exporteren.

  7. Importeren van gegevens uit Excel-bestanden
    • Voeg aan sheet “UI” een veld toe waarin je een importdirectory kunt ingeven
      • Plaats in die directory de oefenbestanden (uitgereikt door de docent)
    • Maak een knop import aan met de volgende procedure “cmdImport” die
      • Stap 1: van alle bestanden uit die importdirectory één voor één de naam weergeeft in een msgbox
      • Stap 2: per bestandsnaam kijkt of er een “d1”, “d2”,”d3” of “d4” in voorkomt, waarvan dat gïmporteerd moet worden in rsp “S1”,”S2”, “S3”, “S4”. Geef naast de bestandsnaam ook die “S1” etc. terug in de msgbox.
      • Stap 3: zet van elk bestand de gegevens over naar de bijbehorende sheet, in kolom A

  8. Maak er nu één geheel van
    • Voeg een extra knop toe die alle procedures combineert in een procedure “cmdAll”: importeert bestanden, verwerkt data tot sheet SALL, exporteer naar .csv-bestand
    • Zorg ervoor dat bij meer/minder data in importbestanden alles correct blijft functioneren.
      • Veeg oude data vooraf leeg.
      • Zorg ervoor dat fomules in kolom B “synchroon lopen” met vulling in kolom A (waarbij gegeven is dat er wel altijd meer dan 1 record in elk importbestand staat, anders moet je nog meer zaken gaan afvangen)

  9. Bewerkstellig dat eventuele filters geen roet in het eten gooien, noch filters in invoerbestanden, noch filters in de applicatie zelf. Deactiveer hiertoe eventeel aanwezige filters.

Variabelen: constanten & gebruikersgedefinieerde

Constanten

Je kunt variabelen als constanten definiëren.


In een werkblad - ter gebruik in dat werkblad:
Const conPi = 3.14159

In een module - ter gebruik in het hele bestand:
Public Const conPi = 3.14159


Gebruikersgedefinieerde variabelen

Je kunt ook zelf een variabelentype definiëren, bijvoorbeeld als data bij elkaar hoort:

Cities(1).name = "Haarlem"
Cities(1).longitude = 4.666
Cities(1).latitude = 52.353
Cities(2).name = "Alphen"
Cities(2).longitude = 4.662
Cities(2).latitude = 52.128

Gevarieerd

  • Met
    WorksheetFunction.
    kun je standaard functies aanroepen.
    Bijvoorbeeld:
    XX = WorksheetFunction.RandBetween(1,9)
    → Geheel getal XX (integer) krijgt een willekeurig waarde tussen 1 en 9.

  • Gebruik
    IsError(C.Value)
    of
    IsEmpty(C.Value)
    om invoer vooraf te controleren / invalide invoer af te vangen.

  • Plaats na een
    .Paste
    of
    .PasteSpecial
    de code
    Application.CutCopyMode = False
    om het klembord te legen en het werkgeheugen niet vol te laten lopen.

    Beter is het om rechtsreeks waarden over te zetten via (
    R1.Value = R2.Value
    ) en zo het klembord te vermijden. Dan moeten er echter nooit filters relevant/actief zijn, want die worden genegeerd: alle data wordt overgehaald, niet enkel die van zichtbare cellen! Nog sneller is (
    R1.Value2 = R2.Value2
    ) want dat negeert datum en currency formats en kopieert enkel de onderliggende numerieke waarden.

    Ditzelfde kan met formules, maar let daarbij wel op: je moet ze kopiëren over de originele formule heen om ze vervolgens netjes naar onder door te laten lopen (dus niet naar 1 regel lager en verder kopiëren. Deze rechtstreeks formule-kopieermethode hapert echter als tussengelegen rijen onzichtbaar zijn. Veiliger is om .Paste te gebruiken.

  • Je kunt refereren naar de huidige actieve sheet met
    Me
    of
    ActiveSheet
    bijvoorbeeld
    Me.range("Table").value=""

  • Workbook.RefreshAll
    ververst alle externe links en draaitabellen.

  • Gebruik
    Application.International(xlDecimalSeparator)
    om het actuele decimale scheidingsteken van Excel te achterhalen (en negeer Application.DecimalSeparator).

  • Gebruik
    & _
    om extreem lange VBA coderegels af te breken en ze weer volledig in beeld te krijgen
    If MsgBox("Primenumber" & Str(nPrime) & " = " Str(iNumber) & _
    "Show next prime number?", vbYesNo) = vbNo Then End


  • Herhaalbare simulatie (handig voor debuggen)
    Rnd
    geeft een willekeurig getal uniform verdeeld op [0,1>. Zo geeft
    Int((9 * Rnd) + 1)
    een willekeurig geheel getal tussen 1 en 9.
    Randomize
    initialiseert de random generator met een willekeurige seed

    Gebruik de volgende tweeregelige constructie - met een vaste waarde voor VasteRandomSeed - om een simulatie te maken die dezelfde reeks willekeurige getallen oplevert elke keer als je de simulatie opnieuw start: handig voor debuggen.
    Rnd -1
    Randomize (VasteRandomSeed)


  • Beveilig je VBA code door deze af te grendelen en eventueel een wachtwoord mee te geven (vergeet dat dan niet aan iemand anders door te geven).




  • Programmacode priemgetal generator - Uitwerking oefening

    Uitwerking stap 1


    Korte versie

    Private Sub cmdStep1_Click()
       Dim iNumber As Long

       iNumber = 0
       Do While True
          iNumber = iNumber + 1
          If MsgBox(iNumber, vbYesNo, "Continue?") = vbNo Then Exit Sub
       Loop
    End Sub


    Langere versie, meer 'volgens het boekje' met betrekking tot de Do While conditie

    Private Sub cmdStep1_Click()
       Dim iNumber As Long
       Dim doContinue as boolean

       iNumber = 0
       doContinue = true
       Do While doContinue = True
          iNumber = iNumber + 1
          If MsgBox(iNumber, vbYesNo, "Continue?") = vbNo Then doContinue=false
       Loop
    End Sub


    Uitwerking stap 2


    Private Sub cmdStep2_Click()
       Dim iNumber As Long
       Dim isOdd As Boolean
       iNumber = 0

       Do While True
          iNumber = iNumber + 1

          isOdd = True
          If (iNumber Mod 2 = 0) Then isOdd = False

          If isOdd Then
             If MsgBox(iNumber, vbYesNo, "Continue?") = vbNo Then Exit Sub
          End If
       Loop
    End Sub


    Uitwerking stap 3


    Private Sub cmdStep3_Click()
       Dim iNumber As Long
       Dim isOdd As Boolean
       Dim primeNumber(9999999) As Long
       Dim nPrime As Long

       iNumber = 0
       nPrime = 0

       Do While True
          iNumber = iNumber + 1

          isOdd = True
          If (iNumber Mod 2 = 0) Then isOdd = False

          If isOdd Then
             nPrime = nPrime + 1
             primeNumber(nPrime) = iNumber
             If MsgBox(iNumber, vbYesNo, "Continue?") = vbNo Then Exit Sub
          End If
       Loop
    End Sub


    Uitwerking stap 4

    Met meer uitgewerkte melding richting de gebruiker.

    Private Sub cmdStep4_Click()
       Dim iNumber As Long
       Dim isPrimeNumber As Boolean
       Dim primeNumber(9999999) As Long
       Dim nPrime As Long
       Dim iPrime As Long

       iNumber = 0
       nPrime = 0

       Do While True
          iNumber = iNumber + 1

          isPrimeNumber = True
          For iPrime = 2 To nPrime
             If iNumber Mod primeNumber(iPrime) = 0 Then
                isPrimeNumber = False
                Exit For
             End If
          Next

          If isPrimeNumber Then
             nPrime = nPrime + 1
             primeNumber(nPrime) = iNumber
             If MsgBox("Primenumber" & Str(nPrime) & " = " & Str(primeNumber(nPrime)) & vbCrLf & "Continue?", vbYesNo, "Continue?") = vbNo Then Exit Sub
          End If
       Loop
    End Sub