In Excel VBA kunt u met de instructie IF Then Else controleren op een voorwaarde en dienovereenkomstig een actie uitvoeren.
Dit is in veel situaties zeer waardevol, zoals we in de voorbeelden verderop in deze tutorial zullen zien.
Om u een eenvoudig voorbeeld te geven, stel dat u een lijst met cijfers in Excel hebt en u wilt alle studenten markeren die een A hebben gescoord. Als ik u nu vraag om dit handmatig te doen, controleert u het cijfer van elke student en of het een A, markeert u het, en als dat niet het geval is, laat u het zoals het is.
Dezelfde logica kan in VBA worden gebouwd met behulp van de Als dan anders verklaring ook (en natuurlijk veel meer doen dan alleen cijfers benadrukken).
In deze tutorial laat ik je verschillende manieren zien waarop de 'If Then Else'-constructie kan worden gebruikt in Excel VBA, en enkele praktische voorbeelden in actie.
Maar voordat ik op de details inga, wil ik u de syntaxis geven van de 'IF Then Else'-instructie.
Als je geïnteresseerd bent om VBA op een gemakkelijke manier te leren, bekijk dan mijn Online Excel VBA-training.
Syntaxis - ALS Dan Anders
Hieronder staat de generieke syntaxis van If Then Else-constructie in VBA
IF-voorwaarde Dan true_code [Else false_code]
Of
IF-voorwaarde Dan true_code Anders false_code End IF
Merk op dat het Else-gedeelte van deze verklaring optioneel is.
Als je je nu afvraagt wat het verschil is tussen de twee syntaxis, laat me het dan verduidelijken.
De eerste syntaxis is een eenvoudige eenregelige IF THEN ELSE-instructie waarbij u de END IF-instructie niet hoeft te gebruiken.
In de tweede syntaxis bevindt het true_code-gedeelte zich echter op de tweede regel. Dit is handig wanneer de code die u moet uitvoeren als de IF-voorwaarde waar is, lang is en uit meerdere regels bestaat.
Wanneer u de IF-instructie in meerdere regels splitst, moet u VBA vertellen waar de IF Then-constructie eindigt.
Daarom moet u het End IF-statement gebruiken.
Als u End IF niet gebruikt wanneer dat nodig is, zal VBA u een foutmelding geven - "Block IF without END IF"
Voorbeelden van het gebruik van IF Then-verklaring in VBA
Om u een idee te geven van hoe de IF-THEN-instructie in VBA werkt, zal ik beginnen met enkele basisvoorbeelden (sommige praktische en nuttigere voorbeelden worden later in deze tutorial behandeld).
Stel je hebt de score van een student in cel A1 en je wilt controleren of de student het examen heeft gehaald of niet (de drempel voor voldoende punten is 35).
Dan kun je de volgende code gebruiken:
Sub CheckScore() If Range("A1").Waarde >=35 Dan MsgBox "Pass" End Sub
De bovenstaande code heeft een enkele regel IF-instructie die de waarde in cel A1 controleert.
Als het meer dan 35 is, wordt het bericht "Pass" weergegeven.
Als het minder dan 35 is, gebeurt er niets.
Maar wat als je in beide gevallen een bericht wilt laten zien, of een student is geslaagd of niet is geslaagd voor het examen.
De onderstaande code zou dit doen:
Sub CheckScore() If Range("A1").Waarde >= 35 Dan MsgBox "Pass" Anders MsgBox "Fail" End If End Sub
De bovenstaande code gebruikt zowel de IF- als de ELSE-instructie om twee verschillende voorwaarden uit te voeren. Wanneer de score hoger is dan (of gelijk aan) 35, is de IF-voorwaarde waar en wordt de code eronder uitgevoerd (alles vóór de Else-instructie).
Maar wanneer de IF-voorwaarde ONWAAR is, springt de code naar het Else-gedeelte en voert het codeblok daarin uit.
Merk op dat wanneer we een enkele regel IF Then-instructie gebruiken, we End IF niet hoeven te gebruiken. Maar wanneer we het in meer dan één regel splitsen, moeten we de End If-instructie gebruiken.
Geneste IF Then (meerdere IF Then-instructies)
Tot nu toe hebben we een enkele IF Then-instructie gebruikt.
Als u meerdere voorwaarden moet controleren, kunt u gebruik maken van:
- Meerdere IF-voorwaarden
- Als Dan Anders-verklaring
- ALS Dan Anders Als Anders constructie
Laat me je laten zien hoe deze verschillen en hoe je dit kunt gebruiken in Excel VBA.
Meerdere ALS Dan-verklaringen
Laten we hetzelfde voorbeeld nemen van het gebruik van de score van een leerling.
Als de student minder dan 35 scoort, is het bericht dat moet worden weergegeven 'Fail', als de score hoger is dan of gelijk is aan 35, is het bericht dat moet worden weergegeven 'Pass'.
We kunnen de onderstaande code gebruiken om dit voor elkaar te krijgen:
Sub CheckScore() If Range("A1").Waarde = 35 Dan MsgBox "Pass" End Sub
U kunt meerdere IF Then-instructies gebruiken, zoals hierboven weergegeven. Hoewel dit werkt, is het geen voorbeeld van goede codering (zoals u de onderstaande alternatieven zult zien).
Als u besluit dit te gebruiken, onthoud dan dat deze verklaringen onafhankelijk moeten zijn of elkaar uitsluiten. Het belangrijkste om te weten is dat in de bovenstaande constructie alle IF-statements worden geëvalueerd en degene waarbij de voorwaarde waar is, de code wordt uitgevoerd.
Dus zelfs als de eerste IF-verklaring correct is, wordt de tweede nog steeds geëvalueerd.
ALS Dan Anders Verklaring
Stel dat we deze keer, in plaats van alleen het bericht Pass/Fail weer te geven, nog een voorwaarde hebben.
Als de student minder dan 35 scoort, is het bericht dat moet worden weergegeven 'Fail', als de score hoger is dan of gelijk is aan 35, is het bericht dat moet worden weergegeven 'Pass' en als de score hoger is dan 80, moet het bericht worden weergegeven is 'Pass, met onderscheiding'.
We kunnen de onderstaande code gebruiken om dit voor elkaar te krijgen:
Sub CheckScore() If Range("A1").Waarde < 35 Then MsgBox "Fail" Else If Range ("A1").Waarde < 80 Then MsgBox "Pass" Anders MsgBox "Pass, with Distinction" End If End If End Sub
In de bovenstaande code hebben we met behulp van Else meerdere IF-statements (geneste IF Then) gebruikt.
Er is dus een 'ALS Dan Anders'-constructie binnen een 'ALS Dan Anders'-constructie. Met dit type nesting kunt u controleren op meerdere voorwaarden en het relevante codeblok uitvoeren.
ALS Dan AndersAls Anders Verklaring
De bovenstaande code (die we in de vorige sectie zagen) kan verder worden geoptimaliseerd met behulp van de ElseIf-instructie.
Dit is wat we proberen te doen - Als de student minder dan 35 scoort, is het bericht dat moet worden weergegeven 'Fail', als de score hoger is dan of gelijk is aan 35, is het bericht dat moet worden weergegeven 'Pass' en als de score is meer dan 80, het bericht dat moet worden weergegeven is 'Pass, with Distinction'.
Sub CheckScore() If Range("A1").Waarde < 35 Then MsgBox "Fail" ElseIf Range("A1").Waarde < 80 Then MsgBox "Pass" Anders MsgBox "Pass, with Distinction" End If End Sub
De bovenstaande code gebruikt ElseIf, waarmee we alle voorwaarden binnen één enkele IF Then-verklaring kunnen houden.
AND en OR gebruiken in IF Then Else
Tot nu toe hebben we in deze zelfstudie slechts op één voorwaarde tegelijk gecontroleerd.
Als u echter meerdere afhankelijke voorwaarden heeft, kunt u de AND- of OR-instructie gebruiken met de IF-voorwaarden.
Hieronder vindt u de syntaxis van het gebruik van de EN/OF-voorwaarde met de IF Then-instructie.
IF Conditie1 AND Conditie2 Dan true_code Anders false_code End IF
In de bovenstaande code wordt de true_code alleen uitgevoerd wanneer aan zowel Voorwaarde1 als Voorwaarde2 wordt voldaan. Zelfs als een van de voorwaarden onwaar is, wordt de valse_code uitgevoerd.
Met OR zal het, zelfs als een van de voorwaarden waar is, de true_code uitvoeren. Alleen wanneer alle voorwaarden onwaar zijn, voert het de valse_code uit.
Laten we nu eens kijken hoe de AND- en OR-statements werken met de IF Then Else-constructie.
Stel dat u de scores voor twee vakken hebt in plaats van één, en u wilt controleren op de volgende voorwaarden:
- Mislukking - Wanneer de score lager is dan 35 in een van de vakken.
- Doorgang - Wanneer de score meer dan of gelijk is aan 35, maar minder dan 80 in beide vakken.
- Passeren met onderscheiding - Wanneer de score hoger is dan 35 in beide vakken en hoger dan of gelijk aan 80 in een of beide vakken.
Hier is de code die dit zal doen:
Sub CheckScore() If Range("A1").Waarde <35 Or Range("B1").Waarde <35 Dan MsgBox "Fail" ElseIf Range("A1").Waarde <80 And Range("B1"). Waarde < 80 Dan MsgBox "Pass" Anders MsgBox "Pass, with Distinction" End If End Sub
De bovenstaande code gebruikt zowel OR- als AND-instructies.
Je kunt dezelfde code ook met een kleine wijziging schrijven (met OR in plaats van AND).
Sub CheckScore() If Range("A1").Waarde < 35 Or Range("B1").Waarde 80 Or Range("B1").Waarde > 80 Dan MsgBox "Pass, with Distinction" Anders MsgBox "Pass" End Als Einde Sub
Beide bovenstaande VBA-codes geven hetzelfde resultaat. Persoonlijk geef ik de voorkeur aan de eerste omdat deze een logische stroom heeft voor het controleren van de scores (maar dat ben ik alleen).
Gebruik Niet gelijk aan in Als Dan
In alle bovenstaande voorbeelden hebben we de voorwaarden gebruikt die controleren of een waarde gelijk is aan een opgegeven waarde of niet.
U kunt vergelijkbare codes ook gebruiken bij het controleren wanneer de waarde niet gelijk is aan een opgegeven waarde in de VBA-code. Niet gelijk aan vertegenwoordigd door de Excel VBA.
Bekijk voorbeeld 1 hieronder voor een praktisch voorbeeld van het gebruik van .
Als dan anders gebruiken met lussen in VBA
Tot nu toe hebben we enkele voorbeelden doorgenomen die goed zijn om te begrijpen hoe de 'IF-THEN'-statements in VBA werken, maar die in de praktijk niet nuttig zijn.
Als ik studenten moet beoordelen, kan ik dat gemakkelijk doen met Excel-functies.
Laten we dus eens kijken naar enkele nuttige en praktische voorbeelden die u kunnen helpen bepaalde zaken te automatiseren en efficiënter te maken.
Voorbeeld 1 - Bewaar en sluit alle werkmappen behalve de actieve werkmap
Als u veel werkmappen hebt geopend en u wilt snel alles sluiten, behalve de actieve werkmap, kunt u de onderstaande code gebruiken,
Sub SaveCloseAllWorkbooks() Dim wb As Workbook For Each wb In Workbooks Bij fout hervatten volgende If wb.Name ActiveWorkbook.Name Dan wb.Save wb.Close End If Next wb End Sub
De bovenstaande code zou alle werkmappen opslaan en sluiten (behalve de actieve).
Het gebruikt de For Next-lus om door de verzameling van alle open werkmappen te gaan en controleert de naam met behulp van de IF-voorwaarde.
Als de naam niet dezelfde is als die van de actieve werkmap, wordt deze opgeslagen en gesloten.
Als er een VBA-code in een van de werkmappen staat en u deze niet hebt opgeslagen als .xls of .xlsm, ziet u een waarschuwing (omdat de vba-codes verloren gaan wanneer u deze opslaat in .xlsx-indeling).
Voorbeeld 2 - Markeer cellen met negatieve waarden
Stel dat je een kolom vol getallen hebt en je wilt snel alle cellen met negatieve waarden in het rood markeren, dan kan dat met onderstaande code.
Sub HighlightNegativeCells() Dim Cll As Range voor elke Cll In Selectie Als Cll.Waarde < 0 Dan Cll.Interior.Color = vbRed Cll.Font.Color = vbWhite End If Next Cll End Sub
De bovenstaande code gebruikt de For Each-lus en controleert elke cel in de selectie die u hebt gemaakt. Als de cel een waarde heeft die negatief is, wordt deze rood gemarkeerd met een witte letterkleur.
Voorbeeld 3 - Verberg het hele werkblad behalve het huidige werkblad
Als u snel alle werkbladen wilt verbergen, behalve de actieve, kunt u de onderstaande code gebruiken:
Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name ActiveSheet.Name Dan ws.Visible = xlSheetHidden Volgende ws End Sub
De bovenstaande code gebruikt de For Each-lus om een verzameling werkbladen te doorlopen. Het controleert de naam van elk werkblad en verbergt het als het niet het actieve werkblad is.
Voorbeeld 4 - Extraheer het numerieke gedeelte uit een alfanumerieke tekenreeks
Als je alfanumerieke tekenreeksen in cellen hebt en je wilt het numerieke deel eruit halen, dan kun je dat doen met de onderstaande code:
Functie GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Resultaat Eindfunctie
Deze code maakt een aangepaste functie in Excel die in het werkblad kan worden gebruikt (net als een gewone functie).
Waar plaats je de VBA-code?
Vraagt u zich af waar de VBA-code naartoe gaat in uw Excel-werkmap?
Excel heeft een VBA-backend, de VB-editor. U moet de code kopiëren en plakken in het codevenster van de VB Editor-module.
Hier zijn de stappen om dit te doen:
- Ga naar het tabblad Ontwikkelaars.
- Klik op de Visual Basic-optie. Dit opent de VB-editor in de backend.
- Klik in het deelvenster Projectverkenner in de VB-editor met de rechtermuisknop op een object voor de werkmap waarin u de code wilt invoegen. Als u de Projectverkenner niet ziet, gaat u naar het tabblad Weergave en klikt u op Projectverkenner.
- Ga naar Invoegen en klik op Module. Hiermee wordt een moduleobject voor uw werkmap ingevoegd.
- Kopieer en plak de code in het modulevenster.
