Open Excel en de VBE (Visual Basic Editor). Tenzij het is gewijzigd, bevat het VBE-venster de Projectverkenner raam en de Eigendommen venster (deze zijn toegankelijk via de Weergave menu).
Projectverkenner: Werkt als een bestandsbeheerder. Helpt u bij het navigeren door de code in uw werkmap.
Eigenschappenvenster: Toont de eigenschappen van het huidige actieve object (bijv. Blad1) van de huidige werkmap (bijv.Boek1).
In dit artikel zullen we leren hoe eenvoudig macro-opname in Excel is.
Oefening 1: Een macro opnemen.
Deze oefening laat zien wat er gebeurt als een macro wordt opgenomen en demonstreert het verschil tussen het opnemen van absolute en relatieve referenties.
1. Selecteer cel op een leeg werkblad in een nieuwe werkmap C10
2. Start de Macrorecorder met optie om macro op te slaan in Dit werkboek. Op dit punt maakt de VBE een nieuwe Modules map. Het is vrij veilig om ernaar te gaan kijken - uw acties worden niet geregistreerd. Klik op de [+] naast de map en zie dat de VBE een module in de map heeft geplaatst en deze een naam heeft gegeven Module 1. Dubbelklik op het modulepictogram om het codevenster te openen. Schakel terug naar Excel.
3. Zorg ervoor dat de Relatieve referentie: knop op de Stop met opnemen werkbalk is NIET ingedrukt.
4. Selecteer cel B5 en stop de recorder.
5. Schakel over naar de VBE en bekijk de code:
Bereik ("B5"). Selecteer
6. Neem nu nog een macro op, precies op dezelfde manier, maar deze keer met de Relatieve referentie: knop ingedrukt.
7. Schakel over naar de VBE en bekijk de code:
ActiveCell.Offset(-5, -1).Bereik("A1").Selecteer
8. Neem nu nog een macro op, maar in plaats van cel B5 te selecteren, selecteer je een blok cellen 3x3 beginnend bij B5 (selecteer cellen B5:F7)
9. Schakel over naar de VBE en bekijk de code:
ActiveCell.Offset(-5, -1).Bereik("A1:B3").Selecteer
10. Speel de macro's af, nadat u eerst een andere cel dan C10 hebt geselecteerd (voor Macro2 en Macro3 moet de startcel in rij 6 of lager staan - zie stap 11 hieronder)
Macro1 - verplaatst de selectie altijd naar B5
Macro2 - verplaatst de selectie naar een cel 5 rijen omhoog en 1 kolom links van de geselecteerde cel.
Macro3 - selecteert altijd een blok van zes cellen beginnend 5 rijen omhoog en 1 kolom links van de geselecteerde cel.
11. Voer Macro2 uit, maar forceer een fout door een cel in rij 5 of hoger te selecteren. De macro probeert een niet-bestaande cel te selecteren omdat de code hem vertelt een cel 5 rijen boven het startpunt te selecteren, en dat is boven aan het blad. druk op Debuggen om naar het deel van de macro te gaan dat het probleem veroorzaakte.
OPMERKING: Wanneer de VBE zich in de foutopsporingsmodus bevindt, wordt de coderegel die het probleem veroorzaakte geel gemarkeerd. U moet de macro "resetten" voordat u verder kunt gaan. Klik op de Resetten knop op de VBE-werkbalk of ga naar Uitvoeren > Reset. De gele markering verdwijnt en de VBE komt uit de foutopsporingsmodus.
12. Het is belangrijk om op deze manier op gebruikersfouten te anticiperen. De eenvoudigste manier is om de code aan te passen om fouten eenvoudigweg te negeren en door te gaan naar de volgende taak. Doe dit door de regel toe te voegen…
Bij fout Hervatten Volgende
… direct boven de eerste regel van de macro (onder de regel Sub Macro1()
13. Uitvoeren Macro2 zoals voorheen, te hoog op het blad beginnen. Deze keer vertelt de regel die u hebt getypt Excel om de regel code te negeren die niet kan worden uitgevoerd. Er is geen foutmelding en de macro wordt afgesloten nadat hij alles heeft gedaan wat hij kan. Gebruik deze methode om fouten met de nodige voorzichtigheid te behandelen. Dit is een heel eenvoudige macro. Een complexere macro zou waarschijnlijk niet presteren zoals verwacht als fouten gewoon genegeerd zouden worden. Ook heeft de gebruiker geen idee dat er iets mis is gegaan.
14. Wijzig de code van Macro2 om een meer geavanceerde fout-handler op te nemen, dus:
Submacro2()
Bij fout Ga naar ErrorHandler
ActiveCell.Offset(-5, -1).Bereik("A1").Selecteer
Sluit sub
ErrorHandler:
MsgBox "Je moet onder rij 5 beginnen"
Einde sub
15. Deze keer krijgt de gebruiker een dialoogvenster te zien wanneer er iets misgaat. Als er geen fout is, zorgt de regel Exit Sub ervoor dat de macro wordt beëindigd nadat deze zijn werk heeft gedaan - anders zou de gebruiker het bericht zien, zelfs als er geen fout was.
Opgenomen macro's verbeteren
De goede manier om de basis van VBA te leren, is door een macro op te nemen en te zien hoe Excel zijn eigen code schrijft. Vaak bevatten opgenomen macro's echter veel meer code dan nodig is. De volgende oefeningen laten zien hoe u code die door een opgenomen macro is geproduceerd, kunt verbeteren en stroomlijnen.
Oefening 2: Verbeteren van opgenomen macro's
Deze oefening laat zien dat wanneer macro's worden opgenomen, er vaak meer code wordt gegenereerd dan nodig is. Het demonstreert het gebruik van de With-instructie om de code te specificeren.
1. Selecteer een cel of celblok.
2. Start de macrorecorder en roep de macro FormatCells op. De instelling Relatieve verwijzingen is niet relevant.
3. Ga naar Opmaak > Cellen > Lettertype en kies Times New Roman en rood.
Ga naar Patronen en kies Geel.
Ga naar Uitlijning en kies Horizontaal, Midden
Ga naar Nummer en kies Munteenheid.
4. Klik op Oke en stop de recorder.
5. Klik op de ongedaan maken knop (of Ctrl+Z) om uw wijzigingen in het werkblad ongedaan te maken.
6. Selecteer een blok cellen en voer de FormaatCellen macro. Let op: dit kan niet ongedaan worden gemaakt! Typ de cellen in om het resultaat van de opmaak te controleren.
7. Kijk naar de code:
Subformaatselectie()
Selection.NumberFormat = "$#,##0.00"
Met selectie
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Oriëntatie = 0
.ShrinkToFit = False
.MergeCells = False
Eindigt met
Met Selectie.Lettertype
.Name = "Times New Roman"
.FontStyle = "Normaal"
Maat = 10
.Doorstrepen = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Schaduw = Onwaar
.Onderstrepen = xlOnderstrepenStyleGeen
.ColorIndex = 3
Eindigt met
Met Selectie.Interieur
.ColorIndex = 6
.Patroon = xlSolid
.PatternColorIndex = xlAutomatisch
Eindigt met
Einde sub
Verander het lettertype in Times New Roman
Wijzig de letterkleur in rood
Wijzig de vulkleur in Geel
Klik op de Centrum knop
Klik op de Munteenheid knop
13. Bekijk de code. Je krijgt nog steeds veel dingen die je niet per se wilt. Excel registreert alle standaard instellingen. De meeste hiervan kunnen veilig worden verwijderd.
14. Experimenteer met het rechtstreeks bewerken in de code om kleuren, het lettertype, het getalformaat enz. te wijzigen.
Oefening 3: Kijk hoe een macro wordt opgenomen
Deze oefening laat zien dat je kunt leren door te kijken naar de opbouw van de macro terwijl deze wordt opgenomen. Het is ook een voorbeeld van wanneer de With-instructie soms niet geschikt is.
1. Open het bestand VBA01.xls.
Hoewel dit werkblad visueel in orde is en door de gebruiker kan worden begrepen, kan de aanwezigheid van lege cellen problemen veroorzaken. Probeer de gegevens te filteren en kijk wat er gebeurt. Ga naar Gegevens > Filter > Autofilter en filter op Regio of Maand. Het is duidelijk dat Excel niet dezelfde aannames doet als de gebruiker. De lege cellen moeten worden gevuld.
2. Tegel de Excel- en VBE-vensters (verticaal) zodat ze naast elkaar staan.
3. Selecteer een willekeurige cel in de gegevens. Als het een lege cel is, moet deze grenzen aan een cel met gegevens.
4. Start de macrorecorder en roep de macro op Vullen Lege Cellen. Instellen om op te nemen Relatieve referenties.
5. Zoek en dubbelklik in het VBE-venster op de module (Module1) voor de huidige werkmap om het bewerkingsvenster te openen, schakel vervolgens het venster Projectverkenner en het venster Eigenschappen uit (alleen om ruimte vrij te maken).
6. Neem de nieuwe macro als volgt op:
Stap 1. Ctrl+* (om de huidige regio te selecteren)
Stap 2. Bewerken > Ga naar > Speciaal > Spaties > OK (om alle lege cellen in de huidige regio te selecteren)
Stap 3. Typ =[pijl omhoog] druk dan op Ctrl+Enter (om uw typen in alle geselecteerde cellen te plaatsen)
Stap 4. Ctrl+* (om de huidige regio opnieuw te selecteren)
Stap 5. Ctrl+C (om de selectie te kopiëren - elke methode is voldoende)
Stap 6. Bewerken > Plakken speciaal > Waarden > OK (om de gegevens weer op dezelfde plaats te plakken, maar de formules weg te gooien)
Stap 7. Esc (om uit de kopieermodus te komen)
Stap 8. Stop de opname.
7. Kijk naar de code:
Sub Fill LegeCells()
Selectie.Huidige Regio.Selecteren
Selectie.SpecialCells(xlCellTypeBlanks).Selecteren
Selectie.FormuleR1C1 = "=R[-1]C"
Selectie.Huidige Regio.Selecteren
Selectie.Kopiëren
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, transponeren:= False
Application.CutCopyMode = False
Einde sub
8. Let op het gebruik van de spatie-en-underscore “ _” om het splitsen van een enkele regel code op een nieuwe regel aan te duiden. Zonder dit zou Excel de code als twee afzonderlijke instructies behandelen.
9. Doordat deze macro is opgenomen met goed doordachte commando's, is er weinig onnodige code. In de Plakken speciaal alles na het woord "xlValues" kan worden verwijderd.
10. Probeer de macro uit. Gebruik vervolgens de tool AutoFilter en merk het verschil op.