Issuu

Inhoud ■

■1

Inleiding

Voor wie is dit boek bedoeld? Op welke apparaten werkt Excel VBA? Werken de macro’s bij Apple en OpenOffice? Werken de macro’s op een 64-bitssysteem? Werken met Excel in het Engels Werken met dit boek De inhoud overzien Kennisnemen van afspraken Coderegels afbreken Werken met sneltoetsen Voorbeeldbestanden ophalen Extra hoofdstuk downloaden Over de auteur

1 2 3 3 3 4 4 5 7 8 8 9 9

Macro’s opnemen en uitvoeren Kennismaken met een macro De macro opnemen De macro uitvoeren Help! De macro doet het niet De opties in het venster langslopen De macro een naam geven Een sneltoets opgeven De sneltoets veranderen De opslagplaats kiezen Een toelichting schrijven De eigenlijke macro opnemen De opname stoppen Help! Ik heb mij vergist Het venster van Excel inrichten Opnameknop in de statusbalk plaatsen Het tabblad Ontwikkelaars openen De macro anders aansturen Inkomsten per week onder elkaar zetten

11 11 12 13 14 14 14 15 16 16 17 17 17 18 18 19 19 20 21

Inhoud

Een knop in het werkblad plaatsen Een knop maken De knop een opschrift geven Macro aan de knop koppelen Een knop verwijderen Een standaardknop invoegen Een werkmap met macro opslaan Macrobeveiliging instellen Beveiliging bij de gebruiker instellen Een vertrouwde map aanleggen Digitale handtekening plaatsen De code verkennen Van Sub naar End Sub gaan De macronaam veranderen Commentaar toevoegen De opdrachten begrijpen De macro bijstellen Meer cellen sorteren Op een andere kolom sorteren Andersom sorteren Adreslijst sorteren op meer kolommen Meer macro’s opnemen Te sorteren rijen bijstellen Sorteren met een klik op een keuzerondje Macro’s aan keuzerondjes koppelen

■2

Werken met de Visual Basic Editor De Visual Basic Editor openen Het venster van de editor inrichten Code en werkblad tegelijk zien De Visual Basic Editor verkennen De werkbalk Standaard overzien De werkbalk Bewerken openen Werkbalken sluiten Opties instellen Het venster Projectverkenner openen Actieve module terugvinden Navigeren met het menu Venster Door het codevenster navigeren Eén procedure zien of alle Standaardopmaak aanhouden

22 23 24 25 26 26 27 29 32 32 32 33 34 34 35 35 37 37 37 38 38 40 41 42 43

45 45 47 48 49 49 52 53 53 55 56 57 58 58 59

Het Complete Boek Excel VBA voor professionals

Macro uitvoeren vanuit het codevenster Beëindigen of Foutopsporing kiezen? Macro stap voor stap uitvoeren Fouten opsporen Een onderbrekingspunt aanbrengen De Esc-toets gebruiken Werken met het venster Direct VBA-functies testen Code controleren met Debug.Print Het venster Direct leegmaken Uw code beveiligen Beveiliging opheffen Een werkblad diep verbergen De Visual Basic Editor sluiten

■3

Code schrijven Uw eigen procedure schrijven Nieuwe module invoegen Module invoegen vanuit Excel Het raamwerk maken Foutmelding: compileerfout Opdrachten in de procedure plaatsen Procedure uitvoeren Regels laten inspringen Regels afbreken Op de juiste plaats afbreken Compileerfout door afbreken oplossen Snel invoeren met AutoLijstweergave Gericht uit bijpassende opties kiezen Syntaxisinfo inschakelen Syntaxis begrijpen Syntaxisinfo oproepen Compileerfouten oplossen Code van berichtvenster uitbreiden Andere knoppen plaatsen Eigen opschrift bedenken Standaardknop instellen Codes uit voorbeeldbestanden kopiëren Complete module kopiëren Foutmelding: dubbelzinnige naam gevonden

59 60 61 62 63 64 64 66 67 68 69 69 70 71

73 73 73 74 75 76 76 77 77 79 80 81 81 82 83 83 84 84 85 86 87 87 89 90 91

Inhoud

Code van internet halen Ruimte maken voor een kopie Code kopiëren Help! Een deel van de code is rood Compileerfout door plakken oplossen Ongedaan maken Ctrl+Y gebruiken voor kopiëren Commentaar toevoegen Opdrachtregels in- en uitschakelen Speciale tekens in een code typen Opletten bij punten en komma’s Macro verwijderen Module verwijderen Sporen van de macro wissen Algemene macro verwijderen

■4

Werken met objecten Objectgeoriënteerd programmeren Met objecten werken Werken met een verzameling De verzameling uitbreiden Eigenschappen toepassen Cellen opmaken Getallen en tekst in een cel plaatsen Meer eigenschappen toekennen Methoden gebruiken Naar een cel navigeren Verplaatsen vanaf een cel Langs grenzen gaan Naar de onderste lege cel gaan Cellen selecteren Losliggende cellen selecteren Aparte groepen van cellen selecteren Selecteren vanaf een cel Automatisch de lengte van een bereik bepalen Bewerkingen uitvoeren Geldige methoden gebruiken Een rij leegmaken of verwijderen Adreslijst sorteren en hele rij markeren Achternaam en voornaam terugvinden Sorteermacro’s aanpassen Wekelijkse uren onder elkaar zetten

xii

92 92 93 94 94 94 95 95 96 96 97 98 98 99 99

101 101 102 103 104 105 105 106 107 109 110 111 112 113 115 116 117 118 120 120 121 121 122 124 126 128

Het Complete Boek Excel VBA voor professionals

Argumenten aan een methode meegeven KopiĂŤren met een korte code Waarden kopiĂŤren en plakken Argumenten bij een functie gebruiken Werken met benoemde argumenten Werken met positionele argumenten Berichtvenster samenstellen met variabelen Naam of positie gebruiken Foutmelding: Het argument is niet optioneel Het objectenoverzicht verkennen Help gebruiken Help! De Help werkt niet in Excel 2010

â– 5

Variabelen en constanten Werken met variabelen Variabelen benoemen Foutmelding: Verwacht: Variabele De variabele gebruiken Variabele gebruiken voor celverwijzing Naar een datum gaan met een variabele Naar het einde van een reeks gaan met een variabele Rekenen met een variabele De waarde van de variabele volgen Werken met gegevenstypen Variabelen declareren Impliciet declareren Werken met declaratietekens Expliciet declareren Variabelen toepassen Option Explicit instellen Woord aanvullen gebruiken Meer variabelen declareren Het juiste type opgeven Help! Overloop Afkortingen gebruiken Foutmelding: Gegevenstype niet gedefinieerd Achteraf gegevenstype aan variabele toewijzen De variabele herkennen Werken met een objectvariabele Werken met Set Een gebied instellen met Set Naar een cel zoeken met Set

129 130 131 132 132 133 134 135 136 137 139 140

143 143 146 147 147 148 149 151 152 153 154 155 155 156 157 158 159 160 161 161 162 163 164 164 165 165 165 167 168

xiii

Inhoud

Naar een website gaan met Set Let links laten liggen Zelf gegevenstypen maken Foutmelding: Gegevenstype niet gedefinieerd Het bereik van de variabele instellen Reikwijdte onderscheiden Bereik op procedureniveau definiëren Bereik op moduleniveau definiëren Private houden Public maken De levensduur van de variabele verlengen Een variabele statisch maken Static gebruiken: een voorbeeld Werken met arrays De array vullen Meer dimensies opgeven De array vullen met waarden uit een werkblad Een array vullen met een naambereik Informatie uit een array halen Bovengrens opzoeken met UBound Ondergrens opzoeken met LBound Waarde opzoeken binnen een array Constanten gebruiken Werken met algemene constanten Werken met constanten van Excel Een andere constante kiezen Zelf constanten benoemen Een constante maken voor pi Foutmeldingen begrijpen Foutmelding: Verwacht: expressie Foutmelding: Verwacht: lijstscheidingsteken of ) Foutmelding: Verwacht: instructie-einde

■6

Verkorten en versnellen Opgenomen macrocode inkorten Select en Selection weghalen Van vier regels één maken Range tussen blokhaken zetten Sorteermacro inkorten Macro Weekstaat verkorten Een groep cellen leeg maken Overbodige opdrachten weghalen

xiv

169 170 170 171 172 172 173 174 174 175 176 177 178 181 182 183 185 185 185 187 189 189 191 191 192 193 193 194 195 195 196 197

199 199 199 200 201 203 205 205 207

Het Complete Boek Excel VBA voor professionals

Overbodige eigenschappen weghalen Een blok With…End With maken Blokken samenvoegen Eigenschappen onder elkaar zetten De uitvoering versnellen Schermverversing uitzetten Niet steeds automatisch berekenen Variabele overslaan Een andere macro oproepen Programmeren in modules

■7

Beslissingen nemen Werken met operatoren Beslissingen nemen De termen afspreken If…Then gebruiken Wel of geen End If gebruiken Reageren op de foutmelding If…Then…Else inzetten If…Then nesten Werken met twee voorwaarden Minder voorwaarden benoemen Werken met Or Werken met ElseIf Werken met Not Geval kiezen met Select Case Korting toekennen Maand toekennen met Select Case Cijfer vervangen door de maand Wel of geen cel opgeven Voorwaarden anders noteren

■8

Handelingen herhalen Herhalen met For…Next Optellen met For…Next Stappen met Step Een aantal cellen vullen Werkbladnamen verzamelen Werkbladen met maanden maken Jaaroverzicht over maanden verdelen Oude gegevens wissen

207 209 209 211 211 211 212 214 214 215

217 217 218 218 218 220 221 222 222 224 225 225 228 229 230 231 232 234 235 235

237 237 239 240 240 243 244 245 248

Inhoud

Een wisselend aantal werkbladen doorlopen Doorgaan met On Error Resume Next Alle werkbladen inventariseren Alles meenemen met For Each…Next Alle werkbladen zichtbaar maken Alle bladtabs een andere naam geven Naam van de bladtabs uit een cel halen Stap overslaan in For Each…Next Bepaalde bladtabs kleuren Alle werkbladen beveiligen Herhalen met Do Loop De lus Do While…Loop De lus Do…Loop Until Willekeurige getallen onder elkaar plaatsen Reeks unieke getallen onder elkaar plaatsen Een sprong maken met GoTo Naar de datum van vandaag gaan

■9

Algemeen bruikbare macro’s maken Een Persoonlijke Macrowerkmap maken In de Persoonlijke Macrowerkmap schrijven Pad in de titelbalk weergeven Alle opmerkingen opmaken De macro testen De macro aanpassen Alle hyperlinks verwijderen Geblokkeerde cellen markeren Niet-geblokkeerde cellen markeren Knop in de werkbalk Snelle toegang plaatsen Pictogram voor de knop kiezen Knoppen aan het lint toevoegen Knop Cel vergrendelen aan het lint toevoegen Tabblad met eigen knoppen maken Bestaande macro algemeen bruikbaar maken Algemene macro privé maken Algemene macro verwijderen

■ 10 Reageren op gebeurtenissen Zelfstartende macro’s maken Codeblad koppelen aan een werkblad Codeblad koppelen aan de hele werkmap Events voor een blad of de hele werkmap gebruiken

xvi

250 250 251 252 253 254 255 256 256 257 259 260 261 262 265 267 269

271 271 272 273 273 275 276 277 278 280 281 282 283 285 286 287 288 288

289 289 290 291 291

Het Complete Boek Excel VBA voor professionals

