Udalosti programu Excel VBA - ľahký (a kompletný) sprievodca

Keď vytvárate alebo zaznamenávate makro v programe Excel, musíte ho spustiť a vykonať kroky v kóde.

Niekoľko spôsobov spustenia makra zahŕňa použitie dialógového okna makra, priradenie makra k tlačidlu, skratku atď.

Okrem týchto používateľmi spustených makier môžete na spustenie makra použiť aj udalosti VBA.

Udalosti programu Excel VBA - úvod

Dovoľte mi najskôr vysvetliť, čo je udalosť vo VBA.

Udalosť je akcia, ktorá môže spustiť vykonanie zadaného makra.

Keď napríklad otvoríte nový zošit, je to udalosť. Keď vložíte nový pracovný hárok, je to udalosť. Keď dvakrát kliknete na bunku, je to udalosť.

Vo VBA je veľa takýchto udalostí a môžete pre tieto udalosti vytvárať kódy. To znamená, že hneď ako dôjde k udalosti, a ak ste pre ňu zadali kód, tento kód sa okamžite spustí.

Excel to urobí automaticky, akonáhle si všimne, že sa stala udalosť. Stačí teda napísať kód a umiestniť ho do správneho podprogramu udalosti (tomu sa budeme venovať ďalej v tomto článku).

Ak napríklad vložíte nový pracovný hárok a chcete, aby mal predponu roka, môžete naň napísať kód.

Teraz, kedykoľvek ktokoľvek vloží nový pracovný hárok, tento kód sa automaticky spustí a k názvu pracovného hárka pridá predponu roku.

Ďalším príkladom môže byť to, že chcete zmeniť farbu bunky, keď na ňu niekto dvakrát klikne. Na to môžete použiť udalosť dvojitého kliknutia.

Podobne môžete vytvoriť kódy VBA pre mnoho takýchto udalostí (ako uvidíme neskôr v tomto článku).

Nasleduje krátky vizuál, ktorý ukazuje udalosť dvojitého kliknutia v akcii. Hneď ako dvakrát kliknem na bunku A1. Excel okamžite otvorí pole so správou, ktoré zobrazuje adresu bunky.

Dvojité kliknutie je udalosť a zobrazenie okna so správou je to, čo som zadal v kóde, kedykoľvek sa udalosť dvojitého kliknutia uskutoční.

Aj keď je vyššie uvedený príklad zbytočnou udalosťou, dúfam, že vám pomôže pochopiť, aké udalosti skutočne sú.

Rôzne typy udalostí Excel VBA

V Exceli existujú rôzne objekty - napríklad samotný Excel (ku ktorému sa často hovorí ako o aplikácii), zošity, pracovné listy, grafy atď.

S každým z týchto objektov môžu byť spojené rôzne udalosti. Napríklad:

• Ak vytvoríte nový zošit, je to udalosť na úrovni aplikácie.
• Ak pridáte nový pracovný hárok, je to udalosť na úrovni zošita.
• Ak zmeníte hodnotu v bunke v hárku, je to udalosť na úrovni pracovného hárka.

Nasledujú rôzne typy udalostí, ktoré existujú v programe Excel:

1. Udalosti na úrovni pracovného hárka: Toto sú typy udalostí, ktoré by sa mohli spustiť na základe akcií vykonaných v pracovnom hárku. Medzi príklady týchto udalostí patrí zmena bunky v pracovnom hárku, zmena výberu, dvojité kliknutie na bunku, pravé kliknutie na bunku atď.
2. Udalosti na úrovni zošita: Tieto udalosti by sa spustili na základe akcií na úrovni zošita. Medzi príklady týchto udalostí patrí pridanie nového pracovného hárka, uloženie zošita, otvorenie zošita, vytlačenie časti alebo celého zošita atď.
3. Udalosti na úrovni aplikácie: Toto sú udalosti, ktoré sa vyskytujú v aplikácii Excel. Medzi tieto príklady patrí zatvorenie ktoréhokoľvek z otvorených zošitov alebo otvorenie nového zošita.
4. Udalosti na úrovni UserForm: Tieto udalosti by sa spustili na základe akcií v „UserForm“. Medzi tieto príklady patrí inicializácia UserForm alebo kliknutie na tlačidlo v UserForm.
5. Udalosti grafu: Ide o udalosti súvisiace s listom s grafmi. Hárok grafu sa líši od pracovného hárka (v ktorom je väčšina z nás zvyknutá pracovať v programe Excel). Účelom listov grafu je držať graf. Medzi príklady takýchto udalostí patrí zmena série grafu alebo zmena veľkosti grafu.
6. Udalosti OnTime a OnKey: Toto sú dve udalosti, ktoré nezapadajú do žiadnej z vyššie uvedených kategórií. Preto som ich uviedol samostatne. Udalosť „OnTime“ vám umožňuje spustiť kód v konkrétnom čase alebo po uplynutí určitého času. Udalosť „OnKey“ vám umožňuje spustiť kód pri použití konkrétneho stlačenia klávesu (alebo kombinácie klávesov).

Kam vložiť kód súvisiaci s udalosťou

Vo vyššie uvedenej časti som rozobral rôzne typy udalostí.

Na základe typu udalosti musíte vložiť kód do príslušného objektu.

Ak ide napríklad o udalosť súvisiacu s pracovným listom, malo by ísť do okna kódu objektu pracovného hárka. Ak súvisí so zošitom, malo by ísť do okna kódu pre objekt zošita.

Vo VBA majú rôzne objekty - napríklad pracovné hárky, zošity, grafy, UserForms atď., Svoje vlastné okná s kódom. Kód udalosti musíte vložiť do okna kódu príslušného objektu. Napríklad - ak ide o udalosť na úrovni zošita, potom musíte mať kód udalosti v okne s kódom zošita.

