Wydarzenia Excel VBA - łatwy (i kompletny) przewodnik

Gdy tworzysz lub rejestrujesz makro w programie Excel, musisz uruchomić makro, aby wykonać kroki w kodzie.

Kilka sposobów uruchamiania makra obejmuje użycie okna dialogowego makra, przypisanie makra do przycisku, użycie skrótu itp.

Oprócz tych inicjowanych przez użytkownika makr można również użyć zdarzeń VBA do uruchomienia makra.

Zdarzenia Excel VBA - wprowadzenie

Pozwolę sobie najpierw wyjaśnić, czym jest wydarzenie w VBA.

Zdarzenie to akcja, która może wywołać wykonanie określonego makra.

Na przykład otwarcie nowego skoroszytu jest wydarzeniem. Kiedy wstawiasz nowy arkusz, jest to wydarzenie. Gdy klikniesz dwukrotnie komórkę, jest to wydarzenie.

W VBA jest wiele takich wydarzeń i możesz tworzyć kody dla tych wydarzeń. Oznacza to, że natychmiast po wystąpieniu zdarzenia i jeśli określiłeś kod dla tego zdarzenia, kod ten zostanie natychmiast wykonany.

Excel robi to automatycznie, gdy tylko zauważy, że zdarzenie miało miejsce. Wystarczy więc napisać kod i umieścić go we właściwym podprogramie zdarzenia (jest to omówione w dalszej części tego artykułu).

Na przykład, jeśli wstawiasz nowy arkusz i chcesz, aby miał prefiks roku, możesz napisać dla niego kod.

Teraz, gdy ktoś wstawi nowy arkusz, ten kod zostanie automatycznie wykonany i doda prefiks roku do nazwy arkusza roboczego.

Innym przykładem może być chęć zmiany koloru komórki, gdy ktoś ją dwukrotnie kliknie. Możesz do tego użyć zdarzenia podwójnego kliknięcia.

Podobnie możesz tworzyć kody VBA dla wielu takich wydarzeń (o czym zobaczymy w dalszej części tego artykułu).

Poniżej znajduje się krótki obraz przedstawiający zdarzenie dwukrotnego kliknięcia w akcji. Jak tylko kliknę dwukrotnie komórkę A1. Excel natychmiast otwiera okno komunikatu, które pokazuje adres komórki.

Dwukrotne kliknięcie jest zdarzeniem, a wyświetlenie okna komunikatu jest tym, co określiłem w kodzie za każdym razem, gdy ma miejsce zdarzenie dwukrotnego kliknięcia.

Chociaż powyższy przykład jest bezużytecznym wydarzeniem, mam nadzieję, że pomoże ci zrozumieć, czym naprawdę są wydarzenia.

Różne typy zdarzeń Excel VBA

W Excelu istnieją różne obiekty - takie jak sam Excel (do którego często nazywamy aplikację), skoroszyty, arkusze, wykresy itp.

Z każdym z tych obiektów mogą być powiązane różne zdarzenia. Na przykład:

• Jeśli utworzysz nowy skoroszyt, jest to zdarzenie na poziomie aplikacji.
• Jeśli dodasz nowy arkusz, będzie to zdarzenie na poziomie skoroszytu.
• Jeśli zmienisz wartość w komórce w arkuszu, będzie to zdarzenie na poziomie arkusza.

Poniżej znajdują się różne typy zdarzeń, które istnieją w programie Excel:

1. Zdarzenia na poziomie arkusza roboczego: Są to rodzaje zdarzeń, które zostaną wyzwolone na podstawie działań podjętych w arkuszu. Przykłady takich zdarzeń obejmują zmianę komórki w arkuszu, zmianę zaznaczenia, dwukrotne kliknięcie komórki, kliknięcie komórki prawym przyciskiem myszy itp.
2. Zdarzenia na poziomie skoroszytu: Te zdarzenia byłyby wyzwalane na podstawie akcji na poziomie skoroszytu. Przykłady takich zdarzeń obejmują dodanie nowego arkusza, zapisanie skoroszytu, otwarcie skoroszytu, wydrukowanie części lub całego skoroszytu itp.
3. Zdarzenia na poziomie aplikacji: Są to zdarzenia, które występują w aplikacji Excel. Przykładem może być zamknięcie dowolnego z otwartych skoroszytów lub otwarcie nowego skoroszytu.
4. Zdarzenia na poziomie UserForm: Te zdarzenia byłyby wyzwalane na podstawie działań w „UserForm”. Przykłady obejmują inicjowanie UserForm lub kliknięcie przycisku w UserForm.
5. Wydarzenia na wykresie: Są to zdarzenia związane z arkuszem wykresu. Arkusz wykresu różni się od arkusza roboczego (w którym większość z nas jest przyzwyczajona do pracy w programie Excel). Celem arkuszy wykresów jest przechowywanie wykresu. Przykładami takich zdarzeń mogą być zmiana serii wykresu lub zmiana rozmiaru wykresu.
6. Wydarzenia OnTime i OnKey: To są dwa wydarzenia, które nie pasują do żadnej z powyższych kategorii. Więc wymieniłem je osobno. Zdarzenie „OnTime” umożliwia wykonanie kodu o określonej godzinie lub po upływie określonego czasu. Zdarzenie „OnKey” umożliwia wykonanie kodu po użyciu określonego naciśnięcia klawisza (lub kombinacji naciśnięć klawiszy).

Gdzie umieścić kod związany z wydarzeniem?

W powyższej sekcji omówiłem różne rodzaje wydarzeń.

W zależności od rodzaju zdarzenia należy umieścić kod w odpowiednim obiekcie.

Na przykład, jeśli jest to zdarzenie związane z arkuszem, powinno pojawić się w oknie kodu obiektu arkusza. Jeśli jest związany ze skoroszytem, ​​powinien pojawić się w oknie kodu dla obiektu skoroszytu.