Reageren op een klik Aangeklikte cel markeren Reageren op een wijziging Datum naast een cel plaatsen Tijdstip automatisch invoeren Laatst bewerkte cel tonen Ingevoerde postcode corrigeren Dialoogvenster openen bij foute invoer Controleren of andere cellen zijn ingevuld Help! Automatische macro werkt niet meer Draaitabel automatisch vernieuwen Macro opnemen en zelfstartend maken Alle draaitabellen automatisch verversen Draaitabellen in een ander werkblad automatisch verversen Eventmacro onderscheppen Reageren op een dubbelklik Waarden kiezen met een dubbelklik Unieke waarden kiezen Reageren op activeren van het werkblad Het werkgebied beperken Waarden van grafiek verbergen Tab van kopie rood maken Reageren bij het openen Meteen naar onderste cel gaan Bestaande code zelfstartend maken Meteen naar de huidige maand gaan Kalender bij vandaag laten beginnen Keuzelijst voor de maand maken Zorgen dat de kalender altijd vandaag toont Reageren op de opdracht Afdrukken Afdrukken tegenhouden Reageren op werkmap sluiten Alle werkbladen beveiligen bij het sluiten Ook de structuur beveiligen bij het sluiten

â– 11 Opslaan en sluiten Opslaan of sluiten Opslaan Sluiten Opslaan door middel van VBA Een bestaande werkmap opslaan Een nieuwe werkmap opslaan Opslaan met een naam

293 293 295 295 296 298 299 300 301 302 302 303 304 306 307 309 310 312 314 314 315 316 318 319 319 320 320 321 322 323 323 324 324 327

329 329 329 330 330 330 330 331 xvii

Inhoud

Sluiten met VBA Argumenten gebruiken bij Close Alle bestanden sluiten Het hele programma sluiten Help! Compileerfout: Ongeldige of niet-gekwalificeerde verwijzing Opslaan met een venster Opslaan en sluiten Opslaan voorkomen Een kopie van de werkmap opslaan Werkmap opslaan met naam en datum Werkmap opslaan als PDF Alle werkbladen of selectie opslaan Opslaan als PDF met dezelfde bestandsnaam Factuur doornummeren en opslaan als PDF Datum in de bestandsnaam opnemen Alle werkmappen in een map behandelen

â– 12 Berichtvensters maken Werken met het berichtvenster Knoppen kiezen Een knop standaard maken Pictogram in het venster plaatsen Pictogrammen inbouwen Met uw keuze de macro sturen Gegevens wissen na waarschuwing Waarschuwingsvenster anders samenstellen Constante door een waarde vervangen? Bericht over meer regels verdelen Gewerkte tijd in een jaarlijst plaatsen Controles inbouwen Ingevoerde gegevens wissen na melding Excel of Windows laten wachten Iets meedelen via de statusbalk Procenten in de statusbalk De statusbalk vrijgeven Bericht in de statusbalk

â– 13 Invoervensters maken Werken met het invoervenster Alvast een naam invullen De positie van het venster bepalen

xviii

332 333 334 334 335 336 337 338 338 339 341 343 343 345 347 347

355 355 356 357 358 358 360 360 361 362 363 365 367 370 371 372 372 373 374

375 375 376 377

Het Complete Boek Excel VBA voor professionals

Kwadraat berekenen Help! Compileerfout: Verwacht instructie-einde De invoer afronden Niet altijd een invoervenster gebruiken Met het invoervenster gegevens in het werkblad plaatsen Grote getallen invoeren Probleem met een datum oplossen Werken met procenten Code vereenvoudigen Alle werkbladen beveiligen via een invoervenster Gegevenstype van de Inputbox opgeven Cellen selecteren met invoervenster Waarden plakken Waarden ergens anders plakken Unieke getallen opvragen met invoervensters Unieke getallen in een kolom plaatsen Cellen selecteren voor willekeurige getallen Cellen selecteren voor unieke getallen Reiskosten declareren De route kiezen Foute invoer afvangen Controleren of een getal is ingevuld Reageren op Annuleren Beveiligen met een pincode Drie pogingen toestaan

â– 14 Dialoogvensters opbouwen De stappen overzien De terminologie begrijpen Opties instellen voor UserForm Het dialoogvenster opzetten Werken met de werkset Een dialoogvenster met invoervakken maken Het venster Eigenschappen oproepen De elementen een naam geven Afkortingen gebruiken voor elementen Aanduiding bij de invoervakken plaatsen Knoppen OK en Annuleren plaatsen De elementen schikken Code aan het dialoogvenster koppelen Code voor de knop OK opstellen Controle op de naam inbouwen

378 379 379 381 382 382 383 384 385 386 389 390 392 393 396 396 399 400 404 404 406 406 408 408 409

413 413 414 414 415 416 416 417 418 419 419 420 421 423 424 424

xix

Inhoud

Vragen om af te drukken Vragen om te verzenden per e-mail Code voor de knop Annuleren schrijven Het venster oproepen met een knop Andere acties wel of niet toelaten Dialoogvenster sluiten met Esc-toets Een dialoogvenster met keuzelijsten maken De opties van de keuzelijst instellen Waarden uit cellen in de keuzelijst aanbieden Naar cellen verwijzen via Eigenschappen Code voor het dialoogvenster schrijven Het venster oproepen Het gedrag van de keuzelijst bepalen Andere inbreng toelaten Nieuwe waarden opnemen in de keuzelijst Controleren of waarden zijn ingevuld Leeg veld markeren Een andere kleur instellen Bekende waarden alvast invullen Een dialoogvenster met keuzerondjes maken Reeks omzetten naar bedragen inclusief btw Dialoogvenster met vier opties maken Keuzerondjes in het UserForm plaatsen Standaardkeuze instellen Meer keuzerondjes maken Groepen indelen via Eigenschappen Knoppen OK en Annuleren plaatsen Andere aanduiding op de knop plaatsen Code voor het dialoogvenster maken Code voor de knop OK schrijven Gekozen berekening uitvoeren Berekening uitvoeren in geselecteerde cellen Onder iedere optie een eigen lus plaatsen Code voor de knop Annuleren maken Controleren of meer cellen zijn geselecteerd Gegevenslijst aanvullen met een dialoogvenster De knop Toevoegen code geven Knop voor dit venster maken Een dialoogvenster kopiĂŤren

426 426 427 428 429 429 430 431 432 434 434 436 436 436 438 440 440 443 444 446 446 446 447 448 449 449 450 451 452 452 452 453 454 455 456 458 459 461 462

Het Complete Boek Excel VBA voor professionals

■ 15 Meer elementen in dialoogvensters plaatsen De knop Annuleren plaatsen Selecteren met selectievakjes Kiezen met een keuzelijst zonder invoervak Aantal selecties van de keuzelijst bepalen Wisselen met de wisselknop Schuiven met een schuifbalk Kiezen met een kringveld Dialoogvenster openen bij foute invoer Meer bladen maken met Meerbladig Bestaande gegevens in het venster laden Naar een range verwijzen met RefEdit Controleren of cellen zijn geselecteerd Geïmporteerde gegevens converteren Datum en tijd kiezen met Datepicker Een afspraak maken Uw dialoogvenster perfectioneren Cursor tegen de linkerkant plaatsen Met sneltoetsen door het venster gaan Tabvolgorde anders instellen Tabvolgorde controleren De knop OK standaard maken Annuleren met de Esc-toets Keuzes maken met sneltoetsen Een tip tonen bij de invoervakken Het dialoogvenster van kleuren voorzien Uw eigen kleuren kiezen Van een afbeelding voorzien De afbeelding verwijderen

■ 16 Werken met functies voor getallen Werken met functies Werken met argumenten Proberen in het venster Direct Testen met Debug.Print Werken met rekenfuncties De functie Abs De functie Sgn Letten op punten en komma’s De functie Fix De functie Int

465 465 465 467 469 471 474 477 479 481 483 485 487 488 492 495 496 496 497 498 498 499 500 501 501 502 503 503 504

507 507 508 509 509 511 511 511 511 512 512

xxi

Inhoud

Afronden met Round Afronden op vijf cent Afronden met de backslash Werken met financiële functies Periodieke betalingen berekenen Kapitaal voorspellen Afschrijving berekenen met SLn Werken met functies voor meetkunde Sinus berekenen Worteltrekken en machtsverheffen Met imaginaire getallen rekenen Willekeurige getallen trekken Willekeurige getallen in kolom plaatsen Getallen opmaken met een functie Werken met de functie FormatNumber Werken met FormatCurrency Werken met FormatPercent Verder opmaken met Format Voorloopnullen aanbrengen Een eigen teken als opmaak opgeven Procenten opmaken met Format Cellen in Excel opmaken Excel-formules invoeren met VBA Een formule invoeren via de code Vertaling van alle Excel-functies raadplegen Excel-functies in VBA verwerken AutoLijstweergave gebruiken Kiezen tussen VBA-functie en Excel-functie Uitsluitend de VBA-functie gebruiken Functie van Excel combineren met een VBA-functie Excel-functies gebruiken in VBA Afronden met meer opties Afronden naar beneden en naar boven Afronden in stappen Letten op komma’s en puntkomma’s Functienamen in Excel en VBA onderscheiden Logaritme berekenen in Excel en VBA Moeilijkheden voorkomen met Mod

xxii

512 513 514 515 515 516 517 518 518 518 519 519 521 523 523 525 526 527 529 530 531 531 532 532 533 536 537 538 538 540 541 542 543 544 544 545 545 547

Het Complete Boek Excel VBA voor professionals

■ 17 Werken met andere functies Functies voor tekenreeksen gebruiken De functie Str gebruiken De functie Val gebruiken De functie Replace toepassen Werken met Lcase, Ucase en StrConv Een deel van de tekst ophalen met Mid Zoeken met InStr Omgekeerd zoeken met InStrRev InStrRev gebruiken in een macro Celinhoud opschonen Omzetten met conversiefuncties WAAR of ONWAAR aangeven met CBool CStr gebruiken Werken met informatiefuncties Informatie vragen over een bestand De invoer controleren

■ 18 Eigen functies maken Een functie schrijven Functie maken voor de oppervlakte van een cirkel De functie CIRKELOPPERVLAK gebruiken Een functie programmeren Help: Uitkomst is steeds nul De nauwkeurigheid instellen Uw functie testen Testen met het venster Direct De formule in hoofdletters weergeven De functie PIRAMIDEDRIEHOEK programmeren De functie PIRAMIDEVIERKANT maken De functie BOLOPPERVLAK schrijven De functie BOLINHOUD programmeren De functie CILINDERINHOUD maken De functie ELLIPS schrijven Eigen functie invoeren met Functie invoegen Eigen uitleg aan dialoogvenster toevoegen Aantal rode cellen tellen De functie RODECELLEN gebruiken Getallen in gele cellen optellen De functie SOMKLEUR gebruiken Gewenste kleur aanwijzen

549 549 550 551 551 552 554 555 558 559 561 565 565 566 567 568 569

571 571 572 573 574 575 576 576 577 577 578 580 581 582 583 584 584 585 586 587 588 589 590

xxiii

Inhoud

Cellen met een formule laten opvallen Voorwaardelijke opmaak instellen Functie voor Pythagoras maken Met een functie de achternaam achterhalen Adreslijst sorteerbaar maken Functie voor BMI schrijven Indeling bepalen met Select Case De functie verfijnen Controleren met Debug.Print Eigen functies voor belastingdruk maken Functie BELASTING2016 maken Code voor deze functie langslopen Belasting en premies berekenen met één formule Functie BELASTINGAOW2016 maken Eigen functie IBAN maken Het controlegetal berekenen Van vier letters naar acht cijfers IBAN samenstellen Zelfgemaakte functie bewaren In een bepaalde werkmap bewaren Uw functie als invoegtoepassing opslaan