Nasledujúce sekcie pokrývajú miesta, kam môžete vložiť kód udalosti:

V okne Kód pracovného hárka

Keď otvoríte editor VB (pomocou klávesovej skratky ALT + F11), v programe Prieskumník projektu si všimnete objekt pracovných hárkov. Pre každý pracovný list v zošite uvidíte jeden objekt.

Keď dvakrát kliknete na objekt pracovného hárka, do ktorého chcete vložiť kód, otvorí sa okno kódu pre tento pracovný hárok.

Aj keď môžete začať písať kód od začiatku, je oveľa lepšie vybrať udalosť zo zoznamu možností a nechať VBA automaticky vložiť príslušný kód pre vybratú udalosť.

Ak to chcete urobiť, musíte najskôr vybrať pracovný hárok z rozbaľovacej ponuky v ľavej hornej časti okna kódu.

Po výbere pracovného hárka z rozbaľovacieho zoznamu získate zoznam všetkých udalostí súvisiacich s pracovným listom. V rozbaľovacej ponuke v pravom hornom rohu okna kódu môžete vybrať ten, ktorý chcete použiť.

Hneď ako vyberiete udalosť, automaticky sa zadá prvý a posledný riadok kódu pre vybratú udalosť. Teraz môžete pridať kód medzi dva riadky.

Poznámka: Hneď ako v rozbaľovacom zozname vyberiete položku Pracovný list, všimnete si, že v okne kódu sa zobrazia dva riadky kódu. Hneď ako vyberiete udalosť, pre ktorú chcete kód, môžete odstrániť riadky, ktoré sa zobrazili v predvolenom nastavení.

Každý pracovný hárok má vlastné okno s kódom. Keď zadáte kód pre Sheet1, bude fungovať, iba ak sa udalosť stane v Sheet1.

V okne kódu tejto učebnice

Rovnako ako pracovné listy, ak máte kód udalosti na úrovni zošita, môžete ho umiestniť do okna s kódom ThisWorkbook.

Keď dvakrát kliknete na ThisWorkbook, otvorí sa mu okno s kódom.

V rozbaľovacej ponuke v ľavej hornej časti okna s kódom musíte vybrať zošit.

Po výbere zošita v rozbaľovacom zozname získate zoznam všetkých udalostí súvisiacich so zošitom. V rozbaľovacej ponuke v pravom hornom rohu okna kódu môžete vybrať ten, ktorý chcete použiť.

Hneď ako vyberiete udalosť, automaticky sa zadá prvý a posledný riadok kódu pre vybratú udalosť. Teraz môžete pridať kód medzi dva riadky.

Poznámka: Hneď ako v rozbaľovacom zozname vyberiete možnosť Zošit, všimnete si v okne kódu dva riadky kódu. Hneď ako vyberiete udalosť, pre ktorú chcete kód, môžete odstrániť riadky, ktoré sa zobrazili v predvolenom nastavení.

V okne s kódom Userform

Pri vytváraní UserForms v Exceli môžete tiež použiť udalosti UserForm na spustenie kódov na základe konkrétnych akcií. Môžete napríklad zadať kód, ktorý sa spustí po kliknutí na tlačidlo.

Kým objekty Sheet a ThisWorkbook sú už k dispozícii, keď otvoríte editor VB, UserForm je niečo, čo musíte najskôr vytvoriť.

Ak chcete vytvoriť UserForm, kliknite pravým tlačidlom myši na ktorýkoľvek z objektov, prejdite na Vložiť a kliknite na UserForm.

To by vložilo objekt UserForm do zošita.

Keď dvakrát kliknete na UserForm (alebo na ktorýkoľvek objekt, ktorý pridáte do UserForm), otvorí sa okno s kódom pre UserForm.

Teraz, rovnako ako pracovné listy alebo ThisWorkbook, môžete vybrať udalosť a vloží prvý a posledný riadok pre túto udalosť. A potom môžete kód pridať do jeho stredu.

V okne kódu grafu

V programe Excel môžete tiež vložiť hárky s grafmi (ktoré sa líšia od hárkov). Hárok grafu má obsahovať iba grafy.

Keď vložíte hárok grafu, budete môcť vidieť objekt hárka grafu v editore VB.

Kód udalosti môžete pridať do okna kódu listu hárka, rovnako ako sme to urobili v pracovnom hárku.

Dvakrát kliknite na objekt hárka grafu v programe Project Explorer. Tým sa otvorí okno kódu pre list grafu.

Teraz musíte z rozbaľovacej ponuky v ľavej hornej časti okna kódu vybrať graf.

Po výbere grafu z rozbaľovacieho zoznamu získate zoznam všetkých udalostí spojených s hárkom grafu. V rozbaľovacej ponuke v pravom hornom rohu okna kódu môžete vybrať ten, ktorý chcete použiť.

Poznámka: Hneď ako v rozbaľovacej ponuke vyberiete položku Graf, všimnete si, že v okne kódu sa zobrazia dva riadky kódu. Hneď ako vyberiete udalosť, pre ktorú chcete kód, môžete odstrániť riadky, ktoré sa zobrazili v predvolenom nastavení.

V module triedy

Moduly triedy je potrebné vložiť rovnako ako UserForms.

Modul triedy môže obsahovať kód týkajúci sa aplikácie - čo by bol samotný Excel a vložené grafy.

Modulu triedy sa budem v nasledujúcich týždňoch venovať samostatným návodom.

Všimnite si toho, že okrem udalostí OnTime a OnKey nemôže byť žiadna z vyššie uvedených udalostí uložená v bežnom module VBA.

Pochopenie sekvencie udalostí

Keď spustíte udalosť, nestane sa izolovane. Môže to tiež viesť k sledu viacerých spúšťačov.

