Dit e-boek is voorzien van een verborgen, persoonlijk watermerk. Het verspreiden of openbaar maken van dit e-boek is niet toegestaan.

This e-book contains a hidden and personal watermark. Distribution is prohibited.

Inhoud in vogelvlucht

Inleiding

1 Kennismaken met Excel VBA-programmeren

Hoofdstuk 1: Wat is VBA?

Hoofdstuk 2: Meteen in het diepe

2 Hoe VBA werkt met Excel

Hoofdstuk 3: Werken in de Visual Basic Editor

Hoofdstuk 4: Kennismaken met het objectmodel van Excel

Hoofdstuk 5: VBA Sub- en Function-procedures

Hoofdstuk 6: De Excel-macrorecorder gebruiken

3 Programmeerconcepten

Hoofdstuk 7: Essentiële VBA-taalelementen

Hoofdstuk 8: Werken met Range-objecten

Hoofdstuk 9: VBA en werkbladfuncties gebruiken

Hoofdstuk 10: De programmaflow beheren en beslissingen nemen

Hoofdstuk 11: Automatische procedures en gebeurtenissen

Hoofdstuk 12: Technieken voor foutafhandeling

Hoofdstuk 13: Technieken voor bugbestrijding

Hoofdstuk 14: Voorbeelden van VBA-programmering

4 Communiceren met je gebruikers

Hoofdstuk 15: Simpele dialoogvensters

Hoofdstuk 16: De beginselen van UserForms

Hoofdstuk 17: UserForm-besturingselementen gebruiken

Hoofdstuk 18: Technieken en trucs voor UserForms

Hoofdstuk 19: Je macro’s benaderen via de gebruikersinterface

5 Alles bij elkaar brengen

Hoofdstuk 20 : Werkbladfuncties maken – en het kunnen navertellen

Hoofdstuk 21: Excel-invoegtoepassingen maken

6 Het deel van de tientallen

Hoofdstuk 22: Tien handige VBE-tips

Hoofdstuk 23: Hulpbronnen voor VBA

Hoofdstuk 24: Tien do’s and don’ts in VBA

Inhoud

Inleiding

DEEL 1 KENNISMAKEN MET EXCEL VBA-PROGRAMMEREN

HOOFDSTUK 1: Wat is VBA?

Oké, wat is VBA?

Wat kun je doen met VBA?

Een hoop tekst invoegen

Een taak automatiseren die je vaak uitvoert

Terugkerende operaties automatiseren

Een zelfgemaakte opdracht creëren

Een eigen opdrachtknop creëren

Nieuwe werkbladfuncties ontwikkelen

Eigen invoegtoepassingen creëren voor Excel

Voordelen en nadelen van VBA

Voordelen van VBA

Nadelen van VBA

VBA in een notendop

Excel-compatibiliteit

HOOFDSTUK 2: Meteen in het diepe

Maar eerst even dit

Wat je gaat doen

De eerste stappen zetten

De macro opnemen

De macro testen

De macro onderzoeken

De macro wijzigen

Werkmappen opslaan die macro’s bevatten

Macrobeveiliging begrijpen

Meer onthullen over de macro NaamEnTijd

DEEL 2 HOE VBA WERKT MET EXCEL

HOOFDSTUK 3: Werken in de Visual Basic Editor

Wat is de Visual Basic Editor?

De VBE activeren

VBE-componenten begrijpen

Werken met de Projectverkenner

Een nieuwe VBA-module toevoegen

Een VBA-module verwijderen

Objecten exporteren en importeren

Werken met een codevenster

Vensters minimaliseren en maximaliseren

Een module creëren

VBA-code in een module krijgen

Code direct invoeren

De macrorecorder gebruiken

VBA-code kopiëren

De VBA-omgeving aanpassen

Het tabblad Editor gebruiken

Het tabblad Opmaak in editor gebruiken

Het tabblad Algemeen gebruiken

Het tabblad Dokken gebruiken

HOOFDSTUK 4: Kennismaken met het objectmodel van Excel

Excel is een object?

De objectenhiërarchie afdalen

Verzamelingen begrijpen

Verwijzen naar objecten

Navigeren door de hiërarchie

Objectverwijzingen vereenvoudigen

Eigenschappen en methoden van objecten

Objecteigenschappen

Objectmethoden

Objectgebeurtenissen

Meer te weten komen

VBA’s Help-systeem gebruiken

Het Objectenoverzicht gebruiken

Automatisch eigenschappen en methoden vermelden

HOOFDSTUK 5: VBA Sub- en Function-procedures

Subs versus Functions

Sub-procedures

Function-procedures

Subs en Functions een naam geven

Sub-procedures uitvoeren

De Sub-procedure direct uitvoeren

De procedure uitvoeren vanuit het dialoogvenster Macro

Een macro uitvoeren met een sneltoets

De procedure uitvoeren vanuit een knop of vorm

De procedure uitvoeren vanuit een andere procedure

Function-procedures uitvoeren

De functie aanroepen vanuit een Sub-procedure

Een functie aanroepen vanuit een werkbladformule

HOOFDSTUK 6: De Excel-macrorecorder gebruiken

De beginselen van opnemen

Voorbereiden op opnemen

Relatief of absoluut?

Opnemen in absolute modus

Opnemen in relatieve modus

Wat wordt er opgenomen?

Opnameopties

Macronaam

Sneltoets

Macro opslaan in

Beschrijving

Is dit ding efficiënt?

DEEL 3 PROGRAMMEERCONCEPTEN

HOOFDSTUK 7: Essentiële VBA-taalelementen

Opmerkingen gebruiken in je VBA-code

Variabelen, constanten en gegevenstypen gebruiken

Variabelen begrijpen

Wat zijn de gegevenstypen van VBA?

Variabelen declareren en een bereik geven

Werken met constanten

Kant-en-klare constanten

Werken met strings

Werken met datums

Toewijzingsstatements gebruiken

Voorbeelden van toewijzingsstatements

Over het isgelijkteken

Operatoren

Werken met arrays

Arrays declareren

Multidimensionale arrays

Dynamische arrays

Labels gebruiken

HOOFDSTUK 8: Werken met Range-objecten

Een snelle opfrisser

Andere manieren om naar een bereik te verwijzen

De eigenschap Cells

De eigenschap Offset

Enkele nuttige eigenschappen van Range-objecten

De eigenschap Value

De eigenschap Text

De eigenschap Count

De eigenschappen Column en Row

De eigenschap Address

De eigenschap HasFormula

De eigenschap Font

De eigenschap Interior

De eigenschap Formula

De eigenschap NumberFormat

Enkele nuttige methoden van het Range-object

De methode Select

De methoden Copy en Paste

De methode Clear

De methode Delete

HOOFDSTUK 9: VBA en werkbladfuncties gebruiken

Wat is een functie?

Ingebouwde VBA-functies gebruiken

Voorbeelden van VBA-functies

VBA-functies die meer doen dan een waarde teruggeven

VBA-functies ontdekken

Werkbladfuncties gebruiken in VBA

Voorbeelden van werkbladfuncties

Werkbladfuncties invoeren

Meer over het gebruiken van werkbladfuncties

Zelfgemaakte functies gebruiken

HOOFDSTUK 10: De programmaflow beheren en beslissingen nemen

Go with the flow

Het GoTo-statement

Beslissingen, beslissingen

De structuur If-Then

De structuur Select Case

Je code door een lus halen

For-Next-loops

Do-While-loop

Do-Until-loop

For Each-Next-loops gebruiken bij Verzamelingen

HOOFDSTUK 11: Automatische procedures en gebeurtenissen

Je voorbereiden op de grote gebeurtenis

Zijn gebeurtenissen nuttig?

Eventhandlers programmeren

Waar moet de VBA-code staan?

Een eventhandler-procedure schrijven

Inleidende voorbeelden

Het event Open voor een werkmap

Het event BeforeClose voor een werkmap

Het event BeforeSave voor een werkmap

Voorbeelden van Activation-events

Activerings- en deactiveringsevents in een werkblad

Activerings- en deactiveringsevents in een werkmap

Activeringsevents van een werkmap

Andere werkbladgerelateerde events

Het event BeforeDoubleClick

Het event BeforeRightClick

Het event Change

Gebeurtenissen die losstaan van objecten

Het event OnTime

Toetsaanslag-events

HOOFDSTUK 12: Technieken voor foutafhandeling