■ 19 Programmeren met datums De datum goed noteren Functies voor datum Datum samenstellen met DateSerial Dag van de week ophalen Weekdag van een datum weergeven Een datum verder analyseren Leeftijd berekenen met DateDiff Leeftijd berekenen met Year, Month en Day Datum opmaken met de functie Format Datum combineren met opmaak van de cel Tijdstip met of zonder datum weergeven Opmaak met datum en tijd Uitkomst in een zin plaatsen Tekst en functie in een variabele plaatsen De berekening direct in het berichtvenster plaatsen Het weeknummer berekenen Weeknummer berekenen met een formule Eigen functie EURWEEKNUMMER maken Formule omzetten naar eigen functie

xxiv

592 593 594 596 597 598 598 600 600 601 601 602 604 605 606 607 608 609 611 611 611

615 615 617 617 618 618 618 619 620 621 622 623 624 625 626 626 627 627 628 629

Het Complete Boek Excel VBA voor professionals

Van weeknummer en jaar naar datum Datum bepalen met weeknummer en jaartal in tekst De weekdagen van een maand tellen Weekdagen per maand tellen met Excel-formules Weekdagen tellen met een eigen functie De code van de functie verkorten Twee weekdagen tellen met eigen functie Alle werkdagen van een maand tellen Weekdagen tellen in langere periode De functie SCHRIKKELJAAR maken Functies voor de feestdagen schrijven Formule voor de paasdatum overnemen De functie PASEN schrijven De functie PASEN gebruiken Andere feestdagen berekenen De functie PINKSTEREN opstellen Berekenen wanneer het carnaval is Een functie voor de ramadan maken Rekenen met datums voor 1900 Oude datums testen Nagaan op welke dag een gebeurtenis viel De functie aanpassen aan de Gregoriaanse kalender De functie DATUMVERSCHIL uitbreiden Functie op een andere manier invoegen De functie DATUMVERSCHILSPEC testen Verder tellen met de functie DATUMPLUS Een periode bij de datum optellen Terugtellen met de functie DATUMMIN Een periode van de datum aftrekken Oude datums opvragen met een dialoogvenster Werkmap met paasdatums ophalen

â– 20 Programmeren met tijd Vast tijdstip opgeven Functies voor tijd gebruiken Tijd opmaken met de functie Format Tijdstip combineren met opmaak van de cel Tijdstip zonder datum weergeven Dialoogvenster met tijdstippen maken De tijdstippen van de keuzelijst instellen Macroâ€™s voor het dialoogvenster schrijven Het venster oproepen

631 632 633 633 634 636 637 638 638 640 642 642 642 643 644 644 645 647 648 648 650 652 653 653 654 656 657 658 659 660 660

663 663 664 664 665 666 666 667 668 669

xxv

Inhoud

Een procedure op één tijdstip uitvoeren Werkmap automatisch sluiten Een procedure regelmatig uitvoeren Een digitale klok maken De klok aan- en uitzetten Automatisch starten en stoppen Cel laten knipperen bij foute invoer Knipperen inschakelen bij foute invoer Knipperen stoppen bij juiste invoer Na een uur automatisch sluiten Macro’s voor de timer maken Kiezen hoe het bestand sluit Timer automatisch inschakelen Voorkomen dat het bestand te snel sluit Nadeel voor lief nemen Webquery uitvoeren en lijst bijhouden Een query instellen Ieder kwartier de koersen vernieuwen Een fonds benadrukken De klok instellen Ieder kwartier gegevens ophalen en vastleggen Een doel stellen Verloop vastleggen in grafiek Query verwijderen

670 671 672 672 673 675 675 677 677 678 678 680 681 682 682 682 683 686 687 688 690 693 695 696

■ 21 Grafieken maken met VBA

697

Macro voor grafiek opnemen AddChart vergelijken met AddChart2 Grafiekelementen aanpassen Grafiektitel instellen Blok With...End With gebruiken Positie van de grafiek bepalen Afmetingen van de grafiek instellen Het grafiekgebied opmaken Set gebruiken voor nieuwe grafiek De codes toepassen Het tekengebied opmaken Legenda toevoegen Legenda en titel aanbrengen Reeksnaam wijzigen Een gegevensreeks opmaken Kolommen opmaken

697 699 699 700 701 702 703 706 708 709 710 712 713 714 715 716

xxvi

Het Complete Boek Excel VBA voor professionals

De horizontale as bijstellen De maatstreepjes verfijnen De verticale as aanpassen Getallen op de as opmaken Rasterlijnen toevoegen of verwijderen Horizontale rasterlijnen aanbrengen Verticale rasterlijnen toevoegen Codes toepassen op lijngrafiek Combinatiegrafiek maken Groeiende lijst automatisch op lengte houden Naambereiken instellen Naambereiken toepassen

■ 22 Vanuit Excel Word aansturen Alle opmerkingen apart afdrukken De code aanpassen Model arbeidscontract aan personeelslijst koppelen Koppelen zonder VBA Verbindingen maken Koppelingen bijwerken Vanuit Word het Excelblad openen Vanuit Excel het contract in Word afdrukken Word openen vanuit Excel Vanuit Excel Word aansturen Meer exemplaren afdrukken Verwijzing naar Word instellen Help! Fout bij laden van DLL Early binding of Late binding kiezen In Excel nagaan of Word is gestart

■A ■B

718 719 720 721 723 723 724 726 728 730 730 731

735 735 737 738 738 740 742 742 742 743 744 745 746 747 747 747

Vertaling Nederlands - Engels

749

De termen in het Engels terugvinden De interface in het Nederlands weergeven

750 765

Foutmeldingen Compileerfouten Fouten tijdens de uitvoering Leren van fouten Gemakzuchtig gekopieerd Systeemdatum gewijzigd met Date

767 768 771 773 773 773

xxvii

Inhoud

■C

Sneltoetsen in de Visual Basic Editor

775

■D

Codes voor opmaak

779

Opmaak van getallen instellen Opmaak van datums bepalen Opmaak van tijd opgeven Opmaak van datum en tijd instellen

■E

VBA-functies Rekenfuncties Functies voor tekenreeksen Functies voor datum en tijd Conversiefuncties Informatiefuncties

780 782 783 784

785 786 786 787 788 788

■F

VBA in plaats van Excel-functie

789

■G

Snel werken zonder macro’s

793

Bij het openen naar een vaste cel gaan Query op gezette tijden verversen Klassement opstellen en bijwerken

■ Index

xxviii

794 794 795

797

Inleiding Om te beginnen maak ik enkele algemene opmerkingen over dit boek, zodat u er prettig mee werkt. Excel werkt op veel apparaten, maar VBA werkt alleen als Excel op een computer of laptop staat. U krijgt een globaal overzicht van de inhoud. Ik deel een aantal afspraken met u over de opmaak en het gebruik van lettertypen, zowel voor de VBA-code als voor kaders. Ook is het belangrijk om te weten hoe coderegels worden afgebroken, want in dit boek gebeurt dat hier en daar vanwege de breedte van de pagina’s. Bent u liefhebber van sneltoetsen dan leest u hoe deze in de tekst tegenkomt. En u leest waar u gratis de voorbeeldbestanden kunt ophalen, plus een bonushoofdstuk.

Voor wie is dit boek bedoeld? Ik ga ervan uit dat u: I Excel voor uw werk gebruikt en efficiënter wilt werken, voor uw studie wilt

kennismaken met VBA of gewoon geïnteresseerd bent in de talloze mogelijkheden van VBA; I enige ervaring met Excel hebt; I geen of weinig ervaring hebt met programmeren; I gedreven bent om iets voor elkaar te krijgen; I enige kennis van de Engelse taal hebt. Ook al vertaal ik de vaktermen, het pro-

grammeren in VBA gebeurt in het Engels. Dit boek richt zich op Excel 2010, 2013 en 2016 (hebt u Office 365, dan is Excel 2016 op het moment van schrijven de nieuwste versie). De afbeeldingen zijn met deze versies gemaakt. Maar vrijwel alles wat in dit boek wordt beschreven kunt u ook in Excel 2007 gebruiken. Bent u een beginnende Excel-gebruiker, dan zult u aan dit boek weinig plezier beleven. Maakt u zich eerst de basiskennis van Excel eigen. I U leert werken met Excel 2016 aan de hand van het Handboek Microsoft Excel

2016 (ISBN 978-90-5940- 848-7). De inhoud hiervan staat ook vrijwel geheel in Het Complete Boek: Microsoft Office 2016 (ISBN 978-90-5940-889-0). I Het werken met Excel 2013 leert u met het Handboek Microsoft Excel 2013

(ISBN 978-90-5940-605-6). Deze inhoud staat ook geheel in Het Complete Boek: Microsoft Office 2013 (ISBN 978-90-5940-639-1). I Leren werken met Excel 2010 gaat soepel met het Handboek Microsoft Excel

2010 (ISBN 978-90-5940-467-0).

Inleiding

Hebt u de beginselen van Excel onder de knie, dan kunt u uw kennis verdiepen met: I Excel voor professionals derde editie (IBN 978-90-5940-8906). Dit lijvige boek-

werk bestrijkt de versies 2007 tot en met 2016.

Op welke apparaten werkt Excel VBA? Excel is het spreadsheetprogramma van het Office-pakket. Als dit pakket volledig op uw pc of laptop staat, is het programma VBA ook automatisch geïnstalleerd. Op andere apparaten is VBA niet beschikbaar. Excel als app op een tablet is een uitgeklede versie zonder VBA. Als u daarop een Excel-bestand met een macro opent, zullen de macro’s niet werken. Een macro opnemen of schrijven kan op een tablet evenmin. Dit geldt voor tablets die draaien op Android en voor die van Apple (die op iOS werken). Kortom: om met VBA te kunnen werken hebt u een pc of laptop met Excel nodig. Dat kan de versie 2016, 2013, 2010 of 2007 zijn. Hebt u Excel als onderdeel van Office 365, dan hebt u altijd de laatste versie van Excel (op het moment van schrijven is dat Excel 2016). Hoewel voor sommige onderdelen van Office 365 een internetverbinding nodig is, is het een misvatting dat deze versie volledig in de cloud zou werken. Ook Office 365 laat zich gewoon op de desktop installeren. Werkt u met Excel Online, dan wordt de werkmap weergegeven in de browser. Deze webapplicatie van Excel is een beperkte versie die via internet werkt. U hebt alleen de tabbladen Bestand, Startpagina, Invoegen, Gegevens en Beeld ter beschikking, waar minder knoppen op staan. Macro’s uitvoeren is niet mogelijk. Met Excel Online kunt u dus eenvoudige bewerkingen in uw werkmap uitvoeren. Wilt u er macro’s aan toevoegen, dan maakt u die werkmap in de complete versie van Excel. Bevat een werkmap eenmaal macro’s, dan kunt u die wel online opslaan. Maar probeert u een werkmap met macro’s te openen in Excel Online, dan kan dat niet. U krijgt dan een melding dat de werkmap niet kan worden geopend in de browser. Wilt u de macro kunnen uitvoeren of bewerken, dan hebt u een volledige versie van Excel nodig, op een pc of laptop. Hebt u een werkmap in de cloud opgeslagen (bijvoorbeeld op uw OneDrive), dan opent u deze via Documenten. Hier geldt hetzelfde: klikt u op de rechtermuisknop gevolgd door Openen in Excel, dan kunt u de werkmap in de volledige versie van Excel bewerken, dus ook de macro’s. Klikt u op de rechtermuisknop gevolgd door Openen in Excel Online, dan kunt u de werkmap online bewerken in de webapplicatie van Excel, maar is VBA niet beschikbaar.

