Excel VBA Events - En enkel (og komplett) guide

Når du oppretter eller registrerer en makro i Excel, må du kjøre makroen for å utføre trinnene i koden.

Noen få måter å kjøre en makro på inkluderer å bruke makro -dialogboksen, tilordne makroen til en knapp, bruke en snarvei, etc.

Bortsett fra disse brukerinitierte makrokjøringene, kan du også bruke VBA-hendelser til å kjøre makroen.

Excel VBA Events - Introduksjon

La meg først forklare hva som er en hendelse i VBA.

En hendelse er en handling som kan utløse utførelsen av den angitte makroen.

For eksempel, når du åpner en ny arbeidsbok, er det en hendelse. Når du setter inn et nytt regneark, er det en hendelse. Når du dobbeltklikker på en celle, er det en hendelse.

Det er mange slike hendelser i VBA, og du kan opprette koder for disse hendelsene. Dette betyr at så snart en hendelse oppstår, og hvis du har angitt en kode for den hendelsen, vil den koden kjøres umiddelbart.

Excel gjør dette automatisk så snart den merker at en hendelse har funnet sted. Så du trenger bare å skrive koden og plassere den i den riktige hendelsesrutinen (dette dekkes senere i denne artikkelen).

For eksempel, hvis du setter inn et nytt regneark og vil at det skal ha et års prefiks, kan du skrive koden for det.

Når noen setter inn et nytt regneark, vil denne koden automatisk bli utført og legge til årsprefikset i regnearkets navn.

Et annet eksempel kan være at du vil endre fargen på cellen når noen dobbeltklikker på den. Du kan bruke dobbeltklikk-hendelsen til dette.

På samme måte kan du opprette VBA -koder for mange slike hendelser (som vi vil se senere i denne artikkelen).

Nedenfor er en kort visualisering som viser dobbeltklikk-hendelsen i aksjon. Så snart jeg dobbeltklikker på celle A1. Excel åpner umiddelbart en meldingsboks som viser adressen til cellen.

Dobbeltklikk er en hendelse, og det å vise meldingsboksen er det jeg har spesifisert i koden når dobbeltklikk-hendelsen finner sted.

Selv om eksemplet ovenfor er en ubrukelig hendelse, håper jeg at det hjelper deg å forstå hva hendelser egentlig er.

Ulike typer Excel VBA -hendelser

Det er forskjellige objekter i Excel - for eksempel Excel selv (som vi ofte refererer til som applikasjonen), arbeidsbøker, regneark, diagrammer, etc.

Hver av disse objektene kan ha forskjellige hendelser knyttet til seg. For eksempel:

• Hvis du oppretter en ny arbeidsbok, er det en hendelse på applikasjonsnivå.
• Hvis du legger til et nytt regneark, er det en hendelse på arbeidsboknivå.
• Hvis du endrer verdien i en celle i et ark, er det en hendelsesnivåhendelse.

Nedenfor er de forskjellige typene hendelser som finnes i Excel:

1. Hendelser på arbeidsarknivå: Dette er typer hendelser som vil utløse basert på handlingene som er utført i regnearket. Eksempler på disse hendelsene inkluderer å endre en celle i regnearket, endre markeringen, dobbeltklikke på en celle, høyreklikke på en celle, etc.
2. Hendelser på arbeidsboknivå: Disse hendelsene vil bli utløst basert på handlingene på arbeidsboknivå. Eksempler på disse hendelsene inkluderer å legge til et nytt regneark, lagre arbeidsboken, åpne arbeidsboken, skrive ut en del eller hele arbeidsboken, etc.
3. Hendelser på applikasjonsnivå: Dette er hendelsene som skjer i Excel -programmet. Eksempel på disse vil inkludere å lukke noen av de åpne arbeidsbøkene eller åpne en ny arbeidsbok.
4. UserForm -hendelser: Disse hendelsene vil bli utløst basert på handlingene i 'UserForm'. Eksempler på disse inkluderer initialisering av en UserForm eller klikk på en knapp i UserForm.
5. Kart hendelser: Dette er hendelser relatert til diagramarket. Et diagramark er annerledes enn et regneark (det er der de fleste av oss er vant til å jobbe i Excel). Formålet med et diagramark er å holde et diagram. Eksempler på slike hendelser vil inkludere å endre serien på diagrammet eller endre størrelsen på diagrammet.
6. OnTime og OnKey Events: Dette er to hendelser som ikke passer i noen av kategoriene ovenfor. Så jeg har listet disse opp separat. Med "OnTime" -hendelse kan du utføre en kode på et bestemt tidspunkt eller etter at et bestemt tidspunkt har gått. "OnKey" -hendelse lar deg utføre en kode når et bestemt tastetrykk (eller en kombinasjon av tastetrykk) brukes.

Hvor du skal legge hendelsesrelatert kode

I avsnittet ovenfor dekket jeg de forskjellige typene hendelser.

Basert på hendelsestypen må du sette koden i det aktuelle objektet.

For eksempel, hvis det er en regnearkrelatert hendelse, bør den gå i kodevinduet til regnearkobjektet. Hvis det er arbeidsbokrelatert, bør det gå i kodevinduet for et arbeidsbokobjekt.