Typen fouten

Een foutief voorbeeld

De macro is niet helemaal perfect

De macro is nog steeds niet perfect

Is de macro al perfect?

Vergeet perfectie

Fouten op een andere manier afhandelen

Nogmaals de procedure EnterSquareRoot

Over het statement On Error

Fouten afhandelen: de details

Hervatting na een fout

Foutafhandeling in een notendop

Weten wanneer je fouten moet negeren

Specifieke fouten identificeren

Een opzettelijke fout

HOOFDSTUK 13: Technieken voor bugbestrijding

Soorten bugs

Bugs identificeren

Debuggingtechnieken

Je code onderzoeken

De functie MsgBox gebruiken

Debug.Print-statements invoegen

De VBA-foutopsporing gebruiken

Over de VBA-foutopsporing

Breakpoints instellen in je code

Het venster Controles gebruiken

Het venster Lokale variabelen gebruiken

Tips voor minder bugs

HOOFDSTUK 14: Voorbeelden van VBA-programmering

Werken met bereiken

Een bereik kopiëren

Een bereik met variabele grootte kopiëren

Selecteren tot aan het einde van een rij of kolom

Een rij of kolom selecteren

Een bereik verplaatsen

Efficiënt door een bereik loopen

Efficiënt door een bereik loopen (deel 2)

Vragen om een celwaarde

Het selectietype bepalen

Een meervoudige selectie identificeren

Excel-instellingen veranderen

Booleaanse instellingen veranderen

Niet-booleaanse instellingen veranderen

Werken met grafieken

AddChart versus AddChart2

Het grafiektype wijzigen

Een lus uitvoeren door de verzameling ChartObjects

Grafiekeigenschappen wijzigen

Grafiekopmaak toepassen

Snelheidstips voor VBA

Scherm bijwerken uitzetten

Automatische berekening uitschakelen

Die vervelende waarschuwingen elimineren

Objectverwijzingen vereenvoudigen

Variabeletypen declareren

De structuur With-End With gebruiken

DEEL 4 COMMUNICEREN MET JE GEBRUIKERS

HOOFDSTUK 15: Simpele dialoogvensters

Alternatieven voor UserForms

De functie MsgBox

Een simpel berichtvenster weergeven

Een respons krijgen van een berichtvenster

Berichtvensters aanpassen

De functie InputBox

De schrijfwijze van de InputBox-functie

Een InputBox-voorbeeld

Een ander type InputBox

De methode GetOpenFilename

De syntaxis voor de methode GetOpenFilename

Een GetOpenFilename-voorbeeld

De methode GetSaveAsFilename

Een mapnaam krijgen

Excels ingebouwde dialoogvensters weergeven

HOOFDSTUK 16: De beginselen van UserForms

Weten wanneer je een UserForm moet gebruiken

UserForms maken: een overzicht

Werken met UserForms

Een nieuw UserForm invoegen

Besturingselementen toevoegen aan een UserForm

De eigenschappen veranderen van een UserForm-besturingselement

Het codevenster van het UserForm bekijken

Een UserForm weergeven

Informatie van een UserForm gebruiken

Een UserForm-voorbeeld

Het UserForm maken

De opdrachtknoppen toevoegen

De keuzerondjes toevoegen

Eventhandler-procedures toevoegen

Een macro maken om het dialoogvenster weer te geven

De macro beschikbaar maken

De macro testen

HOOFDSTUK 17: UserForm-besturingselementen gebruiken

Aan de slag met dialoogvensterbesturingselementen

Besturingselementen toevoegen

Kennismaken met de eigenschappen van besturingselementen

Dialoogvensterbesturingselementen: de details

Het besturingselement Selectievakje (CheckBox)

Het besturingselement Keuzelijst met invoervak (ComboBox)

Het besturingselement Opdrachtknop (CommandButton)

Het besturingselement Groepsvak (Frame)

Het besturingselement Afbeelding (Image)

Het besturingselement Bijschrift (Label)

Het besturingselement Keuzelijst (ListBox)

Het besturingselement Meerbladig (MultiPage)

Het besturingselement Keuzerondje (OptionButton)

Het besturingselement RefEdit

Het besturingselement Schuifbalk (ScrollBar)

Het besturingselement Kringveld (SpinButton)

Het besturingselement Tabstrook (TabStrip)

Het besturingselement Tekstvak (TextBox)

Het besturingselement Wisselknop (ToggleButton)

Werken met besturingselementen in dialoogvensters

Besturingselementen verplaatsen, vergroten en verkleinen

Besturingselementen uitlijnen en uit elkaar plaatsen

Toetsenbordgebruikers tegemoetkomen

Een UserForm testen

De vormgeving van dialoogvensters

HOOFDSTUK 18: Technieken en trucs voor UserForms

Dialoogvensters gebruiken

Een UserForm-voorbeeld

Het dialoogvenster maken

Code schrijven om het dialoogvenster weer te geven

De macro beschikbaar maken

Je dialoogvenster uitproberen

Eventhandler-procedures toevoegen

De gegevens valideren

Nu werkt het dialoogvenster

Een Keuzelijst-voorbeeld

Een keuzelijst vullen

Het geselecteerde item bepalen

Meerdere selecties bepalen

Een bereik selecteren

Meerdere sets met keuzerondjes gebruiken

Een kringveld en een tekstvak gebruiken

Een UserForm gebruiken als progressiebalk

Het voortgangsdialoogvenster creëren

De procedures

Hoe dit voorbeeld werkt

Een modeless dialoogvenster met tabbladen maken

Een grafiek weergeven in een UserForm

Een checklist voor dialoogvensters

HOOFDSTUK 19: Je macro’s benaderen via de gebruikersinterface

Het lint aanpassen

Het lint handmatig aanpassen

Een macro toevoegen aan het lint

Het lint aanpassen met XML

Snelmenu’s aanpassen

Een nieuw item toevoegen aan het snelmenu van een cel

Wat is er anders sinds Excel 2007?

DEEL 5 ALLES BIJ ELKAAR BRENGEN

Hoofdstuk 20 : Werkbladfuncties maken – en het kunnen navertellen

Waarom aangepaste functies creëren?

De beginselen van VBA-functies

Functies schrijven

Werken met functieargumenten

Een functie zonder argument

Een functie met één argument

Een functie met twee argumenten

Een functie met een optioneel argument

Kennismaken met wrapperfuncties

De functie NumberFormat

De functie ExtractElement

De functie SayIt

De functie IsLike

Werken met functies die een array teruggeven

Een array van maandnamen teruggeven

Een gesorteerde lijst teruggeven

Het dialoogvenster Functie invoegen gebruiken

De functiebeschrijving weergeven

Argumentbeschrijvingen toevoegen

HOOFDSTUK 21: Excel-invoegtoepassingen maken

Oké... maar waar hebben we het eigenlijk over?

Waarom invoegtoepassingen maken?

Werken met invoegtoepassingen

De beginselen van invoegtoepassingen

Een voorbeeld van een invoegtoepassing bekijken

De werkmap instellen

De werkmap testen

Beschrijvende informatie toevoegen

Je VBA-code beschermen

De invoegtoepassing creëren

De invoegtoepassing openen

De invoegtoepassing delen

De invoegtoepassing wijzigen

DEEL 6 HET DEEL VAN DE TIENTALLEN

HOOFDSTUK 22: Tien handige VBE-tips

Blokopmerkingen toepassen

Meerdere coderegels ineens kopiëren

Heen en weer springen tussen modules en procedures

Teleporteren naar je functies

In de juiste procedure blijven

Stapsgewijs door je code lopen

Naar een specifieke coderegel stappen

De code stoppen op een vooraf bepaald punt

Het begin en einde zien van waarden van variabelen

AutoSyntaxiscontrole uitschakelen

HOOFDSTUK 23: Hulpbronnen voor VBA

Laat Excel code voor je schrijven

Code pikken van internet

Je voordeel doen met gebruikersforums

Blogs van experts bezoeken

Op YouTube zoeken naar instructievideo’s

Meedoen aan (online) cursussen

Leren van het Microsoft Office Dev Center

De andere Excel-bestanden in je organisatie ontleden

Je lokale Excel-goeroe om advies vragen

HOOFDSTUK 24: Tien do’s and don’ts in VBA

Declareer altijd alle variabelen

Verwar wachtwoorden niet met veiligheid

