EXCEL Inleiding tot Macro s Mod VII. CEVORA vzw - opleidingscentrum van het ANPCB (PC218)

EXCEL Inleiding tot Macro’s Mod VII CEVORA vzw - opleidingscentrum van het ANPCB (PC218)

Inhoudsopgave INHOUDSOPGAVE .............................................................................................................................. 1-1 1

EEN MACRO OPNEMEN. ............................................................................................................. 1-3

1.1 1.2 1.3 1.4 1.5 1.6

2

Wat is een macro? Het tabblad ontwikkelaars (developer) Macro’s opnemen Een macro uitvoeren Een macro verwijderen of verbeteren Relatieve en absolute macro’s

1-3 1-3 1-4 1-5 1-6 1-6

EEN MACRO AAN EEN KNOP VERBINDEN. .................................................................................. 2-9

2.1 2.2 2.3 2.4 2.5

3 4

Knop via werkbalk voor snelle toegang Knop via het lint Formulierknop op het werkblad ActiveX control op het werkblad Macro’s koppelen aan autovormen

2-9 2-11 2-12 2-14 2-16

MAPPEN MET MACRO’S OPSLAAN. .......................................................................................... 3-18 MACROBEVEILIGING. ............................................................................................................... 4-19

4.1 4.2 4.3 4.4 4.4.1

Macro's inschakelen wanneer de berichtenbalk wordt weergegeven 4-19 Macro's inschakelen in de Backstage-weergave 4-19 Macro-instellingen wijzigen in het Vertrouwenscentrum 4-20 Info over de macro-instellingen 4-21 Alle macro's uitschakelen, zonder melding ..................................................................................... 4-21

4.4.2

Alle macro's uitschakelen, met melding .......................................................................................... 4-21

4.4.3

Alle macro's uitschakelen ............................................................................................................... 4-21

4.4.4 4.4.5 4.5

Alle macro's inschakelen ................................................................................................................ 4-21 Toegang tot het objectmodel van het VBA-project vertrouwen ...................................................... 4-21 Een vertrouwde locatie voor uw bestanden toevoegen, verwijderen of wijzigen 4-22

5

VISUAL BASIC EDITOR OMGEVING. ........................................................................................... 5-23

5.1 5.2 5.3

Commentaar Sub … End Sub With… End With

5.3.1 5.3.2

6

5-24 5-24 5-24

Kleurindex...................................................................................................................................... 5-24 Verwijderen overtollige code ......................................................................................................... 5-24

VBA EN MACRO BOUWSTENEN. ............................................................................................... 6-25

6.1 6.2 6.3 6.3.1

Berichtvensters 6-25 Invoervensters 6-25 Variabelen en constanten 6-25 De verschillende gegevenstypes ..................................................................................................... 6-25

6.3.2

Variabelen ..................................................................................................................................... 6-25

6.3.3 6.3.4

Constanten .................................................................................................................................... 6-25 Option Explicit ............................................................................................................................... 6-26

7

STRUCTUUR BOUWEN MET MACRO’S. ..................................................................................... 7-27

7.1 7.2 7.3 7.3.1

Sequentie of opeenvolging Selectie of keuze Iteratie of herhaling

7-27 7-27 7-27

Begrensde herhaling ...................................................................................................................... 7-28

7.3.2 7.4

8 8.1 8.2

9 9.1 9.2 9.3

Voorwaardelijke herhaling ............................................................................................................. 7-28 Meervoudige keuze 7-28

GERESERVEERDE MACRONAMEN. ............................................................................................ 8-29 Auto_Open of Workbook_Open Auto_Close of Workbook_Close

8-29 8-29

EEN FUNCTIE ONTWIKKELEN. ................................................................................................... 9-30 Procedure maken Een functie oproepen De functie finaliseren

9-30 9-31 9-32

10 HET VERSCHIL TUSSEN MACRO’S EN FUNCTIES....................................................................... 10-33 10.1 10.2 10.3 10.4

Macro’s of functies aanroepen Het gebruik van argumenten Functies geven een waarde terug Macro aanroep Call

10-33 10-35 10-35 10-36

11 MACRO’S EN FUNCTIES VERSPREIDEN EXCEL ADD-IN. ............................................................ 11-37 11.1 11.2 11.3

Excel Add-in Add-In beschikbaar stellen Nieuwe functionaliteiten gebruiken

11-37 11-37 11-39