I VBA har forskjellige objekter - for eksempel regneark, arbeidsbøker, diagramark, brukerformer, etc., sine egne kodevinduer. Du må sette hendelseskoden i det relevante objektets kodevindu. For eksempel - hvis det er en hendelse på arbeidsboknivå, må du ha hendelseskoden i vinduet for arbeidsbokkode.

Følgende seksjoner dekker stedene du kan sette hendelseskoden på:

I vinduet Regnearkkode

Når du åpner VB Editor (ved hjelp av hurtigtast ALT + F11), vil du legge merke til regnearkobjektet i Project Explorer. For hvert regneark i arbeidsboken ser du ett objekt.

Når du dobbeltklikker på regnearkobjektet du vil plassere koden i, åpnes det kodevinduet for det regnearket.

Selv om du kan begynne å skrive koden fra bunnen av, er det mye bedre å velge hendelsen fra en liste med alternativer og la VBA automatisk sette inn den relevante koden for den valgte hendelsen.

For å gjøre dette må du først velge regneark fra rullegardinmenyen øverst til venstre i kodevinduet.

Etter at du har valgt regneark fra rullegardinmenyen, får du en liste over alle hendelsene knyttet til regnearket. Du kan velge den du vil bruke fra rullegardinmenyen øverst til høyre i kodevinduet.

Så snart du velger hendelsen, vil den automatisk legge inn den første og siste linjen i koden for den valgte hendelsen. Nå kan du legge til koden din mellom de to linjene.

Merk: Så snart du velger Regneark fra rullegardinmenyen, vil du legge merke til at to linjer med kode vises i kodevinduet. Når du har valgt hendelsen du vil ha koden for, kan du slette linjene som dukket opp som standard.

Vær oppmerksom på at hvert regneark har et eget kodevindu. Når du legger inn koden for Sheet1, fungerer det bare hvis hendelsen skjer i Sheet1.

I vinduet ThisWorkbook Code

På samme måte som regneark, hvis du har en hendelseskode på arbeidsboknivå, kan du plassere den i ThisWorkbook -kodevinduet.

Når du dobbeltklikker på ThisWorkbook, åpnes kodevinduet for den.

Du må velge arbeidsbok fra rullegardinmenyen øverst til venstre i kodevinduet.

Etter at du har valgt Workbook fra rullegardinlisten, får du en liste over alle hendelsene knyttet til Workbook. Du kan velge den du vil bruke fra rullegardinmenyen øverst til høyre i kodevinduet.

Så snart du velger hendelsen, angir den automatisk den første og siste linjen i koden for den valgte hendelsen. Nå kan du legge til koden din mellom de to linjene.

Merk: Så snart du velger Workbook fra rullegardinmenyen, vil du legge merke til at to linjer med kode vises i kodevinduet. Når du har valgt hendelsen du vil ha koden for, kan du slette linjene som dukket opp som standard.

I vinduet Koder for brukerform

Når du oppretter UserForms i Excel, kan du også bruke UserForm -hendelser til å utføre koder basert på spesifikke handlinger. For eksempel kan du angi en kode som skal kjøres når du klikker på knappen.

Selv om arkobjektene og ThisWorkbook -objektene allerede er tilgjengelige når du åpner VB Editor, er UserForm noe du må opprette først.

For å opprette en UserForm, høyreklikk på et av objektene, gå til Sett inn og klikk på UserForm.

Dette vil sette inn et UserForm -objekt i arbeidsboken.

Når du dobbeltklikker på UserForm (eller et av objektene du legger til i UserForm), åpnes det kodevinduet for UserForm.

Nå, akkurat som regneark eller ThisWorkbook, kan du velge hendelsen, og den vil sette inn den første og den siste linjen for den hendelsen. Og så kan du legge til koden midt i den.

I vinduet Kartkode

I Excel kan du også sette inn diagramark (som er forskjellige fra regneark). Et diagramark er kun ment å inneholde diagrammer.

Når du har satt inn et diagramark, vil du kunne se diagramarkobjektet i VB Editor.

Du kan legge til hendelseskoden i vinduet for diagramarket, akkurat som vi gjorde i regnearket.

Dobbeltklikk på diagramarkobjektet i Project Explorer. Dette åpner kodevinduet for diagramarket.

Nå må du velge Chart fra rullegardinmenyen øverst til venstre i kodevinduet.

Etter at du har valgt Chart fra rullegardinmenyen, får du en liste over alle hendelsene knyttet til diagramarket. Du kan velge den du vil bruke fra rullegardinmenyen øverst til høyre i kodevinduet.

Merk: Så snart du velger Chart fra rullegardinmenyen, vil du legge merke til at to linjer med kode vises i kodevinduet. Når du har valgt hendelsen du vil ha koden for, kan du slette linjene som dukket opp som standard.

I klassemodul

Klassemoduler må settes inn akkurat som UserForms.

En klassemodul kan inneholde kode relatert til programmet - som ville være Excel selv og de innebygde diagrammene.

Jeg vil dekke klassemodulen som en egen opplæring i de kommende ukene.

Vær oppmerksom på at bortsett fra OnTime- og OnKey -hendelser, kan ingen av hendelsene ovenfor lagres i den vanlige VBA -modulen.

Forstå hendelsesrekkefølgen

Når du utløser en hendelse, skjer det ikke isolert. Det kan også føre til en sekvens av flere utløsere.

Når du for eksempel setter inn et nytt regneark, skjer følgende ting:

1. Et nytt regneark legges til
2. Det forrige regnearket blir deaktivert
3. Det nye regnearket blir aktivert

Selv om du i de fleste tilfeller ikke trenger å bekymre deg for sekvensen, er det bedre å kjenne sekvensen for å unngå uventede resultater hvis du oppretter komplekse koder som er avhengige av hendelser.

Forstå argumentenes rolle i VBA -hendelser

Før vi hopper til hendelseseksempler og de fantastiske tingene du kan gjøre med det, er det et viktig konsept jeg må dekke.

I VBA -hendelser vil det være to typer koder:

• Uten noen argumenter
• Med argumenter

Og i denne delen vil jeg raskt dekke argumentenes rolle.

Nedenfor er en kode som ikke har noe argument (parentesen er tom):

Private Sub Workbook_Open () MsgBox "Husk å fylle timelisten" Slutt Sub

Med koden ovenfor, når du åpner en arbeidsbok, viser den ganske enkelt en meldingsboks med meldingen - "Husk å fylle timelisten".

La oss nå se på en kode som har et argument.

Private Sub Workbook_NewSheet (ByVal Sh As Object) Sh.Range ("A1") = Sh.Name End Sub

Koden ovenfor bruker Sh -argumentet som er definert som en objekttype. Sh -argumentet kan være et regneark eller et diagramark, ettersom hendelsen ovenfor utløses når et nytt ark legges til.

Ved å tilordne det nye arket som er lagt til arbeidsboken til objektvariabelen Sh, har VBA gjort det mulig for oss å bruke det i koden. Så for å referere til det nye arkenavnet, kan jeg bruke Sh.Name.

Argumentbegrepet vil være nyttig når du går gjennom eksemplene på VBA -hendelser i de neste avsnittene.

Hendelser på arbeidsboknivå (forklart med eksempler)

Følgende er de mest brukte hendelsene i en arbeidsbok.

ARRANGEMENTSNAVN	HVA UTLØSER HENDELSEN
Aktiver	Når en arbeidsbok er aktivert
Etterspar	Når en arbeidsbok er installert som et tillegg
FørSpar	Når en arbeidsbok er lagret
FørLukk	Når en arbeidsbok er stengt
Før Skriv ut	Når en arbeidsbok skrives ut
Deaktiver	Når en arbeidsbok er deaktivert
Nytt ark	Når et nytt ark legges til
Åpen	Når en arbeidsbok åpnes
ArkAktiver	Når et ark i arbeidsboken er aktivert
SheetBeforeDelete	Når et ark slettes
SheetBeforeDoubleClick	Når et ark er dobbeltklikket
SheetBeforeRightClick	Når et ark er høyreklikket
Ark Beregn	Når et ark beregnes eller beregnes på nytt
Ark Deaktiver	Når en arbeidsbok er deaktivert
SheetPivotTableUpdate	Når en arbeidsbok er oppdatert
ArkValg Endre	Når en arbeidsbok endres
Vindu Aktiver	Når en arbeidsbok er aktivert
Vindu Deaktiver	Når en arbeidsbok er deaktivert

Vær oppmerksom på at dette ikke er en komplett liste. Du finner den komplette listen her.

Husk at koden for Workbook -hendelsen er lagret i ThisWorkbook -objektets kodevindu.

La oss ta en titt på noen nyttige arbeidsbokhendelser og se hvordan disse kan brukes i ditt daglige arbeid.

Arbeidsbok Åpent arrangement

La oss si at du vil vise brukeren en vennlig påminnelse om å fylle timelistene når de åpner en bestemt arbeidsbok.

Du kan bruke koden nedenfor for å gjøre dette:

Private Sub Workbook_Open () MsgBox "Husk å fylle timelisten" Slutt Sub

Så snart du åpner arbeidsboken som har denne koden, vil den vise deg en meldingsboks med den angitte meldingen.

Det er noen ting du må vite når du arbeider med denne koden (eller arbeidsbokhendelseskoder generelt):

• Hvis en arbeidsbok har en makro og du vil lagre den, må du lagre den i .XLSM -formatet. Ellers vil makrokoden gå tapt.
• I eksemplet ovenfor vil hendelseskoden bare bli utført når makroene er aktivert. Du kan se en gul stolpe som ber om tillatelse til å aktivere makroer. Til det er aktivert, kjøres ikke hendelseskoden.
• Arbeidsbokhendelseskoden plasseres i kodevinduet til ThisWorkbook -objektet.

Du kan avgrense denne koden ytterligere og vise meldingen bare på fredag.

Koden nedenfor vil gjøre dette:

Private Sub Workbook_Open () wkday = Weekday (Date) If wkday = 6 Da MsgBox "Husk å fylle timelisten" Slutt Sub

Vær oppmerksom på at i ukedagsfunksjonen tildeles søndag verdien 1, mandag er 2 og så videre.

Derfor har jeg brukt 6 for fredag.

Workbook Open -hendelse kan være nyttig i mange situasjoner, for eksempel:

• Når du vil vise en velkomstmelding til personen når en arbeidsbok åpnes.
• Når du vil vise en påminnelse når arbeidsboken åpnes.
• Når du alltid vil aktivere ett bestemt regneark i arbeidsboken når den åpnes.
• Når du vil åpne relaterte filer sammen med arbeidsboken.
• Når du vil registrere dato og klokkeslettstempel hver gang arbeidsboken åpnes.