Ruim je code op

Stop niet alles in één procedure

Overweeg ook andere software

Ga er niet van uit dat iedereen macro’s inschakelt

Ga beslist experimenteren

Ga er niet van uit dat je code zal werken met andere Excel-versies

Houd je gebruikers in gedachten

Vergeet je back-ups niet

Over de auteurs

Michael Alexander is een Microsoft Certified Application Developer (MCAD) en schrijver van verscheidene boeken over de geavanceerde analyse van bedrijfsgegevens met Microsoft Access en Microsoft Excel. Hij heeft meer dan 20 jaar ervaring als consultant en ontwikkelaar van Microsoft Office-oplossingen. Mike heeft de titel Microsoft Most Valuable Person gekregen voor zijn aanhoudende bijdragen aan de Excel-community. Je kunt hem vinden op www.datapigtechnologies.com.

John Walkenbach, alias Mr. Spreadsheet, is de vooraanstaande autoriteit op het gebied van spreadsheet software. Hij is de auteur van meer dan 50 boeken over spreadsheets, waaronder Excel 2013 Power Programming with VBA, Excel 2013 Formulas, en de Amerikaanse beststeller Excel 2013 Bible. Walkenbach schreef honderden artikelen en software-beoordelingen, ook ontwikkelde hij de prijswinnende Power Utility Pak add-in voor Excel.

Inleiding

Gegroet, toekomstige Excel-programmeur... Je hebt ongetwijfeld je redenen om een boek over VBA-programmeren ter hand te nemen. Misschien heb je een nieuwe baan (gefeliciteerd). Misschien probeer je enkele steeds weer terugkerende rekentaken te automatiseren. Misschien ben je onderhuids gewoon een nerd. Wat de reden ook is, bedankt dat je dit boek hebt gekozen.

In dit boek vind je alles wat je nodig hebt om snel aan de slag te gaan met VBA. Ook al heb je geen flauw idee wat programmeren überhaupt inhoudt, dit boek kan je helpen. Anders dan bij de meeste programmeerboeken vind je in dit boek juist die informatie die je nodig hebt om je VBA-programmeervaardigheden snel in de steigers te zetten.

Over dit boek

In een grote boekenwinkel (in de stad of online) vind je allerlei Excel-boeken. Het volgende overzicht is handig om te kijken of dit boek echt iets voor jou is. Dit boek:

is ontworpen voor gemiddelde tot gevorderde Excel-gebruikers die aan de slag willen met Visual Basic for Applications (VBA);

vereist geen programmeerervaring;

behandelt de meest gebruikte opdrachten;

is geschikt voor Excel 2013, Excel 2016 of Excel 2019;

maakt je soms even aan het lachen.

Als je Excel 2003 gebruikt, heb je een veel ouder boek nodig (en een hug). Als je Excel 2007 of 2010 gebruikt, zou dit boek geschikt kunnen zijn, maar sommige dingen zijn wel veranderd. Dan ben je waarschijnlijk beter af met de vorige editie.

O ja, dit is niet een inleidend boek over Excel. Als je op zoek bent naar een algemeen Excel-boek, kijk dan eens naar Excel 2019 voor Dummies van Greg Harvey. Dit boek is ook beschikbaar in edities voor eerdere versies van Excel.

Let wel op dat de titel van dit boek niet is ‘De complete Excel VBA-programmeergids voor Dummies’. In dit boek worden niet alle aspecten van Excel-programmeren behandeld. Maar waarschijnlijk wil je ook niet alles over dit onderwerp weten.

Verplichte paragraaf over typografische conventies

Alle computerboeken hebben zo’n paragraaf. Je kunt hem lezen of overslaan, zie maar.

Soms verwijzen we naar een toetsencombinatie, wat inhoudt dat je de ene toets ingedrukt houdt terwijl je op een andere toets drukt. Zo betekent Ctrl+Z dat je de Ctrl-toets ingedrukt houdt terwijl je op Z drukt.

Voor menuopdrachten gebruik ik een specifiek leesteken om items op het lint of in het menu te scheiden. Je gebruikt bijvoorbeeld de volgende opdracht om een benoemd cellenbereik te maken in een werkblad:

Formules → Gedefinieerde namen → Naam definiëren

Formules is het tabblad boven aan het lint, Gedefinieerde namen is de opdrachtgroep en Naam definiëren is de feitelijke opdracht.

De Visual Basic Editor maakt nog steeds gebruik van ouderwetse menu’s en werkbalken. Extra → Opties betekent dus: kies het menu Extra en kies vervolgens het menu-item Opties.

Excel-programmeren gaat over het ontwikkelen van code – ofwel de instructies die Excel volgt. Alle code in dit boek verschijnt in een proportioneel lettertype, zoals hier

Range(“A1:A12”).Select

Sommige coderegels zijn zo lang dat ze niet binnen de marges van dit boek passen. In die gevallen maak ik gebruik van de standaard-VBA-leestekens voor doorlopende regels: een spatie gevolgd door een underscore. Een voorbeeld:

Selection.PasteSpecial Paste:=xlValues, _

Operation:=xlNone, SkipBlanks:=False, _

Transpose:=False

Wanneer je deze code invoert, typ hem dan zoals het hier geschreven staat of plaats het geheel op één regel (zonder spatie en underscore).

Controleer je beveiligingsinstellingen

Het is een wrede wereld. Het lijkt wel of een of andere oplichter altijd van je probeert te profiteren of problemen veroorzaakt. De computerwereld is al net zo wreed. Waarschijnlijk ben je op de hoogte van computervirussen die je systeem veel narigheid kunnen bezorgen. Maar wist je ook dat computervirussen zich ook kunnen schuilhouden in een Excel-bestand? Het is waar. Het is zelfs vrij eenvoudig om met behulp van VBA een computervirus te schrijven. Een onbekende gebruiker kan een Excel-bestand openen en het virus verspreiden naar andere Excel-werkmappen en andere systemen.

In de loop van de jaren is Microsoft zich steeds meer gaan bezighouden met veiligheidskwesties. Dit is een goede zaak, maar het betekent ook dat Excel-gebruikers dienen te begrijpen hoe dingen werken. Je kunt de beveiligingsinstellingen van Excel controleren via Bestand → Opties → Vertrouwenscentrum → Instellingen voor het Vertrouwenscentrum. Je vindt er een overvloed aan opties, en van sommige mensen die erheen zijn gegaan is sindsdien nooit meer iets vernomen.

Als je (aan de linkerkant van het dialoogvenster Vertrouwenscentrum) op de knop Macro-instellingen klikt, vind je daar de volgende opties:

Alle macro’s uitschakelen zonder melding. Macro’s zullen niet werken, wat je ook doet.

Alle macro’s uitschakelen, met melding. Wanneer je een werkmap met macro’s opent, zie je de Berichtenbalk met een optie waarop je kunt klikken om macro’s in te schakelen, of (als het venster van de Visual Basic Editor is geopend) een melding waarin je gevraagd wordt of je macro’s wilt inschakelen.

Alle macro’s uitschakelen met uitzondering van macro’s die digitaal zijn ondertekend. Alleen macro’s met een digitale ondertekening kunnen worden uitgevoerd (maar voor ondertekeningen die je niet als vertrouwd hebt gemarkeerd, krijg je nog steeds de beveiligingswaarschuwing).

Alle macro’s inschakelen. Alle macro’s worden uitgevoerd zonder waarschuwing. Deze optie raden we af omdat er dan potentieel gevaarlijke code kan worden uitgevoerd.

Overweeg dit scenario: je schrijft een week aan een geweldig VBA-programma dat een grote impact zal hebben op het bedrijf waar je werkt. Je test het grondig en stuurt het dan naar je baas. Hij laat je naar kantoor komen en beweert dat je programma helemaal niets doet. Wat is er aan de hand? De kans is groot dat de beveiligingsinstellingen van je baas niet toestaan dat macro’s worden uitgevoerd. Of misschien is hij ingegaan op de standaardsuggestie van Microsoft en heeft hij macro’s uitgeschakeld bij het openen van het bestand.

Conclusie? Het feit dat een Excel-werkmap een macro bevat, is nog geen garantie dat de macro ooit wordt uitgevoerd. Dit hangt helemaal af van de beveiligingsinstelling en of de gebruiker ervoor kiest om macro’s in- of uit te schakelen voor dat bestand.

