Vorige week hebben we gezien dat het handig is om bij het starten van Excel standaardinstellingen in te laden (zie Standaardinstellingen Excel).
Ik heb toen ook beloofd om de gebruikte VBA-routine toe te lichten.
Deze keer zal ik dat dan ook doen, maar wil allereerst voor diegene die nog nooit of nauwelijks met VBA hebben gewerkt, een korte, algemene toelichting op VBA geven. Het is daardoor ook een wat uitgebreider verhaal geworden; en zeker ook voor de beginnende VBA-programmeur niet al te makkelijk!
VBA (Visual Basic for Applications)
Kortweg zou je kunnen zeggen, dat dit een programmeertaal is waarmee je handelingen, die je in Excel wilt uitvoeren, kunt automatiseren.
Wanneer je bijvoorbeeld vaak je naam en adres-gegevens in Excel moet invoeren, dan kun je dat met VBA simpel zodanig automatiseren, dat dit met ‘één druk op de knop’ wordt uitgevoerd.
Of als je regelmatig een bestand krijgt, waarin na iedere regel met gegevens een lege rij voorkomt, die eruit moet: automatiseren via VBA!
Dit soort handelingen kun je als Macro opnemen en daarna opnieuw ‘afspelen’. De macro kun je in de VBA-editor bekijken (en daar kun je veel van leren hoe het programmeren in zijn werk gaat!) en eventueel aanpassen. Om dit verder uit te leggen gaat echter te ver voor dit blog.
Het automatiseren van handelingen (macro’s opnemen en uitvoeren) is niet het enige dat met VBA kan: je kunt ook zelf macro’s schrijven (door programmeurs sub-routines genoemd) of eigen functies bedenken, wanneer de standaard-functies van Excel niet voldoende voor je zijn.
VBA-editor
Hieronder gaan we zelf aan de slag met programmeren in VBA. Programmeren is niets anders dan het in de goede volgorde zetten van opdrachten die uitgevoerd moeten worden (klinkt simpel, toch?).
Voor die opdrachten gebruik je een soort tekstverwerker, de zogenaamde VBA-editor. Waar vinden we de editor, is dan de volgende vraag.
Net als met zoveel zaken in Excel, kun je die op verschillende manieren vinden; vaak nog afhankelijk van de versie die je gebruikt. In de 2010-versie en hoger is deze ‘verstopt’ op het Ontwikkelaars-tabblad, dat standaard niet in de menu-balk zit.
Gelukkig werkt rechtsklikken op een tabblad onderin (en dan Programmacode weergeven kiezen) nog steeds, maar nog makkelijker: er is ook een toets-combinatie: Alt-F11 (dat doet me er aan denken om binnenkort eens een blog aan sneltoetsen te wijden!)
Dus ga naar Excel (als er geen werkmap geopend is, maak dan een nieuwe aan; onthoud even de naam van de geopende werkmap), hou de Alt-toets vast en druk op F11: en daar is de VBA-editor!
In het linker gedeelte van het scherm zie je een
overzicht van alle openstaande ‘projecten’. De editor noemt iedere werkmap, die in gebruik is, ‘project’. Maar daarnaast zijn add-ins ook projecten.
Klik op het project met de naam
van de werkmap, die je net hebt onthouden (anders even terug naar Excel (Alt-F11), kijken hoe de werkmap heet en weer naar de VBA-editor (Alt-F11)).
Macro’s/subroutines/programma’s/functies worden binnen een project weggeschreven in een Module. Standaard is die er nog niet, dus: kies boven in het menu Invoegen en dan Module.
Programma-1
Aan de slag: we gaan eerst een simpel programmaatje schrijven:
- maak een nieuw werkblad in Excel aan; onthoud de naam
- ga naar de VBA-editor mbv Alt-F11
- zoek het project met de naam uit stap 1 en klik daarop
- maak een Module aan: Invoegen/Module
- in je project zie je een nieuwe module met de naam Module1; dubbelklik daarop; we krijgen dan een leeg ‘vel’ in onze ’tekstverwerker’
- nu gaan we programmeren!
Tik in: Sub ToonBericht()
en druk op Enter - als alles goed is gegaan heeft de editor een nieuwe regel toegevoegd: End Sub
- VBA kent nu een programma (ofwel subroutine, vandaar het woordje Sub) met de naam ToonBericht; hij weet waar dit begint (bij Sub) en waar deze routine eindigt (bij End Sub). Zo kunnen er diverse programma’s in één module komen.
- het programma doet nog niets. Tik daarom op de lege regel (tussen Sub en End) het volgende: MsgBox “Een bericht vanuit VBA!”, vbInformation, “Informatie-bericht” (MsgBox is het VBA-commando om een bericht weer te geven; let goed op wat er allemaal op je scherm verschijnt als je iets intikt; het eerste gedeelte achter MsgBox is de boodschap die je weer wilt laten geven; dan komt er een ‘code’ waardoor VBA weet, wat er rond het bericht moet komen (vbInformation is de vb-code voor een Information-boodschap); als derde komt de titel van het bericht)
- nu het programma klaar is gaan we het oproepen vanuit Excel:
ga van VBA naar Excel: Alt-F11.
Nu moeten we de macro aan het werk zetten; ga naar het macro-overzicht (via Alt-F8)
klik op de macro met de naam ToonBericht en kies Uitvoeren - Gelukt?
Programma-2
De techniek uit het eerste programma gebruiken we nu ook voor het ophalen van de directory waarde templates met standaardinstellingen moeten staan:
- ga opnieuw naar de VBA-editor mbv Alt-F11
- dubbelklik zonodig op Module1
- Tik onder het vorige programma:
Sub ToonDir()
MsgBox Application.StartupPath, vbInformation, “Gevonden!”
End Sub - de MsgBox zal het opstartpad van de aanroepende applicatie (Excel dus) tonen
- ga van VBA naar Excel: Alt-F11 en voer de macro ToonDir uit(via Alt-F8)
Programma-3
Omdat het resultaat van het vorige programma moeilijk te kopiëren is, heb ik vorige keer een routine gebruikt die het gewenste resultaat in een Excel-cel wegschrijft.
Hieronder staat het programma weergegeven:
De groene teksten zijn geen opdrachten binnen VBA, maar is commentaar/uitleg die de programmeur toevoegt, zodat de routine beter door anderen (en later door de programmeur zelf!) te begrijpen is.
Alles wat achter een apostrof-teken (dus ‘) staat, wordt door VBA als commentaar gezien.
Hoewel het bovenstaande in eerste instantie misschien ingewikkeld overkomt, valt het (na wat oefening) heel erg mee. Door zelf macro’s op te nemen en binnen VBA de commando’s te bestuderen, ontstaat er snel inzicht in de structuur van het programmeren en de te gebruiken commando’s.
In het Voorbeeldbestand heb ik bovenstaande routines opgenomen, inclusief een beschrijving hoe een macro te starten m.b.v. een knop.
Ook heb ik daar de 2 voorbeelden van het begin (NAW-gegevens invoegen en lege regels verwijderen) uitgewerkt.