1 Een macro opnemen. 1.1 Wat is een macro? 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. Dit is niet enkel tijdsbesparend maar je vermijdt ook fouten. Complexe handelingen kunnen voortaan ook door minder ervaren Excel –gebruikers uitgevoerd worden door middel van een macro.Willen we bijvoorbeeld de opdracht mcrKleurAchtergrondRood 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.

1.2 Het tabblad ontwikkelaars (developer)

In normale omstandigheden is het tabblad ‘Ontwikkelaars’ (‘Developer’) niet actief. Om macro’s te kunnen maken in Excel moet je het tabblad “Ontwikkelaars” activeren. Dit doen we als volgt: 

Klik op het tabblad Bestand (File) en kies Opties (Options).



In het dialoogvenster Opties voor Excel (Excel options) kies je voor Lint aanpassen (Customize Ribbon).



Hier kan je in het rechter deel van het venster het tabblad Ontwikkelaars activeren.

3

Een macro opnemen. | Excel 2013

(Developer)

1.3 Macro’s opnemen 

Selecteer het tabblad Ontwikkelaars (Developer) en klik op de knop Macro opnemen (Record Macro) in de groep Programmacode (Code).

Vervolgens krijg je een dialoogvenster waar je de macronaam kan ingeven.

Afspraken ivm naamgeving: 

gebruik enkel letters, cijfers en het onderlijningsteken (underscore) ( _ );

4




macro’s beginnen we met mcr en vervolgens beginhoofdletters om een leesbare naam te bekomen;



spaties zijn niet toegestaan;



we gebruiken de naam die ons deelalgoritme moet krijgen;



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 (Average)).

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. 

In de groep Programmacode (Code) verschijnt de knop Opname stoppen (Stop Recording). Deze knop verschijnt eveneens in de statusbalk.

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.

1.4 Een macro uitvoeren Verschillende mogelijkheden om de opname te starten:



Via het Lint, Ontwikkelaars (Developer) in de groep Programmagoce (Code) klik op de knop Macros



Als er bij de macro een sneltoets gedefinieerd is, kan je de macro natuurlijk ook starten met behulp van deze sneltoets.



Een macro koppelen aan een knop (zie hieronder)

Vervolgens krijg je een dialoogvenster met alle beschikbare macro’s in deze werkmap. Selecteer de gewenste macro en klik op de knop “Uitvoeren”.

5


1.5 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. Om een macro te verbeteren of te verwijderen ga je terug naar het dialoogvenster “Macro”. Om een macro te verwijderen klik je de gewenste macro aan en klik je op de knop Verwijderen (Delete). Om de macro aan te passen klik je op de knop Bewerken (Edit). 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. Nu kan je de bestaande code eventueel aanpassen. Een macro verbeteren kan ook wel handig zijn om er “overtollige broncode” uit te verwijderen. De programmagenerator plaatst immers dikwijls overbodige code in de macro, wat de uitvoering kan vertragen. 

In de groep Code (Code) vind je de knop Visual Basic. Deze knop geeft toegang tot de Visual Basic Editor.

1.6 Relatieve en absolute macro’s Bij het opnemen van een macro slaat Excel exacte verwijzingen op naar de cellen die je selecteert. Dit betekent dat bij het uitvoeren van de macro altijd dezelfde cellen zullen geselecteerd worden, ongeacht wat de huidige actieve cel is. Standaard zijn alle macro’s in Excel absoluut. In het onderstaande voorbeeld werd een macro opgenomen die de inhoud van de cellen copieerd naar de kolom D. Standaard werd deze macro opghenomen met Absolute referenties

6




De code ziet er als volgt uit:



Dezelfde macro werd opnieuw opgenomen met Relatieve celverwijzingen. Dit maal werd bij de opname de knop Relatieve verwijzingen gebruiken (Use Relative References) aangeklikt in de groep Code



De macro zal nu rekening houden met de locatie van de huidige actieve cel.



De code ziet er in dit geval anders uit:

7


Het gebruik van Absolute en Relatieve verwijzingen moet worden ingesteld telkens dit voor de uitvoering van de macro noodzakelijk is. Je dient hiermee rekening te houden bij de opname.

8


2 Een macro aan een knop verbinden. Een handige manier om een macro te laten starten is: 

Een knop toe te voegen in de werkbalk voor snelle toegang (Quick Access Toolbar), in het lint een bijkomend tabblad te maken met één of meerdere groepen en de knoppen hieraan toe te voegen.



Gebruik maken van een formulierknop in het werkblad.



Gebruik maken van een ActiveX knop in het werkblad.



