Evenimente Excel VBA - Un ghid ușor (și complet)

Când creați sau înregistrați o macro în Excel, trebuie să rulați macro-ul pentru a executa pașii din cod.

Câteva moduri de a rula o macro includ utilizarea casetei de dialog macro, atribuirea macro unui buton, utilizarea unei comenzi rapide etc.

În afară de aceste execuții de macro inițiate de utilizator, puteți utiliza și evenimente VBA pentru a rula macro-ul.

Evenimente Excel VBA - Introducere

Permiteți-mi să vă explic mai întâi ce este un eveniment în VBA.

Un eveniment este o acțiune care poate declanșa executarea macro-ului specificat.

De exemplu, atunci când deschideți un nou registru de lucru, acesta este un eveniment. Când inserați o foaie de lucru nouă, este un eveniment. Când dai dublu clic pe o celulă, este un eveniment.

Există multe astfel de evenimente în VBA și puteți crea coduri pentru aceste evenimente. Aceasta înseamnă că de îndată ce apare un eveniment și dacă ați specificat un cod pentru acel eveniment, acel cod ar fi executat instantaneu.

Excel face acest lucru automat de îndată ce observă că a avut loc un eveniment. Deci, trebuie doar să scrieți codul și să-l plasați în subrutina corectă de evenimente (acest lucru este tratat mai târziu în acest articol).

De exemplu, dacă introduceți o foaie de lucru nouă și doriți să aibă un prefix de an, puteți scrie codul pentru aceasta.

Acum, ori de câte ori cineva introduce o nouă foaie de lucru, acest cod va fi executat automat și va adăuga prefixul anului la numele foii de lucru.

Un alt exemplu ar putea fi acela că doriți să schimbați culoarea celulei atunci când cineva face dublu clic pe ea. Puteți utiliza evenimentul cu dublu clic pentru aceasta.

În mod similar, puteți crea coduri VBA pentru multe astfel de evenimente (așa cum vom vedea mai târziu în acest articol).

Mai jos este un scurt vizual care arată evenimentul cu dublu clic în acțiune. De îndată ce fac dublu clic pe celula A1. Excel deschide instantaneu o casetă de mesaj care arată adresa celulei.

Faceți dublu clic este un eveniment, iar afișarea casetei de mesaj este ceea ce am specificat în cod ori de câte ori are loc dublu clic.

Deși exemplul de mai sus este un eveniment inutil, sper să vă ajute să înțelegeți ce sunt evenimentele cu adevărat.

Diferite tipuri de evenimente VBA Excel

Există diferite obiecte în Excel - cum ar fi Excel în sine (la care ne referim adesea ca aplicație), registre de lucru, foi de lucru, diagrame etc.

Fiecare dintre aceste obiecte poate avea diferite evenimente asociate cu acesta. De exemplu:

• Dacă creați un registru de lucru nou, acesta este un eveniment la nivel de aplicație.
• Dacă adăugați o foaie de lucru nouă, este un eveniment la nivel de registru de lucru.
• Dacă modificați valoarea dintr-o celulă dintr-o foaie, este un eveniment la nivel de foaie de lucru.

Mai jos sunt diferite tipuri de evenimente care există în Excel:

1. Evenimente la nivel de foaie de lucru: Acestea sunt tipurile de evenimente care s-ar declanșa pe baza acțiunilor întreprinse în foaia de lucru. Exemplele acestor evenimente includ schimbarea unei celule din foaia de lucru, schimbarea selecției, dublu clic pe o celulă, clic dreapta pe o celulă etc.
2. Evenimente la nivel de registru de lucru: Aceste evenimente ar fi declanșate pe baza acțiunilor de la nivelul registrului de lucru. Exemplele acestor evenimente includ adăugarea unei noi foi de lucru, salvarea registrului de lucru, deschiderea registrului de lucru, imprimarea unei piese sau a întregului registru de lucru etc.
3. Evenimente la nivel de aplicație: Acestea sunt evenimentele care apar în aplicația Excel. Un exemplu dintre acestea ar include închiderea oricărui registru de lucru deschis sau deschiderea unui nou registru de lucru.
4. Evenimente nivel utilizator: Aceste evenimente ar fi declanșate pe baza acțiunilor din „UserForm”. Exemple de acestea includ inițializarea unui UserForm sau clic pe un buton din UserForm.
5. Evenimente în grafic: Acestea sunt evenimente legate de foaia grafică. O foaie de diagramă este diferită de o foaie de lucru (unde majoritatea dintre noi suntem obișnuiți să lucrăm în Excel). Scopul foilor de diagrame este de a ține o diagramă. Exemple de astfel de evenimente ar include schimbarea seriei graficului sau redimensionarea graficului.
6. Evenimente OnTime și OnKey: Acestea sunt două evenimente care nu se încadrează în niciuna dintre categoriile de mai sus. Așa că le-am enumerat separat. Evenimentul „OnTime” vă permite să executați un cod la o anumită oră sau după ce a trecut un anumit timp. Evenimentul „OnKey” vă permite să executați un cod atunci când este utilizată o anumită apăsare de tastă (sau o combinație de apăsări de tastă).

Unde să puneți codul legat de eveniment

În secțiunea de mai sus, am acoperit diferitele tipuri de evenimente.

Pe baza tipului de eveniment, trebuie să introduceți codul în obiectul relevant.

De exemplu, dacă este un eveniment legat de foaia de lucru, ar trebui să intre în fereastra de cod a obiectului foaie de lucru. Dacă este legat de registrul de lucru, acesta ar trebui să intre în fereastra de cod pentru un obiect din registrul de lucru.

