Problemen oplossen met VBA – V.Cools pagina 1 Wij laten de

352%/(0(123/266(10(79%$
9$1$/*25,70(727352*5$00$
Wij laten de computer allerlei taken voor ons uitvoeren:
ƒ
ƒ
ƒ
ƒ
ƒ
Een tekst bewerken
Een schoolrapport berekenen
Een overschrijving uitvoeren van de ene naar de andere bankrekening
Surfen op het Internet
…
De computer kan veel? Neen, op zichzelf kan de computer niets! Een toestel kan immers niet
"denken". De computer heeft instructies nodig om hem te helpen. Reeksen van instructies noemen
we DOJRULWPHQ. Ze moeten in de computer ingevoerd worden in een taal die de computer verstaat!
Een SURJUDPPD is niets meer of minder dan een algoritme dat uitgevoerd kan worden door de
computer. Het wordt geschreven in een bepaalde SURJUDPPHHUWDDO. De instructies die het verloop
van een programma bepalen, noemen we de EURQFRGH.
9225%((/'(19$1352*5$00$¶6
Van sommige programma's kan je de broncode opvragen. Open de webpagina Testweb1.htm.
Internet Explorer wordt automatisch geopend en een webpagina verschijnt. Let op de inhoud van de
titelbalk en bekijk de inhoud van het venster. Kies vervolgens “Beeld” – “Bron”. De broncode wordt
getoond met de toepassing Kladblok.
'HEURQFRGHYDQHHQZHESDJLQD«
De programmeertaal die hier gebruikt wordt, is
HTML (HyperText Markup Language). De
instructies staan tussen zogenaamde <tags>".
Vergelijk de webpagina met de broncode.
Tracht op die manier de betekenis van de
opdrachten te achterhalen!
Dit programma werkt niet zonder een
webbrowser zoals Internet Explorer. De browser
zet de code om in een machinetaal die
“begrepen” wordt door de computer.
Internet Explorer is een LQWHUSUHWHU voor html!
Problemen oplossen met VBA – V.Cools
pagina 1
Open het programma Eurocalculator.exe. Met deze toepassing kan je waarden in EURO omrekenen
naar BEF en omgekeerd. Ga even na hoe het programma werkt en sluit het daarna. Open nu het
bestand Eurocalculator.exe in Kladblok om de broncode te zien…
'HEURQFRGHYDQKHWSURJUDPPD(XURFDOFXODWRU«
De broncode van dit programma is geschreven in de
programmeertaal Visual Basic, maar is niet meer leesbaar!
De opdrachten werden omgezet in een taal die verstaanbaar is
voor de computer. Op die manier kan het programma
rechtstreeks uitgevoerd worden, zonder tussenkomst van
andere programma’s.
Het overbrengen van instructies van een hogere
programmeertaal naar machinetaal, noemt men FRPSLOHUHQ.
Open Euro.xls. Het is mogelijk dat één van onderstaande waarschuwingen op het scherm verschijnt.
Waarschuwing 1
Klik op “Macro’s inschakelen”!
Waarschuwing 2
Klik op OK. Kies “Opties…” in menu “Extra”. Breng tabblad “Beveiliging” op de voorgrond. Klik op de
knop “Macrobeveiliging”. Kies een gemiddeld beveiligingsniveau en klik op de knop “OK”. Klik
nogmaals op “OK” en sluit de toepassing. Open Euro.xls opnieuw. Op het scherm zal nu
waarschuwing 1verschijnen. Schakel de macro’s in!
pagina 2
Problemen oplossen met VBA – V.Cools
Van zodra Euro.xls geopend is, klik je in tabblad “Valuta” op de knop “REKEN
OM VAN EURO NAAR BEF”. Gebruik de toepassing om na te gaan wat de
waarde is van 1000 EURO in Belgische frank. Klik op “OK” als je klaar bent.
Vraag de broncode op: kies in de menubalk van Excel: “Extra” – “Macro” – “Macro’s…”, selecteer de
macro met de naam “RekenOmNaarBef” en klik op de knop “Bewerken”. In het volgende venster
wordt de broncode van het programma getoond.
'HEURQFRGHYDQ5HNHQ2P1DDU%HI
De broncode is leesbaar en, met een beetje moeite, te begrijpen. Het programma is geschreven in de
programmeertaal Visual Basic for Applications, kortweg VBA genoemd. Om de opdrachten te laten
uitvoeren is de toepassing Excel onmisbaar! Excel fungeert als LQWHUSUHWHU.
((1352*5$00$217:,..(/(1
Hoe is het programmaatje RekenOmNaarBef tot stand gekomen? We beschouwen hier de
verschillende fasen die doorlopen werden.
1. PROBLEEMSTELLING
We gaan tegenwoordig allemaal al heel vertrouwd om met de euro. Betalen en ontvangen in euro
is geen probleem. Toch hebben heel wat mensen nog steeds problemen met het aanvoelen van
de waarde van de nieuwe munt. Daarom willen we een computerprogramma maken waarmee we
gelijk welk bedrag in euro onmiddellijk kunnen omzetten in Belgische frank.
2. PROBLEEMANALYSE
Welke gegevens hebben we nodig?
ƒ
ƒ
de waarde van de euro
het bedrag in euro
> 1 EUR = 40,3399 BEF
> dit bedrag moet ingevoerd worden door de gebruiker
Wat wordt er gevraagd?
ƒ
De waarde van het bedrag in BEF
Problemen oplossen met VBA – V.Cools
pagina 3
3. ONTWERP DE OPLOSSINGSMETHODE
6FKULMIHHQDOJRULWPHRPKHWSUREOHHPRSWHORVVHQ
5HNHQ2P1DDU%HI
Vraag het bedrag in EURO
Reken om naar BEF
Toon het bedrag in BEF
--- Vraag het bedrag in Euro--'UXN
“Typ het bedrag in Euro: “
/HHV
Euro
--- Reken om naar BEF--Bef
Euro x 40,3399
--- Toon het bedrag in BEF--'UXN
“Het bedrag in BEF is “ + Bef
'UXN: toon de tekst die tussen aanhalingstekens staat op
het scherm.
/HHV: sla het getal dat ingevoerd wordt door de gebruiker
tijdelijk op in het geheugen. De symbolische naam van een
geheugenplaats noemen we een YDULDEHOH. Hier is Euro de
variabele waarin het getal zal bewaard worden.
Het gelijkheidsteken moet je hier lezen als: “ZRUGWJHOLMN
DDQ”. De inhoud van variabele Euro wordt vermenigvuldigd
met de constante 40,3399. Het product zal bewaard
worden in de variabele met de naam Bef.
'UXN: toon de tekst die tussen aanhalingstekens staat op
het scherm en zet de inhoud van de variabele Bef achter
deze tekst!
De deelalgoritmen zijn nu zodanig VWDSVJHZLMVYHUILMQG dat wij precies weten welke instructies de
computer moet uitvoeren. Er is echter nog één probleem: de computer verstaat onze taal niet! De
deelalgoritmen zijn nog DEVWUDFW voor de processor. Door de instructies om te zetten in een
programmeertaal, maken we ze FRQFUHHW!
9HUWDDOGHRSGUDFKWHQLQ9LVXDO%DVLFIRU$SSOLFDWLRQV9%$
Een aantal programmaregels die samen één geheel vormen noemt men een procedure. In VBA
begint elke procedure met de code 6XE, gevolgd door de naam van de procedure. De procedure
eindigt met de code (QG6XE.
$IVSUDNHQLQYHUEDQGPHWQDPHQ
Kies voor alle DOJRULWPHQenDEVWUDFWHGHHODOJRULWPHQ namen die duidelijk verwijzen naar de
inhoud en hou je aan de volgende regels:
¾
¾
¾
in de naam mogen geen spaties voorkomen
als je een naam wil geven die uit meer dan één woord bestaat, dan schrijf je alle woorden aan
mekaar, telkens beginnend met een hoofdletter
de naam moet een bevel zijn
Vb. 5HNHQ2P1DDU%HI
pagina 4
Problemen oplossen met VBA – V.Cools
Kies voor elke YDULDEHOH een naam die naar de inhoud verwijst.
Hou je aan de LNC-afspraken (Leszynski Naming Conventions): voorzie elke naam van een
voorvoegsel dat correspondeert met het gegevenstype!
Vb. De waarde in Euro, die ingevoerd wordt door de gebruiker, is een tekenreeks of VWULQJ. We
noemen de variabele VWU(XUR. In de variabele GEO%HI wordt de waarde in Belgische frank
opgeslagen, “dbl” verwijst naar het type GRXEOH, een numeriek type waarin decimalen toegelaten
zijn. Meer uitleg over de gegevenstypen in VBA, vind je terug met de helpfunctie van de editor.
2SGUDFKWHQYHUWDOHQ
Elke programmeertaal is onderworpen aan strenge taalregels: de V\QWD[LV. Als de programmeur
zich niet houdt aan deze regels, verstaat de computer de opdrachten niet en worden ze ook niet
uitgevoerd! We bekijken hier de opdrachtregels uit RekenOmNaarBef.
,QSXWER[
Inputbox() zet een dialoogvenster op het scherm met het bericht dat tussen de
haken staat. Letterlijke tekst moet tussen aanhalingstekens staan.
In het dialoogvenster staat ook een tekstvak waarin de gebruiker iets kan typen.
Als de gebruiker op de knop OK klikt, is het resultaat van Inputbox een string
(tekenreeks) met de inhoud van het tekstvak. Als de gebruiker op Annuleren klikt,
is het resultaat een lege tekenreeks (“”)
Vb. strEuro = Inputbox(“Typ het bedrag in Euro: “)
deze 3
worden
automatisch in het
dialoogvenster geplaatst
wat
de
gebruiker in het
typt, wordt
opgeslagen in de variabele
strEuro
&'EO
Met CDbl wordt een tekenreeks omgezet in een kommagetal van het type double.
(Convert to double)
Vb. dblBef = CDbl(strEuro) * 40,3399
De tekenreeks die opgeslagen is in strEuro wordt geconverteerd naar een
getalwaarde. Dit is nodig omdat VBA niet kan rekenen met een string!
Het getal wordt vermenigvuldigd met 40,3399. Het product wordt opgeslagen in de
variabele dblBef.
&6WU
Met CStr wordt een getalwaarde omgezet in een tekenreeks of string.
(Convert to string)
Vb. CStr(dblBef)
Het getal dat opgeslagen is in dblBef wordt geconverteerd naar een tekenreeks.
0VJ%R[
Met MsgBox (messagebox) zet je een boodschap op het scherm. Letterlijke tekst
staat tussen aanhalingstekens. Met het plusteken worden twee tekenreeksen
aaneengeschakeld.
Vb. MsgBox “Het bedrag in BEF is “ + CStr(dblBef)
Als de gebruiker het getal 1000 invoert, verschijnt
nevenstaande boodschap op het scherm.
Problemen oplossen met VBA – V.Cools
pagina 5
4. CONTROLEER DE OPLOSSINGSMETHODE
9RHUGHSURJUDPPDFRGHLQ
Om het programma RekenOmNaarBef te kunnen testen, moet de programmacode ingevoerd
worden met de 9LVXDO%DVLF HGLWRU.
Sluit alle Excelbestanden die nog open staan. Open een nieuwe lege werkmap.
Sla de werkmap op onder de naam NN-VBAOEF.xls (NN=je volgnummer in de klas).
Geef aan werkblad 1 de naam Valuta.
Start de editor: kies Extra – Macro – Visual Basic Editor
Kies vervolgens Invoegen – Module
0RGXOHEODG
Typ in het moduleblad de programmacode van RekenOmNaarBef (zie pagina 4).
Om de eerste programmaregel te laten inspringen gebruik je de tabtoets.
Merk op dat de editor automatisch
¾
¾
¾
¾
¾
inspringt bij het begin van een nieuwe programmaregel
informatie geeft over de syntaxis
de syntaxis controleert, eventuele fouten opspoort en aanwijst
programmacode aanvult
onderdelen van de programmacode in een bepaalde kleur zet
(In het dialoogvenster Extra – Opties … is aangegeven welke info de editor moet geven en hoe.)
Sla het programma op: kies Bestand – Opslaan of klik op de knop in de werkbalk. De gehele Excel
werkmap wordt opnieuw opgeslagen!
9RHUKHWSURJUDPPDXLW
Er zijn verschillende werkwijzen mogelijk om het programma te laten uitvoeren vanuit de Visual
Basic Editor.
*HEUXLNGHZHUNEDONYDQGHHGLWRU«
Klik in de standaard werkbalk op de knop “Sub/Userform uitvoeren”
Selecteer in het volgende venster de naam van het programma en klik op de knop “Uitvoeren”.
*HEUXLNYHQVWHU'LUHFW«
Klik op Beeld – Venster Direct, typ de naam van het programma in het venster en druk op Enter.
pagina 6
Problemen oplossen met VBA – V.Cools
Je kan het programma ook starten vanuit een Excel werkblad!
*HEUXLNGHRSGUDFKWHQXLWGHPHQXEDON«
Kies Extra – Macro – Macro’s, selecteer de naam van het programma in het dialoogvenster en klik
op de knop: “Uitvoeren”.
*HEUXLNZHUNEDON9LVXDO%DVLF«
Klik in deze werkbalk op de knop “Macro uitvoeren”
Selecteer de naam van het programma en klik op de knop: “Uitvoeren”.
.RSSHOKHWSURJUDPPDDDQHHQNQRS«
Voor de programmagebruiker is het veel handiger wanneer er een knop
voorzien is om het programma te starten.
Sluit de Visual Basic Editor. Werkblad “Valuta” staat nu op de voorgrond.
Vraag in menu “Beeld” de lijst met werkbalken en activeer “Formulieren”.
Klik op “Knop”. De cursor neemt de vorm aan van een kruisje.
Teken de knop in het werkblad. Als je tijdens het tekenen de Alt-toets ingedrukt
houdt, past de knop precies op de randen van de cellen. Bij het loslaten van de
muisknop, verschijnt een venster met de beschikbare programmacodes.
Selecteer de code van RekenOmNaarBef en klik op “OK”.
Klik met de rechter muisknop op de knop om deze te bewerken. Verander de
tekst in: “REKEN OM VAN EURO NAAR BEF”. Zet deze tekst in vetjes en
centreer. Pas indien nodig het formaat en de plaats van de knop aan.
Voer het programma RekenOmNaarBef een aantal keren uit! Beantwoordt het resultaat aan de
doelstelling van het programma?
Ga ook na wat er gebeurt als de gebruiker:
¾
¾
¾
verkeerde gegevens invoert (Vb. de gebruiker typt tekst waar een getal gevraagd wordt)
het programma annuleert (Vb. de gebruiker drukt op de knop Annuleren of op de escapetoets)
het programma afsluit (Vb. de gebruiker klikt op de sluitknop van het venster)
Merk op dat ons programmaatje alles behalve perfect is! Verder in de cursus bespreken we hoe we
de foutmeldingen kunnen voorkomen!
5. EVALUEER DE OPLOSSINGSMETHODE EN ZORG VOOR DE NODIGE DOCUMENTATIE
Een goed programma is zodanig ontworpen dat het bruikbaar is in verschillende situaties. Mits
enkele aanpassingen kan ons programmaatje gebruikt worden om de waarde van andere valuta
om te zetten in Bef.
Vooral bij uitgebreide toepassingen is het belangrijk dat de programmeur bij elk onderdeel van de
oplossing uitleg of “FRPPHQWDDU” noteert. In VBA staat vóór een FRPPHQWDDUUHJHO een
aanhalingsteken. Bij de uitvoering van het programma worden de commentaarregels genegeerd.
Een goed gedocumenteerd programma kan later veel gemakkelijker aangepast worden!
Vraag opnieuw de Visual basic Editor op. Typ onder de naam van ons programmaatje een extra
commentaarregel: ‘Omrekenen van Euro naar Bef’. Voer het programma uit. Er is in feite niets
veranderd, alleen … zullen we later veel sneller zien waarvoor dit stukje programmacode dient!
Problemen oplossen met VBA – V.Cools
pagina 7
'RHOVWHOOLQJHQGHHO
1
Begrippen omschrijven en op een correcte manier gebruiken:
programma, broncode, interpreter, compileren, variabele, syntaxis, procedure, commentaar
2
Aangeven welke fasen doorlopen worden bij de ontwikkeling van een programma en bij elke fase
een bondige omschrijving geven.
3
Aangeven wat het belang is van de stapsgewijze verfijning bij de ontwikkeling van een programma.
4
Weten aan welke regels we ons moeten houden bij het geven van namen aan algoritmen,
deelalgoritmen en variabelen.
5
Een algoritme zodanig verfijnen dat het alleen nog vertaald moet worden in een programmeertaal.
6
De volgende VBA-opdrachten begrijpen en toepassen:
- Inputbox()
- MsgBox
- CDbl()
- CStr()
- Sub en End Sub
7
Programmacode invoeren in een moduleblad van VBA.
8
Een programma laten uitvoeren vanuit de Visual Basic Editor en vanuit een Excel werkblad.
9
Eenvoudige problemen oplossen volgens de methode van de stapsgewijze verfijning en de
oplossingsmethode testen met VBA.
pagina 8
Problemen oplossen met VBA – V.Cools
,17(5$&7,(7866(1352*5$00$(15(.(1%/$'
!
"$#%&!'(')
Visual Basic for Applications werd ontwikkeld om taken die we uitvoeren met Excel te automatiseren:
gegevens invoeren in cellen, berekeningen uitvoeren, cellen opmaken, …
In dit hoofdstuk leer je
¾
¾
¾
¾
hoe je waarden die gegenereerd worden door een programma in een rekenblad kunt zetten
hoe een programma waarden uit een rekenblad kan halen
hoe je cellen van een rekenblad automatisch kunt laten opmaken
hoe je nieuwe werkbladfuncties voor Excel kunt maken
:$$5'(1,1&(//(16&+5,-9(1
In elk programma herkennen we de drie fasen van het gegevensverwerkend proces:
,192(5
9(5:(5.,1*
8,792(5
YDQLQIRUPDWLH
YDQJHJHYHQV
De gegevens vragen we aan de gebruiker via invoervensters, de informatie tonen we in een
uitvoervenster. Van zodra de gebruiker op de knop OK klikt, verdwijnt alles van het scherm!
Om de gegevens en de informatie toch te bewaren, moeten we ze in het actieve werkblad schrijven.
Hiervoor gebruiken we de opdracht:
>FHOYHUZLM]LQJ@
ZDDUGH
De waarde kan een constante zijn, een variabele of kan ingevoerd worden door de gebruiker via een
invoervenster. Een voorbeeld zal dit verduidelijken!
Problemen oplossen met VBA – V.Cools
pagina 9
Open NN-VBAOEF. Zorg ervoor dat de werkbalk “Visual Basic” zichtbaar is.
Start de Visual Basic Editor en open een nieuw moduleblad. Typ hierin de volgende code:
6XE7RRQ:DDUGHQ
0HWGLWSURJUDPPD]HWWHQZHYHUVFKLOOHQGHZDDUGHQLQKHWDFWLHYHZHUNEODG
[A1] = InputBox("Typ je voornaam")
strGetal = InputBox("Typ een getal")
[A2] = "Misschien wint u ..."
[A3] = strGetal
[B3] = "euro"
(QG6XE
Voeg in NN-VBAOEF een nieuw werkblad in met de naam
“SchrijvenEnLezen”. Laat vervolgens het programmaatje ToonWaarden
uitvoeren. Bekijk het resultaat!
&(/:$$5'(1/(=(1
Typ in hetzelfde moduleblad onderstaande programmacode.
6XE/HHV:DDUGH
0HWGLWSURJUDPPDOH]HQZHGHLQKRXGYDQFHO$
Inhoud = [A10]
MsgBox "In cel A10 staat " + CStr(Inhoud)
(QG
Sub
Typ een willekeurige waarde in cel A10 en voer programma LeesWaarde uit.
&(//(1230$.(10(70$&52¶6
In VBA bestaan allerlei opdrachten om cellen automatisch te laten opmaken. Als beginnend
programmeur is het onbegonnen werk om al deze opdrachten van buiten te leren! We zullen gebruik
maken van de PDFURUHFRUGHU: jij zet de recorder aan, je voert de handelingen uit die je wil
automatiseren en als je klaar bent zet je de recorder weer af. De opeenvolgende opdrachten die nu
geregistreerd zijn, noemt men een PDFUR. Telkens wanneer je de macro gebruikt, worden alle
opdrachten opnieuw uitgevoerd. Macro’s worden automatisch geschreven in VBA en kunnen dus
toegepast worden in onze programma’s.
Het is nu de bedoeling dat we de cellen die gebruikt worden door het programma ToonWaarden,
automatisch laten opmaken. De achtergrond van de cellen moet geel gekleurd worden, de tekst moet
in blauw en in vetjes getoond worden, de cellen moeten omkaderd worden met een blauwe lijn en het
getal moet uitgevoerd worden met het scheidingsteken voor de duizendtallen en zonder decimalen!
pagina 10
Problemen oplossen met VBA – V.Cools
6WDSQHHPGHPDFURRS
Start de opname met de knop
.
Er verschijnt een dialoogvenster. Typ hierin de
naam van de macro en klik op OK.
Wees nu voorzichtig! Alles wat je doet wordt
opgenomen door de recorder.
Voer de volgende reeks opdrachten uit:
Selecteer celbereik A1:B3
Zet in vetjes
Kies een gele achtergrond
Kies een blauwe tekstkleur
Kies een blauwe omtrek
Selecteer cel A3
Maak het getal op met een scheidingsteken voor de duizendtallen
en zonder decimalen
Stop de opname met de knop
.
6WDSWHVWGHPDFUR
Verwijder alle opmaak uit de cellen A1:B3: selecteer de cellen, kies in menu “Bewerken” de optie
“Wissen…” en vervolgens “Opmaak”.
Klik op de knop
om de macro te starten: kies in het dialoogvenster “MaakWaardenOp” en klik op
de knop “Uitvoeren”. Alle opdrachten om de cellen op te maken worden nu automatisch uitgevoerd.
Je kan de instructies bekijken met de Visual Basic Editor: klik op de knop
. De broncode van de
macro staat in een nieuwe module. Merk op dat de opdrachtenreeks begint met de code 6XE,
gevolgd door de naam van de macro. Onder de laatste opdracht staat (QG6XE. Een macro is niks
meer of minder dan een SURFHGXUH waarvan de broncode automatisch gegenereerd wordt door de
macrorecorder!
Keer terug naar werkblad “SchrijvenEnLezen” en verwijder nu zowel de inhoud als de opmaak uit de
cellen A1:B3!
6WDSJHEUXLNGHPDFURLQHHQSURJUDPPD
We kunnen de macro “MaakWaardenOp” gebruiken in het programma “ToonWaarden”.
Open de Visual Basic Editor en vervolledig de procedure “Toonwaarden”:
Sub ToonWaarden()
0HWGLWSURJUDPPD]HWWHQZHYHUVFKLOOHQGHZDDUGHQLQKHWDFWLHYHZHUNEODG
[A1] = InputBox("Typ je voornaam")
strGetal = InputBox("Typ een getal")
[A2] = "Misschien wint u ..."
[A3] = strGetal
[B3] = "euro"
0DDN:DDUGHQ2Sµ5RHSWPDFURRS
End Sub
Sluit de editor en activeer het programma “ToonWaarden” in werkblad “SchrijvenEnLezen”. Tekst en
getal worden in de cellen geplaatst en de cellen worden onmiddellijk opgemaakt!
Problemen oplossen met VBA – V.Cools
pagina 11
:(5.%/$')81&7,(60$.(1
VBA geeft ons de mogelijkheid om gebruik te maken van IXQFWLHV. Een functie is een stukje
programmacode waaraan we waarden doorgeven. Deze waarden noemen we de argumenten of
parameters. De functie rekent met de parameters en stuurt het resultaat terug! Hierin verschilt een
functie van een procedure. Bij een procedure laat je de computer gewoon iets uitvoeren, bij een
functie verwacht je een antwoord van de pc.
%HNLMNHQNHOHYRRUEHHOGHQYDQLQJHERXZGHIXQFWLHV«
Functie in cel C6: =SOM(C1:C5)
De waarden die in de 5 cellen staan worden doorgegeven aan de
functie SOM. De functie telt de getalwaarden op en stuurt het
resultaat terug naar cel C6.
Functie in cel C7: =AANTAL(C1:C5)
De waarden die in de 5 cellen staan worden doorgegeven aan de
functie AANTAL. De functie telt de cellen waarin een getal staat en
stuurt het resultaat terug naar cel C7
Functie in cel C8: =GEMIDDELDE(C1:C5)
De waarden die in de 5 cellen staan worden doorgegeven aan de
functie GEMIDDELDE. De functie telt de getalwaarden op, telt het
aantal cellen met getallen, deelt de som van de getalwaarden door
het aantal getallen en stuurt het resultaat terug naar cel C8.
0DDN]HOIHHQIXQFWLH«
Maak een functie om de omtrek van een
rechthoek te berekenen.
Open werkmap NN-VBAOEF.xls. Voeg een
nieuw werkblad in en noem dit werkblad:
“Rechthoeken”.
Typ in het werkblad nevenstaande gegevens.
Open de Visual Basic Editor. Voeg een nieuwe
module in. Typ hierin de volgende
programmacode:
)XQFWLRQ
fOmtrekRechthoek(dblLengte, dblBreedte)
fOmtrekRechthoek = (dblLengte + dblBreedte) * 2
(QG)XQFWLRQ
Let op begin en einde van een functie!
Wat de naamgeving betreft, houden we ons aan de afspraken (p.4,5)!
pagina 12
Problemen oplossen met VBA – V.Cools
*HEUXLNGHIXQFWLHLQZHUNEODG5HFKWKRHNHQ«
Een functie kan je niet uitvoeren zoals een procedure. De functie heeft parameters nodig!
Sla de werkmap op en sluit de editor. Zet de celwijzer in werkblad “Rechthoeken” in cel B6.
Typ de formule:=fOmtrekRechthoek(B3;B4)
De parameters of argumenten die je wil doorgeven
aan de functie fOmtrekRechthoek, schrijf je tussen
haakjes achter de functie.
Druk op de entertoets.
Het getal in B3 wordt doorgegeven aan de
variabele dblLengte, het getal in B4 wordt
doorgegeven aan de variabele dblBreedte.
Het resultaat verschijnt onmiddellijk in de cel
Met deze 2 waarden berekent de functie de omtrek
van de rechthoek en stuurt het resultaat naar cel
C6.
Gebruik de vulgreep om de formule te kopiëren naar de cellen C6 en D6.
Op die manier wordt ook de omtrek van de rechthoeken 2 en 3 snel berekend!
Schrijf in hetzelfde moduleblad een
functie om de oppervlakte van een
rechthoek te berekenen.
Noem deze functie fOppRechthoek.
Gebruik de functie om de oppervlakte
van de 3 rechthoeken te berekenen.
&RPELQHHUIXQFWLHVHQSURFHGXUHV«
Functies zijn statisch: zij werken met de waarden die ze toegestuurd krijgen en zij sturen het resultaat
terug. Met een procedure kan je de gebruiker interactief laten werken met de computer: de gebruiker
typt zelf de gegevens en het programma toont de informatie.
We kunnen functies en procedures combineren: we gebruiken een procedure om de gegevens te
laten invoeren door de gebruiker en we gebruiken de functies om de berekeningen te laten uitvoeren
in het werkblad.
Vervolledig werkblad
Rechthoeken:
¾
voorzie ruimte om de
gegevens van een vierde
rechthoek in te voeren
¾
kopieer de functies die in
D6:D7 staan naar E6:E7
Open de Visual Basic Editor en typ onderstaande programmacode in moduleblad 7, onder de code
van de twee functies.
6XE9UDDJPDWHQ
[E3]= InputBox("Voer de lengte van de rechthoek in, in cm: ")
[E4]= InputBox("Voer de breedte van de rechthoek in, in cm: ")
(QG6XE
Problemen oplossen met VBA – V.Cools
pagina 13
Maak in werkblad Rechthoeken een knop voor de
gebruiker en koppel de knop aan procedure
VraagMaten!
Voer het programma VraagMaten
uit: typ in het eerste invoervenster
het getal 20 en in het tweede het
getal 10.
%(*5,33(123((15,-7-(
Een groepering van opdrachten, geschreven in VBA, noemt men een SURFHGXUH . De opdrachten
kunnen ingetypt worden door de programmeur of ze kunnen automatisch gegenereerd worden door
de macrorecorder. In het laatste geval noemt men de procedure een PDFUR. Het resultaat van een
procedure is dat er iets uitgevoerd wordt, meer niet.
Een IXQFWLH bestaat eveneens uit opdrachten die geschreven zijn in VBA. Een functie geeft echter
altijd een antwoord, ze stuurt een resultaat terug!
Procedures en functies worden verzameld in modules. In één PRGXOH groepeert men de functies en
procedures die bij mekaar horen.
Geef de modulebladen een passende naam: selecteer de naam van de module in het linker
deelvenster, kies in menu “Beeld” de optie “Venster Eigenschappen” en wijzig de naam van de
module.
Module1
Module2
Module3
Module4
Module5
Module6
Module7
>
>
>
>
>
>
>
Valuta
Welkom
Tellen
Rechthoek
WaardenLezenEnSchrijven
Macros
FunctieRechthoeken
ZLM]LJQDDP
'RHOVWHOOLQJHQGHHO
1
Begrippen omschrijven en op een correcte manier gebruiken:
procedure, macro, functie, parameters of argumenten, module
2
Via procedures waarden in de cellen van een rekenblad schrijven
3
Via procedures celwaarden aflezen van een rekenblad
4
Celopmaak automatiseren met macro’s
5
Werkbladfuncties maken en gebruiken om het rekenwerk in een werkblad te automatiseren
6
Een procedure schrijven om de parameters of argumenten van een functie interactief in te
voeren.
7
Het onderscheid tussen procedures en functies formuleren
8
Modulebladen invoegen en benoemen
pagina 14
Problemen oplossen met VBA – V.Cools
&21752/(6758&785(1*(%58,.(1,19%$
Bij het oplossen van problemen zijn het de FRQWUROHVWUXFWXUHQ die het verloop van de oplossing
bepalen. We onderscheiden:
¾
sequentie of opeenvolging
¾
selectie of keuze
¾
iteratie of herhaling
ƒ
begrensde herhaling
ƒ
voorwaardelijke herhaling met aanvangsvoorwaarde
ƒ
voorwaardelijke herhaling met afbreekvoorwaarde
'(6(48(17,(
Tot nu toe hebben we in onze VBA-programma’s alleen opeenvolgende instructies gebruikt.
De processor voert elke opdracht één keer uit, in de volgorde waarin de opdrachten voorkomen.
Hierin herken je een VHTXHQWLH!
In het VWUXFWRJUDP wordt elke opdracht in een apart vak gezet.
Open in Excel de map Controle.xls
Zet de celwijzer in een willekeurige cel van Blad1 en voer het programma BerekenDubbel uit.
Het programma vraagt een getal. Typ een willekeurig getal in het invoervak en klik op OK.
Het getal wordt automatisch in de actieve cel geplaatst en in de rechts aangrenzende cel verschijnt
het dubbel van het getal.
Vraag de broncode van het programma op en probeer de programmaregels te begrijpen!
De broncode is de vertaling van onderstaand algoritme.
$OJRULWPH
--- BerekenDubbel ---
%URQFRGH
Sub BerekenDubbel ()
Vraag getal
StrGetal = InputBox("Voer een getal in:")
Zet getal in de actieve cel
ActiveCell.FormulaR1C1 = CDbl(StrGetal)
Schuif één cel naar rechts
CelRechts
Zet het dubbel van het getal in de actieve cel
ActiveCell.FormulaR1C1 = CDbl(StrGetal) * 2
End Sub
Problemen oplossen met VBA – V.Cools
pagina 15
Opmerking
De opdracht CelRechts verwijst naar een procedure die reeds ingevoerd werd in de werkmap in
moduleblad “Basisopdrachten”. Je vindt daar ook de procedures om de celwijzer naar boven, naar
beneden en naar links te verplaatsen. Als je een bestaande procedure oproept vanuit een andere
procedure, laat je de haakjes () weg.
Verwijder alle gegevens uit Blad1 met de knop die daarvoor voorzien is.
Deze knop is verbonden met de macro “WisBlad”. Je vindt de code terug in moduleblad “Macros”.
Ook dit algoritme is een sequentie!
6(/(&7,(
Veronderstel dat we alleen het dubbel van een positief getal willen berekenen. In dat geval is er een
verbonden aan de uitvoering van de berekening: DOV het getal positief is, GDQ wordt het
dubbel berekend. Noem het aangepaste programma BerekenDubbelSelectief.
YRRUZDDUGH
6WUXFWRJUDP
--- BerekenDubbelSelectief --Vraag getal
*HWDO!
Z
Y
Zet getal in de actieve cel
GEEN OPDRACHT
Schuif één cel naar rechts
Zet het dubbel van het getal in de actieve cel
6\QWD[
Kopieer de procedure BerekenDubbel in hetzelfde moduleblad, pas de naam en de inhoud van de
procedure als volgt aan:
Sub BerekenDubbelSelectief()
strGetal = InputBox("Voer een positief getal in:")
,I
CDbl(strGetal) >= 0 7KHQ
ActiveCell.FormulaR1C1 = CDbl(strGetal)
CelRechts
ActiveCell.FormulaR1C1 = CDbl(strGetal) * 2
(QG,I
End Sub
Test het programma.
Deze selectie is eenzijdig: er is geen opdracht voorzien als niet aan de voorwaarde voldaan is.
In VBA is de syntax van de eenzijdige selectie:
,I
voorwaarde 7KHQ
Opdracht(en)
(QG,I
pagina 16
Problemen oplossen met VBA – V.Cools
De programmeur kan ook één of meer opdrachten voorzien als niet aan de voorwaarde voldaan is. In
dat geval spreken we over een tweezijdige selectie. De syntax van de tweezijdige selectie is:
,I
voorwaarde 7KHQ
Opdracht(en)
(OVH
Opdracht(en)
(QG,I
Pas het programma BerekenDubbelSelectief aan zodat een foutmelding verschijnt wanneer de
gebruiker een negatief getal invoert.
Sub BerekenDubbelSelectief()
strGetal = InputBox("Voer een positief getal in:")
,I
CDbl(strGetal) >= 0 7KHQ
ActiveCell.FormulaR1C1 = CDbl(strGetal)
CelRechts
ActiveCell.FormulaR1C1 = CDbl(strGetal) * 2
(OVH
MsgBox "Foutieve invoer!"
(QG,I
End Sub
Test het programma.
9RRUZDDUGHQIRUPXOHUHQ
Een voorwaarde bevat vaak relationele operatoren
5HODWLRQHOHRSHUDWRU
%HWHNHQLV
>
<
=
<>
<=
>=
Groter dan
Kleiner dan
Gelijk aan
Verschillend van
Kleiner of gelijk aan
Groter of gelijk aan
Voorwaarden kunnen ook logische operatoren bevatten
/RJLVFKHRSHUDWRU
$1'
Bv. If Getal>0 AND Getal <3 Then …
25
Bv. If Getal>0 OR Getal <3 Then …
127
Bv. If NOT getal>0 Then …
Problemen oplossen met VBA – V.Cools
%HWHNHQLV
De opdracht wordt uitgevoerd als aan beide voorwaarden
voldaan is!
Hier zal de voorwaarde “Waar” zijn als het getal 1 of 2 wordt
ingevoerd. Elk ander getal voldoet niet aan de voorwaarde!
De opdracht wordt uitgevoerd als aan minstens één van de
twee voorwaarden voldaan is.
Hier zal de voorwaarde “Waar” zijn als gelijk welk getal
wordt ingevoerd. Elk getal is immers groter dan 0 OF
kleiner dan 3.
De opdracht wordt uitgevoerd als niet voldaan is aan de
opdracht.
Hier zal de voorwaarde “Waar” zijn als het getal 0 is of
kleiner dan 0.
pagina 17
Pas het programma BerekenDubbelSelectief aan: de gebruiker moet nu een positief getal invoeren
dat kleiner is dan 1000! Het getal moet dus aan twee voorwaarden voldoen:het getal moet groter of
gelijk aan 0 zijn en het moet kleiner zijn dan 1000. We combineren de twee voorwaarden met de
AND-operator!
Sub BerekenDubbelSelectief()
strGetal = InputBox("Voer een positief getal in, kleiner dan 1000:")
If &'EOVWU*HWDO! $QG&'EOVWU*HWDO Then
ActiveCell.FormulaR1C1 = CDbl(strGetal)
CelRechts
ActiveCell.FormulaR1C1 = CDbl(strGetal) * 2
Else
MsgBox "Foutieve invoer!"
End If
End Sub
%(*5(16'(+(5+$/,1*
Laat de gebruiker 5 willekeurige getallen invoeren. Het programma
berekent het dubbel van elk getal. Zowel de ingevoerde getallen als de
berekende waarden blijven in het werkblad staan!
Noem het programma BerekenDubbelBegrensd.
Om dit probleem op te lossen kunnen we gebruik maken van procedure BerekenDubbel. Deze
procedure moet 5 keer uitgevoerd worden. Daarbij moet de celwijzer telkens naar de volgende cel
worden verplaatst! Hiervoor beschikken we over de procedures CelOmlaag en CelLinks.
Als eenzelfde opdracht meermaals herhaald moet worden en het aantal herhalingen is op voorhand
gekend, dan passen we de EHJUHQVGHKHUKDOLQJ toe!
6WUXFWRJUDP
--- BerekenDubbelBegrensd --Herhaal 5 maal
BerekenDubbel
CelLinks
CelOmlaag
6\QWD[
Voer in moduleblad “Procedures” onderstaande programmacode in:
Sub BerekenDubbelBegrensd()
)RU
intTeller = 1 7R 5
BerekenDubbel
CelLinks
CelOmlaag
1H[W
intTeller
End Sub
pagina 18
Problemen oplossen met VBA – V.Cools
IntTeller is een variabele van het type integer (geheel getal). In deze variabele houdt de processor
een automatische teller bij. De naam van de variabele speelt geen rol.
Met de sleutelwoorden )RU …7R wordt bepaald wat de beginwaarde en eindwaarde van de teller is.
De beginwaarde moet kleiner zijn dan de eindwaarde!
Onder deze regel staan één of meer opdrachten. De opdrachtenreeks wordt afgesloten met het
sleutelwoord 1H[W. Wanneer de opdrachtenreeks uitgevoerd is, wordt de teller met 1 verhoogd.
Zolang de waarde van de teller kleiner of gelijk is aan de eindwaarde, wordt de opdrachtenreeks
opnieuw uitgevoerd.
Voer nu het programma ToonReeksGetallen uit in een leeg werkblad.
Bekijk de broncode van dit programma
Sub ToonReeksGetallen()
)RU intTeller = 0 7R 10 6WHS 2
ActiveCell.FormulaR1C1 = intTeller
CelOmlaag
1H[W intTeller
End Sub
Met de toevoeging van het sleutelwoord 6WHS, gevolgd door een getalwaarde, kan de programmeur
bepalen met hoeveel eenheden de teller verhoogd wordt!
Wijzig de broncode en voer het programma telkens opnieuw uit:
¾ For intTeller = -10 To 10 Step 5
¾ For intTeller = 0 To 100 Step 4
In VBA is de syntax van de begrensde herhaling:
)RU
teller = beginwaarde 7R eindwaarde 6WHS interval
Opdracht(en)
1H[W
teller
Problemen oplossen met VBA – V.Cools
pagina 19
9225:$$5'(/,-.(+(5+$/,1*0(7$$19$1*69225:$$5'(
Als de programmeur niet op voorhand weet hoeveel maal een opdracht uitgevoerd moet worden, en
dat is meestal het geval, dan moet hij beroep doen op een YRRUZDDUGHOLMNHKHUKDOLQJ. Het aantal
keren dat de opdracht wordt uitgevoerd, hangt af van een voorwaarde. Die voorwaarde kan aan het
begin van de herhaling gecontroleerd worden of ze kan gecontroleerd worden nadat de opdracht de
eerste keer is uitgevoerd. In het eerste geval spreken we van een voorwaardelijke herhaling met
DDQYDQJVYRRUZDDUGH, in het tweede geval spreken we van een voorwaardelijke herhaling met
DIEUHHNYRRUZDDUGH.
Voer het programma BerekenSom uit. Dit programma berekent de som van een reeks getallen die
door de gebruiker worden ingevoerd via invoervensters. De reeks wordt beëindigd met het getal 0.
De som wordt getoond in een uitvoervenster.
Aanvankelijk is de som nog gelijk aan 0. Van zodra de gebruiker een getal invoert dat verschilt van 0,
wordt dit getal bij de som geteld (som = som + getal) en er wordt een nieuw getal gevraagd. Deze
actie moet herhaald worden zolang het ingevoerd getal verschilt van 0. Van zodra het getal 0 wordt
ingetypt, wordt de som getoond op het scherm.
6WUXFWRJUDP
--- BerekenSom --Vraag getal
Zolang getal <> 0
Som = som + getal
Vraag volgend getal
Toon som
We hebben hier te maken met een YRRUZDDUGHOLMNHKHUKDOLQJPHWDDQYDQJVYRRUZDDUGH: de
opdracht(en) moet uitgevoerd worden ]RODQJ aan de voorwaarde voldaan is!
Als van bij het begin niet aan de voorwaarde voldaan is, worden de opdrachten geen enkele keer
uitgevoerd!
6\QWD[
Bekijk de broncode van het programma. Let op de sleutelwoorden!
Sub BerekenSom()
strGetal = InputBox("Voer het eerste getal in:")
:KLOH strGetal <> "0"
dblSom = dblSom + CDbl(strGetal)
strGetal = InputBox("Voer het volgende getal in. Typ 0 om te eindigen.")
:HQG
MsgBox ("De som van de getallen is " + CStr(dblSom))
End Sub
In VBA is de syntax van de voorwaardelijke herhaling met aanvangsvoorwaarde:
:KLOH
voorwaarde
Opdracht(en)
:HQG
pagina 20
Problemen oplossen met VBA – V.Cools
9225:$$5'(/,-.(+(5+$/,1*0(7$)%5((.9225:$$5'(
We kunnen de voorwaarde die het aantal herhalingen bepaalt in BerekenSom ook op het einde van
de opdrachtenreeks plaatsen. De opdrachten moeten dan herhaald worden WRWGDW aan de
voorwaarde voldaan is.
We maken gebruik van de YRRUZDDUGHOLMNHKHUKDOLQJPHWDIEUHHNYRRUZDDUGH!
Noem het programma BerekenSomAnders.
6WUXFWRJUDP
--- BerekenSomAnders --Vraag getal
Som = som + getal
Totdat getal = 0
Toon som
6\QWD[
Typ de broncode van het programma in moduleblad Procedures. Let op de sleutelwoorden!
Sub BerekenSomAnders()
'R
strGetal = InputBox("Voer een getal in. Typ 0 om te eindigen.")
dblSom = dblSom + CDbl(strGetal)
/RRS8QWLO strGetal = "0"
MsgBox ("De som van de getallen is " + CStr(dblSom))
End Sub
Test de twee programma’s om de som te berekenen.
Ga na wat er gebeurt als je als eerste getal een 0 invoert!
BerekenSom
BerekenSomAnders
Voorwaardelijke herhaling
met aanvangsvoorwaarde
Voorwaardelijke herhaling met
afbreekvoorwaarde
De invoer wordt eerst gecontroleerd en voldoet niet
aan de voorwaarde. Daardoor worden de opdrachten
die in de herhaling staan niet uitgevoerd.
De variabele dblSom krijgt geen waarde en kan dus
niet getoond worden!
De opdrachten die in de herhaling staan
worden minstens 1 keer uitgevoerd. Daardoor krijgt de variabele dblSom de waarde 0
en kan getoond worden in het uitvoervenster.
6ODGHZHUNPDSRSRQGHUGHQDDP11&21752/([OVZDDUELM11MHQXPPHULV
Problemen oplossen met VBA – V.Cools
pagina 21
'RHOVWHOOLQJHQGHHO
1
Controlestructuren
ƒ
ƒ
ƒ
ƒ
ƒ
Sequentie
Eenzijdige en tweezijdige selectie
Begrensde herhaling
Voorwaardelijke herhaling met
aanvangsvoorwaarde
Voorwaardelijke herhaling met
afbreekvoorwaarde
¾
¾
¾
¾
de betekenis omschrijven
voorstellen in een structogram
de syntax kennen
kunnen toepassen bij het oplossen van
problemen met VBA
2
Relationele en logische operatoren gebruiken bij het formuleren van voorwaarden
3
Herhalingen vergelijken
pagina 22
Problemen oplossen met VBA – V.Cools
67$36*(:,-=(9(5),-1,1*72(3$66(1,19%$
In een procedure kan je verwijzen naar een andere procedure. Deze techniek heb je reeds toegepast
in een aantal oefeningen.
Geef het resultaat van volgend programma weer: teken de afgelegde weg en de eindpositie van de
celwijzer. Het grijze vak geeft aan waar de celwijzer staat bij het begin.
6XE9HUSODDWV&XUVRU
For intTeller = 1 To 3
CelOmlaag
9HUZLM]LQJHQQDDUDQGHUHSURFHGXUHV
CelRechts
Next intTeller
End Sub
Op deze manier kunnen we stapsgewijze verfijning toepassen bij het programmeren.
Voer onderstaand stapsgewijs verfijnd programma in om de omtrek van een vierkant te berekenen.
6XE2PWUHN9LHUNDQW
'LWSURJUDPPDEHUHNHQWGHRPWUHNYDQHHQYLHUNDQW
ZDDUYDQGH]LMGHLQJHYRHUGZRUGWGRRUGHJHEUXLNHU
VraagZijde
BerekenOmtrek
ToonResultaat
(QG6XE
9HUILMQLQJHQYDQ2PWUHN9LHUNDQW
Sub VraagZijde()
strZijde = InputBox("Voer de lengte van de zijde in (in cm)!")
End Sub
Sub BerekenOmtrek()
dblOmtrek = CDbl(strZijde) * 4
End Sub
Sub ToonResultaat()
MsgBox "De omtrek is " + CStr(dblOmtrek) + " cm"
End Sub
Voer het programma uit. Het resultaat is niet erg bevredigend!
Problemen oplossen met VBA – V.Cools
pagina 23
Oorzaak? De variabelen zijn ORNDDO, dat wil zeggen dat ze alleen gekend zijn in de procedure waarin
ze voorkomen. Hun waarde wordt niet doorgegeven aan een andere procedure! Om ervoor te zorgen
dat het programma de variabelen in alle procedures kent, maken we ze JOREDDO! Globale variabelen
worden vooraf gedeclareerd met het commando:
'LP
NaamVanVariable
$V
TypeVariable
In het programma OmtrekVierkant gebruiken we 2 globale variabelen:
¾
de zijde die ingevoerd wordt in de inputbox is van type string
¾
de omtrek die we berekenen is van het type double
Vóór het programma typ je de declaratie van deze globale variabelen, voorzien van enige
commentaar:
*OREDOHYDULDEHOHQYRRU2PWUHN9LHUNDQW
Dim strZijde As String
Dim dblOmtrek As Double
Voer het programma opnieuw uit. Het probleem moet nu opgelost zijn.
'RHOVWHOOLQJHQGHHO
1
Betekenis omschrijven van lokale en globale variabelen
2
Globale variabelen kunnen declareren
3
Stapsgewijze verfijning toepassen bij het programmeren in VBA
pagina 24
Problemen oplossen met VBA – V.Cools