Deze toe te kennen aan eender welke tekenobject…

Macro’s kunnen ook aan ander gebeurtenissen worden toegekend zoals het openen van een werkblad, sluiten van een werkblad. Daar gaan we in deze cursus later verder op in.

2.1 Knop via werkbalk voor snelle toegang Klik op het pijltje in de Werkbalk voor Snelle toegang (Quick Access Toolbar) en kies voor Meer opdrachten (More Commands).

Het dialoogvenster Excel Opties wordt geopend in het gedeelte Werkbalk Snelle toegang (Quick Access Toolbar).

9

Een macro aan een knop verbinden. | Excel 2013



Selecteer in de groep Kies opdrachten uit (Choose commands from) voor Macro’s.



De macro’s verschijnen in het linker venster.



Je kan nu Macro’s Toevoegen (Add) aan de de Werkbalk voor snelle toegang (Quick Access Toolbar).



Via de knop Aanpassen (Modify) kan je de Naam en het getoonde Icoon aanpassen.

10




Je bekomt het volgende resultaaat.

2.2 Knop via het lint 

Klik op het tabblad Bestand (File). De Backstage-weergave wordt geopend.



Klik onder Help op Opties. Het dialoogvenster Opties wordt weergegeven.



Klik op Lint aanpassen (Customize Ribbon).

Je kan nu nieuwe tabbladen met groepen toevoegen waar je zelf vanuit de groep macro’s, deze kan toevoegen in de zelfgemaakte groepen.

11


Verder kan je de titel van het tabblad en de namen van de groepen aanpassen naar wens. Je bekomt vervolgens het gewenste resultaat.

2.3 Formulierknop op het werkblad 

In de groep Programmacode (Controls) vind je de knop Invoegden (Insert). Deze knop bevat twee delen; de Formulierbesturingselementen (Form Controls) en de ActiveX besturingselementen (ActiveX Controls).

Van deze werkbalk hebben we maar één knop nodig: Klik op deze knop en je ziet dat je muiscursor in een fijn kruis verandert. Teken nu de knop op de gewenste plaats door te slepen van de linkerbovenhoek naar de rechterbenedenhoek. 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. We maken hier gebruik van de Formulierbesturingselementen en tekenen een knop op het werkblad. Onmiddellijk hierna wordt het dialoogvenster Macro toewijzen aan object (Assign Macro) geopend van waaruit we de desgewenste macro kunnen selecteren. 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 rechtermuisknop 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.

12


Zorg dat de macroknop niet verdwijnt bij het filteren van gegevens Klik met de rechtermuisknop op de knop en kies “Besturingselement opmaken” (Format Control). Stip aan dat de knop niet mag gerelateerd worden aan de cellen in het tabblad Kenmerken (Properties).

13


2.4 ActiveX control op het werkblad 

In de groep Programmacode (Controls) vind je de knop Invoegden (Insert). Deze knop bevat twee delen; de Formulierbesturingselementen (Form Controls) en de ActiveX besturingselementen (ActiveX Controls).

Dit lijkt heel sterk op de eerste mogelijkheid om een knop via werkbalk Formulieren te maken. De besturingselementen zijn ongeveer dezelfde maar de aanpassingsmogelijkheden zijn uitgebreider. Je kan meteen VBA-code schrijven gekoppeld aan de knop. We maken hier gebruik van de ActiveX besturingselementen en tekenen een knop op het werkblad.



In de groep Programmacode (Controls) merk je dat de knop Ontwerpmodus (Design Mode) actief is en dat je tevens kunt gebruik maken van de knoppen Eigenschappen (Properties) en Programmacode weergeven(View Code).

Via rechtermuisknop kan je ook de eigenschappen (Properies) van de knop wijzigen. Geef alvast onmiddellijk een correcte en duidelijke naam alvorens je code gaat toekennen.

14


Na het tekenen van een knop klikken we met de rechtermuisknop op het object en kiezen we Programmacode weergeven (View Code).

15


Wil je een dergelijk knop testen dan moet je eerst de ontwerpmodus afsluiten. Wil je later wijzigingen uitvoeren zowel wat opmaak betreft als programmeercode, dan schakel je de ontwerpmodus terug in via dezelfde knop.

2.5 Macro’s koppelen aan autovormen Kies een autovorm en plaats die in je werkblad. Via rechtermuisknop kan je een macro toewijzen aan je object. Uiteraard is het mogelijk om de opmaak naar wens aan te passen.

16


17