Om met dit boek te werken, moet je macro’s inschakelen voor de bestanden waarmee je werkt. Ons advies is om de tweede beveiligingsinstelling te kiezen. Dan kun je bij het openen van een zelfgemaakt bestand eenvoudig de macro’s inschakelen. Als je een bestand opent van iemand die je niet kent, kun je de macro’s beter uitschakelen en de VBA-code controleren op destructieve of kwalijke inhoud. In de regel is verdachte VBA-code vrij makkelijk te herkennen.

Een andere optie is om een vertrouwde map aan te wijzen. Kies Bestand → Opties → Vertrouwenscentrum → Instellingen voor het Vertrouwenscentrum. Selecteer de optie Vertrouwde locaties en wijs een specifieke map aan als een vertrouwde locatie. Sla daar je vertrouwde werkmappen op, en Excel zal je niet meer lastigvallen over het inschakelen van macro’s.

Dwaze veronderstellingen

Schrijvers hebben vaak een bepaald lezerspubliek voor ogen. De hypothetische lezer van dit boek kunnen we als volgt beschrijven:

Je hebt toegang tot een pc, thuis en/of op het werk. En deze computers beschikken over een internetverbinding.

Je hebt Excel 2013, 2016 of 2019.

Je gebruikt al jaren een computer.

Je gebruikt Excel regelmatig voor je werk en je vindt dat je er beter in thuis bent dan de gemiddelde computergebruiker.

Je moet Excel bepaalde dingen laten doen die je tot nu toe niet gelukt zijn.

Je hebt weinig of geen programmeerervaring.

Je begrijpt dat het Help-systeem in Excel daadwerkelijk zijn nut heeft. Want je snapt wel dat dit boek niet alles kan behandelen. Als je goed weet om te gaan met de Help-functie, kun je zelf enkele van de ontbrekende onderdelen invullen.

Je moet iets voor elkaar krijgen en je kunt niet het geduld opbrengen om dikke en saaie computerboeken door te ploeteren.

Pictogrammen in dit boek

Sla informatie met dit pictogram niet over. Het gaat dan om een snelle manier om iets te doen die je veel tijd kan besparen (zodat je misschien wel op een redelijke tijd naar huis kunt).

Dit pictogram wordt ook gebruikt om je erop te wijzen dat de besproken code online beschikbaar is in voorbeeldbestanden. Download de code om je al dat typewerk te besparen.

Dit pictogram wijst op informatie die je diep in je geheugen dient op te slaan voor later gebruik.

Dit pictogram verwijst naar wat technischere tekst. Misschien vind je het interessant, maar als je haast hebt kun je het gerust overslaan.

Lees altijd de tekst waar dit pictogram bij staat. Anders kun je je gegevens kwijtraken, je computer opblazen of een nucleaire meltdown veroorzaken. Misschien verpest het zelfs je hele dag.

Voorbeeldbestanden online

De voorbeeldbestanden bij dit boek kun je downloaden van:

www.dummies.nl/downloads

Hiermee bespaar je jezelf een hoop typewerk. En wat nog mooier is, je kunt ermee spelen en allerlei wijzigingen uitproberen. Experimenteren is in feite de beste manier om VBA onder de knie te krijgen.

Hoe nu verder

Dit boek bevat alles wat je nodig hebt om VBA-programmeren te leren boven beginnersniveau. Het boek begint hoofdstuk voor hoofdstuk met de basisbeginselen van het opnemen van macro’s en builds.

Als Excel-macro’s helemaal nieuw voor je zijn, begin dan bij deel 1 om de beginselen van het opnemen van macro’s op te frissen. Als je ervaring hebt met het opnemen van macro’s, maar meer wilt begrijpen van de achterliggende VBA, ga dan naar deel 2. Daar leer je hoe VBA werkt en hoe je je eigen code kunt implementeren.

Tot slot, als je al vertrouwd bent met programmeerconcepten en een snelle blik wilt werpen op de geavanceerdere technieken zoals het maken van aangepaste functies en invoegtoepassingen, neem dan een kijkje in deel 4. Daarnaast vind je voor in het boek ook nog de spiekbrief met handige sneltoetsen.

1
Kennismaken met Excel VBA-programmeren

IN DIT DEEL . . .

Leer je Visual Basic for Applications kennen.

Krijg je voorbeelden van dingen die je kunt doen met VBA.

Werk je een echte Excel-programmeersessie door.

Begrijp je hoe Excel omgaat met de beveiliging van macro’s.

IN DIT HOOFDSTUK

Een conceptueel overzicht van VBA

Ontdekken wat je kunt doen met VBA

De voordelen en nadelen van VBA leren kennen

Het fijne te weten komen over VBA

Compatibel blijven met Excel

Hoofdstuk 1:
Wat is VBA?

Misschien kun je niet wachten om je op het programmeren in VBA te storten, maar maak even pas op de plaats. In dit hoofdstuk vind je geen oefenmateriaal. Maar het bevat wel essentiële achtergrondinformatie die je helpt om een Excel-programmeur te worden. Met andere woorden, dit hoofdstuk effent de weg voor alles wat nog komen gaat en laat je zien waar Excel-programmeren precies thuishoort in het totaalplan van het universum. Het is niet zo saai als je misschien denkt, dus weersta de verleiding om meteen door te bladeren naar hoofdstuk 2.

Oké, wat is VBA?

VBA, dat staat voor Visual Basic for Applications, is een programmeertaal die is ontwikkeld door Microsoft – je weet wel, dat bedrijf dat elke paar jaar weer een nieuwe versie van Windows aan je probeert te slijten. Net als bij de andere onderdelen van Microsoft Office is de VBA-taal (gratis) ingebouwd in Excel. Kortgezegd, VBA is het gereedschap dat je gebruikt om programma’s te ontwikkelen voor Excel.

Stel je voor dat een intelligente robot alles afweet van Excel. Deze robot kan instructies lezen en ook Excel heel snel en accuraat bedienen. Als je wilt dat de robot iets doet in Excel, schrijf je een aantal robotinstructies door gebruik te maken van speciale codes. Dan draag je de robot op om jouw instructies te volgen terwijl jij achteroverleunt en van je koffie drinkt. Dat is eigenlijk waar VBA over gaat: een codetaal voor robots. Maar bedenk wel dat Excel niet wordt geleverd met een robot of koffie.

EVEN IETS OVER TERMINOLOGIE

De terminologie van Excel-programmeren kan wat verwarrend zijn. Zo is VBA een programmeertaal, maar het doet ook dienst als een macrotaal. In deze context is een macro een set instructies die Excel uitvoert om toetsaanslagen en muisacties na te bootsen. Hoe noem je iets dat in VBA is geschreven en in Excel wordt uitgevoerd? Is het een macro of een programma? Het Help-systeem van Excel verwijst vaak naar VBA-procedures als macro’s, en dus doen wij dat ook in dit boek. Maar je mag het wat ons betreft ook gerust een programma noemen. Overal in dit boek kom je de term automatiseren tegen. Deze term betekent dat een reeks stappen automatisch wordt uitgevoerd. Als je bijvoorbeeld een macro schrijft die kleur toevoegt aan bepaalde cellen, het werkblad afdrukt en dan de kleur weer verwijdert, dan heb je deze drie stappen geautomatiseerd. Tussen haakjes, het woord macro betekent niet Messy And Confusing Repeated Operation. Het is afkomstig van het Griekse woord makros, dat groot betekent. En dat geldt ook voor de salarisverhoging die jou te wachten staat als je een expert wordt in het programmeren van macro’s.

Wat kun je doen met VBA?

Je weet waarschijnlijk wel dat mensen Excel voor duizend-en-een dingen gebruiken. Om maar enkele voorbeelden te noemen:

wetenschappelijke data analyseren;

begrotingen en projecties maken;

facturen en andere formulieren maken;

grafieken maken van gegevens;

lijsten maken, zoals klantennamen, cijfers voor leerlingen of cadeau-ideeën;

enzovoort enzovoort.

De lijst is eindeloos, maar je snapt het wel. Het punt is dat Excel voor de meest uiteenlopende taken wordt gebruikt, en dat iedereen die dit boek leest eigen behoeften en verwachtingen heeft over Excel. Wat vrijwel alle lezers gemeen hebben, is de behoefte om een bepaald aspect van Excel te automatiseren. En daar, beste lezer, hebben we nu VBA voor.