xxx

Het Complete Boek Excel VBA voor professionals

Werken de macro’s bij Apple en OpenOffice? Op een Apple-computer kunt u in het programma Numbers wel een Excel-bestand met macro’s openen, maar die werken daarin helaas niet altijd of helemaal niet. Hoewel Microsoft beweert dat VBA ook werkt in Excel voor de Mac (Excel 2011 en 2016), lees ik veel berichten dat niet alle VBA-functies werken. Ook schijnt de ondersteuning in de Help minimaal te zijn. In ieder geval is de bediening van Excel op een Mac anders dan in dit boek wordt uitgelegd, dus dit boek sluit niet aan op Apple-computers. U kunt een Excel-bestand met macro’s ook openen met een programma als OpenOffice Calc. Maar daarin werken macro’s beperkt of helemaal niet, want OpenOffice ondersteunt niet alle functies van VBA. Wilt u macro’s schrijven in OpenOffice, dan kan dat in diverse programmeertalen, maar niet in VBA.

Werken de macro’s op een 64-bitssysteem? Ja. De meeste moderne computers werken met 64-bits, oudere systemen hebben vaak nog een 32-bitsprocessor. Met de 64-bitsversie kunt u met grotere hoeveelheden gegevens werken in Excel. Excel 2016, 2013 en 2010 kunnen met beide systemen overweg, in de standaardinstallatie wordt de 32-bitsversie geïnstalleerd, zelfs op 64-bitssystemen. Als u een 64-bitsversie van Windows hebt en u wilt een 64bitsversie van Office, moet u bij de installatie expliciet voor deze optie kiezen. Voor de 64-bitsversie vanaf Excel 2010 is een nieuwe versie van VBA ontwikkeld, namelijk VBA 7; deze kan dus 64-bits aan, maar is ook nog steeds geschikt voor 32-bits. Als u een werkmap met macro’s hebt gemaakt in de 32-bitsversie van Excel, zullen die ook werken als u deze opent in de 64-bitsversie van Excel. Alleen als daar statements voor Windows Application Programming Interface (API) in staan, moeten die worden aangepast om in de 64-bitsversie te werken. Deze zogeheten API’s worden in dit boek niet besproken. Kortom, de macro’s in dit boek werken ook op een 64-bitssysteem.

Werken met Excel in het Engels Werkt u met een Engelstalige Excel, dan moet u af en toe zoeken welke opdracht overeenkomt met wat in dit boek wordt genoemd. Raadpleeg dan appendix A, daarin vindt u alle tabs, knoppen en menuopdrachten die in dit boek worden besproken, vertaald in het Engels. Niet alleen de aanduidingen in Excel zijn vertaald, ook die in het VBA-venster. In appendix B zijn de foutmeldingen vertaald.

xxxi

Inleiding

Werken met dit boek Met dit boek leert u werken met VBA in Excel. U leert niet alleen wat u moet doen, maar de voorbeelden worden ook zo uitgelegd, dat u begrijpt hoe ze werken. Zo kunt u de codes toepassen op uw eigen situatie en gebruiken voor uw eigen werkzaamheden. U hoeft dit boek niet als een cursusboek van voor naar achter door te werken; al kan dat wel, want er zit een bewuste volgorde in de hoofdstukken. Mogelijk zult u ook niet alles gebruiken. U kunt de onderwerpen gericht opzoeken met de index achterin.

De inhoud overzien I Neemt u in ieder geval de hoofdstukken 1, 2 en 3 door, want in hoofdstuk 1

staan basistips als het opnemen van een macro, het maken van een knop om een macro te bedienen en het instellen van de beveiliging. Hoofdstuk 2 gaat over het werken met de Visual Basic Editor, dat is de omgeving waarin het programmeren plaats vindt. Vervolgens leest u in hoofdstuk 3 hoe u zelf code schrijft en welke hulpmiddelen daarvoor zijn. I Hoofdstuk 4 gaat over belangrijke bouwstenen die u kunt gebruiken. VBA is

gebaseerd op het aansturen van objecten. Dat gaat door middel van eigenschappen, methoden en argumenten. In hoofdstuk 5 leest u hoe u informatie vastlegt in variabelen om uw code snel en goed te kunnen uitvoeren. Ook constanten zijn daarvoor bijzonder nuttig. I Als u een macro opneemt, krijgt u een vrij lange code. Die kunt u inkorten,

waardoor de macro sneller werkt. Daarover leest u in hoofdstuk 6, waar u ook leest hoe u het zelf schrijven van de code tot een minimum beperkt en hoe u de uitvoering van de macro versnelt. I Bij het programmeren horen structuren: instructies die beslissingen nemen, lus-

sen die opdrachten herhalen en regels die de procedure terug laten keren. Over deze logica van Visual Basic gaan hoofdstuk 7 en 8. I In hoofdstuk 9 leest u dat de werking van macro’s niet beperkt hoeft te blijven

tot één bepaalde werkmap, u kunt ze algemeen inzetbaar maken. I U kunt macro’s met een knop bedienen, maar ze kunnen ook reageren op een

gebeurtenis. Ze worden dan automatisch uitgevoerd als u een bestand opent of sluit, een werkblad benadert of een verandering in een werkblad aanbrengt. Hoofdstuk 10 gaat daarover. I De wijze waarop u een werkmap opslaat of sluit, kunt u aan een macro overla-

ten. Ook over het opslaan als een PDF leest u in hoofdstuk 11. I Aan de hand van hoofdstuk 12 maakt u berichtvensters, hoofdstuk 13 toont

hoe u gegevens invult en verwerkt met een invoervenster. Hoofdstuk 14 vertelt

xxxii

Het Complete Boek Excel VBA voor professionals

uitvoerig hoe u een dialoogvenster maakt, daar kunt u naast de gebruikelijke knoppen ook tekstvakken aan toevoegen, keuzelijsten en keuzerondjes. Hoofdstuk 15 behandelt de andere elementen die u in een dialoogvenster kunt gebruiken: een keuzelijst zonder invoervak, een wisselknop, een schuifbalk, een kringveld, meerdere tabs, een venster om cellen te selecteren en een bouwsteen om datum en tijd te kiezen. Daar leest u ook hoe u het dialoogvenster kunt verfraaien. I VBA beschikt over een groot aantal ingebouwde functies. Rekenfuncties komen

in hoofdstuk 16 aan de orde. VBA kan rekenen en als de ingebouwde functies niet volstaan, schakelt u de rekenfuncties van Excel in. Functies voor tekst en informatie komen aan hoofdstuk 17 aan bod en met hoofdstuk 18 kunt u zelf functies maken, zoals voor het berekenen van oppervlakte en inhoud van ronde vormen, of om gekleurde cellen op te tellen. I Voor datums kunt u VBA-functies gebruiken en uw eigen functie schrijven. Zo

kunt u rekenen met datums voor 1900, wat in Excel niet kan. U kunt ook werken met tijd: om een digitale klok te maken of een timer die op gezette tijden in actie komt. Hoofdstuk 19 en 20 gaan daarover. I In hoofdstuk 21 leest u hoe u een macro programmeert die grafieken maakt en

hoe u de talloze mogelijkheden voor de kenmerken en opmaak van grafieken aanstuurt. I Het laatste hoofdstuk is grensoverschrijdend: daarin stuurt u Word aan vanuit

Excel. I In appendix A staan alle tabs, knoppen en menuopdrachten die in dit boek

worden besproken in het Nederlands en Engels naast elkaar, zowel van Excel als van het VBA-venster. In appendix B vindt u foutmeldingen die bij het programmeren of tijdens het uitvoeren kunnen opdoemen, met ook steeds de foutmelding in het Engels. In appendix C staan de sneltoetsen op een rij waarmee u de VBA-code sneller bewerkt. Appendix D geeft de codes voor opmaak van getallen, datum en tijd, appendix E geeft een overzicht van de ingebouwde VBA-functies en appendix F noemt de rekenfuncties die VBA zelf heeft en waarvoor u Excel niet kunt aanroepen. Ten slotte appendix G: ook al kunt u met macro’s zaken versnellen, ze zijn niet zaligmakend, want er zijn zaken waarvoor u geen macro nodig hebt om toch snel met Excel te kunnen werken.

Kennisnemen van afspraken Zaken die u op het beeldscherm aantreft, zijn vet gedrukt, zoals de naam van een tabblad of van een venster. U leest dus: het tabblad Start en het venster Zoeken. De toetsen op het toetsenbord worden aangeduid met toets, knoppen in het lint en in dialoogvensters worden knop genoemd. Namen van knoppen zijn vetgedrukt. U treft dus aanwijzingen aan zoals ‘druk op de Enter-toets’ of ‘klik op de knop Macro opnemen’.

xxxiii

Inleiding

Het lint van Excel bestaat uit tabbladen; het zichtbare rechthoekje met de aanduiding waarop u klikt, is een tab. Zo leest u: klik op de tab Start; op het tabblad dat opent, vindt u onder meer knoppen voor de opmaak. Opdrachten die u uitvoert, staan in de volgorde waarin u ze uitvoert, bijvoorbeeld: Klik op Bewerken en op Knippen. Ga twee regels omlaag en klik op Bewerken, Plakken. Moet u op een knop klikken, dan leest u na een puntkomma het effect van deze handeling. Bijvoorbeeld: Klik in het venster Celeigenschappen op de tab Getal; u ziet een tabblad met opties voor getalopmaak. Kunt u een opdracht ook op een andere manier uitvoeren, dan staat dat er ingesprongen onder. Bijvoorbeeld: Klik op de onderste helft van de knop Plakken en klik op Plakken speciaal; er verschijnt een venster. I Of klik met de rechtermuisknop in de doelcel en kies in het snelmenu Plakken

speciaal. Volgt er na een aantal instructies een aanvullende tip, dan is die ook ingesprongen. Bijvoorbeeld: U kunt het formaat van een vorm bijstellen door te slepen aan de greepjes eromheen. I Als u tijden het slepen aan de greepjes de Alt-toets ingedrukt houdt, wordt de

vorm uitgelijnd langs de lijnen van de cellen. Wanneer u in een dialoogvenster een keuze hebt gemaakt, moet u altijd op OK klikken. Dit spreekt vanzelf en noem ik meestal niet apart. Woorden die u moet overtypen, staan in een apart lettertype. U leest bijvoorbeeld: Typ tussen de beide coderegels de opdracht Unload Me. U begrijpt dat de punt aan het eind van deze opdracht het einde van de zin is, die typt u niet over. VBA-code staat in de Visual Basic Editor in een eigen lettertype. Dat lettertype komt in dit boek ook voor: losse woorden uit een code, namen van functies, argumenten, variabelen en macro’s hebben dit lettertype, evenals aparte regels code en een complete, werkende procedure. U treft bijvoorbeeld aan: Het woord Sub duidt het begin van de macro aan. U neemt de waarde uit cel C5 in cel B5 over met: Range(“B5").Value = Range(”C5").Value

xxxiv

Het Complete Boek Excel VBA voor professionals

De volgende code toont een berichtvenster: Sub MijnVenster() MsgBox “Hallo allemaal!” End Sub