Arbeidsbok NewSheet -hendelse

NewSheet -hendelsen utløses når du setter inn et nytt ark i arbeidsboken.

La oss si at du vil angi dato og klokkeslett i celle A1 i det nylig innsatte arket. Du kan bruke koden nedenfor for å gjøre dette:

Private Sub Workbook_NewSheet (ByVal Sh As Object) On Error Resume Next Sh.Range ("A1") = Format (Now, "dd-mmm-yyyy hh: mm: ss") End Sub

Koden ovenfor bruker 'On Error Resume Next' for å håndtere tilfeller der noen setter inn et diagramark og ikke et regneark. Siden diagramarket ikke har celle A1, vil det vise en feil hvis "On Error Resume Next" ikke brukes.

Et annet eksempel kan være når du vil bruke noen grunnleggende innstillinger eller formatering på et nytt ark så snart det er lagt til. For eksempel, hvis du vil legge til et nytt ark og vil at det automatisk skal få et serienummer (opptil 100), kan du bruke koden nedenfor.

Private Sub Workbook_NewSheet (ByVal Sh As Object) On Error Resume Next With Sh.Range ("A1") .Value = "S. No." .Interior.Color = vbBlue .Font.Color = vbWhite End With For i = 1 To 100 Sh.Range ("A1"). Offset (i, 0) .Value = i Next i Sh.Range ("A1", Range ("A1"). End (xlDown)). Borders.LineStyle = xlContinuous End Sub

Koden ovenfor formaterer også litt. Det gir toppcellen en blå farge og gjør skriften hvit. Det gjelder også en grense for alle de fylte cellene.

Koden ovenfor er et eksempel på hvordan en kort VBA -kode kan hjelpe deg med å stjele noen sekunder hver gang du setter inn et nytt regneark (i tilfelle dette er noe du må gjøre hver gang).

Arbeidsbok før lagring av hendelse

Før Lagre -hendelsen utløses når du lagrer en arbeidsbok. Vær oppmerksom på at hendelsen først utløses og deretter blir arbeidsboken lagret.

Når du lagrer en Excel -arbeidsbok, kan det være to mulige scenarier:

1. Du lagrer det for første gang, og det vil vise dialogboksen Lagre som.
2. Du har allerede lagret det tidligere, og det vil ganske enkelt lagre og overskrive endringene i den allerede lagrede versjonen.

La oss se på noen få eksempler der du kan bruke BeforeSave -hendelsen.

Anta at du har en ny arbeidsbok som du lagrer for første gang, og du vil minne brukeren om å lagre den i K -stasjonen, så kan du bruke koden nedenfor:

Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then MsgBox "Save this File in the K Drive" End Sub

I filen ovenfor, hvis filen aldri har blitt lagret, er SaveAsUI True og åpner dialogboksen Lagre som. Koden ovenfor vil vise meldingen før dialogboksen Lagre som vises.

Et annet eksempel kan være å oppdatere dato og klokkeslett da filen lagres i en bestemt celle.

Koden nedenfor vil sette inn dato og klokkeslettstempel i celle A1 i ark1 når filen lagres.

Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean) Worksheets ("Sheet1"). Range ("A1") = Format (Now, "dd-mmm-yyyy hh: mm: ss") End Sub

Vær oppmerksom på at denne koden kjøres så snart brukeren lagrer arbeidsboken. Hvis arbeidsboken blir lagret for første gang, vil den vise en Lagre som -dialogboks. Men koden er allerede utført når du ser dialogboksen Lagre som. På dette tidspunktet, hvis du bestemmer deg for å avbryte og ikke lagre arbeidsboken, vil dato og klokkeslett allerede være angitt i cellen.

Arbeidsbok førLukk hendelse

Før lukke -hendelsen skjer rett før arbeidsboken lukkes.

Koden nedenfor beskytter alle regnearkene før arbeidsboken lukkes.

Private Sub Workbook_BeforeClose (Cancel As Boolean) Dim sh As Worksheet For each sh In ThisWorkbook.Worksheets sh.Protect Next sh End Sub

Husk at hendelseskoden utløses så snart du lukker arbeidsboken.

En viktig ting å vite om denne hendelsen er at det ikke bryr seg om arbeidsboken faktisk er lukket eller ikke.

Hvis arbeidsboken ikke har blitt lagret og du får spørsmål om du vil lagre arbeidsboken eller ikke, og du klikker Avbryt, vil ikke arbeidsboken bli lagret.Hendelseskoden hadde imidlertid allerede blitt utført da.

Arbeidsbok før utskriftshendelse

Når du gir utskriftskommandoen (eller Utskriftsforhåndsvisning), blir hendelsen Før utskrift utløst.

Koden nedenfor vil beregne alle regnearkene på nytt før arbeidsboken skrives ut.

Private Sub Workbook_BeforePrint (Avbryt som boolsk) For hver ws i regneark ws.Beregn neste ws End Sub

Når brukeren skriver ut arbeidsboken, vil hendelsen bli utløst enten han/hun skriver ut hele arbeidsboken eller bare en del av den.

Et annet eksempel nedenfor er koden som vil legge til dato og klokkeslett i bunnteksten når arbeidsboken skrives ut.

