Excellerend
Kwartaaltip 2014-4
Excellerend Heemraadweg 21 2741 NC Waddinxveen 06 – 5115 97 46
[email protected] BTW: NL0021459225 BANK: NL72ABNA0524989982 KVK: 24389967
Automatisch handelingen uitvoeren Wanneer je periodiek in Excel dezelfde handelingen uitvoert, kan het handig zijn je eens te verdiepen in VBA. VBA staat voor Visual Basic for Applications. Het betekent dat je gaat programmeren in Excel. Haak niet meteen af, want in het begin is het vrij eenvoudig, al zijn de mogelijkheden bijna onbegrensd. Tab ontwikkelaars Om met VBA aan de slag te gaan, of anders gezegd, om macro’s te bouwen, zal eerst de tab Ontwikkelaars aangezet moeten worden. Klik hiervoor (Excel 2010) op Bestand > Opties > Lint aanpassen. Klik in het rechter scherm het vierkantje aan bij ‘Ontwikkelaars’ en klik op OK.
In het lint is nu ook de tab Ontwikkelaars zichtbaar:
Pagina 1 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-4
Op deze tab is veel mogelijk Om VBA te snappen, zul je moeten begrijpen dat alle onderdelen van een werkmap objecten zijn. Een cel is een object, een tabblad is een object, een grafiek is een object et cetera. Al die objecten hebben eigenschappen. Van een cel is de achtergrondkleur een eigenschap, het achtergrondpatroon is een eigenschap, het lettertype is een eigenschap, de celranden zijn eigenschappen. En al deze eigenschappen hebben weer waardes. De waarde van de eigenschap achtergrondkleur van het object cel A1 kan “255” zijn, wat betekent dat de cel rood gekleurd is. Maar waarde “4228885” kan ook en staat voor een groene variant. Alle objecten zijn vastgelegd in een hiërarchie in Excel, maar dat gaat veel te diep voor een kwartaaltip. Dit klinkt allemaal vrij technisch. Laten we eerst maar gewoon aan de slag gaan. Macro opnemen In de eerste groepering ‘Programmacode’ staat een knop ‘Macro opnemen’. Excel toont dan het dialoogvenster ‘Macro opnemen’:
De macro moet een naam krijgen die voldoet aan de volgende eisen: 1) Het begint met een letter of underscore: ( _ ) 2) Het mag geen spaties of leestekens (&%$#@ et cetera) bevatten 3) Het mag geen naam zijn die al in Excel is ingebouwd. Als je altijd begint met je eigen initialen en dan een onderstrepingsteken RM_ zal het goed gaan. Je kunt een sneltoets opgeven om de macro te starten. Zoals gezegd, het gaat om regelmatig terugkerende handelingen, en als je die met één druk op het toetsenbord kunt starten is dat wel zo prettig.
Pagina 2 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-4
Ook hier moet je opletten, want Excel maakt al gebruik van veel combinaties die met de Ctrltoets gestart kunnen worden. Denk aan Ctrl+B voor vetgedrukt, Ctrl+C voor kopiëren. Als je een sneltoets wilt gebruiken, houdt dan ook altijd de Shift-toets ingedrukt en kies dan een letter. We slaan de macro op in het huidige bestand en je kunt nog een omschrijving meegeven over de werking van de macro:
Klik op OK. In de groepering ‘Programmacode’ is de knop veranderd in ‘Opname stoppen’:
Wat er op dit moment gebeurt, is dat Excel al jouw handelingen uitvoert èn opslaat in VBAcode. Ik voer de volgende handelingen uit: Tekst invoeren: Dit is cel A1 Enter Tekst invoeren: Ik heb nu op enter gedrukt. Selecteer cel A1 Maak de achtergrond geel, de tekst vet en de puntgrootte op 14. Selecteer cel A3 en voer het getal 1 in Druk op enter Voer het getal 2 in Druk op enter
Pagina 3 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-4
Voer de som-formule in Stop de opname.
Het resultaat in Excel is dit:
Handelingen in VBA Maar de vraag is: “Wat maakt VBA er van?” Klik hiervoor in de groepering ‘Programmacode’ op de meest links knop ‘Visual Basic’ of op Alt+F11. Excel springt dan naar een nieuw venster:
Aan de linkerkant is het VBAproject genaamd Map1 geopend. Dit komt doordat ik mijn nieuwe bestand nog niet opgeslagen heb, anders zou hier een andere naam staan. Het bestand bestaat uit drie tabbladen (Blad1, Blad2 en Blad3), en deze worden in VBA weergegeven als MicroSoft Excel-objecten. Onder de drie tabbladen staat ‘ThisWorkbook’ wat betekent: het hele bestand. Daaronder staat modules. Klik op de + en dubbelklik daarna op ‘Module1’: Als het venster nog niet gemaximaliseerd is, dubbelklik dan op de rand met de tekst “Map1 – Module1 (Code)”. Die paar handelingen die ik uitgevoerd heb, worden door VBA omgezet in meer dan 30 regels code! Maar als we de code nader bekijken, blijkt heel veel weg te kunnen om toch hetzelfde resultaat te krijgen. De code is in het Engels, ook al heb je een Nederlandstalige versie van Excel. Dit is niet te veranderen.
Pagina 4 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-4
Bovenin de macro staat de naam: Sub RM_eerste_macro() Dan volgt groene tekst. Dit komt doordat de regel begint met een quote. Alle tekst na een quote wordt voor VBA genegeerd als code. Ook de sneltoets Ctrl+Shift+A die ik aan de code gegeven heb, wordt ter informatie groen weergegeven. Uitleg regels code Regel 1 luidt: ActiveCell.FormulaR1C1 = "Dit is cel A1" Er is op een werkblad altijd 1 actieve cel, en dat is in dit geval de ene cel die ik geselecteerd heb. In dit geval A1. Alle invoer ziet Excel als een formule, daarom staat er Formula. R1C1 is VBA-taal voor de celaanduiding. In het tabblad zie je staan A1, VBA ‘ziet’ dat als het snijpunt van rij 1 en kolom 1. Dan volgt het =-teken en tussen dubbele quotes de door mij ingevoerde tekst. Regel 2 luidt: Range("A2").Select
Pagina 5 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-4
Nadat ik op enter heb gedrukt, verandert mijn geselecteerde bereik naar cel A2. En in deze geactiveerde cel voer ik ook weer tekst in: ActiveCell.FormulaR1C1 = "Ik heb nu op enter gedrukt." Op regel 4 staat dat ik cel A1 geselecteerd heb: Range("A1").Select Er staat niet bij hoe ik dat gedaan heb. Ik kan op de cel geklikt hebben, maar ik kan ook na mijn tweede handeling in cel A2 op Shift+Enter gedrukt hebben. Dat wordt niet bijgehouden. Daarna volgt een verzameling regels die bij elkaar horen. Deze beginnen met ‘With’, en eindigen met ‘End With’: With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With Op de eerste regel valt te lezen dat het object Interior een aantal eigenschappen heeft (paars weergegeven) en deze eigenschappen krijgen waardes mee (rood weergegeven). Maar omdat de enige handeling die ik uitgevoerd had de kleur betrof, kan ik de overige eigenschappen weghalen: With Selection.Interior.Color = 65535 End With Daarna wordt de tekst vet weergegeven via: Selection.Font.Bold = True De tweede reeks regels die bij elkaar horen betreffen het lettertype: With Selection.Font .Name = "Calibri" .Size = 14 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontMinor End With Waar komen al die eigenschappen vandaan? Als je in Excel op Ctrl+1 klikt, of op de tab Start in de groepering Lettertype klikt op het kleine icoontje rechts onderin , verschijnt het volgende dialoogvenster, en dat maakt veel duidelijk:
Pagina 6 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-4
Een deel van de paars gekleurde eigenschappen in de VBA code komen in dit venster terug. De waarden zie je terug in de code, maar als een vierkantje niet aangevinkt staat (zoals bij Doorhalen, Superscript en Subscript), is de waarde in VA: “False”. Ook hiervan kunnen we dus regels verwijderen omdat ik slechts één ding aangepast had: De tekst 14 punten groot: Selection.Font.Size = 14 Vervolgens heb ik de getallen 1 en 2 ingevoerd in de cellen A3 en A4: Range("A3").Select ActiveCell.FormulaR1C1 = "1" Range("A4").Select ActiveCell.FormulaR1C1 = "2" Tekst werd gezien als een formula, en dat geldt ook voor getallen zoals hierboven zichtbaar wordt. Tot slot heb ik in cel A5 een echte formule ingevoerd: Range("A5").Select ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)" In Excel (Nederlandse versie) zie ik staan: =SOM(A3:A4)
Pagina 7 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-4
Dit wordt in VBA vertaald naar SUM (Het engels voor som) en dan volgt weer de celaanduiding met rijen en kolommen, maar dan relatief ten opzichte van de actieve cel: "(R[-2]C" betekent twee rijen omhoog in dezelfde kolom; ":R[-1]C" betekent tot en met één rij omhoog in dezelfde kolom. R[1]C[2] zou betekenen: één rij naar beneden en twee kolommen naar rechts. Range("A1") kan ingekort worden naar [A1] De code valt nog meer in te dikken want heel vaak valt de actieve cel gelijk met de selectie. En het selecteren van een cel is in VBA niet nodig om de eigenschappen ervan aan te passen. Dat is een groot voordeel ten opzichte van handmatige handelingen, want wij moeten wel een cel selecteren voordat we er iets aan kunnen veranderen! Tevens gaat het aanpassen van een cel zonder het te selecteren sneller dan de cel eerst te selecteren en dan de waardes aan te passen. De code eindigt altijd met End Sub De code, die de geselecteerde cel ongemoeid laat omdat er niets geselecteerd wordt, kan uiteindelijk worden: Sub RM_eerste_macro() ' ' RM_eerste_macro Macro ' Deze macro laat zien wat VBA opslaat als ik een paar handelingen uitvoer. ' ' Sneltoets: Ctrl+Shift+A ' [A1].Formula = "Dit is cel A1" [A2].Formula = "Ik heb nu op enter gedrukt." [A1].Interior.Color = 65535 [A1].Font.Bold = True [A1].Font.Size = 14 [A3].Formula = "1" [A4].Formula = "2" [A5].Formula = "=SUM(R[-2]C:R[-1]C)" End Sub Dit is een stuk overzichtelijker en eenvoudiger aan te passen. De macro is alleen in dit Excelbestand op ieder tabblad te starten via Ctrl+Shift+A. Bestand opslaan Wanneer je een Excelbestand opslaat, krijgt het standaard de extensie xlsx mee. Maar deze extensie ondersteunt geen macro’s! Excel geeft daarom bij het opslaan een waarschuwing dat macro’s zouden verdwijnen.
Dit is niet ongedaan te maken! Weg is weg. Pagina 8 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-4
Sla het bestand op als xlsm:
Verwijderen macro of module Bij het opnemen van een macro, wordt een module aangemaakt waarin de code komt te staan. Een bestand kan meerdere modules bevatten, en een module kan meerdere macro’s bevatten.
Een macro kun je uitvegen, maar je kunt ook een module verwijderen via de rechter muisknop > Module# verwijderen. Er volgt nog wel een venster ter bevestiging, waarbij de code ook geëxporteerd kan worden naar een bestand los van Excel:
Pagina 9 van 10
www.excellerend.nl
Excellerend
Kwartaaltip 2014-4
Het is via een loop of en case – when constructie ook mogelijk data aan te passen tot aan verschillende voorwaarden is voldaan. Je kunt formulieren maken die de eindgebruiker kan invullen waarbij de data teruggeschreven wordt in een tabel. Dit is héééél kort een beginnetje over VBA. Enthousiast en nieuwsgierig geworden? Schaf dan een boek aan van John Walkenbach Excel 2013 Power Programming with VBA, of begin eenvoudig met John Walkenbach’s Excel VBA Programming For Dummies. Ook zijn er op youtube veel filmpjes te vinden over wat er mogelijk is met VBA. Einde kwartaaltip 4-2014
De kwartaaltip(s) zijn als PDF te downloaden via: www.excellerend.nl\kwartaaltips.html Wilt u een op maat gemaakte cursus Excel voor uw organisatie regelen? Zoekt u ondersteuning in het bouwen van rekenmodellen, controles, of denkt u dat iets mogelijk is in Excel maar u weet niet hoe? Neem dan contact op met Richard Meijles: 06 – 5115 97 46 of via e-mail:
[email protected]. Wilt u geen kwartaaltips meer ontvangen? Klik dan op:
[email protected] Hebt u een verzoek voor een kwartaaltip? Klik dan op:
[email protected] en vermeld tevens uw Excelversie. Richard Meijles
Pagina 10 van 10
www.excellerend.nl
