Excel-macro's maken met de VBA-editor

Office 365 aanbiedingen bekijken
macros in excel

Macro’s in Excel

Wie regelmatig met tekstverwerkingsprogramma’s, databanksystemen of toepassingen voor tabelberekening werkt, weet hoe moeizaam het kan zijn om telkens weer dezelfde instructies te moeten invoeren. Vooral ingewikkelde, multilayer programma’s kunnen zo al snel je geduld op de proef stellen. Zogeheten macro’s bieden hier uitkomst. Hiermee kun je een reeks commando’s opslaan zodat deze door de gebruiker van de software altijd automatisch kunnen worden uitgevoerd. Hoewel macro’s in een programmeertaal zijn geschreven, hoef je heus geen computerexpert te zijn om ze te beheersen. Veel programma’s, waaronder Excel, kunnen simpele macro’s opslaan en zo hulp bieden bij de verdere ontwikkeling van de macro’s. Voor Microsoft Excel zijn macro’s één van de belangrijkste features.


wat is excel macro

Wat is een Excel macro?

Microsoft Excel is een van de populairste programma’s voor het bewerken, analyseren en presenteren van gegevens. Windows-gebruikers werken al jaren met het tabelberekeningsprogramma, dat onderdeel uitmaakt van het Microsoft Office-pakket, om bijvoorbeeld de begroting op te stellen of om een persoonlijke kalender te maken. Ook bij bedrijven wordt Excel ingezet: met deze software kunnen immers project- en budgetplanningen en uurtabellen worden opgesteld, maar ook grafieken van omzetcijfers, winsten of verliezen. Iedereen die bekend is met de werking van het programma, zal al snel de talrijke, handige functies leren waarderen – en helaas ook snel een moeilijk te onderdrukken afkeer ontwikkelen voor routinehandelingen of taken die telkens weer terugkomen en die niet eenvoudig met behulp van de standaardinterface kunnen worden uitgevoerd.

Niet voor niets is het zo dat de mogelijkheid om macro’s in Excel te maken een kernfeature van het programma is. De hiervoor ontwikkelde tool maakt gebruik van de programmeertaal Visual Basics for Applications (VBA), die niet alleen in Excel maar ook in de Microsoft Office-programma’s Word, PowerPoint, Access en Outlook wordt gebruikt. Dankzij deze taal kun je eenvoudig zelf Excel macro’s maken waarmee je de eerdergenoemde routinehandelingen automatisch kunt laten uitvoeren of waarmee je nieuwe functies aan een tabel (bijvoorbeeld algoritmes voor data-analyse) kunt toevoegen.


zo werken macros

Zo werken macro’s in Excel

Excel biedt de verschillende onderdelen van de gebruikersinterface aan via hiërarchisch georganiseerde programmaobjecten. Deze hebben ieder hun specifieke eigenschappen en methodes. Alle beschikbare objecten zijn via het overkoepelende objectmodel met elkaar verbonden en komen terug in de visuele gebruikersinterface waarmee knoppen en invoervelden ter beschikking worden gesteld om de toepassing te gebruiken. Door eigenschappen vast te leggen en de methodes op te roepen, kun je de verschillende objecten bewerken. Voor het object ‘Workbook’ bestaat bijvoorbeeld de methode ‘Close’ waarmee je de gekozen werkmap kunt sluiten. Ook heeft dit object de eigenschap ‘ActiveSheet’ waarmee je het werkblad kunt weergeven dat op dat moment actief is in de werkmap.

Met behulp van verzamelingen kunnen macro’s bovendien handelingen uitvoeren voor een hele groep objecten. Het opsommingsobject ‘Worksheet’ zorgt er in een macro bijvoorbeeld voor dat de instructies voor alle werkbladen gelden. Voor de uitvoering van een macro heb je de volgende mogelijkheden:

  • Selecteren in het macromenu.
  • Klikken op de individueel aangemaakte knop.
  • Individuele toetsencombinatie.

