Události aplikace Excel VBA - snadný (a kompletní) průvodce

Když vytváříte nebo zaznamenáváte makro v aplikaci Excel, je třeba spustit makro a provést kroky v kódu.

Několik způsobů spuštění makra zahrnuje použití dialogového okna makra, přiřazení makra k tlačítku, použití zkratky atd.

Kromě těchto uživatelem spuštěných maker můžete ke spuštění makra použít také události VBA.

Události aplikace Excel VBA - úvod

Dovolte mi nejprve vysvětlit, co je událost ve VBA.

Událost je akce, která může spustit spuštění zadaného makra.

Když například otevřete nový sešit, je to událost. Když vložíte nový list, je to událost. Když dvakrát kliknete na buňku, je to událost.

Ve VBA existuje mnoho takových událostí a můžete pro ně vytvořit kódy. To znamená, že jakmile dojde k události, a pokud jste pro tuto událost zadali kód, tento kód bude okamžitě spuštěn.

Excel to automaticky provede, jakmile si všimne, že došlo k události. Stačí tedy napsat kód a umístit jej do podprogramu správné události (o tom pojednává dále v tomto článku).

Pokud například vložíte nový list a chcete, aby měl předponu roku, můžete pro něj napsat kód.

Nyní, kdykoli kdokoli vloží nový list, bude tento kód automaticky spuštěn a do názvu listu přidá předponu roku.

Dalším příkladem může být, že chcete změnit barvu buňky, když na ni někdo dvakrát klikne. K tomu můžete použít událost dvojitého kliknutí.

Podobně můžete pro mnoho takových událostí vytvářet kódy VBA (jak uvidíme dále v tomto článku).

Níže je krátký vizuál, který ukazuje událost dvojitého kliknutí v akci. Jakmile dvakrát kliknu na buňku A1. Excel okamžitě otevře pole se zprávou, které zobrazuje adresu buňky.

Poklepání je událost a zobrazení pole se zprávou je to, co jsem zadal v kódu, kdykoli dojde k události dvojitého kliknutí.

I když je výše uvedený příklad zbytečnou událostí, doufám, že vám pomůže pochopit, jaké události skutečně jsou.

Různé typy událostí Excel VBA

V Excelu existují různé objekty - například samotný Excel (kterému často říkáme aplikace), sešity, pracovní listy, grafy atd.

Ke každému z těchto objektů mohou být přidruženy různé události. Například:

• Pokud vytvoříte nový sešit, je to událost na úrovni aplikace.
• Pokud přidáte nový list, je to událost na úrovni sešitu.
• Pokud změníte hodnotu v buňce v listu, je to událost na úrovni listu.

Níže jsou uvedeny různé typy událostí, které existují v aplikaci Excel:

1. Události na úrovni pracovního listu: Toto jsou typy událostí, které by se spustily na základě akcí provedených v listu. Mezi příklady těchto událostí patří změna buňky v listu, změna výběru, dvojité kliknutí na buňku, pravé kliknutí na buňku atd.
2. Události na úrovni sešitu: Tyto události by se spouštěly na základě akcí na úrovni sešitu. Mezi příklady těchto událostí patří přidání nového listu, uložení sešitu, otevření sešitu, tisk části nebo celého sešitu atd.
3. Události na úrovni aplikace: Jedná se o události, ke kterým dochází v aplikaci Excel. Mezi tyto příklady patří zavření některého z otevřených sešitů nebo otevření nového sešitu.
4. Události na úrovni UserForm: Tyto události by se spouštěly na základě akcí v ‘UserForm’. Mezi tyto příklady patří inicializace UserForm nebo kliknutí na tlačítko v UserForm.
5. Události grafu: Jedná se o události související s listem grafu. List grafu se liší od listu (což je místo, kde je většina z nás zvyklá pracovat v aplikaci Excel). Účelem grafů je držet graf. Příklady takových událostí mohou zahrnovat změnu řady grafu nebo změnu velikosti grafu.
6. Události OnTime a OnKey: Toto jsou dvě události, které se nehodí do žádné z výše uvedených kategorií. Uvedl jsem je tedy samostatně. Událost „OnTime“ vám umožňuje spustit kód v určitém čase nebo po uplynutí určitého času. Událost „OnKey“ vám umožňuje spustit kód při použití konkrétního stisknutí klávesy (nebo kombinace kláves).

Kam vložit kód související s událostí

Ve výše uvedené části jsem se zabýval různými typy událostí.

Na základě typu události je třeba vložit kód do příslušného objektu.

Pokud se jedná například o událost související s listem, měla by jít do okna kódu objektu listu. Pokud se týká sešitu, mělo by jít do okna kódu pro objekt sešitu.

Ve VBA mají různé objekty - například sešity, sešity, grafy, UserForms atd. Svá vlastní okna kódů. Kód události musíte vložit do okna kódu příslušného objektu. Například - pokud se jedná o událost na úrovni sešitu, pak musíte mít kód události v okně Kód sešitu.