În VBA, diferite obiecte - cum ar fi foi de lucru, cărți de lucru, foi de diagrame, UserForms etc., au propriile ferestre de cod. Trebuie să introduceți codul evenimentului în fereastra de cod a obiectului relevant. De exemplu - dacă este un eveniment la nivel de registru de lucru, atunci trebuie să aveți codul evenimentului în fereastra Codului registrului de lucru.

Următoarele secțiuni acoperă locurile în care puteți pune codul evenimentului:

În fereastra de cod a foii de lucru

Când deschideți Editorul VB (utilizând comanda rapidă de la tastatură ALT + F11), veți observa obiectul foilor de lucru în Project Explorer. Pentru fiecare foaie de lucru din registrul de lucru, veți vedea un obiect.

Când faceți dublu clic pe obiectul foii de lucru în care doriți să plasați codul, acesta va deschide fereastra de cod pentru foaia de lucru respectivă.

În timp ce puteți începe să scrieți codul de la zero, este mult mai bine să selectați evenimentul dintr-o listă de opțiuni și să permiteți VBA să introducă automat codul relevant pentru evenimentul selectat.

Pentru a face acest lucru, trebuie mai întâi să selectați foaia de lucru din meniul derulant din partea stângă sus a ferestrei de cod.

După ce selectați foaia de lucru din meniul derulant, veți obține o listă cu toate evenimentele legate de foaia de lucru. Puteți selecta cea pe care doriți să o utilizați din meniul derulant din partea dreaptă sus a ferestrei de cod.

De îndată ce selectați evenimentul, acesta va introduce automat prima și ultima linie a codului pentru evenimentul selectat. Acum puteți adăuga codul între cele două linii.

Notă: De îndată ce selectați Foaia de lucru din meniul derulant, veți observa două linii de cod care apar în fereastra de cod. După ce ați selectat evenimentul pentru care doriți codul, puteți șterge liniile care au apărut în mod implicit.

Rețineți că fiecare foaie de lucru are o fereastră de cod proprie. Când puneți codul pentru Sheet1, acesta va funcționa numai dacă evenimentul se întâmplă în Sheet1.

În fereastra de cod ThisWorkbook

La fel ca foile de lucru, dacă aveți un cod de eveniment la nivel de registru de lucru, îl puteți plasa în fereastra de cod ThisWorkbook.

Când faceți dublu clic pe ThisWorkbook, acesta va deschide fereastra de cod pentru aceasta.

Trebuie să selectați Cartea de lucru din meniul derulant din partea stângă sus a ferestrei de cod.

După ce selectați Workbook din meniul derulant, veți obține o listă cu toate evenimentele legate de Workbook. Puteți selecta cea pe care doriți să o utilizați din meniul derulant din partea dreaptă sus a ferestrei de cod.

De îndată ce selectați evenimentul, acesta va introduce automat prima și ultima linie a codului pentru evenimentul selectat. Acum puteți adăuga codul între cele două linii.

Notă: De îndată ce selectați Workbook din meniul derulant, veți observa două linii de cod care apar în fereastra de cod. După ce ați selectat evenimentul pentru care doriți codul, puteți șterge liniile care au apărut în mod implicit.

În fereastra de cod Userform

Când creați UserForms în Excel, puteți utiliza și evenimente UserForm pentru a executa coduri pe baza unor acțiuni specifice. De exemplu, puteți specifica un cod care se execută când se face clic pe buton.

În timp ce obiectele Sheet și obiectele ThisWorkbook sunt deja disponibile atunci când deschideți editorul VB, UserForm este ceva ce trebuie să creați mai întâi.

Pentru a crea un UserForm, faceți clic dreapta pe oricare dintre obiecte, accesați Insert și faceți clic pe UserForm.

Aceasta ar insera un obiect UserForm în registrul de lucru.

Când faceți dublu clic pe UserForm (sau pe oricare dintre obiectele pe care le adăugați la UserForm), se va deschide fereastra de cod pentru UserForm.

Acum, la fel ca foile de lucru sau ThisWorkbook, puteți selecta evenimentul și acesta va insera prima și ultima linie pentru acel eveniment. Și apoi puteți adăuga codul în mijlocul acestuia.

În fereastra Codului graficului

În Excel, puteți, de asemenea, să inserați foi de diagrame (care sunt diferite decât foile de lucru). O foaie de diagramă este menită să conțină numai diagrame.

După ce ați inserat o foaie de diagramă, veți putea vedea obiectul Foaie de diagramă în Editorul VB.

Puteți adăuga codul evenimentului în fereastra de cod a foii de grafic la fel cum am făcut în foaia de lucru.

Faceți dublu clic pe obiectul Foaie diagramă în Project Explorer. Aceasta va deschide fereastra de cod pentru foaia grafică.

Acum, trebuie să selectați Grafic din meniul derulant din partea stângă sus a ferestrei de cod.

După ce selectați Diagrama din meniul derulant, veți obține o listă cu toate evenimentele legate de foaia Diagramă. Puteți selecta cea pe care doriți să o utilizați din meniul derulant din partea dreaptă sus a ferestrei de cod.

Notă: De îndată ce selectați Diagrama din meniul derulant, veți observa două linii de cod care apar în fereastra de cod. După ce ați selectat evenimentul pentru care doriți codul, puteți șterge liniile care au apărut în mod implicit.