3 Mappen met macro’s opslaan. Mappen met macro’s hebben de extensie *.xlsm. Standaard stelt Excel bij het opslaan de extensie *.xlsx voor. Deze bestandsindeling kan echter geen macro’s opslaan. Je moet dit zelf wijzigen in *.xlsm

Als je dit toch vergeet, krijg je van Excel een waarschuwing

18

Mappen met macro’s opslaan. | Excel 2013

4 Macrobeveiliging. Om efficiënt te kunnen werken kan het echter zijn dat je bepaalde macro's op de computer moet uitvoeren. Dat betekent dat je op een bepaald moment moet beslissen of je de makers van deze macro's kunt vertrouwen. Beveiliging is een zaak van vertrouwen. Wie kan je vertrouwen? Hoe weet je dat? Gelukkig beschikken de Office-programma's over functies die je helpen bij het nemen van deze beslissingen. Met behulp van digitale certificaten kan je met de beveiligingsniveaus voor macro’s instellen. Hierdoor kan je de computer minder kwetsbaar kunt maken tegen aanvallen door kwaadwillenden.

4.1 Macro's inschakelen weergegeven

wanneer

de

berichtenbalk

wordt

Wanneer je een bestand met macro's opent, wordt de gele berichtenbalk weergegeven met een schildpictogram en de knop Inhoud inschakelen (Enable content). Voer de volgende stappen uit als je zeker weet dat de macro's afkomstig zijn van een betrouwbare bron: 

Klik op de berichtenbalk op Inschakelen.



Het bestand wordt geopend en is een vertrouwd document.

De volgende afbeelding is een voorbeeld van de berichtenbalk wanneer het bestand macro's bevat.

4.2 Macro's inschakelen in de Backstage-weergave U kunt ook macro's in een bestand inschakelen vanuit de Backstage-weergave van Microsoft Office wanneer de gele berichtenbalk wordt weergegeven. 


19

Macrobeveiliging. | Excel 2013



Klik in het gedeelte Beveiligingswaarschuwing op de knop Inhoud inschakelen (Enable content) op de pijl-omlaag.



Selecteer onder Alle inhoud inschakelen (Enable All Content) de optie Altijd de actieve inhoud van dit document inschakelen.



Het bestand wordt een vertrouwd document.

4.3 Macro-instellingen wijzigen in het Vertrouwenscentrum Instellingen voor macro's bevinden zich in het Vertrouwenscentrum. Als je in een organisatie werkt, kan de systeembeheerder echter mogelijk de standaardinstellingen hebben aangepast om te voorkomen dat iemand instellingen wijzigt. Als je de instellingen voor macro's wijzigt in het Vertrouwenscentrum, worden deze alleen gewijzigd voor het Office-programma waarmee je nu aan het werk bent. De instellingen voor macro's worden niet voor alle Office 2010-programma's gewijzigd. 


20







Klik op Vertrouwenscentrum (Trust Center) en klik op Instellingen voor het Vertrouwenscentrum (Trust Center Settings).



Klik in het Vertrouwenscentrum op Instellingen voor macro's (Macro Settings).



Selecteer de gewenste opties. Klik op OK.

De volgende afbeelding Vertrouwenscentrum.

bevat

het

gedeelte

Macro-instellingen

van

het

4.4 Info over de macro-instellingen 4.4.1

Alle macro's uitschakelen, zonder melding

Macro's en beveiligingsmeldingen over macro's zijn uitgeschakeld. 4.4.2

Alle macro's uitschakelen, met melding

Macro's zijn uitgeschakeld, maar er worden wel beveiligingswaarschuwingen weergegeven als er macro's aanwezig zijn. Op deze manier kan je per keer kiezen of je macro's wilt inschakelen. 4.4.3

Alle macro's uitschakelen

Macro's zijn uitgeschakeld, maar er worden wel beveiligingswaarschuwingen weergegeven als er macro's aanwezig zijn. Als de macro echter digitaal is ondertekend door een vertrouwde uitgever, wordt deze uitgevoerd als je eerder hebt aangegeven dat de uitgever vertrouwd is. Als dit niet het geval is, ontvangt u een melding waarin je de ondertekende macro kunt inschakelen en de uitgever kunt vertrouwen. 4.4.4

Alle macro's inschakelen

Alle macro's worden uitgevoerd. Met deze optie wordt de computer kwetsbaar voor mogelijk schadelijke programmacode. 4.4.5

Toegang tot het objectmodel van het VBA-project vertrouwen