Private Sub Workbook_BeforePrint (Avbryt som boolsk) Dim ws Som regneark for hver ws i ThisWorkbook.Worksheets ws.PageSetup.LeftFooter = "Printed On-" & Format (Now, "dd-mmm-yyyy hh: mm") Next ws End Sub

Hendelser på arbeidsarknivå (forklart med eksempler)

Arbeidsarkhendelser finner sted basert på utløserne i regnearket.

Følgende er de mest brukte hendelsene i et regneark.

Arrangementsnavn	Hva som utløser hendelsen
Aktiver	Når regnearket er aktivert
Før Slett	Før regnearket slettes
FørDobbeltklikk	Før regnearket dobbeltklikkes
BeforeRightClick	Før regnearket høyreklikkes
Regne ut	Før regnearket beregnes eller beregnes på nytt
Endring	Når cellene i regnearket endres
Deaktiver	Når regnearket er deaktivert
PivotTableUpdate	Når pivottabellen i regnearket oppdateres
Valg Endre	Når valget på regnearket endres

Vær oppmerksom på at dette ikke er en komplett liste. Du finner den komplette listen her.

Husk at koden for regnearkhendelsen er lagret i objektarkvinduet for regnearket (i den der du vil at hendelsen skal utløses). Det kan være flere regneark i en arbeidsbok, og koden din vil bare bli utløst når hendelsen finner sted i regnearket den er plassert i.

La oss nå se på noen nyttige regnearkhendelser og se hvordan disse kan brukes i det daglige arbeidet.

Regneark Aktiver hendelse

Denne hendelsen utløses når du aktiverer et regneark.

Koden nedenfor avbeskytter et ark så snart det er aktivert.

Private Sub Worksheet_Activate () ActiveSheet.Beskytt slutten Sub

Du kan også bruke denne hendelsen til å kontrollere at en bestemt celle eller et celleområde (eller et navngitt område) er valgt så snart du aktiverer regnearket. Koden nedenfor vil velge celle D1 så snart du aktiverer arket.

Private Sub Worksheet_Activate () ActiveSheet.Range ("D1"). Velg End Sub

Endring av regneark

En endringshendelse utløses hver gang du gjør en endring i regnearket.

Vel … ikke alltid.

Det er noen endringer som utløser hendelsen, og noen som ikke gjør det. Her er en liste over noen endringer som ikke vil utløse hendelsen:

• Når du endrer cellens formatering (skriftstørrelse, farge, kant, etc.).
• Når du slår sammen celler. Dette er overraskende, ettersom sammenslåing av celler også fjerner innhold fra alle cellene unntatt den øverst til venstre.
• Når du legger til, sletter eller redigerer en cellekommentar.
• Når du sorterer en rekke celler.
• Når du bruker Målsøk.

Følgende endringer vil utløse hendelsen (selv om du kanskje tror det ikke burde):

• Kopiering og liming av formatering vil utløse hendelsen.
• Slette formatering vil utløse hendelsen.
• Å kjøre en stavekontroll ville utløse hendelsen.

Nedenfor er en kode som viser en meldingsboks med adressen til cellen som er endret.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox "Du har nettopp endret" og Target.Address End Sub

Selv om dette er en ubrukelig makro, viser den deg hvordan du bruker Target -argumentet for å finne ut hvilke celler som er endret.

La oss nå se et par flere nyttige eksempler.

Anta at du har en rekke celler (la oss si A1: D10), og du vil vise en melding og spør brukeren om de virkelig ønsket å endre en celle i dette området eller ikke, du kan bruke koden nedenfor.

Den viser en melding med to knapper - Ja og Nei. Hvis brukeren velger ‘Ja’, er endringen gjort, ellers reverseres den.

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Du gjør en endring i cellene i A1: D10. Er du sikker på at du vil ha det?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = Falsk Application.Undo Application.EnableEvents = True End If End Sub

I koden ovenfor sjekker vi om målcellen er i de fire første kolonnene og de første 10 radene. Hvis det er tilfelle, vises meldingsboksen. Hvis brukeren også valgte Nei i meldingsboksen, blir endringen reversert (med Application.Undo -kommandoen).

Vær oppmerksom på at jeg har brukt Application.EnableEvents = False før Application.Undo -linjen. Og så reverserte jeg det ved å bruke Application.EnableEvent = True på neste linje.

Dette er nødvendig, da når angre skjer, utløser det også endringshendelsen. Hvis jeg ikke setter EnableEvent til False, vil det fortsette å utløse endringshendelsen.

Du kan også overvåke endringene i et navngitt område ved å bruke endringshendelsen. For eksempel, hvis du har et navngitt område kalt "DataRange" og du vil vise en melding i tilfelle brukeren gjør en endring i dette navngitte området, kan du bruke koden nedenfor:

Private Sub Worksheet_Change (ByVal Target As Range) Dim DRange As Range Set DRange = Range ("DataRange") If Not Intersect (Target, DRange) Is Nothing Then MsgBox "You just made a change to the Data Range" End If End Sub

Koden ovenfor kontrollerer om cellen/området der du har gjort endringene har noen celler som er felles for dataområdet. Hvis det gjør det, viser det meldingsboksen.

Valg av arbeidsbok Endre hendelse

Valgendringshendelsen utløses når det er en endring i regnearket.