Zo zou je een VBA-programma kunnen maken om bepaalde getallen te importeren en vervolgens je maandelijkse verkoopcijfers op te maken en af te drukken. Nadat je het programma hebt ontwikkeld en getest, kun je de macro uitvoeren met een enkele opdracht, waarna Excel automatisch vele tijdrovende procedures uitvoert. In plaats van dat je je door een hele reeks opdrachten heen moet worstelen, kun je op een knop klikken en iets leuks gaan doen terwijl de macro het werk doet.

In de volgende paragrafen beschrijven we enkele veelgebruikte toepassingen van VBA-macro’s. Misschien dat daar iets bij zit waar je blij van wordt.

Een hoop tekst invoegen

Als je vaak je bedrijfsnaam, adres en telefoonnummer moet typen in je werkbladen, kun je een macro maken die dat typen van je overneemt. En je kunt hier zover mee gaan als je wilt. Je zou bijvoorbeeld een macro kunnen maken die automatisch een lijst van al je verkopers invoert.

Een taak automatiseren die je vaak uitvoert

Stel dat je een salesmanager bent en een rapport van de eindemaandcijfers moet voorbereiden om je baas tevreden te houden. Als de taak niet gecompliceerd is, kun je een VBA-programma ontwikkelen om dat voor jou te doen. Je baas zal onder de indruk zijn van de consistente hoge kwaliteit van je rapporten en je wordt gepromoveerd naar een functie waar je absoluut niet voor gekwalificeerd bent.

Terugkerende operaties automatiseren

Als je dezelfde handeling moet uitvoeren op, zeg, twaalf Excel-werkmappen, kun je een macro opnemen terwijl je de taak uitvoert op de eerste werkmap en vervolgens de macro de handeling laten herhalen op de andere werkmappen. Het mooie hiervan is dat Excel nooit zal klagen over verveling. De macrorecorder van Excel is vergelijkbaar met het opnemen van beelden met een camcorder. Maar je hebt er geen camera voor nodig en de batterij hoef je ook nooit op te laden.

Een zelfgemaakte opdracht creëren

Geef je vaak dezelfde reeks Excel-opdrachten? Zo ja, bespaar dan wat tijd door een macro te ontwikkelen die deze opdrachten combineert tot een enkele zelfgemaakte opdracht die je kunt uitvoeren met een enkele toetsaanslag of muisklik. Je zult er waarschijnlijk niet heel veel tijd mee besparen, maar je wordt er wel accurater van. En je buurman aan het volgende bureau zal onder de indruk zijn.

Een eigen opdrachtknop creëren

Je kunt de werkbalk Snelle toegang aanpassen met knoppen die jouw eigen macro’s uitvoeren. Kantoorpersoneel is vaak erg onder de indruk van knoppen die magische dingen doen. En als je je collega’s echt wilt verbazen, kun je zelfs nieuwe knoppen toevoegen aan het lint.

Nieuwe werkbladfuncties ontwikkelen

Hoewel Excel beschikt over honderden ingebouwde functies (zoals SOM en GEMIDDELDE), kun je zelfgemaakte werkbladfuncties creëren die je formules aanzienlijk kunnen vereenvoudigen. Het zal je verbazen hoe makkelijk dit is. (Hoe dit werkt, lees je in hoofdstuk 20.) En het dialoogvenster Functie invoegen geeft jouw eigen functies weer alsof ze zijn ingebouwd. Mooi spul.

Eigen invoegtoepassingen creëren voor Excel

Waarschijnlijk ben je vertrouwd met enkele invoegtoepassingen die bij Excel worden geleverd. Het Analysis ToolPak, bijvoorbeeld, is een populaire invoegtoepassing. Je kunt VBA gebruiken om je eigen gespecialiseerde invoegtoepassingen te ontwikkelen.

Voordelen en nadelen van VBA

In deze paragraaf schetsen we de goede zaken van VBA, maar ook de minder goede dingen.

Voordelen van VBA

Je kunt vrijwel alles wat je doet in Excel automatiseren. Hiervoor schrijf je instructies die Excel uitvoert. Het heeft meerdere voordelen om een taak met VBA te automatiseren:

Excel voert de taak altijd op precies dezelfde manier uit. (In de meeste gevallen is consistentie een goede zaak.)

Excel voert de taak veel sneller uit dan jij met de hand kunt doen.

Als je een goede macroprogrammeur bent, voert Excel de taken altijd uit zonder fouten (wat we waarschijnlijk niet over jou kunnen zeggen, hoe nauwgezet je ook bent).

Als je een en ander goed hebt opgezet, kan ook iemand die niets afweet van Excel de taak uitvoeren door de macro te draaien.

Je kunt in Excel dingen doen die anders onmogelijk zijn. Dat zal je populariteit op kantoor zeker ten goede komen.

Bij lange en tijdrovende taken hoef je niet verveeld achter je computer te blijven zitten. Excel doet het werk, terwijl jij rondhangt bij de koffieautomaat.

Nadelen van VBA

Natuurlijk moeten we evengoed stilstaan bij de nadelen (of potentiële nadelen) van VBA:

Je moet weten hoe je programma’s schrijft in VBA (maar daarom heb je ook dit boek gekocht, nietwaar?). Gelukkig is het niet zo moeilijk als je zou denken.

Andere mensen die jouw VBA-programma’s moeten gebruiken moeten over hun eigen exemplaar van Excel beschikken. Het zou mooi zijn als je op een knop kon drukken die je Excel/VBA-toepassing omtovert in een zelfstandig werkend programma, maar dat is niet mogelijk (en zal dat waarschijnlijk ook nooit zijn).

Soms gaat er iets fout. Met andere woorden, je kunt er niet blind op vertrouwen dat je VBA-programma altijd foutloos werkt onder alle omstandigheden. Welkom in de wereld van het debuggen en van de technische ondersteuning (als anderen jouw macro’s gebruiken).

VBA is een bewegend doelwit. Zoals je weet, wordt Excel voortdurend door Microsoft geüpdatet. Microsoft spant zich in om versies compatibel te houden, maar het kan zijn dat jouw VBA-code niet correct werkt in oudere of toekomstige versies van Excel.

VBA in een notendop

Om je alvast een voorproefje te geven van wat je te wachten staat, vatten we hier al even wat dingen samen.

Je voert acties uit in VBA door het schrijven (of opnemen) van code in een VBA-module. Je bekijkt en bewerkt VBA-modules in de Visual Basic Editor (VBE).

Een VBA-module bestaat uit Sub-procedures. Een Sub-procedure is een brok computercode die een bepaalde actie uitvoert op of met objecten (komen we zo op). In het volgende voorbeeld zie je een simpele Sub-procedure met de naam AddEmUp. Dit verbazingwekkende programma geeft bij uitvoering het resultaat weer van 1 plus 1:

Sub AddEmUp()

Sum = 1 + 1

MsgBox “Het antwoord is “ & Sum

End Sub

Een VBA-module kan ook Function-procedures bevatten. Een Function-procedure geeft een enkele waarde terug. Je kunt de procedure aanroepen vanuit een andere VBA-procedure of deze zelfs gebruiken als een functie in een werkbladformule. Hier zie je een voorbeeld van de Function-procedure AddTwo. Deze functie accepteert twee getallen (argumenten genoemd) en geeft de som van die waarden terug:

Function AddTwo(arg1, arg2)

AddTwo = arg1 + arg2

End Function

VBA manipuleert objecten. Excel biedt tientallen objecten die je kunt manipuleren. Voorbeelden van objecten zijn een werkmap, een werkblad, een cellenbereik, een grafiek en een vorm. Er zijn er nog veel meer beschikbaar en je kunt ze manipuleren met VBA-code.

Objecten zijn geordend in een hiërarchie. Objecten kunnen fungeren als containers voor andere objecten. Boven aan de hiërarchie staat Excel. Excel is zelf een object met de naam Application. Het Application-object bevat andere objecten, zoals Workbook-objecten (werkmappen) en AddIn-objecten (invoegtoepassingen). Het Workbook-object kan andere objecten bevatten, zoals Worksheet-objecten (werkbladen) en Chart-objecten (grafieken). Een Worksheet-object kan objecten bevatten zoals Range-objecten (celbereiken) en PivotTable-objecten (draaitabellen). De term objectmodel verwijst naar de rangschikking van deze objecten. (Objectmodelnerds lezen er meer over in hoofdstuk 4.)

