Een beginnershandleiding over het schrijven van VBA-macro's in Excel (en waarom u dit zou moeten leren)

Excel-macro's kunnen u veel tijd besparen door het automatiseren van Excel-processen die u vaak gebruikt Hoe een Macro opnemen in Excel 2016 Hoe een Macro opnemen in Excel 2016 Wist u dat u terugkerende taken in Microsoft Excel kon automatiseren met behulp van macro's? We laten u zien hoe u een macro kunt opnemen in Excel 2016 om veel tijd te besparen. Lees verder . Maar macro's zijn eigenlijk vrij beperkt. Het is gemakkelijk om een ​​fout te maken met de opnametool en het opnameproces is lastig.

Het gebruik van VBA om macro's te maken geeft je veel meer kracht. U kunt Excel precies vertellen wat u moet doen en hoe u het moet doen. Je krijgt ook toegang tot veel meer functies en mogelijkheden. Als u Excel regelmatig gebruikt, is het de moeite waard om te leren hoe u VBA-macro's kunt maken.

We beginnen met de basis.

Wat is VBA?

VBA is Visual Basic for Applications, een programmeertaal die je in veel Microsoft-apps kunt gebruiken. Visual Basic is een programmeertaal en VBA is de toepassingsspecifieke versie ervan. (Microsoft heeft Visual Basic in 2008 opgeheven, maar VBA gaat nog steeds sterk).

Gelukkig voor niet-programmeurs is VBA erg eenvoudig en de interface die je gebruikt om het te bewerken biedt veel hulp. Veel van de opdrachten die u gebruikt, bevatten pop-upvoorstellen en automatische aanvullingen, zodat u uw script snel kunt laten werken.

Toch duurt het een tijdje om aan VBA te wennen.

De voordelen van VBA-macro's in Excel

Als VBA moeilijker is dan het opnemen van een macro, waarom zou je het dan gebruiken? Het korte antwoord is dat je veel meer kracht haalt uit VBA-macro's.

In plaats van rond uw spreadsheet te klikken en die klikken vast te leggen, krijgt u toegang tot het volledige scala aan functies en mogelijkheden van Excel. Je moet gewoon weten hoe je ze moet gebruiken.

En als je eenmaal vertrouwd bent met VBA, kun je in een veel minder tijd alle dingen doen die je in een gewone macro zou kunnen doen. De resultaten zullen ook meer voorspelbaar zijn, omdat je Excel vertelt precies wat te doen. Er is helemaal geen dubbelzinnigheid.

Nadat u uw VBA-macro hebt gemaakt, kunt u deze eenvoudig opslaan en delen, zodat iedereen er gebruik van kan maken. Dit is vooral handig wanneer u met veel mensen werkt die dezelfde dingen in Excel moeten doen.

Laten we naar een eenvoudige VBA-macro kijken om te zien hoe het werkt.

Een voorbeeld van een VBA-macro in Excel

Laten we naar een eenvoudige macro kijken. Onze spreadsheet bevat namen van werknemers, het winkelnummer waar de werknemers werken en hun kwartaalverkopen.

Deze macro voegt de kwartaalomzet van elke winkel toe en schrijft die totalen naar cellen in de spreadsheet (als u niet zeker weet hoe u toegang kunt krijgen tot de VBA-dialoog, bekijk dan hier onze VBA-oplossing):

Sub StoreSales () Dim Sum1 As Currency Dim Sum2 As Currency Dim Sum3 As Currency Dim Sum4 As Currency For each Cell In Range ("C2: C51") Cell.Activate If IsEmpty (Cell) Then Exit For If ActiveCell.Offset (0, -1) = 1 Then Sum1 = Sum1 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 2 Then Sum2 = Sum2 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 3 Dan Sum3 = Sum3 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 4 Then Sum4 = Sum4 + Cell.Value End If Next Cell Range ("F2"). Waarde = Sum1-bereik ("F3"). Waarde = Sum2-bereik (" F4 "). Waarde = Sum3 Range (" F5 "). Waarde = Sum4 End Sub

Dit ziet er misschien lang en gecompliceerd uit, maar we zullen het opsplitsen zodat je de individuele elementen kunt zien en een beetje kunt leren over de basisprincipes van VBA..