W VBA różne obiekty - takie jak Arkusze, Skoroszyty, Arkusze Wykresów, Formularze Użytkownika itp. mają własne okna kodu. Musisz umieścić kod zdarzenia w oknie kodu odpowiedniego obiektu. Na przykład - jeśli jest to zdarzenie na poziomie skoroszytu, musisz mieć kod zdarzenia w oknie kodu skoroszytu.

Poniższe sekcje opisują miejsca, w których można umieścić kod wydarzenia:

W oknie kodu arkusza roboczego

Kiedy otworzysz Edytor VB (używając skrótu klawiaturowego ALT + F11), zauważysz obiekt arkuszy w Eksploratorze projektów. Dla każdego arkusza w skoroszycie zobaczysz jeden obiekt.

Po dwukrotnym kliknięciu obiektu arkusza, w którym chcesz umieścić kod, otworzy się okno kodu dla tego arkusza.

Chociaż możesz zacząć pisać kod od zera, znacznie lepiej jest wybrać zdarzenie z listy opcji i pozwolić VBA automatycznie wstawić odpowiedni kod dla wybranego zdarzenia.

Aby to zrobić, musisz najpierw wybrać arkusz roboczy z listy rozwijanej w lewym górnym rogu okna kodu.

Po wybraniu arkusza roboczego z rozwijanego menu otrzymasz listę wszystkich zdarzeń związanych z arkuszem. Możesz wybrać ten, którego chcesz użyć, z listy rozwijanej w prawym górnym rogu okna kodu.

Jak tylko wybierzesz wydarzenie, automatycznie wprowadzi pierwszą i ostatnią linię kodu dla wybranego wydarzenia. Teraz możesz dodać swój kod między dwiema liniami.

Uwaga: Gdy tylko wybierzesz Arkusz z listy rozwijanej, zauważysz, że w oknie kodu pojawiają się dwa wiersze kodu. Po wybraniu zdarzenia, dla którego chcesz otrzymać kod, możesz usunąć wiersze, które pojawiły się domyślnie.

Zauważ, że każdy arkusz ma własne okno kodu. Gdy umieścisz kod dla Arkusza1, zadziała on tylko wtedy, gdy zdarzenie nastąpi w Arkuszu1.

W tym oknie kodu skoroszytu

Podobnie jak w przypadku arkuszy roboczych, jeśli masz kod zdarzenia na poziomie skoroszytu, możesz umieścić go w oknie kodu ThisWorkbook.

Po dwukrotnym kliknięciu ThisWorkbook otworzy się dla niego okno kodu.

Musisz wybrać Skoroszyt z listy rozwijanej w lewym górnym rogu okna kodu.

Po wybraniu skoroszytu z rozwijanego menu otrzymasz listę wszystkich zdarzeń związanych ze skoroszytem. Możesz wybrać ten, którego chcesz użyć, z listy rozwijanej w prawym górnym rogu okna kodu.

Jak tylko wybierzesz wydarzenie, automatycznie wprowadzi pierwszą i ostatnią linię kodu dla wybranego wydarzenia. Teraz możesz dodać swój kod między dwiema liniami.

Uwaga: Jak tylko wybierzesz Skoroszyt z menu rozwijanego, zauważysz, że w oknie kodu pojawiają się dwa wiersze kodu. Po wybraniu zdarzenia, dla którego chcesz otrzymać kod, możesz usunąć wiersze, które pojawiły się domyślnie.

W oknie kodu formularza użytkownika

Podczas tworzenia formularzy UserForm w programie Excel możesz również używać zdarzeń UserForm do wykonywania kodów na podstawie określonych akcji. Na przykład możesz określić kod, który jest wykonywany po kliknięciu przycisku.

Podczas gdy obiekty Sheet i ThisWorkbook są już dostępne po otwarciu edytora VB, UserForm to coś, co musisz najpierw utworzyć.

Aby utworzyć UserForm, kliknij prawym przyciskiem myszy dowolny obiekt, przejdź do Insert i kliknij UserForm.

Spowoduje to wstawienie obiektu UserForm do skoroszytu.

Po dwukrotnym kliknięciu UserForm (lub dowolnego obiektu dodanego do UserForm) otworzy się okno kodu dla UserForm.

Teraz, podobnie jak arkusze robocze lub ThisWorkbook, możesz wybrać zdarzenie i wstawić pierwszy i ostatni wiersz dla tego zdarzenia. A potem możesz dodać kod w środku.

W oknie kodu wykresu

W programie Excel możesz także wstawiać arkusze wykresów (które różnią się od arkuszy roboczych). Arkusz wykresu ma zawierać tylko wykresy.

Po wstawieniu arkusza wykresu będzie można zobaczyć obiekt arkusza wykresu w edytorze VB.

Możesz dodać kod zdarzenia do okna kodu arkusza wykresu, tak jak zrobiliśmy to w arkuszu.

Kliknij dwukrotnie obiekt arkusza wykresu w Eksploratorze projektów. Spowoduje to otwarcie okna kodu dla arkusza wykresu.

Teraz musisz wybrać Wykres z listy rozwijanej w lewym górnym rogu okna kodu.

Po wybraniu z rozwijanego menu Wykres, otrzymasz listę wszystkich zdarzeń związanych z arkuszem Wykres. Możesz wybrać ten, którego chcesz użyć, z listy rozwijanej w prawym górnym rogu okna kodu.

Uwaga: Jak tylko wybierzesz Wykres z rozwijanego menu, zauważysz dwie linie kodu pojawiające się w oknie kodu. Po wybraniu zdarzenia, dla którego chcesz otrzymać kod, możesz usunąć wiersze, które pojawiły się domyślnie.

W module klasy

Moduły klas muszą być wstawiane tak jak UserForms.

Moduł klasy może zawierać kod związany z aplikacją - czyli sam Excel i osadzone wykresy.

W nadchodzących tygodniach omówię moduł zajęć jako osobny samouczek.