În modulul de clasă

Modulele de clasă trebuie inserate la fel ca UserForms.

Un modul de clasă poate conține codul legat de aplicație - care ar fi Excel în sine și diagramele încorporate.

Voi acoperi modulul clasei ca un tutorial separat în următoarele săptămâni.

Rețineți că, în afară de evenimentele OnTime și OnKey, niciunul dintre evenimentele de mai sus nu poate fi stocat în modulul VBA obișnuit.

Înțelegerea secvenței evenimentului

Când declanșezi un eveniment, acesta nu se întâmplă izolat. De asemenea, poate duce la o secvență de declanșatori multipli.

De exemplu, atunci când introduceți o nouă foaie de lucru, se întâmplă următoarele lucruri:

1. Se adaugă o nouă foaie de lucru
2. Foaia de lucru anterioară este dezactivată
3. Noua foaie de lucru este activată

Deși, în majoritatea cazurilor, este posibil să nu vă faceți griji cu privire la secvență, dacă creați coduri complexe care se bazează pe evenimente, este mai bine să cunoașteți secvența pentru a evita rezultatele neașteptate.

Înțelegerea rolului argumentelor în evenimentele VBA

Înainte de a trece la exemplele de evenimente și la lucrurile minunate pe care le puteți face cu acesta, trebuie să abordez un concept important.

În evenimentele VBA, ar exista două tipuri de coduri:

• Fără argumente
• Cu argumente

Și în această secțiune, vreau să acopăr rapid rolul argumentelor.

Mai jos este un cod care nu are niciun argument (parantezele sunt goale):

Private Sub Workbook_Open () MsgBox "Amintiți-vă să completați foaia de timp" Finalizați sub

Cu codul de mai sus, când deschideți un registru de lucru, acesta afișează pur și simplu o casetă de mesaj cu mesajul - „Nu uitați să completați foaia de lucru”.

Acum să aruncăm o privire la un cod care are un argument.

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

Codul de mai sus folosește argumentul Sh care este definit ca un tip de obiect. Argumentul Sh ar putea fi o foaie de lucru sau o foaie de diagramă, deoarece evenimentul de mai sus este declanșat când se adaugă o foaie nouă.

Prin atribuirea noii foi care este adăugată în registrul de lucru variabilei obiect Sh, VBA ne-a permis să o folosim în cod. Deci, pentru a face referire la noul nume de foaie, pot folosi Sh.Name.

Conceptul de argumente va fi util atunci când parcurgeți exemplele de evenimente VBA din secțiunile următoare.

Evenimente la nivel de registru de lucru (explicate cu exemple)

Următoarele sunt cele mai frecvent utilizate evenimente dintr-un registru de lucru.

NUMELE EVENIMENTULUI	CE DECLINEAZĂ EVENIMENTUL
Activati	Când este activat un registru de lucru
După Salvare	Când este instalat un registru de lucru ca supliment
Înainte de a salva	Când se salvează un registru de lucru
Înainte de Închidere	Când un registru de lucru este închis
Înainte de a imprima	Când se tipărește un registru de lucru
Dezactivați	Când un registru de lucru este dezactivat
Foaie nouă	Când se adaugă o foaie nouă
Deschis	Când se deschide un registru de lucru
SheetActivate	Când este activată orice foaie din registrul de lucru
SheetBeforeDelete	Când orice foaie este ștearsă
SheetBeforeDoubleClick	Când se face dublu clic pe orice foaie
SheetBeforeRightClick	Când se face clic dreapta pe orice foaie
SheetCalculate	Atunci când orice foaie este calculată sau recalculată
SheetDeactivate	Când un registru de lucru este dezactivat
SheetPivotTableUpdate	Când se actualizează un registru de lucru
SheetSelectionChange	Când se schimbă un registru de lucru
WindowActivate	Când este activat un registru de lucru
WindowDeactivate	Când un registru de lucru este dezactivat

Rețineți că aceasta nu este o listă completă. Lista completă o puteți găsi aici.

Amintiți-vă că codul pentru evenimentul din Workbook este stocat în fereastra de cod a obiectelor ThisWorkbook.

Acum, să aruncăm o privire asupra unor evenimente utile din registrele de lucru și să vedem cum acestea pot fi utilizate în munca dvs. de zi cu zi.

Eveniment deschis de registru de lucru

Să presupunem că doriți să arătați utilizatorului un memento prietenos pentru a-și completa fișele de timp ori de câte ori deschid un anumit registru de lucru.

Puteți utiliza codul de mai jos pentru a face acest lucru:

Private Sub Workbook_Open () MsgBox "Nu uitați să completați foaia de timp" End Sub

Acum, imediat ce deschideți registrul de lucru care are acest cod, acesta vă va arăta o casetă de mesaj cu mesajul specificat.

Există câteva lucruri de știut atunci când lucrați cu acest cod (sau codurile de evenimente ale registrului de lucru în general):

• Dacă un registru de lucru are o macro și doriți să o salvați, trebuie să o salvați în format .XLSM. Altfel, codul macro ar fi pierdut.
• În exemplul de mai sus, codul evenimentului va fi executat numai atunci când macro-urile sunt activate. Este posibil să vedeți o bară galbenă care cere permisiunea pentru a activa macrocomenzile. Până când acest lucru nu este activat, codul evenimentului nu este executat.
• Codul evenimentului Workbook este plasat în fereastra de cod a obiectului ThisWorkbook.

Puteți rafina în continuare acest cod și puteți afișa mesajul doar de vineri.