Toelichtingen staan in een kader. Er zijn drie soorten, in de volgende opmaak. Sneller werken Een tip legt uit hoe u iets handig of snel kunt uitvoeren.

Meer informatie Een opmerking geeft extra informatie.

Let op Een waarschuwing wil voorkomen dat u een vergissing maakt.

Coderegels afbreken Als een opdrachtregel langer is dan de breedte van de pagina van dit boek, breek ik de regel af op de manier van VBA: met een spatie gevolgd door het onderstrepingsteken. In hoofdstuk 4 staat bijvoorbeeld het volgende voorbeeld van een berichtvenster: Sub MijnVenster() MsgBox “Wilt u doorgaan?”, _ vbYesNo + vbQuestion + vbDefaultButton2, _ “Voorbeeld van een bericht” End Sub

U kunt deze code overnemen met de beide afbrekingen. Dus na doorgaan?", typt u een spatie, drukt u op Shift+minteken (de underscore) en op de Enter-toets, typt u vbYesNo enzovoort en doet u na de komma achter vbDefaultButton2 hetzelfde. Maar u kunt de spatie met underscores ook overslaan en de drie middelste regels als één lange regel typen, zoals in de afbeelding.

I Afbeelding I.1 Regels die in dit boek zijn afgebroken kunt u zo overnemen of als één lange regel typen.

xxxv

Inleiding

Werken met sneltoetsen Veel opdrachten die u geeft door op een knop te klikken kunt u ook geven door op een sneltoets te drukken. Het is de moeite waard om u een aantal sneltoetsen eigen te maken voor handelingen die u vaak doet; dan werkt u sneller. Bekende sneltoetsen zijn Ctrl+X om te knippen en Ctrl+V om te plakken. Ook bij het opstellen van code kunt u sneltoetsen gebruiken. Als die mogelijkheid er is, wordt deze genoemd. De sneltoets staat soms tussen haakjes na de instructie. U leest bijvoorbeeld, om de Visual Basic Editor te openen: Klik met de rechtermuisknop op een bladtab (onderaan) en kies Programmacode weergeven (sneltoets: Alt+F11). Een andere keer staat de sneltoets ingesprongen onder de instructie, dus als: Klik met de rechtermuisknop op een bladtab (onderaan) en kies Programmacode weergeven. I Of open de Visual Basic Editor snel met de sneltoets Alt+F11.

Achter in het boek vindt u een overzicht van veel sneltoetsen die u kunt gebruiken bij het opstellen van uw code. De versies van Excel waarover dit boek gaat, hebben het lint (ribbon). Daarin werkt ook een ander systeem van sneltoetsen: drukt u op de Alt-toets, dan verschijnen er letters bij iedere tab van het lint. Door een van die letters te typen, opent u het betreffende tabblad. Daarin staan bij iedere knop letters en/of cijfers. Typ het teken van uw keuze, maak eventueel nog vervolgkeuzes en baan u zo een weg door het lint. Voor knippen zou u achtereenvolgens moeten drukken op Alt, R en X, en voor plakken op Alt, R en V. Maar ik vind dat niet echt handig en zeker niet snel. De enige keer dat ik deze methode noem, is voor het invoegen van een module; daarvoor drukt u op Alt+N, M (dit is een sneltoets in de Editor van Visual Basic).

Voorbeeldbestanden ophalen De macroâ€™s die in dit boek worden besproken, kunt u in de vorm van voorbeeldbestanden gratis ophalen van mijn website www.vbauitleg.nl die met dit boek verbonden is. Er zijn ruim 100 werkmappen beschikbaar. Scrol op deze homepagina omlaag naar Excel VBA voor professionals Tweede Editie, klik met de rechtermuisknop op Voorbeeldbestanden downloaden en kies Doel opslaan als; u ontvangt een zip-bestand met de werkmappen. Als er een werkmap beschikbaar is, ziet u dat als volgt in de tekst: Voorbeeld downloaden Het voorbeeld bij deze uitleg is de werkmap 01 Verkopers Sorteren.xlsm.

xxxvi

Het Complete Boek Excel VBA voor professionals

Het nummer waarmee iedere bestandsnaam begint, is het hoofdstuk waarin dat bestand wordt besproken. In deze bestanden is de bijbehorende programmacode ingebouwd. Op dezelfde website vindt u ook andere bestanden die u gratis kunt downloaden.

Extra hoofdstuk downloaden U treft op de website www.vbauitleg.nl een bonushoofdstuk aan over VBA in Word (want ook daarin werkt VBA). Daarin leest u hoe u een dialoogvenster maakt waarin u een adres opgeeft. Vervolgens wordt een vel A4 afgedrukt met dat ene adres op alle etiketten. Klik op VBA in Word: Een dialoogvenster voor etiketten; er gaat een PDF-bestand open. Wilt u dit bestand op uw computer opslaan, klik dan met de rechtermuisknop op die link en kies Doel opslaan als. U vindt dit hoofdstuk ook op de website www.vanduurenmedia.nl. Ga in het menu Downloads & Support naar Downloads en klik (of klik met de rechtermuisknop) op de titel Excel VBA voor Professionals.

Over de auteur Wim de Groot (1959) schrijft boeken over Excel bij Van Duuren Media en artikelen voor het populaire tijdschrift ComputerIdee. Hij verzorgt cursussen Excel op locatie en ontwikkelt spreadsheets op maat voor bedrijven. Hij heeft al veel mensen weten te boeien met dit krachtige rekenprogramma. Als nuchtere noorderling doet hij niet moeilijk over zaken die ingewikkeld lijken. Hij is sterk in helder communiceren en dat doet hij ook als hij uitleg geeft over Excel en VBA. Aan beginnende en gevorderde gebruikers laat hij zien hoe ze de eindeloze mogelijkheden van dit rekenwonder kunnen benutten. Wim helpt u graag om effectief te werken. Hij geeft informatie over Excel op zijn website www.exceltekstenuitleg.nl en hij deelt zijn kennis over VBA op www.vbauitleg.nl.

xxxvii

Hoofdstuk 1

Macro’s opnemen en uitvoeren De eenvoudigste manier om het gemak van VBA te leren kennen, is door een macro op te nemen. Met een macro kunt u vaak voorkomende handelingen automatiseren. Tijdens de opname van de macro doet u die handelingen één keer. Hebt u een macro eenmaal opgenomen, dan kunt u die zo vaak uitvoeren als u maar wilt en worden die handelingen supersnel voor u gedaan. U drukt daarvoor op een zelfgekozen sneltoets. Die sneltoets moet u dan onthouden, maar u kunt de macro ook met een knop bedienen; dat is prettig als iemand anders met uw macro moet werken. U plaatst de knop in het werkblad. Hiervoor voegt u een vorm in, die u van een opschrift voorziet. U kunt uw macro’s ook bedienen met keuzerondjes, als u een adreslijst op verschillende manieren wilt sorteren. Werkt uw macro nog niet meteen, dan moet u mogelijk de beveiliging bijstellen.

Kennismaken met een macro Als u bepaalde handelingen in Excel regelmatig uitvoert, bespaart u zich veel moeite als u die handelingen vastlegt in een macro. Wanneer u de macro daarna uitvoert, worden deze handelingen snel en foutloos gedaan. Het volgende voorbeeld is een overzicht van de resultaten van verkopers in een bepaalde maand. Degene met het grootste bedrag staat bovenaan. Als de bedragen veranderen, moet u de lijst opnieuw sorteren om de verkoper bovenaan te zien die dan het grootste bedrag heeft. Hiervoor moet u steeds opnieuw het betreffende gebied selecteren en in het sorteervenster uw opties kiezen. Deze handelingen legt u vast door eenmalig een macro op te nemen. Daarna sorteert u de lijst met een druk op knop, zo vaak u wilt. Vergelijk dit met een geluidsopname: de muziek die een groep eenmaal heeft opgenomen, kunt u zo vaak afspelen als u maar wilt.

I Afbeelding 1.1 Het sorteren van een dergelijk overzicht legt u vast in de macro.

Hoofdstuk 1 Macroâ€™s opnemen en uitvoeren

De macro opnemen Voer de volgende opdrachten uit. De uitleg van deze stappen leest u verderop. U start de opname van een macro door te klikken op de tab Beeld; het tabblad Beeld verschijnt. Klik op de onderste helft van de knop Macroâ€™s; er verschijnt een menu. Klik op Macro opnemen; het venster Macro opnemen verschijnt.

I Afbeelding 1.2 U start een opname met een klik op de knop Macro opnemen.

Vul onder Macronaam in: Sorteren. Klik onder Sneltoets in het vakje bij Ctrl+ en typ de letter q. Kies met de keuzelijst onder Macro opslaan in: de optie Deze werkmap (dat is de standaardoptie). Klik op OK. U voert nu precies de bewerkingen uit die u wilt vastleggen. Selecteer de cellen A1 tot en met D10 (in dit voorbeeld). Klik op de tab Gegevens en op de (grote) knop Sorteren; het venster Sorteren verschijnt. Schakel rechtsboven de optie in: De

I Afbeelding 1.3 Voor de opname geeft u een naam, een sneltoets en de opslagplaats op.

Het Complete Boek Excel VBA voor professionals

gegevens bevatten kopteksten. Kies in de eerste keuzelijst onder Kolom bij Sorteren op de optie Verkoop. Controleer of de tweede keuzelijst onder Sorteren op de optie Waarden heeft en kies in de keuzelijst Volgorde de optie Van groot naar klein. Klik op OK en de lijst wordt gesorteerd. De verkoper met het grootste bedrag staat nu bovenaan. Klik op cel D2, zodat de hele tabel niet meer geselecteerd is. Deze handelingen zijn vastgelegd. Zet nu de opname stop. Klik hiervoor in het tabblad Beeld op Macro’s gevolgd door Opname stoppen. Het resultaat ziet u in de afbeelding.

I Afbeelding 1.4 U hebt de tabel gesorteerd van groot naar klein en dat vastgelegd in de macro.

Voorbeeld downloaden Het voorbeeld bij deze uitleg is de werkmap 01 Verkopers Sorteren.xlsm.

De macro uitvoeren De macro is opgenomen en u kunt deze meteen testen. Verander enkele bestaande bedragen in de kolom Verkoop zodat ze groter zijn dan het bedrag dat bovenin staat. Druk op de sneltoets Ctrl+Q; de tabel wordt opnieuw gesorteerd en u ervaart meteen het gemak van een macro.

I Afbeelding 1.5 Als u enkele bedragen hebt veranderd, sorteert u de lijst opnieuw met één druk op de toetsen Ctrl+Q.

Hoofdstuk 1 Macro’s opnemen en uitvoeren

Help! De macro doet het niet Krijgt u een melding dat de macro’s zijn uitgeschakeld of gebeurt er helemaal niets? Lees dan de instructies over de beveiliging in de paragraaf Macrobeveiliging instellen verderop in dit hoofdstuk. Macro werkt nog niet in een langere lijst Als u nieuwe bedragen onder aan de bestaande tabel toevoegt en u drukt daarna op uw sneltoets Ctrl+Q, zult u merken dat deze niet mee worden gesorteerd. Dat komt doordat in de macro exact de cellen A1 tot en met D10 zijn vastgelegd. Hoe u zorgt dat een langere lijst goed wordt gesorteerd, leest u in de paragraaf Meer cellen sorteren.

De opties in het venster langslopen Zoals u hebt gemerkt, bestaat het opnemen van een macro uit drie stappen: I de opname voorbereiden (naam, opslagplaats en sneltoets opgeven); I de eigenlijke macro opnemen; I de opname stopzetten.

