Werken met Excel VBA-macro's
Macro's in Excel worden opgeslagen als VBA-code en soms wilt u deze code rechtstreeks bewerken. In deze zelfstudie wordt beschreven hoe u macro's kunt bekijken en bewerken, enkele technieken voor het opsporen van fouten in macro's en enkele algemene bewerkingsvoorbeelden.
Macro's bekijken
Een lijst met macro's kan worden weergegeven in het dialoogvenster Macro's. Om dit dialoogvenster te bekijken, selecteert u het tabblad Ontwikkelaars op het lint en klikt u op de knop Macro's.
Als er meerdere werkmappen zijn geopend, worden macro's van alle werkmappen in de lijst weergegeven. Macro's in de actieve werkmap worden alleen op naam weergegeven, terwijl macro's in andere werkmappen worden voorafgegaan door de naam van de werkmap en een uitroepteken (d.w.z. "Book2!OtherMacro").
Een macro openen om te bewerken
U kunt het dialoogvenster Macro gebruiken om de code voor een macro te openen door de naam van de macro te selecteren en op de knop Bewerken te klikken. Dit opent de macro in de VB Editor.
U kunt de VB-editor ook rechtstreeks openen door op de Visual Basic-knop op het tabblad Ontwikkelaars te klikken of door op de sneltoets ALT+F11 te drukken.
Met deze methode moet u naar de gewenste macro navigeren (ook wel 'procedure' genoemd). We zullen de lay-out van de VBA-editor doornemen:
Overzicht van de VB-editor
De VB Editor heeft verschillende vensters; in deze tutorial behandelen we het projectvenster, het eigenschappenvenster en het codevenster.
Projectvenster
Het projectvenster toont elk Excel-bestand als een eigen project, met alle objecten in dat project gecategoriseerd op type. Opgenomen macro's verschijnen in de categorie "Modules", gebruikelijk in het object “Module1”. (Als uw project meerdere modules heeft en u niet zeker weet waar uw macro is opgeslagen, opent u deze gewoon vanuit het eerder genoemde dialoogvenster Macro's.)
Eigenschappenvenster
Het Eigenschappenvenster toont de eigenschappen en bijbehorende waarden van een object - als u bijvoorbeeld op een werkbladobject in het Projectvenster klikt, wordt een lijst met eigenschappen voor het werkblad weergegeven. Eigenschapsnamen staan aan de linkerkant en eigenschapswaarden aan de rechterkant.
Als u een module in het projectvenster selecteert, wordt weergegeven dat deze slechts één eigenschap heeft, "(Naam)". U kunt de naam van een module wijzigen door te dubbelklikken op de waarde van de eigenschap, een nieuwe naam te typen en op Enter te drukken. Als u de naam van een module wijzigt, wordt deze hernoemd in het projectvenster, wat handig is als u veel modules heeft.
Codevensters
Codevensters zijn speciale teksteditors waarin u de VBA-code van uw macro kunt bewerken. Als u de code voor een macro in Module1 wilt zien, dubbelklikt u op 'Module1' in het projectvenster.
Macro's uitvoeren in de VB Editor
Macro's kunnen rechtstreeks vanuit de VB-editor worden uitgevoerd, wat handig is voor testen en debuggen.
Een macro uitvoeren
- Dubbelklik in het projectvenster op de module die de macro bevat die u wilt testen (om het codevenster te openen)
- Plaats in het codevenster de cursor ergens op de macrocode tussen "Sub" en "End Sub"
- Klik op de Loop knop op de werkbalk of druk op sneltoets F5
Een macro "doorlopen"
In plaats van de macro in één keer uit te voeren, kunt u de macro regel voor regel uitvoeren, met behulp van een sneltoets om door de code te "stappen". De macro pauzeert op elke regel, zodat u ervoor kunt zorgen dat elke regel code doet wat u in Excel verwacht. U kunt met deze methode ook op elk moment voorkomen dat een macro doorgaat.
Een macro "doorlopen":
- Dubbelklik in het projectvenster op de module die de macro bevat die u wilt testen (om het codevenster te openen)
- Plaats de cursor in het codevenster ergens op de macrocode
- Druk op de sneltoets F8 om het "stap-through"-proces te starten
- Druk herhaaldelijk op F8 om de uitvoering van de code te versnellen, aangegeven door de gele markering in het codevenster
- Om te voorkomen dat een macro doorgaat, drukt u op de Resetten knop
Waarom VBA-macro's bewerken?
De macrorecorder is - hoewel effectief - ook zeer beperkt. In sommige gevallen produceert het trage macro's, registreert het acties die u niet van plan was te herhalen, of registreert het dingen waarvan u niet dacht dat u ze deed. Door te leren uw macro's te bewerken, kunnen ze sneller, efficiënter en voorspelbaarder werken.
Naast het oplossen van deze problemen, verkrijgt u ook een enorme productiviteitsstijging wanneer u de volledige kracht van macro's benut. Macro's hoeven niet alleen opnames van taken te zijn - macro's kunnen logica bevatten, zodat ze taken alleen onder bepaalde voorwaarden uitvoeren. In slechts een paar minuten codeer je loops die een taak honderden of duizenden keren in één keer herhalen!
Hieronder vindt u enkele handige tips om uw macrocode te optimaliseren, evenals hulpmiddelen om uw macro's harder en slimmer te laten werken.
Algemene voorbeelden van macrobewerkingen
Macro's versnellen
Als u een macro heeft die lang duurt voordat deze wordt uitgevoerd, kunnen er een aantal redenen zijn waarom deze langzaam werkt.
Ten eerste: wanneer een macro wordt uitgevoerd, toont Excel alles zoals het in realtime gebeurt - terwijl het misschien kijk snel voor jou, eigenlijklaten zien het werk is een belangrijke prestatiehit. Een manier om Excel aanzienlijk sneller te laten werken, is door het te vertellen: stop met het updaten van het scherm:
' Scherm bijwerken Application.ScreenUpdating uitschakelen = False ' Scherm bijwerken Application inschakelen.ScreenUpdating = True
De regel "Application.ScreenUpdating = False" betekent dat u de macro niet ziet werken, maar dat deze veel sneller zal werken. Houd er rekening mee dat u ScreenUpdating altijd op True moet zetten aan het einde van uw macro, anders werkt Excel mogelijk niet zoals u later verwacht!
Een andere manier om macro's te versnellen:automatische berekening uitschakelen in de macro. Als je met complexe spreadsheets hebt gewerkt, weet je dat kleine wijzigingen duizenden berekeningen kunnen veroorzaken die tijd kosten om te voltooien. Daarom schakelen veel mensen automatische berekening uit in de opties van Excel. Je kunt dit ook schakelen met VBA-code, zodat je macro nog steeds snel werkt op andere computers. Dit helpt in gevallen waarin u veel formulecellen kopieert of veel berekeningen activeert terwijl u gegevens in een bereik plakt:
' Schakel toepassing voor automatische berekening uit. Berekening = xlCalculationManual ' Schakel toepassing voor automatische berekening in. Berekening = xlCalculationAutomatic
Loops en logica toevoegen (If-statements)
De macrorecorder slaat al uw acties op als code in een taal die VBA wordt genoemd. VBA is meer dan alleen een manier om acties in Excel vast te leggen - het is een programmeertaal, wat betekent dat het code kan bevatten om beslissingen te nemen over welke acties moeten worden uitgevoerd, of om acties te herhalen totdat aan een voorwaarde is voldaan.
Looping
Stel dat je een macro wilde maken die een rapport opstelde, en als onderdeel van die macro moest je negentien bladen aan de werkmap toevoegen, in totaal twintig. Je zou jezelf kunnen opnemen door steeds opnieuw op de (+) knop te klikken, of je zou een lus kunnen schrijven die de actie voor je herhaalt, zoals deze:
Sub ReportPrep() Dim i As Long For i = 1 To 19 Sheets.Add Next i End Sub
In dit voorbeeld gebruiken we a For loop, wat een soort lus is die door een reeks items loopt. Hier is ons bereik de nummers 1 tot en met 19, met behulp van een variabele met de naam 'i', zodat de lus het kan bijhouden. Binnen onze lus wordt er maar één actie herhaald tussen de voor enDe volgende regels (het toevoegen van het blad), maar u kunt binnen de lus zoveel code toevoegen als u wilt, zoals het blad opmaken of gegevens naar elk blad kopiëren en plakken - wat u maar wilt herhalen.
Als verklaringen
Een Als verklaring wordt gebruikt om te beslissen of een code wordt uitgevoerd of niet, met behulp van een logische test om de beslissing te nemen. Hier is een eenvoudig voorbeeld:
Sub ClearIfSmall() If Selection.Value < 100 Then Selection.Clear End If End Sub
Dit eenvoudige voorbeeld laat zien hoe de If-instructie werkt - u test een voorwaarde die True of False is (is de waarde van de geselecteerde cel kleiner dan 100?), en als de test True retourneert, wordt de code binnenin uitgevoerd.
Een tekortkoming van deze code is dat deze slechts één cel tegelijk test (en zou mislukken als u meerdere cellen zou selecteren). Dit zou handiger zijn als u… door elke geselecteerde cel kon lopen en elke cel kon testen…
Sub ClearIfSmall() Dim c As Range For Each c In Selection.Cells If c.Value < 100 then c.Clear End If Next c End Sub
In dit voorbeeld is er een iets andere For-lus - deze loopt niet door een reeks getallen, maar doorloopt in plaats daarvan alle cellen in de selectie, waarbij een variabele met de naam 'c' wordt gebruikt om bij te houden. Binnen de lus wordt de waarde van 'c' gebruikt om te bepalen of de cel moet worden gewist of niet.
Loops en If-instructies kunnen op elke gewenste manier worden gecombineerd - u kunt lussen in lussen plaatsen, of een If in een andere, of een If gebruiken om te beslissen of een lus überhaupt moet worden uitgevoerd.
<<>>
Scroleffecten verwijderen
Een veelvoorkomende reden om macrocode te bewerken is om scrollen op het scherm te verwijderen. Wanneer u een macro opneemt, moet u mogelijk andere delen van een werkblad bereiken door te schuiven, maar macro's hoeven niet te schuiven om toegang te krijgen tot gegevens.
Scrollen kan je code overladen met honderden of zelfs duizenden regels onnodige code. Hier is een voorbeeld van de code die wordt opgenomen wanneer u op de schuifbalk klikt en sleept:
Dit soort code is volledig overbodig en kan worden verwijderd zonder enige andere functionaliteit aan te tasten. Zelfs als je het scrollen wilt behouden, kan deze code nog steeds worden gecondenseerd tot een lus.
Verwijder overtollige code
Opgenomen macro's hebben de neiging om veel overbodige code toe te voegen die niet noodzakelijkerwijs overeenkomt met wat u wilt dat de macro doet. Neem bijvoorbeeld de volgende opgenomen code, die het wijzigen van een lettertypenaam in een cel registreert:
Hoewel alleen de naam van het lettertype werd gewijzigd, werden elf (11) wijzigingen in het lettertype geregistreerd, zoals de lettergrootte, de teksteffecten, enz. Als het de bedoeling van de macro was om alleen de naam van het lettertype te wijzigen (terwijl alle andere eigenschappen met rust gelaten worden), opgenomen macro zou niet werken!
Het is mogelijk om deze macro te wijzigen zodat alleen de naam van het lettertype wordt gewijzigd:
Niet alleen zal deze macro nu werken zoals bedoeld, maar hij is ook veel gemakkelijker te lezen.
Cursorbewegingen verwijderen
Een ander ding dat in macro's wordt vastgelegd, zijn werkblad- en celselecties. Dit is een probleem omdat een gebruiker gemakkelijk uit het oog kan verliezen waar hij net aan werkte als de cursor naar een andere positie beweegt nadat een macro is uitgevoerd.
Net als bij scrollen, jij het kan nodig zijn om de cursor te verplaatsen en verschillende cellen te selecteren om een taak uit te voeren, maar macro's hoeven de cursor niet te gebruiken om toegang te krijgen tot gegevens. Beschouw de volgende code, die een bereik kopieert en vervolgens in drie andere bladen plakt:
Er zijn een paar problemen met deze code:
- De gebruiker verliest zijn vorige plaats in de werkmap
- De macro geeft niet aan welk blad we kopiërenvan - dit kan een probleem zijn als de macro op het verkeerde blad is uitgevoerd
Bovendien is de code moeilijk te lezen en verspillend. Deze problemen kunnen eenvoudig genoeg worden opgelost:
In deze code is duidelijk te zien dat we kopiëren van Blad1, en noch het actieve werkblad, noch het geselecteerde bereik hoeft te worden gewijzigd om de gegevens te plakken. (Een belangrijke verandering is het gebruik van "PasteSpecial" in plaats van "Plakken" - Range-objecten, zoals "Range("C4″)", hebben alleen toegang tot de opdracht PasteSpecial.)
Wanneer code vol raakt met verwijzingen naar ".Select" en "Selection", is het een aanwijzing dat er ruimte is om die code te optimaliseren en efficiënter te maken.