Objecten van hetzelfde type vormen een verzameling. Zo bestaat de verzameling Worksheets uit alle werkbladen in een bepaalde werkmap. De verzameling Charts bestaat uit alle grafiekobjecten in een werkmap. Verzamelingen zijn zelf ook objecten.

Je verwijst naar een object door zijn positie in de objectenhiërarchie te specificeren met behulp van een punt als scheidingsteken. Zo kun je naar de werkmap Map1.xlsx verwijzen als:

Application.Workbooks(“Map1.xlsx”)

Dit verwijst naar de werkmap Map1.xlsx in de verzameling Workbooks. De verzameling Workbooks bevindt zich weer in het object Application (ofwel Excel). Als we dit uitbreiden naar een ander niveau, kun je naar Blad1 in Map1.xlsx verwijzen als:

Application.Workbooks(“Map1.xlsx”).Worksheets(“Blad1”)

We kunnen nog verder afdalen en verwijzen naar een specifieke cel (in dit geval cel A1):

Application.Workbooks(“Map1.xlsx”).Worksheets(“Blad1”).Range(“A1”)

Als je bepaalde verwijzingen weglaat, gebruikt Excel de actieve objecten. Als Map1.xlsx de actieve werkmap is, kun je de vorige verwijzing als volgt vereenvoudigen:

Worksheets(“Blad1”).Range(“A1”)

Als je weet dat Blad1 het actieve blad is, kun je de verwijzing nog verder vereenvoudigen:

Range(“A1”)

Objecten hebben eigenschappen. Je kunt een eigenschap zien als een instelling voor een object. Zo heeft een Range-object eigenschappen als Value en Address. Een Chart-object heeft eigenschappen als HasTitle en Type. Je kunt VBA gebruiken om objecteigenschappen vast te stellen en te wijzigen.

Je verwijst naar een eigenschap van een object door de objectnaam te combineren met de eigenschapsnaam, gescheiden door een punt. Zo kun je als volgt verwijzen naar de eigenschap Value in cel A1 op Blad1:

Worksheets(“Blad1”).Range(“A1”).Value

Je kunt waarden toekennen aan variabelen. Een variabele is een benoemd element dat informatie opslaat. Je kunt variabelen gebruiken in je VBA-code om zaken op te slaan als waarden, tekst en eigenschapsinstellingen. Om de waarde in cel A1 op Blad1 toe te wijzen aan een variabele Rente, gebruik je het volgende VBA-statement:

Rente = Worksheets(“Blad1”).Range(“A1”).Value

Objecten hebben methoden. Een methode is een actie die Excel uitvoert met een object. Zo is ClearContents een van de methoden voor een Range-object. Deze methode wist de inhoud van een bereik.

Je specificeert een methode door het object te combineren met de methode, gescheiden door een punt. Zo wist het volgende statement de inhoud van cel A1:

Worksheets(“Blad1”).Range(“A1”).ClearContents

VBA omvat alle concepten van moderne programmeertalen, waaronder variabelen, arrays en loops (lussen). Met andere woorden, als je bereid bent enige tijd te besteden aan het leren van de kneepjes, kun je code schrijven die ongelooflijke dingen doet.

Geloof het of niet, maar de vorige opsomming beschrijft VBA in een notendop. Nu hoef je je alleen nog maar te verdiepen in de details. Om deze reden houdt het boek hier nog niet op.

Excel-compatibiliteit

Dit boek is geschreven voor de desktopversies van Excel 2016 en Excel 2019. Als je niet een van deze versies hebt, loop je het risico hier en daar de draad kwijt te raken.

Als je van plan bent om je Excel/VBA-bestanden te delen met andere gebruikers, is het van cruciaal belang dat je weet welke Excel-versie ze gebruiken. Mensen met oudere versies kunnen niet profiteren van functionaliteit die aan latere versies is toegevoegd. Als je bijvoorbeeld VBA-code schrijft die verwijst naar cel XFD1048576 (de laatste cel in een werkmap), krijgen mensen met een oudere versie dan Excel 2007 een foutmelding omdat werkbladen van vóór Excel 2007 slechts 65.536 rijen en 255 kolommen telden (de laatste cel was IV65536).

Excel 2010 en later hebben ook enkele nieuwe objecten, methoden en eigenschappen. Als je deze in je code gebruikt, krijgen gebruikers met een oudere versie van Excel een foutmelding wanneer ze je macro draaien. En daar krijg jij dan de schuld van.

IN DIT HOOFDSTUK

Een nuttige VBA-macro ontwikkelen: een stapsgewijs voorbeeld

Je acties opnemen met de macrorecorder

Opgenomen code onderzoeken en testen

Een opgenomen macro veranderen

Omgaan met macrobeveiliging

Hoofdstuk 2:
Meteen in het diepe

Als het zwemwater koud is, kun je er maar het beste meteen induiken, anders wordt het alleen maar moeilijker. Dat ga je ook doen in dit hoofdstuk, maar geen zorgen, je hoeft er niet te lang in te blijven.

Tegen de tijd dat je het einde van dit hoofdstuk bereikt, heb je misschien al een beter gevoel over programmeren in Excel en ben je blij dat je de sprong gewaagd hebt. Dit hoofdstuk biedt een stapsgewijze demonstratie van hoe je een simpele maar nuttige VBA-macro ontwikkelt.

Maar eerst even dit

Voordat je jezelf een Excel-programmeur mag noemen, moet je eerst even dit doen. Je moet een kleine verandering aanbrengen zodat Excel boven aan het scherm een nieuw tabblad weergeeft: Ontwikkelaars. Dit is geen moeilijke opgave (en je hoeft het maar één keer te doen). Neem de volgende stappen:

1.    Klik met de rechtermuisknop ergens op het lint en kies Het lint aanpassen in het snelmenu.

2.    Zoek in het rechterdeelvenster van het dialoogvenster Opties voor Excel naar het tabblad Ontwikkelaars.

3.    Markeer het selectievakje naast Ontwikkelaars.

4.    Klik op OK.

Je bent weer terug in Excel met een splinternieuw tabblad: Ontwikkelaars.

Wanneer je op de tab Ontwikkelaars klikt, geeft het lint informatie weer die van belang is voor programmeurs (jij dus!) In figuur 2.1 zie je hoe het lint eruitziet wanneer het tabblad Ontwikkelaars is geselecteerd in Excel 2019.

FIGUUR 2.1: Het tabblad Ontwikkelaars is normaal gesproken verborgen, maar kan makkelijk tevoorschijn worden gehaald.

Wat je gaat doen

In dit hoofdstuk maak je je eerste macro. Die macro doet het volgende:

typt jouw naam in een cel;

plaatst de huidige datum en tijd in de cel daaronder;

maakt tekst en getallen in beide cellen vet;

verandert de tekengrootte in beide cellen in 16 pt.

Deze macro gaat geen prijzen winnen in de jaarlijkse competitie voor VBA-programmeurs, maar je moet ergens beginnen. De macro bereikt al deze stappen in één enkele actie. Zoals beschreven in de volgende paragrafen, begin je met het opnemen van je acties terwijl je deze stappen doorloopt. Vervolgens test je de macro om te zien of hij ook werkt. Tot slot bewerk je de macro om nog wat finishing touches toe te voegen. Klaar?

De eerste stappen zetten

Deze paragraaf beschrijft de stappen die je neemt voordat je de macro opneemt. Met andere woorden, je moet nog wat voorbereidingen treffen voordat de pret begint:

1.    Start Excel als het nog niet is gestart.

2.    Creëer zo nodig een nieuwe lege werkmap.

Dit doe je heel snel met Ctrl+N.

3.    Klik op de tab Ontwikkelaars en werp in de groep Programmacode een blik op de knop Relatieve verwijzingen gebruiken.

Als de kleur van deze knop afwijkt van de andere knoppen, zit je goed. Als de knop Relatieve verwijzingen gebruiken dezelfde achtergrondkleur heeft als de andere knoppen, moet je erop klikken om deze optie in te schakelen.

We bespreken de knop Relatieve verwijzingen gebruiken in hoofdstuk 6. Voor nu hoef je de optie alleen maar in te schakelen. Als de optie is ingeschakeld, heeft de knop Relatieve verwijzingen gebruiken een andere achtergrondkleur.