Codul de mai jos ar face acest lucru:

Private Sub Workbook_Open () wkday = Weekday (Date) If wkday = 6 Then MsgBox „Remember to Fill the Timesheet” End Sub

Rețineți că în funcția Weekday, duminicii i se atribuie valoarea 1, luni este 2 și așa mai departe.

Prin urmare, pentru vineri, am folosit 6.

Evenimentul Open Workbook poate fi util în multe situații, cum ar fi:

• Când doriți să afișați un mesaj de bun venit persoanei atunci când este deschis un registru de lucru.
• Când doriți să afișați un memento când este deschis registrul de lucru.
• Când doriți să activați întotdeauna o foaie de lucru specifică în registrul de lucru când este deschisă.
• Când doriți să deschideți fișiere conexe împreună cu registrul de lucru.
• Când doriți să capturați data și ora ștampilei de fiecare dată când este deschis registrul de lucru.

Cartea de lucru Eveniment NewSheet

Evenimentul NewSheet este declanșat atunci când introduceți o foaie nouă în registrul de lucru.

Să presupunem că doriți să introduceți data și ora în celula A1 a foii nou inserate. Puteți utiliza codul de mai jos pentru a face acest lucru:

Private Sub Workbook_NewSheet (ByVal Sh As Object) On Error Reîncepeți următorul Sh.Range ("A1") = Format (Acum, "dd-mmm-aaaa hh: mm: ss") End Sub

Codul de mai sus folosește „On Error Resume Next” pentru a gestiona cazurile în care cineva introduce o foaie de diagramă și nu o foaie de lucru. Deoarece foaia grafică nu are celula A1, ar apărea o eroare dacă nu se folosește „On Error Resume Next”.

Un alt exemplu ar putea fi atunci când doriți să aplicați o setare de bază sau o formatare la o foaie nouă de îndată ce este adăugată. De exemplu, dacă doriți să adăugați o foaie nouă și doriți să obțină automat un număr de serie (până la 100), atunci puteți utiliza codul de mai jos.

Private Sub Workbook_NewSheet (ByVal Sh As Object) La eroare Reîncepeți cu Sh.Range ("A1") .Value = "S. No." .Interior.Color = vbBlue .Font.Color = vbWith ​​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

Codul de mai sus face, de asemenea, un pic de formatare. Oferă celulei antetului o culoare albastră și face ca fontul să fie alb. De asemenea, aplică un chenar tuturor celulelor umplute.

Codul de mai sus este un exemplu al modului în care un cod VBA scurt vă poate ajuta să furați câteva secunde de fiecare dată când inserați o foaie de lucru nouă (în cazul în care acest lucru trebuie făcut de fiecare dată).

Caiet de lucru Înainte de a salva evenimentul

Înainte de a salva evenimentul este declanșat atunci când salvați un registru de lucru. Rețineți că evenimentul este declanșat mai întâi și apoi registrul de lucru este salvat.

Când salvați un registru de lucru Excel, ar putea exista două scenarii posibile:

1. Îl salvați pentru prima dată și va apărea caseta de dialog Salvare ca.
2. L-ați salvat deja mai devreme și va salva și suprascrie modificările din versiunea deja salvată.

Acum, aruncăm o privire la câteva exemple în care puteți utiliza evenimentul BeforeSave.

Să presupunem că aveți un nou registru de lucru pe care îl salvați pentru prima dată și doriți să reamintiți utilizatorului să îl salveze în unitatea K, atunci puteți utiliza codul de mai jos:

Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean) Dacă SaveAsUI, apoi MsgBox „Salvați acest fișier în unitatea K” End Sub

În codul de mai sus, dacă fișierul nu a fost niciodată salvat, SaveAsUI este True și afișează caseta de dialog Save As. Codul de mai sus va afișa mesajul înainte ca caseta de dialog Salvare ca să apară.

Un alt exemplu ar putea fi actualizarea datei și orei când fișierul este salvat într-o anumită celulă.

Codul de mai jos ar insera ștampila de dată și oră în celula A1 din Sheet1 ori de câte ori fișierul este salvat.

Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean, Cancel As Boolean) Foi de lucru ("Sheet1"). Range ("A1") = Format (Acum, "dd-mmm-aaaa hh: mm: ss") End Sub

Rețineți că acest cod este executat imediat ce utilizatorul salvează registrul de lucru. Dacă registrul de lucru este salvat pentru prima dată, va apărea o casetă de dialog Salvare ca. Dar codul este deja executat până când vedeți caseta de dialog Salvare ca. În acest moment, dacă decideți să anulați și să nu salvați registrul de lucru, data și ora ar fi deja introduse în celulă.

Caiet de lucru BeforeClose Event

Înainte de evenimentul Închidere se întâmplă chiar înainte de închiderea registrului de lucru.

Codul de mai jos protejează toate foile de lucru înainte de închiderea registrului de lucru.

Private Sub Workbook_BeforeClose (Anulați ca boolean) Dim sh Ca foaie de lucru pentru fiecare sh din acest manual de lucru.

Amintiți-vă că codul evenimentului este declanșat imediat ce închideți registrul de lucru.

Un lucru important de știut despre acest eveniment este că nu-i pasă dacă registrul de lucru este de fapt închis sau nu.

În cazul în care registrul de lucru nu a fost salvat și vi se afișează solicitarea care vă întreabă dacă salvați registrul de lucru sau nu și faceți clic pe Anulare, acesta nu vă va salva registrul de lucru.Cu toate acestea, codul evenimentului ar fi fost deja executat până atunci.

