5 belangrijke redenen om VBA voor Excel te ontdekken

03 april 2012

Heeft u zich wel eens gewaagd aan macro's? Dan is de kans groot dat u de handdoek snel daarna al in ring heeft geworpen. Het inflexibele karakter van macro's is hier in veel gevallen debet aan. Een opgenomen macro is erg star en wijkt bij het afspelen geen centimeter van het oorspronkelijke pad. Dat is onder veranderende omstandigheden nu juist wel de bedoeling. In deze gevallen biedt VBA ("Visual Basic for Applications") een schat aan mogelijkheden.

Met VBA bent u in staat om een opgenomen macro te openen in de VBA editor en zet u een van oorsprong inflexibele macro relatief eenvoudig naar uw hand. De macrorecorder blijft daarbij het omvangrijke voorwerk verrichten en zet toetsaanslagen automatisch om in VBA code. In de VBA editor verandert u vervolgens selectief de code om de macro ook onder veranderende omstandigheden te kunnen benutten.

Het goede nieuws daarbij is dat u hiervoor zeker geen programmeur hoeft te zijn. Voldoende is dat u de hoofdstructuur en hoofdregels van VBA leert kennen en dat u binnen het VBA script de plaatsen duidt waar verandering binnen een opgenomen macro gewenst is. Op deze plaatsen past u de code aan en verandert u een “nutteloze” macro in een geweldige tool.

Onderstaand vijf belangrijke redenen om uw werk eenvoudiger te maken met VBA.

Reden 1: VBA is gemakkelijk te leren
Visual Basic is, evenals verre voorganger Basic uit de jaren zeventig en tachtig van de vorige eeuw, nog steeds een taal die relatief snel onder de knie te krijgen is met als bijkomend voordeel dat het programma inmiddels volledig geënt is op voor de gebruiker herkenbare Windows objecten. Visual Basic beschouwt alle onderdelen waar een Excel gebruiker mee te maken krijgt als objecten. Dit geldt zowel voor de meer algemene Windows- (vensters, invoervelden, knoppen) als voor meer specifieke Excel onderdelen (zoals werkmappen, werkbladen en cellen).

Visual Basic vertoont veel kenmerken van objectgeoriënteerd programmeren en dat biedt voordelen boven het gebruik van traditioneel “chronologisch” georiënteerde programmeertalen (zoals COBOL en Fortran uit het verleden). In een objectgeoriënteerde omgeving zijn procedures primair gekoppeld aan gebeurtenissen van herkenbare objecten. De programmeur legt vast wat er bij gebeurtenissen moet gebeuren, zonder dat daarbij de volgorde van de gebeurtenissen vooraf vastligt. Procedures in Visual Basic worden flexibel uitgevoerd in de volgorde waarin de gebeurtenissen zich voordoen.

En zoals in de inleiding al vermeld, vormt de macro recorder hierbij meer dan alleen een handig hulpje aan de zijlijn. Het VBA programmeerwerk wordt, tijdens het opnemen van de macro, geheel automatisch gedaan door de macrorecorder.

Als u via de macrorecorder een bepaalde taak uitvoert, manipuleert u onderliggende objecten, ongeacht of u met de toepassing zelf (het zgn. Application-object), met werkmappen (het zgn. Workbook-object), met werkbladen (het zgn. Worksheet-object) of met afzonderlijke cellen (het zgn. Range-object) werkt. En elk object kan hiërarchisch weer meerdere ondergelegen objecten bevatten. Zo bevat het Workbook-object Worksheets-objecten, die op hun beurt weer Cell- of Range objecten bevatten.

Bij het werken aan een object wordt er een “eigenschap” ingesteld, of wordt aan het object de opdracht gegeven een actie uit te voeren. In dit laatste geval spreken we van een “methode”. Als u bijvoorbeeld binnen een actieve werkmap het werkblad met de naam “Blad2” selecteert gebruikt VBA de methode Select van het Worksheet-object:

Worksheets(“Blad2”).Select

en als u binnen dit werkblad cel A1 van een rode kleur wilt voorzien, geeft VBA hieraan via de eigenschappen “Interior” en “Colorindex” de volgende invulling