voordelen excel macros

De voordelen van Excel macro’s

Als je tot nu toe bewust of onbewust geen macro’s in Excel hebt gemaakt, hoeft dat niet persé negatieve invloed op je Excel-bestanden te hebben. Toch heb je het jezelf daarmee moeilijker gemaakt dan nodig is. Het gebruik van macro’s biedt namelijk een aantal belangrijke voordelen die Excel macro’s verplichte kost maken voor iedereen die zijn of haar berekeningssoftware optimaal wil benutten:

  • Macro’s verminderen het aantal fouten: met iedere instructie die je handmatig in Excel invoert, neemt de kans toe dat je ergens een fout maakt. Vooral bij complexe commando’s en stappen die telkens weer terugkomen, kun je gemakkelijk een fout maken die de volledige functionaliteit blokkeert. Bij een macro is deze kans alleen aanwezig bij het aanmaken van de macro. Als je hier geen fouten maakt, werkt de Excel macro altijd foutloos.
  • Macro’s verminderen de werklast: je hoeft een macro maar één keer te maken. Daarna kun je ze zo vaak als je wilt met slechts een klik op de knop of met de juiste toetsencombinatie starten. Dat scheelt een hoop tijd die je voortaan voor andere dingen kunt gebruiken.
  • Macro’s breiden Excel uit: met VBA kun je niet alleen macro’s programmeren en commando’s samenvatten, maar ook nieuwe functies ontwikkelen. Zo breid je de geïntegreerde functieverzameling flink uit met je eigen werkbladfuncties en eenvoudigere formules. Excel presenteert jouw eigen functies alsof ze bij het programma horen. Bovendien kun je alle zelfgemaakte macro’s met eigen knoppen verbinden aan het Excel-menu waarmee je ze gemakkelijk kunt activeren.

Zelf een Excel macro maken

Om zelf een verzameling aan commando’s te maken, moet je eerst de VBA-editor activeren die bij de ontwikkelaarstool hoort. Deze is niet standaard in het menu (dat bij Microsoft Office-toepassingen ook wel het ‘lint’ wordt genoemd) te vinden. Daarom moet je het bijbehorende tabblad eerst toevoegen.


Stap 1: tabblad ‘Ontwikkelaars’ toevoegen

Klik hiervoor op het tabblad ‘Bestand’ en klik vervolgens op ‘Opties’. Onder ‘Lint aanpassen’ vind je een opsomming van de belangrijkste tabbladen, waaronder ook ‘Ontwikkelaars’.

screenshot ontwikkelaars

Vink het betreffende vakje aan en bevestig de wijziging door op ‘OK’ te klikken. Het tabblad verschijnt vervolgens op het lint.

screenshot tabblad ontwikkelaars

Stap 2: een nieuwe werkmap aanmaken voor macro’s

Bij de tweede stap maak je een nieuwe Excel-map aan waarin je de macro’s kunt opslaan. Daarvoor moet je op het zojuist geactiveerde tabblad ‘Ontwikkelaars’ klikken. Vervolgens klik je op de knop ‘Macro’s’ en vul je onder ‘Macronaam’ de naam van de nieuwe map in (hier bijvoorbeeld ‘Hallo’). Klik op ‘Maken’ om de map te bevestigen. Vervolgens opent de VBA-editor met de volgende code:

screenshot nieuwe werkmap

Sub staat voor subroutine en daarmee voor een macro die een soort ondergeschikt programma van Excel vormt. Voor de betreffende macro wordt de gehele code uitgevoerd, die tussen Sub en End Sub staat. Als test kun je de code van deze voorbeeldmacro ‘Hallo’ als volgt uitbreiden:


		
Sub Hallo()
MsgBox ("Hallo Wereld!")
End Sub