Keď napríklad vložíte nový pracovný hárok, stanú sa nasledujúce veci:

1. Pridáva sa nový pracovný list
2. Predchádzajúci hárok sa deaktivuje
3. Nový pracovný list sa aktivuje

Aj keď si vo väčšine prípadov so sekvenciou nemusíte robiť starosti, ak vytvárate komplexné kódy, ktoré sa spoliehajú na udalosti, je lepšie sekvenciu poznať, aby ste sa vyhli neočakávaným výsledkom.

Pochopenie úlohy argumentov pri udalostiach VBA

Predtým, ako prejdeme k príkladom udalostí a úžasným veciam, ktoré s nimi môžete urobiť, je tu jeden dôležitý koncept, ktorý musím pokryť.

Na podujatiach VBA by existovali dva typy kódov:

• Bez akýchkoľvek argumentov
• S argumentmi

A v tejto časti chcem rýchlo pokryť úlohu argumentov.

Nasleduje kód, ktorý neobsahuje žiadny argument (zátvorky sú prázdne):

Súkromný čiastkový zošit_Open () MsgBox „Nezabudnite vyplniť časový rozvrh“ Koncový pod

Pri vyššie uvedenom kóde sa pri otvorení zošita jednoducho zobrazí pole so správou so správou - „Nezabudnite vyplniť pracovný výkaz“.

Teraz sa pozrime na kód, ktorý má argument.

Súkromný čiastkový zošit_Nový list (objekt ByVal Sh ako objekt) Sh.Range ("A1") = Sh.Name End Sub

Vyššie uvedený kód používa argument Sh, ktorý je definovaný ako typ objektu. Argument Sh môže byť pracovný hárok alebo list grafu, pretože vyššie uvedená udalosť sa spustí po pridaní nového hárka.

Priradením nového hárka, ktorý je pridaný do zošita, k objektovej premennej Sh, nám VBA umožnil použiť ho v kóde. Aby som sa mohol odvolať na nový názov hárka, môžem použiť Sh.Name.

Koncept argumentov bude užitočný, keď si v nasledujúcich častiach prečítate príklady udalostí VBA.

Udalosti na úrovni zošita (vysvetlené s príkladmi)

Nasledujú najčastejšie používané udalosti v zošite.

NÁZOV UDÁLOSTI	ČO SPUSTÍ UDALOSŤ
Aktivovať	Keď je aktivovaný zošit
AfterSave	Keď je zošit nainštalovaný ako doplnok
BeforeSave	Keď je zošit uložený
BeforeClose	Keď je zošit zatvorený
BeforePrint	Keď je vytlačený zošit
Deaktivovať	Keď je zošit deaktivovaný
Nový list	Keď sa pridá nový list
Otvorené	Keď je otvorený zošit
SheetActivate	Keď je aktivovaný akýkoľvek list v zošite
SheetBeforeDelete	Keď sa odstráni akýkoľvek list
SheetBeforeDoubleClick	Keď dvakrát kliknete na ktorýkoľvek list
SheetBeforeRightClick	Keď kliknete pravým tlačidlom myši na ktorýkoľvek list
SheetCalculate	Keď je vypočítaný alebo prepočítaný akýkoľvek hárok
SheetDeactivate	Keď je zošit deaktivovaný
SheetPivotTableUpdate	Keď sa zošit aktualizuje
SheetSelectionChange	Keď sa zmení zošit
WindowActivate	Keď je aktivovaný zošit
Okno Deaktivovať	Keď je zošit deaktivovaný

Upozorňujeme, že toto nie je úplný zoznam. Kompletný zoznam nájdete tu.

Nezabudnite, že kód pre udalosť zošita je uložený v okne kódu objektu ThisWorkbook.

Teraz sa pozrime na niekoľko užitočných udalostí v zošite a zistíme, ako ich možno použiť vo vašej každodennej práci.

Otvorená udalosť zošita

Povedzme, že chcete používateľovi ukázať priateľskú pripomienku na vyplnenie jeho pracovných výkazov vždy, keď otvorí konkrétny zošit.

Na tento účel môžete použiť nasledujúci kód:

Súbor Sub Subbook_Open () MsgBox „Nezabudnite vyplniť časový rozvrh“ Koncový pod

Hneď ako otvoríte zošit s týmto kódom, zobrazí sa vám okno so správou so zadanou správou.

Pri práci s týmto kódom (alebo kódmi udalostí zošita vo všeobecnosti) je potrebné vedieť niekoľko vecí:

• Ak má zošit makro a chcete ho uložiť, musíte ho uložiť vo formáte .XLSM. V opačnom prípade by sa kód makra stratil.
• Vo vyššie uvedenom príklade by sa kód udalosti vykonal iba vtedy, ak sú povolené makrá. Môže sa zobraziť žltý pruh so žiadosťou o povolenie na povolenie makier. Kým to nie je povolené, kód udalosti sa nevykoná.
• Kód udalosti zošita je umiestnený v okne kódu objektu ThisWorkbook.

Tento kód môžete ďalej upresniť a zobraziť správu iba v piatok.

Nasledujúci kód by to urobil:

Súkromný čiastkový zošit_Open () wkday = deň v týždni (dátum) Ak wkday = 6, potom MsgBox „Nezabudnite vyplniť pracovný výkaz“ End Sub

Všimnite si toho, že vo funkcii Deň v týždni je nedeli priradená hodnota 1, pondelku sú 2 a podobne.

Preto som v piatok použil 6.

Otvorená udalosť zošita môže byť užitočná v mnohých situáciách, ako napríklad:

• Keď chcete osobe ukázať uvítaciu správu, keď je otvorený zošit.
• Keď chcete zobraziť pripomienku pri otvorení zošita.
• Ak chcete v zošite vždy aktivovať jeden konkrétny pracovný hárok, keď je otvorený.
• Keď chcete otvoriť súvisiace súbory spolu so zošitom.
• Ak chcete zachytiť dátum a časovú pečiatku pri každom otvorení zošita.

Zošit Udalosť NewSheet

Udalosť NewSheet sa spustí, keď do zošita vložíte nový list.

Povedzme, že chcete zadať hodnotu dátumu a času do bunky A1 novo vloženého hárka. Na tento účel môžete použiť nasledujúci kód:

Súbor Sub Subbook_NewSheet (objekt ByVal Sh As) pri chybe Pokračovať ďalej Sh.Range ("A1") = formát (teraz "dd-mmm-rrrr hh: mm: ss") Koniec

Vyššie uvedený kód používa príkaz „On Error Resume Next“ na riešenie prípadov, keď niekto vloží hárok grafu, a nie pracovný hárok. Keďže hárok grafu nemá bunku A1, v prípade, že sa nepoužije „Pri chybe Pokračovať ďalej“, bude sa zobrazovať chyba.

Ďalším príkladom môže byť prípad, keď chcete na nový hárok použiť základné nastavenie alebo formátovanie hneď po jeho pridaní. Ak napríklad chcete pridať nový hárok a chcete, aby automaticky získal sériové číslo (až 100), môžete použiť kód uvedený nižšie.

Súkromný čiastkový zošit_Nový list (objekt ByVal Sh ako objekt) Pri chybe Pokračovať ďalej pomocou Sh.Range ("A1"). Hodnota = "S. č." .Interior.Color = vbBlue .Font.Color = vbWhite End With For i = 1 až 100 Sh.Range ("A1"). Offset (i, 0) .Hodnota = i Next i Sh.Range ("A1", Range ("A1"). Koniec (xlDown)). Borders.LineStyle = xlSpojitý koniec Sub

Vyššie uvedený kód tiež trochu formátuje. Bunke hlavičky dá modrú farbu a písmo bude biele. Na všetky vyplnené bunky tiež použije orámovanie.

Vyššie uvedený kód je príkladom toho, ako vám krátky kód VBA môže pomôcť ukradnúť niekoľko sekúnd pri každom vložení nového pracovného hárka (v prípade, že je to niečo, čo musíte urobiť zakaždým).

Zošit Udalosť pred uložením

Pred uložením zošita sa spustí udalosť Pred uložením. Upozorňujeme, že najskôr sa spustí udalosť a potom sa uloží zošit.

Pri ukladaní zošita programu Excel môžu existovať dva možné scenáre:

1. Ukladáte ho prvýkrát a zobrazí sa dialógové okno Uložiť ako.
2. Už ste ho uložili skôr a jednoducho uloží a prepíše zmeny v už uloženej verzii.

Teraz sa pozrime na niekoľko príkladov, kde môžete použiť udalosť BeforeSave.

Predpokladajme, že máte nový zošit, ktorý ukladáte prvýkrát, a chcete používateľovi pripomenúť, aby ho uložil na disk K, potom môžete použiť nasledujúci kód:

Súkromný čiastkový zošit_BeforeSave (ByVal SaveAsUI ako Boolean, Zrušiť ako Boolean) Ak SaveAsUI, potom MsgBox „Uložiť tento súbor na disk K“ Koniec Sub

Ak vo vyššie uvedenom kóde nebol súbor nikdy uložený, SaveAsUI má hodnotu True a vyvolá dialógové okno Uložiť ako. Vyššie uvedený kód by zobrazil správu pred zobrazením dialógového okna Uložiť ako.

Ďalším príkladom môže byť aktualizácia dátumu a času, kedy je súbor uložený v konkrétnej bunke.

Nasledujúci kód vloží pečiatku dátumu a času do bunky A1 listu 1 pri každom uložení súboru.

Súkromný čiastkový zošit_BeforeSave (ByVal SaveAsUI ako logická hodnota, Zrušiť ako logická hodnota) Pracovné listy („List1“). Rozsah („A1“) = Formát (Teraz „dd-mmm-rrrr hh: mm: ss“) Koniec sub

Všimnite si toho, že tento kód sa spustí, hneď ako užívateľ uloží zošit. Ak sa zošit ukladá prvýkrát, zobrazí sa dialógové okno Uložiť ako. Kód je však už spustený v čase, keď sa vám zobrazí dialógové okno Uložiť ako. Ak sa v tomto bode rozhodnete zošit zrušiť a neuložiť, dátum a čas by už bol do bunky zadaný.

Zošit Udalosť BeforeClose

Udalosť Before Close sa stane tesne pred zatvorením zošita.

Nasledujúci kód chráni všetky pracovné hárky pred zatvorením zošita.

Súkromný čiastkový zošit_BeforeClose (Zrušiť ako booleovský) Dim sh as As worksheet for each sh In ThisWorkbook.Worksheets sh.Protect Next sh End Sub

Nezabudnite, že kód udalosti sa spustí ihneď po zatvorení zošita.

Jedna dôležitá vec, ktorú je potrebné vedieť o tejto udalosti, je, že jej je jedno, či je zošit skutočne zatvorený alebo nie.

V prípade, že zošit nebol uložený a zobrazí sa výzva, či sašit uložiť alebo nie, a kliknete na položku Zrušiť, zošit sa neuloží.Kód udalosti by však už bol do tej doby vykonaný.

Zošit Udalosť pred tlačou

Keď zadáte príkaz na tlač (alebo príkaz Náhľad), spustí sa udalosť Pred tlačou.

Nasledujúci kód by pred vytlačením zošita prepočítal všetky pracovné hárky.

