Algoritmen met Excel
Hoofdstuk 1: algoritmen en deelalgoritmen
1.1. Inleiding
Ondertussen heb je al heel wat mogelijkheden van de computer leren kennen. We hebben de computer gebruikt om teksten mee in te tikken, te verbeteren en de lay-out ervan te verzorgen. We hebben de computer ook ingeschakeld als hulpmiddel om berekeningen uit te voeren, als communicatiemiddel (e-mail) en als informatiebron via het Internet.
Een computer is nochtans een dom ding. Uit zichzelf weet of kan een computer niets. Alles moet hem van naaldje tot draadje uitgelegd worden. Voor alles wat de computer moet doen, moet een opdracht of instructie gegeven worden. Een reeks instructies wordt een programma genoemd.
Voor elke klus die we door de computer willen laten klaren, moeten we dus een geschikt programma hebben. In dit laatste deel gaan we eens bekijken hoe we een computer een aantal nieuwe taken kunnen leren.
Het is niet de bedoeling om hier een cursus programmeren te geven. De klemtoon wordt gelegd op hoe we een probleem opsplitsen in kleinere deelproblemen om zo tot een juiste oplossing te komen.
Hiervoor gebruiken we Visual Basic for Applications, kortweg VBA, een programmeertaal die kan gebruikt worden om bijvoorbeeld Excel een aantal nieuwe opdrachten te leren.
Om het niet nodeloos moeilijk te maken en om het “van buiten leren van programmacode” te vermijden heb ik voor jullie voor de meest voorkomende taken kant-en-klaar-instucties gemaakt.
Wil je thuis kunnen oefenen, dan zal je ervoor moeten zorgen dat je thuis ook die elementaire opdrachten op je computer zet. Anders verstaat Excel de opdrachten die wij hem geven niet!! Voor de andere programmacode zullen we macro’s opnemen.
Zoals de naam van dit cursusdeel zegt, gaat het dus niet zozeer om het aanleren van een
“programmeertaal” op zich, maar wel om het “ontwerpen van algoritmen”.
1.2. Het begrip algoritme
Tot op zekere hoogte kun je een computerprogramma vergelijken met een recept uit een kookboek, met een montagevoorschrift uit een bouwpakket, met de handleiding voor een videorecorder, ...
Telkens wordt er uitgelegd hoe je een niet-gekende taak moet uitvoeren.
Zo’n voorschrift dat je van je probleem (bijvoorbeeld “hoe stel ik mijn video in”) bij de oplossing van het probleem brengt wordt algemeen een algoritme genoemd.
Een algoritme is een voorschrift met al de handelingen die men moet verrichten om vanuit een gegeven beginsituatie een bepaald resultaat te bereiken.
Een computerprogramma beschrijft de handelingen die de computer moet verrichten om een bepaalde opdracht uit te voeren. Een computerprogramma is dus ook een algoritme.
De bedoeling van dit stuk cursus is dat je de algemene principes leert voor het opstellen van algoritmen. Dit is een heel belangrijke fase in het maken van een programma. Later kan je dan eventueel de stap zetten naar het leren van een volledige programmeertaal. Het echte programmeerwerk wordt hier tot een minimum beperkt.
Een algoritme is meestal te ingewikkeld om in één geheel te beschrijven. Daarom gaan we een probleem opsplitsen in deelproblemen. Om een algoritme op te bouwen maken we daarom gebruik van deelalgoritmen. Een deelalgoritme is eigenlijk niets anders dan een gewoon algoritme dat gebruikt wordt in een ander algoritme.
Er moet altijd iets (of iemand) zijn die het algoritme uitvoert. Anders heeft je algoritme geen zin. De uitvoerder van het algoritme wordt de processor genoemd.
Wat zijn het algoritme en de processor in de volgende gevallen ?
Probleem Algoritme Processor
Instellen van het uur en het alarm van een klokradio
de handleiding van de klokradio De persoon die de klokradio programmeert
Het bakken van een lekkere taart
het recept van de taart de bakker
Het spelen van een muziekstuk op een piano
het partituur de pianist
Het berekenen van het gemiddelde van een reeks getallen in Excel
de functie GEMIDDELDE in Excel
de computer
Het hangt er nu van af wie de processor is, of die een opdracht al dan niet begrijpt. Je kan je broer vragen om voor jou eens de CD, die je zonet van een vriend in bruikleen kreeg, over te nemen op een cassette. Voor je broer geeft dit waarschijnlijk weinig problemen. Voor hem is NeemCDop een concreet deelalgoritme . Vraag je hetzelfde aan je oma dan kan het gerust zijn dat je haar eerst goed zal moeten uitleggen hoe ze het juist moet doen. Voor haar is NeemCDop een abstract deelalgoritme .
Een deelalgoritme is een concreet deelalgoritme voor een processor als deze het zonder bijkomende uitleg kan uitvoeren. Het is een abstract deelalgoritme voor de processor als er nog bijkomende uitleg nodig is.
Als je een algoritme opstelt kan je gerust gebruik maken van abstracte deelalgoritmen. Het is zelfs aangewezen om dat te doen. Zo kan je een probleem opsplitsen in deelproblemen. Deze
deelproblemen moeten dan wel verder uitgewerkt worden. We zeggen dat het deelalgoritme nog verder verfijnd moet worden. Die uitwerking wordt een verfijning genoemd.
Bijvoorbeeld:
Als we algoritmen zullen maken en we willen aan de gebruiker een getal vragen, dan zullen we gebruik maken van de opdracht
LEES(“Geef een getal”)
Probeer je dit op je computer thuis, dan heb je heel veel kans dat je computer niets doet of een foutmelding geeft. Voor je computer is de opdracht LEES een abstracte opdracht.
Omdat dit toch een redelijk logische naam is voor die opdracht zullen wij toch de opdracht LEES gebruiken. Daarom werd in de oefeningen ervoor gezorgd dat de opdracht LEES verfijnd is.
Sub LEES(tekst_in_het_venster)
OpgevraagdGegeven = InputBox(tekst_in_het_venster, "Invoer van gegevens...") If OpgevraagdGegeven <> "" Then
ActiveCell.FormulaR1C1 = OpgevraagdGegeven End If
End Sub
Voor de computer is dit nu volledig duidelijk. Het is met andere woorden een concrete opdracht geworden. Voor jou is dit misschien alles behalve duidelijk, maar er is geen enkel probleem. Als jij de opdracht LEES geeft, wordt dit door Visual Basic toch begrepen, want er werd bijkomende uitleg gegeven.
Het is dan ook zo dat, als je thuis wil oefenen, je van alle basisbevelen die we zullen gebruiken, de verfijningen zal moeten hebben. Die kan je gerust op school vragen. Alle basisbevelen zijn ondergebracht in een sjabloon. Als je je oefeningen op dit sjabloon baseert worden de concrete deelalgoritmen uit deze cursus begrepen.
In het verdere verloop van deze cursus zal de klemtoon vooral liggen op het maken en het gebruiken van zo’n verfijningen.
1.3. Enkele voorbeelden van algoritmen
1.3.1 Het recept van een Sultana Cake
Benodigdheden: 125 g boter; 125 g suiker; pakje vanillesuiker; snuifje zout; 3 eieren; 200 g bloem vooraf gemengd met 1 koffielepel bakpoeder; 5 eetlepels zure room; snuifje kruidnagelpoeder;
snuifje kaneel; 500 g sultanarozijnen; paneermeel 1. klop de boter zo los en romig mogelijk
2. voeg er de suiker bij al kloppend 3. roer er nu de vanillesuiker onder 4. vervolgens het snuifje zout
5. één voor één de eieren telkens kloppend in het mengsel verwerken
6. afwisselend bloem en zure room - lepel per lepel - bij mengen en goed kloppen. Doe er wat kruidnagelpoeder en een snuifje kaneel bij
7. bestuif de rozijnen met bloem en meng ze in het deeg
8. de bakvorm met boter besmeren; dan met paneermeel bestrooien; verder losjes vullen met het deeg 9. in een voorverwarmde over (200 °C) op halve hoogte 50 minuten laten bakken
10. HERHAAL de controle op het einde door er een stokje dwarsdoor te steken TOTDAT er geen nat deeg meer aankleeft
11. Haal de cake uit de vorm en laat op een rooster snel afkoelen 12. Zet de oven af
1.3.2 De morgenstond van een leerling
Een algoritme wordt normaal niet met cijfertjes aangeduid, zoals in het vorig voorbeeld. Normaal wordt een algoritme in een of ander schema voorgesteld. We zullen dit jaar vooral gebruik maken van Nassi-Schneidermann-diagrammen, kortweg NS-diagrammen.
Hieronder vind je een algoritme dat beschrijft hoe je van een bepaalde beginsituatie (de wekker loop af) tot een eindresultaat (ga ik vandaag wel naar school ??) gaat. Dit algoritme is in zo’n NS- diagram voorgesteld.
-- Begin de morgenstond -- ZetDeWekkerAf
Kreun DraaiNogEven
StapUitBed NogDonker
ja neen SteekLichtAan
Koud
ja neen ZetVerwarming
Aan
GaJeWassen Ontbijt GromtDeHond
ja neen LaatHondBuiten
VandaagDST ja neen
ToonJeZiek
Moeder Vertrek Overtuigd NaarSchool
ja neen
TerugIn Vertrek
Bed NaarSchool
1.4 Wat je moet kennen en kunnen
• De begrippen algoritme, deelalgoritme en processor kunnen omschrijven
• Het verschil kunnen aangeven tussen een concreet en een abstract deelalgoritme
• Inzien dat een computerprogramma een bijzondere vorm is van een algoritme.
De computer vervult hierbij de rol van processor
• In een aantal concrete situaties uit het alledaagse leven kunnen aangeven wat het algoritme is en wie of wat de processor is
Hoofdstuk 2: methode van de stapsgewijze verfijning
2.1 Het gegevensverwerkend proces
Vorig jaar kwam het gegevensverwerkend proces al ruimschoots aan bod. Elk gegevens- verwerkend proces kan je opsplitsen in 3 grote fasen:
invoer verwerking uitvoer
(gegevens) (informatie)
Aangezien het nu juist de bedoeling wordt om algoritmen op te stellen die gegevens verwerken, zullen deze drie stappen in elk algoritme zeker moeten voorkomen.
2.2 Stappen voor het oplossen van een probleem
Om een probleem efficiënt op te lossen moet je een aantal fasen doorlopen. Bij eenvoudige problemen kan je misschien een of andere stap overlaten, maar toch maken we er een gewoonte van om bij elk probleem alle stappen te doorlopen.
2.2.1 Probleemdefinitie
In een eerste fase, de probleemdefinitie, ga je duidelijk na wat het probleem is en wat het resultaat is. Het is belangrijk om na te gaan welke gegevens je nodig hebt en waar ze vandaan (moeten) komen. Bij de informatie is het belangrijk om na te gaan welke resultaten er juist gevraagd worden en in welke vorm die moeten staan.
2.2.2 Probleemanalyse
Als je duidelijk weet wat de gegevens zijn en welke informatie je daaruit moet verkrijgen, kan je met het middelste blok van het gegevensverwerkend proces beginnen: de verwerking. Nu is het voor ingewikkelde problemen niet haalbaar om dit allemaal ineens te doen. Je doet er goed aan om een probleem op te splitsen in deelproblemen, die op hun beurt weer verder verfijnd kunnen worden.
Dit principe wordt een stapsgewijze verfijning genoemd.
2.2.3 Schema
Eens je weet in welke deelproblemen je je probleem zal opsplitsen, kan je beginnen nadenken welke basisstructuren (of controlestructuren) je best gebruikt. De verschillende controlestructuren (sequentie, selectie, iteratie) komen verder aan bod.
Om alles overzichtelijk te houden wordt gebruik gemaakt van een schema. Er bestaan verschillende soorten schema's. Wij beperken ons tot Nassi-Schneidermann-diagrammen, kortweg NS- diagrammen.
Een goed schema bevat alle logica van de oplossing, zonder dat die teveel aan een of andere programmeertaal vasthangt.
2.2.4 Programmeren
Nu begint datgene waar we al zo lang op wachten. Eens we definitief weten hoe we alles zullen oplossen kunnen we ons schema in een “echte” programmeertaal vertalen. Welke instructies daar juist voor gebruikt moeten worden hangt af van de programmeertaal die je gekozen hebt. We gebruiken Visual Basic for Applications, afgekort met VBA, om ons algoritme te testen. We zullen ons schema dan ook vertalen naar een pseudo-code die wat van VBA meeheeft.
Pseudo-code is eigenlijk een soort nepprogrammeertaal die vrij duidelijk is en weinig kennis rond de syntax vereist. Vandaar dat je het sjabloon Algoritmen.xlt nodig hebt om deze cursus te kunnen gebruiken. Ze vertalen een aantal “begrijpbare” opdrachten zoals DRUK, LEES, CELOMHOOG, SELECTEER, ... in VBA.
Beginnende programmeurs slaan de eerste drie fasen soms over en stoppen al hun energie in het programmeren en testen. Hierdoor gaat achteraf dikwijls veel tijd verloren. De meer ervaren programmeurs beginnen bij het begin.
Als je iets op een computerscherm wil toveren, moeten daar een hele hoop instructies, de broncode, voor gegeven worden. Bij sommige zaken, zoals programma’s in VBA, is die eenvoudig op te vragen. Bij andere programma’s, zoals uitvoerbare programma’s, werd die omgezet naar een doelprogramma: een programma dat op zichzelf kan werken. Het omzetten van een broncode naar een doelprogramma wordt compileren genoemd. Een programma in VBA moet niet gecompileerd worden. Het maakt gebruik van bijvoorbeeld Excel om te kunnen werken. In dit geval wordt Excel een interpreter genoemd: het interpreteert de code.
i
Een ander voorbeeld waar broncode gemakkelijk zichtbaar te maken is, is op Internet.Een Internetpagina wordt gemaakt met een bepaalde programmeertaal, die men HTML (HyperText Markup Language) noemt. Om Internetpagina’s te kunnen opvragen heb je een browser nodig zoals Internet Explorer of Netscape. In feite zijn die programma’s niets anders dan interpreters voor HTML. Een pagina komt niet binnen zoals je ze ziet.
Ze wordt ter plaatse op je computer opgebouwd aan de hand van de broncode.
Naast HTML, dat enkel gebruikt wordt voor de layout van de pagina’s op Internet, wordt ook soms gebruik gemaakt van JAVA-scripts. Ook JAVA-script is een programmeertaal die door je browser geïnterpreteerd kan worden. In deze cursus gaan we niet verder in op HTML en JAVA-scripts.
Je kan de broncode van een Internetpagina eenvoudig opvragen door met de rechtermuisknop te klikken op die pagina en in het snelmenu te kiezen voor Bron weergeven.
Broncode kan je uiteraard gewoon intikken. Dit vereist een grondige kennis van de programmeertaal. In de praktijk maakt men ook soms gebruik van programmageneratoren. Dit zijn hulpprogramma’s die in staat zijn om broncode voor jou te schrijven. Voor Internetpagina’s wordt vaak gebruik gemaakt van een “HTML-editor” zoals bijvoorbeeld FrontPage of Dreamweaver. Voor VBA zullen we de macrorecorder gebruiken als programmagenerator.
Hiermee kan heel wat van de broncode automatisch gemaakt worden. Het is wel goed om iets van
die code te begrijpen. Een programmagenerator zet er dikwijls veel overbodige zaken in, die je achteraf dan ook best verwijdert. Zo is een elementaire kennis van HTML wel vereist als je een homepagina op Internet wil zetten. Er is ook een elementaire kennis van VBA nodig om efficiënt met VBA te kunnen werken. Trouwens: niet alles is mogelijk met programmageneratoren.
Wij zullen in deze cursus de “moeilijke” code overlaten aan een programmagenerator. De eenvoudigere dingen kunnen we zelf. Het komt erop neer dat we een aantal opdrachten met de macrorecorder zullen opnemen. Deze kunnen we dan als verfijningen gebruiken in een groter geheel dat we zelf ontwerpen.
2.2.5 Testen
Het is heel belangrijk om je programma, eens het af is, goed te testen. Doet het in alle gevallen wat het moet doen? Als er iets fout loopt kan je nog de laatste verbeteringen aanbrengen. Dit zou eerder uitzondering dan regel moeten zijn.
2.2.6 Documenteren
Tot slot doe je er goed aan om in je programma wat commentaarlijnen op te nemen die achteraf duidelijk maken wat je met een bepaalde lijn bedoelt, waarvoor een deelalgoritme juist dient, wat je juist nodig hebt om een deelalgoritme te kunnen gebruiken, ...
Deze stap noemen we het documenteren. Een programma dat goed gedocumenteerd is, kan achteraf eenvoudiger aangepast worden.
2.3 Waaraan voldoet een goed algoritme
Elk goed algoritme voldoet aan een aantal voorwaarden.
• Bij elk algoritme is er invoer vereist
• Elk algoritme voorziet uitvoer in een eindig aantal stappen
• Elke stap moet eenduidig en ondubbelzinnig zijn
• Een algoritme moet zo algemeen mogelijk gemaakt worden. Dat wil zeggen dat je een en hetzelfde algoritme zodanig moet ontwerpen dat je het in verschillende situaties kan gebruiken.
Een programma om een n-de machtswortel van een getal te berekenen is maar goed als je het zowel voor vierkantswortels, als voor derdemachtswortels, als voor tiendemachtswortels, als voor ... kan gebruiken
• Zorg dat je algoritme juist en betrouwbaar is. En dit in alle denkbare situaties. Desnoods moet je een vangnet voorzien. Als het de helft van de keren niet werkt is je algoritme niets waard.
• Robuustheid: je algoritme mag niet van zijn pluimen verliezen bij vreemde situaties. In het algoritme voor het berekenen van de n-de machtswortels zal je er moeten op letten dat je geen wortel kan nemen uit een negatief getal, als de exponent even is. Je zal er ook moeten voor zorgen dan de wortelexponent alleen een natuurlijk getal kan zijn. Anders moet je programma dat “beleefd” opvangen, zonder dat het blokkeert.
2.4 De methode van de stapsgewijze verfijning
2.4.1 Het principe
Je kan een complex probleem maar efficiënt aanpakken als je het uiteenrafelt in kleinere, los van elkaar staande deelproblemen. Deze deelproblemen kan je dan nog verder opsplitsen enz. Dit principe noemt men de methode van de stapsgewijze verfijning. Je combineert al deze deelalgoritmen dan tot één geheel en je hebt je algoritme.
We beginnen steeds met een beschrijving in grote lijnen, zonder ons om de details te bekommeren.
We noemen dit de grofstructuur.
2.4.2 Voorbeeld
Je wil het 7 uur journaal van TV1 op video opnemen. Je bent niet thuis en vraagt je broer dit te doen. Die heeft dit nog nooit gedaan, dus leg je hem de zaak uit:
Dit wordt in het volgende NS-diagram voorgesteld:
-- Opnemen van 7 uur journaal -- Wacht tot vijf voor zeven
Zet de TV aan Zet de video aan Zet de video op TV1
Zet de band juist Wacht tot het nieuws begint
Neem op
Wacht tot het nieuws gedaan is Zet de video en de TV weer uit
Dit zou je als de grofstructuur kunnen aanzien. Het probleem “Opnemen van 7 uur jounaal” werd uiteengerafeld in een aantal deelproblemen. Een aantal van die deelproblemen zijn misschien concreet, zoals “Wacht tot vijf voor zeven”, maar sommige andere misschien niet. Zo kan je de opdracht “Zet de band juist” verder verfijnen met de volgende opdrachten:
-- Zet de band juist -- PLAATS de cassette
DRUK op “play”
Is de opgenomen film nog bezig
Ja Neen
SPOEL een beetje SPOEL een beetje
VERDER TERUG
TOTDAT film is op het einde
grofstructuur = diepte 0
eerste verfijning
= diepte 1
Op een gelijkaardige manier kan je ook nog de andere abstracte deelalgoritmen die in de grofstructuur voorkomen verder verfijnen. We zeggen dat dit opdrachten zijn die voorkomen op diepte 1. Het zijn verfijningen van opdrachten uit de grofstructuur.
Ook dit kan nog verder verfijnd worden. Zo zou je de opdracht “PLAATS de cassette”
bijvoorbeeld als volgt kunnen verfijnen:
-- Plaats de cassette -- DRUK op de knop “eject”
NEEM de vorige cassette uit de videorecorder LEG de cassette op de woonkamertafel NEEM cassette met etiket “vrije opname” van de
woonkamertafel
DUW de cassette met het ene etiket omhoog en het ander etiket naar je toe in de gleuf van de videorecorder
Deze opdrachten zijn een verfijning van een abstract deelalgoritme uit diepte 1. We zeggen dat dit deelalgoritme zich op diepte 2 bevindt. Hoe groter de diepte, hoe gedetailleerder de omschrijvingen, maar ook hoe kleiner het deel van het probleem dat aangepakt wordt.
2.4.3 Wat is daar nu het voordeel van?
Dit lijkt in het begin fantastisch ingewikkeld. Toch heeft deze methode van werken enkele serieuze voordelen.
Veronderstel dat je op reis wil gaan naar de Azurenkust. Dan zou je je reisroute kunnen voorbereiden en gedetailleerd opstellen.
• rijd tot het einde van de straat
• draai naar links
• volg de straat tot op het einde
• draai naar rechts
• rijd tot op het einde
• draai linksaf
• draai aan de derde verkeerslichten linksaf
• ....
• parkeer je wagen
• ga naar de receptie van het hotel.
Dit zou een mogelijkheid zijn. Maar wat als je één foutje hebt gemaakt en op één plaats links hebt gezet in plaats van rechts? Of wat als ergens op je reisweg er een wegomlegging is? Hoe zou je in zo’n geval weten waar het juist is misgelopen en wat je dan moet doen.
Het zou veel meer aangewezen zijn om eerst in heel grote lijnen de reisroute vast te leggen
• Rijd naar de E17
• Neem de E17 tot in Rijsel
• Rijd naar Reims
• Rijd naar Troyes
• Rijd naar Langres
• Rijd naar Lyon
• Volg de Autoroute du soleil tot aan Marseille
• Rijd tot slot door naar Nice
Dan kan je deze 8 algoritmen, die op zich misschien nog abstract zijn, afzonderlijk verder verfijnen.
Allereerst is deze methode van werken veel overzichtelijker dan alle opdrachten zo maar achter elkaar te plaatsen. Daarenboven is het eenvoudiger om op deze manier een betrouwbare oplossing te bedenken. Je kan je aandacht besteden aan de grote lijnen. Als dit op punt staat kan je deel per deel afzonderlijk afwerken. Ten derde is het met deze manier van werken vrij eenvoudig om fouten te lokaliseren.
Kortom: de voordelen van de stapsgewijze verfijning:
• betrouwbaarheid
• overzichtelijkheid en duidelijkheid
• mogelijkheid om fouten te lokaliseren 2.5 naamgeving van algoritmen
Elk deelalgoritme moet een naam hebben. Die namen moeten aan enkele voorwaarden voldoen. Je gebruikt best dezelfde namen in je NS-diagram als deze die je in je eigenlijk programma zal gebruiken.
We maken de volgende afspraken om ons programma duidelijk leesbaar te maken:
• De concrete deelalgoritmen komen volledig in hoofdletters.
Voorbeeld LEES
• De abstracte deelalgoritmen krijgen een naam die bestaat uit één woord.
• De naam van een abstract deelalgoritme wordt zoveel mogelijk in de gebiedende wijs geschreven, want het gaat tenslotte om een bevel voor de computer.
• De naam bestaat uit afwisselend hoofdletters en kleine letters . De eerste letter van elk woord is een hoofdletter, de andere letters zijn kleine letters. Zo kan je opdracht toch uit meerdere woorden bestaan en blijft alles leesbaar.
Voorbeeld: KleurAchtergrondRood
• De naam van je deelalgoritme moet logisch gekozen zijn. Aan de hand van de naam moet je al kunnen vermoeden wat het deelalgoritme gaat doen, zonder dat je zelf de verfijningen bekijkt.
2.6 Werken met macro’s
2.6.1 Wat zijn macro’s
Een macro is een reeks opeenvolgende opdrachten die een toepassingsprogramma zoals Excel onthoudt, zodat je dit in de toekomst automatisch kan laten uitvoeren. Macro’s worden gemaakt in VBA en zijn dus geschikt om te gebruiken in onze algoritmen. Op die manier moeten we namelijk niet veel code van buiten leren. We voeren de handelingen die we willen programmeren uit, en laten die registreren door de macrorecorder. De macrorecorder is een programmagenerator en schrijft er zelf de nodige broncode voor.
We kunnen de laatste stap van onze verfijningen opnemen met de macrorecorder. Willen we bijvoorbeeld de opdracht KleurAchtergrondRood maken, dan kunnen we dit door een macro op te nemen die de achtergrond van de cellen rood kleurt.
Een macro die je met de macrorecorder opgenomen hebt, is dus in feite niets anders dan een opeenvolging van instructies die hetzelfde doen als wat jij in Excel hebt uitgevoerd. Daarom wordt zo’n macro ook een opdrachtmacro genoemd.
2.6.2 Een macro opnemen
Om een opdrachtmacro te maken kies je in het menu Extra voor Macro opnemen. In het deelmenu kies je dan voor Nieuwe macro opnemen. Je kan het opnemen van macro’s een stuk eenvoudiger maken als je de werkbalk Visual Basic weergeeft. Kies daarvoor in het menu Beeld voor Werkbalken en vink Visual Basic aan. Je kan dan een nieuwe macro opnemen door op de knop met de bol te klikken.
Je kan deze werkbalk ook naar de bovenrand van je scherm slepen, zodat hij naast één van de bestaande knoppenbalken staat. Voor het invoeren van algoritmen is het aangewezen deze werkbalk zichtbaar te houden. Hij staat het minst in de weg als je hem naast bijvoorbeeld de opmaakwerkbalk zet.
Als je een macro opneemt krijg je een dialoogkader waar je de macronaam kan ingeven. Houd er wel rekening mee dat je enkel letters, cijfers en het onderlijningsteken ( _ ) mag gebruiken. Spaties zijn niet toegestaan. We gebruiken de naam die ons deelalgoritme moet krijgen. Houd je dus aan de afspraken voor de naamgeving van deelalgoritmen.
Vermijd in elk geval ook om een naam te gebruiken die dezelfde is als de bestandsnaam of de naam is van een van je werkbladen of grafiekbladen. In dat geval kan Excel in de problemen komen en wordt je macro wellicht niet goed uitgevoerd. Je mag ook geen naam gebruiken die al als functie bestaat binnen Excel (zoals bijvoorbeeld GEMIDDELDE).
Eventueel kan je een sneltoets definiëren, maar in de oefeningen voor algoritmen is dat nergens voor nodig. Aangezien je macro’s enkel binnen je oefening beschikbaar moeten zijn, kies je bij Macro opslaan in voor Deze werkmap. Normaal staat dit al zo.
Er verschijnt een werkbalk met 2 knoppen. De linkerknop (het blauwe vierkantje) dient om de opname te stoppen. Met de rechterknop kan je kiezen of de verwijzingen absoluut (knop uit) of relatief (knop ingedrukt) gebeuren.
Al de handelingen die je nu uitvoert, zullen door Excel geregistreerd worden en worden bij het oproepen van de macro letterlijk opnieuw uitgevoerd. Zorg dus dat je op voorhand goed weet wat je moet doen.
Heb je alle opdrachten die je wilde opnemen uitgevoerd, klik dan op de knop Opname stoppen die daarnet verschenen is. De knop Opname stoppen staat ook op de Visual Basic-werkbalk op de plaats waar eerst de knop Macro opnemen stond. Het maakt niet uit welke je gebruikt. De handelingen worden nu niet verder geregistreerd.
2.6.3 Een macro aan een knop verbinden.
Een macro is maar efficiënt als hij gemakkelijk opgeroepen kan worden. Doorgaans zal een macro opgeroepen worden in een algoritme, maar dat algoritme moet ook gestart kunnen worden. Dat gaat het gemakkelijkst door een knop op het blad te voorzien. Een knop gebruiken we enkel om de grofstructuur mee op te starten. Al de rest gebeurt in het algoritme zelf.
Om zo’n knop te plaatsen kies je in het menu Beeld voor Werkbalken. Daar klik je de werkbalk Formulieren aan. Je krijgt een werkbalk zoals hiernaast afgebeeld. Staat die in de weg dan kan je ze verslepen. Sleep je ze tot aan de rand van het werkblad dan wordt het een gewone knoppenbalk (geen zwevende knoppenbalk meer). Als je dat wenst kan je ze achteraf weer op het werkbald slepen zodat de knoppenbalk weer zwevend wordt.
Van deze werkbalk hebben we maar één knop nodig: (Knop). Klik op deze knop en je ziet dat je muiscursor in een fijn kruis verandert. Teken nu de knop op de gewenste plaats. Bij het loslaten van de muisknop krijg je een venster met de beschikbare macro’s.
Als je tijdens het tekenen van de knop de Alt-toets ingedrukt houdt, zal de knop perfect op de randen van een cel komen.
Kies de macro eruit die je met de gemaakte knop wil opstarten en klik op OK. Nu kan je nog de tekst in de knop veranderen, door de gewenste naam in te tikken en daarna buiten de knop te klikken. Door op ENTER te drukken neem je een nieuwe alinea binnen de knop. De tekst binnen deze knop is totaal onafhankelijk van de macro. Het is enkel een hulpmiddel naar de gebruiker toe.
Wil je achteraf de eigenschappen van de knop veranderen (ander lettertype, kleur, formaat, andere plaats, tekst of macro,...) dan volstaat het om de knop met de secundaire muisknop aan te klikken.
In het snelmenu kan je de eigenschappen veranderen, met de handvatten kan je de grootte veranderen en door in de knop te klikken kan je de tekst veranderen.
ONTHOUD: KNOP AANPASSEN = RECHTSKLIKKEN OP DE KNOP
:
Opdracht 1:• Begin een nieuwe algoritmenwerkmap. Kies daarvoor in het menu Bestand voor Nieuw... Kies hier het sjabloon Algoritmen.
• Vul het hoofd van de oefening in. Bij “Oefening” plaats je bladzijde en nummer van de oefening, met een korte omschrijving (hier: macro’s opnemen)
• Verander de naam van de tab door erop te dubbelklikken in Opdracht 1.
• Neem een macro op die de achtergrond van de actieve cel in het rood plaatst.
• Neem een tweede macro op die de achtergrond van de actieve cel weer transparant maakt
• Maak voor elke macro een knop op het blad. Kies zelf een geschikte tekst voor in de knoppen.
• Test de knoppen uit
• Bewaar de map met als naam Macro
2.6.4 Een macro verwijderen of verbeteren
Werkt een macro niet zoals je had gehoopt, dan zijn er twee mogelijkheden. Ofwel ga je hem manueel verbeteren. Gaat het om een eenvoudige opdrachtmacro dan zal je vlugger geholpen zijn om de verkeerde macro eerst te verwijderen. Neem de macro daarna opnieuw op.
Een macro verbeteren kan wel handig zijn om er “overtolloge broncode” uit te verwijderen. De programmagenerator plaatst dikwijls overbodige code in de macro, wat de uitvoering vertraagt.
Om een macro te verbeteren of te verwijderen kies je in het menu Extra voor Macro. Kies in het deelmenu voor . Je kan ook de knop met het driehoekje uit de Visual Basic - werkbalk gebruiken.
Je krijgt een venster met alle macro’s die beschikbaar zijn. Klik de gewenste macro aan en klik op Verwijderen om de macro te verwijderen of op de knop Bewerken om hem aan te passen.
De Visual Basic editor wordt opgestart. Je ziet nu de code van de macro. Een dosis gezond verstand is dikwijls voldoende om te begrijpen wat die code eigenlijk gaat doen. Je kan nu alle overtollige code verwijderen en de bestaande code eventueel aanpassen.
:
Opdracht 2• Maak blad 2 van Macro actief en noem dat blad Opdracht 2
• Vul het hoofd van het blad verder in (opdracht invullen)
• Neem een nieuwe macro op, en geef die de volgende naam: Geef3decimalen.
• Selecteer cel A5, tik hierin het getal 5 en druk op ENTER
• Selecteer opnieuw A5 en stel met de knop de getalopmaak in voor een weergave met 3 cijfers na de komma.
• Stop de opname ( )
• Bekijk de broncode. Klik daarvoor op de knop uit de Visual Basic -werkbalk.
• Je krijgt de onderstaande code. Schrijf bij elke lijn wat de betekenis van het resultaat van deze lijn is.
Sub Geef3decimalen() begin van de macro: de naam
'
' Geef3decimalen Macro commentaar bij deze macro (documenteren) ' De macro is opgenomen op 13/01/2002 door Tom Van Houdenhove.
'
'
Range("A5").Select de cel A5 selecteren
ActiveCell.FormulaR1C1 = "5" celinhoud wordt 5
Range("A5").Select de cel A5 selecteren
Selection.NumberFormat = "0.0" 1 cijfer na de komma instellen
Selection.NumberFormat = "0.00" 2 cijfers na de komma instellen
Selection.NumberFormat = "0.000" 3 cijfers na de komma instellen
End Sub einde van de macro
• Doorstreep hierboven de lijnen die overbodig zijn in deze macro. De bedoeling van de macro is om de actieve cel met 3 cijfers na de komma weer te geven. (zie rood)
• Verwijder de overbodige lijnen ook effectief uit de macro. Schakel over naar je werkblad en maak een knop om je macro op te starten
• Test je macro uit op verschillende cellen.
• Bewaar de werkmap opnieuw.
2.7 Modules, procedures en functies
Een macro is een stukje programmacode dat een aantal opdrachten uitvoert. Zo’n macro kan op zijn beurt in een groter geheel opgenomen worden. Het komt er op neer dat de macro een deelalgoritme kan zijn van een ander algoritme.
Een procedure is niets anders dan een deelalgoritme. In feite is een macro dus een procedure.
Maar een procedure kan ook ontstaan door zelf programmacode in te tikken. Een procedure is dus een groepering van een aantal opdrachten, die samen een deelalgoritme vormen. Het resultaat van een procedure is dat er iets wordt uitgevoerd, meer niet. Bijvoorbeeld “Kleur de achtergrond van de cel rood” of “Stel het aantal cijfers na de komma in op 3”.
Een functie is, net zoals een procedure, ook een deelalgoritme. Maar nu geeft dat deelalgoritme een “antwoord” of “uitkomst”. Een procedure “doet” alleen maar het een en het ander. We hebben al functies gebruikt in Excel . Zo was de functie GEMIDDELDE( ) een groepering van een aantal opdrachten om het gemiddelde uit een aantal getallen te berekenen. Dit algoritme geeft ons een antwoord: het gemiddelde. Omdat dit een antwoord geeft, noemen we het een functie. Ook een algoritme dat een antwoord geeft op de vraag “Hoeveel schijfruimte is er nog vrij?” zal een functie zijn, want het geeft een antwoord.
De verzamelnaam van procedures en functies wordt een module genoemd. Een module is dus in feite een groepering van functies en/of procedures.
Om functies en/of procedures te maken, gebruiken we de Visual Basic editor. Die hebben we daarnet eigenlijk ook al gebruikt om de procedure Geef3decimalen te bewerken. De vlotste methode om de editor te starten is met de knop uit de Visual Basic werkbalk.
In het linkerdeelvenster zie je de structuur van je werkmap (bij ) en zie je de modules (bij )
De module Basisbevelen bevat alle concrete deelalgoritmen die in deze cursus gebruikt worden.
Verder heb je ook nog de module Procedures en de module Functies. Deze modules zijn eigen aan het sjabloon Algoritmen. Deze twee laatste zijn nog leeg.
Om een module te openen moet je op die module dubbelklikken.
Als je een macro opneemt, komt die automatisch in een nieuwe module terecht, met als naam Module1. Bewaar je de werkmap en open je ze later opnieuw, dan zullen nieuwe macro’s in de module Module2 komen, enz.
Als je niet oplet, vind je op de duur je procedures niet meer terug. Daarom nemen we de gewoonte aan om alle procedures (eventueel na aanpassing) te verplaatsen naar de module Procedures (met knippen en plakken) en alle functies onmiddellijk in de module Functies te schrijven.
Het lege moduleblad ModuleX verwijderen we dan door er met de secundaire muisknop op te klikken en in het snelmenu te kiezen voor ModuleX verwijderen...
Op de volgende vraag antwoord je met nee.
:
Opdracht 3• Start vanuit de werkmap Macro de Visual Basic editor.
• Let er op dat de volledige module wordt weergegeven. Daarvoor moet de rechtse knop onderaan het rechterdeelvenster ingedrukt zijn.
• Wat staat er in de module Procedures? niets ...
• Wat staat er in de module Functies? niets...
• Wat staat er in de module ModuleX (met X een cijfer) ...
de macro’s die opgenomen werden...
• Verplaats de inhoud van ModuleX volledig naar Procedures. Controleer of alle macro’s nu in de module Procedures staan.
• Verwijder de module ModuleX.
• Bewaar de werkmap.
2.8 Eenvoudige algoritmen schrijven.
Eerst zorgen we ervoor dat we goed weten wat we allemaal zullen nodig hebben. Daarvoor zullen we ons probleem eerst uiteenrafelen in deelproblemen en onze oplossing voorstellen in een NS-diagram.
Hoe noemt men dit principe? de methode van de stapsgewijze verfijning...
Hieruit kunnen we al afleiden of er macro’s opgenomen moeten worden. Op die manier moeten we weinig code van buiten kennen.
We willen bijvoorbeeld een algoritme ontwerpen dat het getal in een cel op een rode achtergrond plaatst en dat getal weergeeft op 3 cijfers na de komma. We kunnen dit probleem opsplitsen in twee deelproblemen:
-- StelWeergaveIn --
KleurAchtergrondRood Geef3decimalen
De oplossing die hier gegeven werd, staat in een NS-diagram. Bovenaan staat de titel (tussen streepjes, in een afwijkend kleur of op een gekleurde achtergrond). Deze titel is eigenlijk de naam van ons algoritme. Beide deelalgoritmen zijn abstract, maar kunnen met de macrorecorder opgenomen worden.
Nu moet dit gecodeerd worden. Daarvoor starten we de Visual Basic editor. In het moduleblad Procedures kunnen we onder de bestaande procedures een nieuwe procedure maken. Dat kan door ze gewoon in te typen.
Een procedure begint altijd met Sub gevolgd door de naam van het algoritme en twee haakjes. Ze eindigt altijd met End Sub. Alle code komt hier tussen.
De beide deelalgoritmen moeten eerst opgenomen worden met de macrorecorder. Je kan dan je programma schrijven door tussen de sleutelwoorden Sub en End Sub de naam van de deelalgoritmen (zonder de haakjes) over te nemen.
Om alles overzichtelijk te houden, laat je de opdrachten een beetje inspringen door op de TAB-toets te drukken.
In dit voorbeeld wordt het dus:
Sub StelWeergaveIn()
KleurAchtergrondRood ...
Geef3decimalen ...
End Sub
:
Opdracht 4• Keer terug naar de Visual Basic editor. Als die al opgestart was kan dit met de knop op de taakbalk. Plaats de cursor op de eerste lijn na de laatste procedure.
• Tik Sub StelWeergaveIn( ) en druk op ENTER. Blader even met je
pijltjestoetsen omlaag. Wat stel je vast? End Sub werd automatisch toegevoegd ...
• Tik de twee opdrachten op hun plaats
• schakel via de taakbalk weer over naar Excel
• Verander de naam van Blad3 in Opdracht 4. Vul het hoofd van dt blad verder aan.
• Plaats in dat blad een knop die je algoritme start.
• Plaats enkele getallen in willekeurige cellen en test je programma uit.
• Bewaar de werkmap en sluit ze af.
2.9 Oefeningen
2.9.1 Opnemen en aanpassen van macro’s
a) Begin een nieuwe algoritmenwerkmap. Noem die Kleuren.
Maak een werkblad met 6 knoppen. De eerste knop beslaat K6:K7, de tweede K9:K10, de derde K12:K13, ... De randen van deze knoppen moeten perfect op de randen van de cellen liggen. Hoe doe je dat? door de Alt-toets ingedrukt te houden...
Ze hebben als opschrift respectievelijk zwart, grijs, blauw, rood, geel, groen. Als je op zo’n knop klikt moet de celinhoud van de actieve cel in dat kleur verschijnen. Plaats alle opgenomen macro’s achteraf in de module Procedures en documenteer je procedure . Doe dit ook telkens voor de volgende opdrachten.
Bewaar de werkmap, test uit en sluit af.
b) Begin een nieuwe algoritmenwerkmap. Noem die Kolombreedte.
Maak een knop die de kolombreedte van de actieve cel zodanig aanpast dat het langste gegeven uit deze kolom er net in past.
Wat stel je vast als je je macro test in een andere kolom? ...
de macro werkt enkel in de oorspronkelijke kolom waar de macro mee werd opgenomen...
Bekijk de broncode van de macro. Hoe komt dat? ...
omdat in de broncode naar de kolom wordt verwezen...
Is dit bijgevolg een goed deelalgoritme? Verklaar. neen. Een goed deelalgoritme is...
algemeen. Dit deelalgoritme werkt maar voor 1 kolom en is dus niet algemeen...
Bewaar de werkmap, test uit en sluit af.
c) Begin een nieuwe algoritmenwerkmap. Noem die Kolombreedte.
Maak een werkblad met 10 knoppen, geschikt zoals de cijfers op een telefoontoestel. In elke knop staat een cijfer (van 0 tot en met 9). Klik je op knop 1 dan wordt de inhoud van de actieve cel weergegeven met 1 cijfer na de komma. Klik je op knop 5 dan zijn dat 5 cijfers na de komma, ...
Bewaar de werkmap, test uit en sluit af.
2.9.2 Eenvoudige algoritmen schrijven
Begin een nieuw algoritmenblad en noem het Slotoefening Hoofdstuk 2.
Stel een algoritme op dat celinhoud in blauwe cijfers, in het vet plaatst. De achtergrond van de cel moet in het lichtgeel komen. De cel wordt met een dubbele rand omkaderd. Het getal wordt zonder cijfers na de komma getoond, maar de cijfers worden wel per 3 gegroepeerd, zoals de BIN-normen het verlangen (vb. 1 236 741 658). Zorg ervoor dat de kolombreedte aangepast wordt aan het langste gegeven in die kolom. Werk met de stapsgewijze verfijning en stel eerst een NS-diagram van je grofstructuur op. Probeer gebruik te maken van reeds bestaande deelalgoritmen. Noem je algoritme Grofstructuur. Zorg voor een knop die je algoritme kan opstarten en plaats er een passende tekst in. Plaats alle deelalgoritmen in de module Procedures en documenteer je algoritme.
G Tips bij deze opgave:
• Neem voor elk deelalgoritme een macro op. Bekijk de inhoud van de macro’s en verwijder de overtollige lijnen.
• De macro voor de kolombreedte werkte alleen voor 1 kolom, namelijk deze die gebruikt werd tijdens het opnemen. De broncode bevat de volgende lijn:
Columns("G:G").EntireColumn.AutoFit Met wat gezond verstand kan je deze lijn ontleden: van de kolom G
(Columns("G:G")) moet de volledige kolom (EntireColumn) een gepaste breedte krijgen (AutoFit).
Het is het eerste deel van die lijn dat ervoor zorgt dat enkel kolom G aangepast kan worden (dit kan ook een andere kolomletter zijn). Daardoor is ons
deelalgoritme dus niet algemeen. Pas de macro aan zodat de macro wel
algemeen wordt. In de plaats van de kolom G best passend te maken zullen we de kolom van de actieve cel (= van de selectie) best passend maken. Pas daarom deze lijn aan tot
Selection.EntireColumn.AutoFit
De betekenis wordt nu: van de geselecteerde cel (Selection) moet de volledige kolom (EntireColumn) een gepaste breedte krijgen (AutoFit).
Zo’n aanpassingen moet je niet uit jezelf kunnen maken. Als die nodig zijn, dan worden ze gegeven.
2.10 Probleem bij het openen van bestanden die macro’s bevatten
Als je een document dat macro’s bevat opent, zal Excel bij het openen ervan volgende vraag stellen:
Aangezien je met macro’s kan programmeren, kunnen er ook virussen mee gemaakt worden. Let op met Exceldocumenten met macro’s die je niet zelf gemaakt hebt.
Klik op de knop Macro’s inschakelen. Aangezien we enkel met eigen geschreven macro’s werken is er geen gevaar op macrovirussen.
Wil je deze melding in het vervolg niet meer krijgen, open dan Excel en klik in de Visual Basic werkbalk de knop .
Kies in het dialoogvenster Beveiliging voor Laag.
Let op met deze instelling!! Je wordt niet meer gewaarschuwd als er macro’s in het document zitten.
Doe dit enkel als je een degelijke en recente virusscanner staan hebt.
2.11 Wat je moet kennen en kunnen
• De stappen kunnen geven die nodig zijn voor het efficiënt oplossen van een probleem
• De stappen nodig voor het oplossen van een probleem kunnen uitleggen
• De stappen nodig voor het oplossen van een probleem kunnen gebruiken bij een gegeven probleem
• De begrippen broncode en compileren kunnen uitleggen
• Inzien dat Excel fungeert als interpreter voor VBA en dat er dus niet gecompileerd moet worden
• Weten wat een programmagenerator is en enkele voorbeelden kunnen noemen
• De eisen kunnen geven waaraan een goed algoritme moet voldoen
• De begrippen grofstructuur en verfijning kunnen uitleggen
• De voordelen van de stapsgewijze verfijning kunnen geven
• Een probleem kunnen opdelen in deelproblemen en dit kunnen voorstellen in een Nassi-Schneidermann-diagram (NS-diagram)
• Een macro kunnen opnemen
• Een knop kunnen maken om er een macro mee te starten
• Een knop kunnen aanpassen (andere macro aan koppelen, tekst in de knop aanpassen, ...)
• Een (verkeerde) macro kunnen verwijderen
• Een macro kunnen aanpassen (overtollige lijnen verwijderen, broncode aanpassen)
• Het verschil tussen functies, procedures en modules kunnen aangeven
• Een opgenomen macro naar de module Procedures kunnen verplaatsen
• Een module kunnen verwijderen
• Een algoritme kunnen invoeren, gebaseerd op de methode van de stapsgewijze verfijning, waar de verfijningen bestaan uit opgenomen macro’s.
Hoofdstuk 3: de sequentie en de begrensde herhaling
3.1 De sequentie
Algoritmen worden opgebouwd met een aantal controlestructuren. Dit zijn hulpmiddelen om onze oplossing te beschrijven. Het woord “controle” wordt hier gebruikt in de betekenis van “besturen”, niet in de betekenis van “nagaan of iets in orde is”.
Een eerste controlestructuur die we ter beschikking hebben is de sequentie.
Een sequentie of opeenvolging is een reeks opdrachten die elk éénmaal, in een opgegeven volgorde worden uitgevoerd.
Een sequentie bestaat bijgevolg altijd minstens uit 2 bevelen.
We leren nu onze algoritmen in een overzichtelijke vorm te noteren. Dat gebeurt met een Nassi-Schneidermann-diagram. In een sequentie bestaat zo’n NS-diagram uit een aantal onder elkaar getekende rechthoeken, die allen even breed zijn. Elke rechthoek bevat één opdracht.
Bovenaan wordt de naam van het algoritme genoteerd. Om duidelijk te maken dat het de naam is van een algoritme en geen opdracht, wordt deze tussen streepjes gezet en wordt die op een gekleurde achtergrond geplaatst. In je schriftelijke voorbereiding van de oefeningen kan dit eenvoudig met bijvoorbeeld een fluostift, of door de rechthoek met de titel te kleuren met een potlood.
Voorbeeld:
We maken een algoritme dat het dubbel van een gevraagd getal berekent en afdrukt. Dit is een elementair voorbeeld van een gegevensverwerkend proces.
Deze stappen moeten hoe dan ook terug te vinden zijn in ons algoritme. Aangezien we onze algoritmen met een werkblad oplossen zullen we bij het opstellen van ons algoritme hiermee rekening moeten houden.
Het algoritme zou als volgt kunnen zijn:
-- BerekenDubbel --
SELECTEER cel E8 LEES getal x ← getal in de cel
dubbel ← 2x Zak6Cellen DRUK dubbel
invoer verwerking uitvoer
Met deze sequentie wordt in de cel E8 een getal gevraagd en het dubbel komt zes cellen daaronder terecht.
Wat zal er gebeuren als we er het volgende van maken? Er werden net dezelfde opdrachten gebruikt, maar de volgorde werd licht veranderd.
-- BerekenDubbel --
LEES getal SELECTEER cel E8
x ← getal in de cel dubbel ← 2x DRUK dubbel
Zak6Cellen Wat loopt er fout?
het ingelezen getal komt in de actieve cel terecht, niet noodzakelijk in E8...
en het dubbel wordt berekend met de waarde uit E8, die overschreven wordt...
Waaraan is dit te wijten?
omdat de volgorde in een sequentie van belang is ...
3.2 De basisbevelen
Om algoritmen te kunnen opstellen moeten we een aantal instructies kennen. Hier wordt een onderscheid gemaakt tussen abstracte en concrete deelalgoritmen.
Wat was het onderscheid ?
• concreet deelalgoritme: wordt begrepen door de processor en vereist dus geen ...
bijkomende uitleg...
• abstract deelalgoritme: wordt niet begrepen door de processor en vereist dus wel ...
nog bijkomende uitleg...
Om het “programmeren” niet nodeloos moeilijk te maken, werden een aantal opdrachten in het sjabloon Algoritmen reeds verfijnd. We beschouwen ze als concrete deelalgoritmen. Het voordeel hiervan is dat je geen echte programmacode zal moeten van buiten leren en dat je op deze manier de kans op syntaxfouten een heel stuk verkleint.
Met syntaxfouten worden fouten tegen de syntax bedoeld, met andere woorden: fouten tegen de juiste schrijfwijze van de broncode.
Bovendien krijg je op deze manier verkrijg een aantal bevelen die duidelijk herkenbaar zijn en die een minimale kennis van de syntax vereisen. Het zijn bevelen voor invoer en uitvoer, en bevelen voor het verplaatsen van de celcursor.
We spreken af dat deze concrete deelalgoritmen steeds met hoofdletters genoteerd worden.
Als je aan het programmeren bent zal VBA ze trouwens automatisch in hoofdletters omzetten.
Gebruik deze namen dan ook nooit voor eigen abstracte deelalgoritmen. In dit laatste geval zou de processor (in dit geval Excel) niet weten welk deelalgoritme nu bedoeld wordt.
Je vindt deze basisbevelen ook terug op de kaart met concrete deelalgoritmen. Houd deze best in de buurt als je oefeningen oplost.
Bevelen voor invoer en uitvoer
Concreet deelalgoritme Gebruik van dit deelalgoritme LEES ("tekst die afgedrukt moet worden") Er wordt een invoervenster weergegeven met
daarin de tekst die tussen de haakjes voorkomt.
Als je op Annuleren klikt blijft de celinhoud ongewijzigd. Het resultaat van deze LEES- opdracht komt in de actieve cel.
DRUK ("tekst die afgedrukt moet worden") of
DRUK(variabele)
De tekst (of de waarde van de variabele) die tussen de haakjes voorkomt wordt in de actieve cel geplaatst.
TEKSTINCEL voorbeeld: naam = TEKSTINCEL
Aan de variabele naam wordt de inhoud van de actieve cel toegekend. Het resultaat is een alfanumeriek gegeven.
GETALINCEL voorbeeld: aantal = GETALINCEL
Aan de variabele aantal wordt de inhoud van de actieve cel toegekend. Het resultaat is een numeriek gegeven.
Bevelen voor het verplaatsen van de celcursor
Concreet deelalgoritme Gebruik van dit deelalgoritme SELECTEER(celverwijzing) voorbeeld: SELECTEER("A1")
De cel of het bereik dat als parameter wordt opgegeven wordt geselecteerd. De celver- wijzing moet tussen aanhalingstekens staan.
CELOMHOOG Plaatst de celcursor 1 cel hoger
CELOMLAAG Plaatst de celcursor 1 cel lager
CELLINKS Plaatst de celcursor 1 cel naar links
CELRECHTS Plaatst de celcursor 1 cel naar rechts
Bevel voor het wissen van cellen
Concreet deelalgoritme Gebruik van dit deelalgoritme WIS(celbereik) voorbeeld: WIS("A1:B10")
De cellen A1:B10 worden leeggemaakt Het celbereik moet tussen aanhalingstekens staan.
Afspraken :
• tekst moet altijd tussen aanhalingstekens staan
• een celverwijzing moet ook altijd tussen aanhalingstekens staan
Het algoritme dat we daarnet aan de hand van een NS-diagram ontworpen hebben kunnen we nu
“vertalen” naar een programma. De broncode van ons programma wordt:
Sub BerekenDubbel()
SELECTEER ("E8")...
LEES ("Van welk getal wil je het dubbel berekenen?") x = GETALINCEL...
dubbel = 2 * x...
Zak6Cellen...
DRUK (dubbel)...
End Sub
Op één opdracht na is dit algoritme nu volledig bepaald. Enkel de abstracte opdracht Zak6Cellen moet nog verfijnd worden.
:
Opdracht 5• Open de werkmap Sequentie
• Ga naar de VBA editor en open het moduleblad Procedures
• Tik de broncode voor de grofstructuur en voor de verfijning in
-- Zak6Cellen --
Sub Zak6Cellen()...
...
...
...
...
...
End Sub
NS-diagram Broncode
• Test het programma uit
• Wat gebeurt er als je in de plaats van een getal tekst invoert? Je krijgt een foutmelding...
...
• Klik op de knop Stop. Probeer je programma opnieuw, maar nu weer met een getal.
• Bewaar de werkmap 3.3 Gebruik van variabelen
Om berekeningen te kunnen maken, moeten we variabelen gebruiken. In det voorbeeld daarnet werden al variabelen gebruikt.
Een variabele is in feite niets anders dan een naam waar we een waarde aan toekennen. Die naam kan bestaan uit één enkele letter, maar kan ook uit meerdere letters en/of cijfers bestaan. Je moet wel met een letter beginnen.
Om de oppervalkte van een cirkel te berekenen heb je de straal en π nodig. De straal kan aan de gebruiker gevraagd worden, dus is het aangewezen om hier een variabele voor te gebruiken.
Wanneer je de variabele straal de waarde 3 geeft schrijf je: straal krijgt als waarde 3.
In een NS-diagram wordt een pijltje gebruikt om aan een variabele een waarde toe te kennen.
Voorbeelden:
straal ← 3 straal krijgt als waarde 3 pi ← 3,14159265 pi krijgt als waarde 3,14159265
oppervlakte ← pi x straal² oppervlakte krijgt als waarde het resultaat van de berekening pi x straal², dus 3,14159265 x 3²
school ← “Spes Nostra” school krijgt als waarde Spes Nostra. Tekst moet altijd tussen aanhalingstekens staan
Opmerking:
In de broncode van VBA wordt een waarde aan een variabele toegekend met een =-teken en niet met een pijltje. Toch gebruikt men dit niet in een NS-diagram, omdat niet alle programmeertalen hiervoor een =-teken gebruiken.
Door met variabelen te werken wordt ons programma algemeen bruikbaar. Met de instructie LEES kunnen we een gegeven opvragen en de waarde ervan toekennen aan een variabele. Hiermee kunnen we dan verder werken binnen ons programma.
Welke variabelen werden gebruikt in ons programma BerekenDubbel? x en dubbel...
3.4 Werken met functies
3.4.1 Functies ontwerpen
Een functie is, net zoals een procedure, een stukje programma dat op zichzelf gebruikt kan worden.
We hebben al gebruik gemaakt van procedures.
• de macro’s die we opnamen waren procedures: het waren stukjes programma die een deel van het probleem voor hun rekening namen. Bijvoorbeeld KleurAchtergrondRood.
• binnen de grofstructuur maken we gebruik van een aantal deelalgoritmen. Zo’n deelalgoritme was ook een voorbeeld van een procedure. Bijvoorbeeld: Zak6Cellen.
Een procedure is dus niets anders dan een programma dat binnen een ander programma gebruikt kan worden. Een procedure wordt ook soms een subroutine genoemd, vandaar de sleutelwoorden Sub en End Sub in het begin en op het einde van een procedure.
Een funcite is dat in feite ook. Tot op zekere hoogte zijn een functie en een procedure dus aan elkaar gelijk. Het essentiële verschil tussen de twee zit hem in het resultaat. Een procedure doet iets, terwijl een functie een antwoord op een vraag geeft.
Zijn de volgende deelalgoritmen procedures of functies? Verklaar.
TekenKader
Procedure. Er wordt gewoon iets uitgevoerd, zonder dat er een antwoord gegeven wordt
BepaalLeeftijd
Functie. Als resultaat van dit deelalgoritme krijg je de leeftijd, dus een antwoord.
CentreerTitel
Procedure. De titel wordt gecentreerd, maar er wordt geen antwoord gegeven
GaNaarBegin
Procedure. Er wordt naar het begin gegaan, maar er wordt geen antwoord gegeven
TelOp
Functie. Er worden getallen bij elkaar opgeteld en het resultaat wordt gegeven
BepaalVrijeSchijfruimte
Functie. Als antwoord krijg je de beschikbare schijfruimte
Het principe om een functie te maken is hetzelfde als om een procedure te maken. Er moet enkel nog gezorgd worden dat het antwoord gegeven wordt. Daarvoor wordt in die functie het antwoord aan een variabele toegekend die dezelfde naam heeft als de functie zelf.
Daarbij worden de nodige gegevens om met deze functie te kunnen werken tussen haakjes geplaatst bij de titel.
Voorbeeld: we willen de n-de machtswortel van een geral berekenen, maar daar bestaat binnen Excel geen functie voor. Daarom maken we er zelf een. De gegevens die we daarvoor nodig hebben zijn de wortelexponent en het grondtal.
De functie zelf bestaat nu maar uit één stap meer, namelijk de berekening van de wortel. Uiteraard kan een functie, net als een procedure, ook uit een sequentie bestaan.
-- nWortel (exponent, grondtal) --
nWortel ← grondtal^(1/exponent)
Het resultaat van nWortel(4, 16) zal dus de vierdemachtswortel uit 16 zijn, of 2.
Om een functie te programmeren gebruiken we hetzelfde principe als bij een procedure, maar we plaatsen ze tussen de sleutelwoorden Function en End Function.
Function nWortel(exponent, grondtal) nWortel = grondtal ^ (1 / exponent) End Function
Een functie kan geprogrammeerd worden binnen een moduleblad. In het sjabloon Algoritmen.xlt werd echter een afzonderlijk moduleblad Functies voorzien. We nemen de gewoonte aan om alle functies hier in te programmeren.
Een functie moet altijd een variabele bevatten met dezelfde naam als de functie. Het is via deze variabele dat het antwoord doorgegeven wordt!!
Is de concrete opdracht LEES een procedure of een functie? Verklaar. een functie...
Het resultaat van de instructie LEES geeft een antwoord op wat je gevraagd hebt...
Dat wil zeggen dat we het algoritme BerekenDubbel in feite kunnen vereenvoudigen.
-- BerekenDubbel --
SELECTEER cel E8 x ← LEES getal
dubbel ← 2x Zak6Cellen DRUK dubbel
Sub BerekenDubbel()
SELECTEER ("E8")...
x = LEES ("Van welk getal wil je het dubbel berekenen?") ...
dubbel = 2 * x...
Zak6Cellen...
DRUK (dubbel)...
End Sub
Afspraak:
In een NS-diagram zetten we vanaf nu niet meer
...
...x ← LEES getal maar kortweg
...
...LEES x
De essentie is dat we een waarde opvragen voor de variabele x. Welke tekst daarvoor gebruikt wordt is niet zo belangrijk, dus dit moet niet in het NS-diagram komen. In de broncode van VBA zal het wel een toekenning worden.
3.4.2 Een zelfgemaakte functie gebruiken in je werkblad
Het principe is net hetzelfde als het werken met een functie uit Excel. Je begint de lijn met een gelijkheidsteken, tikt dan de naam van je functie, en geeft de argumenten op door middel van celverwijzingen. De argumenten worden hier ook gescheiden door een puntkomma, niet door een komma zoals bij het programmeren in VBA .
A B C
1 wortelexponent grondtal wortel
2 4 16 =nwortel(A2;B2)
3 3 8 =nwortel(A3;B3)
4 2 81 =nwortel(A4;B4)
:
Opdracht 6• Open de werkmap Functies
• Programmeer de functie voor het berekenen van de n-de machtswortel
• Gebruik deze functie om in het blad Functie in een werkblad de n-de machtswortel van de getallen in de eerste (gele) tabel te berekenen.
• Maak de functies dubbel, helft en kwadraat en gebruik ze om de groene tabel verder aan te vullen.
• Maak de functies OppCirkel en OmtrCirkel. Bereken hiermee de oppervlakte en de omtrek van een cirkel als de straal gegeven is. Stel eerst het NS-diagram op. Gebruik een variabele pi (π=3,14159265).
-- OppCirkel( r ) --
Pi ← 3,14159265 OppCirkel ← r^2 * Pi
-- OmtrCirkel( r ) --
Pi ← 3,14159265 OmtrCirkel ← 2 * Pi * r
• Gebruik een komma als decimaal teken. Wat stel je vast? je krijgt een foutmelding...
...
• Verander de komma in een punt. Tegen de gewoonte in wordt in VBA een punt gebruikt als decimaal teken bij getallen. De komma dient als scheiding tussen argumenten van een functie.
• Gebruik de functies in de blauwe tabel en voer ze door voor de volledige tabel (vulgreep gebruiken)
• Bewaar je werkmap
3.4.3 Functies gebruiken in een procedure
Functies kunnen ook als deelalgoritme gebruikt worden in een procedure. Daarvoor moet je het resultaat van deze functie toekennen aan een variabele.
Bijvoorbeeld: oppervlakte←OppCirkel(straal)
Stel dat we een algoritme moeten opstellen om de oppervlakte en de omtrek van een cirkel te berekenen. Uiteraard is er invoer en uitvoer nodig, dus zullen we in onze grofstructuur zeker de opdrachten LEES en DRUK nodig hebben. Maar voor de berekening van de oppervlakte en de omtrek kunnen we gebruik maken van de gemaakte functies.
-- OppervlakteEnOmtrek --
SELECTEER juiste cel LEES straal
oppervlakte ←← OppCirkel(straal) omtrek ←← OmtrCirkel(straal)
CELOMLAAG DRUK oppervlakte
DRUK omtrek
De verfijningen die in deze grofstructuur nodig zijn, zijn de gebruikte functies, maar deze werden al gemaakt.
De broncode wordt:
Sub OppervlakteEnOmtrek() SELECTEER ("C7")
straal = LEES ("Wat is de straal van de cirkel?") oppervlakte = OppCirkel(straal)
omtrek = OmtrCirkel(straal) CELOMLAAG
DRUK (oppervlakte) CELOMLAAG
DRUK (omtrek)
End Sub
:
Opdracht 7• Selecteer het blad Functie in procedure in de map Functies
• Maak een programma dat de straal van de cirkel vraagt en deze noteert in de cel C7. De oppervlakte komt dan in C8 en de omtrek in C9. Maak op de voorziene plaats een knop om je algoritme te starten. De randen van de knop laat je perfect samenvallen met de randen van de cellen. De broncode vind je hierboven.
• Maak een tweede algoritme dat de wortelexponent en het grondtal vraagt en hieruit de n-de machtswortel berekent. Gebruik de functie die je gemaakt hebt. Voorzie een knop om je programma op te starten. Kijk zelf hoe je alles schikt.
Vul je broncode aan:
Sub ...ndemachtswortel() ...SELECTEER ("H7") ...n = LEES
("Wortelexponent")
...CELOMLAAG
...a = LEES ("grondtal") ...CELOMLAAG
...resultaat = nWortel(n, a)
...DRUK (resultaat) End Sub
• Bewaar de werkmap en sluit ze af.
3.5 De begrensde herhaling
3.5.1 Het principe: het werken met een teller
Tot nu toe hebben we enkel met sequenties gewerkt. Hiermee kunnen we al een aantal problemen aanpakken, maar het is niet altijd de meest efficiënte manier. We nemen er een nieuwe controlestructuur bij: de herhaling of iteratie.
In ons algoritme BerekenDubbel kwam een deelalgoritme Zak6Cellen voor. Dat laatste werd als volgt verfijnd:
-- Zak6Cellen --
CELOMLAAG CELOMLAAG CELOMLAAG CELOMLAAG CELOMLAAG
CELOMLAAG
Hierin zit dus in feite maar één enkele opdracht CELOMLAAG. Diezelfde opdracht moest zes keer uitgevoerd worden, dus hebben we hem zes keer ingevoerd. Nu bestaat er een handige controlestructuur die speciaal ontworpen werd om een deelalgoritme een vast aantal keren uit te voeren. We noemen deze controlestuctuur de begrensde herhaling, omdat het aantal herhalingen door een gegeven getal begrensd is.
-- Zak6Cellen --
Herhaal 6 keer
CELOMLAAG
De processor zal met behulp van de begrensde herhaling tellen hoe dikwijls de opdracht al is uitgevoerd.
Om te kunnen tellen hebben we een variabele nodig, al is het maar om te onthouden hoe dikwijls de opdracht al werd uitgevoerd. We zullen deze variabele als teller gebruiken. Dikwijls wordt hiervoor de letter i gebruikt, naar analogie met de wiskunde, maar in feite kan je die vrij kiezen.
De broncode voor onze verfijning wordt nu:
Sub Zak6Cellen() For i = 1 To 6 CELOMLAAG Next i
End Sub
Hierbij zal i de waarde 1 krijgen (For i = 1) en de opdracht CELOMLAAG wordt uitgevoerd. Door de instructie Next i (volgende waarde voor i) wordt i gelijkgesteld aan 2 en de opdracht CELOMLAAG wordt opnieuw uitgevoerd. Dit blijft zo verder gaan tot i gelijk is aan 6 (To 6). Met To 6 wordt wel bedoeld tot en met 6.
De variabele i wordt in dit voorbeeld dus niet gebruikt om mee te rekenen. Ze dient enkel als teller om te weten hoe dikwijls de opdracht al werd uitgevoerd.
? Syntaxregels
hier is een beperkte kennis van de syntax (juiste schrijfwijze) nodig:
For variabele = kleinste To grootste → komt op 1 lijn. Er komt niets voor of achter opdracht → te herhalen opdracht(en) komen op de volgende
lijn(en)
Next variabele → woord Next gevolgd door de naam van de tellervariabele. Deze komt op de lijn volgend op deze met de laatste te herhalen opdracht.
Om het geheel overzichtelijk te houden laten we de opdracht die herhaald moet worden wat meer inspringen.
:
Opdracht 8• Open de werkmap Begrensde herhaling
• Vul de verfijning Zak6Cellen verder aan. De grofstructuur werd al gemaakt.
De woorden For en Next worden sleutelwoorden genoemd. Wat stel je vast als je de lijn waar je het sleutelwoord in geplaatst hebt verlaat? Ze worden in het blauw...
geplaatst...
Daarom staan ze in deze cursus in het vet. Als je zelf programmacode neerschrijft, neem dan voor je sleutelwoorden ook een afwijkende kleur.
• Test je programma uit
• Bewaar de werkmap opnieuw
3.5.2. Een praktisch voorbeeld: tekenen van zebrapaden.
a) Probleemdefinitie
Maak een algoritme dat een zebrapad tekent in de kolom B. De beginsituatie staat al op schijf in het bestand Zebrapad. De knoppen zijn al voorzien en de knop “Wis zebrapaden” werkt. De knop
“Teken Zebrapaden” is al gekoppeld aan de (nog lege) grofstructuur.
Dit programma is een voorbereiding op een ruimere opgave, waar meerdere zebrapaden naast elkaar zullen moeten komen. Zorg dat je programma eenvoudig aanpasbaar is.
Bestaande zebrapaden moeten niet gewist worden in de uitvoering van het programma.
Na de uitvoering van je algoritme staat de celcursor in de cel C7 (achter de knop).
b) Probleemanalyse
We trekken het probleem uiteen in een aantal deelproblemen.
Eerst en vooral moeten we de celcursor...op de juiste plaats zetten...
Vervolgens tekenen ...we het ...zebrapad zelf. Tot slot plaatsen we ...de celcursor achter de knop.
c) Schema
Grofstructuur:
-- Zebrapaden --
GaNaarStartpositie Teken1zebrapad VerbergCelcursor
Verfijningen diepte 1
-- GaNaarStartpositie --
SELECTEER cel B10
-- Teken1Zebrapad --
Herhaal ....6 keer
KleurAchtergrondWit Zak2Cellen
-- VerbergCelcursor --
SELECTEER cel B10
Verfijning diepte 2
-- Zak2Cellen --
Herhaal ....2 keer
CELOMLAAG
Opgenomen macro
-- KleurAchtergrondWit -- d) Programmeren
Nu het echte denkwerk gedaan is, kunnen we aan het programmeren beginnen. Het komt er op neer dat we ons NS-diagram in VBA moeten ingeven. Hiervoor kunnen we gebruik maken van de concrete deelalgoritmen (zie vroeger in deze cursus), van eigen gemaakte procedures en functies en van macro’s. In dit algoritme komen zelfgedefinieerde functies voor.
We zullen hier deelalgoritme per deelalgoritme “vertalen”.
grofstructuur:
-- Zebrapaden --
GaNaarStartpositie Teken1zebrapad VerbergCelcursor
Om een deelalgoritme in onze programmacode op te nemen volstaat het om de naam van dat deelalgoritme in te tikken. Als we dit vertalen in VBA krijgen we:
Sub TekenZebrapaden() GaNaarStartpositie Teken1Zebrapad VerbergCelcursor End Sub
:
Opdracht 9• Open de werkmap Zebrapad
• Start de VBA -editor op. Dat kan met de knop uit de werkbalk Visual Basic
• Open de module Procedures, als dat nog niet het geval zou zijn.
• Je ziet dat er al commentaarregels in staan. Maak er een gewoonte van om uitleg te geven bij je algoritme (documenteren) en geef aan waar de grofstructuur begint.
