De macrorecorder van Excel heeft veel kracht, maar heeft zijn beperkingen. Zoals besproken in een ander artikel, neemt de macrorecorder vaak onnodige code op en kan hij geen zaken als logica of interacties met andere programma's opnemen. Het kan ook moeilijk zijn om te gebruiken voor langere macro's - het kan zijn dat u van tevoren uw acties in een storyboard plaatst om te voorkomen dat u kostbare fouten maakt.
Dit artikel is bedoeld om u te helpen beginnen met het vanaf het begin coderen van macro's in VBA. Je leert waar macro's worden opgeslagen, schrijft een basismacro en leert de basisprincipes van programmeren in VBA met behulp van variabelen, logica en lussen.
Beginnen
VBA en de Visual Basic Editor
VBA, of Visual Basic for Applications, is de taal waarin macro's worden geschreven. Alle macro's worden opgeslagen als VBA-code, of ze nu met de hand zijn gecodeerd of met de macrorecorder zijn gemaakt.
U hebt toegang tot alle VBA-code in een werkmap met behulp van de Visual Basic Editor. Dit is een speciale teksteditor en debugger die is ingebouwd in alle Office-apps, inclusief Excel. Meestal opent u deze editor met de ALT+F11 sneltoets in Excel, maar u kunt deze ook openen vanuit Excel's Ontwikkelaar tabblad als u het hebt ingeschakeld.
De projectverkenner
De Projectverkenner is een venster in de VB-editor waarin u alle items ziet die VBA-code kunnen bevatten. Als u dit venster niet ziet, drukt u op F5 om het te laten verschijnen of selecteer Projectverkenner van de Weergave menu.
Als u dubbelklikt op een item in de Projectverkenner, wordt de code voor dat item weergegeven. Er zijn verschillende soorten items die in de Projectverkenner kunnen verschijnen:
- Werkmappen
- Werkbladen
- Gebruikersformulieren
- Klasmodules
- Modules (macro's worden in deze items opgeslagen)
Hoewel al deze itemtypen VBA-code kunnen bevatten, is het het beste om macro's in Modules te coderen.
Je eerste macro maken
De lijst met macro's gebruiken
De lijst met macro's toont u alle macro's in uw werkmap. Vanuit deze lijst kunt u een bestaande macro bewerken of een nieuwe maken.
Een nieuwe macro maken met behulp van de lijst Macro's:
- Selecteer het tabblad Ontwikkelaars en klik op Macro's (of druk op ALT+F8)
- Typ een nieuwe naam voor uw macro en klik vervolgens op "Maken"
Nadat u op "Maken" hebt geklikt, verschijnt de VB-editor met de nieuw gemaakte macro. Excel maakt indien nodig een nieuwe module voor de macro.
Handmatig in de VB Editor
U kunt handmatig een nieuwe macro toevoegen zonder de lijst Macro's. Dit is de betere optie als u de module wilt specificeren waarin de macro is opgeslagen.
Handmatig een macro toevoegen:
- Open de VB-editor (ALT+F11)
- Of:
- Voeg een nieuwe module toe door te klikken op Invoegen > Module in het menu (de module wordt automatisch geopend)
-
- OF, dubbelklik op een bestaande module in de Projectverkenner om deze te openen
- Typ in de module de code voor uw nieuwe macro
Sub MijnMacro() Einde Sub
Deze twee regels geven het begin en einde van een macro met de naam "MyMacro" aan (let op de haakjes, die vereist zijn). Dit verschijnt in het dialoogvenster "Macro's bekijken" in Excel en kan worden toegewezen aan een knop (ook al doet het nog niets).
Voeg wat code toe aan de macro
Laten we nu wat code toevoegen tussen de regels "Sub" en "End Sub" om deze macro daadwerkelijk iets te laten doen:
Sub MyMacro () Bereik ("A1"). Waarde = "Hallo wereld!" Einde sub
Basiscodestructuren
Het bereikobject
Excel VBA gebruikt het bereikobject om cellen op een werkblad weer te geven. In het bovenstaande voorbeeld wordt een Range-object gemaakt met de code Bereik ("A1") om toegang te krijgen tot de waarde van cel A1.
Bereikobjecten worden voornamelijk gebruikt om celwaarden in te stellen:
Bereik ("A1"). Waarde = 1
Bereik ("A1"). Waarde = "Eerste cel"
Merk op dat wanneer u celwaarden als getallen definieert, u alleen het getal invoert, maar wanneer u tekst invoert, moet u de tekst tussen aanhalingstekens plaatsen.
Bereiken kunnen ook worden gebruikt om toegang te krijgen tot veel eigenschappen van cellen, zoals hun lettertype, randen, formules en meer.
U kunt bijvoorbeeld het lettertype van een cel als volgt op Vet zetten:
Bereik ("A1"). Lettertype. Vet = True
U kunt ook de formule van een cel instellen:
Bereik ("A1"). Formule = "= Som (A2: A10)"
In Excel kunt u een blok cellen selecteren met de cursor (bijvoorbeeld van A1 tot D10) en ze allemaal vet maken. Bereikobjecten hebben als volgt toegang tot celblokken:
Bereik ("A1: D10"). Lettertype. Vet = True
U kunt ook naar meerdere cellen/blokken tegelijk verwijzen:
Bereik ("A1: D10, A12: D12, G1"). Lettertype. Vet = True
De indeling hiervoor is dezelfde als de indeling die u zou gebruiken bij het selecteren van cellen voor de SUM()-formule in Excel. Elk blok wordt gescheiden door een komma en blokken worden aangegeven door de cellen linksboven en rechtsonder, gescheiden door een dubbele punt.
Ten slotte hebben Range-objecten ingebouwde methoden om algemene bewerkingen op een werkblad uit te voeren. U wilt bijvoorbeeld enkele gegevens van de ene plaats naar de andere kopiëren. Hier is een voorbeeld:
Bereik ("A1: D10"). Bereik kopiëren ("F1").PlakkenSpecial xlPasteValues Range ("F1").PlakkenSpecial xlPasteFormats
Dit kopieert cellen A1:D10 naar het klembord en voert vervolgens een PasteSpecial() uit, beginnend in cel C1 - net zoals u dat handmatig zou doen in Excel. Merk op dat dit voorbeeld laat zien hoe je PasteSpecial() gebruikt om alleen Waarden en Formaten te plakken - er zijn parameters voor alle opties die je zou zien in het dialoogvenster Plakken speciaal.
Hier is een voorbeeld van het plakken van "Alle" naar een ander werkblad:
Bereik ("A1: D10"). Bladen kopiëren ("Blad2"). Bereik ("A1"). Plakken Speciaal xlPlakkenAlles
Als verklaringen
Met een Als verklaring, kunt u een gedeelte van de code alleen laten uitvoeren "als" een bepaalde verklaring waar is.
U wilt bijvoorbeeld een cel vet maken en deze rood kleuren, maar alleen "als" de waarde in de cel kleiner is dan 100.
Als Bereik ("A4"). Waarde < 100 Dan Bereik ("A4"). Lettertype Vet = True Range ("A4"). Interieur. Kleur = vbRed End If
De juiste structuur van een If-statement is als volgt (vierkante haken geven optionele componenten aan):
Als dan
[Anders Als Dan]
[Anders]
Stop als
Je kunt er zoveel toevoegen ElseIf blokken zoals je wilt om meerdere voorwaarden te testen. Je kunt ook een toevoegen Anders blok dat alleen wordt uitgevoerd als aan geen van de andere voorwaarden in de If-instructie is voldaan.
Hier is nog een voorbeeld op basis van het vorige, waarbij de cel op verschillende manieren is opgemaakt, afhankelijk van de waarde:
If Range("A4").Waarde < 100 Then Range("A4").Font.Bold = True Range("A4").Interior.Color = vbRed ElseIf Range("A4").Waarde < 200 Then Range( "A4").Font.Bold = False Range("A4").Interior.Color = vbYellow Else Range("A4").Font.Bold = False Range("A4").Interior.Color = vbGreen End If
In het bovenstaande voorbeeld is de cel vetgedrukt in de ElseIf-blokken waar de waarde niet lager is dan 100. U kunt nest If-instructies om dubbele code te voorkomen, zoals deze:
If Range("A4").Waarde < 100 Dan Range("A4").Font.Bold = True Range("A4").Interior.Color = vbRed Else Range("A4").Font.Bold = False ' het lettertype slechts één keer vrijgeven If Range("A4").Waarde < 200 Then Range("A4").Interior.Color = vbYellow Else Range("A4").Interior.Color = vbGreen End If End If
Variabelen
EEN Variabele is een stukje geheugen dat wordt gebruikt om tijdelijke informatie op te slaan terwijl een macro wordt uitgevoerd. Ze worden vaak gebruikt in lussen als iterators, of om het resultaat van een bewerking vast te houden die u meerdere keren in een macro wilt gebruiken.
Hier is een voorbeeld van een variabele en hoe u deze zou kunnen gebruiken:
Sub ExtractSerialNumber() Dim strSerial As String ' dit is de variabele declaratie ' 'As String' betekent dat deze variabele bedoeld is om tekst te bevatten ' een nep serienummer instellen: Range("A4").Value = "serial# 804567-88 ” ' ontleden het serienummer uit cel A4 en wijs het toe aan de variabele strSerial = Mid(Range(“A4”).Value, 9) ' gebruik nu de variabele twee keer, in plaats van het serienummer twee keer te moeten ontleden Range(“ B4"). Waarde = strSerial MsgBox strSerial End Sub
In dit basisvoorbeeld wordt de variabele 'strSerial' gebruikt om het serienummer uit cel A4 te extraheren met behulp van de functie Mid() en wordt vervolgens op twee andere plaatsen gebruikt.
De standaard manier om verklaren een variabele is als volgt:
dim welke naam dan ook [Als type]
- welke naam dan ook is de naam die u besluit aan uw variabele te geven
- type is het gegevenstype van de variabele
De “[As type]”-gedeelte kan worden weggelaten - als dat het geval is, wordt de variabele gedeclareerd als een Variant-type, dat alle soorten gegevens kan bevatten. Hoewel ze volkomen geldig zijn, moeten varianttypen worden vermeden, omdat ze tot onverwachte resultaten kunnen leiden als u niet voorzichtig bent.
Er zijn reglement voor variabelenamen. Ze moeten beginnen met een letter of een onderstrepingsteken, mogen geen spaties, punten, komma's, aanhalingstekens of de tekens "! @ & $ #”.
Hier zijn enkele voorbeelden van variabele declaraties:
Dim strFilename As String ' goede naamstijl - beschrijvend en gebruikt voorvoegsel Dim i As Long ' slechte naamstijl - alleen acceptabel voor sommige iterators Dim SalePrice As Double ' oke naamstijl - beschrijvend, maar gebruikt geen voorvoegsel Dim iCounter ' oke naam - niet te beschrijvend, gebruikt voorvoegsel, geen gegevenstype
Al deze voorbeelden gebruiken iets andere naamgevingsschema's, maar ze zijn allemaal geldig. Het is geen slecht idee om de naam van een variabele te laten voorafgaan door een korte vorm van het gegevenstype (zoals in sommige van deze voorbeelden), omdat het uw code in één oogopslag leesbaarder maakt.
VBA bevat veel basis gegevenstypen. De meest populaire zijn onder meer:
- Snaar (gebruikt om tekstgegevens te bewaren)
- Lang (gebruikt om hele getallen te bevatten, d.w.z. geen decimalen)
- Dubbele (gebruikt om getallen met drijvende komma vast te houden, d.w.z. decimalen)
Een volledige lijst van intrinsieke VBA-gegevenstypen is hier te vinden: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary
Variabelen bereikobject
Het is ook mogelijk om variabelen te maken die verwijzen naar Range Objects. Dit is handig als u op verschillende plaatsen in uw code naar een specifiek bereik wilt verwijzen - op die manier hoeft u het bereik slechts op één plaats te wijzigen als u het moet wijzigen.
Wanneer u een Range-objectvariabele maakt, moet u deze "instellen" op een instantie van een Range. Bijvoorbeeld:
Dim rMyRange As Range Stel rMyRange = Range ("A1:A10;D1:J10") in
Als u de instructie "Set" weglaat bij het toewijzen van een Range-variabele, resulteert dit in een fout.
lussen
Loops zijn blokken die de code erin een bepaald aantal keren herhalen. Ze zijn handig om de hoeveelheid code die u moet schrijven te verminderen en stellen u in staat om één stuk code te schrijven dat dezelfde acties uitvoert op veel verschillende gerelateerde items.
Voor de volgende
EEN Voor de volgende blok is een lus die een bepaald aantal keren wordt herhaald. Het gebruikt een variabele als een iterator om te tellen hoe vaak het is uitgevoerd, en deze iteratorvariabele kan binnen de lus worden gebruikt. Dit maakt For-Next-lussen erg handig voor het doorlopen van cellen of arrays.
Hier is een voorbeeld dat door cellen loopt in rijen 1 tot 100, kolom 1, en hun waarden instelt op de waarde van de iteratorvariabele:
Dim i As Long For i = 1 To 100 Cells (i, 1). Waarde = i Volgende i
De regel "For i = 1 To 100" betekent dat de lus begint bij 1 en eindigt na 100. U kunt elk gewenst begin- en eindnummer instellen; je kunt ook variabelen gebruiken voor deze getallen.
For-Next-lussen tellen standaard met 1. Als u met een ander nummer wilt tellen, kunt u de lus schrijven met een expliciete Stap clausule:
Voor i = 5 tot 100 Stap 5
Deze lus begint bij 5 en voegt vervolgens 5 toe aan 'i' elke keer dat de lus wordt herhaald (dus 'i' is 10 bij de tweede herhaling, 15 bij de derde, enzovoort).
Gebruik makend van Stap, kunt u een lus ook achteruit laten tellen:
Voor i = 100 Naar 1 Stap -1
Je kan ook nest For-Next-lussen. Elk blok heeft zijn eigen variabele nodig om mee te tellen, maar je kunt die variabelen overal gebruiken. Hier is een voorbeeld van hoe dat handig is in Excel VBA:
Dim i As Long, j As Long For i = 1 To 100 For j = 1 To 100 Cells(i, j). Waarde = i * j Next j Next i
Hiermee kunt u zowel rijen als kolommen doorlopen.
WAARSCHUWING: hoewel het is toegestaan, moet je NOOIT de iteratorvariabele in een For-Next-blok WIJZIGEN, omdat het die iterator gebruikt om de lus bij te houden. Het wijzigen van de iterator kan een oneindige lus veroorzaken en uw macro laten hangen. Bijvoorbeeld:
Voor i = 1 Tot 100 i = 1 Volgende i
In deze lus komt 'I' nooit verder dan 2 voordat hij wordt gereset naar 1, en de lus zal voor altijd worden herhaald.
Voor-elk
Voor-elk blokken lijken erg op For-Next-blokken, behalve dat ze geen teller gebruiken om aan te geven hoe vaak ze een lus maken. In plaats daarvan neemt een For-Each-blok een 'verzameling' objecten (zoals een reeks cellen) en wordt deze zo vaak uitgevoerd als er objecten in die verzameling zijn.
Hier is een voorbeeld:
Dim r As Range For Each r In Range ("A15:J54") Als r.Waarde > 0 Dan r.Font.Bold = True End If Next r
Let op het gebruik van de Range-objectvariabele 'r'. Dit is de iteratorvariabele die wordt gebruikt in de For-Each-lus - elke keer dat de lus doorloopt, krijgt 'r' een verwijzing naar de volgende cel in het bereik.
Een voordeel van het gebruik van For-Each-lussen in Excel VBA is dat u door alle cellen in een bereik kunt lopen zonder lussen te nesten. Dit kan handig zijn als u alle cellen in een complex bereik moet doorlopen, zoals Bereik ("A1:D12,J13, M1:Y12").
Een nadeel van For-Each-loops is dat je geen controle hebt over de volgorde waarin cellen worden verwerkt. Hoewel in praktijk Excel doorloopt de cellen in volgorde, in theorie het zou de cellen in een volledig willekeurige volgorde kunnen verwerken. Als u cellen in een bepaalde volgorde moet verwerken, moet u in plaats daarvan For-Next-lussen gebruiken.
Do-Loop
Terwijl For-Next-blokken tellers gebruiken om te weten wanneer ze moeten stoppen, Do-Loop blokken lopen totdat aan een voorwaarde is voldaan. Om dit te doen, gebruik je een Tot clausule aan het begin of het einde van het blok, die de voorwaarde test en ervoor zorgt dat de lus stopt wanneer aan die voorwaarde wordt voldaan.
Voorbeeld:
Dim str As String str = "Buffalo" Do Until str = "Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo" str = str & " " & "Buffalo" Loop Range ("A1"). Waarde = str
In deze lus wordt "Buffalo" elke keer door de lus samengevoegd tot 'str' totdat het overeenkomt met de verwachte zin. In dit geval wordt de test uitgevoerd aan het begin van de lus - als 'str' al de verwachte zin was (wat niet het geval is omdat we het niet op die manier begonnen, maar als) de lus niet eens zou lopen .
U kunt de lus minstens één keer laten lopen door de clausule Tot naar het einde te verplaatsen, zoals dit:
Doe str = str & " " & "Buffalo" Loop Tot str = “Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo Buffalo”
U kunt elke versie gebruiken die zinvol is in uw macro.
WAARSCHUWING: je kunt een oneindige lus veroorzaken met een Do-Loop-blok als nooit aan de voorwaarde tot wordt voldaan. Schrijf uw code altijd zo dat aan de voorwaarde tot zeker wordt voldaan wanneer u dit type lus gebruikt.
Wat is het volgende?
Als je eenmaal de basis hebt begrepen, waarom probeer je dan niet wat meer geavanceerde technieken te leren? Onze tutorial op https://easyexcel.net/excel/learn-vba-tutorial/ zal voortbouwen op alles wat je hier hebt geleerd en je vaardigheden uitbreiden met Events, UserForms, code-optimalisatie en nog veel meer!