Súkromný čiastkový zošit_BeforePrint (zrušiť ako booleovskú hodnotu) pre každé ws v pracovných listoch ws. Vypočítať ďalšie ws Koniec Sub

Keď používateľ tlačí zošit, udalosť by sa spustila bez ohľadu na to, či tlačí celý zošit alebo iba jeho časť.

Ďalším príkladom nižšie je kód, ktorý by pri tlači zošita pridal dátum a čas do päty.

Súkromný čiastkový zošit_BeforePrint (Zrušiť ako booleovský) Dim ws as worksheet for each ws In ThisWorkbook.Worksheets ws.PageSetup.LeftFooter = "Printed On-" & Format (Now, "dd-mmm-yyyy hh: mm") Next ws End Sub

Udalosti na úrovni pracovného hárka (vysvetlené s príkladmi)

Udalosti pracovného hárka sa konajú na základe spúšťačov v pracovnom hárku.

Nasledujú najčastejšie používané udalosti v pracovnom hárku.

Názov udalosti	Čo spúšťa udalosť
Aktivovať	Keď je pracovný list aktivovaný
Pred odstránením	Pred odstránením pracovného hárka
BeforeDoubleClick	Pred dvojitým kliknutím na pracovný hárok
BeforeRightClick	Predtým, ako kliknete pravým tlačidlom myši na pracovný hárok
Vypočítajte	Pred výpočtom alebo prepočtom pracovného hárka
Zmeniť	Keď sa zmenia bunky v pracovnom hárku
Deaktivovať	Keď je pracovný hárok deaktivovaný
PivotTableUpdate	Keď sa kontingenčná tabuľka v pracovnom hárku aktualizuje
SelectionChange	Keď sa zmení výber v pracovnom hárku

Upozorňujeme, že toto nie je úplný zoznam. Kompletný zoznam nájdete tu.

Nezabudnite, že kód pre udalosť pracovného hárka je uložený v okne kódu objektu pracovného hárka (v tom, v ktorom chcete, aby sa udalosť spustila). V pracovnom zošite môže byť viac pracovných hárkov a váš kód sa spustí iba vtedy, keď sa udalosť uskutoční v pracovnom hárku, v ktorom je umiestnená.

Teraz sa pozrime na niekoľko užitočných udalostí pracovného hárka a zistíme, ako ich možno použiť vo vašej každodennej práci.

Pracovný list Aktivovať udalosť

Táto udalosť sa spustí, keď aktivujete pracovný hárok.

Nasledujúci kód nechráni hárok hneď po jeho aktivácii.

Private Sub Worksheet_Activate () ActiveSheet.Unprotect End Sub

Túto udalosť môžete použiť aj na zaistenie toho, aby bola vybratá konkrétna bunka alebo rozsah buniek (alebo pomenovaný rozsah) hneď po aktivácii pracovného hárka. Nasledujúci kód by vybral bunku D1 hneď po aktivácii hárka.

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

Udalosť zmeny pracovného hárka

Udalosť zmeny sa spustí vždy, keď v hárku vykonáte zmenu.

No … nie vždy.

Udalosť spôsobuje niekoľko zmien a niektoré nie. Tu je zoznam niektorých zmien, ktoré nespôsobia udalosť:

• Keď zmeníte formátovanie bunky (veľkosť písma, farba, orámovanie atď.).
• Keď zlúčite bunky. Je to prekvapujúce, pretože zlúčenie buniek niekedy odstráni obsah zo všetkých buniek okrem ľavej hornej.
• Keď pridáte, odstránite alebo upravíte komentár k bunke.
• Keď zoradíte rozsah buniek.
• Keď používate funkciu Hľadanie cieľa.

Nasledujúce zmeny by spustili udalosť (aj keď si možno myslíte, že by nemala):

• Udalosť by spustilo formátovanie kopírovania a vkladania.
• Udalosť by spustilo vymazanie formátovania.
• Spustenie kontroly pravopisu by spustilo udalosť.

Nasleduje kód, ktorý by zobrazil pole so správou s adresou bunky, ktorá bola zmenená.

Súkromný čiastkový pracovný list_Zmeniť (ByVal cieľ ako rozsah) MsgBox „Práve ste sa zmenili“ & Target.Address End Sub

Aj keď je to zbytočné makro, ukazuje vám, ako pomocou argumentu Cieľ zistiť, ktoré bunky boli zmenené.

Teraz sa pozrime na niekoľko užitočných príkladov.

Predpokladajme, že máte rozsah buniek (povedzme A1: D10) a chcete zobraziť výzvu a opýtať sa používateľa, či skutočne chcel zmeniť bunku v tomto rozsahu alebo nie, môžete použiť nižšie uvedený kód.

Zobrazí sa výzva s dvoma tlačidlami - Áno a Nie. Ak používateľ zvolí „Áno“, zmena sa vykoná, v opačnom prípade sa obráti.