Sla het resultaat op als .xlsm-bestand, ga terug naar Excel, klik opnieuw op ‘Macro’s’ en kies de ‘Hallo’-macro uit de aangegeven lijst. Als je nu op ‘uitvoeren’ klikt, verschijnt er een klein dialoogvenster met de zojuist ingevoerde tekst:

screenshot hallo wereld box

Door op ‘OK’ te klikken sluit je het venster en beëindig je de macro. Sla vervolgens de macrowerkmap op in de VBA-editor.


Stap 3: een knop aanmaken voor snelle toegang tot de macro’s

In zowel het tabblad ‘Ontwikkelaars’ als het tabblad ‘Beeld’ kun je de beschikbare macro’s in Excel weergeven en uitvoeren. Als je het commando echter vaker nodig hebt, is het handig om een knop op de werkbalk aan te maken voor snelle toegang. Dit doe je zo:

  1. Open het tabblad ‘Bestand’.
  2. Ga naar ‘Opties’ en klik op ‘Werkbalk Snelle toegang’.
  3. Ga naar ‘Kies opdrachten uit’ en klik in de keuzelijst op ‘Macro’s’. Zoek hier naar de eerder gemaakte ‘Hallo’-macro.
  4. Selecteer de macro en klik op ‘Toevoegen’. Hiermee verplaats je de macro naar de lijst met knoppen voor snelle toegang.
  5. Voordat je de nieuwe knop met ‘OK’ bevestigt, kun je nog een pictogram voor de knop uitzoeken. Selecteer daarvoor de macro en klik op ‘Wijzigen’.

Nu heeft de werkbalk, naast de standaardknoppen ‘Opslaan’, ‘Ongedaan maken’ en ‘Herhalen’, ook een knop voor de ‘Hallo’-macro:

screenshot hallo macro werkbalk


TIP!
als alternatief kun je ook een toetsencombinatie vastleggen voor het uitvoeren van de macro.
(‘Ontwikkelaars’ -> ‘Macro’s’ -> ‘Opties’)

Stap 4: macro’s opnemen

De VBA-editor die Microsoft in zijn Office-toepassingen heeft geïntegreerd, heeft als voordeel dat je de programmeertaal niet hoeft te beheersen om simpele Excel macro’s te maken. Hiervoor volstaat namelijk de geïntegreerde functie ‘Macro opnemen’. Als voorbeeld maken we nu een macro die het werkblad automatisch een nieuwe naam geeft.

  1. Klik deze keer in het tabblad ‘Ontwikkelaars’ op de knop ‘Macro opnemen’.
  2. In het volgende dialoogvenster geef je de macro de naam ‘RenameWorksheets’ en begin je vervolgens met opnemen door op ‘OK’ te klikken.
  3. Hernoem ‘Blad1’ links onderin het scherm nu naar ‘Nieuwe naam’ en beëindig de opname door op ‘Opname stoppen’ te klikken.

Als je nu teruggaat naar de VBA-editor (‘Ontwikkelaars’ -> ‘Macro’s’ -> ‘Bewerken’), zou je de volgende code te zien moeten krijgen:

screenshot nieuwe naam macro

De eerste vier coderegels onder de Sub-regel beginnen met een apostrof. Dit zijn commentaarregels. Deze regels hebben geen invloed op het algemeen functioneren van de macro en zijn er in de eerste plaats om de code beter leesbaar te maken. Bovendien kun je coderegels met behulp van opmerkingen ook tijdelijk uitschakelen. Voor de voorbeeldmacro heb je deze vier automatisch gegenereerde regels niet nodig. Je kunt ze zonder problemen wissen.

De volgende regel bevat de selectiemethode, het uitkiezen van ‘Blad1’, dat bij het handmatige proces van herbenoeming nodig was voordat je deze een nieuwe naam kon geven. VBA-scripts hoeven objecten echter niet uit te kiezen om ze te kunnen bewerken. Daarom heb je deze coderegel ook niet nodig voor de Excel macro. Daarmee ziet de code er nu als volgt uit:


		
Sub RenameWorksheets ()
Sheets("Blad1").Name = "Nieuwe naam"
End Sub