Zwróć uwagę, że poza zdarzeniami OnTime i OnKey żadne z powyższych zdarzeń nie może być przechowywane w zwykłym module VBA.

Zrozumienie sekwencji zdarzeń

Kiedy uruchamiasz zdarzenie, nie dzieje się to w odosobnieniu. Może również prowadzić do sekwencji wielu wyzwalaczy.

Na przykład po wstawieniu nowego arkusza dzieją się następujące rzeczy:

1. Dodano nowy arkusz roboczy
2. Poprzedni arkusz roboczy zostanie dezaktywowany
3. Nowy arkusz roboczy zostanie aktywowany

Chociaż w większości przypadków możesz nie martwić się o sekwencję, jeśli tworzysz złożone kody, które opierają się na zdarzeniach, lepiej jest znać sekwencję, aby uniknąć nieoczekiwanych wyników.

Zrozumienie roli argumentów w zdarzeniach VBA

Zanim przejdziemy do przykładów wydarzeń i niesamowitych rzeczy, które możesz z nimi zrobić, jest jedna ważna koncepcja, którą muszę omówić.

W wydarzeniach VBA byłyby dwa rodzaje kodów:

• Bez żadnych argumentów
• Z argumentami

A w tej sekcji chcę szybko omówić rolę argumentów.

Poniżej znajduje się kod, który nie zawiera żadnych argumentów (nawiasy są puste):

Private Sub Workbook_Open() MsgBox "Pamiętaj o wypełnieniu grafiku" End Sub

Za pomocą powyższego kodu po otwarciu skoroszytu wyświetla się po prostu okno komunikatu z komunikatem „Pamiętaj, aby wypełnić grafik”.

Teraz spójrzmy na kod, który ma argument.

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

Powyższy kod używa argumentu Sh, który jest zdefiniowany jako typ obiektu. Argument Sh może być arkuszem lub arkuszem wykresu, ponieważ powyższe zdarzenie jest wyzwalane po dodaniu nowego arkusza.

Przypisując nowy arkusz, który jest dodawany do skoroszytu, do zmiennej obiektu Sh, VBA umożliwił nam użycie go w kodzie. Aby odwołać się do nowej nazwy arkusza, mogę użyć Sh.Name.

Pojęcie argumentów będzie przydatne, gdy przejdziesz przez przykłady zdarzeń VBA w następnych sekcjach.

Zdarzenia na poziomie skoroszytu (wyjaśnione z przykładami)

Poniżej przedstawiono najczęściej używane zdarzenia w skoroszycie.

NAZWA WYDARZENIA	CO POWODUJE ZDARZENIE
Aktywuj	Gdy skoroszyt jest aktywowany
Po zapisaniu	Gdy skoroszyt jest zainstalowany jako dodatek
Przed zapisaniem	Po zapisaniu skoroszytu
PrzedZamknij	Kiedy skoroszyt jest zamknięty
Przed wydrukiem	Kiedy drukowany jest skoroszyt
Dezaktywować	Gdy skoroszyt jest dezaktywowany
Nowy arkusz	Po dodaniu nowego arkusza
otwarty	Gdy skoroszyt jest otwarty
Aktywacja arkusza	Gdy dowolny arkusz w skoroszycie jest aktywny
Arkusz przed usunięciem	Po usunięciu dowolnego arkusza
Arkusz przed podwójnym kliknięciem	Po dwukrotnym kliknięciu dowolnego arkusza
ArkuszPrzed kliknięciem prawym przyciskiem	Gdy dowolny arkusz zostanie kliknięty prawym przyciskiem myszy
Obliczanie arkusza	Gdy dowolny arkusz jest obliczany lub przeliczany ponownie
Arkusz Dezaktywuj	Gdy skoroszyt jest dezaktywowany
Aktualizacja tabeli przestawnej arkusza	Kiedy skoroszyt jest aktualizowany
Zmiana wyboru arkusza	Po zmianie skoroszytu
OknoAktywuj	Gdy skoroszyt jest aktywowany
OknoDezaktywuj	Gdy skoroszyt jest dezaktywowany

Zauważ, że to nie jest pełna lista. Pełną listę znajdziesz tutaj.

Pamiętaj, że kod zdarzenia Workbook jest przechowywany w oknie kodu obiektów ThisWorkbook.

Przyjrzyjmy się teraz kilku przydatnym wydarzeniom ze skoroszytu i zobaczmy, jak można je wykorzystać w codziennej pracy.

Wydarzenie otwarte w skoroszycie

Załóżmy, że chcesz pokazać użytkownikowi przyjazne przypomnienie o wypełnieniu grafiku za każdym razem, gdy otworzy określony skoroszyt.

W tym celu możesz użyć poniższego kodu:

Private Sub Workbook_Open() MsgBox "Pamiętaj o wypełnieniu grafiku" End Sub

Teraz, gdy tylko otworzysz skoroszyt zawierający ten kod, wyświetli się okno komunikatu z określoną wiadomością.

Podczas pracy z tym kodem (lub ogólnie z kodami zdarzeń ze skoroszytu) należy wiedzieć kilka rzeczy:

• Jeśli skoroszyt zawiera makro i chcesz je zapisać, musisz je zapisać w formacie .XLSM. W przeciwnym razie kod makra zostałby utracony.
• W powyższym przykładzie kod zdarzenia zostałby wykonany tylko wtedy, gdy włączone są makra. Możesz zobaczyć żółty pasek z prośbą o pozwolenie na włączenie makr. Dopóki to nie jest włączone, kod zdarzenia nie jest wykonywany.
• Kod zdarzenia Workbook jest umieszczany w oknie kodu obiektu ThisWorkbook.

Możesz dodatkowo doprecyzować ten kod i pokazać wiadomość tylko z piątku.

Poniższy kod zrobi to:

Private Sub Workbook_Open() wkday = Dzień tygodnia (Data) Jeśli wkday = 6 Następnie MsgBox "Pamiętaj o wypełnieniu grafiku" End Sub

