Händelser händer hela tiden när en användare öppnar en Excel -arbetsbok och börjar göra olika åtgärder som att mata in data i celler eller flytta mellan ark
Inom Visual Basic Editor (ALT+F11) är subrutiner redan inställda som kan avfyras när användaren gör något t.ex. mata in data i en cell. Delrutinen tillhandahåller ingen åtgärdskod, bara en 'Sub' -sats och en' End Sub' -sats utan kod mellan dem. De är i själva verket vilande så ingenting händer förrän du anger en kod.
Här är ett exempel baserat på "Ändra" -händelsen i ett kalkylblad:
Som VBA -programmerare kan du lägga till kod för att få vissa saker att hända när användaren vidtar en specifik åtgärd. Detta ger dig chansen att kontrollera användaren och förhindra att de vidtar åtgärder som du inte vill att de ska göra och som kan skada din arbetsbok. Du kan till exempel vilja att de sparar sin egen individuella kopia av arbetsboken under ett annat namn, så att de inte påverkar originalet, som kan användas av ett antal användare.
Om de stänger arbetsboken uppmanas de automatiskt att spara sina ändringar. Arbetsboken har dock en "BeforeClose" -händelse och du kan ange kod för att förhindra att arbetsboken stängs och en "Save" -händelse avfyras. Du kan sedan lägga till en knapp i själva kalkylbladet och lägga din egen "Spara" -rutin på den. Du kan också inaktivera rutan "Spara" med händelsen "BeforeSave"
En förståelse för hur händelser fungerar är absolut nödvändigt för en VBA -programmerare.
Typer av evenemang
Arbetsbok evenemang - dessa händelser avfyras baserat på vad användaren gör med själva arbetsboken. De inkluderar användaråtgärder som att öppna arbetsboken, stänga arbetsboken, spara arbetsboken, lägga till eller ta bort blad
Arbetsbladshändelser - dessa händelser avfyras av en användare som vidtar åtgärder på ett specifikt kalkylblad. Varje kalkylblad i arbetsboken har en individuell kodmodul, som innehåller olika händelser specifikt för det kalkylbladet (inte för alla kalkylblad). Dessa inkluderar användaråtgärder som att ändra innehållet i en cell, dubbelklicka på en cell eller högerklicka på en cell.
Aktiva X -kontrollhändelser - Aktiva X -kontroller kan läggas till i ett kalkylblad med ikonen "Infoga" på fliken "Utvecklare" i Excel -bandet. Dessa är ofta knappkontroller för att göra det möjligt för användaren att vidta olika åtgärder under kontroll av din kod, men de kan också vara objekt som nedrullning. Att använda Active X -kontroller i motsats till formulärkontroller i kalkylbladet ger ett helt utrymme för programmerbarhet. Aktiva X -kontroller ger dig mycket mer flexibilitet ur programmeringssynpunkt när du använder formulärkontroller i ett kalkylblad.
Du kan till exempel ha två rullgardinsmenyer i ditt kalkylblad. Du vill att den tillgängliga listan i den andra rullgardinsmenyn ska baseras på vad användaren valde i den första rullgardinsmenyn. Med hjälp av "Ändra" -händelsen på den första rullgardinsmenyn kan du skapa kod för att läsa vad användaren har valt och sedan uppdatera den andra rullgardinsmenyn. Du kan också avaktivera den andra rullgardinsmenyn tills användaren har gjort ett val i den första rullgardinsmenyn
UserForm -händelser - Du kan infoga och designa ett professionellt utseende som kan användas som popup-fönster. Alla kontroller som du placerar i formuläret är Active X -kontroller och de har samma händelser som Active X -kontrollerna som du kan placera i ett kalkylblad
Diagramhändelser - Dessa händelser är endast relaterade till ett diagramblad och inte till ett diagram som visas som en del av ett kalkylblad. Dessa händelser inkluderar att ändra storlek på diagrammet eller välja diagram.
Applikationshändelser - Dessa använder applikationsobjektet i VBA. Exempel skulle tillåta att koden avfyras när en viss knapp trycks ned eller när en viss tid har nåtts. Du kan programmera en situation där arbetsboken är öppen dygnet runt och den importerar data från en extern källa över natten vid en förutbestämd tidpunkt.
Faror med att använda kod i händelser
När du skriver kod för att göra något när användaren vidtar en viss åtgärd måste du komma ihåg att din kod kan utlösa andra händelser, vilket kan sätta din kod i en kontinuerlig loop.
Anta till exempel att du använder "Ändra" -händelsen i ett kalkylblad så att när användaren lägger in ett värde i en cell placeras en beräkning baserad på den cellen i cellen omedelbart till höger om den.
Problemet här är att placeringen av det beräknade värdet i cellen utlöser en annan "Change" -händelse, som sedan i sin tur utlöser ännu en "Change" -händelse, och så vidare tills din kod har tagit slut på kolumner att använda och kastar upp ett felmeddelande.
Du måste tänka noga när du skriver koden för händelsen för att säkerställa att andra händelser inte utlöses av misstag
Inaktivera händelser
Du kan använda koden för att inaktivera händelser för att komma runt detta problem. Vad du behöver göra är att införliva kod för att inaktivera händelser medan din händelsekod körs och sedan återaktivera händelser i slutet av koden. Här är ett exempel på hur du gör det:
| 1234 | Sub DisableEvents ()Application.EnableEvents = FalsktApplication.EnableEvents = TrueAvsluta Sub |
Tänk på att detta inaktiverar alla händelser i Excel -programmet, så detta påverkar även andra funktioner i Excel. Om du använder detta av någon anledning, se till att händelserna slås på igen efteråt.
Betydelsen av parametrar i händelser
Händelser har vanligtvis parametrar som du kan använda för att ta reda på mer om vad användaren gör och cellplatsen de befinner sig i.
Till exempel ser arbetshändelseshändelsen ut så här:
| 1 | Private Sub Worksheet_Change (ByVal Target As Range) |
Genom att använda intervallobjektet kan du ta reda på cellrad/kolumnkoordinater som användaren faktiskt befinner sig i.
| 1234 | Private Sub Worksheet_Change (ByVal Target As Range)MsgBox Target.ColumnMsgBox Target.RowAvsluta Sub |
Om du bara vill att din kod ska fungera på ett visst kolumn- eller radnummer, lägger du till ett villkor som lämnar delrutan om kolumnen inte är den som krävs.
| 123 | Private Sub Worksheet_Change (ByVal Target As Range)Om Target.Column 2 Avsluta sedan SubAvsluta Sub |
Detta löser problemet med att din kod utlöser flera händelser, eftersom det bara fungerar om användaren har ändrat en cell i kolumn 2 (kolumn B)
Arbetsboksevenemangsexempel (inte uttömmande)
Arbetsbokshändelserna finns under objektet "ThisWorkbook" i VBE Project Explorer. Du måste välja "Workbook" i den första rullgardinsmenyn i kodfönstret och sedan den andra rullgardinsmenyn visar dig alla tillgängliga händelser
Workbook Open Event
Denna händelse avaktiveras när arbetsboken öppnas av en användare. Du kan använda det för att skicka ett välkomstmeddelande till en användare genom att fånga deras användarnamn
| 123 | Private Sub Workbook_Open ()MsgBox "Välkommen" och Application.UserNameAvsluta Sub |
Du kan också kontrollera deras användarnamn mot en lista på ett dolt blad för att se om de har behörighet att komma åt arbetsboken. Om de inte är en auktoriserad användare kan du visa ett meddelande och stänga arbetsboken så att de inte kan använda det.
Arbetsbok Nytt bladhändelse
Den här händelsen utlöses när en användare lägger till ett nytt blad i arbetsboken
Du kan använda den här koden för att bara tillåta dig själv att lägga till ett nytt blad, istället för att olika användare lägger till ark och gör en röra i arbetsboken
| 1234567 | Private Sub Workbook_NewSheet (ByVal Sh As Object)Application.DisplayAlerts = FalsktOm Application.UserName "Richard" DåSh.DeleteAvsluta omApplication.DisplayAlerts = TrueAvsluta Sub |
Observera att du måste stänga av varningarna eftersom en användarvarning visas när arket raderas vilket gör att användaren kan kringgå din kod. Se till att du aktiverar varningarna igen efteråt!
Trött på att söka efter exempel på VBA -kod? Prova AutoMacro!
Arbetsbok Innan du sparar händelse
Denna händelse utlöses när användaren klickar på "Spara" -ikonen, men innan "Spara" faktiskt sker
Som beskrivits tidigare kanske du vill förhindra att användare sparar sina ändringar i den ursprungliga arbetsboken och tvinga dem att skapa en ny version med en knapp på kalkylbladet. Allt du behöver göra är att ändra parametern ‘Avbryt’ till True, och arbetsboken kan aldrig sparas med den konventionella metoden.
| 123 | Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean)Avbryt = SantAvsluta Sub |
Arbetsbok före stängningshändelse
Du kan använda den här händelsen för att förhindra att användare stänger av arbetsboken och igen tvinga dem att avsluta genom en kalkylbladsknapp. Återigen ställer du in parametern "Avbryt" till "Sant". Det röda X i det övre högra hörnet av Excel-fönstret fungerar inte längre.
| 123 | Private Sub Workbook_BeforeClose (Avbryt som booleskt)Avbryt = SantAvsluta Sub |
Exempel på arbetsbladshändelser (inte uttömmande)
Arbetsbladshändelserna finns under det specifika bladnamnobjektet i VBE Project Explorer. Du måste välja "Arbetsblad" i den första rullgardinsmenyn i kodfönstret och sedan den andra rullgardinsmenyn visar alla tillgängliga händelser
Ändring av arbetsblad
Den här händelsen utlöses när en användare gör en ändring i ett kalkylblad, till exempel genom att ange ett nytt värde i en cell
Du kan använda den här händelsen för att lägga till ett ytterligare värde eller en kommentar bredvid den ändrade cellen, men som diskuterats tidigare vill du inte börja sätta igång en loop av händelser.
| 12345 | Private Sub Worksheet_Change (ByVal Target As Range)Om Target.Column 2 Avsluta sedan SubActiveSheet.Cells (Target.Row, Target.Column + 1). Värde = _ActiveSheet.Cells (Target.Row, Target.Column). Värde * 1.1Avsluta Sub |
I det här exemplet fungerar koden bara om värdet anges i kolumn B (kolumn 2). Om detta är sant kommer det att lägga till 10% till antalet och placera det i nästa tillgängliga cell
Arbetsblad före dubbelklickhändelse
Denna händelse avfyrar koden om en användare dubbelklickar på en cell. Detta kan vara oerhört användbart för finansiella rapporter som en balansräkning eller resultaträkning där siffror sannolikt kommer att utmanas av chefer, särskilt om slutresultatet är negativt!
Du kan använda det här för att tillhandahålla en nedgraderingsfunktion, så att när chefen utmanar ett visst nummer är det bara att dubbelklicka på numret och uppdelningen visas som en del av rapporten.
Detta är mycket imponerande ur en användares synvinkel och sparar dem ständigt frågar "varför är detta antal så högt?"
Du måste skriva kod för att ta reda på rubriken / kriterierna för numret (med hjälp av objektobjektets egenskaper) och sedan filtrera tabelldata och sedan kopiera den till rapporten.
VBA -programmering | Kodgenerator fungerar för dig!
Arbetsblad Aktivera händelse
Denna händelse inträffar när användaren flyttar från ett ark till ett annat. Det gäller det nya bladet som användaren flyttar till.
Det kan användas för att säkerställa att det nya bladet är helt beräknat innan användaren börjar göra något på det. Det kan också användas för att bara beräkna det särskilda bladet utan att beräkna hela arbetsboken igen. Om arbetsboken är stor och har en komplicerad formel, sparar det mycket tid att omberäkna ett ark
| 123 | Private Sub Worksheet_Activate ()ActiveSheet.CalculateAvsluta Sub |
Aktiva X -kontrollhändelser (inte uttömmande)
Som diskuterats tidigare kan du lägga till Active X -kontroller direkt på ett kalkylblad. Dessa kan vara kommandoknappar, listrutor och listrutor
Active X -händelserna finns under det specifika bladnamnobjektet (där du lade till kontrollen) i VBE Project Explorer. Du måste välja namnet på Active X -kontrollen i den första rullgardinsmenyn i kodfönstret och sedan den andra rullgardinsmenyn visar alla tillgängliga händelser
Kommandoknapp Klicka på händelse
När du har lagt en kommandoknapp på ett kalkylblad vill du att den ska vidta åtgärder. Du gör detta genom att sätta kod på Click -händelsen.
Du kan enkelt sätta ett "Är du säker meddelande?" På detta så att en kontroll görs innan din kod körs
| 12345 | Privat underkommandoButton1_Click ()Dim ButtonRet Som variantButtonRet = MsgBox ("Är du säker på att du vill göra det här?", VbQuestion Eller vbYesNo)If ButtonRet = vbNo Stäng sedan SubAvsluta Sub |
Drop Down (kombinationsruta) Ändra händelse
En Active X-rullgardinsmeny har en ändringshändelse, så att om en användare väljer ett visst objekt från listrutan kan du fånga deras val med hjälp av den här händelsen och sedan skriva kod för att anpassa andra delar av arket eller arbetsboken i enlighet därmed.
| 123 | Private Sub ComboBox1_Change ()MsgBox "Du har valt" & ComboBox1.TextAvsluta Sub |
VBA -programmering | Kodgenerator fungerar för dig!
Kryssruta (kryssruta) Klicka på Händelse
Du kan lägga till en bock eller kryssruta i ett kalkylblad för att ge alternativ för användaren. Du kan använda klickhändelsen på den för att se om användaren har ändrat något på detta. Värdena som returneras är sanna eller falska beroende på om det har kryssats eller inte.
| 123 | Privat under CheckBox1_Click ()MsgBox CheckBox1.VärdeAvsluta Sub |
UserForm -händelser (inte uttömmande)
Excel ger dig möjlighet att designa dina egna formulär. Dessa kan vara mycket användbara att använda som popup-fönster för att samla in information eller för att ge flera val till användaren. De använder Active X -kontroller som beskrivits tidigare och har exakt samma händelser, även om händelserna beror mycket på typen av kontroll.
Här är ett exempel på en enkel form:
Så här ser det ut på skärmen när det visas
Du skulle använda händelser i formuläret för att göra saker som att ange ett standardföretagsnamn när formuläret öppnas, för att kontrollera att företagsnamnsinmatningen godkänner en som redan finns i kalkylarket och inte har stavats fel och för att lägga till kod i klick händelser på knapparna "OK" och "Avbryt"
Koden och händelserna bakom formuläret kan ses genom att dubbelklicka var som helst på formuläret
Den första rullgardinsmenyn ger tillgång till alla kontroller i formuläret. Den andra rullgardinsmenyn ger tillgång till händelserna
UserForm Activate Event
Denna händelse utlöses när formuläret är aktiverat, normalt när det visas. Denna händelse kan användas för att ställa in standardvärden t.ex. ett standardföretagsnamn i textrutan för företagsnamn
| 123 | Private Sub UserForm_Activate ()TextBox1.Text = "Mitt företagsnamn"Avsluta Sub |
VBA -programmering | Kodgenerator fungerar för dig!
Ändra händelse
De flesta kontrollerna på formuläret har en ändringshändelse, men i det här exemplet kan textrutan för företagsnamn använda händelsen för att begränsa längden på det företagsnamn som anges
| 123456 | Private Sub TextBox1_Change ()Om Len (TextBox1.Text)> 20 DåMsgBox "Namnet är begränsat till 20 tecken", vbCriticalTextBox1.Text = ""Avsluta omAvsluta Sub |
Klicka på Händelse
Du kan använda den här händelsen för att vidta åtgärder från att användaren klickar på kontrollerna i formuläret, eller till och med själva formuläret
På det här formuläret finns en "OK" -knapp, och efter att ha samlat ett företagsnamn skulle vi vilja placera det i en cell i kalkylarket för framtida referens
| 1234 | Privat underkommandoButton1_Click ()ActiveSheet.Range ("A1"). Value = TextBox1.TextJag. DöljAvsluta Sub |
Denna kod fungerar när användaren klickar på "OK" -knappen. Det lägger värdet i inmatningsrutan för företagsnamn i cell A1 på det aktiva arket och döljer sedan formuläret så att användarkontroll återgår till kalkylbladet.
Diagramhändelser
Karthändelser fungerar bara på diagram som finns på ett separat diagramark, och inte på ett diagram som ingår i ett vanligt kalkylblad
Diagramhändelser är något begränsade och kan inte användas på ett kalkylblad där du mycket väl kan ha flera diagram. Dessutom vill användarna inte nödvändigtvis byta från ett kalkylblad som innehåller siffror till ett diagramark - det finns ingen omedelbar visuell påverkan här
Den mest användbara händelsen skulle vara att ta reda på komponenten i ett diagram som en användare har klickat på t.ex. ett segment i ett cirkeldiagram eller en stapel i ett stapeldiagram, men det här är inte en händelse som är tillgänglig på standardhändelserna.
Detta problem kan lösas genom att använda en klassmodul för att lägga till en "Mouse Down" -händelse som returnerar detaljer om diagramkomponenten som användaren har klickat på. Detta används på ett diagram i ett kalkylblad.
Detta innebär mycket komplicerad kodning, men resultaten är spektakulära. Du kan skapa borrningar t.ex. användaren klickar på ett cirkeldiagrams segment och omedelbart är det diagrammet dolt och ett andra diagram visas i stället som visar ett cirkeldiagram med detaljer för det ursprungliga segmentet, eller så kan du ta fram tabelldata som stöder det segmentet i cirkeldiagrammet.
Applikationshändelser
Du kan använda applikationsobjektet i VBA för att avfyra kod enligt en viss händelse
VBA -programmering | Kodgenerator fungerar för dig!
Application.OnTime
Detta kan göra att du kan avfyra en kodbit med jämna mellanrum så länge arbetsboken laddas in i Excel. Du kanske vill spara din arbetsbok automatiskt i en annan mapp var 10: e minut eller lämna kalkylbladet igång över natten för att hämta de senaste data från en extern källa.
I det här exemplet anges en delrutin i en modul. Den visar en meddelanderuta var 5: e minut, även om detta lätt kan vara en annan kodad procedur. Samtidigt återställs timern till aktuell tid plus ytterligare 5 minuter.
Varje gång den körs återställs timern för att köra samma underrutin på ytterligare 5 minuter.
| 1234 | Sub TestOnTime ()MsgBox "Testar i tid"Application.OnTime (Now () + TimeValue ("00:05:00")), "TestOnTime"Avsluta Sub |
Application.OnKey
Med den här funktionen kan du designa dina egna snabbtangenter. Du kan få valfri tangentkombination att ringa till en underrutin för ditt skapande.
I det här exemplet omdirigeras bokstaven 'a' så att den i stället för att placera ett 'a' i en cell visar en meddelanderuta. Denna kod måste placeras i en insatt modul.
| 123456 | Sub TestKeyPress ()Application.OnKey "a", "TestKeyPress"Avsluta SubSub TestKeyPress ()MsgBox "Du tryckte på 'a'"Avsluta Sub |
Du kör först rutinen "TestKeyPress". Du behöver bara köra detta en gång. Det berättar för Excel att varje gång bokstaven 'a' trycks kommer det att kalla delrutinen 'TestKeyPress'. Underrutinen 'TestKeyPress' visar bara en meddelanderuta som berättar att du tryckte på knappen 'a'. Det kan naturligtvis ladda ett formulär eller göra alla möjliga andra saker.
Du kan använda valfri tangentkombination som du kan använda med funktionen ‘SendKeys’
För att avbryta den här funktionen kör du "OnKey" -uttalningen utan parametern "Procedure".
| 123 | Sub CancelOnKey ()Application.OnKey "a"Avsluta Sub |
Allt är nu normalt igen.