Caiet de lucru BeforePrint Event

Când dați comanda print (sau comanda Print Preview), se declanșează evenimentul Before Print.

Codul de mai jos ar recalcula toate foile de lucru înainte de a fi tipărit registrul de lucru.

Private Sub Workbook_BeforePrint (Anulați ca boolean) pentru fiecare ws din foile de lucru ws. Calculați următorul ws End Sub

Când utilizatorul tipărește registrul de lucru, evenimentul va fi declanșat indiferent dacă el / ea tipărește întregul registru de lucru sau doar o parte din acesta.

Un alt exemplu de mai jos este al codului care ar adăuga data și ora la subsol la imprimarea registrului de lucru.

Private Sub Workbook_BeforePrint (Anulați ca boolean) Dim ws ca foaie de lucru pentru fiecare ws din ThisWorkbook.Worksheets ws.PageSetup.LeftFooter = "Printed On -" & Format (Now, "dd-mmm-aaaa hh: mm") Next ws End Sub

Evenimente la nivel de foaie de lucru (explicate cu exemple)

Evenimentele foii de lucru au loc pe baza declanșatoarelor din foaia de lucru.

Următoarele sunt cele mai frecvent utilizate evenimente dintr-o foaie de lucru.

Numele evenimentului	Ce declanșează evenimentul
Activati	Când foaia de lucru este activată
Înainte de a șterge	Înainte de a șterge foaia de lucru
Înainte de DoubleClick	Înainte de a face dublu clic pe foaia de lucru
BeforeRightClick	Înainte de a face clic dreapta pe foaia de lucru
calculati	Înainte ca foaia de lucru să fie calculată sau recalculată
Schimbare	Când celulele din foaia de lucru sunt modificate
Dezactivați	Când foaia de lucru este dezactivată
PivotTableUpdate	Când tabelul pivot din foaia de lucru este actualizat
SelectionChange	Când se schimbă selecția din foaia de lucru

Rețineți că aceasta nu este o listă completă. Lista completă o puteți găsi aici.

Amintiți-vă că codul pentru evenimentul Foaie de lucru este stocat în fereastra de cod obiect a foii de lucru (în cea în care doriți să fie declanșat evenimentul). Pot exista mai multe foi de lucru într-un registru de lucru, iar codul dvs. ar fi declanșat numai atunci când evenimentul are loc în foaia de lucru în care este plasat.

Acum, să aruncăm o privire la câteva evenimente utile din foaia de lucru și să vedem cum acestea pot fi utilizate în munca dvs. de zi cu zi.

Foaie de lucru Activare eveniment

Acest eveniment este declanșat când activați o foaie de lucru.

Codul de mai jos neprotejează o foaie imediat ce este activată.

Private Sub Worksheet_Activate () ActiveSheet.Unprotect End Sub

De asemenea, puteți utiliza acest eveniment pentru a vă asigura că o anumită celulă sau un interval de celule (sau un interval denumit) este selectat imediat ce activați foaia de lucru. Codul de mai jos va selecta celula D1 imediat ce activați foaia.

Private Sub Worksheet_Activate () ActiveSheet.Range ("D1"). Selectați End Sub

Eveniment de schimbare a foii de lucru

Un eveniment de schimbare este declanșat ori de câte ori efectuați o modificare în foaia de lucru.

Ei bine … nu întotdeauna.

Există unele modificări care declanșează evenimentul, iar altele nu. Iată o listă cu câteva modificări care nu vor declanșa evenimentul:

• Când modificați formatarea celulei (dimensiunea fontului, culoarea, chenarul etc.).
• Când îmbinați celulele. Acest lucru este surprinzător, deoarece uneori, îmbinarea celulelor elimină conținutul din toate celulele, cu excepția celei din stânga sus.
• Când adăugați, ștergeți sau editați un comentariu de celulă.
• Când sortați o gamă de celule.
• Când folosiți Goal Seek.

Următoarele modificări ar declanșa evenimentul (chiar dacă ați putea crede că nu ar trebui):

• Copierea și lipirea formatării ar declanșa evenimentul.
• Ștergerea formatării ar declanșa evenimentul.
• Executarea unei verificări ortografice ar declanșa evenimentul.

Mai jos este un cod care va afișa o casetă de mesaj cu adresa celulei care a fost modificată.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox „Tocmai ai schimbat” & Target.Address End Sub

Deși aceasta este o macro inutilă, aceasta vă arată cum să utilizați argumentul țintă pentru a afla ce celule au fost modificate.

Acum să vedem câteva exemple mai utile.

Să presupunem că aveți o gamă de celule (să spunem A1: D10) și doriți să afișați o solicitare și să întrebați utilizatorul dacă dorea cu adevărat să schimbe o celulă din acest interval sau nu, puteți utiliza codul de mai jos.

Se afișează o solicitare cu două butoane - Da și Nu. Dacă utilizatorul selectează „Da”, schimbarea este efectuată, altfel este inversată.

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Faceți o modificare în celulele din A1: D10. Sigur doriți?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

În codul de mai sus, verificăm dacă celula țintă se află în primele 4 coloane și primele 10 rânduri. Dacă acesta este cazul, este afișată caseta de mesaje. De asemenea, dacă utilizatorul a selectat Nu în caseta de mesaj, modificarea este inversată (prin comanda Application.Undo).

Rețineți că am folosit Application.EnableEvents = False înainte de linia Application.Undo. Și apoi l-am inversat folosind Application.EnableEvent = True în următoarea linie.