Zauważ, że w funkcji Weekday niedziela ma przypisaną wartość 1, poniedziałek to 2 i tak dalej.

Stąd na piątek użyłem 6.

Zdarzenie Workbook Open może być przydatne w wielu sytuacjach, takich jak:

• Gdy chcesz wyświetlić wiadomość powitalną osobie po otwarciu skoroszytu.
• Gdy chcesz wyświetlić przypomnienie po otwarciu skoroszytu.
• Gdy chcesz zawsze aktywować jeden określony arkusz w skoroszycie po jego otwarciu.
• Gdy chcesz otworzyć powiązane pliki wraz ze skoroszytem.
• Gdy chcesz przechwycić sygnaturę daty i godziny przy każdym otwarciu skoroszytu.

Zdarzenie nowego arkusza ze skoroszytu

Zdarzenie NewSheet jest wyzwalane po wstawieniu nowego arkusza do skoroszytu.

Załóżmy, że chcesz wprowadzić wartość daty i godziny w komórce A1 nowo wstawionego arkusza. W tym celu możesz użyć poniższego kodu:

Private Sub Workbook_NewSheet(ByVal Sh As Object) W przypadku błędu Wznów Dalej Sh.Range("A1") = Format(Teraz, "dd-mmm-rrrr hh:mm:ss") End Sub

Powyższy kod używa „On Error Resume Next” do obsługi przypadków, w których ktoś wstawia arkusz wykresu, a nie arkusz. Ponieważ arkusz wykresu nie ma komórki A1, wyświetli błąd, jeśli nie zostanie użyty „Po błędzie wznowienia dalej”.

Innym przykładem może być sytuacja, w której chcesz zastosować podstawowe ustawienia lub formatowanie do nowego arkusza zaraz po jego dodaniu. Na przykład, jeśli chcesz dodać nowy arkusz i chcesz, aby automatycznie otrzymał numer seryjny (do 100), możesz użyć poniższego kodu.

Private Sub Workbook_NewSheet(ByVal Sh As Object) W przypadku błędu Wznów dalej z 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").End(xlDown)).Borders.LineStyle = xlContinuous End Sub

Powyższy kod również trochę formatuje. Nadaje komórce nagłówka kolor niebieski, a czcionkę białą. Stosuje również obramowanie do wszystkich wypełnionych komórek.

Powyższy kod jest przykładem tego, jak krótki kod VBA może pomóc ci skraść kilka sekund za każdym razem, gdy wstawiasz nowy arkusz (na wypadek, gdyby to było coś, co musisz robić za każdym razem).

Skoroszyt przed zapisaniem zdarzenia

Zdarzenie Przed zapisaniem jest wyzwalane podczas zapisywania skoroszytu. Zwróć uwagę, że zdarzenie jest wyzwalane jako pierwsze, a następnie skoroszyt jest zapisywany.

Podczas zapisywania skoroszytu programu Excel mogą istnieć dwa możliwe scenariusze:

1. Zapisujesz go po raz pierwszy i wyświetli okno dialogowe Zapisz jako.
2. Zapisałeś go już wcześniej, a po prostu zapisze i nadpisze zmiany w już zapisanej wersji.

Przyjrzyjmy się teraz kilku przykładom, w których możesz użyć zdarzenia BeforeSave.

Załóżmy, że masz nowy skoroszyt, który zapisujesz po raz pierwszy, i chcesz przypomnieć użytkownikowi, aby zapisał go na dysku K, a następnie możesz użyć poniższego kodu:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Jeśli SaveAsUI to MsgBox "Zapisz ten plik na dysku K" End Sub

W powyższym kodzie, jeśli plik nigdy nie został zapisany, SaveAsUI ma wartość True i wyświetla okno dialogowe Zapisz jako. Powyższy kod wyświetli komunikat przed pojawieniem się okna dialogowego Zapisz jako.

Innym przykładem może być aktualizacja daty i godziny zapisania pliku w określonej komórce.

Poniższy kod wstawi znacznik daty i godziny w komórce A1 arkusza Sheet1 za każdym razem, gdy plik zostanie zapisany.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Worksheets("Sheet1").Range("A1") = Format(Now, "dd-mmm-rrrr hh:mm:ss") End Sub

Zauważ, że ten kod jest wykonywany, gdy tylko użytkownik zapisze skoroszyt. Jeśli skoroszyt jest zapisywany po raz pierwszy, zostanie wyświetlone okno dialogowe Zapisz jako. Ale kod jest już wykonywany przed wyświetleniem okna dialogowego Zapisz jako. W tym momencie, jeśli zdecydujesz się anulować i nie zapisywać skoroszytu, data i godzina zostaną już wprowadzone w komórce.

Skoroszyt przed zamknięciem wydarzenia

Zdarzenie Przed zamknięciem ma miejsce tuż przed zamknięciem skoroszytu.

Poniższy kod chroni wszystkie arkusze przed zamknięciem skoroszytu.

Private Sub Workbook_BeforeClose(Anuluj jako Boolean) Dim sh As Worksheet dla każdego sh In ThisWorkbook.Worksheets sh.Protect Next sh End Sub

Pamiętaj, że kod zdarzenia jest wyzwalany zaraz po zamknięciu skoroszytu.

Jedną ważną rzeczą, którą należy wiedzieć o tym wydarzeniu, jest to, że nie obchodzi go, czy skoroszyt jest rzeczywiście zamknięty, czy nie.

Jeśli skoroszyt nie został zapisany i zostanie wyświetlony monit z pytaniem, czy zapisać skoroszyt, czy nie, i klikniesz Anuluj, skoroszyt nie zostanie zapisany.Jednak do tego czasu kod zdarzenia zostałby już wykonany.

Skoroszyt przed zdarzeniem drukowania

