Als geslaagd uitbater van de beste broodjeszaak, weet je inmiddels waar je omzet mist. Met de vorige blog heb je namelijk je eigen overzicht gemaakt. Een dervingsdashboard met een heldere splitsing tussen het invoerwerkblad en het werkblad waar informatie wordt bewaard en samengevoegd. Je bent alleen nog elke keer de data handmatig van het ene werkblad naar het andere aan het kopiëren. Dat kan makkelijker.
Hieronder laat ik je zien hoe je met één druk op die knop, data van de ene sheet naar de andere kan laten kopiëren. Daar gebruiken we een macro voor. Dat scheelt veel tijd en voorkomt typefouten.
Wat is een macro?
Een macro is een stukje Visual Basic (VB) code die repetitieve handelingen op een makkelijker manier automatiseert. Zo’n macro kan je vastleggen door toetsaanslagen en muisklikken op te nemen. Microsoft vertaalt die opnamen vervolgens in Visual Basic code. Je kan ook gelijk Visual Basic code invoeren en dat is wat wij gaan doen. Dat klinkt misschien complex en lastig, maar met behulp van onderstaande stappen en voorbeeld code heb je het zo geregeld.
Instellingen
Allereerst selecteer je via Instellingen de juiste optie om gebruik te kunnen maken van macro’s. Sommige virussen maken gebruik van macro’s, dus altijd toestaan is niet verstandig en met nooit toestaan, weet je zeker dat je macro niet gaat werken. De gulden middenweg is niet toestaan met notificatie te selecteren. Met deze optie verschijnt er een keuzevenster zodra je een Excelbestand met macro’s wilt openen.
Liever uitbesteden aan Expert Excel?
Met het team Excel specialisten sta ik voor je klaar!
De juiste namen
De VB code verwijst naar de namen van de diverse werkbladen. Als de naam slechts afwijkt door een extra spatie, dan snapt de macro niet meer welk werkblad je bedoelt. Voor het gemak kan je hier een vereenvoudige versie van het dervingsdashboard downloaden waar je zelf de macro aan kan toevoegen.
Gedefinieerde namen
Om Excel te vertellen welke velden gekopieerd moeten worden zijn er twee manieren. Als het slechts velden uit een tabel zijn, dan kan het via rijnummer en kolomnummer. Maar in ons voorbeeld hebben we ook een viertal velden boven de tabel staan.
Hier gebruiken we gedefinieerde namen voor. Ga naar de menuknop Formules en klik op gedefinieerde namen.
Elke van de vier velden geven we een unieke naam en koppelen we aan de juiste cel. In de VB code kunnen we nu deze veldnamen gebruiken en waardoor Excel exact weet welke cellen bedoeld worden.
Een button met een macro
Om de macro straks te activeren, gebruiken we een button. Download hier vast de code, dan kan je gelijk de macro aan de button verbinden.
Voeg via de menuknop Ontwikkelaar de button toe.
Pas de macronaam aan naar ‘Voerin’ en klik daarna op Nieuw.
Het Visual Basic venster verschijnt nu.
Kopieer de gehele inhoud uit de gedownloade tekstbestand en plak dat over de reeds aanwezige tekst in het rechter venster.
Klik op opslaan. Werkend vanuit het gedownloade Voorbeeld excel.xlsx, zal je een melding ontvangen dat macro’s niet kunnen opgeslagen. Klik op ‘niet opslaan’ om daarna het keuzevenster te krijgen. Selecteer het .xlsm bestandstype.
Als je nu weer naar het venster van het dervingsdashboard gaat, zie je een mooie macro button toegevoegd. De tekst ‘Button 1’ passen we aan door met ctr/control op de knop te klikken. Klik daarna even naast de knop, zodat de button weer actief wordt om de macro te activeren.
Wanneer je nu op de button klikt, zullen de velden naar het volgende werkblad gekopieerd worden!
De visual basic code uitgelegd
Het is natuurlijk fijn dat de macro werkt, maar wat doet die code nou (in grote lijnen)? Eerst terug naar het Visual Basic venster via de menuknop Ontwikkelaar en Visual Basic.
De macro gaat van boven naar beneden door de code heen. Daarom starten we met het declareren van de variabelen. Dim i As Long: i = 9 geeft aan dat Excel op de 9e regel moet starten. Met de volgende regel verbinden we variabele i met de velden van het eerste werkblad, startend in kolom B. Variabele j gebruiken we voor de velden op het tweede werkblad. Om te voorkomen dat de header op het tweede werkblad wordt overschreven, gebruiken we de + 1 toevoeging bij Row + 1. Met de onderste twee regels declareren we de ‘van’ en ‘naar’ variabelen.
De volgende regels controleren of de velden met gedefinieerde namen gevuld zijn. Zo niet, dan verschijnt er een foutmelding.
Een werkblad kan beveiligd zijn, daarom wordt eerst een standaardprogramma aangeroepen die de beveiliging eraf haalt.
Daarna volgt een loopje dat start wanneer cel B9 gevuld is en eindigt wanneer er geen data meer staat in kolom B. Elke veldnaam van het eerste werkblad wordt gekoppeld aan een veldnaam van het tweede werkblad. De cijfers refereren hier naar de kolom. In de eerste vier regels zie je ook de eerder gedefinieerde velden zoals str_naam.
Als laatste stap beveiligt de macro het tweede werkblad. Niet per se nodig, maar wel makkelijk zodat je kan zien dat de macro zijn werk heeft gedaan.
Door eenmalig wat tijd te investeren in een macro hoef je voor dit dashboard nooit meer handmatig informatie te kopiëren en plakken. En zo creëer je een fijne workflow, die veel minder tijd kost en de kwaliteit van de data waarborgt.
Succes!
Om de blog overzichtelijk te houden, zal ik in de volgende blog het Macro gedeelte uitleggen. Voor nu kan je handmatig de data uit de invoer tab kopiëren om hetzelfde resultaat te bereiken.