Následující části pokrývají místa, kam můžete vložit kód události:

V okně Kód listu

Když otevřete editor VB (pomocí klávesové zkratky ALT + F11), všimnete si objektu listů v Průzkumníku projektů. Pro každý list v sešitu uvidíte jeden objekt.

Když dvakrát kliknete na objekt listu, do kterého chcete umístit kód, otevře se okno kódu pro tento list.

I když můžete začít psát kód úplně od začátku, je mnohem lepší vybrat událost ze seznamu možností a nechat VBA automaticky vložit příslušný kód pro vybranou událost.

Chcete-li to provést, musíte nejprve vybrat list z rozevíracího seznamu v levé horní části okna kódu.

Po výběru listu z rozevíracího seznamu získáte seznam všech událostí souvisejících s listem. Z rozevíracího seznamu v pravé horní části okna kódu můžete vybrat ten, který chcete použít.

Jakmile vyberete událost, automaticky zadá první a poslední řádek kódu pro vybranou událost. Nyní můžete přidat kód mezi dva řádky.

Poznámka: Jakmile v rozevíracím seznamu vyberete list, všimnete si dvou řádků kódu v okně kódu. Jakmile vyberete událost, pro kterou chcete kód, můžete odstranit řádky, které se ve výchozím nastavení zobrazily.

Každý list má vlastní okno kódu. Když zadáte kód pro List1, bude fungovat pouze v případě, že se událost stane v List1.

V okně Kód této pracovní knihy

Stejně jako listy, pokud máte kód události na úrovni sešitu, můžete jej umístit do okna kódu ThisWorkbook.

Když dvakrát kliknete na ThisWorkbook, otevře se mu okno s kódem.

Je třeba vybrat sešit z rozevíracího seznamu v levé horní části okna kódu.

Po výběru sešitu z rozevíracího seznamu získáte seznam všech událostí souvisejících se sešitem. Z rozevíracího seznamu v pravé horní části okna kódu můžete vybrat ten, který chcete použít.

Jakmile vyberete událost, automaticky zadá první a poslední řádek kódu pro vybranou událost. Nyní můžete přidat kód mezi dva řádky.

Poznámka: Jakmile v rozevíracím seznamu vyberete Sešit, všimnete si dvou řádků kódu v okně kódu. Jakmile vyberete událost, pro kterou chcete kód, můžete odstranit řádky, které se ve výchozím nastavení zobrazily.

V okně s kódem Userform

Při vytváření UserForms v aplikaci Excel můžete také použít události UserForm ke spouštění kódů na základě konkrétních akcí. Můžete například zadat kód, který se spustí po kliknutí na tlačítko.

Zatímco objekty Sheet a ThisWorkbook jsou již k dispozici při otevření editoru VB, UserForm je něco, co musíte nejprve vytvořit.

Chcete-li vytvořit UserForm, klikněte pravým tlačítkem na libovolný z objektů, přejděte na Vložit a klikněte na UserForm.

To by vložilo objekt UserForm do sešitu.

Když dvakrát kliknete na UserForm (nebo jakýkoli objekt, který přidáte do UserForm), otevře se okno kódu pro UserForm.

Nyní, stejně jako pracovní listy nebo ThisWorkbook, můžete vybrat událost a vloží první a poslední řádek pro tuto událost. A pak můžete přidat kód uprostřed.

V okně Kód grafu

V aplikaci Excel můžete také vložit listy grafů (které se liší od listů). List grafu má obsahovat pouze grafy.

Po vložení listu grafu uvidíte objekt listu grafu v editoru VB.

Kód události můžete přidat do okna kódu listu grafu stejně jako my v listu.

Poklepejte na objekt listu Graf v Průzkumníku projektů. Tím se otevře okno kódu pro list grafu.

Nyní musíte z rozevíracího seznamu v levé horní části okna kódu vybrat graf.

Po výběru grafu z rozevíracího seznamu získáte seznam všech událostí souvisejících s listem grafu. Z rozevíracího seznamu v pravé horní části okna kódu můžete vybrat ten, který chcete použít.

Poznámka: Jakmile z rozevíracího seznamu vyberete Graf, všimnete si dvou řádků kódu v okně kódu. Jakmile vyberete událost, pro kterou chcete kód, můžete odstranit řádky, které se ve výchozím nastavení zobrazily.

V modulu třídy

Moduly třídy je třeba vložit stejně jako UserForms.

Modul třídy může obsahovat kód související s aplikací - což by byl samotný Excel a vložené grafy.

V následujících týdnech se budu věnovat modulu třídy jako samostatnému kurzu.

Všimněte si toho, že kromě událostí OnTime a OnKey nelze do běžného modulu VBA ukládat žádnou z výše uvedených událostí.

Pochopení pořadí událostí