Range(“A1”).Select
With
Selection.Interior.ColorIndex = 3(rood)
End With

Als u vervolgens bij het afspelen van de macro een ander werkblad dan “Blad 2” wilt selecteren (bijvoorbeeld “Blad n”) dan past u in de VBA editor de code als volgt aan:

Worksheets(“Blad n”).Select

En wilt u er nog wat meer flexibiliteit inbrengen dan kunt u via een eenvoudig invoervenster of invoerformulier (het VB object “Inputbox”) de gebruiker het gewenste werkblad laten definiëren:

Dim keuzewerkblad AS String
keuzewerkblad = Inputbox(“Geef de naam van het te selecteren werkblad”)
Sheets(keuzewerkblad).Select

Maakt u zich vooral niet teveel zorgen als u het niet direct vat. De essentie is dat u de belangrijkste objecten binnen Excel herkent en dat u na opname met de macrorecorder de bijbehorende VBA code aanpast op die plekken waarbij u meer flexibiliteit wenst.

Reden 2: het flexibele karakter van VBA

Naast een objectgeoriënteerde is VBA ook een gebeurtenisgeoriënteerde taal. Dit betekent dat de programmacode naar aanleiding van een gebeurtenis wordt uitgevoerd.
Als voorbeelden van vaak binnen Excel voorkomende gebeurtenissen kunnen worden genoemd:

•   Werkmappen openen en sluiten;
•   Werkmappen activeren of deactiveren;
•   Werkmappen opslaan;
•   Op een grafiek klikken;
•   Op een (zelfgemaakte) toets klikken;
•   Gegevens invoeren in een cel;
•   Op een hyperlink klikken.

Dit maakt VBA tot een uiterst flexibele taal welke veel meer herbergt dan uitsluitend het bijschaven en het op maat maken van eerder opgenomen macro’s. Als praktische toepassing valt bijvoorbeeld te denken aan het automatisch beveiligen of verbergen van celbereiken op het moment dat een werkblad wordt geopend. Of het automatisch laten verschijnen van een grafiek nadat een celbereik is gevuld. Door gebeurtenisgeoriënteerd te gaan programmaren krijgt Excel een professionelere en gebruikersvriendelijkere uitstraling. Daarnaast wordt het, als reactie op door gebruikers veroorzaakte gebeurtenissen, beter mogelijk om werkbladonderdelen gericht te gaan beveiligen en om gebruikers te “sturen”.

De eerlijkheid gebiedt overigens wel te zeggen dat hiervoor gemiddeld meer VBA kennis vereist is dan voor het aanpassen van VBA code op basis van eerder opgenomen macro’s.

Lees verder op pagina 2

Klassiekers uit het archief van Financieel-Management.nl:
10 veel voorkomende fouten in Excel
10 veel voorkomende fouten in Excel – deel 2

Bekijk ook de inspirerende video's:
Excel Video #1: Winstmaximalisatie met de oplosser
Excel Video #2: Bekijk alle opties met 'wat-als' analyses
Excel Video #3: Overzicht in een handomdraai met een 'histogram'
Excel Video #4: Elimineer fouten met snijpuntoperatoren
Excel Video #5: Boek tijdswinst met draaitabellen
Excel Video #6: Foutloos vermenigvuldigen met 'Productmat'
Excel Video #7: Effectief Formules controleren
Excel Video #8: Optimale rooster- of werkplanning
Excel Video #9: Sneller rekenen met 'plakken speciaal'
Excel Video #10: Snel foutmeldingen opsporen en herstellen

Reden 3: met VBA kunt u acties herhalen of juist overslaan
Programmacode binnen een VBA procedure wordt normaal gesproken instructie na instructie uitgevoerd. Dit gebeurt ook letterlijk zo als u een macro opneemt en deze naderhand afspeelt. Maar zoals we als zagen speelt vaak de behoefte om deze top down benadering te doorbreken. VBA biedt verschillende methoden om bepaalde delen van de programmacode te herhalen of om delen over te slaan. VBA kan flexibel reageren op basis van gevarieerde invoer van de gebruiker of op basis van berekende waarden binnen een spreadsheet.