Po wydaniu polecenia drukowania (lub polecenia Podgląd wydruku) wyzwalane jest zdarzenie Przed drukowaniem.

Poniższy kod ponownie obliczy wszystkie arkusze przed wydrukowaniem skoroszytu.

Private Sub Workbook_BeforePrint(Anuluj jako Boolean) Dla każdego ws w arkuszach roboczych ws.Calculate Next ws End Sub

Gdy użytkownik drukuje skoroszyt, zdarzenie zostanie wywołane niezależnie od tego, czy drukuje cały skoroszyt, czy tylko jego część.

Innym przykładem poniżej jest kod, który dodaje datę i godzinę do stopki podczas drukowania skoroszytu.

Private Sub Workbook_BeforePrint(Anuluj jako Boolean) Dim ws As Worksheet For Every ws In ThisWorkbook.Worksheets ws.PageSetup.LeftFooter = "Drukowane na - " & Format(Now, "dd-mmm-rrrr hh:mm") Next ws End Sub

Zdarzenia na poziomie arkusza roboczego (wyjaśnione z przykładami)

Zdarzenia arkusza roboczego odbywają się na podstawie wyzwalaczy w arkuszu.

Poniżej przedstawiono najczęściej używane zdarzenia w arkuszu.

Nazwa wydarzenia	Co powoduje zdarzenie
Aktywuj	Gdy arkusz jest aktywny
Przed usunięciem	Zanim arkusz zostanie usunięty
Przed podwójnym kliknięciem	Zanim arkusz roboczy zostanie dwukrotnie kliknięty
Przed kliknięciem prawym przyciskiem	Zanim arkusz zostanie kliknięty prawym przyciskiem myszy
Oblicz	Przed obliczeniem lub przeliczeniem arkusza roboczego
Reszta	Gdy komórki w arkuszu zostaną zmienione
Dezaktywować	Gdy arkusz jest dezaktywowany
Aktualizacja tabeli przestawnej	Gdy tabela przestawna w arkuszu zostanie zaktualizowana
WybórZmiana	Po zmianie zaznaczenia w arkuszu

Zauważ, że to nie jest pełna lista. Pełną listę znajdziesz tutaj.

Pamiętaj, że kod zdarzenia Worksheet jest przechowywany w oknie kodu obiektu arkusza roboczego (w tym, w którym chcesz, aby zdarzenie zostało wyzwolone). W skoroszycie może znajdować się wiele arkuszy, a kod zostanie uruchomiony tylko wtedy, gdy zdarzenie nastąpi w arkuszu, w którym jest umieszczone.

Przyjrzyjmy się teraz kilku przydatnym wydarzeniom w arkuszu i zobaczmy, jak można je wykorzystać w codziennej pracy.

Arkusz roboczy Aktywuj wydarzenie

To zdarzenie jest wyzwalane, gdy aktywujesz arkusz.

Poniższy kod usuwa ochronę arkusza zaraz po jego aktywacji.

Private Sub Worksheet_Activate() ActiveSheet.Unprotect End Sub

Możesz również użyć tego zdarzenia, aby upewnić się, że konkretna komórka lub zakres komórek (lub nazwany zakres) jest zaznaczona zaraz po aktywowaniu arkusza. Poniższy kod wybierze komórkę D1, gdy tylko aktywujesz arkusz.

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

Zmiana arkusza roboczego

Zdarzenie zmiany jest uruchamiane za każdym razem, gdy wprowadzasz zmianę w arkuszu.

Cóż… nie zawsze.

Niektóre zmiany wywołują zdarzenie, a inne nie. Oto lista niektórych zmian, które nie wywołają zdarzenia:

• Po zmianie formatowania komórki (rozmiar czcionki, kolor, obramowanie itp.).
• Kiedy łączysz komórki. Jest to zaskakujące, ponieważ czasami scalanie komórek usuwa również zawartość ze wszystkich komórek poza lewą górną.
• Gdy dodajesz, usuwasz lub edytujesz komentarz w komórce.
• Kiedy sortujesz zakres komórek.
• Kiedy używasz Szukaj celu.

Następujące zmiany wywołałyby zdarzenie (nawet jeśli myślisz, że nie powinno):

• Kopiowanie i wklejanie formatowania wywołałoby zdarzenie.
• Wyczyszczenie formatowania wywołałoby zdarzenie.
• Uruchomienie sprawdzania pisowni wywołałoby zdarzenie.

Poniżej znajduje się kod pokazujący okno komunikatu z adresem komórki, która została zmieniona.

Private Sub Worksheet_Change(ByVal Target As Range) MsgBox "Właśnie zmieniłeś" & Target.Address End Sub

Chociaż jest to bezużyteczne makro, pokazuje, jak użyć argumentu Cel, aby dowiedzieć się, które komórki zostały zmienione.

Zobaczmy teraz kilka bardziej przydatnych przykładów.

Załóżmy, że masz zakres komórek (powiedzmy A1: D10) i chcesz wyświetlić monit i zapytać użytkownika, czy naprawdę chciał zmienić komórkę w tym zakresie, czy nie, możesz użyć poniższego kodu.