De Sub verklaren

Aan het begin van de module hebben we “Sub StoreSales ()”. Dit definieert een nieuwe sub genaamd StoreSales.

U kunt ook functies definiëren - het verschil is dat functies waarden kunnen retourneren en subs niet (als u bekend bent met andere programmeertalen, subs zijn het equivalent van methoden). In dit geval hoeven we geen waarde te retourneren, dus gebruiken we een sub.

Aan het einde van de module hebben we “End Sub,” wat Excel vertelt dat we klaar zijn met deze VBA-macro.

Variabelen declareren

De eerste regels code in ons script beginnen allemaal met “verduisteren.” Dimmen is de VBA-opdracht voor het declareren van een variabele.

Zo “Dim Sum1” maakt een nieuwe variabele genaamd “SUM1.” We moeten echter Excel vertellen wat voor soort variabele dit is. We moeten een gegevenstype kiezen. Er zijn veel verschillende gegevenstypen in VBA - u vindt de volledige lijst in de helpdocumenten van Microsoft.

Omdat onze VBA-macro te maken heeft met valuta's, gebruiken we het gegevenstype Valuta.

De verklaring “Dim Sum1 als valuta” vertelt Excel om een ​​nieuwe valutavariabele met de naam Sum1 te maken. Elke variabele die u aangeeft moet een hebben “Zoals” verklaring om Excel zijn type te vertellen.

Een For Loop starten

Loops zijn enkele van de meest krachtige dingen die je in elke programmeertaal kunt creëren. Als je niet bekend bent met loops, bekijk dan deze uitleg van Do-While-loops. Hoe doen-terwijl loops werken in computerprogrammeren Hoe doen-terwijl loops werken in computerprogrammering Loops zijn een van de eerste besturingstypes die je leert bij het programmeren . Je weet waarschijnlijk wel over while en for loops, maar wat bereikt een do-while-lus? Lees verder . In dit voorbeeld gebruiken we een For-lus, die ook in het artikel wordt behandeld.

Dit is hoe de loop eruit ziet:

Voor elke cel in bereik ("C2: C51") [een heleboel dingen] Volgende cel

Dit zorgt ervoor dat Excel de cellen in het opgegeven bereik doorloopt. We hebben een Range-object gebruikt Excel-cellen versus bereikfuncties in VBA begrijpen Excel-cellen versus bereikfuncties in VBA begrijpen Het gebruik van de bereik- en cellenfuncties in Excel kan zeer verwarrend zijn. Hier leest u hoe u ze kunt gebruiken op manieren die u zich waarschijnlijk nooit hebt voorgesteld, met de hulp van VBA Read More, een specifiek type object in VBA. Wanneer we het op deze manier gebruiken: bereik (“C2: C51”) -it vertelt Excel dat we geïnteresseerd zijn in die 50 cellen.

“Voor elk” vertelt Excel dat we iets gaan doen met elke cel in het bereik. “Volgende cel” komt na alles wat we willen doen, en vertelt Excel om de lus vanaf het begin te starten (beginnend met de volgende cel).

We hebben ook deze verklaring: “If IsEmpty (Cell) Then Exit For.”

Kun je raden wat het doet?

Notitie: Strikt genomen is het gebruik van een While-lus een betere keuze geweest 4 Fouten die u kunt vermijden bij het programmeren van Excel-macro's met VBA 4 Fouten die u kunt vermijden bij het programmeren van Excel-macro's met VBA Eenvoudige code en macro's zijn de sleutels tot Microsoft Excel-superkrachten. Zelfs niet-programmeurs kunnen gemakkelijk indrukwekkende functionaliteit toevoegen aan hun spreadsheets met Virtual Basics for Applications (VBA). Vermijd gewoon deze programmeerfouten voor beginners! Lees verder . Echter, omwille van het lesgeven, heb ik besloten om een ​​For-lus te gebruiken met een Exit.

Als-dan-anders-verklaringen

De kern van deze specifieke macro bevindt zich in de If-Then-Else-instructies. Dit is onze reeks voorwaardelijke verklaringen:

Als ActiveCell.Offset (0, -1) = 1 Then Sum1 = Sum1 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 2 Then Sum2 = Sum2 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 3 Then Sum3 = Sum3 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 4 Then Sum4 = Sum4 + Cell.Value End If

Voor het grootste deel kun je waarschijnlijk raden wat deze uitspraken doen. U bent misschien niet bekend met ActiveCell.Offset. “ActiveCell.Offset (0, -1)” vertelt Excel om naar de cel te kijken die één kolom links van de actieve cel is.

In ons geval vertelt dat Excel om de kolom met winkelnummers te raadplegen. Als Excel in deze kolom een ​​1 vindt, dan is de inhoud van de actieve cel nodig en wordt deze aan Sum1 toegevoegd. Als het een 2 vindt, wordt de inhoud van de actieve cel toegevoegd aan Sum2. Enzovoorts.

Excel doorloopt al deze uitspraken in volgorde. Als de voorwaardelijke verklaring IF-zinnen in Microsoft Excel gebruiken IF-zinnen in Microsoft Excel gebruiken Of u nu een ervaren expert of een beginnende spreadsheet bent, wilt u deze handleiding voor IF-instructies in Excel bekijken. Meer lezen is tevreden, het voltooit de Dan-verklaring. Als dat niet het geval is, wordt het verplaatst naar de volgende ElseIf. Als het helemaal tot het einde is en geen van de voorwaarden is vervuld, zal het geen actie ondernemen.

De combinatie van de lus en de conditionals drijft deze macro. De lus vertelt Excel om elke cel in de selectie te doorlopen, en de conditionals vertellen wat te doen met die cel.

Celwaarden schrijven

Uiteindelijk kunnen we de resultaten van onze berekeningen naar cellen schrijven. Dit zijn de regels die we gebruiken om dat te doen:

Bereik ("F2"). Waarde = Som1 Bereik ("F3"). Waarde = Som2 Bereik ("F4"). Waarde = Sum3 Bereik ("F5"). Waarde = Som4

Met “.Waarde” en een gelijkteken, we stellen elk van die cellen in op de waarde van een van onze variabelen.

En dat is het! We vertellen Excel dat we klaar zijn met het schrijven van deze sub met “End Sub,” en de VBA-macro is voltooid.

Wanneer we de macro uitvoeren met de macro's knop in de Ontwikkelaar tab, we krijgen onze bedragen:

Bouwstenen van VBA samenvoegen in Excel

Wanneer je voor het eerst naar de bovenstaande VBA-macro kijkt, ziet het er behoorlijk ingewikkeld uit. Maar na het opsplitsen in de samenstellende delen, wordt de logica duidelijk. Zoals elke scripttaal kost het even tijd om te wennen aan de syntaxis van VBA.

Maar met oefenen, bouw je je VBA-vocabulaire op en kun je macro's sneller, nauwkeuriger en met veel meer kracht schrijven dan je ooit zou kunnen opnemen.

Wanneer u vastloopt, is het uitvoeren van een Google-zoekopdracht een snelle manier om uw VBA-vragen te beantwoorden. En de Excel VBA-referentie van Microsoft kan handig zijn als je bereid bent er doorheen te zoeken voor een technisch antwoord.

Als je eenmaal vertrouwd bent met de basis, kun je VBA gaan gebruiken voor dingen zoals het verzenden van e-mails vanuit Excel, het exporteren van Outlook-taken Hoe je Outlook-taken exporteren naar Excel met VBA Hoe je Outlook-taken exporteren naar Excel met VBA Of je nu wel of niet bent een fan van Microsoft, een goed ding dat kan worden gezegd over MS Office-producten, is hoe gemakkelijk het is om elk van hen te integreren met elkaar ... Lees meer, en het weergeven van uw pc-informatie Hoe uw pc-informatie te zien met behulp van een eenvoudig Excel VBA-script Hoe u al uw pc-informatie kunt zien met een eenvoudige Excel VBA Script Excel en 10 minuten werk geven u meer gedetailleerde informatie over uw computer dan u ooit voor mogelijk had gehouden. Klinkt te mooi om waar te zijn? Dat is Windows, als u weet hoe u het moet gebruiken. Lees verder .

.