Er vinden voortdurend gebeurtenissen plaats wanneer een gebruiker een Excel-werkmap opent en verschillende acties begint uit te voeren, zoals het invoeren van gegevens in cellen of het verplaatsen tussen bladen
Binnen de Visual Basic Editor (ALT+F11) zijn al subroutines ingesteld die kunnen worden geactiveerd wanneer de gebruiker iets doet, b.v. gegevens invoeren in een cel. De subroutine biedt geen actiecode, alleen een 'Sub'-instructie en een 'End Sub'-instructie zonder code ertussen. Ze zijn in feite inactief, dus er gebeurt niets totdat u een code invoert.
Hier is een voorbeeld gebaseerd op de gebeurtenis 'Wijzigen' in een werkblad:
Als VBA-programmeur kun je code toevoegen om bepaalde dingen te laten gebeuren wanneer de gebruiker een specifieke actie onderneemt. Dit geeft u de kans om de gebruiker te controleren en te voorkomen dat ze acties ondernemen die u niet wilt dat ze doen en die uw werkmap kunnen beschadigen. U wilt bijvoorbeeld dat ze hun eigen individuele exemplaar van de werkmap onder een andere naam opslaan, zodat ze geen invloed hebben op het origineel, dat door een aantal gebruikers kan worden gebruikt.
Als ze de werkmap sluiten, wordt hen automatisch gevraagd om hun wijzigingen op te slaan. De werkmap heeft echter een 'BeforeClose'-gebeurtenis en u kunt code invoeren om te voorkomen dat de werkmap wordt gesloten en een 'Save'-gebeurtenis activeert. Je kunt dan een knop aan het werkblad zelf toevoegen en je eigen 'Opslaan'-routine erop zetten. U kunt de routine 'Opslaan' ook uitschakelen met de gebeurtenis 'BeforeSave'
Een goed begrip van hoe gebeurtenissen werken, is absoluut essentieel voor een VBA-programmeur.
Soorten evenementen
Werkboek Evenementen - deze gebeurtenissen worden geactiveerd op basis van wat de gebruiker met de werkmap zelf doet. Ze omvatten gebruikersacties zoals het openen van de werkmap, het sluiten van de werkmap, het opslaan van de werkmap, het toevoegen of verwijderen van een werkblad
Werkblad Evenementen - deze gebeurtenissen worden geactiveerd door een gebruiker die acties onderneemt op een specifiek werkblad. Elk werkblad in de werkmap heeft een individuele codemodule, die verschillende gebeurtenissen specifiek voor dat werkblad bevat (niet voor alle werkbladen). Deze omvatten gebruikersacties zoals het wijzigen van de inhoud van een cel, dubbelklikken op een cel of rechtsklikken op een cel.
Actieve X-besturingsgebeurtenissen - Active X-besturingselementen kunnen aan een werkblad worden toegevoegd met behulp van het pictogram 'Invoegen' op het tabblad 'Ontwikkelaar' in het Excel-lint. Dit zijn vaak knopbedieningen waarmee de gebruiker verschillende acties kan ondernemen onder controle van uw code, maar het kunnen ook objecten zijn zoals vervolgkeuzelijsten. Het gebruik van Active X-besturingselementen in tegenstelling tot Form-besturingselementen op het werkblad geeft een heel scala aan programmeerbaarheid. Active X-besturingselementen bieden u veel meer flexibiliteit vanuit het oogpunt van programmeren dan het gebruik van formulierbesturingselementen in een werkblad.
U kunt bijvoorbeeld twee vervolgkeuzemenu's op uw werkblad hebben. U wilt dat de beschikbare lijst in de tweede vervolgkeuzelijst is gebaseerd op wat de gebruiker in de eerste vervolgkeuzelijst heeft gekozen. Met behulp van de gebeurtenis 'Wijzigen' in de eerste vervolgkeuzelijst, kunt u code maken om te lezen wat de gebruiker heeft geselecteerd en vervolgens de tweede vervolgkeuzelijst bijwerken. U kunt de tweede vervolgkeuzelijst ook deactiveren totdat de gebruiker een keuze heeft gemaakt in de eerste vervolgkeuzelijst
UserForm-evenementen - U kunt een professioneel ogend formulier invoegen en ontwerpen om als pop-up te gebruiken. Alle besturingselementen die u op uw formulier plaatst, zijn Active X-besturingselementen en ze hebben dezelfde gebeurtenissen als de Active X-besturingselementen die u op een werkblad zou kunnen plaatsen
Kaartgebeurtenissen - Deze gebeurtenissen hebben alleen betrekking op een grafiekblad en niet op een grafiek die als onderdeel van een werkblad verschijnt. Deze gebeurtenissen omvatten het wijzigen van het formaat van de grafiek of het selecteren van de grafiek.
Toepassingsgebeurtenissen - Deze gebruiken het Application-object in VBA. Voorbeelden zouden het mogelijk maken code te activeren wanneer een bepaalde toets wordt ingedrukt of wanneer een bepaalde tijd is bereikt. U kunt een situatie programmeren waarin de werkmap 24/7 open blijft staan en 's nachts op een vooraf bepaald tijdstip gegevens van een externe bron worden geïmporteerd.
Gevaren van het gebruik van code in evenementen
Wanneer u code schrijft om iets te doen wanneer de gebruiker een bepaalde actie onderneemt, moet u er rekening mee houden dat uw code andere gebeurtenissen kan activeren, waardoor uw code in een continue lus kan raken.
Stel bijvoorbeeld dat u de gebeurtenis 'Wijzigen' op een werkblad gebruikt, zodat wanneer de gebruiker een waarde in een cel invoert, een berekening op basis van die cel in de cel direct rechts ervan wordt geplaatst.
Het probleem hier is dat het plaatsen van de berekende waarde in de cel een andere 'Change'-gebeurtenis activeert, die op zijn beurt weer een andere 'Change'-gebeurtenis activeert, enzovoort totdat uw code geen kolommen meer heeft om te gebruiken en overgeeft een foutmelding.
Je moet goed nadenken bij het schrijven van de code voor het evenement om ervoor te zorgen dat andere evenementen niet per ongeluk worden geactiveerd
Evenementen uitschakelen
U kunt code gebruiken om gebeurtenissen uit te schakelen om dit probleem te omzeilen. Wat u moet doen, is code opnemen om gebeurtenissen uit te schakelen terwijl uw gebeurteniscode actief is en vervolgens gebeurtenissen aan het einde van de code opnieuw in te schakelen. Hier is een voorbeeld van hoe het moet:
| 1234 | Sub DisableEvents()Application.EnableEvents = FalseApplication.EnableEvents = TrueEinde sub |
Houd er rekening mee dat hierdoor alle gebeurtenissen in de Excel-toepassing worden uitgeschakeld, dus dit zou ook andere functies binnen Excel beïnvloeden. Als je dit om wat voor reden dan ook gebruikt, zorg er dan voor dat evenementen daarna weer worden ingeschakeld.
Belang van parameters in gebeurtenissen
Gebeurtenissen hebben meestal parameters die u kunt gebruiken om meer te weten te komen over wat de gebruiker doet en de cellocatie waarin deze zich bevindt.
De gebeurtenis Werkblad wijzigen ziet er bijvoorbeeld als volgt uit:
| 1 | Private Sub Worksheet_Change (ByVal-doel als bereik) |
Door het bereikobject te gebruiken, kunt u de celrij / kolomcoördinaten achterhalen waarin de gebruiker zich daadwerkelijk bevindt.
| 1234 | Private Sub Worksheet_Change (ByVal-doel als bereik)MsgBox Target.ColumnMsgBox Target.RijEinde sub |
Als u wilt dat uw code alleen werkt op een bepaald kolom- of rijnummer, voegt u een voorwaarde toe die de subroutine verlaat als de kolom niet de vereiste kolom is.
| 123 | Private Sub Worksheet_Change (ByVal-doel als bereik)Als Target.Column 2 Sub afsluitenEinde sub |
Dit omzeilt het probleem dat uw code meerdere gebeurtenissen activeert, omdat het alleen werkt als de gebruiker een cel in kolom 2 (kolom B) heeft gewijzigd.
Voorbeelden van werkboekgebeurtenissen (niet uitputtend)
De werkmapgebeurtenissen zijn te vinden onder het object 'ThisWorkbook' in de VBE-projectverkenner. U moet 'Werkmap' selecteren in de eerste vervolgkeuzelijst in het codevenster en vervolgens toont de tweede vervolgkeuzelijst u alle beschikbare evenementen
Werkboek open evenement
Deze gebeurtenis wordt geactiveerd wanneer de werkmap door een gebruiker wordt geopend. Je zou het kunnen gebruiken om een welkomstbericht naar een gebruiker te sturen door hun gebruikersnaam vast te leggen
| 123 | Privé subwerkboek_Open()MsgBox "Welkom" & Toepassing.GebruikersnaamEinde sub |
U kunt ook hun gebruikersnaam vergelijken met een lijst op een verborgen blad om te zien of ze geautoriseerd zijn om toegang te krijgen tot de werkmap. Als ze geen geautoriseerde gebruiker zijn, kunt u een bericht weergeven en de werkmap sluiten zodat ze deze niet kunnen gebruiken.
Werkmap Nieuwe bladgebeurtenis
Deze gebeurtenis wordt geactiveerd wanneer een gebruiker een nieuw blad aan de werkmap toevoegt
Je zou deze code kunnen gebruiken om jezelf alleen toe te staan een nieuw blad toe te voegen, in plaats van dat verschillende gebruikers allemaal bladen toevoegen en een puinhoop van de werkmap maken
| 1234567 | Private Sub Workbook_NewSheet (ByVal Sh As Object)Application.DisplayAlerts = FalseAls Application.UserName "Richard" DanSh.VerwijderenStop alsApplication.DisplayAlerts = TrueEinde sub |
Houd er rekening mee dat u de waarschuwingen moet uitschakelen, omdat er een gebruikerswaarschuwing verschijnt wanneer het blad wordt verwijderd, waardoor de gebruiker uw code kan omzeilen. Zorg ervoor dat u de waarschuwingen daarna weer inschakelt!
Moe van het zoeken naar voorbeelden van VBA-codes? Probeer AutoMacro!
Werkmap vóór gebeurtenis opslaan
Deze gebeurtenis wordt geactiveerd wanneer de gebruiker op het pictogram 'Opslaan' klikt, maar voordat het 'Opslaan' daadwerkelijk plaatsvindt
Zoals eerder beschreven, wilt u wellicht voorkomen dat gebruikers hun wijzigingen in de oorspronkelijke werkmap opslaan en hen dwingen een nieuwe versie te maken met een knop op het werkblad. Het enige dat u hoeft te doen, is de parameter 'Annuleren' wijzigen in True, en de werkmap kan nooit worden opgeslagen met de conventionele methode.
| 123 | Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean)Annuleren = WaarEinde sub |
Werkboek vóór sluitingsgebeurtenis
U kunt deze gebeurtenis gebruiken om te voorkomen dat gebruikers de werkmap sluiten en hen opnieuw dwingen om af te sluiten via een werkbladknop. Nogmaals, u stelt de parameter 'Annuleren' in op 'True'. De rode X in de rechterbovenhoek van het Excel-venster werkt niet meer.
| 123 | Private Sub Workbook_BeforeClose (Annuleren als Boolean)Annuleren = WaarEinde sub |
Voorbeelden van werkbladevenementen (niet uitputtend)
De werkbladgebeurtenissen zijn te vinden onder het specifieke bladnaamobject in de VBE-projectverkenner. U moet 'Werkblad' selecteren in de eerste vervolgkeuzelijst in het codevenster en vervolgens toont de tweede vervolgkeuzelijst u alle beschikbare evenementen
Werkblad Wijzigingsgebeurtenis
Deze gebeurtenis wordt geactiveerd wanneer een gebruiker een wijziging aanbrengt in een werkblad, zoals het invoeren van een nieuwe waarde in een cel
U kunt deze gebeurtenis gebruiken om een extra waarde of opmerking naast de gewijzigde cel te plaatsen, maar zoals eerder besproken, wilt u geen lus van gebeurtenissen veroorzaken.
| 12345 | Private Sub Worksheet_Change (ByVal-doel als bereik)Als Target.Column 2 Sub afsluitenActiveSheet.Cells(Doel.Rij, Doel.Kolom + 1). Waarde = _ActiveSheet.Cells(Doel.Rij, Doel.Kolom). Waarde * 1.1Einde sub |
In dit voorbeeld werkt de code alleen als de waarde wordt ingevoerd in kolom B (kolom 2). Als dit waar is, wordt 10% toegevoegd aan het getal en in de volgende beschikbare cel geplaatst
Werkblad vóór dubbelklikgebeurtenis
Deze gebeurtenis activeert de code als een gebruiker dubbelklikt op een cel. Dit kan erg handig zijn voor financiële rapporten zoals een balans of winst- en verliesrekening, waar cijfers waarschijnlijk door managers worden uitgedaagd, vooral als de bottom line negatief is!
U kunt dit gebruiken om een detailfunctie te bieden, zodat wanneer de manager een bepaald nummer uitdaagt, hij alleen maar hoeft te dubbelklikken op het nummer en de uitsplitsing verschijnt als onderdeel van het rapport.
Dit is zeer indrukwekkend vanuit het oogpunt van een gebruiker en bespaart hen voortdurend de vraag 'waarom is dit aantal zo hoog?'
U zou code moeten schrijven om de kop / criteria voor het nummer te achterhalen (met behulp van de doelobjecteigenschappen) en vervolgens de tabelgegevens filteren en deze vervolgens naar het rapport kopiëren.
VBA-programmering | Code Generator werkt voor u!
Werkblad Evenement activeren
Deze gebeurtenis vindt plaats wanneer de gebruiker van het ene blad naar het andere gaat. Het is van toepassing op het nieuwe blad waar de gebruiker naartoe gaat.
Het kan worden gebruikt om ervoor te zorgen dat het nieuwe blad volledig is berekend voordat de gebruiker er iets op gaat doen. Het kan ook worden gebruikt om alleen dat specifieke blad opnieuw te berekenen zonder de hele werkmap opnieuw te berekenen. Als de werkmap groot is en een ingewikkelde formule bevat, bespaart het opnieuw berekenen van één blad veel tijd
| 123 | Privé subwerkblad_Activate()ActiveSheet.BerekenenEinde sub |
Active X-besturingsgebeurtenissen (niet uitputtend)
Zoals eerder besproken, kunt u Active X-besturingselementen rechtstreeks aan een werkblad toevoegen. Dit kunnen opdrachtknoppen, vervolgkeuzelijsten en keuzelijsten zijn
De Active X-gebeurtenissen zijn te vinden onder het specifieke bladnaamobject (waar u het besturingselement hebt toegevoegd) in de VBE-projectverkenner. U moet de naam van het Active X-besturingselement selecteren in de eerste vervolgkeuzelijst in het codevenster en vervolgens toont de tweede vervolgkeuzelijst u alle beschikbare evenementen
Opdrachtknop Klik op gebeurtenis
Wanneer u een opdrachtknop op een spreadsheet hebt geplaatst, wilt u dat deze actie onderneemt. Dit doe je door code op het Click event te zetten.
Je kunt hier eenvoudig een ‘Weet je het zeker bericht?’ op zetten zodat er een check gedaan wordt voordat je code loopt
| 12345 | Privé subcommandoButton1_Click ()Gedimde knopRet als variantButtonRet = MsgBox("Weet u zeker dat u dit wilt doen?", vbQuestion Of vbYesNo)Als ButtonRet = vbNo, verlaat dan SubEinde sub |
Vervolgkeuzelijst (keuzelijst) Evenement wijzigen
Een Active X-vervolgkeuzelijst heeft een wijzigingsgebeurtenis, zodat als een gebruiker een bepaald item uit de vervolgkeuzelijst selecteert, u hun keuze kunt vastleggen met behulp van deze gebeurtenis en vervolgens code kunt schrijven om andere delen van het werkblad of de werkmap dienovereenkomstig aan te passen.
| 123 | Privé Sub ComboBox1_Change ()MsgBox "Je hebt geselecteerd" & ComboBox1.TextEinde sub |
VBA-programmering | Code Generator werkt voor u!
Aanvinkvakje (selectievakje) Klik op Evenement
U kunt een vinkje of selectievakje aan een werkblad toevoegen om de gebruiker opties te bieden. U kunt de klikgebeurtenis erop gebruiken om te zien of de gebruiker hier iets aan heeft gewijzigd. De geretourneerde waarden zijn True of False, afhankelijk van of het is aangevinkt of niet.
| 123 | Privé Sub CheckBox1_Klik ()MsgBox CheckBox1.ValueEinde sub |
UserForm Events (niet uitputtend)
Excel biedt u de mogelijkheid om uw eigen formulieren te ontwerpen. Deze kunnen erg handig zijn om te gebruiken als pop-ups om informatie te verzamelen of om de gebruiker meerdere keuzes te bieden. Ze gebruiken Active X-besturingselementen zoals eerder beschreven en hebben exact dezelfde gebeurtenissen, hoewel de gebeurtenissen sterk afhangen van het type besturingselement.
Hier is een voorbeeld van een eenvoudig formulier:
Wanneer het wordt weergegeven, ziet het er zo uit op het scherm
U zou gebeurtenissen op het formulier gebruiken om dingen te doen zoals het invoeren van een standaard bedrijfsnaam wanneer het formulier wordt geopend, om te controleren of de ingevoerde bedrijfsnaam overeenkomt met een naam die al in de spreadsheet staat en niet verkeerd is gespeld, en om code toe te voegen aan de klik gebeurtenissen op de knoppen 'OK' en 'Annuleren'
De code en gebeurtenissen achter het formulier kunnen worden bekeken door ergens op het formulier te dubbelklikken
De eerste vervolgkeuzelijst geeft toegang tot alle bedieningselementen op het formulier. De tweede vervolgkeuzelijst geeft toegang tot de evenementen
Gebruikersformulier Evenement activeren
Deze gebeurtenis wordt geactiveerd wanneer het formulier wordt geactiveerd, normaal gesproken wanneer het wordt weergegeven. Deze gebeurtenis kan worden gebruikt om standaardwaarden in te stellen, b.v. een standaard bedrijfsnaam in het tekstvak bedrijfsnaam
| 123 | Privé subgebruikerForm_Activate()TextBox1.Text = "Mijn bedrijfsnaam"Einde sub |
VBA-programmering | Code Generator werkt voor u!
Evenement wijzigen
De meeste besturingselementen op het formulier hebben een wijzigingsgebeurtenis, maar in dit voorbeeld kan het tekstvak van de bedrijfsnaam de gebeurtenis gebruiken om een beperking op te leggen aan de lengte van de ingevoerde bedrijfsnaam
| 123456 | Privé Sub TextBox1_Change ()Als Len (TextBox1.Text) > 20 DanMsgBox "De naam is beperkt tot 20 tekens", vbCriticalTextBox1.Text = ""Stop alsEinde sub |
Klik op Evenement
U kunt deze gebeurtenis gebruiken om actie te ondernemen als de gebruiker op besturingselementen op het formulier klikt, of zelfs op het formulier zelf
Op dit formulier staat een 'OK'-knop en als we een bedrijfsnaam hebben verzameld, willen we deze in een cel op de spreadsheet plaatsen voor toekomstig gebruik
| 1234 | Privé subcommandoButton1_Click ()ActiveSheet.Bereik ("A1"). Waarde = TextBox1.TextIk.VerbergenEinde sub |
Deze code werkt wanneer de gebruiker op de knop 'OK' klikt. Het plaatst de waarde in het invoervak voor de bedrijfsnaam in cel A1 op het actieve blad en verbergt vervolgens het formulier zodat de gebruikerscontrole teruggaat naar het werkblad.
Kaartgebeurtenissen
Grafiekgebeurtenissen werken alleen op grafieken die op een apart grafiekblad staan en niet op een grafiek die in een standaardwerkblad is opgenomen
Grafiekgebeurtenissen zijn enigszins beperkt en kunnen niet worden gebruikt op een werkblad waar u mogelijk meerdere grafieken hebt. Ook willen gebruikers niet per se overschakelen van een werkblad met getallen naar een grafiekblad - er is hier geen onmiddellijke visuele impact
De handigste gebeurtenis zou zijn om uit te zoeken op welk onderdeel van een grafiek een gebruiker heeft geklikt, b.v. een segment in een cirkeldiagram of een staaf in een staafdiagram, maar dit is geen gebeurtenis die beschikbaar is in het standaardbereik van gebeurtenissen.
Dit probleem kan worden opgelost door een klassenmodule te gebruiken om een gebeurtenis 'Mouse Down' toe te voegen die details teruggeeft van het diagramonderdeel waarop de gebruiker heeft geklikt. Dit wordt gebruikt op een grafiek in een werkblad.
Dit brengt een aantal zeer gecompliceerde codering met zich mee, maar de resultaten zijn spectaculair. U kunt drill-downs maken, b.v. de gebruiker klikt op een cirkeldiagramsegment en onmiddellijk wordt dat diagram verborgen en verschijnt er een tweede diagram op zijn plaats met een cirkeldiagram met details voor het oorspronkelijke segment, of u kunt de tabelgegevens produceren die dat segment van het cirkeldiagram ondersteunen.
Toepassingsgebeurtenissen
U kunt het toepassingsobject in VBA gebruiken om code af te vuren volgens een bepaalde gebeurtenis
VBA-programmering | Code Generator werkt voor u!
Toepassing.OnTime
Hierdoor kunt u met regelmatige tussenpozen een stukje code afvuren zolang de werkmap in Excel is geladen. Misschien wilt u uw werkmap elke 10 minuten automatisch in een andere map opslaan of het werkblad 's nachts laten draaien om de nieuwste gegevens van een externe bron binnen te halen.
In dit voorbeeld wordt een subroutine ingevoerd in een module. Het geeft elke 5 minuten een berichtvenster weer, hoewel dit gemakkelijk een andere gecodeerde procedure kan zijn. Tegelijkertijd wordt de timer opnieuw ingesteld op de huidige tijd plus nog 5 minuten.
Elke keer dat deze wordt uitgevoerd, wordt de timer opnieuw ingesteld om dezelfde subroutine in nog eens 5 minuten uit te voeren.
| 1234 | Sub TestOnTime()MsgBox "Test op tijd"Application.OnTime (Now () + TimeValue ("00:05:00")), "TestOnTime"Einde sub |
Applicatie.OnKey
Met deze functie kunt u uw eigen sneltoetsen ontwerpen. U kunt elke toetscombinatie een subroutine van uw creatie laten noemen.
In dit voorbeeld wordt de letter 'a' omgeleid zodat in plaats van een 'a' in een cel te plaatsen, er een berichtvenster wordt weergegeven. Deze code dient in een geplaatste module geplaatst te worden.
| 123456 | SubtestKeyPress()Application.OnKey "a", "TestKeyPress"Einde subSubtestKeyPress()MsgBox "Je hebt op 'a' gedrukt"Einde sub |
U voert eerst de subroutine 'TestKeyPress' uit. U hoeft dit maar één keer uit te voeren. Het vertelt Excel dat elke keer dat de letter 'a' wordt ingedrukt, het de subroutine 'TestKeyPress' zal oproepen. De subroutine 'TestKeyPress' geeft alleen een berichtvenster weer om u te vertellen dat u op toets 'a' hebt gedrukt. Het kan natuurlijk een formulier laden of allerlei andere dingen doen.
U kunt elke toetscombinatie gebruiken die u kunt gebruiken met de functie 'SendKeys'
Om deze functionaliteit te annuleren, voert u de 'OnKey'-instructie uit zonder de 'Procedure'-parameter.
| 123 | Sub AnnulerenOnKey()Applicatie.OnKey "a"Einde sub |
Alles is nu weer normaal.