Ga vervolgens terug naar Excel en hernoem het werkblad weer naar ‘Tabel1’. Als je nu de macro ‘RenameWorksheet’ uitvoert, zou de naam automatisch moeten veranderen. Let op dat je de macro nu aan de nieuwe naam moet aanpassen om hem opnieuw te kunnen gebruiken.

screenshot naam aanpassen macro

Excel macro’s in de praktijk: grafieken en invoermaskers

VBA-scripts maken het mogelijk om de meest uiteenlopende taken in Excel te automatiseren. Met de opnamefunctie kun je niet alleen proberen om je eigen werkproces te vereenvoudigen, maar leer je ook hoe de krachtige programmeertaal werkt. Aarzel dan ook niet om de VBA-tool te gebruiken als je denkt dat je een bepaalde stap kunt vereenvoudigen met een Excel macro.

In de vorige alinea’s hebben we het algemene proces bij het maken van een macro doorgenomen. De volgende twee voorbeelden laten je heel concreet zien hoe veelzijdig de automatisering van commando’s kan zijn.


Excel-Evergreen: grafieken maken op basis van cellen

Een veelgebruikte functie van Excel-tabellen is het visualiseren van ingevoerde data in de vorm van een grafiek. Dit kost vaak veel tijd, maar een macro kan hierbij erg nuttig zijn. Maak hiervoor eerst een macro aan met de naam ‘AssortedTasks’ en bepaal de variabele voor je grafiekobject:

Dim mijngrafiek As Chartobject

In de volgende regel maak je vervolgens een grafiek aan en wijs je deze toe aan de mijngrafiek-variabele:

Set mijngrafiek = ActiveSheet.ChartObjects.Add(100, 50, 200, 200)

De waardes tussen haakjes bepalen de positie en de grootte van de grafiek. De eerste twee waardes zijn de coördinaten voor de linkerbovenhoek, gevolgd door de waardes voor de breedte en de hoogte. Als je de macro nu uitvoert, maakt Excel de grafiek automatisch aan op de opgegeven positie en in het opgegeven formaat – natuurlijk is de grafiek nog leeg, omdat momenteel de inputgegevens nog ontbreken.

screenshot grafiek toevoegen

Tot slot vul je de grafiek met data, die vervolgens naar wens kan worden weergegeven. Daarvoor moet je ten eerste de waardes invoeren, maar ook moet je de Excel macro een beetje aanpassen. Momenteel weet de macro namelijk nog niet waar hij de informatie, die moet worden weergegeven, vandaan moet halen. In het kader van de constructie With … End With moet je daarom nu de SetSourceData-methode gebruiken en moet je deze specificeren door de waarde Selection. Deze zorgt ervoor dat bij het uitvoeren van de macro alle gemarkeerde cellen worden herkend. De volledige code van de macro ziet er dan als volgt uit:

Sub AssortedTasks()
					
Dim mijngrafiek As ChartObject
Set mijngrafiek = ActiveSheet.ChartObjects.Add(100, 50, 200, 200)
With mijngrafiek
.Chart.SetSourceData Source := Selection
End With
End Sub

Voer nu als test de waardes 1 tot en met 5 in de Excel-cellen A1 tot en met A5 in en voer de macro uit. De gegevens zouden als staafdiagram moeten worden weergegeven. Dit is de standaardgrafiek in Excel als je geen andere ChartType-gegevens opgeeft.

screenshot grafiek invullen

Zo maak je een dialoogvenster aan voor gebruikersinvoer

Met de macrotool kun je echter ook andere invoermaskers aanmaken waarmee de gebruiker met Excel kan werken. Om bijvoorbeeld een interactievenster te maken waarmee je de ingevoerde waarde automatisch in een opgegeven cel invoert, begin je zoals gewoonlijk met het aanmaken en herbenoemen van de macro. In ons voorbeeld gebruiken we de naam ‘Invoermasker’. Vervolgens geef je de subroutine de locatie van de later door de gebruiker ingevoerde waarde:

	
		