Na deze beknopte introductie lopen we deze stappen nu uitvoeriger langs. U begint een opname door in het tabblad Beeld te klikken op Macro’s en op Macro opnemen; het venster Macro opnemen verschijnt (afbeelding 1.3).

De macro een naam geven In het eerste vak in het venster Macro opnemen bedenkt u een naam voor deze macro. Standaard staat er Macro1 (of een ander volgnummer). Dat kunt u zo laten, maar het is duidelijker als de naam aanduidt wat deze macro doet. In het voorbeeld hebt u als naam Sorteren ingevuld. De naam van de macro mag niet met een cijfer beginnen, verderop mag er wel een cijfer in voorkomen. De naam mag geen spatie bevatten, geen punt of komma en geen tekens als #, &, $, +, = of *. Typt u in de macronaam een ongeldig teken, dan krijgt u de foutmelding: Ongeldige procedurenaam. Wilt u dat uw macronaam uit twee (of meer) woorden bestaat, dan is het gebruikelijk die aan elkaar te typen en ieder woord met een hoofdletter te laten beginnen, bijvoorbeeld BedragenSorteren. I U mag woorden ook scheiden met het onderstrepingsteken, als in Bedragen_ Sorteren.

Het Complete Boek Excel VBA voor professionals

Een sneltoets opgeven In het vakje bij Sneltoets geeft u op, met welke sneltoets de macro straks wordt uitgevoerd, nadat deze is opgenomen. Typ in het vakje bij Ctrl+ een letter, laten we hiervoor de q nemen. De macro wordt dan uitgevoerd door te klikken op de sneltoets Ctrl+Q. Als u hier de hoofdletter Q typt, verschijnt er in het venster Ctrl+Shift+ en voert u de macro uit door te klikken op de sneltoets Ctrl+Shift+Q. I Overigens kunt u het vak bij Sneltoets ook leeg laten, want er zijn diverse

andere manieren om de macro uit te voeren, bijvoorbeeld door er een knop voor te maken (daarover leest u verderop in dit hoofdstuk). Sneltoetsen met een hoofdletter? Het is gebruikelijk om sneltoetsen met een hoofdletter te schrijven. Als u in dit boek leest: sneltoets Ctrl+C, drukt u op de Ctrl-toets en enkel op de letter c. U hoeft dus niet tegelijk de Shift-toets in te drukken voor de hoofdletter C. Als u in het venster Macro opnemen de sneltoets wel bewust opgeeft met een hoofdletter, moet u voor het uitvoeren van die macro wel de toetsen Ctrl+Shift ingedrukt houden als u op die letter drukt. Welke sneltoets kiest u? Voor de macro die sorteert, ligt het voor de hand om de s van sorteren te kiezen. Maar zoals u weet, bestaat de sneltoets Ctrl+S al, daarmee slaat u een bestand op. De opdracht â€˜Sorterenâ€™ komt dan in plaats van het opslaan. Bovendien werkt deze eigen sneltoets alleen in het werkblad waarin u de macro hebt opgenomen. Dat zou betekenen dat u in deze werkmap met de verkopers door Ctrl+S de lijst sorteert en in alle andere werkmappen (die deze macro niet hebben) met Ctrl+S dat bestand opslaat. Om verwarring te voorkomen kiest u een letter die geen (of een onbelangrijke) functie heeft, zoals de e, j, l, m of q. Uw mag voor de sneltoets niet een getal of een speciaal teken als @ of # opgeven. I Of stel sneltoetsen met een hoofdletter in, die u dus bedient met Ctrl+Shift. I Of maak een knop in het werkblad (zie verderop in dit hoofdstuk).

Het is geen probleem als u in het ene bestand een macro bedient met bijvoorbeeld Ctrl+Q om een lijst te sorteren en in een andere werkmap met hetzelfde Ctrl+Q een macro bedient die bijvoorbeeld gegevens kopieert. Er ontstaat wel een probleem als u de beide bestanden tegelijk open hebt en op uw sneltoets drukt. Want dan zal de sneltoets die een macro in het ene bestand uitvoert, proberen dezelfde macro ook in het andere bestand uit te voeren. Daardoor wordt de sorteeractie van de ene werkmap ook in de andere werkmap uitgevoerd, of de macro loopt vast als een opdracht in de andere werkmap niet kan worden uitgevoerd. I Vuistregel: als u voor verschillende bestanden dezelfde sneltoets kiest, open die

bestanden dan niet tegelijk.

Hoofdstuk 1 Macro’s opnemen en uitvoeren

De sneltoets veranderen Hebt u eenmaal een sneltoets aan de macro toegewezen, dan kunt u die later veranderen. Dat geldt ook als u bij het opnemen van de macro geen sneltoets hebt opgegeven en dat later wilt doen. Klik in het tabblad Beeld op de bovenste helft van de knop Macro’s; er verschijnt een venster met uw macro’s. I Of druk op de sneltoets Alt+F8.

Kies uw macro en klik op Opties; het venster Macro-opties verschijnt. Geef bij Sneltoets: Ctrl+ uw sneltoets op. Bedenkt u zich en wilt u later een andere letter aan de macro toekennen, dan gaat dat op dezelfde manier.

De opslagplaats kiezen Verder ziet u in het venster Macro opnemen de keuzelijst Macro opslaan in:. Hiermee geeft u op, in welke werkmappen deze macro beschikbaar moet zijn. U hebt de keuze uit Deze werkmap, Nieuwe werkmap en Persoonlijke Macrowerkmap. I Kiest u Deze werkmap, dan wordt de macro opgeslagen als onderdeel van de

werkmap waaraan u nu werkt. Deze macro werkt dan alleen in deze werkmap. I Kiest u Nieuwe werkmap, dan opent Excel een nieuw bestand; de huidige

werkmap blijft wel geopend. Na het opnemen van de macro wordt deze opgeslagen in de nieuwe werkmap. I Omdat de macro meestal iets doet, wat alleen in één bepaald werkblad moet

gebeuren, is het verstandig om een macro niet te laten werken in alle andere werkmappen. Stel, u hebt een werkmap met adressen en een macro sorteert hiervan de cellen A2 tot en met E100. Vervolgens opent u een werkmap met de jaarcijfers van uw bedrijf en laat daarin deze macro dezelfde sorteeractie uitvoeren: u wilt niet weten wat de gevolgen zijn.

I Afbeelding 1.6 Hier kiest u waar u de macro wilt bewaren. Dat is meestal de werkmap waarin deze moet werken.

Het Complete Boek Excel VBA voor professionals

I Toch zijn er macro’s die handig zijn om in alle werkmappen te gebruiken.

Daarvoor kiest u de optie Persoonlijke macrowerkmap. Meer hierover leest u in hoofdstuk 9, in de paragraaf Algemeen bruikbare macro’s maken.

Een toelichting schrijven Onder Beschrijving is ruimte om uw eigen toelichting te typen. Dit is niet verplicht, maar het kan handig zijn om te noteren wat deze macro doet, wie deze heeft opgenomen en wanneer. Als u bij het opnemen van de macro geen beschrijving hebt getypt, maar later wilt toevoegen, klik dan in het tabblad Beeld op de bovenste helft van de knop Macro’s; er verschijnt een venster met uw macro’s. I Of druk op de sneltoets Alt+F8.

Kies uw macro en klik op Opties; het venster Macro-opties verschijnt. Typ daar uw toelichting. Als u zelf een rekenfunctie gaat schrijven in VBA, kunt u op deze plaats de toelichting typen die de gebruikers vervolgens in het dialoogvenster zien als ze een formule met uw functie opstellen (zie hoofdstuk 18, de paragraaf Eigen uitleg aan dialoogvenster toevoegen).

De eigenlijke macro opnemen Zoals u leest in de paragraaf De macro opnemen voert u tijdens de opname precies de bewerkingen uit die u wilt vastleggen. Op de achtergrond worden uw handelingen omgezet in VBA-code. Alles wat u doet wordt vastgelegd: uw handelingen, keuzes in een dialoogvenster, muisbewegingen, scrollen door het scherm, wat u typt enzovoort. Probeer u tijdens de opname te beperken tot de noodzakelijke handelingen. Want elke overbodige beweging zal uw macro hierna ook uitvoeren en daardoor wordt deze onnodig traag. Het kan helpen om alle bewerkingen eerst uit te voeren zonder een macro op te nemen en daarbij alle stappen te noteren; vervolgens neemt u aan de hand van uw notities de macro op.

De opname stoppen Het is erg belangrijk dat u de opname stop zet. Want anders worden alle handelingen die u hierna nog doet, ook vastgelegd en dat zal vast niet uw bedoeling zijn. Om de opname te stoppen klikt u in het tabblad Beeld op de onderste helft van de knop Macro’s. De optie Macro opnemen is veranderd in Opname stoppen met een lichtblauw blokje. Klik daarop. I U kunt voor het opnemen en stoppen van een macro ook de knop Macro

opnemen in de statusbalk gebruiken. Staat die knop daar nog niet, lees dan de paragraaf Opnameknop in de statusbalk plaatsen. Dan hebt u de opnameknop altijd linksonder in beeld en is deze gemakkelijker te bereiken. Tijdens een

Hoofdstuk 1 Macroâ€™s opnemen en uitvoeren

opname verandert dat vlak in een wit blokje (in Excel 2010 en 2007 in een lichtblauw blokje). Zo wordt u eraan herinnerd dat de opname loopt en dat u deze moet stoppen. Houdt u de muisaanwijzer op dat blokje, dan leest u: Er wordt momenteel een macro opgenomen. Klik als u de opname wilt beĂŤindigen.

I Afbeelding 1.7 Twee plaatsen waar u de opname kunt stoppen.

Help! Ik heb mij vergist Is het opnemen van de macro niet meteen foutloos gegaan, klik dan opnieuw op Macro opnemen en typ onder Macronaam dezelfde naam als in de eerste poging. In dit voorbeeld: typ daar weer Sorteren. Hebt u in de eerste poging de standaardnaam Macro1 laten staan, dan staat er bij de tweede poging Macro2. Verander dat dan in Macro1. Excel zal melden: Er bestaat al een macro met deze naam. Wilt u deze overschrijven? Klik op Ja en voer de handelingen opnieuw uit.

Het venster van Excel inrichten Het is de moeite waard om enkele zaken in te richten, waarvan u gemak zult hebben. De aanwijzingen in dit boek komen dan het meest overeen met wat u op uw beeldscherm ziet en de meeste afbeeldingen zullen ook herkenbaar zijn. Ik raad u aan om een aantal zaken als volgt in te stellen.

Het Complete Boek Excel VBA voor professionals

Opnameknop in de statusbalk plaatsen Maak de knop Macro opnemen zichtbaar in de statusbalk (onder in beeld). Dan hebt u deze altijd binnen handbereik. Klik hiervoor met de rechtermuisknop op de statusbalk; de keuzelijst Statusbalk aanpassen verschijnt. Schakel de optie Macro opnemen in; de opnameknop verschijnt links onderin de statusbalk, naast het woord Gereed. Tijdens een opname verandert de knop Macro opnemen in de statusbalk in een wit vierkant (in Excel 2010 en 2007 in een lichtblauw vierkant). Vergeet u niet, nadat u de bewerkingen hebt uitgevoerd, de opname van de macro stop te zetten.

I Afbeelding 1.8 Plaats de knop Macro opnemen in de statusbalk. Hiermee start en stopt u de opname.