Koden nedenfor vil beregne arket på nytt så snart du endrer valget.

Private Sub Worksheet_SelectionChange (ByVal Target As Range) Application.Calculate End Sub

Et annet eksempel på denne hendelsen er når du vil markere den aktive raden og kolonnen i den valgte cellen.

Noe som vist nedenfor:

Følgende kode kan gjøre dette:

Private Sub Worksheet_SelectionChange (ByVal Target As Range) Cells.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.Color = RGB (248, 203, 173) .EntireColumn.Interior.Color = RGB (180, 198, 231) Avslutt med Slutt Sub

Koden fjerner først bakgrunnsfargen fra alle cellene og bruker deretter den som er nevnt i koden på den aktive raden og kolonnen.

Og det er problemet med denne koden. At den fjerner farge fra alle cellene.

Hvis du vil markere den aktive raden/kolonnen mens du beholder fargen i andre celler intakt, bruker du teknikken vist i denne opplæringen.

Arbeidsbok DoubleClick -hendelse

Dette er en av mine favoritt regnearkhendelser, og du vil se mange opplæringsprogrammer der jeg har brukt dette (for eksempel denne eller denne).

Denne hendelsen utløses når du dobbeltklikker på en celle.

La meg vise deg hvor fantastisk dette er.

Med koden nedenfor kan du dobbeltklikke på en celle og den vil bruke en bakgrunnsfarge, endre skriftfargen og gjøre teksten i cellen fet;

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Cancel = True With Target .Interior.Color = vbBlue .Font.Color = vbWhite .Font.Bold = True End With End Sub

Dette kan være nyttig når du går gjennom en celleliste og vil markere noen få utvalgte. Selv om du kan bruke F4 -tasten til å gjenta det siste trinnet, vil den bare kunne bruke en formatering. Med denne dobbeltklikk-hendelsen kan du bruke alle tre med bare et dobbeltklikk.

Vær oppmerksom på at i koden ovenfor har jeg angitt verdien Cancel = True.

Dette gjøres slik at standardhandlingen for dobbeltklikk er deaktivert - det vil si å komme inn i redigeringsmodus. Med Cancel = True, ville Excel ikke få deg til å redigere modus når du dobbeltklikker på cellen.

Her er et annet eksempel.

Hvis du har en gjøremålsliste i Excel, kan du bruke dobbeltklikkhendelse til å bruke gjennomsøkingsformatet for å merke oppgaven som fullført.

Noe som vist nedenfor:

Her er koden som gjør dette:

Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Cancel = True CurrFormat = Target.Font.Strikethrough If CurrFormat Then Target.Font.Strikethrough = False Else Target.Font.Strikethrough = True End If End Sub

Vær oppmerksom på at i denne koden har jeg dobbeltklikket som en byttehendelse. Når du dobbeltklikker på en celle, sjekker den om formatet for gjennomslag allerede er brukt. Hvis det har vært det, dobbeltklikker du for å fjerne gjennombruddsformatet, og hvis det ikke har vært det, blir gjennomføringsformatet brukt.

Excel VBA OnTime -hendelse

Hendelsene vi har sett så langt i denne artikkelen var knyttet til et av Excel -objektene, det være seg arbeidsboken, regnearket, diagramarket eller UserForms, etc.

OnTime -hendelsen er annerledes enn andre hendelser, da den kan lagres i den vanlige VBA -modulen (mens de andre skulle plasseres i kodevinduet for objekter som ThisWorkbook eller Worksheets eller UserForms).

Innenfor den vanlige VBA -modulen brukes den som en metode for applikasjonsobjektet.

Grunnen til at dette regnes som en hendelse er at det kan utløses basert på tiden du angir. For eksempel, hvis jeg vil at arket skal beregnes på nytt hvert 5. minutt, kan jeg bruke OnTime -hendelsen til det.

Eller hvis jeg vil vise en melding/påminnelse på et bestemt tidspunkt på dagen, kan jeg bruke OnTime -hendelsen.

Nedenfor er en kode som viser en melding klokken 14.00 hver dag.

Sub MessageTime () Application.OnTime TimeValue ("14:00:00"), "ShowMessage" End Sub Sub ShowMessage () MsgBox "It's Lunch Time" End Sub

Husk at du må plassere denne koden i den vanlige VBA -modulen,

Selv om OnTime -hendelsen ville bli utløst på det angitte tidspunktet, må du kjøre makroen manuelt når som helst. Når du har kjørt makroen, venter den til klokken er 14.00 og kaller deretter 'ShowMessage' -makroen.

ShowMessage -makroen viser deretter meldingen.

OnTime -hendelsen tar fire argumenter:

Application.OnTime (Tidligste tid, Fremgangsmåte, Siste tid, Rute)

• Tidligste tid: Tidspunktet da du vil kjøre prosedyren.
• Fremgangsmåte: Navnet på prosedyren som skal kjøres.
• Siste tid (valgfritt): Hvis en annen kode kjører og den angitte koden ikke kan kjøres på det angitte tidspunktet, kan du angi den siste tiden som den skal vente på. For eksempel kan det være EarliestTime + 45 (som betyr at det vil vente i 45 sekunder før den andre prosedyren er fullført). Hvis prosedyren ikke kan kjøres etter 45 sekunder, blir den forlatt. Hvis du ikke spesifiserer dette, ville Excel vente til koden kan kjøres, og deretter kjøre den.
• Tidsplan (valgfritt): Hvis den er satt til True, planlegger den en ny tidsprosedyre. Hvis False, avbryter den tidligere angitte prosedyren. Som standard er dette True.