Pokazuje monit z dwoma przyciskami - Tak i Nie. Jeśli użytkownik wybierze „Tak”, zmiana zostanie wykonana, w przeciwnym razie zostanie odwrócona.

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox("Dokonujesz zmiany w komórkach w A1:D10. Czy na pewno tego chcesz?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

W powyższym kodzie sprawdzamy, czy komórka Target znajduje się w pierwszych 4 kolumnach i pierwszych 10 wierszach. W takim przypadku zostanie wyświetlone okno komunikatu. Ponadto, jeśli użytkownik wybrał opcję Nie w oknie komunikatu, zmiana zostanie cofnięta (za pomocą polecenia Aplikacja.Cofnij).

Zauważ, że przed wierszem Application.Undo użyłem Application.EnableEvents = False. A potem odwróciłem to, używając w następnym wierszu Application.EnableEvent = True.

Jest to potrzebne, ponieważ cofanie powoduje również zdarzenie zmiany. Jeśli nie ustawię EnableEvent na False, będzie on nadal wyzwalał zdarzenie zmiany.

Możesz również monitorować zmiany w nazwanym zakresie za pomocą zdarzenia zmiany. Na przykład, jeśli masz nazwany zakres o nazwie „DataRange” i chcesz wyświetlić monit w przypadku, gdy użytkownik dokona zmiany w tym nazwanym zakresie, możesz użyć poniższego kodu:

Private Sub Worksheet_Change(ByVal Target As Range) Dim DRange As Range Set DRange = Range("DataRange") If Not Intersect(Target, DRange) To nic Then MsgBox "Właśnie dokonałeś zmiany w zakresie danych" End If End Sub

Powyższy kod sprawdza, czy komórka/zakres, w którym wprowadzono zmiany, zawiera jakiekolwiek komórki wspólne dla zakresu danych. Jeśli tak, pokazuje okno komunikatu.

Wybór skoroszytuZmień zdarzenie

Zdarzenie zmiany wyboru jest wyzwalane za każdym razem, gdy w arkuszu nastąpi zmiana wyboru.

Poniższy kod ponownie obliczy arkusz, gdy tylko zmienisz zaznaczenie.

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

Innym przykładem tego zdarzenia jest podświetlenie aktywnego wiersza i kolumny wybranej komórki.

Coś, jak pokazano poniżej:

Poniższy kod może to zrobić:

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) End With Napis końcowy

Kod najpierw usuwa kolor tła ze wszystkich komórek, a następnie stosuje ten wymieniony w kodzie do aktywnego wiersza i kolumny.

I to jest problem z tym kodem. Że usuwa kolor ze wszystkich komórek.

Jeśli chcesz podświetlić aktywny wiersz/kolumnę, zachowując nienaruszony kolor w innych komórkach, użyj techniki przedstawionej w tym samouczku.

Zdarzenie DoubleClick w skoroszycie

Jest to jedno z moich ulubionych wydarzeń w arkuszu roboczym i zobaczysz wiele samouczków, w których z niego korzystałem (takich jak ten lub ten).

To zdarzenie jest wywoływane po dwukrotnym kliknięciu komórki.

Pokażę ci, jakie to jest niesamowite.

Za pomocą poniższego kodu możesz dwukrotnie kliknąć komórkę, aby zastosować kolor tła, zmienić kolor czcionki i pogrubić tekst w komórce;

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

Może to być przydatne, gdy przeglądasz listę komórek i chcesz wyróżnić kilka wybranych. Chociaż możesz użyć klawisza F4, aby powtórzyć ostatni krok, będzie mógł zastosować tylko jeden rodzaj formatowania. Dzięki temu zdarzeniu podwójnego kliknięcia możesz zastosować wszystkie trzy za pomocą dwukrotnego kliknięcia.

Zauważ, że w powyższym kodzie wprowadziłem wartość Cancel = True.

Odbywa się to tak, że domyślna akcja podwójnego kliknięcia jest wyłączona - czyli przejście do trybu edycji. Gdy Anuluj = True, program Excel nie przeniesie Cię do trybu edycji po dwukrotnym kliknięciu komórki.

Oto kolejny przykład.

Jeśli masz listę zadań do wykonania w programie Excel, możesz użyć zdarzenia dwukrotnego kliknięcia, aby zastosować format przekreślenia, aby oznaczyć zadanie jako ukończone.

Coś, jak pokazano poniżej:

Oto kod, który to zrobi:

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

Zauważ, że w tym kodzie zrobiłem dwukrotne kliknięcie jako zdarzenie przełączania. Po dwukrotnym kliknięciu komórki sprawdza, czy format przekreślenia został już zastosowany. Jeśli tak było, dwukrotne kliknięcie usuwa format przekreślenia, a jeśli tak nie było, zostanie zastosowany format przekreślenia.

Excel VBA OnTime Event

Zdarzenia, które widzieliśmy do tej pory w tym artykule, były powiązane z jednym z obiektów programu Excel, czy to skoroszytem, ​​arkuszem, arkuszem wykresu, formularzami użytkownika itp.

Zdarzenie OnTime różni się od innych zdarzeń tym, że może być przechowywane w zwykłym module VBA (pozostałe miały być umieszczone w oknie kodu obiektów takich jak ThisWorkbook lub Worksheets lub UserForms).

W zwykłym module VBA jest używany jako metoda obiektu aplikacji.

Powodem, dla którego jest to uważane za zdarzenie, jest to, że może zostać wywołane w określonym przez Ciebie czasie. Na przykład, jeśli chcę, aby arkusz był przeliczany co 5 minut, mogę użyć do tego zdarzenia OnTime.

Lub, jeśli chcę pokazać wiadomość/przypomnienie o określonej porze dnia, mogę użyć zdarzenia OnTime.

Poniżej znajduje się kod, który będzie wyświetlał wiadomość codziennie o 14:00.

Sub MessageTime() Application.OnTime TimeValue("14:00:00"), "ShowMessage" End Sub Sub ShowMessage() MsgBox "Czas na lunch" End Sub

Pamiętaj, że musisz umieścić ten kod w zwykłym module VBA,

Ponadto, chociaż zdarzenie OnTime zostałoby wyzwolone w określonym czasie, w dowolnym momencie należy uruchomić makro ręcznie. Po uruchomieniu makra poczeka do 2 po południu, a następnie wywoła makro „ShowMessage”.

Makro ShowMessage wyświetli komunikat.

Zdarzenie OnTime przyjmuje cztery argumenty:

Aplikacja.OnTime(Najwcześniejszy czas, Procedura, Ostatni czas, Harmonogram)