De macro opnemen

Nu kun je aan de slag. Volg deze instructies nauwgezet:

1.    Selecteer een cel.

Welke cel maakt niet uit.

2.    Kies Ontwikkelaars → Programmacode → Macro opnemen of klik op de knop Macro opnemen op de statusbalk.

Het dialoogvenster Macro opnemen verschijnt, zoals je ziet in figuur 2.2.

3.    Typ een naam voor de macro.

Excel heeft al een standaardnaam ingevuld (Macro1), maar het is beter om een beschrijvendere naam te gebruiken. NaamEnTijd (zonder spaties) is een goede naam voor deze macro.

4.    Klik in het vak Sneltoets en gebruik Shift+N (hoofdletter N) als sneltoets.

Het opgeven van een sneltoets is optioneel. Doe je dat wel, dan kun je de macro uitvoeren door een toetsencombinatie te gebruiken, in dit geval Ctrl+Shift+N. Bedenk wel dat als je een veelgebruikte sneltoets toewijst (bijvoorbeeld Ctrl+C), je de normale functionaliteit van deze sneltoets kwijtraakt; in plaats daarvan zal Excel je macro starten.

5.    Zorg dat Macro opslaan in staat ingesteld op Deze werkmap.

6.    Je kunt desgewenst wat tekst invoeren in het vak Beschrijving.

Deze stap is optioneel. Sommige mensen beschrijven graag wat de macro doet (of zou moeten doen).

7.    Klik op OK.

Het dialoogvenster Macro opnemen verdwijnt en de macrorecorder van Excel is ingeschakeld. Vanaf dit moment neemt Excel alles op wat je doet en zet dat om in VBA-code.

8.    Typ je naam in de actieve cel.

9.    Verplaats de celcursor naar de cel eronder en voer deze formule in:

=NU()

De formule geeft de huidige datum en tijd weer.

10. Selecteer de formulecel en gebruik Ctrl+C om deze cel te kopiëren naar het Klembord.

11. Kies Start → Klembord → Plakken → Waarden (W).

Deze opdracht zet de formule om in zijn waarde.

12. Gebruik met de datumcel geselecteerd Shift+↑ om deze cel en de cel erboven (die je naam bevat) te selecteren.

13. Gebruik de knoppen in de groep Lettertype van het tabblad Start om de opmaak te veranderen in Vet en een tekengrootte van 16 pt.

FIGUUR 2.2: Het dialoogvenster Macro opnemen verschijnt wanneer je een macro opneemt.

14. Kies Ontwikkelaars → Programmacode → Opname stoppen.

De macrorecorder wordt uitgeschakeld.

Gefeliciteerd! Je hebt zojuist je eerste Excel VBA-macro gemaakt. Vertel het je vrienden op social media.

De macro testen

Nu kun je deze macro uitproberen om te zien of hij goed werkt. Ga daarvoor naar een lege cel en gebruik Ctrl+Shift+N (of welke sneltoets je ook hebt opgegeven).

Excel voert de macro in een oogwenk uit. Je naam en de huidige datum en tijd worden in grote vette letters weergegeven.

Een andere manier om de macro uit te voeren is door Ontwikkelaars → Programmacode → Macro’s te kiezen (of door Alt+F8 te gebruiken) om het dialoogvenster Macro weer te geven. Selecteer de macronaam in de lijst (in dit geval NaamEnTijd) en klik op Uitvoeren. Zorg ervoor dat je eerst de cel waarin je naam zal verschijnen selecteert voordat je de macro uitvoert.

De macro onderzoeken

Je hebt een macro opgenomen en getest. Als je nieuwsgierig van aard bent, wil je waarschijnlijk weten hoe deze macro eruitziet. En waar hij is opgeslagen.

Weet je nog toen je begon met het opnemen van de macro? Je hebt toen aangegeven dat Excel de macro moest opslaan in Deze werkmap. De macro is opgeslagen in de werkmap, maar je moet de Visual Basic Editor (VBE) activeren om de macro te zien.

Neem de volgende stappen om de macro te zien:

1.    Kies Ontwikkelaars → Programmacode → Visual Basic (of gebruik Alt+F11).

Het programmavenster van de Visual Basic Editor verschijnt, zoals je ziet in figuur 2.3. Dit venster is heel aanpasbaar, dus kan het zijn dat jouw VBE-venster er ietsje anders uitziet. Het VBE-venster bevat verscheidene andere vensters en kan nogal intimiderend overkomen. Maak je niet druk, je went er vanzelf aan.

2.    Linksboven in het VBE-venster vind je meestal het projectvenster, dat we de Projectverkenner noemen.

De Projectverkenner bevat een lijst van alle werkmappen en invoegtoepassingen die momenteel geopend zijn. Elk project is gerangschikt in een boomstructuur en kan worden uitgevouwen (om meer informatie weer te geven) of samengevouwen (om minder informatie weer te geven).

De VBE gebruikt nogal wat vensters, die al dan niet geopend kunnen zijn. Als een venster niet meteen zichtbaar is in de VBE, kun je een optie kiezen in het menu Beeld om het venster weer te geven. Als bijvoorbeeld de Projectverkenner niet zichtbaar is, kun je Beeld → Projectverkenner kiezen (of Ctrl+R gebruiken) om het weer te geven. Je kunt elk ander VBE-venster op soortgelijke wijze weergeven. De componenten van de VBE worden behandeld in hoofdstuk 3.

3.    Selecteer het project dat correspondeert met de werkmap waarin je de macro hebt opgenomen.

Als je de werkmap niet hebt opgeslagen, heet het project waarschijnlijk VBAProject (Map1).

4.    Klik op het plusteken (+) links van de mapnaam Modules.

De boom wordt uitgevouwen en toont Module1, de enige module in het project.

5.    Dubbelklik op Module1.

De VBA-code in deze module wordt weergegeven in een codevenster (zie figuur 2.3). Misschien ziet je scherm er niet precies zo uit als in figuur 2.3. De opgenomen code hangt af van je specifieke acties tijdens het opnemen van de macro.

Laat je op dit moment niet afschrikken door die vreemd uitziende code, dat komt allemaal goed. Over een paar hoofdstukken snap je precies wat er staat.

De macro NaamEnTijd bestaat uit verscheidene statements. Excel voert de statements een voor een uit, van boven naar beneden. Een statement dat vooraf wordt gegaan door een apostrof (’) is een opmerking. Opmerkingen zijn alleen voor jouzelf bedoeld en worden door Excel genegeerd. Met andere woorden, Excel slaat die gewoon over.

FIGUUR 2.3: De VBE geeft de VBA-code in Module1 van Map1 weer.

HÉ, DÁT HEB IK NIET OPGENOMEN!

De macrorecorder werkt als een geluidsrecorder. Wanneer je een opname afspeelt en je eigen stem hoort, ben je geneigd te zeggen: ‘Maar zo klink ik helemaal niet.’ En wanneer je naar je opgenomen macro kijkt, kun je acties tegenkomen die je volgens jou helemaal niet hebt opgenomen.

Toen je de macro NaamEnTijd opnam, heb je alleen de tekengrootte veranderd, maar de opgenomen code laat allerlei lettertypestatements zien (Strikethrough, Superscript, Shadow enzovoort). Geen zorgen, dat gebeurt de hele tijd. Excel neemt vaak ogenschijnlijk nutteloze code op. In latere hoofdstukken ontdek je hoe je dat extra spul kunt verwijderen uit een opgenomen macro.

Het eerste VBA-statement (dat begint met het woord Sub) identificeert de macro als een Sub-procedure en geeft de naam weer; deze naam heb je opgegeven voordat je begon met opnemen. Als je de code doorleest, snap je misschien al hier en daar wat er staat. Je ziet je naam, de formule die je hebt ingevoerd en allerlei extra code die het lettertype wijzigt. De Sub-procedure wordt afgesloten met het statement End Sub.

De macro wijzigen

Zoals je al verwachtte, kun je de macro in de VBE niet alleen bekijken maar ook veranderen. Hoewel je nu waarschijnlijk nog geen idee hebt wat je aan het doen bent, kun je de wijzigingen makkelijk aanbrengen:

Verander de naam die is ingevoerd in de actieve cel. Als je een hond hebt, gebruik dan de naam van de hond.

Wijzig de naam of grootte van het lettertype.