Sub Invoermasker()
ActiveSheet.Range("A1").Value =
End Sub

De macro zorgt er dus voor dat de input van de gebruiker in cel A1 van tabel1 wordt ingevoerd. De waarde van het veld (Value) moet nog worden toegewezen door het tweede deel van de instructie. In ons voorbeeld hebben we gekozen voor het commando Inputbox. Dit lijkt erg op het MsgBox-commando dat we voor onze ‘Hallo’-macro hebben gebruikt. Samen met de volgende drie argumenten, die in de VBA-code tussen aanhalingstekens staan, ontstaat een doelgericht invoervenster:

  • Prompt: met het eerste argument definieer je de tekst van het dialoogvenster die de gebruiker bij de invoer ondersteunt.
  • Title: het ‘Title’-argument is de titel van het invoermasker.
  • Default: ten slotte kun je met ‘Default’ ook een standaardinvoer instellen.

Een mogelijke voorbeeldcode van de volledige ‘invoermasker’-macro inclusief venster en tekst ziet er zo uit:


		
Sub invoermasker()
Tabel1.Range("A1").Value = InputBox("Geef a.u.b. een waarde voor cel A1 op.", "Titel van het invoermasker", "Waarde voor cel A1")
End Sub

Als je deze Excel macro uitvoert, verschijnt het volgende bijbehorende dialoogvenster:

screenshot waarde cel a1

Het delen van Excel macro’s

Als je in je Excel-werkmap macro’s hebt aangemaakt, kun je deze zonder problemen gebruiken in een ander document of delen met andere gebruikers. De VBA-editor heeft hiervoor een Import-/Exportfunctie waarmee je commando’s als .bas-bestanden kunt opslaan of de als .bas-bestand opgeslagen macro’s in je Excel-document kunt integreren. Hiervoor moeten macro’s echter wel zijn geactiveerd in het doelbestand. Omdat de handige scripts mogelijk gevaarlijke codes kunnen bevatten, stelt Excel een aantal beperkingen om gebruikers te beschermen. Zo worden bijvoorbeeld alle macro’s zonder digitale handtekening geblokkeerd, met of zonder notificatie. De simpele oplossing hiervoor is om alle macro’s automatisch toe te staan, al moet je er bij het importeren wel op letten dat de codes betrouwbaar zijn.

De opties voor het activeren en deactiveren van macro’s kun je vinden in het ‘Vertrouwenscentrum’: klik hiervoor op het tabblad ‘Bestand’ en kies ‘Opties’, ‘Vertrouwenscentrum’ en klik vervolgens op de knop ‘Instellingen voor het Vertrouwenscentrum’. Daar vind je het menu-item ‘Macro-instellingen’ inclusief de genoemde instellingsmogelijkheden.

screenshot alle mcros inschakelen

Om nu een macro te exporteren, moet je de VBA-editor openen. Klik in het menu op het tabblad ‘Bestand’ en vervolgens op ‘Bestand exporteren’. Geef je macro-verzameling een relevante naam, kies de gewenste locatie en rond het exporteren af met ‘Sluiten’. Het geëxporteerde bestand is maar een paar bits groot en kan daarom gemakkelijk per mail worden verstuurd of via een draagbaar opslagmedium naar de gewenste computer worden overgezet. Om een macrobestand te importeren, ga je grotendeels op dezelfde manier te werk, behalve dat je natuurlijk kiest voor ‘Bestand importeren’ en vervolgens de opslaglocatie van het bestand aangeeft.

  • Gecertificeerde veiligheid

    Gecertificeerde veiligheid
  • Beste hostingbedrijf

    Beste hostingbedrijf
  • MKB Best Choice

    MKB Best Choice
  • Professionele support

    Professionele support
  • Hosted in Germany

    Hosted in Germany