• Najwcześniejsza godzina: Czas, w którym chcesz uruchomić procedurę.
• Procedura: Nazwa procedury, która powinna zostać uruchomiona.
• Ostatni czas (opcjonalnie): W przypadku, gdy działa inny kod, a podany przez Ciebie kod nie może zostać uruchomiony w określonym czasie, możesz określić ostatni czas, na który powinien czekać. Na przykład może to być EarlyTime + 45 (co oznacza, że ​​czeka 45 sekund na zakończenie drugiej procedury). Jeśli nawet po 45 sekundach procedura nie może działać, zostaje przerwana. Jeśli tego nie określisz, program Excel poczeka, aż kod będzie mógł zostać uruchomiony, a następnie go uruchomi.
• Harmonogram (opcjonalnie): Jeśli ustawione na True, planuje nową procedurę czasu. Jeśli False, to anuluje poprzednio ustawioną procedurę. Domyślnie jest to prawda.

W powyższym przykładzie użyliśmy tylko dwóch pierwszych argumentów.

Spójrzmy na inny przykład.

Poniższy kod odświeżałby arkusz co 5 minut.

Dim NextRefresh jako Data Sub RefreshSheet() ThisWorkbook.Worksheets("Arkusz1").Calculate NextRefresh = Now + TimeValue("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End Sub Sub StopRefresh() W przypadku błędu Wznów Dalej Application.OnTime NextRefresh, "RefreshSheet", , False End Sub

Powyższy kod odświeżałby arkusz co 5 minut.

Wykorzystuje funkcję Now do określenia aktualnego czasu, a następnie dodaje 5 minut do aktualnego czasu.

Zdarzenie OnTime będzie działać, dopóki go nie zatrzymasz. Jeśli zamkniesz skoroszyt, a aplikacja Excel nadal działa (inne skoroszyty są otwarte), skoroszyt, w którym jest uruchomione zdarzenie OnTime, otworzy się ponownie.

Jest to lepiej obsługiwane przez zatrzymanie zdarzenia OnTime.

W powyższym kodzie mam kod StopRefresh, ale musisz go wykonać, aby zatrzymać zdarzenie OnTime. Możesz to zrobić ręcznie, przypisać go do przycisku i zrobić to, naciskając przycisk lub wywołać go ze zdarzenia Workbook Close.

Private Sub Workbook_BeforeClose(Anuluj jako Boolean) Wywołaj StopRefresh End Sub

Powyższy kod zdarzenia „BeforeClose” pojawia się w oknie kodu ThisWorkbook.

Wydarzenie Excel VBA OnKey

Podczas pracy z programem Excel stale monitoruje naciśnięcia klawiszy, których używasz. To pozwala nam używać naciśnięć klawiszy jako wyzwalacza zdarzenia.

Dzięki zdarzeniu OnKey możesz określić naciśnięcia klawisza (lub kombinację naciśnięć klawiszy) oraz kod, który powinien zostać wykonany, gdy zostanie użyty ten klawisz. Kiedy te naciśnięcia klawiszy zostaną naciśnięte, wykona dla niego kod.

Podobnie jak w przypadku wydarzenia OnTime, musisz mieć sposób na anulowanie wydarzenia OnKey. Ponadto po ustawieniu zdarzenia OnKey dla określonego naciśnięcia klawisza staje się ono dostępne we wszystkich otwartych skoroszytach.

Zanim pokażę ci przykład użycia zdarzenia OnKey, pozwól mi najpierw udostępnić kody kluczy, które są dostępne w VBA.

KLUCZ	KOD
Backspace	{BACKSPACE} lub {BS}
Złamać	{ZŁAMAĆ}
Duże litery	{DUŻE LITERY}
Usunąć	{USUŃ} lub {USUŃ}
Strzałka w dół	{NA DÓŁ}
Koniec	{KONIEC}
Wchodzić	~
Enter (na klawiaturze numerycznej)	{WEJŚĆ}
Ucieczka	{ESCAPE} lub {ESC}
Dom	{DOM}
Ins	{WSTAWIĆ}
Strzałka w lewo	{LEWO}
NumLock	{NUMLOCK}
Strona w dół	{PGDN}
Strona w górę	{PGUP}
Prawa strzałka	{DOBRZE}
Blokada przewijania	{SCROLLOCK}
Patka	{PATKA}
Strzałka w górę	{W GÓRĘ}
F1 do F15	{F1} do {F15}

Kiedy potrzebujesz użyć dowolnego zdarzenia onkey, musisz użyć do niego kodu.

Powyższa tabela zawiera kody dla pojedynczych naciśnięć klawiszy.

Możesz je również połączyć z następującymi kodami:

• Zmiana: + (znak plusa)
• Kontrola: ^ (Wstawka korektorska)
• Alt: % (Odsetek)

Na przykład dla Alt F4 musisz użyć kodu: „%{F4}” - gdzie % to klawisz ALT, a {F4} to klawisz F4.

Teraz spójrzmy na przykład (pamiętaj, że kod zdarzeń OnKey znajduje się w zwykłym module VBA).

Kiedy naciśniesz klawisz PageUp lub PageDown, przeskakuje on 29 wierszy powyżej / poniżej aktywnej komórki (przynajmniej to robi na moim laptopie).

Jeśli chcesz, aby przeskakiwał tylko 5 wierszy na raz, możesz użyć poniższego kodu:

Sub PageUpDownKeys() Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub Sub PageUpMod() W przypadku błędu Wznów następny ActiveCell.Offset(-5, 0).Aktywuj Koniec Sub Sub PageDownMod() W przypadku błędu Wznów Dalej ActiveCell.Offset(5, 0).Aktywuj Koniec Sub

Kiedy uruchomisz pierwszą część kodu, uruchomi ona zdarzenia OnKey. Gdy to zostanie wykonane, użycie klawiszy PageUp i PageDown spowoduje, że kursor przeskoczy tylko o 5 wierszy na raz.

Zwróć uwagę, że użyliśmy opcji „Po wznowieniu błędu dalej”, aby upewnić się, że błędy są ignorowane. Te błędy mogą wystąpić, gdy naciśniesz klawisz PageUp, nawet gdy jesteś na górze arkusza. Ponieważ nie ma więcej wierszy do przeskoczenia, kod wyświetli błąd. Ale ponieważ użyliśmy „On Error Resume Next”, zostanie zignorowany.

Aby upewnić się, że te zdarzenia OnKey są dostępne, musisz uruchomić pierwszą część kodu. Jeśli chcesz, aby było to dostępne zaraz po otwarciu skoroszytu, możesz umieścić to w oknie kodu ThisWorkbook.

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

Poniższy kod przywróci klucze do ich normalnej funkcjonalności.

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

Jeśli nie określisz drugiego argumentu w metodzie OnKey, przywróci to naciśnięcie klawisza do jego zwykłej funkcjonalności.

Jeśli chcesz anulować funkcjonalność naciśnięcia klawisza, aby program Excel nic nie robił, gdy to naciśnięcie klawisza jest używane, musisz użyć pustego ciągu jako drugiego argumentu.

W poniższym kodzie Excel nic nie zrobi, gdy użyjemy klawiszy PageUp lub PageDown.

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

Wyłączanie zdarzeń w VBA

Czasami może być konieczne wyłączenie zdarzeń, aby kod działał poprawnie.

Załóżmy na przykład, że mam zakres (A1: D10) i chcę wyświetlać komunikat za każdym razem, gdy komórka zostanie zmieniona w tym zakresie. Wyświetlam więc okno komunikatu i pytam użytkownika, czy jest pewien, że chce dokonać zmiany. Jeśli odpowiedź brzmi Tak, zmiana zostanie dokonana, a jeśli odpowiedź brzmi Nie, to VBA cofnie ją.

Możesz użyć poniższego kodu:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox("Dokonujesz zmiany w komórkach w A1:D10. Czy na pewno tego chcesz?", vbYesNo) End If Ans = vbNo Then Application.Undo End If End Sub

Problem z tym kodem polega na tym, że gdy użytkownik wybierze Nie w oknie komunikatu, akcja jest odwrócona (ponieważ użyłem Application.Undo).

Gdy nastąpi cofnięcie, a wartość zostanie zmieniona z powrotem na pierwotną, zdarzenie zmiany VBA jest ponownie wyzwalane, a użytkownikowi ponownie wyświetlane jest to samo okno komunikatu.

Oznacza to, że możesz nadal klikać NIE w polu wiadomości, a będzie ono nadal wyświetlane. Dzieje się tak, ponieważ w tym przypadku utknąłeś w nieskończonej pętli.

Aby uniknąć takich przypadków, musisz wyłączyć zdarzenia, aby zdarzenie zmiany (lub jakiekolwiek inne zdarzenie) nie zostało wyzwolone.

Poniższy kod sprawdziłby się w tym przypadku:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row <= 10 And Target.Column <= 4 Then Ans = MsgBox("Dokonujesz zmiany w komórkach w A1:D10. Czy na pewno tego chcesz?", vbYesNo) End If If Ans = vbNo Then Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

W powyższym kodzie, tuż nad linią Application.Undo, użyliśmy - Application.EnableEvents = False.

Ustawienie EnableEvents na False nie spowoduje wyzwolenia żadnego zdarzenia (w bieżącym lub żadnym otwartym skoroszycie).

Po zakończeniu operacji cofania możemy zmienić właściwość EnableEvents z powrotem na True.

Należy pamiętać, że wyłączenie zdarzeń wpływa na wszystkie skoroszyty, które są aktualnie otwarte (lub otwarte, gdy EnableEvents jest ustawione na False). Na przykład, jako część kodu, jeśli otworzysz nowy skoroszyt, zdarzenie Workbook Open nie zadziała.

Wpływ zdarzeń Cofnij stos

Pozwól, że najpierw powiem, czym jest stos cofania.

Kiedy pracujesz w Excelu, stale monitoruje Twoje działania. Gdy popełnisz błąd, zawsze możesz użyć Control + Z, aby wrócić do poprzedniego kroku (tj. cofnąć bieżącą akcję).

Jeśli dwukrotnie naciśniesz Control + Z, cofniesz się o dwa kroki. Wykonane kroki są przechowywane jako część stosu Cofnij.

Każde zdarzenie zmieniające arkusz roboczy niszczy ten stos cofania.Oznacza to, że jeśli zrobiłem 5 rzeczy przed wyzwoleniem zdarzenia, nie będę mógł użyć Control + Z, aby wrócić do poprzednich kroków. Wywołanie zdarzenia zniszczyło dla mnie ten stos.

W poniższym kodzie używam VBA do wprowadzania znacznika czasu w komórce A1 za każdym razem, gdy nastąpi zmiana w arkuszu.

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

Ponieważ wprowadzam zmianę w arkuszu, zniszczy to stos cofania.

Pamiętaj też, że nie ogranicza się to tylko do wydarzeń.

Jeśli masz kod przechowywany w zwykłym module VBA i dokonasz zmiany w arkuszu, zniszczy to również stos cofania w programie Excel.

Na przykład poniższy kod po prostu wpisz tekst „Hello” w komórce A1, ale nawet uruchomienie tego spowoduje zniszczenie stosu cofania.

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

Możesz również polubić następujące samouczki Excel VBA:

• Praca z komórkami i zakresami w Excel VBA.
• Praca z Arkuszami w Excel VBA.
• Praca ze skoroszytami w Excel VBA.
• Pętle Excel VBA - najlepszy przewodnik.
• Korzystanie z instrukcji IF Then Else w Excel VBA.
• Dla następnej pętli w Excelu.
• Tworzenie funkcji zdefiniowanych przez użytkownika w Excel VBA.
• Jak tworzyć i używać dodatków w programie Excel.
• Twórz i ponownie wykorzystuj makra, zapisując je w skoroszycie makr osobistych.