Programmatoegang tot het VBA-objectmodel weigeren of toestaan voor een automatiseringsclient. Deze beveiligingsoptie is bedoeld voor code die is geschreven voor de automatisering van een Office-programma en het bewerken van de VBA-omgeving en het objectmodel. Deze optie wordt afzonderlijk ingesteld per gebruiker en toepassing. Toegang wordt standaard geweigerd, zodat onbevoegde programma's geen zichzelf kopiërende code kunnen maken. Schakel het selectievakje in om toegang te verlenen.

21


4.5 Een vertrouwde locatie voor uw bestanden toevoegen, verwijderen of wijzigen Een vertrouwde locatie is een map op een vaste schijf of in een netwerk. U kunt alle bestanden op een vertrouwde locatie openen zonder dat deze door het beveiligingssysteem van het Vertrouwenscentrum worden gecontroleerd. 

Klik op het tabblad Bestand (File). De Backstage-weergave van Microsoft Office wordt geopend.






Klik op Vertrouwenscentrum (Trust Center).



Klik op Instellingen voor het Vertrouwenscentrum (Trust Center settings).



Klik in het Vertrouwenscentrum op Vertrouwde locaties (Trusted locations).



Klik op de knop Nieuwe locatie toevoegen (Add new location). Het dialoogvenster Vertrouwde locaties wordt weergegeven.



Klik op Bladeren om naar de map te gaan, selecteer een map en klik op OK.

De volgende afbeelding bevat het gebied Vertrouwde locaties (Trusted locations) van het Vertrouwenscentrum (Trust Center).

22


5 Visual Basic editor omgeving. 


Je kunt de Visual Basic omgeving eveneens oproepen via het macrovenster

De code gegenereerd door de macro’s bevindt zich in de map Modules van het desbetreffende VBAProject (bestand).

Hieronder volgt wat commentaar bij de VBA-code die onze macro mcrKleurAchtergrondRood heeft aangemaakt.

23

Visual Basic editor omgeving. | Excel 2013

5.1 Commentaar De tekst die voorafgegaan wordt door een ‘ teken wordt groen gekleurd. Dit betekent dat deze tekst als commentaar wordt aangezien.

5.2 Sub … End Sub Sub staat voor subroutine en wordt gevolgd door de naam van de macro gevolgd door 2 ronde haakjes. De code wordt afgesloten met End Sub

5.3 With… End With Er wordt gebruik gemaakt van een “With … End With” – constructie. Dit is een verkorte schrijfwijze om aan te duiden dat je met één bepaald object (in dit geval selectie) meerdere dingen wil doen of meerdere eigenschappen wil wijzigen. 5.3.1

Kleurindex

De code selection.interior.color = 255 kent aan het binnenste van de selectie de kleurcode 255 toe. Er wordt gebruik gemaakt van 256 kleuren (van 1 tem 256) Door rechtstreeks in de code een andere kleurindex te kiezen, wordt de opvulkleur gewijzigd. 5.3.2

Verwijderen overtollige code

Zoals je kunt zien in de tweede macro wordt alle code opgenomen in de macro, ook die instellingen die je niet hebt gewijzigd. Bijgevolg is het nodig om rechtstreeks in de code de overtollige instructies te verwijderen. TIP: plaats de regels code eerst als commentaar en test je code opnieuw uit. Pas achteraf kan je de instructies definitief verwijderen.

24

Visual Basic editor omgeving. | Excel 2013

6 VBA en Macro bouwstenen. 6.1 Berichtvensters Geeft een bericht weer in een dialoogvenster, wacht totdat de gebruiker een knop kiest en geeft als resultaat een Integer-waarde die aangeeft welke knop de gebruiker heeft gekozen. Syntaxis: MsgBox(prompt[, knoppen] [, titel] [, helpbestand, context])

6.2 Invoervensters Geeft een aanwijzing weer in een dialoogvenster, wacht tot de gebruiker tekst invoert of een knop kiest en geeft een string met de inhoud van het tekstvak als resultaat. Syntaxis: InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

6.3 Variabelen en constanten Bij macro’s gaan we ook gebruik maken van variabelen om tijdelijke waarden in op te slaan en constanten om onze vaste gegevens te bewaren voor later gebruik. 6.3.1

De verschillende gegevenstypes