Súkromný čiastkový pracovný list_Zmeniť (ByVal cieľ ako rozsah) Ak Target.Row <= 10 A Target.Column <= 4 Potom Ans = MsgBox ("Vykonávate zmenu v bunkách v A1: D10. Ste si istí, že to chcete?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

Vo vyššie uvedenom kóde kontrolujeme, či je cieľová bunka v prvých 4 stĺpcoch a prvých 10 riadkoch. Ak je to tak, zobrazí sa okno so správou. Ak používateľ v poli so správou vybral možnosť Nie, zmena sa zmení (príkazom Application.Undo).

Všimnite si toho, že som použil Application.EnableEvents = False pred riadkom Application.Undo. A potom som to obrátil pomocou Application.EnableEvent = True v nasledujúcom riadku.

Je to potrebné, pretože keď dôjde k vráteniu späť, spustí sa aj udalosť zmeny. Ak nenastavím položku EnableEvent na hodnotu False, bude naďalej spúšťať udalosť zmeny.

Zmeny v pomenovanom rozsahu môžete monitorovať aj pomocou udalosti zmeny. Ak máte napríklad pomenovaný rozsah s názvom „DataRange“ a chcete zobraziť výzvu v prípade, že používateľ v tomto pomenovanom rozsahu vykoná zmenu, môžete použiť kód uvedený nižšie:

Súkromný čiastkový pracovný hárok_Zmeniť (ByVal cieľ ako rozsah) Dim DRange ako rozsah Nastaviť DRange = rozsah („DataRange“) Ak sa nepretína (cieľ, DRange) nie je nič, potom MsgBox „Práve ste vykonali zmenu v rozsahu údajov“ End If End Sub

Vyššie uvedený kód kontroluje, či bunka/rozsah, v ktorom ste vykonali zmeny, obsahuje bunky spoločné pre rozsah údajov. Ak áno, zobrazí sa okno so správou.

Udalosť SešitChange zošita

Udalosť zmeny výberu sa spustí vždy, keď dôjde k zmene výberu v pracovnom hárku.

Nasledujúci kód by prepočítal list hneď, ako zmeníte výber.

Súkromný čiastkový pracovný hárok_VýberChange (ByVal cieľ ako rozsah) Aplikácia. Vypočítať koncovú pod

Ďalším príkladom tejto udalosti je, keď chcete zvýrazniť aktívny riadok a stĺpec vybratej bunky.

Niečo, ako je uvedené nižšie:

Môže to urobiť nasledujúci kód:

Súbor Sub Subheet_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) End with Koniec pod

Kód najskôr odstráni farbu pozadia zo všetkých buniek a potom aplikuje farbu uvedenú v kóde na aktívny riadok a stĺpec.

A to je problém tohto kódu. Že odstráni farbu zo všetkých buniek.

Ak chcete zvýrazniť aktívny riadok/stĺpec a zároveň zachovať farbu v ostatných bunkách nedotknutú, použite techniku ​​uvedenú v tomto návode.

Zošita Udalosť DoubleClick

Toto je jedna z mojich obľúbených udalostí pracovného hárka a uvidíte veľa návodov, kde som to použil (napríklad tento alebo tento).

Táto udalosť sa spustí, keď dvakrát kliknete na bunku.

Ukážem vám, aké úžasné je to.

Pomocou nižšie uvedeného kódu môžete dvakrát kliknúť na bunku a použije farbu pozadia, zmení farbu písma a zvýrazní text v bunke;

Súbor Sub Subheet_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

To môže byť užitočné, keď prechádzate zoznamom buniek a chcete zvýrazniť niekoľko vybratých. Aj keď môžete posledný krok zopakovať pomocou klávesu F4, bude môcť použiť iba jeden druh formátovania. S touto udalosťou dvojitého kliknutia môžete použiť všetky tri kliknutím dvakrát.

Všimnite si toho, že vo vyššie uvedenom kóde som urobil hodnotu Cancel = True.

To sa deje tak, že je deaktivovaná predvolená akcia dvojitého kliknutia - to znamená dostať sa do režimu úprav. Ak použijete Cancel = True, Excel vás nedostane do režimu úprav, keď dvakrát kliknete na bunku.

Tu je ďalší príklad.

Ak máte v Exceli zoznam úloh, môžete na označenie úlohy ako splnenej použiť udalosť dvojitého kliknutia a použiť prečiarknutý formát.

Niečo, ako je uvedené nižšie:

Tu je kód, ktorý to urobí:

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šimnite si toho, že v tomto kóde som urobil dvojité kliknutie ako prepínaciu udalosť. Keď dvakrát kliknete na bunku, skontroluje, či už bol použitý prečiarknutý formát. Ak bola, dvojitým kliknutím odstráni prečiarknutý formát, a ak nie, použije sa prečiarknutý formát.

Udalosť OnTime programu Excel VBA

Udalosti, ktoré sme doteraz videli v tomto článku, boli spojené s jedným z objektov programu Excel, či už išlo o zošit, pracovný hárok, hárok grafu alebo UserForms atď.

Udalosť OnTime sa líši od ostatných udalostí, pretože môže byť uložená v bežnom module VBA (zatiaľ čo ostatné mali byť umiestnené v okne kódu objektov, ako sú ThisWorkbook alebo Worksheets alebo UserForms).

V rámci bežného modulu VBA sa používa ako metóda aplikačného objektu.

Dôvodom, prečo sa to považuje za udalosť, je to, že môže byť spustený na základe vami zadaného času. Ak napríklad chcem, aby sa hárok prepočítaval každých 5 minút, môžem na to použiť udalosť OnTime.

Alebo, ak chcem ukázať správu/pripomenutie v konkrétnom čase dňa, môžem použiť udalosť OnTime.

Nasleduje kód, ktorý každý deň o 14:00 zobrazí správu.

Sub MessageTime () Application.OnTime TimeValue („14:00:00“), „ShowMessage“ Koniec Sub Sub ShowMessage () MsgBox „Je čas obeda“ Koniec Sub

Nezabudnite, že tento kód musíte vložiť do bežného modulu VBA,

Aj keď sa udalosť OnTime spustí v uvedenom čase, makro musíte spustiť manuálne kedykoľvek. Akonáhle makro spustíte, počká, kým nebude 14:00, a potom zavoláte makro „ShowMessage“.

Makro ShowMessage by potom zobrazilo správu.

Udalosť OnTime má štyri argumenty:

Application.OnTime (EarliestTime, Postup, LatestTime, Rozvrh)