Acest lucru este necesar, deoarece atunci când se întâmplă anularea, declanșează și evenimentul de schimbare. Dacă nu setez EnableEvent la False, va continua să declanșeze evenimentul de modificare.

De asemenea, puteți monitoriza modificările la un interval numit folosind evenimentul de modificare. De exemplu, dacă aveți un interval numit numit „DataRange” și doriți să afișați o solicitare în cazul în care utilizatorul efectuează o modificare în acest interval denumit, puteți utiliza codul de mai jos:

Private Sub Worksheet_Change (ByVal Target As Range) Dim DRange as Range Set DRange = Range ("DataRange") Dacă nu se intersectează (Target, DRange) Nu este nimic, apoi MsgBox "Tocmai ați făcut o modificare a intervalului de date" End If End Sub

Codul de mai sus verifică dacă celula / intervalul în care ați efectuat modificările are celule comune intervalului de date. În caz contrar, se afișează caseta de mesaj.

Selectare registru de lucru Schimbare eveniment

Evenimentul de modificare a selecției este declanșat ori de câte ori există o modificare de selecție în foaia de lucru.

Codul de mai jos ar recalcula foaia imediat ce modificați selecția.

Aplicație Private Sub Worksheet_SelectionChange (ByVal Target As Range). Calculați End Sub

Un alt exemplu al acestui eveniment este atunci când doriți să evidențiați rândul și coloana active ale celulei selectate.

Ceva așa cum se arată mai jos:

Următorul cod poate face acest lucru:

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) Termină cu Sfârșitul Sub

Codul elimină mai întâi culoarea de fundal din toate celulele și apoi aplică-o pe cea menționată în cod rândului și coloanei active.

Și asta este problema cu acest cod. Că elimină culoarea din toate celulele.

Dacă doriți să evidențiați rândul / coloana activă, păstrând intactă culoarea din alte celule, utilizați tehnica prezentată în acest tutorial.

Cartea de lucru Eveniment DoubleClick

Acesta este unul dintre evenimentele mele preferate în foaia de lucru și veți vedea o mulțime de tutoriale în care am folosit acest lucru (cum ar fi acesta sau acesta).

Acest eveniment se declanșează când faceți dublu clic pe o celulă.

Permiteți-mi să vă arăt cât de minunat este acest lucru.

Cu codul de mai jos, puteți face dublu clic pe o celulă și aceasta va aplica o culoare de fundal, va schimba culoarea fontului și va face textul din celulă aldin;

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

Acest lucru poate fi util atunci când parcurgeți o listă de celule și doriți să evidențiați câteva selectate. Deși puteți utiliza tasta F4 pentru a repeta ultimul pas, aceasta ar putea aplica doar un tip de formatare. Cu acest eveniment cu dublu clic, puteți aplica toate cele trei doar cu un dublu clic.

Rețineți că, în codul de mai sus, am făcut valoarea Cancel = True.

Acest lucru se face astfel încât acțiunea implicită de dublu clic să fie dezactivată - care este de a intra în modul de editare. Cu Anulare = Adevărat, Excel nu vă va duce în modul Editare atunci când faceți dublu clic pe celulă.

Iată un alt exemplu.

Dacă aveți o listă de sarcini în Excel, puteți utiliza un dublu clic pe eveniment pentru a aplica formatul de tăiere pentru a marca sarcina ca terminată.

Ceva așa cum se arată mai jos:

Iată codul care va face acest lucru:

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

Rețineți că în acest cod am făcut dublu clic ca eveniment de comutare. Când faceți dublu clic pe o celulă, aceasta verifică dacă formatul de strikethrough a fost deja aplicat. Dacă a fost, faceți dublu clic pentru a elimina formatul strikethrough și, dacă nu a fost, atunci se aplică formatul strikethrough.

Eveniment Excel VBA OnTime

Evenimentele pe care le-am văzut până acum în acest articol au fost asociate cu unul dintre obiectele Excel, fie că este vorba de registrul de lucru, foaia de lucru, foaia de diagrame sau UserForms etc.

Evenimentul OnTime este diferit de alte evenimente, deoarece poate fi stocat în modulul VBA obișnuit (în timp ce celelalte urmau să fie plasate în fereastra de cod a obiectelor precum ThisWorkbook sau Worksheets sau UserForms).

În cadrul modulului VBA obișnuit, este utilizat ca metodă a obiectului aplicației.

Motivul pentru care acesta este considerat un eveniment este că poate fi declanșat în funcție de timpul specificat. De exemplu, dacă vreau ca foaia să se recalculeze la fiecare 5 minute, pot folosi evenimentul OnTime pentru aceasta.

Sau, dacă vreau să afișez un mesaj / memento la o anumită oră a zilei, pot folosi evenimentul OnTime.

Mai jos este un cod care va afișa un mesaj la ora 14:00 în fiecare zi.

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

Amintiți-vă că trebuie să plasați acest cod în modulul VBA obișnuit,

De asemenea, în timp ce evenimentul OnTime ar fi declanșat la ora specificată, trebuie să rulați macrocomanda manual în orice moment. După ce rulați macrocomanda, aceasta va aștepta până la ora 14:00 și apoi va apela macrocomanda „ShowMessage”.

Macrocomanda ShowMessage va afișa apoi mesajul.

Evenimentul OnTime ia patru argumente:

Application.OnTime (EarliestTime, Procedură, LatestTime, Programa)