Když spustíte událost, nestane se izolovaně. Může to také vést k sekvenci více spouštěčů.

Když například vložíte nový list, stanou se následující věci:

1. Je přidán nový list
2. Předchozí list se deaktivuje
3. Nový list se aktivuje

I když si ve většině případů nemusíte dělat starosti se sekvencí, pokud vytváříte složité kódy, které se spoléhají na události, je lepší sekvenci znát, abyste se vyhnuli neočekávaným výsledkům.

Pochopení role argumentů v událostech VBA

Než přejdeme k příkladům událostí a úžasným věcem, které s nimi můžete dělat, je zde jeden důležitý koncept, který musím pokrýt.

V událostech VBA by existovaly dva typy kódů:

• Bez jakýchkoli argumentů
• S argumenty

A v této sekci chci rychle pokrýt roli argumentů.

Níže je kód, který neobsahuje žádný argument (závorky jsou prázdné):

Soukromý dílčí sešit_Otevřít () MsgBox „Nezapomeňte vyplnit časový rozvrh“ Koncový díl

S výše uvedeným kódem při otevření sešitu jednoduše zobrazí okno se zprávou - „Nezapomeňte vyplnit časový rozvrh“.

Nyní se podívejme na kód, který má argument.

Soukromý dílčí sešit_Nový list (objekt ByVal Sh jako objekt) Sh.Range ("A1") = Sh.Name End Sub

Výše uvedený kód používá argument Sh, který je definován jako typ objektu. Argument Sh může být list nebo list grafu, protože výše uvedená událost se spustí při přidání nového listu.

Přiřazením nového listu, který je přidán do sešitu, k objektové proměnné Sh, nám VBA umožnil jeho použití v kódu. Abych se mohl odvolat na nový název listu, mohu použít Sh.Name.

Koncept argumentů bude užitečný, když projdete příklady událostí VBA v následujících částech.

Události na úrovni sešitu (vysvětleno s příklady)

Níže jsou uvedeny nejčastěji používané události v sešitu.

NÁZEV UDÁLOSTI	CO SPOUŠTÍ UDÁLOST
aktivovat	Když je sešit aktivován
AfterSave	Když je sešit nainstalován jako doplněk
BeforeSave	Když je sešit uložen
BeforeClose	Když je sešit zavřený
BeforePrint	Když je sešit vytištěn
Deaktivovat	Když je sešit deaktivován
Nový list	Když je přidán nový list
Otevřeno	Když je sešit otevřený
SheetActivate	Když je aktivován jakýkoli list v sešitu
SheetBeforeDelete	Když je odstraněn jakýkoli list
SheetBeforeDoubleClick	Když dvakrát kliknete na libovolný list
ListPředPravoKlikněte	Když na jakýkoli list kliknete pravým tlačítkem
ListVýpočet	Když se vypočítá nebo přepočítá jakýkoli list
List Deaktivovat	Když je sešit deaktivován
SheetPivotTableUpdate	Když je sešit aktualizován
ListVýběrZměnit	Při změně sešitu
Aktivovat okno	Když je sešit aktivován
Okno Deaktivovat	Když je sešit deaktivován

Všimněte si, že toto není úplný seznam. Kompletní seznam najdete zde.

Nezapomeňte, že kód pro událost sešitu je uložen v okně kódu objektů ThisWorkbook.

Nyní se podívejme na některé užitečné události sešitu a podívejme se, jak je lze použít ve vaší každodenní práci.

Otevřená událost sešitu

Řekněme, že chcete uživateli ukázat přátelskou připomínku k vyplnění jeho pracovních výkazů, kdykoli otevře konkrétní sešit.

K tomu můžete použít následující kód:

Soukromý dílčí sešit_Otevřít () MsgBox „Nezapomeňte vyplnit časový rozvrh“ Koncový díl

Nyní, jakmile otevřete sešit s tímto kódem, zobrazí se vám okno se zprávou se zadanou zprávou.

Při práci s tímto kódem (nebo kódy událostí sešitu obecně) je třeba vědět několik věcí:

• Pokud sešit obsahuje makro a chcete jej uložit, musíte jej uložit ve formátu .XLSM. Jinak by byl kód makra ztracen.
• Ve výše uvedeném příkladu by byl kód události spuštěn pouze v případě, že jsou povolena makra. Může se zobrazit žlutý pruh s žádostí o povolení k povolení maker. Dokud to není povoleno, kód události není spuštěn.
• Kód události sešitu je umístěn v okně kódu objektu ThisWorkbook.

Tento kód můžete dále upřesnit a zobrazit zprávu pouze z pátku.

Následující kód by to udělal:

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

Všimněte si, že ve funkci den v týdnu je neděle přiřazena hodnota 1, pondělí je 2 a tak dále.

V pátek jsem tedy použil 6.

Akce Otevřít sešit může být užitečná v mnoha situacích, například:

• Když chcete osobě ukázat uvítací zprávu při otevření sešitu.
• Pokud chcete zobrazit připomenutí při otevření sešitu.
• Když chcete v sešitu vždy aktivovat jeden konkrétní list, když je otevřený.
• Pokud chcete otevřít související soubory spolu se sešitem.
• Pokud chcete zachytit datum a čas při každém otevření sešitu.

Sešit NewSheet Událost

Událost NewSheet se spustí při vložení nového listu do sešitu.

Řekněme, že chcete zadat hodnotu data a času do buňky A1 nově vloženého listu. K tomu můžete použít níže uvedený kód:

Soukromý dílčí sešit_Nový list (objekt ByVal Sh jako objekt) Při chybě Pokračovat Další Sh.Range ("A1") = Formát (Nyní "dd-mmm-rrrr hh: mm: ss") End Sub

Výše uvedený kód používá „On Error Resume Next“ k řešení případů, kdy někdo vloží list grafu a ne list. Protože list grafu nemá buňku A1, zobrazilo by se chyba, pokud není použito „Při chybě Pokračovat dále“.

Dalším příkladem může být situace, kdy chcete na nový list použít nějaké základní nastavení nebo formátování, jakmile je přidán. Pokud například chcete přidat nový list a chcete, aby automaticky získal sériové číslo (až 100), můžete použít níže uvedený kód.

Soukromý dílčí sešit_Nový list (objekt ByVal Sh jako objekt) Při chybě Pokračovat dále pomocí Sh.Range ("A1") .Value = "S. No." .Interior.Color = vbBlue .Font.Color = vbWhite End With For i = 1 to 100 Sh.Range ("A1"). Offset (i, 0) .Value = i Next i Sh.Range ("A1", Range ("A1"). Konec (xlDown)). Borders.LineStyle = xlSpojitý konec Sub

Výše uvedený kód také trochu formátuje. Dává buňce záhlaví modrou barvu a dělá písmo bílé. Rovněž použije ohraničení na všechny vyplněné buňky.

Výše uvedený kód je příkladem toho, jak vám krátký kód VBA může pomoci ukrást několik sekund při každém vložení nového listu (v případě, že je to něco, co musíte udělat pokaždé).

Sešit Událost BeforeSave

Před uložením sešitu se spustí událost Uložit. Všimněte si, že se nejprve spustí událost a potom se sešit uloží.

Při ukládání sešitu aplikace Excel mohou nastat dva možné scénáře:

1. Ukládáte jej poprvé a zobrazí se dialogové okno Uložit jako.
2. Už jste jej uložili dříve a jednoduše uloží a přepíše změny v již uložené verzi.

Nyní se podívejme na několik příkladů, kde můžete použít událost BeforeSave.

Předpokládejme, že máte nový sešit, který ukládáte poprvé, a chcete uživateli připomenout, aby jej uložil na disk K, pak můžete použít níže uvedený kód:

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

Ve výše uvedeném kódu, pokud soubor nebyl nikdy uložen, SaveAsUI je True a vyvolá dialogové okno Uložit jako. Výše uvedený kód by zobrazil zprávu před zobrazením dialogového okna Uložit jako.

Dalším příkladem může být aktualizace data a času, kdy je soubor uložen v konkrétní buňce.

Níže uvedený kód by vložil razítko data a času do buňky A1 listu1 při každém uložení souboru.

Soukromý dílčí sešit_BeforeSave (ByVal SaveAsUI jako Boolean, Zrušit jako Boolean) Pracovní listy ("Sheet1"). Rozsah ("A1") = Formát (Nyní, "dd-mmm-rrrr hh: mm: ss") End Sub

Všimněte si, že tento kód je spuštěn, jakmile uživatel uloží sešit. Pokud je sešit ukládán poprvé, zobrazí se dialogové okno Uložit jako. Ale kód je již spuštěn v době, kdy se zobrazí dialogové okno Uložit jako. V tomto okamžiku, pokud se rozhodnete sešit zrušit a neuložit, datum a čas by již byly zadány do buňky.

Sešit BeforeClose událost

Než se zavře událost, dojde těsně před zavřením sešitu.

Níže uvedený kód chrání všechny listy před uzavřením sešitu.

Soukromý dílčí sešit_BeforeClose (Zrušit jako Boolean) Dim sh jako pracovní list pro každý sh In ThisWorkbook.Worksheets sh.Protect Next sh End Sub

Nezapomeňte, že kód události se spustí, jakmile zavřete sešit.

Jedna důležitá věc, kterou je třeba o této události vědět, je, že ji nezajímá, zda je sešit skutečně zavřený nebo ne.

V případě, že sešit nebyl uložen a zobrazí se výzva s dotazem, zda sešit uložit nebo ne, a kliknete na Storno, sešit neuloží.Do té doby by však již byl spuštěn kód události.

Sešit BeforePrint událost

Když zadáte příkaz tisku (nebo příkaz Náhled), spustí se událost Před tiskem.