Om acties te herhalen maakt VBA gebruikt van lus- of loopmethoden. Door een lus in te brengen kunt u bijvoorbeeld een enkelvoudig opgenomen macro veranderen in een macro die zichzelf zo vaak als nodig herhaalt. VBA kent expressies voor onder andere de volgende soorten lussen:

“For … Next”
Met deze lussen kunt u – door verwijzing naar bijvoorbeeld een celwaarde – een actie een aantal malen laten herhalen. Met een “For … Next” lus kunt u bijvoorbeeld bepalen hoe vaak een enkelvoudig opgenomen macro achter elkaar afgespeeld dient te worden.

“For Each …. Next”
Met deze lussen kunt u een actie herhalen voor elk object in een (qua omvang) wisselende verzameling. Met een “For Each … Next” lus kunt u eenzelfde actie uitvoeren binnen elk werkblad van een werkmap. Als u bijvoorbeeld cel A1 van ieder werkblad wilt voorzien van een datumstempel dan kan dit op basis van de instructie

For Each Worksheet In Worksheets
Range(“A1”) = “=TODAY()”
Next

“Do While … Loop” en “Do Until …. Loop”
Met een “Do While …. Loop” wordt een actie uitgevoerd zolang aan een opgegeven voorwaarde wordt voldaan. Met de “Do Until … Loop” gebeurt dit tot het moment waarop dit niet langer het geval is.

Naast het gebruik van lussen maakt VBA gebruik van “IF” en “Select Case” opdrachten.
Hiermee bent u in staat bent om een deel van een VBA instructieset door te lopen of juist over te slaan.
Dit kan in de vorm van “IF … Then”, “If … Then …. Else”, “If … Then …. Elseif … Else” en “Select …. Case” opdrachten.

Reden 4: VBA biedt toegang tot een breed ontwikkelplatform
VBA is altijd onlosmakelijk verbonden met een specifiek hostprogramma binnen de Microsoft Office omgeving. En ofschoon VBA als eigenstandig programma niet los van de host (lees in ons geval Excel) kan functioneren blijkt het, o.b.v. een gezamenlijke objectenbibliotheek, toch goed mogelijk met andere Officeonderdelen (Word, Powerpoint en Outlook) te communiceren. VBA gebruikt daartoe voor alle Office onderdelen een identieke editor.

Met VBA is het – o.b.v. gemeenschappelijke objecten – bovendien goed mogelijk om via diezelfde ojectenbibliotheek met elke ander op VB (Visual Basic) gebaseerd programma te communiceren.
En dat kan altijd in twee richtingen: dus vanuit VBA heeft u toegang tot niet Excel objectenbibliotheken en vice versa kunnen andere programma’s gebruik maken van de Excel objecten. Het is zelfs goed mogelijk om, via een interface, met aan VB gelieerde programma’s te communiceren.

Reden 5: VBA als hulp bij omvangrijke taken
De stappen die doorlopen moeten worden om tot een periodieke rapportage te geraken zijn vaak omvangrijk en complex. Bovendien zijn deze stappen (zeker als gekeken wordt naar het niveau van alle onderliggende toetsaanslagen) nooit identiek. Dus als u met een macro het werk wilt verlichten is de kans dat een eenmaal opgenomen macro bij een volgende rapportagegelegenheid al direct strandt, levensgroot.

VBA biedt bij deze omvangrijke terugkerende klussen de mogelijkheid om deze op te knippen is beheersbare modules. Na het testen kunnen deze modules achter elkaar uitgevoerd worden. Loopt een module vast, of dient deze herzien te worden vanwege zich wijzigende omstandigheden, dan hoeft alleen de betreffende module veranderd te worden. Bij een macro bestaat deze mogelijkheid niet en is het “alles of niets”.

Klassiekers uit het archief van Financieel-Management.nl:
10 veel voorkomende fouten in Excel
10 veel voorkomende fouten in Excel – deel 2