• Timp mai devreme: Ora în care doriți să rulați procedura.
• Procedură: Numele procedurii care trebuie executată.
• LatestTime (opțional): În cazul în care se execută un alt cod și codul specificat nu poate fi rulat la ora specificată, puteți specifica ultima oră pentru care ar trebui să aștepte. De exemplu, ar putea fi EarliestTime + 45 (ceea ce înseamnă că va aștepta 45 de secunde pentru ca cealaltă procedură să fie finalizată). Dacă nici după 45 de secunde procedura nu poate rula, aceasta este abandonată. Dacă nu specificați acest lucru, Excel ar aștepta până când codul poate fi rulat, apoi îl va rula.
• Program (opțional): Dacă este setat la True, programează o nouă procedură de timp. Dacă este fals, atunci anulează procedura stabilită anterior. În mod implicit, acesta este adevărat.

În exemplul de mai sus, am folosit doar primele două argumente.

Să vedem un alt exemplu.

Codul de mai jos ar actualiza foaia de lucru la fiecare 5 minute.

Dim NextRefresh as Date Sub RefreshSheet () ThisWorkbook.Worksheets ("Sheet1"). Calculați 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

Codul de mai sus va actualiza foaia de lucru la fiecare 5 minute.

Folosește funcția Acum pentru a determina ora curentă și apoi adaugă 5 minute la ora curentă.

Evenimentul OnTime va continua să ruleze până îl opriți. Dacă închideți registrul de lucru și aplicația Excel este încă în desfășurare (alte registre de lucru sunt deschise), registrul de lucru care are evenimentul OnTime rulat în el s-ar redeschide singur.

Acest lucru este mai bine gestionat prin oprirea specifică a evenimentului OnTime.

În codul de mai sus, am codul StopRefresh, dar trebuie să-l executați pentru a opri evenimentul OnTime. Puteți face acest lucru manual, atribuiți-l unui buton și faceți acest lucru apăsând butonul sau apelați-l din evenimentul Close Workbook.

Private Sub Workbook_BeforeClose (Anulare ca boolean) Apel StopRefresh End Sub

Codul de eveniment „BeforeClose” de mai sus intră în fereastra de cod ThisWorkbook.

Eveniment OnKey Excel VBA

Când lucrați cu Excel, acesta monitorizează continuu apăsările de tastă pe care le utilizați. Acest lucru ne permite să folosim apăsările de tastă ca declanșator pentru un eveniment.

Cu evenimentul OnKey, puteți specifica o apăsare de tastă (sau o combinație de apăsări de taste) și codul care ar trebui executat atunci când se folosește acea apăsare de tastă. Când se apasă aceste apăsări, acesta va executa codul pentru aceasta.

La fel ca evenimentul OnTime, trebuie să aveți o modalitate de a anula evenimentul OnKey. De asemenea, atunci când setați evenimentul OnKey pentru o anumită apăsare de tastă, acesta devine disponibil în toate registrele de lucru deschise.

Înainte de a vă arăta un exemplu de utilizare a evenimentului OnKey, permiteți-mi să vă împărtășesc mai întâi codurile cheie care vă sunt disponibile în VBA.

CHEIE	COD
Backspace	{BACKSPACE} sau {BS}
Pauză	{PAUZĂ}
Majuscule	{MAJUSCULE}
Șterge	{DELETE} sau {DEL}
Sageata in jos	{JOS}
Sfârșit	{SFÂRȘIT}
introduce	~
Introduceți (pe tastatura nuerică)	{INTRODUCE}
Evadare	{ESCAPE} sau {ESC}
Acasă	{ACASĂ}
Ins	{INTRODUCE}
Sageata stanga	{STÂNGA}
Blocarea numerelor	{BLOCAREA NUMERELOR}
In josul paginii	{PGDN}
Pagină sus	{PGUP}
Sageata dreapta	{DREAPTA}
Blocare defilare	{SCROLLOCK}
Tab	{TAB}
Săgeata în sus	{SUS}
F1 până la F15	{F1} până la {F15}

Când trebuie să utilizați orice eveniment onkey, trebuie să utilizați codul pentru acesta.

Tabelul de mai sus conține codurile pentru o singură apăsare de tastă.

De asemenea, le puteți combina cu următoarele coduri:

• Schimb: + (Semnul plus)
• Control: ^ (Semn de omisiune)
• Alt: % (Procent)

De exemplu, pentru Alt F4, trebuie să utilizați codul: „% {F4}”- unde% este pentru tasta ALT și {F4} este pentru tasta F4.

Acum să aruncăm o privire la un exemplu (amintiți-vă că codul pentru evenimentele OnKey sunt plasate în modulul VBA obișnuit).

Când apăsați tasta PageUp sau PageDown, aceasta sare de 29 de rânduri deasupra / sub celula activă (cel puțin asta face pe laptopul meu).

Dacă doriți să sară doar 5 rânduri odată, puteți utiliza codul de mai jos:

Sub PageUpDOwnKeys () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub Sub PageUpMod () On Error Reîncepe Următorul ActiveCell.Offset (-5, 0). Sub Sub PageDownMod () On Error Reîncepeți următorul ActiveCell.Offset (5, 0). Activați End Sub

Când rulați prima parte a codului, acesta va rula evenimentele OnKey. Odată ce acest lucru este executat, folosind PageUp și tasta PageDown ar face ca cursorul să sară doar 5 rânduri pe rând.

