Van macrorecorder door naar VBA Geo van Dijk Excel Experience Day 2014
Opbouw van de sessie 9 9 9 9 9 9 9 9
Waarom macro’s/ VBA? VBA procedures opnemen met de macrorecorder. De VBA Editor (VBE). Macro’s opnemen die van toepassing zijn op in grootte variërende bereiken. Code begrijpen: Het RANGE object. Beperkingen van de macrorecorder. Tips voor tijdens het opnemen. Hoe verder met VBA?
Macro’s of VBA? 9 Macro ‐ ‐ ‐
Een script dat (Excel) handelingen (taken) uitvoert. Kan worden aangemaakt worden zonder nadere VBA kennis met de macrorecorder van Excel. Elke macro wordt opgeslagen als procedure in een VBA module.
9 VBA (Visual Basic for Applications) ‐ ‐ ‐
De programmeertaal (afgeleid van Visual Basic). De Editor (VBE) ‐ het programma waarmee VBA procedures kunnen worden bewerkt. Het geheel van projecten, modules, procedures, functies etc. aangemaakt met de Editor gebruik makend van de programmeertaal.
Waarom je eigen macro’s/VBA? 9 Automatiseren van telkens terugkerende handelingen en taken: ‐ Hogere efficiëntie. ‐ Minder fouten. ‐ Complexe taken kunnen door iedereen worden uitgevoerd.
9 Uitbreiden en toespitsen van functionaliteit.
In Excel: de macrorecorder 9 Hulpmiddel waarmee (eenvoudige) macro’s kunnen worden aangemaakt. 9 Handelingen die worden uitgevoerd in Excel worden tijdens een opname vertaald naar opdrachtregels in een VBA procedure. 9 Voordeel: procedures kunnen zonder VBA kennis worden aangemaakt. 9 Nadeel: procedures zijn statisch, vaak niet optimaal en kunnen zonder VBA kennis moeilijk worden aangepast.
Eerst tabblad Ontwikkelaars activeren!
Een nieuwe macro opnemen Optie ‘Macro opnemen’ op tabblad ‘Ontwikkelaars’ 1 2
Een macro opnemen Macronaam (verplicht) Geen spaties toegestaan!
Sneltoets (niet verplicht) “Ctrl + x “ toetsencombinatie waarmee de macro gestart kan worden.
Opslaglocatie (verplichte keuze) Persoonlijke macrowerkmap. Deze werkmap. Eigen map.
Beschrijving (niet verplicht) Documentatie.
Macro’s opslaan – Bestanden met macro‐ ondersteuning 9 Alle VBA code wordt opgeslagen een Excel file van het type: ‐ ‐ ‐ ‐
.xlsm (standaard ‘macro‐enabled’ bestandsformaat) .xlsb (binair formaat). .xltm (template formaat). .xlam (invoegtoepassing).
9 Bedenk dat bij het bepalen van de opslaglocatie dat alleen macro’s/VBA procedures in geopende bestanden beschikbaar zijn om uit te voeren.
Macrorecorder ‐ relatief of absoluut opnemen
9 Met relatieve verwijzingen opnemen. ‐ Vastgelegd wordt het aantal rijen en kolommen waarover de celwijzer wordt verplaatst.
9 Met absolute verwijzingen opnemen. ‐ De kolomletter en rijnummer (Bijvoorbeeld “C23” of “AH1654”) worden ‘hard’ vastgelegd van de cellen of celbereiken die tijdens de opname worden benaderd en bewerkt.
9 Tijdens een opname kan worden geswitcht tussen ‘relatief’ en ‘absoluut’ opnemen. 9 Achteraf kan code niet automatisch worden omgezet van relatief naar absoluut v.v.
Macrorecorder – ‘relatief’ of ‘absoluut’ opnemen
Relatief of absoluut opnemen Keuze “Relatieve verwijzingen gebruiken” op linttab
Macrorecorder ‐ opname beëindigen
Keuze “Opname stoppen” op linttab of knop in statusbalk
De Editor starten om de opgenomen code te bekijken (Alt + F11)
Projectvenster
In deze module bevindt zich de opgenomen procedure
Codevenster
Het Project venster 9 Projecten en Modules. 9 Voor elk geopend Excel bestand een project. 9 Een project bevat modules. 9 Een module bevat VBA procedures (macro’s). 9 Open een module door dubbelklik op modulenaam.
Voorbeeld VBA procedure (opgenomen met macrorecorder en absolute verwijzingen)
Sub NAW_Gegevens_Invoegen() ' ' NAW_Gegevens_Invoegen Macro ' De macro is opgenomen als voorbeeld tijdens een Excel VBA cursus ' ' Sneltoets: CTRL+SHIFT+N ' Range("A6").Select ActiveCell.FormulaR1C1 = “Vakmedianet" Range("A7").Select ActiveCell.FormulaR1C1 = “Binnensingel 3" Range("A8").Select ActiveCell.FormulaR1C1 = “7411 PL Deventer" Range("A6:A8").Select Selection.Font.Bold = True Selection.Font.Italic = True Range("A1").Select End Sub
9 Alle programmaregels vanaf Sub t/m End Sub horen bij de procedure NAW_Gegevens_Invoegen. 9 Haakjes () na procedurenaam verplicht. 9 In groen na ‘ teken: Commentaar. 9 Blauwe tekst is een VBA sleutelwoord. 9 De zwarte regels in het voorbeeld zijn opgenomen handelingen met een geselecteerd cel(bereik). 9 .FormulaR1C1 verwijst naar de waarde in een cel.
Voorbeeld VBA procedure (opgenomen met macrorecorder en relatieve verwijzingen) Sub AdresRelatief() ' ' AdresRelatief Macro ' Deze procedure is aangemaakt met de macrorecorder met gebruik van relatieve verwijzingen. ActiveCell.FormulaR1C1 = "Vakmedianet" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = “Binnensingel 3" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = " 7411 PL Deventer" ActiveCell.Offset(-2, 0).Range("A1:A3").Select Selection.Font.Bold = True ActiveCell.Select End Sub
9
De opdracht .Offset(x,y) verplaatst de celwijzer over x rijen en y kolommen.
Het RANGE object 9 Meest gebruikte object in VBA procedures die zijn gemaakt met de macrorecorder. 9 Doet zich dan vaak voor als gevolg van methode Select van RANGE object als: ‐ Bij meervoudige selecties: SELECTION ‐ Bij enkelvoudige selecties: ACTIVECELL
9 Voorbeelden verwijzingen naar RANGE object: ‐ Range (“A1”).Select ‐ ActiveCell.FormulaR1C1 = “Binnensingel 3" ‐ Selection.Copy
Objecten bekijken met F2 9 Vanuit Editor functietoets F2 voor opstarten Object Browser. 9 Kies bibliotheek Excel voor objecten van Excel. 9 Selecteer object in kolom Klassen en zie properties en methods van het geselecteerde object bij Leden van. 9 F1 (Help) geeft extra info over property of method.
Eigenschappen van door de recorder aangemaakte code 9 Code is specifiek en dikwijls afhankelijk van lokale instellingen en opties. 9 Bestaande macro’s kunnen niet worden aangepast met nieuwe opgenomen code. Dit moet achteraf handmatig gebeuren. 9 Code is dikwijls onnodig uitgebreid en het verdient aanbeveling dat achteraf op te schonen. 9 Code is inefficiënt vergeleken met zelf geprogrammeerde code. (o.a. door onnodige ‘Selects’).
Tips voor het opnemen met de macrorecorder (1) 9 Maak vooraf een (uitgeschreven) scenario. 9 Denk goed na over wanneer tijdens de opname geschakeld moet worden naar relatief of absoluut opnemen. 9 In veel gevallen moet ‘relatief’ worden opgenomen voor het juiste resultaat. ‐ Let op: dit is NIET de standaardinstelling!
9 Zet tijdens de opname het venster met de Editor op het scherm. 9 Als er iets fout gaat tijdens de opname is opnieuw opnemen leidt vaak efficienter als opgenomen code bewerken.
Tips voor tijdens het opnemen met de macrorecorder (2) 9 Gebruik bij het navigeren in datasets met een wisselende grootte uitsluitend toetsen(combinaties) als: ‐ ‘Ctrl + *’ (Selectie van de ‘current region’ bij aaneengesloten bereiken). ‐ Alternatief: combinaties als ‘Ctrl + ↓’ en ‘Ctrl + →’. ‐ Tel in situaties waarbij sprake is van een wisselend aantal op te tellen cellen met formules als =SOM(E$2:E10).
Tips voor tijdens het opnemen met de macrorecorder (3) 9 Verdeel de gewenste functionaliteit in delen. 9 Neem voor elk deel een eigen macroprocedure op. 9 Schoon de aangemaakte code waar mogelijk op (vereist enige basiskennis van VBA). 9 Stel m.b.v. de VBA Editor een nieuwe samengestelde procedure samen die de deelprocedures sequentieel uitvoert.
Wat je niet kunt met de macrorecorder 9
9
Als je meer wilt dan je kunt ‘voordoen’ tijdens de macro‐opname bijvoorbeeld: ‐
Flexibiliteit aanbrengen voor gebruik in context van bijvoorbeeld verschillende bladen of mappen.
‐
Een bericht geven of een vraag stellen aan de gebruiker.
‐
Een beslissing nemen op basis van een criterium (IF.. THEN.. ELSE).
‐
Eenzelfde handeling op basis van een criterium meerdere keren uitvoeren (FOR….NEXT of DO LOOP lus).
‐
Foutsituaties afhandelen. (ON ERROR GOTO…)
‐
Eigen dialoogvensters (User Forms) maken.
‐
Gegevens overbrengen naar een ander programma.
‐
Eigen functies maken.
‐
Eigen invoegtoepassingen (Add‐Ins) maken.
‐
Code optimaliseren.
In dat geval: programmeren, d.w.z. handmatig VBA code maken!
Stappen om in Excel met VBA te kunnen programmeren 1. Structuur van VBA begrijpen : projecten – modules – procedures. 2. Het (Excel) objectconcept begrijpen. 3. Basis programmeervaardigheden beheersen o.a.: ‐ ‐ ‐ ‐
Omgaan met variabelen. Beslissingsstructuur (If Then Else, Select Case). Lusstructuur (o.a. For Next). VBA functies kunnen toepassen.
Hoe kan je efficiënt programmeren in Excel met VBA leren? 1. Een uitstekende kennis van Excel hebben. 2. Een goede VBA (basis)cursus volgen. 3. Verdere ervaring opdoen via ‘Trial and Error’ op basis van een concreet project.