I eksemplet ovenfor brukte vi bare de to første argumentene.

La oss se på et annet eksempel.

Koden nedenfor vil oppdatere regnearket hvert 5. minutt.

Dim NextRefresh as Date Sub RefreshSheet () ThisWorkbook.Worksheets ("Sheet1"). Calculate NextRefresh = Now + TimeValue ("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End Sub Sub StopRefresh () On Error Resume Next Application.OnTime NextRefresh, "RefreshSheet",, False End Sub

Koden ovenfor vil oppdatere regnearket hvert 5. minutt.

Den bruker nå -funksjonen til å bestemme gjeldende tid og legger deretter til 5 minutter til gjeldende tid.

OnTime -hendelsen vil fortsette å kjøre til du stopper den. Hvis du lukker arbeidsboken og Excel -programmet fortsatt kjører (andre arbeidsbøker er åpne), åpnes arbeidsboken som har OnTime -hendelsen i seg igjen.

Dette håndteres bedre ved å spesifikt stoppe OnTime -hendelsen.

I koden ovenfor har jeg StopRefresh -koden, men du må utføre den for å stoppe OnTime -hendelsen. Du kan gjøre dette manuelt, tilordne det til en knapp og gjøre dette ved å trykke på knappen eller ringe det fra Workbook Close -hendelsen.

Private Sub Workbook_BeforeClose (Cancel As Boolean) Call StopRefresh End Sub

Ovenstående 'BeforeClose' hendelseskode går i ThisWorkbook -kodevinduet.

Excel VBA OnKey -hendelse

Når du jobber med Excel, fortsetter det å overvåke tastetrykkene du bruker. Dette lar oss bruke tastetrykk som utløser for en hendelse.

Med OnKey -hendelsen kan du angi et tastetrykk (eller en kombinasjon av tastetrykk) og koden som skal kjøres når det tastetrykket brukes. Når du trykker på disse tastetrykk, vil den utføre koden for den.

På samme måte som OnTime -hendelsen, må du ha en måte å avbryte OnKey -hendelsen. Når du angir OnKey -hendelsen for et bestemt tastetrykk, blir den også tilgjengelig i alle de åpne arbeidsbøkene.

Før jeg viser deg et eksempel på bruk av OnKey -hendelsen, la meg først dele nøkkelkodene som er tilgjengelige for deg i VBA.

NØKKEL	KODE
Tilbake	{BACKSPACE} eller {BS}
Gå i stykker	{GÅ I STYKKER}
Caps Lock	{CAPSLOCK}
Slett	{DELETE} eller {DEL}
Pil ned	{NED}
Slutt	{SLUTT}
Tast inn	~
Enter (på det numeriske tastaturet)	{TAST INN}
Flukt	{ESCAPE} eller {ESC}
Hjem	{HJEM}
Ins	{SETT INN}
Venstre pil	{VENSTRE}
NumLock	{NUMLOCK}
Bla ned	{PGDN}
Side opp	{PGUP}
Høyre pil	{IKKE SANT}
Rullelås	{SCROLLOCK}
Tab	{TAB}
Pil opp	{OPP}
F1 til F15	{F1} til {F15}

Når du trenger å bruke en hvilken som helst onkey -hendelse, må du bruke koden for den.

Tabellen ovenfor har kodene for enkle tastetrykk.

Du kan også kombinere disse med følgende koder:

• Skifte: + (Pluss -tegn)
• Kontroll: ^ (Caret)
• Alt: % (Prosentdel)

For eksempel, for Alt F4, må du bruke koden: "%{F4}” - der % er for ALT -tasten og {F4} er for F4 -nøkkelen.

La oss nå se på et eksempel (husk at koden for OnKey -hendelser er plassert i den vanlige VBA -modulen).

Når du trykker på PageUp eller PageDown -tasten, hopper den 29 rader over/under den aktive cellen (det er i hvert fall det den gjør på den bærbare datamaskinen min).

Hvis du vil at den bare skal hoppe over 5 rader om gangen, kan du bruke koden nedenfor:

Sub PageUpDOwnKeys () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub Sub PageUpMod () On Error Resume Next ActiveCell.Offset (-5, 0) .Activate End Sub Sub PageDownMod () På Feil Fortsett Neste ActiveCell.Offset (5, 0) .Aktiver Slutt Sub

Når du kjører den første delen av koden, kjører den OnKey -hendelsene. Når dette er utført, ville bruk av PageUp og PageDown -tasten bare få markøren til å hoppe 5 rader om gangen.

Vær oppmerksom på at vi har brukt 'On Error Resume Next' for å sikre at feil blir ignorert. Disse feilene kan oppstå når du trykker på PageUp -tasten, selv når du er øverst i regnearket. Siden det ikke er flere rader å hoppe, vil koden vise en feil. Men siden vi har brukt ‘On Error Resume Next’, blir det ignorert.

For å sikre at disse OnKey -hendelsene er tilgjengelige, må du kjøre den første delen av koden. Hvis du vil at dette skal være tilgjengelig så snart du åpner arbeidsboken, kan du plassere dette i ThisWorkbook -kodevinduet.

Private Sub Workbook_Open () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub

Koden nedenfor vil returnere nøklene til normal funksjonalitet.

Sub Cancel_PageUpDownKeysMod () Application.OnKey "{PgUp}" Application.OnKey "{PgDn}" End Sub

Når du ikke angir det andre argumentet i OnKey -metoden, vil tastetrykket gå tilbake til den vanlige funksjonaliteten.

Hvis du vil avbryte funksjonaliteten til et tastetrykk, slik at Excel ikke gjør noe når tastetrykket brukes, må du bruke en tom streng som det andre argumentet.

I koden nedenfor ville Excel ikke gjort noe når vi bruker PageUp- eller PageDown -tastene.

Sub Ignore_PageUpDownKeys () Application.OnKey "{PgUp}", "" Application.OnKey "{PgDn}", "" End Sub

Deaktivering av hendelser i VBA

Noen ganger må du kanskje deaktivere hendelser for å få koden til å fungere skikkelig.

Anta for eksempel at jeg har et område (A1: D10), og jeg vil vise en melding når en celle endres i dette området. Så jeg viser en meldingsboks og spør brukeren om de er sikre på at de vil gjøre endringen. Hvis svaret er Ja, blir endringen gjort, og hvis svaret er Nei, ville VBA angre det.

Du kan bruke koden nedenfor:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Du gjør en endring i cellene i A1: D10. Er du sikker på at du vil ha det?", vbYesNo) End If If Ans = vbNo Then Application.Ondo End If End Sub

Problemet med denne koden er at når brukeren velger Nei i meldingsboksen, blir handlingen reversert (som jeg har brukt Application.Undo).

Når angre skjer og verdien endres tilbake til den opprinnelige, utløses VBA -endringshendelsen igjen, og brukeren vises igjen den samme meldingsboksen.

Dette betyr at du kan fortsette å klikke NEI i meldingsboksen, og det vil fortsette å dukke opp. Dette skjer ettersom du har sittet fast i den uendelige løkken i dette tilfellet.

For å unngå slike tilfeller må du deaktivere hendelser slik at endringshendelsen (eller en annen hendelse) ikke utløses.

Følgende kode vil fungere godt i dette tilfellet:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Du gjør en endring i cellene i A1: D10. Er du sikker på at du vil ha det?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = Falsk Application.Undo Application.EnableEvents = True End If End Sub

I koden ovenfor, rett over Application.Undo -linjen, har vi brukt - Application.EnableEvents = False.

Å sette EnableEvents til False ville ikke utløse noen hendelse (i gjeldende eller åpne arbeidsbøker).

Når vi har fullført angreoperasjonen, kan vi bytte tilbake EnableEvents -egenskapen til True.

Husk at deaktivering av hendelser påvirker alle arbeidsbøkene som åpnes (eller åpnes mens EnableEvents er satt til False). For eksempel, som en del av koden, hvis du åpner en ny arbeidsbok, fungerer ikke Workbook Open -hendelsen.

Virkningen av hendelser Angre stabel

La meg først fortelle deg hva en Angre stabel er.

Når du jobber i Excel, fortsetter det å overvåke handlingene dine. Når du gjør en feil, kan du alltid bruke Ctrl + Z for å gå tilbake til forrige trinn (dvs. angre din nåværende handling).

Hvis du trykker Control + Z to ganger, tar det deg to trinn tilbake. Disse trinnene du har utført, lagres som en del av Angre -bunken.

Enhver hendelse som endrer regnearket ødelegger denne Angre -bunken.Dette betyr at hvis jeg har gjort 5 ting før jeg utløser en hendelse, vil jeg ikke kunne bruke Control + Z for å gå tilbake til de foregående trinnene. Å starte hendelsen har ødelagt den bunken for meg.

I koden nedenfor bruker jeg VBA til å angi tidsstempelet i celle A1 når det er en endring i regnearket.

Private Sub Worksheet_Change (ByVal Target As Range) Application.EnableEvents = Falsk Range ("A1"). Value = Format (Now, "dd-mmm-åååå hh: mm: ss") Application.EnableEvents = True End Sub

Siden jeg gjør en endring i regnearket, vil dette ødelegge angre -bunken.

Vær også oppmerksom på at dette ikke bare er begrenset til arrangementer.

Hvis du har en kode som er lagret i en vanlig VBA -modul, og du gjør en endring i regnearket, ville det også ødelegge angre -bunken i Excel.

For eksempel skriver koden nedenfor bare inn teksten "Hei" i celle A1, men selv å kjøre dette ville ødelegge angre -bunken.

Sub TypeHello () Range ("A1"). Value = "Hello" End Sub

Du kan også like følgende Excel VBA -opplæringsprogrammer:

• Arbeider med celler og områder i Excel VBA.
• Arbeider med regneark i Excel VBA.
• Arbeider med arbeidsbøker i Excel VBA.
• Excel VBA Loops - Den ultimate guiden.
• Bruke IF Then Else Statement i Excel VBA.
• For Next Loop i Excel.
• Opprette brukerdefinerte funksjoner i Excel VBA.
• Hvordan lage og bruke tillegg i Excel.
• Lag og gjenbruk makroer ved å lagre i Personal Macro Workbook.