Hendelser skjer hele tiden når en bruker åpner en Excel -arbeidsbok og begynner å gjøre forskjellige handlinger, for eksempel å legge inn data i celler eller flytte mellom ark
I Visual Basic Editor (ALT+F11) er det allerede satt opp sub -rutiner som kan bli avfyrt når brukeren gjør noe f.eks. legge inn data i en celle. Underrutinen gir ingen handlingskode, bare en 'Sub' -setning og en' End Sub' -setning uten kode mellom dem. De er effektivt sovende, så ingenting skjer før du skriver inn noen kode.
Her er et eksempel basert på "Endre" -hendelsen i et regneark:
Som VBA -programmerer kan du legge til kode for å få visse ting til å skje når brukeren foretar en bestemt handling. Dette gir deg sjansen til å kontrollere brukeren og forhindre at de utfører handlinger du ikke vil at de skal gjøre og som kan skade arbeidsboken din. For eksempel vil du kanskje at de skal lagre sin egen individuelle kopi av arbeidsboken under et annet navn, slik at de ikke påvirker originalen, som kan bli brukt av en rekke brukere.
Hvis de lukker arbeidsboken, blir de automatisk bedt om å lagre endringene. Imidlertid har arbeidsboken en "BeforeClose" -hendelse, og du kan skrive inn kode for å forhindre at arbeidsboken lukkes og utløser en "Lagre" -hendelse. Du kan deretter legge til en knapp i selve regnearket og sette din egen "Lagre" -rutine på den. Du kan også deaktivere "Lagre" -rutinen ved å bruke "BeforeSave" -hendelsen
En forståelse av hvordan hendelser fungerer er helt avgjørende for en VBA -programmerer.
Typer hendelser
Arbeidsbok arrangementer - disse hendelsene avfyres basert på hva brukeren gjør med arbeidsboken selv. De inkluderer brukerhandlinger som å åpne arbeidsboken, lukke arbeidsboken, lagre arbeidsboken, legge til eller slette ark
Arbeidsark Hendelser - disse hendelsene blir avfyrt av en bruker som utfører handlinger på et bestemt regneark. Hvert regneark i arbeidsboken har en individuell kodemodul, som inneholder forskjellige hendelser spesielt for det regnearket (ikke for alle regnearkene). Disse inkluderer brukerhandlinger som å endre innholdet i en celle, dobbeltklikke på en celle eller høyreklikke på en celle.
Aktive X -kontrollhendelser - Aktive X -kontroller kan legges til i et regneark ved hjelp av "Sett inn" -ikonet på "Utvikler" -fanen i Excel -båndet. Dette er ofte knappekontroller for å gjøre det mulig for brukeren å utføre forskjellige handlinger under kontroll av koden din, men de kan også være objekter som nedtrekkslister. Å bruke Active X -kontroller i motsetning til skjemakontroller på regnearket gir et helt rom for programmerbarhet. Aktive X -kontroller gir deg langt mer fleksibilitet fra et programmeringssynspunkt over bruk av skjemakontroller i et regneark.
For eksempel kan du ha to rullegardinmenyer på regnearket. Du vil at den tilgjengelige listen i den andre rullegardinmenyen skal være basert på hva brukeren valgte i den første rullegardinmenyen. Ved å bruke "Endre" -hendelsen i den første rullegardinmenyen, kan du opprette kode for å lese hva brukeren har valgt, og deretter oppdatere den andre rullegardinmenyen. Du kan også deaktivere den andre rullegardinmenyen til brukeren har gjort et valg i den første rullegardinmenyen
UserForm -hendelser - Du kan sette inn og designe et profesjonelt utseende som du kan bruke som en popup. Alle kontrollene du plasserer på skjemaet er Active X -kontroller, og de har de samme hendelsene som Active X -kontrollene du kan plassere i et regneark
Kart hendelser - Disse hendelsene er bare relatert til et diagramark og ikke til et diagram som vises som en del av et regneark. Disse hendelsene inkluderer endring av størrelsen på diagrammet eller valg av diagrammet.
Søknadshendelser - Disse bruker applikasjonsobjektet i VBA. Eksempler vil tillate at koden slås av når en bestemt tast trykkes eller når en bestemt tid er nådd. Du kan programmere en situasjon der arbeidsboken er åpen 24/7 og den importerer data fra en ekstern kilde over natten på et forhåndsbestemt tidspunkt.
Farer ved bruk av kode i hendelser
Når du skriver kode for å gjøre noe når brukeren foretar en bestemt handling, må du huske på at koden din kan utløse andre hendelser, noe som kan sette koden din i en kontinuerlig sløyfe.
Anta for eksempel at du bruker "Endre" -hendelsen i et regneark, slik at når brukeren legger inn en verdi i en celle, blir en beregning basert på den cellen plassert i cellen umiddelbart til høyre for den.
Problemet her er at plasseringen av den beregnede verdien i cellen utløser en annen "Endring" -hendelse, som deretter utløser enda en "Endre" -hendelse, og så videre til koden din har gått tom for kolonner som skal brukes, og kaster opp en feilmelding.
Du må tenke nøye når du skriver koden for hendelsen for å sikre at andre hendelser ikke utløses utilsiktet
Deaktiver hendelser
Du kan bruke koden til å deaktivere hendelser for å omgå dette problemet. Det du må gjøre er å inkludere kode for å deaktivere hendelser mens hendelseskoden din kjører og deretter aktivere hendelser på nytt på slutten av koden. Her er et eksempel på hvordan du gjør det:
| 1234 | Sub DisableEvents ()Application.EnableEvents = FalskApplication.EnableEvents = TrueSlutt Sub |
Husk at dette deaktiverer alle hendelser på tvers av Excel -programmet, så dette vil også påvirke andre funksjoner i Excel. Hvis du bruker dette av en eller annen grunn, må du kontrollere at hendelsene slås på igjen etterpå.
Viktigheten av parametere i hendelser
Hendelser har vanligvis parametere som du kan bruke til å finne ut mer om hva brukeren gjør og cellelokaliteten de befinner seg i.
For eksempel ser regnearksendringshendelsen slik ut:
| 1 | Private Sub Worksheet_Change (ByVal Target As Range) |
Ved å bruke rekkeviddeobjektet, kan du finne ut cellelinje/kolonne -koordinatene som brukeren faktisk er i.
| 1234 | Private Sub Worksheet_Change (ByVal Target As Range)MsgBox Target.ColumnMsgBox Target.RowSlutt Sub |
Hvis du bare vil at koden din skal fungere på et bestemt kolonne- eller radnummer, legger du til en betingelse som går ut av underrutinen hvis kolonnen ikke er den nødvendige.
| 123 | Private Sub Worksheet_Change (ByVal Target As Range)Hvis Target.Column 2 Avslutt deretter SubSlutt Sub |
Dette løser problemet med at koden din utløser flere hendelser, siden den bare fungerer hvis brukeren har endret en celle i kolonne 2 (kolonne B)
Arbeidsbokhendelser Eksempler (ikke uttømmende)
Arbeidsbokhendelsene finnes under objektet ‘ThisWorkbook’ i VBE Project Explorer. Du må velge "Arbeidsbok" i den første rullegardinmenyen i kodevinduet, og deretter den andre rullegardinmenyen vil vise deg alle tilgjengelige hendelser
Arbeidsbok Åpent arrangement
Denne hendelsen slås av når arbeidsboken åpnes av en bruker. Du kan bruke den til å sende en velkomstmelding til en bruker ved å fange brukernavn
| 123 | Private Sub Workbook_Open ()MsgBox "Velkommen" og Application.UserNameSlutt Sub |
Du kan også sjekke brukernavnet deres mot en liste på et skjult ark for å se om de har autorisasjon til å få tilgang til arbeidsboken. Hvis de ikke er en autorisert bruker, kan du vise en melding og lukke arbeidsboken slik at de ikke kan bruke den.
Arbeidsbok nytt arkhendelse
Denne hendelsen utløses når en bruker legger til et nytt ark i arbeidsboken
Du kan bruke denne koden til å bare tillate deg selv å legge til et nytt ark, i stedet for at forskjellige brukere legger til ark og roter med arbeidsboken
| 1234567 | Private Sub Workbook_NewSheet (ByVal Sh As Object)Application.DisplayAlerts = FalskHvis Application.UserName "Richard" DaSlettSlutt omApplication.DisplayAlerts = TrueSlutt Sub |
Vær oppmerksom på at du må slå av varslene ettersom en brukeradvarsel vil vises når arket slettes, slik at brukeren kan omgå koden din. Sørg for at du slår på varslene igjen etterpå!
Lei av å søke etter eksempler på VBA -kode? Prøv AutoMacro!
Arbeidsbok før lagring av hendelse
Denne hendelsen utløses når brukeren klikker på "Lagre" -ikonet, men før "Lagre" faktisk finner sted
Som beskrevet tidligere, kan det være lurt å forhindre at brukere lagrer endringene i den opprinnelige arbeidsboken, og tvinge dem til å opprette en ny versjon ved hjelp av en knapp på regnearket. Alt du trenger å gjøre er å endre parameteren ‘Avbryt’ til True, og arbeidsboken kan aldri lagres med den konvensjonelle metoden.
| 123 | Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean)Avbryt = santSlutt Sub |
Arbeidsbok før lukk hendelse
Du kan bruke denne hendelsen til å forhindre at brukere lukker arbeidsboken, og igjen tvinge dem til å gå ut av en regnearksknapp. Igjen setter du parameteren ‘Avbryt’ til ‘Sant’. Den røde X øverst til høyre i Excel-vinduet fungerer ikke lenger.
| 123 | Private Sub Workbook_BeforeClose (Avbryt som boolsk)Avbryt = santSlutt Sub |
Hendelseseksempler på regneark (ikke uttømmende)
Regnearkhendelsene finner du under det spesifikke arknavnobjektet i VBE Project Explorer. Du må velge "Regneark" i den første rullegardinmenyen i kodevinduet, og deretter den andre rullegardinmenyen vil vise deg alle hendelsene som er tilgjengelige
Endring av regneark
Denne hendelsen utløses når en bruker foretar en endring i et regneark, for eksempel å skrive inn en ny verdi i en celle
Du kan bruke denne hendelsen til å sette en tilleggsverdi eller kommentar ved siden av den endrede cellen, men som diskutert tidligere, vil du ikke begynne å sette i gang en hendelsesløkke.
| 12345 | Private Sub Worksheet_Change (ByVal Target As Range)Hvis Target.Column 2 Avslutt deretter SubActiveSheet.Cells (Target.Row, Target.Column + 1). Verdi = _ActiveSheet.Cells (Target.Row, Target.Column). Verdi * 1.1Slutt Sub |
I dette eksemplet fungerer koden bare hvis verdien er angitt i kolonne B (kolonne 2). Hvis dette er sant, vil det legge 10% til tallet og plassere det i den neste tilgjengelige cellen
Regneark Før dobbeltklikk -hendelse
Denne hendelsen vil slå av koden hvis en bruker dobbeltklikker på en celle. Dette kan være ekstremt nyttig for finansielle rapporter som en balanse eller resultatregnskap der tall sannsynligvis vil bli utfordret av ledere, spesielt hvis bunnlinjen er negativ!
Du kan bruke dette til å tilby et utfyllingsanlegg, slik at når lederen utfordrer et bestemt nummer, er det bare å dobbeltklikke på nummeret, og sammenbruddet vises som en del av rapporten.
Dette er veldig imponerende sett fra en brukers synsvinkel, og sparer dem hele tiden spør "hvorfor er dette tallet så høyt?"
Du må skrive kode for å finne overskriften / kriteriene for nummeret (ved hjelp av objektobjektegenskapene) og deretter filtrere tabelldataene og deretter kopiere dem til rapporten.
VBA -programmering | Kodegenerator fungerer for deg!
Regneark Aktiver hendelse
Denne hendelsen skjer når brukeren flytter fra ett ark til et annet. Det gjelder det nye arket som brukeren flytter til.
Det kan brukes til å sikre at det nye arket er fullstendig beregnet før brukeren begynner å gjøre noe på det. Det kan også brukes til å bare beregne det aktuelle arket på nytt uten å beregne hele arbeidsboken på nytt. Hvis arbeidsboken er stor og har en komplisert formel, sparer det mye tid på nytt å beregne ett ark på nytt
| 123 | Private Sub Worksheet_Activate ()ActiveSheet.CalculateSlutt Sub |
Active X Control Events (ikke uttømmende)
Som diskutert tidligere kan du legge til Active X -kontroller direkte på et regneark. Disse kan være kommandoknapper, rullegardinmenyer og listebokser
Active X -hendelsene finner du under det spesifikke arknavnobjektet (der du la til kontrollen) i VBE Project Explorer. Du må velge navnet på Active X -kontrollen i den første rullegardinmenyen i kodevinduet, og deretter vil den andre rullegardinlisten vise deg alle tilgjengelige hendelser
Kommandoknapp Klikk hendelse
Når du har satt en kommandoknapp på et regneark, vil du at den skal gjøre noe. Du gjør dette ved å sette kode på Click -hendelsen.
Du kan enkelt sette en "Er du sikker melding?" På denne slik at en sjekk blir utført før koden din kjøres
| 12345 | Private Sub CommandButton1_Click ()Dim ButtonRet As VariantButtonRet = MsgBox ("Er du sikker på at du vil gjøre dette?", VbQuestion Eller vbYesNo)Hvis ButtonRet = vbNo, avslutt deretter SubSlutt Sub |
Drop Down (kombinasjonsboks) Endre hendelse
En Active X-rullegardinmeny har en endringshendelse, slik at hvis en bruker velger et bestemt element fra rullegardinlisten, kan du fange valget ved å bruke denne hendelsen og deretter skrive kode for å tilpasse andre deler av arket eller arbeidsboken deretter.
| 123 | Private Sub ComboBox1_Change ()MsgBox "Du valgte" & ComboBox1.TextSlutt Sub |
VBA -programmering | Kodegenerator fungerer for deg!
Kryss av (Avmerkingsboks) Klikk på Hendelse
Du kan legge til en hake eller avmerkingsboks i et regneark for å gi alternativer for brukeren. Du kan bruke klikkhendelsen på den for å se om brukeren har endret noe på dette. Verdiene som returneres er True eller False, avhengig av om det er krysset av eller ikke.
| 123 | Private Sub CheckBox1_Click ()MsgBox CheckBox 1. verdiSlutt Sub |
UserForm -hendelser (ikke uttømmende)
Excel gir deg muligheten til å designe dine egne skjemaer. Disse kan være svært nyttige å bruke som popup-vinduer for å samle informasjon eller for å gi flere valg til brukeren. De bruker Active X -kontroller som beskrevet tidligere og har nøyaktig de samme hendelsene, selv om hendelsene avhenger veldig av typen kontroll.
Her er et eksempel på en enkel form:
Når det vises slik ser det ut på skjermen
Du vil bruke hendelser på skjemaet for å gjøre ting som å skrive inn et standard firmanavn når skjemaet åpnes, for å kontrollere at firmanavnsinngangen er enig i en som allerede er i regnearket og ikke har blitt stavet feil, og for å legge til kode i klikket hendelser på knappene "OK" og "Avbryt"
Koden og hendelsene bak skjemaet kan sees ved å dobbeltklikke hvor som helst på skjemaet
Den første rullegardinmenyen gir tilgang til alle kontrollene på skjemaet. Den andre rullegardinmenyen vil gi tilgang til hendelsene
UserForm Activate Event
Denne hendelsen utløses når skjemaet er aktivert, normalt når det vises. Denne hendelsen kan brukes til å sette opp standardverdier f.eks. et standard firmanavn i tekstboksen for firmanavn
| 123 | Privat underbrukerForm_Aktiver ()TextBox1.Text = "Mitt firmanavn"Slutt Sub |
VBA -programmering | Kodegenerator fungerer for deg!
Endre hendelse
De fleste kontrollene på skjemaet har en endringshendelse, men i dette eksemplet kan tekstboksen firmanavn bruke hendelsen til å sette en begrensning på lengden på firmanavnet som legges inn
| 123456 | Private Sub TextBox1_Change ()Hvis Len (TextBox1.Text)> 20 DaMsgBox "Navnet er begrenset til 20 tegn", vbCriticalTextBox1.Text = ""Slutt omSlutt Sub |
Klikk på Hendelse
Du kan bruke denne hendelsen til å iverksette tiltak fra brukeren klikker på kontroller på skjemaet, eller til og med selve skjemaet
På dette skjemaet er det en "OK" -knapp, og etter å ha samlet et firmanavn, vil vi plassere det i en celle i regnearket for fremtidig referanse
| 1234 | Private Sub CommandButton1_Click ()ActiveSheet.Range ("A1"). Verdi = TextBox1.TextJeg. SkjulSlutt Sub |
Denne koden virker når brukeren klikker på "OK" -knappen. Den setter verdien i boksen for firmanavn i celle A1 på det aktive arket og skjuler deretter skjemaet slik at brukerkontrollen returneres til regnearket.
Kart hendelser
Karthendelser fungerer bare på diagrammer som er på et eget diagramark, og ikke på et diagram som er innarbeidet i et standard regneark
Karthendelser er noe begrenset og kan ikke brukes på et regneark der du godt kan ha flere diagrammer. Brukerne vil heller ikke nødvendigvis bytte fra et regneark som inneholder tall til et diagramark - det er ingen umiddelbar visuell innvirkning her
Den mest nyttige hendelsen ville være å finne ut komponenten i et diagram som en bruker har klikket på f.eks. et segment i et sektordiagram, eller en stolpe i et stolpediagram, men dette er ikke en hendelse tilgjengelig på standard hendelsesområde.
Dette problemet kan løses ved å bruke en klassemodul til å legge til en "mus ned" -hendelse som vil returnere detaljer om diagramkomponenten som brukeren har klikket på. Dette brukes på et diagram i et regneark.
Dette innebærer en veldig komplisert koding, men resultatene er spektakulære. Du kan lage drill downs f.eks. brukeren klikker på et sektordiagram og umiddelbart blir diagrammet skjult, og et annet diagram vises i stedet som viser et sektordiagram med detaljer for det originale segmentet, eller du kan lage tabelldataene som støtter det segmentet i sektordiagrammet.
Søknadshendelser
Du kan bruke applikasjonsobjektet i VBA til å utløse kode i henhold til en bestemt hendelse
VBA -programmering | Kodegenerator fungerer for deg!
Application.OnTime
Dette kan gjøre at du kan fyre av et stykke kode med jevne mellomrom så lenge arbeidsboken er lastet inn i Excel. Det kan være lurt å lagre arbeidsboken automatisk i en annen mappe hvert 10. minutt, eller la regnearket kjøre over natten for å hente inn de nyeste dataene fra en ekstern kilde.
I dette eksemplet legges en underrutine inn i en modul. Den viser en meldingsboks hvert 5. minutt, selv om dette lett kan være en annen kodet prosedyre. Samtidig tilbakestiller den timeren til gjeldende tid pluss 5 minutter til.
Hver gang den kjøres, tilbakestilles timeren til å kjøre den samme underrutinen på ytterligere 5 minutter.
| 1234 | Sub TestOnTime ()MsgBox "Testing OnTime"Application.OnTime (Now () + TimeValue ("00:05:00")), "TestOnTime"Slutt Sub |
Application.OnKey
Denne funksjonen lar deg designe dine egne hurtigtaster. Du kan få hvilken som helst tastekombinasjon til å kalle en underrutine for opprettelsen.
I dette eksemplet blir bokstaven 'a' omdirigert slik at den i stedet for å plassere et 'a' i en celle, vil vise en meldingsboks. Denne koden må plasseres i en innsatt modul.
| 123456 | Sub TestKeyPress ()Application.OnKey "a", "TestKeyPress"Slutt SubSub TestKeyPress ()MsgBox "Du trykket" a ""Slutt Sub |
Du kjører først rutinen "TestKeyPress". Du trenger bare å kjøre dette en gang. Den forteller Excel at hver gang bokstaven "a" trykkes, kalles den delrutinen "TestKeyPress". Underrutinen 'TestKeyPress' viser bare en meldingsboks for å fortelle deg at du trykket på tasten 'a'. Det kan selvfølgelig laste inn et skjema eller gjøre alle slags andre ting.
Du kan bruke hvilken som helst tastekombinasjon du kan bruke med funksjonen ‘SendKeys’
For å avbryte denne funksjonaliteten kjører du "OnKey" -setningen uten parameteren "Prosedyre".
| 123 | Sub CancelOnKey ()Application.OnKey "a"Slutt Sub |
Alt er nå tilbake til det normale.