Níže uvedený kód by přepočítal všechny listy před vytištěním sešitu.

Soukromý dílčí sešit_BeforePrint (Zrušit jako booleovský) Pro každé ws v pracovních listech ws. Vypočítat další ws Konec Sub

Když uživatel tiskne sešit, událost by byla spuštěna bez ohledu na to, zda tiskne celý sešit nebo pouze jeho část.

Dalším příkladem níže je kód, který by přidal datum a čas do zápatí při tisku sešitu.

Soukromý dílčí sešit_BeforePrint (Zrušit jako 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

Události na úrovni pracovního listu (vysvětleno s příklady)

Události pracovního listu se konají na základě spouštěčů v listu.

Níže jsou uvedeny nejčastěji používané události v listu.

Název události	Co spouští událost
aktivovat	Když je list aktivován
Před odstraněním	Před odstraněním listu
Před dvojitým kliknutím	Před dvojitým kliknutím na list
BeforeRightClick	Před klepnutím pravým tlačítkem na list
Vypočítat	Než se list vypočítá nebo přepočítá
Změna	Když se změní buňky v listu
Deaktivovat	Když je list deaktivován
PivotTableUpdate	Když je kontingenční tabulka v listu aktualizována
SelectionChange	Když se změní výběr na listu

Všimněte si, že toto není úplný seznam. Kompletní seznam najdete zde.

Nezapomeňte, že kód události listu je uložen v okně kódu objektu listu (v tom, ve kterém chcete událost spustit). V sešitu může být více listů a váš kód bude spuštěn pouze tehdy, když se událost odehraje v listu, ve kterém je umístěn.

Nyní se podívejme na některé užitečné události pracovního listu a uvidíme, jak je lze použít ve vaší každodenní práci.

Pracovní list Aktivovat událost

Tato událost se spustí, když aktivujete list.

Níže uvedený kód nechrání list, jakmile je aktivován.

Private Sub Worksheet_Activate () ActiveSheet.Unprotect End Sub

Tuto událost můžete také použít k zajištění, že je vybrána konkrétní buňka nebo rozsah buněk (nebo pojmenovaný rozsah), jakmile aktivujete list. Níže uvedený kód by vybral buňku D1, jakmile list aktivujete.

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

Událost změny pracovního listu

Událost změny se spustí vždy, když provedete změnu v listu.

No… ne vždy.

Existují určité změny, které událost vyvolávají, a některé nikoli. Zde je seznam některých změn, které událost nespustí:

• Když změníte formátování buňky (velikost písma, barva, ohraničení atd.).
• Když slučujete buňky. To je překvapivé, protože někdy sloučení buněk také odstraní obsah ze všech buněk kromě levého horního.
• Když přidáte, odstraníte nebo upravíte komentář buňky.
• Když řadíte rozsah buněk.
• Když používáte vyhledávání cílů.

Následující změny by spustily událost (i když si možná myslíte, že by neměla):

• Událost by spustilo formátování kopírování a vkládání.
• Vymazání formátování by spustilo událost.
• Spuštění kontroly pravopisu by spustilo událost.

Níže je kód, který by zobrazil okno se zprávou s adresou buňky, která byla změněna.

Soukromý dílčí pracovní list_Změna (ByVal cíl jako rozsah) MsgBox „Právě jste se změnili“ & Target.Address End Sub

I když je to zbytečné makro, ukazuje vám, jak pomocí argumentu Cíl zjistit, které buňky byly změněny.

Nyní se podívejme na několik dalších užitečných příkladů.

Předpokládejme, že máte řadu buněk (řekněme A1: D10) a chcete zobrazit výzvu a zeptat se uživatele, zda skutečně chtěl změnit buňku v tomto rozsahu nebo ne, můžete použít níže uvedený kód.

Zobrazí výzvu se dvěma tlačítky - Ano a Ne. Pokud uživatel zvolí „Ano“, změna je provedena, jinak je vrácena.

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("You are creating a change in cells in A1: D10. Si si jistý, že to chceš?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

Ve výše uvedeném kódu zkontrolujeme, zda je cílová buňka v prvních 4 sloupcích a prvních 10 řádcích. Pokud tomu tak je, zobrazí se okno se zprávou. Také, pokud uživatel v okně se zprávou vybral Ne, je změna obrácena (příkazem Application.Undo).

Všimněte si, že jsem použil Application.EnableEvents = False před řádkem Application.Undo. A pak jsem to obrátil pomocí Application.EnableEvent = True v dalším řádku.

Je to nutné, protože když dojde k vrácení, spustí se také událost změny. Pokud nenastavím EnableEvent na False, bude i nadále spouštět událost change.

Změny v pojmenovaném rozsahu můžete také sledovat pomocí události change. Pokud například máte pojmenovaný rozsah s názvem „DataRange“ a chcete zobrazit výzvu v případě, že uživatel v tomto pojmenovaném rozsahu provede změnu, můžete použít níže uvedený kód:

Private Sub Worksheet_Change (ByVal Target As Range) Dim DRange As Range Set DRange = Range ("DataRange") If Not Intersect (Target, DRange) Is nothing Then MsgBox "Právě jste změnili rozsah dat" End If End Sub

Výše uvedený kód kontroluje, zda buňka/rozsah, ve kterém jste provedli změny, má buňky společné pro rozsah dat. Pokud ano, zobrazí se okno se zprávou.

Událost SešitChange sešitu

Událost změny výběru se spustí vždy, když dojde k změně výběru v listu.

Níže uvedený kód by přepočítal list, jakmile změníte výběr.

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

Dalším příkladem této události je, když chcete zvýraznit aktivní řádek a sloupec vybrané buňky.

Něco, jak je uvedeno níže:

Následující kód to dokáže:

Soukromý dílčí pracovní list_VýběrZměna (cíl podle rozsahu jako cíl) Buňky. Interiér.ColorIndex = xl Žádný s ActiveCell. End Sub

Kód nejprve odstraní barvu pozadí ze všech buněk a poté aplikuje barvu uvedenou v kódu na aktivní řádek a sloupec.

A to je problém tohoto kódu. Že odstraní barvu ze všech buněk.

Pokud chcete zvýraznit aktivní řádek/sloupec a přitom zachovat barvu v ostatních buňkách nedotčenou, použijte techniku ​​uvedenou v tomto kurzu.

Sešit DoubleClick událost

Toto je jedna z mých oblíbených událostí pracovního listu a uvidíte spoustu návodů, kde jsem toto použil (například tento nebo tento).

Tato událost se spustí, když dvakrát kliknete na buňku.

Ukážu vám, jak je to úžasné.

Pomocí níže uvedeného kódu můžete dvakrát kliknout na buňku a použije barvu pozadí, změní barvu písma a zvýrazní text v buňce tučně;

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

To může být užitečné, když procházíte seznam buněk a chcete zvýraznit několik vybraných. I když můžete poslední krok zopakovat pomocí klávesy F4, bylo by možné použít pouze jeden druh formátování. S touto událostí dvojitého kliknutí můžete všechny tři použít pouhým dvojitým kliknutím.

Všimněte si, že ve výše uvedeném kódu jsem vytvořil hodnotu Cancel = True.

To se provádí tak, že je deaktivována výchozí akce dvojitého kliknutí - což je dostat se do režimu úprav. Pokud použijete Cancel = True, Excel vás nedostane do režimu úprav, když na buňku dvakrát kliknete.

Zde je další příklad.

Pokud máte v aplikaci Excel seznam úkolů, můžete pomocí události dvojitého kliknutí použít přeškrtnutý formát a úkol označit jako dokončený.

Něco, jak je uvedeno níže:

Zde je kód, který to provede:

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šimněte si, že v tomto kódu jsem provedl dvojité kliknutí jako přepínací událost. Když dvakrát kliknete na buňku, zkontroluje, zda již byl použit přeškrtnutý formát. Pokud ano, dvojitým kliknutím odstraníte přeškrtnutý formát, a pokud nebyl, použije se přeškrtnutý formát.

Událost OnTime aplikace Excel VBA

Události, které jsme dosud viděli v tomto článku, byly spojeny s jedním z objektů aplikace Excel, ať už sešit, pracovní list, list grafu nebo UserForms atd.

Událost OnTime se liší od ostatních událostí, protože může být uložena v běžném modulu VBA (zatímco ostatní měly být umístěny v okně kódu objektů, jako je ThisWorkbook nebo Worksheets nebo UserForms).

V rámci běžného modulu VBA se používá jako metoda objektu aplikace.

Důvod, proč je to považováno za událost, je ten, že může být spuštěna na základě zadaného času. Pokud například chci, aby se list každých 5 minut přepočítával, mohu k tomu použít událost OnTime.

Nebo pokud chci ukázat zprávu/připomenutí v konkrétní denní dobu, mohu použít událost OnTime.

Níže je kód, který každý den ve 14 hodin zobrazí zprávu.

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

Nezapomeňte, že tento kód musíte umístit do běžného modulu VBA,

Také, zatímco událost OnTime by byla spuštěna v zadaný čas, musíte makro spustit kdykoli ručně. Jakmile makro spustíte, bude čekat, dokud nebudou 2:00, a poté zavoláte makro „ShowMessage“.

Makro ShowMessage by pak zobrazilo zprávu.

Událost OnTime přebírá čtyři argumenty:

Application.OnTime (EarliestTime, Postup, LatestTime, Plán)

• EarliestTime: Čas, kdy chcete spustit proceduru.
• Postup: Název procedury, která by měla být spuštěna.
• LatestTime (volitelně): V případě, že je spuštěn jiný kód a váš zadaný kód nelze spustit v zadanou dobu, můžete zadat LatestTime, na který má čekat. Může to být například EarliestTime + 45 (což znamená, že bude čekat 45 sekund, než bude dokončen další postup). Pokud ani po 45 sekundách nelze proceduru spustit, bude opuštěna. Pokud to neurčíte, Excel počká, až bude možné kód spustit, a poté jej spustí.
• Plán (volitelně): Pokud je nastaveno na True, naplánuje nový časový postup. Pokud je hodnota False, ruší dříve nastavený postup. Ve výchozím nastavení je to True.

Ve výše uvedeném příkladu jsme použili pouze první dva argumenty.

Podívejme se na další příklad.

Níže uvedený kód by aktualizoval list každých 5 minut.

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

Výše uvedený kód by aktualizoval list každých 5 minut.

Pomocí funkce Now určuje aktuální čas a poté k aktuálnímu času přidá 5 minut.

Událost OnTime bude pokračovat, dokud ji nezastavíte. Pokud zavřete sešit a aplikace Excel je stále spuštěna (jiné sešity jsou otevřené), sešit, ve kterém je spuštěna událost OnTime, by se sám znovu otevřel.

To lze lépe vyřešit konkrétním zastavením události OnTime.

Ve výše uvedeném kódu mám kód StopRefresh, ale musíte jej spustit, abyste zastavili událost OnTime. Můžete to provést ručně, přiřadit k tlačítku a provést to stisknutím tlačítka nebo zavolat z události Zavřít sešit.

Soukromý dílčí sešit_BeforeClose (Zrušit jako booleovský) Volání StopRefresh Konec Sub

Výše uvedený kód události „BeforeClose“ jde do okna kódu ThisWorkbook.

Událost OnKey aplikace Excel VBA

Když pracujete s aplikací Excel, neustále sleduje používané klávesy. To nám umožňuje použít klávesové zkratky jako spouštěč události.

Pomocí události OnKey můžete zadat stisknutí klávesy (nebo kombinaci kláves) a kód, který by měl být spuštěn při použití tohoto stisknutí klávesy. Po stisknutí těchto úhozů se pro něj spustí kód.

Stejně jako událost OnTime musíte mít způsob, jak zrušit událost OnKey. Také když nastavíte událost OnKey pro konkrétní stisk klávesy, bude k dispozici ve všech otevřených sešitech.

Než vám ukážu příklad použití události OnKey, dovolte mi nejprve sdílet klíčové kódy, které máte ve VBA k dispozici.

KLÍČ	KÓD
Backspace	{BACKSPACE} nebo {BS}
Přestávka	{PŘESTÁVKA}
Caps Lock	{CAPSLOCK}
Vymazat	{DELETE} nebo {DEL}
Šipka dolů	{DOLŮ}
Konec	{KONEC}
Vstupte	~
Enter (na nuerické klávesnici)	{ENTER}
Uniknout	{ESCAPE} nebo {ESC}
Domov	{DOMOV}
Ins	{VLOŽIT}
Šipka vlevo	{VLEVO, ODJET}
NumLock	{NUMLOCK}
PageDown	{PGDN}
PageUp	{PGUP}
Šipka doprava	{ŽE JO}
Scroll Lock	{SCROLLOCK}
Tab	{TAB}
Šipka nahoru	{NAHORU}
F1 až F15	{F1} až {F15}

Když potřebujete použít jakoukoli událost onkey, musíte pro ni použít kód.

Výše uvedená tabulka obsahuje kódy pro jednotlivé stisknutí kláves.

Můžete je také zkombinovat s následujícími kódy:

• Posun: + (Znaménko plus)
• Řízení: ^ (Stříška)
• Alt: % (Procento)

Například pro Alt F4 musíte použít kód: „%{F4}” - kde % je pro klávesu ALT a {F4} je pro klávesu F4.

Nyní se podívejme na příklad (pamatujte, že kód pro události OnKey jsou umístěny v běžném modulu VBA).

Když stisknete klávesu PageUp nebo PageDown, přeskočí 29 řádků nad/pod aktivní buňku (alespoň to dělá na mém notebooku).

Pokud chcete, aby přeskočilo pouze 5 řádků najednou, můžete použít níže uvedený 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

Když spustíte první část kódu, spustí se události OnKey. Jakmile je toto provedeno, pomocí PageUp a klávesy PageDown by kurzor přeskočil pouze 5 řádků najednou.

Všimli jsme si, že jsme použili „On Error Resume Next“, abychom zajistili, že chyby budou ignorovány. K těmto chybám může dojít, když stisknete klávesu PageUp, i když jste v horní části listu. Vzhledem k tomu, že již nelze přeskočit žádné řádky, kód by zobrazil chybu. Protože jsme však použili „On Error Resume Next“, bude ignorováno.

Abyste se ujistili, že jsou tyto události OnKey k dispozici, musíte spustit první část kódu. V případě, že chcete, aby to bylo k dispozici hned po otevření sešitu, můžete to umístit do okna kódu ThisWorkbook.

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

Níže uvedený kód vrátí klíčům jejich normální funkce.

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

Pokud v metodě OnKey nezadáte druhý argument, vrátí stisknutí kláves jeho normální funkce.

V případě, že chcete zrušit funkčnost stisknutí klávesy, aby aplikace Excel při použití této klávesové zkratky nic neudělala, musíte jako druhý argument použít prázdný řetězec.

V níže uvedeném kódu by Excel nedělal nic, kdybychom použili klávesy PageUp nebo PageDown.

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

Zakázání událostí ve VBA

Někdy může být nutné zakázat události, aby váš kód fungoval správně.

Předpokládejme například, že mám rozsah (A1: D10) a chci zobrazit zprávu při každé změně buňky v tomto rozsahu. Ukážu tedy okno se zprávou a ptám se uživatele, zda si je jistý, že chce změnu provést. Pokud je odpověď Ano, provede se změna a pokud odpoví Ne, VBA by to vrátila.

Můžete použít níže uvedený kód:

Soukromý dílčí pracovní list_Změna (ByVal Cíl jako rozsah) Pokud Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("Provádíte změnu v buňkách v A1: D10. Opravdu to chcete?", vbYesNo) End If If Ans = vbNo Then Application.Undo End If End Sub

Problém s tímto kódem je ten, že když uživatel v okně se zprávou zvolí Ne, akce se obrátí (jak jsem použil Application.Undo).

Když dojde k vrácení zpět a hodnota se změní zpět na původní, znovu se spustí událost změny VBA a uživateli se znovu zobrazí stejné okno se zprávou.

To znamená, že můžete i nadále klikat na NE v okně se zprávou a bude se nadále zobrazovat. K tomu dochází, protože jste v tomto případě uvízli v nekonečné smyčce.

Abyste se takovým případům vyhnuli, je třeba zakázat události, aby se nespustila událost změny (nebo jakákoli jiná událost).

Následující kód by v tomto případě fungoval dobře:

Private Sub Worksheet_Change (ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox ("You are creating a change in cells in A1: D10. Si si jistý, že to chceš?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

Ve výše uvedeném kódu, přímo nad řádkem Application.Undo, jsme použili - Application.EnableEvents = False.

Nastavení EnableEvents na False by nevyvolalo žádnou událost (v aktuálním nebo žádném otevřeném sešitu).

Jakmile dokončíme operaci vrácení zpět, můžeme vlastnost EnableEvents přepnout zpět na True.

Mějte na paměti, že deaktivace událostí ovlivní všechny sešity, které jsou aktuálně otevřeny (nebo otevřeny, když je EnableEvents nastaveno na False). Pokud například jako součást kódu otevřete nový sešit, událost Otevřít sešit by nefungovala.

Dopad událostí Zpět Stack

Nejprve vám řeknu, co je Undo Stack.

Když pracujete v Excelu, neustále sleduje vaše akce. Když uděláte chybu, můžete se kdykoli pomocí Ctrl + Z vrátit k předchozímu kroku (tj. Vrátit zpět svou aktuální akci).

Pokud dvakrát stisknete Ctrl + Z, vrátíte se o dva kroky zpět. Tyto kroky, které jste provedli, jsou uloženy jako součást zásobníku Zpět.

Jakákoli událost, která změní list, zničí tento zásobník zpět.To znamená, že pokud jsem před spuštěním události udělal 5 věcí, nebudu se moci pomocí Control + Z vrátit k předchozím krokům. Spuštění události mi tento stack zničilo.

V níže uvedeném kódu používám VBA k zadání časového razítka do buňky A1, kdykoli dojde ke změně v listu.

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

Protože provádím změnu v listu, zničí to zásobník zpět.

Všimněte si také, že to není omezeno pouze na události.

Pokud máte kód uložený v běžném modulu VBA a provedete změnu v listu, zničí to také zásobník zpět v aplikaci Excel.

Například níže uvedený kód jednoduše zadejte text „Hello“ do buňky A1, ale i spuštěním by to zničilo zásobník zpět.

Podtyp Dobrý den () Rozsah („A1“). Hodnota = „Dobrý den“ Koncový díl

Také by se vám mohly líbit následující návody Excel VBA:

• Práce s buňkami a rozsahy v aplikaci Excel VBA.
• Práce s listy v Excelu VBA.
• Práce se sešity v aplikaci Excel VBA.
• Excel VBA Loops - Ultimate Guide.
• Použití příkazu IF Then Else Statement v aplikaci Excel VBA.
• Pro další smyčku v aplikaci Excel.
• Vytváření funkcí definovaných uživatelem v aplikaci Excel VBA.
• Jak vytvářet a používat doplňky v aplikaci Excel.
• Vytvářejte a znovu používejte makra uložením do osobního sešitu maker.