Het tabblad Ontwikkelaars openen Breng het tabblad Ontwikkelaars in beeld, als volgt. Klik met de rechtermuisknop op een van de tabs in het lint en kies Het lint aanpassen; in het venster dat verschijnt, staan rechts de Hoofdtabbladen onder elkaar. Schakel Ontwikkelaars in; rechts in het lint verschijnt een extra tab, van het tabblad Ontwikkelaars. I Wilt u dit tabblad te zijner tijd weer sluiten, dan schakelt u de genoemde optie

op dezelfde plaats uit. Het tabblad Ontwikkelaars bevat onder meer de volgende knoppen. I De knop Macro opnemen ziet er net zo uit als de knop in de statusbalk (zie de

vorige paragraaf). Ook met deze knop kunt u een opname starten. Tijdens het opnemen heeft deze knop het opschrift Opname stoppen. Als u daar op dat moment op klikt, stopt u de opname. I De knop Macro’s toont een lijst met uw macro’s. Zodra u een macro hebt

gemaakt, krijgt u na een klik op deze knop een venster met uw macro’s. Kies hieruit uw macro en klik op Uitvoeren om deze uit te voeren.

Hoofdstuk 1 Macro’s opnemen en uitvoeren

I Afbeelding 1.9 Dit zijn de belangrijkste knoppen in het tabblad Ontwikkelaars.

I Met de knop Macrobeveiliging bereikt u het venster waarin u het niveau van

beveiliging kunt instellen; hierover leest u meer in de paragraaf Macrobeveiliging instellen. I Met de knop Visual Basic komt u in het venster met de macrocode (zie de

paragraaf De code verkennen verderop in dit hoofdstuk). I De knop Invoegen bevat besturingselementen die u in het werkblad kunt plaat-

sen. De elementen voor uw eigen dialoogvensters haalt u ergens anders vandaan (daarover leest u vanaf hoofdstuk 14). I De knop Programmacode weergeven doet hetzelfde als de knop Visual Basic;

hiermee opent u het venster met de macrocode.

De macro anders aansturen U hebt gelezen dat u een macro uitvoert door op uw zelfgekozen sneltoets te drukken. U kunt ook in het tabblad Ontwikkelaars klikken op de knop Macro’s; er verschijnt een venster met uw beschikbare macro’s. I Of klik in het tabblad Beeld op de knop Macro’s. I Of roep dit venster op met de sneltoets Alt+F8.

Kies hierin uw macro en klik op Uitvoeren om deze uit te voeren. U begrijpt dat dit venster pas macro’s toont nadat u er minstens een hebt gemaakt.

Het Complete Boek Excel VBA voor professionals

I Afbeelding 1.10 U kunt uw macro ook uitvoeren via de knop Macroâ€™s.

Inkomsten per week onder elkaar zetten Ik geef nog een voorbeeld. Stel, u noteert in een tabel de inkomsten van uw bedrijf per week op de verschillende dagen. U typt het nummer van de week en onder elke dag de totale inkomsten, in een staatje als de volgende afbeelding. In J4 worden de inkomsten opgeteld. U wilt een overzicht van alle weken opbouwen. Hiervoor moeten de bedragen van week 1 omlaag worden gekopieerd en moet de tabel worden leeg gemaakt om week 2 te kunnen invullen. Ook de bedragen van week 2 wilt u omlaag kopiĂŤren, hiervoor moet een rij worden ingevoegd boven de bestaande rij van week 1. Zo bouwt u de historie op, waarin week 1 steeds verder omlaag schuift en de meest actuele week bovenaan staat.

I Afbeelding 1.11 U wilt een overzicht van alle weken onder elkaar.

Deze stappen legt u als volgt vast in een macro. Start de opname en geef in het openingsvenster als naam bijvoorbeeld Weekstaat op. U kunt het opgeven van een sneltoets deze keer overslaan, want u kunt een knop maken om deze macro uit te voeren, volgens de aanwijzingen in de volgende paragraaf Een knop in het werkblad plaatsen. Selecteer heel rij 5 (de lege rij in dit voorbeeld), klik met de rechtermuis-

Hoofdstuk 1 Macro’s opnemen en uitvoeren

knop op rijnummer 5; er verschijnt een menu. Kies Invoegen (sneltoets: Ctrl+plusteken). Selecteer van de tabel de cellen B4 tot en met J4 en kies Kopiëren, klik op cel B6 (dit is dus een cel lager dan de zopas ingevoegde rij) en klik op Plakken. Selecteer de bestaande cijfers in de tabel (B4 tot en met I4, dus zonder de formule in J4) en druk op de Delete-toets om deze cellen leeg te maken. Klik op cel B4, zodat u in de eerste cel staat om de nieuwe week in te vullen. Stop de opname. Maak een knop voor deze macro volgens de aanwijzingen in de volgende paragraaf Een knop in het werkblad plaatsen. Als u nu in het bovenste staatje het nummer van de volgende week invoert met de inkomsten ernaast en op de knop klikt, worden de cijfers van die week toegevoegd aan de tabel eronder. Die komen bovenaan het overzicht en de bestaande gegevens schuiven omlaag. Zo komen alle weken onder elkaar en bouwt u de historie van dat jaar op. I Klikt u een keer te vaak op de knop, dan komende de gegevens van dezelfde

week twee keer in het jaaroverzicht. U verwijdert dan de overbodige rij als volgt. Klik met de rechtermuisknop op het rijnummer van de rij; er verschijnt een menu. Kies Verwijderen (sneltoets: Ctrl+minteken).

I Afbeelding 1.12 U krijgt met één klik op de knop een overzicht van alle weken onder elkaar.

Voorbeeld downloaden Het voorbeeld bij deze uitleg is de werkmap 01 Weekinkomsten Historie.xlsm.

Een knop in het werkblad plaatsen U kunt een macro uitvoeren door te drukken op de sneltoets die u tijdens de opname hebt ingesteld, het kan via de knop Macro’s in de tabbladen Ontwikkelaars en Beeld, maar u kunt er ook een eigen knop voor maken. Omdat een macro meestal een taak heeft in één bepaald werkblad, plaatst u de knop in dat werkblad. Dan stuurt u met de knop de macro die alleen in dat werkblad iets doet. I U kunt ook een knop plaatsen in de werkbalk Snelle toegang of een eigen tab-

blad in het lint maken met uw eigen knoppen. Hoe dat werkt, leest u in hoofdstuk 9, de paragraaf Knoppen aan het lint toevoegen.

Het Complete Boek Excel VBA voor professionals

Een knop maken Voor knoppen in het werkblad kunt u kiezen uit allerlei vormen in het tabblad Invoegen. Klik op de tab Invoegen en klik op Vormen. Er gaat een uitgebreid menu open met vormen waarmee u een knop kunt maken. Aan elke vorm die u tekent, kunt u een macro koppelen.

I Afbeelding 1.13 Vormen voor een knop vindt u in het tabblad Invoegen. Een knop met een schuine rand staat professioneel.

Als voorbeeld maken we van een rechthoek met een schuine rand een knop om de lijst met verkopers te sorteren. Open de werkmap 01 Verkopers Sorteren.xlsm (of welke naam u aan uw werkmap hebt gegeven). Klik in het tabblad Invoegen op Vormen, kies in het menu in de groep Basisvormen de Schuine rand. Klik ergens in het werkblad en sleep diagonaal. Zodra u loslaat, verschijnt er een rechthoek met een schuine rand. U kunt het formaat van deze vorm bijstellen door te slepen aan de greepjes eromheen. I Als u tijden het slepen aan de greepjes de Alt-toets ingedrukt houdt, wordt de

vorm uitgelijnd langs de lijnen van de cellen. Zolang de rechthoek geselecteerd is, is de tab Hulpmiddelen voor tekenen actief. Met de knop Opvullen van vorm geeft u de knop als geheel een andere kleur. Klik op Opvullen van vorm; er verschijnt een menu. Klik op een kleur uit dit palet; het voorbeeld is geel. I Is uw kleur hier niet bij, kies dan Meer opvulkleuren, maak uw keuze en klik

op OK. I Wilt u liever overlopende kleuren, dan kiest u in dit menu Kleurovergang en

kiest u uit het vervolgmenu een van de mogelijkheden.

Hoofdstuk 1 Macroâ€™s opnemen en uitvoeren

I Afbeelding 1.14 Via Opvullen geeft u de knop uw kleur en via Omtrek een rand.

Om aan de randen van de knop uw eigen kleur te geven, klikt u op de rechthoek; de tab Hulpmiddelen voor tekenen wordt actief. Klik op de knop Omtrek van vorm en kies uw eigen kleur voor de rand. Via Dikte in dit menu kiest u de breedte van de lijnen. Het voorbeeld heeft als kleur Automatisch (dat is zwart) met een dikte van 1 pt. I De breedte van de rand stelt u in door te slepen aan de gele ruit.

Eerst opmaken Ik raad u aan om de knop op te maken voordat u de macro eraan koppelt. Want dan kunt u op de knop klikken en deze opmaken zonder dat de macro al iets doet. Hebt u eenmaal een macro aan de knop verbonden en wilt u daarna het uiterlijk van de knop veranderen, houd dan de Ctrl-toets ingedrukt, klik op uw knop en klik op de tab Opmaak die dan verschijnt.

De knop een opschrift geven Om aan te duiden waar deze knop voor dient, brengt u als volgt een opschrift aan. Klik met de rechtermuisknop op de knop en kies Tekst bewerken; in de rechthoek knippert een cursor. Typ uw aanduiding, in dit voorbeeld: Sorteren en klik ergens naast de knop. U geeft de tekst als volgt een eigen kleur. Klik met de rechtermuisknop op de knop en kies Tekst bewerken. Selecteer de tekst in de knop, klik in het tabblad Start op het pijltje rechts van de knop Tekstkleur en kies een kleur uit het palet dat verschijnt; in het voorbeeld nemen we Automatisch (dat is zwart). Klik op de knop Vet om de tekst vet te maken.

Het Complete Boek Excel VBA voor professionals

I Afbeelding 1.15 U maakt de knop helemaal volgens uw smaak.

I Met de knoppen Cursief en/of Onderstrepen maakt u het opschrift cursief of

onderstreept of beide. Als de tekst nog niet automatisch in het midden staat, klikt u in het tabblad Start op de knop Centreren (om de tekst horizontaal in het midden te plaatsen) en Midden uitlijnen (om de tekst verticaal te centreren).

Macro aan de knop koppelen Is uw knop klaar, dan koppelt u deze aan de macro. Zoals opgemerkt is het handig om dit te doen na het opmaken van de knop. Klik met de rechtermuisknop op de knop en kies Macro toewijzen; er verschijnt een venster met een lijst van uw macro’s. Selecteer hieruit uw macro; in dit voorbeeld Sorteren. I Staan er in venster meer macro’s door elkaar, kies dan met de keuzelijst bij

Macro’s in voor Deze werkmap; dan ziet u hier alleen de macro’s die in deze werkmap voorkomen.

I Afbeelding 1.16 Verbind de macro aan de knop en de lijst wordt met één klik op de knop gesorteerd.

Hoofdstuk 1 Macroâ€™s opnemen en uitvoeren