Het voorgaande moet je reeds duidelijk gemaakt hebben dat er verschillende gegevenstypes bestaan. Het is ook van het allergrootste belang dat niet Excel, maar jijzelf deze gegevenstypes declareert. Hierdoor spaar je niet alleen het geheugen, laat je ook de code sneller lopen, en zal Excel u zelfs verder nog helpen bij het schrijven van de code, of het opsporen van fouten. 6.3.2

Variabelen

Voor het declareren van variabelen gebruik je gewoonlijk de instructie Dim. Je kunt een declaratie-instructie binnen een procedure plaatsen om een variabele van procedureniveau aan te maken. Je kunt de instructie ook boven aan een module in de sectie Declaraties plaatsen, waardoor je een variabele van moduleniveau maakt. Syntaxis: [Public | Private] Dim variabelenaam [As type]

Als deze instructie binnen een procedure verschijnt, kan de variabele alleen in die procedure worden gebruikt en spreken we van een lokale variabele. Als de instructie in de sectie Declaraties van de module verschijnt, is de variabele voor alle procedures binnen de module beschikbaar, maar niet voor de procedures in andere modules in het project. Als je vóór deze variabele de instructie Public opneemt ipv Dim, maak je deze beschikbaar voor alle procedures in het project. 6.3.3

Constanten

Declareert constanten voor gebruik in plaats van letterlijke tekenreeksen. Syntaxis: [Public | Private] Const constnaam [As type] = expressie

Constanten die je declareert in een procedure Sub, Function of Property, zijn lokale constanten van die procedure. Een constante die je buiten een procedure declareert, is gedefinieerd voor de gehele module waarin je deze hebt gedeclareerd.

25

VBA en Macro bouwstenen. | Excel 2013

6.3.4

Option Explicit

Je kunt in Visual Basic impliciet een variabele declareren door deze in een toewijzingsinstructie te gebruiken. Alle variabelen die impliciet worden gedeclareerd, zijn van het type Variant. Variabelen van het type Variant nemen meer geheugenruimte in beslag dan andere variabelen. Je kunt een toepassing efficiënter maken door variabelen expliciet te declareren met een specifiek gegevenstype. 

Als je alle variabelen expliciet declareert, verklein je de kans op naamconflicten en spellingsfouten.



Als je niet wil dat Visual Basic impliciete declaraties maakt, plaats je vóór alle procedures in een module de instructie Option Explicit.



Een vereiste voor deze module is dat je alle variabelen binnen de module expliciet declareert.



Als een module de instructie Option Explicit bevat, treedt een compileerfout op wanneer Visual Basic een variabele tegenkomt die nog niet is gedeclareerd of verkeerd is gespeld.



In de programmeeromgeving van Visual Basic bevindt zich een optie die de mogelijkheid biedt de instructie Option Explicit automatisch in alle nieuwe modules op te nemen.



Extra (Tools)– Opties (Options): Variabelen declareren vereist

Vanaf dat moment zal de instructie Option Explicit automatisch worden toegevoegd in de algemene declaratiezone bij nieuwe modules.

26

VBA en Macro bouwstenen. | Excel 2013

7 Structuur bouwen met macro’s. 7.1

Sequentie of opeenvolging

Sequentie is een controlestructuur opgebouwd uit één of meer opdrachten die elkaar opvolgen. Deze controlestructuur legt vast in welke volgorde de instructies zullen uitgevoerd worden.

7.2

Selectie of keuze

Selectie is een controlestructuur opgebouwd uit een (selectie)voorwaarde gecombineerd met twee sequenties waarvan, afhankelijk van de voorwaarde slechts één wordt uitgevoerd. Syntaxis: If voorwaarde Then [instructies] [Else andersinstructies]

U kunt ook de volgende syntaxis in blokvorm gebruiken: If voorwaarde Then [instructies] [Else [andersinstructies]] End If

If-constructies kunnen ook genest worden. Een voorbeeld:

7.3

Iteratie of herhaling

Net zoals in andere programmeeromgevingen spreken we van begrensde herhalingen en voorwaardelijke herhalingen. Bij die laatste heb je opnieuw voorwaardelijke herhalingen met aanvangsvoorwaarde of met afbreekvoorwaarde. Een begrensde herhaling is een controlestructuur waarbij de instructies verschillende keren opnieuw worden uitgevoerd. Het aantal keren ligt vooraf vast. Een voorwaardelijke herhaling met aanvangsvoorwaarde is een controlestructuur opgebouwd uit een aanvangsvoorwaarde en herhalingsopdrachten. Zolang de aanvangsvoorwaarde waar is, wordt de herhaling uitgevoerd. Een voorwaardelijke herhaling met afbreekvoorwaarde is een controlestructuur opgebouwd uit herhalingsopdrachten en een afbreekvoorwaarde. De herhaling wordt uitgevoerd totdat de voorwaarde waar is.

