Evenimentele se întâmplă tot timpul când un utilizator deschide un registru de lucru Excel și începe să facă diverse acțiuni, cum ar fi introducerea datelor în celule sau deplasarea între foi
În cadrul Editorului Visual Basic (ALT + F11), sunt deja configurate sub rutine care pot fi declanșate atunci când utilizatorul face ceva de ex. introducerea datelor într-o celulă. Sub rutina nu oferă niciun cod de acțiune, doar o instrucțiune „Sub” și o instrucțiune „End Sub” fără cod între ele. Sunt în mod efectiv latente, așa că nu se întâmplă nimic până nu introduceți un cod.
Iată un exemplu bazat pe evenimentul „Schimbă” dintr-o foaie de lucru:
În calitate de programator VBA, puteți adăuga cod pentru a face anumite lucruri să se întâmple atunci când utilizatorul face o acțiune specifică. Acest lucru vă oferă șansa de a controla utilizatorul și de a-l împiedica să ia măsuri pe care nu doriți să le facă și care ar putea deteriora registrul de lucru. De exemplu, vă recomandăm să-și salveze propria copie individuală a registrului de lucru sub un alt nume, astfel încât să nu afecteze originalul, care poate fi folosit de un număr de utilizatori.
Dacă închid registrul de lucru, atunci li se va solicita automat să-și salveze modificările. Cu toate acestea, registrul de lucru are un eveniment „BeforeClose” și puteți introduce codul pentru a preveni închiderea registrului de lucru și declanșarea unui eveniment „Save”. Apoi, puteți adăuga un buton la foaia de lucru în sine și puteți pune propria rutină „Salvați” pe ea. De asemenea, puteți dezactiva rutina „Salvați” utilizând evenimentul „Înainte de a salva”
Înțelegerea modului în care funcționează evenimentele este absolut esențială pentru un programator VBA.
Tipuri de evenimente
Caiet de lucru Evenimente - aceste evenimente sunt declanșate în funcție de ceea ce face utilizatorul cu registrul de lucru în sine. Acestea includ acțiuni ale utilizatorului, cum ar fi deschiderea registrului de lucru, închiderea registrului de lucru, salvarea registrului de lucru, adăugarea sau ștergerea foii
Evenimente din foaia de lucru - aceste evenimente sunt declanșate de un utilizator care acționează pe o anumită foaie de lucru. Fiecare foaie de lucru din registrul de lucru are un modul de cod individual, care conține diverse evenimente special pentru foaia de lucru respectivă (nu pentru toate foile de lucru). Acestea includ acțiuni ale utilizatorului, cum ar fi schimbarea conținutului unei celule, dublu clic pe o celulă sau clic dreapta pe o celulă.
Evenimente Active X Control - Comenzile Active X pot fi adăugate la o foaie de lucru folosind pictograma „Insert” din fila „Developer” din panglica Excel. Acestea sunt adesea comenzi de butoane pentru a permite utilizatorului să întreprindă diverse acțiuni sub controlul codului dvs., dar pot fi și obiecte precum drop-down-uri. Folosirea controalelor Active X spre deosebire de comenzile Form din foaia de lucru oferă o gamă întreagă de programabilitate. Comenzile Active X vă oferă mult mai multă flexibilitate din punct de vedere al programării în ceea ce privește utilizarea comenzilor de formular într-o foaie de lucru.
De exemplu, puteți avea două controale derulante pe foaia de lucru. Doriți ca lista disponibilă în al doilea drop-down să se bazeze pe ceea ce a ales utilizatorul în primul drop-down. Folosind evenimentul „Modificare” din primul meniu derulant, puteți crea cod pentru a citi ce a selectat utilizatorul și apoi să actualizați al doilea meniu derulant. De asemenea, puteți dezactiva al doilea drop-down până când utilizatorul a făcut o selecție în primul drop-down
Evenimente UserForm - Puteți insera și proiecta un formular cu aspect profesional pentru a fi folosit ca fereastră pop-up. Toate comenzile pe care le plasați în formular sunt controale Active X și au aceleași evenimente ca și comenzile Active X pe care le-ați putea plasa pe o foaie de lucru
Grafic Evenimente - Aceste evenimente sunt legate numai de o foaie de diagramă și nu de o diagramă care apare ca parte a unei foi de lucru. Aceste evenimente includ redimensionarea diagramei sau selectarea diagramei.
Evenimente de aplicare - Acestea folosesc obiectul Application în VBA. Exemplele ar permite deconectarea codului atunci când este apăsată o anumită tastă sau când este atins un anumit timp. Puteți programa o situație în care registrul de lucru este lăsat deschis 24/7 și importă date dintr-o sursă externă peste noapte la o oră prestabilită.
Pericolele utilizării codului în evenimente
Când scrieți cod pentru a face ceva atunci când utilizatorul face o anumită acțiune, trebuie să aveți în vedere faptul că codul dvs. ar putea declanșa alte evenimente, care ar putea pune codul dvs. într-o buclă continuă.
De exemplu, să presupunem că utilizați evenimentul „Modificare” pe o foaie de lucru, astfel încât atunci când utilizatorul introduce o valoare într-o celulă, un calcul bazat pe acea celulă este plasat în celulă imediat în dreapta acesteia.
Problema aici este că plasarea valorii calculate în celulă declanșează un alt eveniment „Change”, care apoi la rândul său declanșează încă un eveniment „Change”, și așa mai departe, până când codul dvs. a epuizat coloanele de utilizat și aruncă în sus un mesaj de eroare.
Trebuie să vă gândiți cu atenție atunci când scrieți codul evenimentului pentru a vă asigura că alte evenimente nu vor fi declanșate din greșeală
Dezactivați evenimentele
Puteți utiliza codul pentru a dezactiva evenimentele pentru a rezolva această problemă. Ce va trebui să faceți este să încorporați codul pentru a dezactiva evenimentele în timp ce codul evenimentului rulează și apoi reactivați evenimentele de la sfârșitul codului. Iată un exemplu de cum se face:
| 1234 | Sub DisableEvents ()Application.EnableEvents = FalsApplication.EnableEvents = AdevăratSfârșitul Sub |
Rețineți că acest lucru dezactivează toate evenimentele chiar în aplicația Excel, deci acest lucru ar afecta și alte funcții din Excel. Dacă utilizați acest lucru din orice motiv, asigurați-vă că evenimentele sunt repornite după aceea.
Importanța parametrilor în evenimente
Evenimentele au de obicei parametri pe care îi puteți folosi pentru a afla mai multe despre ceea ce face utilizatorul și locația celulei în care se află.
De exemplu, evenimentul Change Sheet Change arată astfel:
| 1 | Foaie de lucru sub privat_Change (țintă ByVal ca interval) |
Utilizând obiectul interval, puteți afla coordonatele rândului / coloanei celulei în care se află utilizatorul.
| 1234 | Foaie de lucru sub privat_Change (țintă ByVal ca interval)MsgBox Target.ColumnMsgBox Target.RowSfârșitul Sub |
Dacă doriți doar ca codul dvs. să funcționeze pe o anumită coloană sau un anumit număr de rând, atunci adăugați o condiție care iese din subrutină dacă coloana nu este cea necesară.
| 123 | Private Sub Worksheet_Change (țintă ByVal ca interval)Dacă țintă.Coloana 2, apoi Ieșiți din SubSfârșitul Sub |
Acest lucru rezolvă problema codului dvs. care declanșează mai multe evenimente, deoarece va funcționa numai dacă utilizatorul a schimbat o celulă din coloana 2 (coloana B)
Exemple de evenimente din registrul de lucru (nu sunt exhaustive)
Evenimentele din registrul de lucru se găsesc sub obiectul „ThisWorkbook” din VBE Project Explorer. Va trebui să selectați „Cartea de lucru” în primul meniu derulant din fereastra de cod și apoi al doilea meniu derulant vă va arăta toate evenimentele disponibile
Cartea de lucru Eveniment deschis
Acest eveniment este dezactivat ori de câte ori registrul de lucru este deschis de către un utilizator. Puteți să-l utilizați pentru a trimite un mesaj de bun venit unui utilizator prin captarea numelui său de utilizator
| 123 | Private Sub Workbook_Open ()MsgBox „Bun venit” și Application.UserNameSfârșitul Sub |
De asemenea, puteți verifica numele de utilizator al acestora cu o listă de pe o foaie ascunsă pentru a vedea dacă sunt autorizați să acceseze registrul de lucru. Dacă nu sunt un utilizator autorizat, atunci puteți afișa un mesaj și puteți închide registrul de lucru pentru a nu-l putea folosi.
Caiet de lucru Eveniment foaie nouă
Acest eveniment se declanșează atunci când un utilizator adaugă o foaie nouă în registrul de lucru
Puteți utiliza acest cod doar pentru a vă permite să adăugați o foaie nouă, în loc să aveți utilizatori diferiți care să adauge toate foile și să facă o mizerie din registrul de lucru
| 1234567 | Private Sub Workbook_NewSheet (ByVal Sh As Object)Application.DisplayAlerts = FalsDacă Application.UserName "Richard" AtunciȘtergețiEnd IfApplication.DisplayAlerts = AdevăratSfârșitul Sub |
Rețineți că trebuie să dezactivați alertele, deoarece un avertisment de utilizator va apărea atunci când foaia este ștearsă, ceea ce îi permite utilizatorului să vă ocolească codul. Asigurați-vă că porniți din nou alertele după aceea!
V-ați săturat să căutați exemple de cod VBA? Încercați AutoMacro!
Caiet de lucru înainte de salvarea evenimentului
Acest eveniment se declanșează atunci când utilizatorul dă clic pe pictograma „Salvare”, dar înainte ca „Salvare” să aibă loc
Așa cum s-a descris mai devreme, poate doriți să împiedicați utilizatorii să-și salveze modificările în registrul de lucru original și să le forțați să creeze o nouă versiune folosind un buton din foaia de lucru. Tot ce trebuie să faceți este să schimbați parametrul „Cancel” la True, iar registrul de lucru nu poate fi salvat niciodată prin metoda convențională.
| 123 | Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean)Anulare = AdevăratSfârșitul Sub |
Caiet de lucru înainte de încheierea evenimentului
Puteți utiliza acest eveniment pentru a împiedica utilizatorii să închidă registrul de lucru și să-i forțați din nou să iasă printr-un buton de foaie de lucru. Din nou, setați parametrul „Anulare” la „Adevărat”. X roșu din colțul din dreapta sus al ferestrei Excel nu mai funcționează.
| 123 | Private Sub Workbook_BeforeClose (Anulați ca boolean)Anulare = AdevăratSfârșitul Sub |
Exemple de evenimente din foaia de lucru (nu sunt exhaustive)
Evenimentele foii de lucru se găsesc sub obiectul specific al numelui foii în VBE Project Explorer. Va trebui să selectați „Foaie de lucru” în primul drop-down din fereastra de cod și apoi al doilea drop-down vă va arăta toate evenimentele disponibile
Eveniment de schimbare a foii de lucru
Acest eveniment este declanșat atunci când un utilizator efectuează o modificare a unei foi de lucru, cum ar fi introducerea unei noi valori într-o celulă
Puteți utiliza acest eveniment pentru a pune o valoare sau un comentariu suplimentar lângă celula modificată, dar, așa cum am discutat mai devreme, nu doriți să începeți să activați o buclă de evenimente.
| 12345 | Foaie de lucru sub privat_Change (țintă ByVal ca interval)Dacă țintă.Coloana 2, apoi Ieșiți din SubActiveSheet.Cells (Target.Row, Target.Column + 1). Valoare = _ActiveSheet.Cells (Target.Row, Target.Column). Valoare * 1.1Sfârșitul Sub |
În acest exemplu, codul va funcționa numai dacă valoarea este introdusă în Coloana B (coloana 2). Dacă acest lucru este adevărat, atunci va adăuga 10% la număr și îl va plasa în următoarea celulă disponibilă
Foaie de lucru înainte de evenimentul cu dublu clic
Acest eveniment va declanșa codul dacă un utilizator dă dublu clic pe o celulă. Acest lucru poate fi extrem de util pentru rapoartele financiare, cum ar fi un bilanț sau un cont de profit și pierdere, unde numerele sunt susceptibile de a fi contestate de manageri, mai ales dacă rezultatul este negativ!
Puteți utiliza acest lucru pentru a oferi o facilitate de detaliere, astfel încât atunci când managerul contestă un anumit număr, tot ce trebuie să facă este să faceți dublu clic pe număr, iar defalcarea să apară ca parte a raportului.
Acest lucru este foarte impresionant din punctul de vedere al utilizatorului și îi salvează întrebându-se în mod constant „de ce este atât de mare acest număr?”
Trebuie să scrieți cod pentru a afla titlul / criteriile pentru număr (folosind proprietățile obiectului țintă) și apoi filtrați datele tabulare și apoi copiați-le în raport.
Programare VBA | Generatorul de coduri funcționează pentru dvs.!
Foaie de lucru Activare eveniment
Acest eveniment apare atunci când utilizatorul trece de la o foaie la alta. Se aplică noii foi pe care utilizatorul se mută.
Poate fi folosit pentru a se asigura că noua foaie este complet calculată înainte ca utilizatorul să înceapă să facă ceva pe ea. Poate fi, de asemenea, utilizat pentru recalcularea acelei foi, fără a recalcula întregul registru de lucru. Dacă registrul de lucru este mare și are o formulă complicată, atunci recalcularea unei coli economisește mult timp
| 123 | Private Sub Worksheet_Activate ()ActiveSheet.CalculateSfârșitul Sub |
Evenimente Active X Control (nu exhaustive)
După cum sa discutat anterior, puteți adăuga controale Active X direct pe o foaie de lucru. Acestea pot fi butoane de comandă, drop-down și casete de listă
Evenimentele Active X se găsesc sub obiectul specific al numelui foii (unde ați adăugat controlul) în VBE Project Explorer. Va trebui să selectați numele controlului Active X în primul drop-down din fereastra de cod și apoi al doilea drop-down vă va arăta toate evenimentele disponibile
Buton de comandă Faceți clic pe Eveniment
După ce ați pus un buton de comandă pe o foaie de calcul, veți dori să acționeze. Faceți acest lucru punând cod la evenimentul Click.
Puteți pune cu ușurință un mesaj „Sunteți sigur?”, Astfel încât să fie efectuată o verificare înainte de executarea codului
| 12345 | Private Sub CommandButton1_Click ()Dim ButtonRet ca variantăButtonRet = MsgBox („Sunteți sigur că doriți să faceți acest lucru?”, VbQuestion Sau vbYesNo)Dacă ButtonRet = vbNu, apoi ieșiți din SubSfârșitul Sub |
Drop Down (casetă combinată) Eveniment de schimbare
O listă derulantă Active X are un eveniment de modificare, astfel încât, dacă un utilizator selectează un anumit element din lista derulantă, puteți captura alegerea folosind acest eveniment și apoi puteți scrie cod pentru a adapta alte părți ale foii sau registrului de lucru în consecință.
| 123 | Private Sub ComboBox1_Change ()MsgBox „Ați selectat” & ComboBox1.TextSfârșitul Sub |
Programare VBA | Generatorul de coduri funcționează pentru dvs.!
Caseta de selectare (Caseta de selectare) Faceți clic pe Eveniment
Puteți adăuga o bifă sau o casetă de selectare la o foaie de lucru, astfel încât să oferiți opțiuni pentru utilizator. Puteți utiliza evenimentul de clic pe acesta pentru a vedea dacă utilizatorul a schimbat ceva în acest sens. Valorile returnate sunt adevărate sau false în funcție de dacă a fost bifată sau nu.
| 123 | Private Sub CheckBox1_Click ()MsgBox CheckBox1.ValueSfârșitul Sub |
UserForm Events (nu exhaustiv)
Excel vă oferă posibilitatea de a vă crea propriile formulare. Acestea pot fi foarte utile pentru a fi utilizate ca ferestre de tip pop-up pentru a colecta informații sau pentru a oferi mai multe opțiuni utilizatorului. Folosesc controale Active X așa cum s-a descris anterior și au exact aceleași evenimente, deși evenimentele depind foarte mult de tipul de control.
Iată un exemplu de formă simplă:
Când este afișat, așa arată pe ecran
Ați folosi evenimentele din formular pentru a face lucruri precum introducerea unui nume implicit de companie atunci când formularul este deschis, pentru a verifica introducerea numelui companiei este de acord cu unul deja în foaia de calcul și nu a fost greșit de scris și pentru a adăuga cod la clic evenimente de pe butoanele „OK” și „Anulare”
Codul și evenimentele din spatele formularului pot fi vizualizate făcând dublu clic oriunde pe formular
Primul meniu derulant oferă acces la toate comenzile din formular. A doua listă verticală va da acces la evenimente
UserForm Activare eveniment
Acest eveniment este declanșat când formularul este activat, în mod normal când este afișat. Acest eveniment poate fi folosit pentru a configura valorile implicite, de ex. un nume de companie implicit în caseta de text numele companiei
| 123 | Private Sub UserForm_Activate ()TextBox1.Text = "Numele companiei mele"Sfârșitul Sub |
Programare VBA | Generatorul de coduri funcționează pentru dvs.!
Schimbă evenimentul
Majoritatea controalelor din formular au un eveniment de modificare, dar în acest exemplu, caseta de text numele companiei poate utiliza evenimentul pentru a pune o restricție asupra lungimii numelui companiei introduse.
| 123456 | Private Sub TextBox1_Change ()Dacă Len (TextBox1.Text)> 20 ApoiMsgBox „Numele este limitat la 20 de caractere”, vbCriticalTextBox1.Text = ""End IfSfârșitul Sub |
Faceți clic pe Eveniment
Puteți utiliza acest eveniment pentru a acționa făcând clic pe utilizator pe comenzile din formular sau chiar pe formularul în sine
În acest formular există un buton „OK” și, după ce am colectat un nume de companie, am dori să îl plasăm într-o celulă de pe foaia de calcul pentru referințe viitoare
| 1234 | Private Sub CommandButton1_Click ()ActiveSheet.Range („A1”). Valoare = TextBox1.TextEu.AscundeSfârșitul Sub |
Acest cod acționează atunci când utilizatorul dă clic pe butonul „OK”. Pune valoarea în caseta de introducere a numelui companiei în celula A1 din foaia activă și apoi ascunde formularul, astfel încât controlul utilizatorului să fie returnat înapoi la foaia de lucru.
Grafic Evenimente
Evenimentele grafice funcționează numai pe diagrame care se află pe o foaie de diagramă separată și nu pe o diagramă care este încorporată într-o foaie de lucru standard
Evenimentele grafice sunt oarecum limitate și nu pot fi utilizate pe o foaie de lucru în care este posibil să aveți mai multe diagrame. De asemenea, utilizatorii nu doresc neapărat să treacă de la o foaie de lucru care conține numere la o foaie de diagramă - nu există niciun impact vizual imediat
Cel mai util eveniment ar fi să aflăm componenta unei diagrame pe care a făcut clic un utilizator de ex. un segment dintr-o diagramă circulară sau o bară într-o diagramă cu bare, dar acesta nu este un eveniment disponibil în gama standard de evenimente.
Această problemă poate fi rezolvată folosind un modul de clasă pentru a adăuga un eveniment „Mouse Down” care va returna detaliile componentei grafice pe care a făcut clic utilizatorul. Aceasta este utilizată pe o diagramă dintr-o foaie de lucru.
Aceasta implică o codificare foarte complicată, dar rezultatele sunt spectaculoase. Puteți crea drill downs de ex. utilizatorul face clic pe un segment de diagramă circulară și instantaneu acea diagramă este ascunsă și apare un al doilea diagramă în locul său care arată o diagramă circulară cu detalii pentru segmentul original sau puteți produce datele tabulare care susțin acel segment al diagramei circulară.
Evenimente de aplicare
Puteți utiliza obiectul Aplicație în VBA pentru a declanșa codul în funcție de un anumit eveniment
Programare VBA | Generatorul de coduri funcționează pentru dvs.!
Application.OnTime
Acest lucru vă poate permite să declanșați o bucată de cod la intervale regulate atât timp cât registrul de lucru este încărcat în Excel. Poate doriți să vă salvați automat registrul de lucru într-un dosar diferit la fiecare 10 minute sau să lăsați foaia de lucru să ruleze peste noapte pentru a aduce cele mai recente date dintr-o sursă externă.
În acest exemplu, o sub rutină este introdusă într-un modul. Afișează o casetă de mesaje la fiecare 5 minute, deși aceasta ar putea fi cu ușurință o altă procedură codificată. În același timp, resetează temporizatorul la ora curentă plus încă 5 minute.
De fiecare dată când rulează, temporizatorul se resetează pentru a rula aceeași sub rutină în alte 5 minute.
| 1234 | Sub TestOnTime ()MsgBox „Testarea OnTime”Application.OnTime (Now () + TimeValue ("00:05:00")), "TestOnTime"Sfârșitul Sub |
Application.OnKey
Această funcție vă permite să vă proiectați propriile taste rapide. Puteți face ca orice combinație de taste să apeleze la o rutină sub creație.
În acest exemplu, litera „a” este redirecționată astfel încât, în loc să plaseze o „a” într-o celulă, va afișa o casetă de mesaj. Acest cod trebuie plasat într-un modul inserat.
| 123456 | Sub TestKeyPress ()Application.OnKey "a", "TestKeyPress"Sfârșitul SubSub TestKeyPress ()MsgBox „Ați apăsat„ a ””Sfârșitul Sub |
Rulați sub rutina „TestKeyPress” în primul rând. Trebuie să rulați o singură dată. Îi spune lui Excel că de fiecare dată când este apăsată litera „a” va apela sub rutina „TestKeyPress”. Sub rutina „TestKeyPress” afișează doar o casetă de mesaj pentru a vă spune că ați apăsat tasta „a”. S-ar putea, desigur, să încarce un formular sau să facă tot felul de alte lucruri.
Puteți utiliza orice combinație de taste pe care o puteți utiliza cu funcția „SendKeys”
Pentru a anula această funcționalitate, rulați instrucțiunea „OnKey” fără parametrul „Procedură”.
| 123 | Sub CancelOnKey ()Application.OnKey „a”Sfârșitul Sub |
Totul a revenit acum la normal.
