Begivenheder sker hele tiden, når en bruger åbner en Excel -projektmappe og begynder at udføre forskellige handlinger, f.eks. Indtastning af data i celler eller flytning mellem ark
Inden for Visual Basic Editor (ALT+F11) er der allerede konfigureret delrutiner, som kan blive afbrudt, når brugeren gør noget f.eks. indtastning af data i en celle. Underrutinen giver ikke nogen handlingskode, blot en 'Sub' -sætning og en' End Sub' -sætning uden kode mellem dem. De er effektivt i dvale, så der sker ikke noget, før du indtaster en kode.
Her er et eksempel baseret på 'Change' -begivenheden i et regneark:
Som VBA -programmør kan du tilføje kode for at få visse ting til at ske, når brugeren foretager en bestemt handling. Dette giver dig mulighed for at kontrollere brugeren og forhindre dem i at foretage handlinger, som du ikke ønsker, at de skal udføre, og som kan skade din projektmappe. For eksempel vil du måske have dem til at gemme deres egen individuelle kopi af projektmappen under et andet navn, så de ikke påvirker originalen, som muligvis bruges af et antal brugere.
Hvis de lukker projektmappen, bliver de automatisk bedt om at gemme deres ændringer. Men projektmappen har en "BeforeClose" -begivenhed, og du kan indtaste kode for at forhindre, at projektmappen lukkes og udløser en "Save" -hændelse. Du kan derefter tilføje en knap til selve regnearket og sætte din egen ‘Gem’ -rutine på den. Du kan også deaktivere 'Gem' -rutinen ved hjælp af 'BeforeSave' -begivenheden
En forståelse af, hvordan begivenheder fungerer, er helt afgørende for en VBA -programmør.
Begivenhedstyper
Arbejdsbog Begivenheder - disse hændelser udløses baseret på, hvad brugeren gør med selve projektmappen. De omfatter brugerhandlinger, f.eks. At åbne projektmappen, lukke projektmappen, gemme projektmappen, tilføje eller slette ark
Regneark Begivenheder - disse hændelser affyres ved, at en bruger foretager handlinger på et specifikt regneark. Hvert regneark i projektmappen har et individuelt kodemodul, som indeholder forskellige hændelser specifikt for dette regneark (ikke for alle regnearkene). Disse omfatter brugerhandlinger såsom ændring af indholdet i en celle, dobbeltklik på en celle eller højreklik på en celle.
Aktive X -kontrolbegivenheder - Aktive X -kontroller kan føjes til et regneark ved hjælp af ikonet 'Indsæt' på fanen 'Udvikler' i Excel -båndet. Disse er ofte knapkontroller for at gøre det muligt for brugeren at foretage forskellige handlinger under kontrol af din kode, men de kan også være objekter som f.eks. Dropdown. Brug af Active X -kontroller i modsætning til formularkontroller på regnearket giver et helt anvendelsesområde for programmerbarhed. Aktive X -kontroller giver dig langt mere fleksibilitet set fra et programmeringssynspunkt over brug af formularkontroller i et regneark.
For eksempel kan du have to rullemenuer på dit regneark. Du vil have den tilgængelige liste i den anden rullemenu baseret på, hvad brugeren valgte i den første rullemenu. Ved hjælp af "Skift" -begivenheden i den første rullemenu kan du oprette kode for at læse, hvad brugeren har valgt, og derefter opdatere den anden rullemenu. Du kan også deaktivere den anden drop-down, indtil brugeren har foretaget et valg i den første drop-down
UserForm -begivenheder - Du kan indsætte og designe en professionel form, der skal bruges som en pop-up. Alle de kontroller, du placerer på din formular, er Active X -kontroller, og de har de samme hændelser som Active X -kontrollerne, som du kan placere på et regneark
Chart begivenheder - Disse begivenheder er kun relateret til et diagramark og ikke til et diagram, der vises som en del af et regneark. Disse begivenheder omfatter ændring af diagrammet eller valg af diagrammet.
Ansøgningsbegivenheder - Disse bruger applikationsobjektet i VBA. Eksempler ville tillade, at kode blev affyret, når der trykkes på en bestemt tast, eller når et bestemt tidspunkt er nået. Du kan programmere en situation, hvor projektmappen efterlades åben døgnet rundt, og den importerer data fra en ekstern kilde natten over på et forudbestemt tidspunkt.
Farer ved brug af kode i begivenheder
Når du skriver kode for at gøre noget, når brugeren foretager en bestemt handling, skal du huske på, at din kode kan udløse andre hændelser, som kan sætte din kode i en kontinuerlig sløjfe.
Antag for eksempel, at du bruger 'Skift' -hændelsen på et regneark, så når brugeren lægger en værdi i en celle, placeres en beregning baseret på den celle i cellen umiddelbart til højre for den.
Problemet her er, at placeringen af den beregnede værdi i cellen udløser en anden 'Change' -hændelse, som derefter igen udløser endnu en 'Change' -hændelse og så videre, indtil din kode er løbet tør for kolonner, der skal bruges og kaster op en fejlmeddelelse.
Du skal tænke dig godt om, når du skriver koden til begivenheden for at sikre, at andre begivenheder ikke udløses utilsigtet
Deaktiver begivenheder
Du kan bruge kode til at deaktivere begivenheder for at omgå dette problem. Hvad du skal gøre er at indarbejde kode for at deaktivere begivenheder, mens din begivenhedskode kører og derefter genaktivere begivenheder i slutningen af koden. Her er et eksempel på, hvordan du gør det:
| 1234 | Sub DisableEvents ()Application.EnableEvents = FalskApplication.EnableEvents = TrueAfslut Sub |
Husk, at dette deaktiverer alle hændelser på tværs af Excel -applikationen, så dette vil også påvirke andre funktioner i Excel. Hvis du af en eller anden grund bruger dette, skal du sørge for, at begivenhederne tændes bagefter.
Betydningen af parametre i begivenheder
Begivenheder har normalt parametre, som du kan bruge til at finde ud af mere om, hvad brugeren laver og den celleplacering, de befinder sig i.
For eksempel ser regnearksændringshændelsen sådan ud:
| 1 | Private Sub Worksheet_Change (ByVal Target As Range) |
Ved at bruge områdeobjektet kan du finde ud af de cellerække/kolonne -koordinater, som brugeren faktisk er i.
| 1234 | Private Sub Worksheet_Change (ByVal Target As Range)MsgBox Target.ColumnMsgBox Target.RowAfslut Sub |
Hvis du kun vil have din kode til at fungere på et bestemt kolonne- eller rækkenummer, tilføjer du en betingelse, der forlader underrutinen, hvis kolonnen ikke er den nødvendige.
| 123 | Private Sub Worksheet_Change (ByVal Target As Range)Hvis Target.Column 2 Afslut derefter SubAfslut Sub |
Dette løser problemet med din kode, der udløser flere hændelser, da det kun fungerer, hvis brugeren har ændret en celle i kolonne 2 (kolonne B)
Workbook Events -eksempler (ikke udtømmende)
Arbejdsbogshændelser findes under objektet 'ThisWorkbook' i VBE Project Explorer. Du bliver nødt til at vælge 'Workbook' i den første rullemenu i kodevinduet, og derefter vil den anden drop -down vise dig alle de tilgængelige begivenheder
Workbook Open Event
Denne begivenhed udløses, hver gang projektmappen åbnes af en bruger. Du kan bruge den til at sende en velkomstbesked til en bruger ved at fange deres brugernavn
| 123 | Private Sub Workbook_Open ()MsgBox "Velkommen" og Application.UserNameAfslut Sub |
Du kan også kontrollere deres brugernavn i forhold til en liste på et skjult ark for at se, om de har tilladelse til at få adgang til projektmappen. Hvis de ikke er en autoriseret bruger, kan du vise en meddelelse og lukke projektmappen, så de ikke kan bruge den.
Arbejdsbog Nyt ark begivenhed
Denne hændelse udløses, når en bruger tilføjer et nyt ark til projektmappen
Du kan bruge denne kode til kun at tillade dig selv at tilføje et nyt ark, i stedet for at forskellige brugere alle tilføjer ark og laver rod i projektmappen
| 1234567 | Private Sub Workbook_NewSheet (ByVal Sh As Object)Application.DisplayAlerts = FalskHvis Application.UserName "Richard" SåSh.SletAfslut HvisApplication.DisplayAlerts = TrueAfslut Sub |
Bemærk, at du skal deaktivere advarslerne, da der vises en brugeradvarsel, når arket slettes, hvilket gør det muligt for brugeren at omgå din kode. Sørg for at tænde for advarslerne bagefter!
Træt af at søge efter eksempler på VBA -kode? Prøv AutoMacro!
Arbejdsbog Før Gem begivenhed
Denne begivenhed udløses, når brugeren klikker på ikonet ‘Gem’, men før ’Gem’ faktisk finder sted
Som beskrevet tidligere kan du forhindre, at brugere gemmer deres ændringer i den originale projektmappe og tvinge dem til at oprette en ny version ved hjælp af en knap på regnearket. Det eneste du skal gøre er at ændre parameteren ‘Annuller’ til Sand, og projektmappen kan aldrig gemmes med den konventionelle metode.
| 123 | Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean)Annuller = SandtAfslut Sub |
Arbejdsbog Før luk begivenhed
Du kan bruge denne hændelse til at forhindre brugere i at lukke projektmappen og igen tvinge dem til at forlade gennem en regnearksknap. Igen sætter du parameteren 'Annuller' til 'Sand'. Det røde X i øverste højre hjørne af Excel-vinduet fungerer ikke længere.
| 123 | Private Sub Workbook_BeforeClose (Annuller som boolsk)Annuller = SandtAfslut Sub |
Eksempler på regnearkbegivenhed (ikke udtømmende)
Regnearkets begivenheder findes under det specifikke arknavnobjekt i VBE Project Explorer. Du bliver nødt til at vælge 'Regneark' i den første rullemenu i kodevinduet, og derefter viser den anden rullemenu alle de tilgængelige begivenheder
Hændelse til ændring af regneark
Denne hændelse udløses, når en bruger foretager en ændring af et regneark, f.eks. Ved at indtaste en ny værdi i en celle
Du kan bruge denne begivenhed til at lægge en ekstra værdi eller kommentar ved siden af den ændrede celle, men som diskuteret tidligere vil du ikke starte med at starte en loop af begivenheder.
| 12345 | Private Sub Worksheet_Change (ByVal Target As Range)Hvis Target.Column 2 Afslut derefter SubActiveSheet.Cells (Target.Row, Target.Column + 1). Værdi = _ActiveSheet.Cells (Target.Row, Target.Column). Værdi * 1.1Afslut Sub |
I dette eksempel fungerer koden kun, hvis værdien er angivet i kolonne B (kolonne 2). Hvis dette er sandt, tilføjer det 10% til tallet og placerer det i den næste tilgængelige celle
Regneark Før dobbeltklikshændelse
Denne begivenhed udløser kode, hvis en bruger dobbeltklikker på en celle. Dette kan være yderst nyttigt for finansielle rapporter såsom en balance eller en resultatopgørelse, hvor tal sandsynligvis vil blive udfordret af ledere, især hvis bundlinjen er negativ!
Du kan bruge dette til at tilvejebringe en drill-down facilitet, så når lederen udfordrer et bestemt nummer, er alt, hvad de skal gøre, at dobbeltklikke på nummeret, og opdelingen vises som en del af rapporten.
Dette er meget imponerende set fra en brugers synspunkt og gemmer dem konstant ved at spørge 'hvorfor er dette tal så højt?'
Du bliver nødt til at skrive kode for at finde overskriften / kriterierne for nummeret (ved hjælp af objektobjektets egenskaber) og derefter filtrere de tabelformede data og derefter kopiere dem til rapporten.
VBA -programmering | Kodegenerator virker for dig!
Regneark Aktiver begivenhed
Denne hændelse opstår, når brugeren flytter fra et ark til et andet. Det gælder for det nye ark, som brugeren flytter til.
Det kunne bruges til at sikre, at det nye ark er fuldstændig beregnet, før brugeren begynder at gøre noget på det. Det kan også bruges til kun at genberegne det pågældende ark uden at genberegne hele projektmappen. Hvis projektmappen er stor og har en kompliceret formel, sparer det meget tid at genberegne et ark
| 123 | Privat under regneark_Aktiver ()ActiveSheet. BeregnAfslut Sub |
Active X Control Events (ikke udtømmende)
Som diskuteret tidligere kan du tilføje Active X -kontroller direkte til et regneark. Disse kan være kommandoknapper, rullelister og listebokse
Active X -hændelserne findes under det specifikke arknavnobjekt (hvor du tilføjede kontrollen) i VBE Project Explorer. Du bliver nødt til at vælge navnet på Active X -kontrollen i den første rullemenu i kodevinduet, og derefter viser den anden rullemenu alle de tilgængelige hændelser
Kommandoknap Klik på begivenhed
Når du har lagt en kommandoknap på et regneark, vil du gerne have, at den tager nogle handlinger. Det gør du ved at sætte kode på Click -begivenheden.
Du kan nemt sætte en 'Er du sikker på meddelelse?' På dette, så der foretages en kontrol, før din kode kører
| 12345 | Privat underkommandoButton1_Click ()Dim ButtonRet som variantButtonRet = MsgBox ("Er du sikker på, at du vil gøre dette?", VbQuestion Eller vbYesNo)Hvis ButtonRet = vbNo Så afslut SubAfslut Sub |
Drop Down (kombinationsboks) Skift begivenhed
En Active X-rullemenu har en ændringshændelse, så hvis en bruger vælger et bestemt element fra rullelisten, kan du fange deres valg ved hjælp af denne hændelse og derefter skrive kode for at tilpasse andre dele af arket eller projektmappen i overensstemmelse hermed.
| 123 | Private Sub ComboBox1_Change ()MsgBox "Du har valgt" & ComboBox1.TextAfslut Sub |
VBA -programmering | Kodegenerator virker for dig!
Afkrydsningsfelt (afkrydsningsfelt) Klik på begivenhed
Du kan tilføje et flueben eller afkrydsningsfelt til et regneark for at give valgmuligheder for brugeren. Du kan bruge klikhændelsen på den for at se, om brugeren har ændret noget på dette. De returnerede værdier er True eller False alt efter om det er markeret eller ej.
| 123 | Privat under CheckBox1_Click ()MsgBox CheckBox 1. VærdiAfslut Sub |
UserForm -begivenheder (ikke udtømmende)
Excel giver dig mulighed for at designe dine egne formularer. Disse kan være meget nyttige at bruge som pop-ups til at indsamle oplysninger eller til at give brugeren flere valgmuligheder. De bruger Active X -kontroller som beskrevet tidligere og har nøjagtig de samme hændelser, selvom hændelserne i høj grad afhænger af typen af kontrol.
Her er et eksempel på en simpel form:
Når det vises, ser det sådan ud på skærmen
Du ville bruge begivenheder i formularen til at gøre ting som f.eks. Indtaste et standard firmanavn, når formularen åbnes, for at kontrollere, at virksomhedsnavnet er indforstået med en, der allerede er i regnearket og ikke er stavet forkert, og for at tilføje kode til klikket begivenheder på knapperne 'OK' og 'Annuller'
Koden og begivenhederne bag formularen kan ses ved at dobbeltklikke hvor som helst på formularen
Den første drop down giver adgang til alle kontrolelementer på formularen. Den anden drop down vil give adgang til begivenhederne
UserForm Activate Event
Denne hændelse udløses, når formularen aktiveres, normalt når den vises. Denne hændelse kan bruges til at opsætte standardværdier f.eks. et standard firmanavn i tekstfeltet virksomhedsnavn
| 123 | Privat underbrugerForm_Aktiver ()TextBox1.Text = "Mit firmanavn"Afslut Sub |
VBA -programmering | Kodegenerator virker for dig!
Skift begivenhed
De fleste af kontrollerne på formularen har en ændringshændelse, men i dette eksempel kan tekstboksen firmanavn bruge hændelsen til at sætte en begrænsning på længden af det firmanavn, der skal indtastes
| 123456 | Private Sub TextBox1_Change ()Hvis Len (TextBox1.Text)> 20 SåMsgBox "Navnet er begrænset til 20 tegn", vbCriticalTextBox1.Text = ""Afslut HvisAfslut Sub |
Klik på Begivenhed
Du kan bruge denne begivenhed til at handle fra brugeren ved at klikke på kontrolelementer på formularen eller endda selve formularen
På denne formular er der en ‘OK’ -knap, og efter at have samlet et firmanavn, vil vi gerne placere det i en celle på regnearket til fremtidig reference
| 1234 | Privat underkommandoButton1_Click ()ActiveSheet.Range ("A1"). Værdi = TextBox1.TextMig. SkjulAfslut Sub |
Denne kode virker, når brugeren klikker på knappen ‘OK’. Det sætter værdien i firmanavnsfeltet ind i celle A1 på det aktive ark og skjuler derefter formularen, så brugerkontrol returneres til regnearket.
Chart begivenheder
Kortbegivenheder fungerer kun på diagrammer, der er på et separat diagramark, og ikke på et diagram, der er indarbejdet i et standardark
Diagramhændelser er noget begrænsede og kan ikke bruges på et regneark, hvor du godt kan have flere diagrammer. Brugerne ønsker heller ikke nødvendigvis at skifte fra et regneark med tal til et diagramark - der er ingen umiddelbar visuel indvirkning her
Den mest nyttige begivenhed ville være at finde ud af komponenten i et diagram, som en bruger har klikket på f.eks. et segment i et cirkeldiagram eller en søjle i et søjlediagram, men dette er ikke en begivenhed, der er tilgængelig på standardhændelsesområdet.
Dette problem kan løses ved at bruge et klassemodul til at tilføje en 'Mouse Down' -hændelse, der returnerer detaljer om diagramkomponenten, som brugeren har klikket på. Dette bruges på et diagram i et regneark.
Dette indebærer en meget kompliceret kodning, men resultaterne er spektakulære. Du kan oprette drill downs f.eks. brugeren klikker på et cirkeldiagramsegment, og øjeblikkeligt er diagrammet skjult, og et andet diagram vises i stedet, der viser et cirkeldiagram med detaljer for det originale segment, eller du kan producere de tabeloplysninger, der understøtter dette segment i cirkeldiagrammet.
Ansøgningsbegivenheder
Du kan bruge applikationsobjektet i VBA til at afbryde kode i henhold til en bestemt hændelse
VBA -programmering | Kodegenerator virker for dig!
Application.OnTime
Dette kan gøre det muligt for dig at afskære et stykke kode med jævne mellemrum, så længe projektmappen er indlæst i Excel. Du vil måske automatisk gemme din projektmappe i en anden mappe hvert 10. minut eller lade regnearket køre natten over for at hente de nyeste data fra en ekstern kilde.
I dette eksempel indtastes en underrutine i et modul. Det viser en meddelelsesboks til hvert 5. minut, selvom dette let kan være en anden kodet procedure. Samtidig nulstiller den timeren til den aktuelle tid plus 5 minutter mere.
Hver gang den kører, nulstilles timeren til at køre den samme underrutine på yderligere 5 minutter.
| 1234 | Sub TestOnTime ()MsgBox "Testing OnTime"Application.OnTime (Now () + TimeValue ("00:05:00")), "TestOnTime"Afslut Sub |
Application.OnKey
Denne funktion giver dig mulighed for at designe dine egne genvejstaster. Du kan få enhver tastekombination til at kalde en underrutine for din oprettelse.
I dette eksempel omdirigeres bogstavet 'a', så det i stedet for at placere et 'a' i en celle viser et meddelelsesfelt. Denne kode skal placeres i et indsat modul.
| 123456 | Sub TestKeyPress ()Application.OnKey "a", "TestKeyPress"Afslut SubSub TestKeyPress ()MsgBox "Du trykkede 'a'"Afslut Sub |
Du kører først rutinen 'TestKeyPress'. Du behøver kun at køre dette én gang. Det fortæller Excel, at hver gang der trykkes på bogstavet 'a', vil det kalde underrutinen 'TestKeyPress'. Underrutinen 'TestKeyPress' viser bare en meddelelsesboks for at fortælle dig, at du har trykket på tasten 'a'. Det kunne selvfølgelig indlæse en formular eller gøre alle mulige andre ting.
Du kan bruge enhver tastekombination, du kan bruge med funktionen ‘SendKeys’
For at annullere denne funktionalitet kører du 'OnKey' -sætningen uden parameteren' Procedure '.
| 123 | Sub CancelOnKey ()Application.OnKey "a"Afslut Sub |
Alt er nu tilbage til det normale.