Kijk of je de juiste plek kunt vinden voor dit nieuwe statement dat de celinhoud cursief maakt:

Selection.Font.Italic = True

Werken in VBA-code heeft wel iets van werken in een tekstverwerker (alleen is er geen tekstoverloop en kun je de tekst niet opmaken). Of beter gezegd, het lijkt op werken in Windows Kladblok. Je kunt op Enter drukken om naar een nieuwe regel te gaan en de vertrouwde bewerkingstoetsen werken zoals verwacht.

Ga nadat je je wijzigingen hebt aangebracht terug naar Excel en probeer de herziene macro uit. Net zoals je Alt+F11 kunt gebruiken in Excel om de VBE weer te geven, kun je Alt+F11 gebruiken in de VBE om terug te gaan naar Excel.

Werkmappen opslaan die macro’s bevatten

Als je een of meer macro’s opslaat in een werkmap, moet het bestand worden opgeslagen in de bestandsindeling Excel-werkmap met macro’s. Met andere woorden, het bestand moet worden opgeslagen met een .xslm-extensie in plaats van de gebruikelijke .xlsx-extensie.

Wanneer je bijvoorbeeld de werkmap die je macro NaamEnTijd bevat opslaat, zal de bestandsindeling in het dialoogvenster Opslaan als standaard xlsx zijn (een indeling die geen macro’s kan bevatten). Tenzij je de bestandsindeling wijzigt in xslm, zal Excel de waarschuwing in figuur 2.4 laten zien. Je moet dan op Nee klikken en in de vervolgkeuzelijst Opslaan als de bestandsindeling Excel-werkmap met macro’s (*.xlsm) kiezen.

FIGUUR 2.4: Als je werkmap macro’s bevat en je die probeert op te slaan in een macrovrije bestandsindeling, laat Excel een waarschuwing zien.

Macrobeveiliging begrijpen

Macrobeveiliging is een belangrijke functie in Excel. De reden daarvoor is dat VBA een krachtige programmeertaal is, zo krachtig zelfs dat het mogelijk is om een macro te maken die je computer ernstig kan beschadigen. Een macro kan bestanden wissen, informatie naar andere computers sturen en zelfs Windows vernietigen zodat je je systeem niet eens meer kunt opstarten.

De macrobeveiligingsfuncties die in Excel 2007 zijn geïntroduceerd, zijn gecreëerd om dit soort problemen te helpen voorkomen.

In figuur 2.5 zie je het onderdeel Macro-instellingen van het dialoogvenster Vertrouwenscentrum. Dit dialoogvenster geef je weer met Ontwikkelaars → Programmacode → Macrobeveiliging.

FIGUUR 2.5: Het onderdeel Macro-instellingen van het dialoogvenster Vertrouwenscentrum.

Standaard hanteert Excel de optie Alle macro’s uitschakelen zonder melding. Als je bij deze instelling een werkmap opent die macro’s bevat (en het bestand niet digitaal is ‘ondertekend’ of op een vertrouwde locatie is opgeslagen), laat Excel een waarschuwing zien zoals in figuur 2.6. Als je zeker weet dat de werkmap afkomstig is van een vertrouwde bron, klik dan op Macro’s inschakelen zodat de macro’s ingeschakeld zijn.

FIGUUR 2.6: Excels waarschuwing dat het te openen bestand macro’s bevat.

Je ziet het waarschuwingsvenster in figuur 2.6 alleen wanneer de VBE is geopend. Zo niet, dan laat Excel een in het oog springende beveiligingswaarschuwing zien boven de formulebalk, zoals je ziet in figuur 2.7. Als je weet dat de werkmap veilig is, klik je op de knop Inhoud inschakelen om de macro’s in te schakelen. Als je de werkmap zonder macro’s wilt gebruiken, klik je op de X om de waarschuwing te laten verdwijnen.

Excel onthoudt het wanneer je een werkmap als veilig hebt aangewezen. De eerstvolgende keer dat je het bestand opent, blijft de beveiligingswaarschuwing achterwege.

FIGUUR 2.7: Excels waarschuwing dat de zojuist geopende werkmap macro’s bevat. Deze waarschuwing zie je wanneer de VBE niet geopend is.

De beste manier om met macrobeveiliging om te gaan is wellicht om een of meer mappen aan te wijzen als vertrouwde locaties. Alle werkmappen in een vertrouwde locatie worden geopend zonder macrowaarschuwing. Je wijst vertrouwde mappen aan in het onderdeel Vertrouwde locaties van het dialoogvenster Vertrouwenscentrum.

Als je wilt weten wat de andere instellingen voor macrobeveiliging inhouden, druk je op F1 terwijl het onderdeel Macro-instellingen van het dialoogvenster Vertrouwenscentrum in beeld is. Je krijgt dan een Help-scherm dat de instellingen beschrijft.

Meer onthullen over de macro NaamEnTijd

Tegen de tijd dat je dit boek uit hebt, zou je volledig moeten begrijpen hoe de macro NaamEnTijd werkt, en zul je ook in staat zijn om gecompliceerdere macro’s te ontwikkelen. Voor nu sluiten we dit hoofdstuk af met nog enkele extra aandachtspunten met betrekking tot deze macro:

De macro werkt alleen als zijn werkmap geopend is. Als je de werkmap sluit, is de macro niet meer beschikbaar en kan hij ook niet werken (ook niet met zijn sneltoets).

Zolang de werkmap die de macro bevat geopend is, kun je de macro uitvoeren terwijl een andere werkmap actief is. Met andere woorden, de eigen werkmap van de macro hoeft niet het actieve venster te zijn.

De macro is niet van professionele kwaliteit. Hij zal bestaande tekst overschrijven zonder waarschuwing en de gevolgen ervan kunnen niet ongedaan worden gemaakt.

Voordat je de macro begon op te nemen, heb je er een nieuwe sneltoets aan toegewezen. Dit is slechts een van meerdere manieren om de macro uit te voeren. (In hoofdstuk 5 ontdek je andere manieren.)

Je kunt deze macro handmatig maken in plaats van hem op te nemen. Daarvoor moet je wel goed thuis zijn in VBA. (Wees geduldig, dat gaat ook gebeuren.)

Je kunt deze macro opslaan in je Persoonlijke macrowerkmap. Als je dit doet, is de macro automatisch beschikbaar wanneer je Excel start. (Zie hoofdstuk 6 voor details over de Persoonlijke macrowerkmap.)

Je kunt de werkmap ook converteren naar een invoegtoepassing. (Daarover lees je meer in hoofdstuk 21.)

Gefeliciteerd. Je bent ingewijd in de wereld van Excel-programmeren. (Sorry, er is geen geheime handdruk.)

2
Hoe VBA werkt met Excel

IN DIT DEEL . . .

Zie je hoe je toegang krijgt tot het belangrijke deel van de Visual Basic Editor.

Ontdek je VBA-codemodules (waar je je VBA-code opslaat).

Krijg je een overzicht van het Excel-objectmodel.

Ontdek je iets over twee sleutelconcepten: objecteigenschappen en objectmethoden.

Ontdek je de verschillen tussen Sub-procedures en Function-procedures.

Doe je een snelcursus in het gebruik van de Excel-macrorecorder.

IN DIT HOOFDSTUK

De onderdelen van de Visual Basic Editor ontdekken

Weten wat in een VBA-module terechtkomt

Drie manieren leren kennen om VBA-code in een module te krijgen

De VBA-omgeving aanpassen

Hoofdstuk 3:
Werken in de Visual Basic Editor

Als bovengemiddeld ervaren Excel-gebruiker weet je waarschijnlijk al veel over werkmappen, formules, grafieken en andere Excel-dingen. Nu is het tijd om je horizon te verbreden en een volledig nieuw aspect van Excel te verkennen: de Visual Basic Editor. In dit hoofdstuk leer je hoe je werkt met de Visual Basic Editor en ga je je verdiepen in het schrijven van VBA-code.

Wat is de Visual Basic Editor?

De Visual Basic Editor (of VBE) is een afzonderlijke toepassing waarin je je VBA-macro’s schrijft en bewerkt. Met ingang van Excel 2013 wordt elke werkmap weergegeven in een apart venster. Maar er is maar één VBE-venster, en dat werkt met alle geopende Excel-vensters.

Je kunt de VBE niet afzonderlijk starten. Excel moet gestart zijn om de VBE te kunnen openen.

De VBE activeren