In bovenstaande ElseIf-constructie kun je in de voorwaarden variabelen van verschillende type gebruiken. In onderstaande Select Case-alternatief kun je dat niet.
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
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 is
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 zelf proberen leer/ervaar je het meest!
- 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)
- Maak een 'is oneven' check
- Gebruik If Then
- Functie Mod geeft de rest na een deling, bijvoorbeeld: (41 Mod 3) geeft 2.
- 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.
- 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).
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
Public Function extractSubString(ByVal InputString As Range, ByVal separator As String, ByVal n As Integer, Optional ByVal ReadBackwards As Boolean) As Variant
Dim x As Variant
x = Split(InputString, separator)
If n > 0 And n - 1 <= UBound(x) Then
If (ReadBackwards = False) Or IsNull(ReadBackwards) Or IsEmpty(ReadBackwards) Then
extractSubString = x(n - 1)
Else
extractSubString = x(UBound(x) - n + 1)
End If
Else
extractSubString = CVErr(xlErrNA)
End If
End Function
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
- Gegegeven een ActiveX knop en code
Private Sub cmdHelloMsg_Click()
MsgBox "hello"
End Sub
- Verander het woord Private in Public
Public Sub cmdHelloMsg_Click()
MsgBox "hello"
End Sub
- 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
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
- Exporteren naar CSV bestand
- 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.
- 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.
- Maak een procedure “cmdRemove” die alle regels met datum > vandaag uit sheet SALL verwijderd
- 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.
- Pas de exportprocedure aan. Het csv bestand moet nu de gegevens van sheet SALL exporteren.
- 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
- 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)
- 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