• EarliestTime: Čas, kedy chcete spustiť procedúru.
• Postup: Názov postupu, ktorý by mal byť spustený.
• LatestTime (voliteľné): V prípade, že je spustený iný kód a váš zadaný kód nie je možné spustiť v uvedenom čase, môžete zadať LatestTime, na ktorý by mal čakať. Môže to byť napríklad EarliestTime + 45 (čo znamená, že bude 45 sekúnd čakať, kým sa nedokončí ďalší postup). Ak ani po 45 sekundách nie je možné procedúru spustiť, bude ukončená. Ak to neurčíte, Excel počká, kým sa kód dá spustiť, a potom ho spustí.
• Plán (voliteľné): Ak je nastavený na hodnotu True, naplánuje nový časový postup. Ak je nepravdivé, potom sa zruší predtým nastavený postup. Štandardne je to pravda.

Vo vyššie uvedenom príklade sme použili iba prvé dva argumenty.

Pozrime sa na ďalší príklad.

Nasledujúci kód by obnovil pracovný hárok každých 5 minút.

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

Vyššie uvedený kód by obnovil pracovný hárok každých 5 minút.

Na zistenie aktuálneho času používa funkciu Teraz a potom k aktuálnemu času pridá 5 minút.

Udalosť OnTime bude pokračovať, kým ju nezastavíte. Ak zatvoríte zošit a aplikácia Excel je stále spustená (ostatné zošity sú otvorené), zošit, v ktorom je spustená udalosť OnTime, by sa sám znova otvoril.

To je možné lepšie vyriešiť konkrétnym zastavením udalosti OnTime.

Vo vyššie uvedenom kóde mám kód StopRefresh, ale musíte ho spustiť, aby ste zastavili udalosť OnTime. Môžete to urobiť ručne, priradiť to tlačidlu a urobiť to stlačením tlačidla alebo to zavolať z udalosti Zavrieť zošit.

Súkromný čiastkový zošit_BeforeClose (Zrušiť ako booleovský) Zavolať StopRefresh Ukončiť pod

Vyššie uvedený kód udalosti „BeforeClose“ prejde do okna kódu ThisWorkbook.

Udalosť OnKey programu Excel VBA

Keď pracujete s Excelom, nepretržite monitoruje používané klávesové skratky. To nám umožňuje použiť stlačenie klávesov ako spúšťač udalosti.

Pri udalosti OnKey môžete zadať stlačenie klávesu (alebo kombináciu klávesov) a kód, ktorý sa má vykonať pri použití tohto stlačenia klávesu. Po stlačení týchto klávesov sa spustí príslušný kód.

Rovnako ako udalosť OnTime, musíte mať spôsob, ako zrušiť udalosť OnKey. Keď nastavíte udalosť OnKey pre konkrétny stisk klávesu, bude k dispozícii vo všetkých otvorených zošitoch.

Predtým, ako vám ukážem príklad použitia udalosti OnKey, dovoľte mi najskôr zdieľať kľúčové kódy, ktoré máte k dispozícii vo VBA.

KĽÚČ	KÓD
Backspace	{BACKSPACE} alebo {BS}
Prestávka	{PRESTÁVKA}
Zámok veľkých písmen	{ZÁMOK VEĽKÝCH PÍSMEN}
Vymazať	{DELETE} alebo {DEL}
Šípka dole	{DOWN}
Koniec	{KONIEC}
Zadajte	~
Enter (na nuerickej klávesnici)	{ENTER}
Uniknúť	{ESCAPE} alebo {ESC}
Domov	{DOMOV}
Ins	{INSERT}
Ľavá šípka	{LEFT}
NumLock	{NUMLOCK}
O stranu nižšie	{PGDN}
Strana hore	{PGUP}
Pravá šípka	{SPRÁVNY}
Scroll Lock	{SCROLLOCK}
Tab	{TAB}
Šípka hore	{UP}
F1 až F15	{F1} až {F15}

Keď potrebujete použiť akúkoľvek udalosť na kľúči, musíte na to použiť kód.

Vyššie uvedená tabuľka obsahuje kódy pre jednotlivé stlačenia klávesov.

Môžete ich tiež skombinovať s nasledujúcimi kódmi:

• Posun: + (Znamienko plus)
• Ovládanie: ^ (Strieška)
• Alt: % (Percento)

Napríklad pre Alt F4 musíte použiť kód: „%{F4}” - kde % je pre kláves ALT a {F4} je pre kláves F4.

Teraz sa pozrime na príklad (pamätajte, že kód pre udalosti OnKey sú umiestnené v bežnom module VBA).

Keď stlačíte kláves PageUp alebo PageDown, preskočí 29 riadkov nad/pod aktívnu bunku (aspoň to robí na mojom prenosnom počítači).

Ak chcete, aby preskočilo iba 5 riadkov naraz, môžete použiť nasledujúci kód:

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 () On Error Resume Next ActiveCell.Offset (5, 0) .Activate End Sub

Keď spustíte prvú časť kódu, spustia sa udalosti OnKey. Akonáhle je to vykonané, pomocou PageUp a klávesu PageDown by kurzor preskočil iba 5 riadkov naraz.

Všimnite si toho, že sme použili „On Error Resume Next“, aby sme zaistili, že chyby budú ignorované. Tieto chyby sa môžu vyskytnúť, keď stlačíte kláves PageUp, aj keď ste v hornej časti pracovného hárka. Pretože už nie sú k dispozícii žiadne riadky na skok, kód by zobrazil chybu. Pretože sme však použili možnosť „On Error Resume Next“, bude ignorovaná.

Aby ste sa uistili, že sú tieto udalosti OnKey k dispozícii, musíte spustiť prvú časť kódu. V prípade, že chcete, aby to bolo k dispozícii hneď po otvorení zošita, môžete to vložiť do okna s kódom ThisWorkbook.

Súbor Sub Subbook_Open () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub

Nasledujúci kód vráti kľúčom ich normálnu funkciu.

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

Ak v metóde OnKey nezadáte druhý argument, vráti stlačenie klávesu do normálnej funkčnosti.

V prípade, že chcete zrušiť funkčnosť stlačenia klávesu, aby program Excel pri použití tohto klávesu nerobil nič, musíte ako druhý argument použiť prázdny reťazec.

V nižšie uvedenom kóde by Excel nerobil nič, keby sme použili klávesy PageUp alebo PageDown.

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

Zakázanie udalostí vo VBA

Niekedy môže byť potrebné vypnúť udalosti, aby váš kód fungoval správne.

Predpokladajme napríklad, že mám rozsah (A1: D10) a chcem zobraziť správu vždy, keď sa v tomto rozsahu zmení bunka. Ukážem teda okno so správou a opýtam sa používateľa, či si je istý, že chce vykonať zmenu. Ak odpoviete áno, vykoná sa zmena a ak odpoviete nie, VBA by to vrátila.

Môžete použiť nasledujúci kód:

Súkromný čiastkový pracovný list_Zmeniť (ByVal cieľ ako rozsah) Ak Target.Row <= 10 A Target.Column <= 4 Potom Ans = MsgBox ("Vykonávate zmenu v bunkách v A1: D10. Ste si istí, že to chcete?", vbYesNo) Koniec, ak Ans = vbNo Potom aplikácia. Zrušiť Koniec, ak Koniec Sub

Problém s týmto kódom je ten, že keď používateľ v okne so správou zvolí Nie, akcia sa obráti (ako som použil Application.Undo).

Akonáhle dôjde k vráteniu späť a hodnota sa zmení späť na pôvodnú hodnotu, znova sa spustí udalosť zmeny VBA a používateľovi sa opäť zobrazí rovnaké pole správ.

To znamená, že v poli so správou môžete naďalej klikať na NIE a bude sa naďalej zobrazovať. Stáva sa to, pretože ste v tomto prípade uviazli v nekonečnej slučke.

Aby ste sa vyhli takýmto prípadom, musíte vypnúť udalosti, aby sa nespustila udalosť zmeny (alebo akákoľvek iná udalosť).

Nasledujúci kód by v tomto prípade fungoval dobre:

Súkromný čiastkový pracovný list_Zmeniť (ByVal cieľ ako rozsah) Ak Target.Row <= 10 A Target.Column <= 4 Potom Ans = MsgBox ("Vykonávate zmenu v bunkách v A1: D10. Ste si istí, že to chcete?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

Vo vyššie uvedenom kóde, priamo nad riadkom Application.Undo, sme použili - Application.EnableEvents = False.

Nastavením hodnoty EnableEvents na hodnotu False sa nespustí žiadna udalosť (v aktuálnom ani v otvorenom zošite).

Po dokončení operácie vrátenia späť môžeme prepnúť vlastnosť EnableEvents späť na hodnotu True.

Majte na pamäti, že deaktivácia udalostí má vplyv na všetky zošity, ktoré sú aktuálne otvorené (alebo sa otvárajú, keď je EnableEvents nastavené na hodnotu False). Napríklad ako súčasť kódu, ak otvoríte nový zošit, udalosť Otvorenie zošita nebude fungovať.

Vplyv udalostí Späť na zásobník

Najprv vám poviem, čo je to Undo Stack.

Keď pracujete v Exceli, neustále sleduje vaše akcie. Ak sa pomýlite, vždy sa môžete pomocou klávesov Control + Z vrátiť k predchádzajúcemu kroku (t.j. vrátiť späť svoju aktuálnu akciu).

Ak dvakrát stlačíte Control + Z, vrátite sa o dva kroky späť. Tieto kroky, ktoré ste vykonali, sú uložené ako súčasť zásobníka späť.

Každá udalosť, ktorá zmení pracovný hárok, zničí tento zásobník späť.To znamená, že ak som urobil 5 vecí pred spustením udalosti, nebudem sa môcť pomocou Control + Z vrátiť k týmto predchádzajúcim krokom. Spustenie udalosti mi zničilo tento zásobník.

V nižšie uvedenom kóde používam VBA na zadanie časovej pečiatky do bunky A1 vždy, keď dôjde k zmene v pracovnom hárku.

Súkromný čiastkový pracovný list_Zmeniť (ByVal cieľ ako rozsah) Aplikácia.EnableEvents = nepravdivý rozsah ("A1"). Hodnota = formát (teraz "dd-mmm-rrrr hh: mm: ss") Application.EnableEvents = True End Sub

Keďže v pracovnom hárku robím zmenu, zruší sa tým zásobník späť.

Upozorňujeme, že sa to netýka iba udalostí.

Ak máte kód uložený v bežnom module VBA a vykonáte zmenu v pracovnom hárku, zničí to aj zásobník späť v programe Excel.

Nasledujúci kód napríklad jednoducho zadajte do bunky A1 text „Ahoj“, ale aj spustením by sa zničil zásobník späť.

PodtypDobrý deň () Rozsah („A1“). Hodnota = „Dobrý deň“ Koncový podrad

Tiež by sa vám mohli páčiť nasledujúce návody na Excel VBA:

• Práca s bunkami a rozsahmi v programe Excel VBA.
• Práca s pracovnými listami v programe Excel VBA.
• Práca so zošitmi v programe Excel VBA.
• Excel VBA Loops - Ultimate Guide.
• Použitie príkazu IF Then Else v programe Excel VBA.
• Pre ďalšiu slučku v programe Excel.
• Vytváranie funkcií definovaných používateľom v programe Excel VBA.
• Ako vytvárať a používať doplnky v programe Excel.
• Vytvárajte a opakovane používajte makrá uložením do osobného zošita makier.