27

Structuur bouwen met macro’s. | Excel 2013

7.3.1 Begrensde herhaling Syntaxis: For teller = beginwaarde To eindwaarde [Step stap] [instructies] [Exit For] [instructies] Next [teller]

7.3.2

Voorwaardelijke herhaling

Syntaxis Do [{While | Until} voorwaarde] [instructies] [Exit Do] [instructies] Loop

U kunt ook de volgende syntaxis gebruiken: Do [instructies] [Exit Do] [instructies] Loop [{While | Until} voorwaarde] While voorwaarde [instructies] Wend

7.4

Meervoudige keuze

Syntaxis Select Case testexpressie [Case expressielijst-n [instructies-n]] ... [Case Else [andersinstructies]] End Select

28

Structuur bouwen met macro’s. | Excel 2013

8 Gereserveerde macronamen. In de meeste gevallen bedenk je zelf een naam voor een macro. Maar in een paar gevallen geef je een macro een speciale, gereserveerde, naam.

8.1 Auto_Open of Workbook_Open Macro wordt automatisch uitgevoerd zodra een werkmap wordt geopend.

8.2 Auto_Close of Workbook_Close Macro wordt automatisch uitgevoerd zodra een werkmap wordt gesloten. Als je Auto_Open en Auto_Close opslaat in de persoonlijke macrowerkmapzal de macro enkel bij het opstarten en afsluiten van Excel werken. Enkele voorbeelden:

29

Gereserveerde macronamen. | Excel 2013

9 Een functie ontwikkelen. We kunnen ook zelf procedures invoegen in VBA.

9.1 Procedure maken Kies hiervoor in het menu van de VBA-editor voor Invoegen (Insert)– Procedure. Daarna krijg je een dialoogvenster waar je de naam van de procedure moet ingeven en aanduiden of het een subroutine of een functie betreft.

Een sub is zichtbaar in de Excel-omgeving als macro, een functie echter wordt in de Excelomgeving zichtbaar als functie in de categorie “Door gebruiker gedefinieerd” (User Defined). Let op: definieer een functie in een module en niet in een werkblad anders is de functie niet zichtbaar in Excel. Private of Public maakt voor ons nog niet veel uit. Als je een functie als private definieert is hij alleen “bekend” in de module waar hij gedefinieerd is.

30

Een functie ontwikkelen. | Excel 2013

Er wordt onmiddellijk een stukje VBA-code toegevoegd Public Function SurfaceCirclel() End Function

9.2 Een functie oproepen In de Excel-omgeving vinden we de functie al terug in de categorie “Door gebruiker gedefinieerd” (User Defined).

Uiteraard is er nog geen verwerking en zijn er nog geen argumenten gedefinieerd voor de functie.

31


9.3 De functie finaliseren We kunnen de code verder uitschrijven om de oppervlakte te berekenen maar het is ook de bedoeling dat we via het argumentenvenster de straal als argument kunnen opgeven. Plaats hiervoor een variabele tussen de haakjes van de functie als argument (in ons geval “straal”). Die variabele kan je verder gebruiken in je code.

Wanneer we de functie opnieuw gaan invoegen in Excel krijgen we onderstaand argumentenvenster waarbij we de straal kunnen selecteren en als resultaat wordt in de cel de oppervlakte getoond.

32


10 Het verschil tussen macro’s en functies. Macro’s en functies zijn beide procedures. Een macro is een procedure die geen waarde retourneert. Een functie retourneert wel een waarde, bijvoorbeeld een Integer of een String. In de code van een macro kun je zien dat deze gedeclareerd is met Sub. 


Alternatief kan je macro’s aanroepen in Excel met de toetsencombinatie Alt-F8.

10.1 Macro’s of functies aanroepen In het Venster Direct (Immediate Window) roep je macro’s aan door de naam in te typen, bijvoorbeeld mcrCopyCells (VBA is niet case-sensitive). Het Venster Direct (Immediat window) vind je in de VB editor. Wellicht moet je dit venster nog zichtbaar maken. Dit kan je in het menu van de VBE Beeld (View)

33

Het verschil tussen macro’s en functies. | Excel 2013