Na een klik op OK is de knop met de macro verbonden. Klik op een willekeurige plaats in het werkblad; de greepjes verdwijnen. Als u de knop nu met de muiswijzer benadert, verandert deze in een handje: uw knop is actief. Klik op de knop en uw macro wordt uitgevoerd. Knop wijzigen Hebt u eenmaal een macro aan de knop verbonden, dan houdt klikken in dat de macro wordt gestart (de muiswijzer verandert dan in een handje). Wilt u daarna het uiterlijk van de knop veranderen, houd dan de Ctrl-toets ingedrukt en klik erop (of klik met de rechtermuisknop op de knop); de tab Opmaak verschijnt dan, de witte grepen worden weer zichtbaar en u kunt de knop zelf wijzigen. Bent u klaar, dan klikt u ergens naast de knop; de greepjes verdwijnen.

Een knop verwijderen Om een knop te verwijderen klikt u erop met de rechtermuisknop en kiest u Knippen. I Of klik met ingedrukte Ctrl-toets op de knop en druk op de Delete-toets.

Een standaardknop invoegen Excel kent ook een standaardknop, een besturingselement geheten. Deze maakt u als volgt. Klik op de tab Ontwikkelaars, klik op de knop Invoegen; het menu Formulierbesturingselementen verschijnt. Klik op de eerste optie Knop, klik in het werkblad en sleep diagonaal. Laat los en meteen gaat het venster Macro toewijzen aan object open, met een lijst van uw macroâ€™s. U kunt een macro aan de knop toewijzen, door die uit de lijst te selecteren en op OK te klikken. I U hoeft in het venster Macro toewijzen aan object nog niet meteen een macro

aan de knop te verbinden. Klik in dat geval op Annuleren (of druk op de Esctoets). Om de knop op een later moment aan de macro te koppelen klikt u erop

I Afbeelding 1.17 In de tab Ontwikkelaars vindt u de standaardknop.

Het Complete Boek Excel VBA voor professionals

met de rechtermuisknop en kiest u in het menu dat verschijnt, Macro toewijzen; de lijst met uw macro’s verschijnt. Selecteer uw macro en klik op OK. U kunt deze knop niet kleuren, deze is er alleen in het grijs. U kunt er wel een eigen opschrift op aanbrengen. Klik hiervoor met de rechtermuisknop op de knop, klik op de tekst Knop 1 (dit nummer kan hoger zijn), verwijder die tekst, typ in plaats daarvan uw eigen aanduiding, bijvoorbeeld Sorteren en klik ergens naast de knop. Wilt u het formaat van de knop wijzigen, klik er dan op met de rechtermuisknop; de greepjes verschijnen rondom. Sleep aan de greepjes. Als u tijden het slepen aan de greepjes de Alt-toets ingedrukt houdt, wordt de vorm uitgelijnd langs de lijnen van de cellen.

Een werkmap met macro opslaan Wilt u de werkmap opslaan die een macro bevat, dan moet u die bewust opslaan als een werkmap met macro’s. Klik op de tab Bestand en kies Opslaan als. I In Excel 2016 en 2013 kiest u hierna de locatie waar u het bestand wilt

opslaan. I In Excel 2007 klikt u op de ronde Office-knop linksboven. I Drukt u op de F12-toets, dan gaat meteen het venster Opslaan als open, met

de map Documenten. Het venster Opslaan als gaat open. Kies met de keuzelijst onder in het venster bij Opslaan als: de optie Excel-werkmap met macro’s. I Slaat u deze werkmap voor het eerst op, dan gaat het venster Opslaan als open

en wordt als standaardnaam Map1.xlsm voorgesteld. Typ in plaats hiervan een makkelijk te onthouden naam. I Gaat het om een bestaande werkmap waaraan u een macro hebt toegevoegd,

dan moet u die ook op deze manier opslaan. De bestaande werkmap heet bijvoorbeeld Naam.xlsx en nu deze macro’s bevat, komt er een versie van dezelfde werkmap bij die Naam.xlsm heet. U kunt de eerste versie (zonder de macro’s) verwijderen. I De extensie xlsm duidt aan dat de werkmap een macro bevat. Uw macrocode

wordt opgeslagen als onderdeel van het bestand. Zou u na het opgeven van een eigen bestandsnaam deze keuzelijst overslaan en meteen klikken op Opslaan, dan maakt Excel u erop attent dat u deze werkmap op de genoemde manier moet opslaan. Dit gebeurt ook als u aan een bestaande werkmap naderhand een macro hebt toegevoegd.

Hoofdstuk 1 Macro’s opnemen en uitvoeren

I Afbeelding 1.18 Een werkmap met macro’s moet u op een andere manier opslaan dan een gewone werkmap.

Er verschijnt een venster dat meldt: De volgende zaken kunnen niet worden opgeslagen in werkmappen zonder macro’s: VB-project. Een macro is namelijk een project in VB (Visual Basic). Verder leest u: Als u een bestand met deze functies wilt opslaan, klikt u op Nee en kiest u vervolgens een bestandstype met macro’s in de lijst Bestandstype. Klik op Ja om het bestand op te slaan als een werkmap zonder macro’s.

I Afbeelding 1.19 Sla ieder bestand met een macro op als Excel-werkmap met macro’s (*.xlsm).

Het VB-project kan dus niet worden opgeslagen in een werkmap zonder macro’s. Door de dubbele ontkenning is de boodschap van dit venster lastig te begrijpen. U wilt het bestand opslaan als een werkmap met macro’s. Klik dus op Nee, kies met de keuzelijst onder in het venster Opslaan als de optie Excel-werkmap met macro’s (*.xlsm) en klik op Opslaan. I Als u op Ja klikt, wordt de werkmap wel opgeslagen, maar zonder de macro’s,

die bent u dan voorgoed kwijt.

Het Complete Boek Excel VBA voor professionals

Opslaan met macro’s Sla een bestand met een macro altijd op als een bestand met macro’s. Kies hiervoor via Opslaan als met de keuzelijst onder in het venster de optie Excel-werkmap met macro’s (*.xlsm) en klik op Opslaan. I U kunt met het menu bij de knop Opslaan als: ook kiezen voor Excel 97-

2003-werkmap (*.xls). Uw werkmap wordt dan opgeslagen in de ‘oude’ bestandsindeling en die kan ook macro’s bevatten. Het voordeel is, dat u deze werkmap (inclusief de macro’s) kunt uitwisselen met mensen die met Excel 2003 werken. Het nadeel is dat de vernieuwingen die sinds Excel 2007 aan het programma zijn toegevoegd daarin niet werken en dat u veel minder rijen en kolommen kunt gebruiken.

Macrobeveiliging instellen Opent u een bestand dat een macro bevat, dan kan Excel een waarschuwing geven tegen virussen. U krijgt deze waarschuwing als u de macro in een bepaald bestand hebt geplaatst, dat wil zeggen: als u bij het opnemen hebt gekozen voor Deze werkmap of Nieuwe werkmap. Steeds als u een werkmap met een macro opent, kunt u deze viruswaarschuwing verwachten. I Deze waarschuwing verschijnt niet bij macro’s die in alle bestanden beschikbaar

zijn, met andere woorden: die in de Persoonlijke Macrowerkmap staan. Als u een werkmap met een macro opnieuw opent, ziet u mogelijk onder het lint een waarschuwing: Macro’s zijn uitgeschakeld. Of u probeert de macro uit te voeren en krijgt dan een melding dat deze zijn uitgeschakeld. Dit heeft te maken met de beveiliging tegen virussen. Hebt u de macro’s zelf gemaakt, dan valt er weinig te vrezen (en voor bedreigingen van buitenaf hebt u een virusscanner, neem ik aan). Klik op de knop Inhoud inschakelen; de macro’s werken dan alleen tijdens deze sessie. Als u de werkmap de volgende keer opent, verschijnt deze melding weer en moet u opnieuw toestemming geven.

I Afbeelding 1.20 Als u de werkmap en de macro vertrouwt, schakelt u voor die keer Inhoud inschakelen in

Hoofdstuk 1 Macro’s opnemen en uitvoeren

Waarom verschijnt deze waarschuwing? Elke macro wordt geschreven in VBA. Met hetzelfde VBA kunnen ook virussen worden geprogrammeerd. Wie kwaad wil, kan dus een virus in een Excel-bestand meesturen, in een macro. Vervolgens kan een nietsvermoedende gebruiker dit virus naar andere Excel-bestanden verspreiden. Daarom is Microsoft erg gebrand op de beveiliging. Zozeer, dat een Excel-bestand met een macro bij voorbaat verdacht is, tenzij het tegendeel blijkt. Standaard is Excel dan ook beveiligd tegen macrovirussen. In plaats van steeds voor iedere werkmap apart de beveiliging in te stellen, kunt u dat ook eenmalig doen voor alle werkmappen die u opent. Klik hiervoor op Bestand en op Opties. Klik op Vertrouwenscentrum, op de knop Instellingen voor het Vertrouwenscentrum en op Instellingen voor macro’s. In het venster dat opengaat, kiest u het beveiligingsniveau. I Als u het tabblad Ontwikkelaars in beeld hebt, bereikt u dit venster meteen

met een klik op Macrobeveiliging.

I Afbeelding 1.21 Via de Opties bereikt u de instellingen voor de macrobeveiliging.

Voor de mate van beveiliging hebt u vier opties. I Met Alle macro’s uitschakelen, zonder melding wordt geen enkele macro uit-

gevoerd en krijgt u hiervan bij het openen van de werkmap ook geen waarschuwing. Als u, met deze optie ingeschakeld, drukt op een zelfgekozen sneltoets, verschijnt de melding Vanwege uw beveiligingsinstellingen zijn macro’s uitgeschakeld. I De optie Alle macro’s uitschakelen, met melding is de standaardinstelling. Als

u een werkmap met een macro opent, verschijnt onder het lint de melding

Het Complete Boek Excel VBA voor professionals

Beveiligingswaarschuwing Macro’s zijn uitgeschakeld. Na een klik op Inhoud inschakelen werken de macro’s tijdens deze sessie. Als u de werkmap de volgende keer opent, moet u opnieuw toestemming geven. I Voor de derde optie Alle macro’s uitschakelen, met uitzondering van macro’s

die digitaal zijn ondertekend hebt u een digitale handtekening nodig (zie de paragraaf Digitale handtekening plaatsen). Kiest u deze optie, maar u hebt geen digitale handtekening, dan worden de macro’s niet uitgevoerd. I Met de onderste optie Alle macro’s inschakelen worden de macro’s voortaan

uitgevoerd, zonder dat er iets wordt gevraagd. Het wordt weliswaar niet aanbevolen door het programma, maar als u mogelijke virussen opvangt met een antivirusprogramma, werkt u met deze optie het soepelst.

I Afbeelding 1.22 U kiest hoe zwaar de beveiliging moet staan.

Sluiten en openen Hebt u het beveiligingsniveau veranderd, dan moet u het bestand sluiten; op de vraag of u wijzigingen in dit bestand wilt opslaan, klikt u op Ja dan wel Nee. Het programma Excel mag wel actief blijven. Pas nadat u het bestand opnieuw hebt geopend, geldt het nieuwe veiligheidsniveau. Om met een macro te kunnen werken, moet u deze uiteraard inschakelen in het bestand waarin die macro staat. Wilt u dat uw macro’s zonder meer worden uitgevoerd, kies dan de vierde optie Alle macro’s inschakelen. Zelf heb ik deze optie ingeschakeld. Bent u voorzichtiger, kies dan Alle macro’s uitschakelen, met melding. Steeds als u een werkmap met een macro opent, verschijnt dan een waarschuwing, waarin u klikt op Inhoud inschakelen; zo schakelt u per keer de macro’s in. Als u dan een bestand opent van iemand die u niet kent, controleert u in de Visual Basic Editor of de VBA-code te vertrouwen is.