Rețineți că am folosit „On Error Resume Next” pentru a ne asigura că erorile sunt ignorate. Aceste erori pot apărea atunci când apăsați tasta PageUp chiar dacă vă aflați în partea de sus a foii de lucru. Deoarece nu mai există rânduri de sărit, codul ar arăta o eroare. Dar, din moment ce am folosit „On Error Resume Next”, va fi ignorat.

Pentru a vă asigura că aceste evenimente OnKey sunt disponibile, trebuie să rulați prima parte a codului. În cazul în care doriți ca acesta să fie disponibil imediat ce deschideți registrul de lucru, îl puteți plasa în fereastra de cod ThisWorkbook.

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

Codul de mai jos va readuce cheile la funcționalitatea lor normală.

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

Când nu specificați al doilea argument în metoda OnKey, acesta va readuce apăsarea tastei la funcționalitatea sa obișnuită.

În cazul în care doriți să anulați funcționalitatea unei apăsări de tastă, astfel încât Excel să nu facă nimic atunci când se folosește acea apăsare de tastă, trebuie să utilizați un șir gol ca al doilea argument.

În codul de mai jos, Excel nu ar face nimic atunci când folosim tastele PageUp sau PageDown.

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

Dezactivarea evenimentelor în VBA

Uneori este posibil să fie necesar să dezactivați evenimentele pentru ca codul dvs. să funcționeze corect.

De exemplu, să presupunem că am un interval (A1: D10) și vreau să afișez un mesaj ori de câte ori o celulă este schimbată în acest interval. Așa că afișez o casetă de mesaje și îl întreb pe utilizator dacă sunt sigur că vor să facă modificarea. Dacă răspunsul este Da, modificarea este făcută și dacă răspunsul este Nu, atunci VBA ar anula.

Puteți utiliza codul de mai jos:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Faceți o modificare în celulele din A1: D10. Sigur doriți?", vbYesNo) End If If Ans = vbNo Then Application.Undo End If End Sub

Problema cu acest cod este că atunci când utilizatorul selectează Nu în caseta de mesaj, acțiunea este inversată (așa cum am folosit Application.Undo).

Când anularea se întâmplă și valoarea este schimbată înapoi la cea originală, evenimentul de modificare VBA este din nou declanșat, iar utilizatorului i se arată din nou aceeași casetă de mesaj.

Aceasta înseamnă că puteți continua să faceți clic pe NU pe caseta de mesaj și va continua să apară. Acest lucru se întâmplă pe măsură ce v-ați blocat în bucla infinită în acest caz.

Pentru a evita astfel de cazuri, trebuie să dezactivați evenimentele, astfel încât evenimentul de modificare (sau orice alt eveniment) să nu fie declanșat.

Următorul cod ar funcționa bine în acest caz:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Faceți o modificare în celulele din A1: D10. Sigur doriți?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

În codul de mai sus, chiar deasupra liniei Application.Undo, am folosit - Application.EnableEvents = False.

Setarea EnableEvents la False nu va declanșa niciun eveniment (în registrele de lucru curente sau deschise).

Odată ce am finalizat operațiunea de anulare, putem reveni la proprietatea EnableEvents la True.

Rețineți că dezactivarea evenimentelor afectează toate registrele de lucru care sunt deschise în prezent (sau deschise în timp ce EnableEvents este setat la False). De exemplu, ca parte a codului, dacă deschideți un nou registru de lucru, atunci evenimentul Open Workbook nu ar funcționa.

Impactul evenimentelor Undo Stack

Permiteți-mi să vă spun mai întâi ce este Undo Stack.

Când lucrați în Excel, acesta vă monitorizează în continuare acțiunile. Când faceți o greșeală, puteți folosi oricând Control + Z pentru a reveni la pasul anterior (adică anulați acțiunea curentă).

Dacă apăsați Control + Z de două ori, vă va duce înapoi cu doi pași. Acești pași pe care i-ați efectuat sunt stocate ca parte a stivei Undo.

Orice eveniment care modifică foaia de lucru distruge această stivă Undo.Aceasta înseamnă că, dacă am făcut 5 lucruri înainte de a declanșa un eveniment, nu voi putea folosi Control + Z pentru a reveni la acei pași anteriori. Declanșarea evenimentului mi-a distrus acel stack.

În codul de mai jos, folosesc VBA pentru a introduce marca de timp în celula A1 ori de câte ori există o modificare în foaia de lucru.

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

Deoarece fac o modificare în foaia de lucru, aceasta va distruge stiva de anulare.

De asemenea, rețineți că acest lucru nu se limitează doar la evenimente.

Dacă aveți un cod care este stocat în modulul VBA obișnuit și faceți o modificare în foaia de lucru, acesta ar distruge și stiva de anulare în Excel.

De exemplu, codul de mai jos introduce pur și simplu textul „Bună ziua” în celula A1, dar chiar și executarea acestuia ar distruge stiva de anulare.

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

Vă pot plăcea, de asemenea, următoarele tutoriale Excel VBA:

• Lucrul cu celule și intervale în Excel VBA.
• Lucrul cu foi de lucru în Excel VBA.
• Lucrul cu registrele de lucru în Excel VBA.
• Bucle VBA Excel - Ghidul final.
• Folosind IF Then Else Statement în Excel VBA.
• Pentru următoarea buclă în Excel.
• Crearea funcțiilor definite de utilizator în Excel VBA.
• Cum se creează și se utilizează programe de completare în Excel.
• Creați și reutilizați macrocomenzi salvând în registrul personal de lucru Macro.