Een functie declareer je met Function in plaats van Sub. Je kunt een functie in cellen op een worksheet aanroepen met bijvoorbeeld =SurfaceCirclel(A1). De functie gebruikt de waarde in de cel als argument. In het Venster Direct (Immediate Window) gebruik je: ?SurfaceCircle(3).

34


10.2 Het gebruik van argumenten Een ander verschil tussen macro’s en functies zit in het gebruik van argumenten. Stel dat we de volgende macro hebben: Sub MacroTest(s As String) Debug.Print "Ja, " & s End Sub

Dan roepen we deze in het Venster Direct (Immediate Window) aan door MacroTest (“zeker!”) of MacroTest “zeker!”. De eerste aanroep heet een functie-aanroep, de tweede een macro-aanroep. Hebben we meer dan één argument, zoals in de volgende macro: Sub MacroNewTest(s As String, n as Integer) Debug.Print "Ja, " & s End Sub

dan werkt de functie-aanroep MacroNewTest(“zeker!”, 2) niet meer. We moeten dan de macro-aanroep MacroNewTest “zeker!” , 2 gebruiken.

10.3 Functies geven een waarde terug Functies retourneren een waarde; wanneer we deze waarde in een expressie gebruiken, moeten we de functie aanroepen met een functie-aanroep. Deze functie retourneert heel eenvoudig de meegegeven waarde: Function NewTest(s As String) NewTest = s End Function

Deze kunnen we met een functie-aanroep gebruiken in een Debug.Print statement: Debug.Print NewTest(“Ja, zeker!”). Als we niets met de geretourneerde waarde doen, dan moeten we de functie aanroepen alsof we een macro aanroepen. Dat wil zeggen: met één argument mogen we kiezen, met meer dan één argument moeten we de macro-aanroep gebruiken.

35


Bovenstaande functie kunnen we dus met NewTest (“Ja, zeker!”) of met NewTest “Ja, zeker!” aanroepen. Onderstaande functie: Function AnotherNewTest (s As String, n As Integer) AnotherNewTest = s End Function

Moeten we met AnotherNewTest “Ja, zeker!”, 2 aanroepen. Tenzij we er een vraagteken voor plaatsen ? AnotherNewTest (“Ja, zeker!”, 2), dan retourneerd deze de waarde van de functie.

10.4 Macro aanroep Call Macro’s en functies kun je ook via een Call aanroepen. In dat geval moet je altijd haakjes om de argumenten zetten: Call makro (“zeker!”, 2)

Als je een functie met Call aanroept, kunnen we niets met de geretourneerde waarde doen.

36


11 Macro’s en Functies verspreiden Excel Add-in. Het is bijzonder handig om eigen formules of macro’s te verspreiden. De functionaliteiten die werden gecreeerd worden om deze wijze ook aan andere gebruikers aangeboden. Wij gaan hier een Add-In ontwikkelen en deze verspreiden om ook aan andere gebruikers aan te bieden.

11.1 Excel Add-in Excel Add-in .xlam Een Excel add-in voegt extra functionaliteit en gereedschappen toe. We slaan de extra macro’s en functies op in een module in de VBE, nadien slaan we het bestand op als een Excel Add-in (extensie .xlam).

11.2 Add-In beschikbaar stellen Om een zelf gemaakte Add-In beschikbaar te stellen voegen we deze toe aan Excel. Dit gaat als volgt: 

Open de backstage (Bestand (File)) en ga naar de Excel opties (Excel Options)



Activeer de categorie Invoegtoepassing (Add-Ins)

37

Macro’s en Functies verspreiden Excel Add-in. | Excel 2013



Onderaan in het scherm vind je de groep Beheren (Manage)



Standaard zijn de Excel-invoegtoepassingen (Excel Add-Ins) geselecteerd.



Klik op Start (Go).

In het venster Invoegtoepassingen vind je al enkele geïnstalleerde (en mogelijks geactiveerde) invoegtoepassingen. Wij voegen er zelf nog een aan toe. 

Klik op Bladeren (Browse). 38




Selecteerd het geswenste bestand

11.3 Nieuwe functionaliteiten gebruiken 

Je hebt nu een invoegtoepassing (Add-In) toegevoegd aan Excel en kan de de ingebouwde Macro’s en Functies gebruiken

In de categorie Door gebruiker gedefineerd (User Defined) vind je alle nieuwe functionaliteiten terug.

39


Je kan ook de functies invoeren rechtstreeks in een cel

40


.

EXCEL Inleiding tot Macro s Mod VII. CEVORA vzw - opleidingscentrum van het ANPCB (PC218)

Recommend Documents