II. ZELFGEDEFINIEERDE FUNCTIES In Excel bestaat reeds een uitgebreide reeks van functies zoals SOM, GEMIDDELDE, AFRONDEN, NU enz. Het is de bedoeling om functies aan deze lijst toe te voegen door in Visual Basic For Applications (VBA) te gaan programmeren. Aan de hand van enkele eenvoudige functies laten we zien hoe dit moet. Tevens leggen we enkele zaken uit over VBA. §1. Voorbeeld Hoe kan ik beginnen programmeren? Wil je kunnen programmeren moet je eerst de visual-basic editor openen als volgt: Kies in het menu van Excel: Extra Macro Visual basic-editor Een programma wordt opgedeeld in modules. Je moet dus eerst een module invoegen, wil je kunnen code voor het programma invoegen. Een module voeg je als volgt in: Kies in het menu van VBA: Invoegen Module Er verschijnt nu een invoervenster, waarin je kan werken net alsof je met een elementaire tekstverwerker werkt. In de titelbalk van het visual basic venster vind je de volgende titel terug: Microsoft Visual basic - Map1 - [Module1(code)] Hier programmeren we onze eerste functie: Opgave: Maak een functie OmtrekCirkel die de omtrek van een cirkel bepaalt. In het invoer venster typen we de volgende regel: Function OmtrekCirkel(r)
John De Maesschalck IKSO DENDERLEEUW
21
Nadat je enter drukt om van regel te veranderen verschijnt er automatisch de volgende regel: End Function Hieronder vind je een schermafdruk: De wiskundige definitie van de omtrek van een cirkel met straal π = 2πR In Visual basic kunnen we de waarde van π niet opvragen. We kunnen dit echter wel vanuit Excel, de toepassing waaraan VBA is gekoppeld. π kunnen we bepalen door de functie Application.pi().(dit is application puntje pi en haakje open en haakje dicht) Vandaar dat we de functie OmtrekCirkel als volgt definiëren: OmtrekCirkel=2*Application.pi()*r
Alvorens de functie uit te proberen controleren we of er geen fouten tegen de taal van VBA zijn gemaakt. Kies daarom uit het menu: Foutopsporing VBA Project compileren We klikken onderaan op de taakbalk op het pictogram van Excel. In Excel vul je de volgende cellen in
In cel A1 de straal namelijk 1 en in cel B1 de formule =omtrekcirkel(A1) Extra opgave: Maak een functie die de oppervlakte van een cirkel bepaalt. Wiskundige formule=πr2. In Excel kan je machten programmeren met het hoedje ^. Rn wordt dan R^n vandaar dat in VBA de functie als volgt wordt geprogrammeerd:
John De Maesschalck IKSO DENDERLEEUW
22
§2. Controle-structuren In de volgende voorbeelden kijken we hoe je de uitvoering van de code kan beïnvloeden. Soms moet een programma code uitvoeren naargelang bepaalde voorwaarden voldaan zijn. ➊ De selectie of keuze-structuur (IF THEN ELSE) Opgave Maak een functie die het grootste getal bepaalt van twee getallen: De code voor in VBA vind je hier onder:
Opmerkingen: 1. 2. 3.
Na het sleutelwoord Then mag je geen code plaatsen De keuze-structuur eindigt op End If Je hoeft het ELSE-gedeelte niet te gebruiken.
Test in Excel: Stel dat je het grootste getal van 10 en 20 wenst te bepalen, dan kan je niet gewoon =grootste(10;20) gebruiken. Grootste is een voorgedefinieerde functie in Excel. Je zal dus moeten expliciet vermelden dat Excel uw functie uit module1 moet gebruiken. Dit doe door als functienaam Module1.grootste en niet grootste te gebruiken.
Extra opgave: Maak een functie die het kleinste getal bepaalt van twee getallen:
John De Maesschalck IKSO DENDERLEEUW
23
Een variant van de IF THEN ELSE is de IF THEN ELSEIF controlestructuur Bij de IF THEN ELSE-structuur kan je maar één voorwaarde testen, bij de IF THEN ELSEIF kan je meerder voorwaarden testen Hierna volgt een functie Bonus. Deze functie bepaalt de bonus die een werknemer krijgt op het einde van het jaar. De bonus hangt af van de functie in het bedrijf, de salaris en de uiteindelijke waardering van de werkgever. We gaan vanaf nu ook als eerste regel in onze module opnemen: Option Explicit Indien we dan foutopsporing uitvoeren krijgen we een foutmelding indien we een variabele gebruiken die we nergens gedeclareerd(gedefinieerd) hebben
Voorbeelden van het gebruikt van de functie:
Oefeningen: 1. Bootst de ABS-functie van Excel na. Deze functie bepaalt de absolute waarde van een getal.
2.
Maak een functie die de coëfficiënten van een kwadratische functie als argumenten krijgt en die als resultaat drie mogelijke zinnen geeft nl één nulpunt, twee nulpunten of geen nulpunten.
John De Maesschalck IKSO DENDERLEEUW
24
3.
Maak een functie die drie getallen sorteert. Het resultaat is een zin waarin de getallen gescheiden door spaties achter elkaar staan.
4.
Maak een functie die bepaalt of een getal even is of niet. De functie levert true of false Gebruik de functie mod. Deze functie levert de rest bij deling. 7 mod 3 levert 1
Indien je deze functie EVEN noemt, moet je in Excel testen als module1.EVEN !
John De Maesschalck IKSO DENDERLEEUW
25
➋ De begrensde herhaling (FOR NEXT) Opgave1 Maak een functie die de bestaande Excelfunctie SOM nabootst Noem de functie Totaal Hiernaast zie je een voorbeeld schermafdruk
In het programma hierboven stelt de variabele bereik een bereik van cellen voor. De structuur FOR EACH wordt gebruikt om in dit voorbeeld alle cellen uit het bereik te doorlopen. Voor elke cel worden de bevelen tussen FOR EACH en NEXT één keer uitgevoerd. Opgave2 Schrijf een functie die voor een vierkantig bereik cellen de som bepaalt van de cellen op de dalende diagonaal of op de stijgende diagonaal. Noem de functie TotaalDiagonaal. Het onderscheid tussen een dalende of een stijgende diagonaal wordt gemaakt door als tweede parameter een -1 of een +1 op te geven.
In een bereik worden de rijen en de kolommen genummerd vanaf 1. John De Maesschalck IKSO DENDERLEEUW
26
Hoe weet je nu hoeveel rijen en kolommen er aanwezig zijn in een bereik? Een bereik wordt opgedeeld in Rows(rijen) en Columns(kolommen). We kunnen aan het object Rows vragen hoeveel rijen er aanwezig zijn door de eigenschap Count op te vragen. Het volledige bevel ziet er uit als volgt: bereik.Rows.Count en levert het aantal rijen in het bereik op. De getalwaarde in een cel afkomstig uit een bereik verkrijg je door het bevel bereik.Cells(rijnr,kolomnr). Je moet dus eerst het rijnummer en dan het kolomnummer opgeven. Voor een dalende diagonaal is het rijnummer steeds gelijk aan het kolomnummer, vandaar dat de tweede parameter ook rij is. Het bevel is dan bereik.Cells(rij,rij) Voor een stijgende diagonaal beginnen we linksonderaan het bereik. Hier hebben we het hoogste rijnummer en het laagste kolomnummer namelijk 1. We moeten kunnen uitdrukken dat hoge rijnummers moeten overeenkomen met lage kolomnummers en omgekeerd. We maken de vergelijking met de volgende getallenrij x= 6+1-x=
1 6
2 5
3 4
4 3
5 2
6 1
We moeten dus van de maximale waarde x aftrekken en nog 1 extra optellen. Vandaar het bevel bereik.Cells(rij,bereik.Columns.count-rij+1) Het is een rechthoekig bereik. We kunnen dan evengoed het bevel bereik.Cells(rij,bereik.Rows.count-rij+1) gebruiken. Opgave 3 Maak een functie die enkel de positieve getallen uit een bereik optelt Noem de functie SomPos
Opgave 4
John De Maesschalck IKSO DENDERLEEUW
27
Schrijf een functie die de volgende speciale som maakt van twee bereiken. Gegeven twee bereiken die even groot zijn. Elke cel uit het eerste bereik wordt vergeleken met de overeenkomende cel uit het tweede bereik. Telkens wordt de grootste waarde genomen van beide cellen. Bepaal de totale som. Noem de functie SomMax.
Opgave 5 Schrijf een functie die alle cellen uit een bereik kolom per kolom optelt. Sommige kolommen mogen niet worden opgenomen in de optelling. In een afzonderlijk bereik wordt overeenkomstig met een 0 aangegeven dat de kolom niet mag worden meegerekend, een 1 geeft aan dat de overeenkomstige kolom wel moet worden meegerekend. Noem de functie SomSelect.
Opgave 6 John De Maesschalck IKSO DENDERLEEUW
28
Schrijf een functie die het gemiddelde kan bepalen van een aantal toetsen, die op een verschillend maximum staan. De functie moet de behaalde punten, de maxima en het uiteindelijke maximum als argumenten krijgen. Noem de functie zwaartepunt.
De functie IsNumber(in het Nederlands IsGetal) bepaalt of de waarde in een cel een numerieke waarde bepaald. Opgave 7 Zorg ervoor dat de functie uit opgave 6 als resultaat #WAARDE! oplevert indien het bereik met de maxima en het bereik met de behaalde punten niet even veel kolommen bevat.
John De Maesschalck IKSO DENDERLEEUW
29
De functie Na(Not Available of in het Nederlands Nb Niet Beschikbaar) levert de waarde #WAARDE!. Deze fout blijft zich voortplanten in alle berekeningen die deze foutwaarde gebruiken. Het bevel Exit Function zorgt ervoor dat de functie onmiddellijk wordt verlaten. Alle volgende bevelen worden NIET meer uitgevoerd.
John De Maesschalck IKSO DENDERLEEUW
30
