Zdarzenia dzieją się cały czas, gdy użytkownik otwiera skoroszyt programu Excel i zaczyna wykonywać różne czynności, takie jak wprowadzanie danych do komórek lub przechodzenie między arkuszami
W Edytorze Visual Basic (ALT+F11) są już skonfigurowane podprocedury, które mogą zostać uruchomione, gdy użytkownik zrobi coś, np. wprowadzanie danych do komórki. Procedura sub nie dostarcza żadnego kodu akcji, a jedynie instrukcję „Sub” i instrukcję „End Sub” bez żadnego kodu między nimi. Są praktycznie uśpione, więc nic się nie dzieje, dopóki nie wpiszesz kodu.
Oto przykład oparty na zdarzeniu „Zmiana” w arkuszu:
Jako programista VBA możesz dodać kod, aby pewne rzeczy się wydarzyły, gdy użytkownik wykona określoną akcję. Daje to możliwość kontrolowania użytkownika i zapobiegania podejmowaniu przez niego działań, których nie chcesz, a które mogą uszkodzić skoroszyt. Na przykład możesz chcieć, aby zapisali własną kopię skoroszytu pod inną nazwą, aby nie wpływały na oryginał, który może być używany przez wielu użytkowników.
Jeśli zamkną skoroszyt, zostaną automatycznie poproszeni o zapisanie zmian. Jednak skoroszyt zawiera zdarzenie „BeforeClose” i możesz wprowadzić kod, aby zapobiec zamknięciu skoroszytu i wyzwoleniu zdarzenia „Zapisz”. Następnie możesz dodać przycisk do samego arkusza roboczego i umieścić na nim własną procedurę „Zapisz”. Możesz także wyłączyć procedurę „Zapisz” za pomocą zdarzenia „Przed zapisaniem”
Zrozumienie, jak działają zdarzenia, jest absolutnie niezbędne dla programisty VBA.
Rodzaje wydarzeń
zeszyt ćwiczeń Wydarzenia - te zdarzenia są uruchamiane na podstawie tego, co użytkownik robi z samym skoroszytem. Obejmują one działania użytkownika, takie jak otwieranie skoroszytu, zamykanie skoroszytu, zapisywanie skoroszytu, dodawanie lub usuwanie arkusza
Wydarzenia w arkuszu roboczym - te zdarzenia są uruchamiane przez użytkownika wykonującego działania na określonym arkuszu. Każdy arkusz w skoroszycie ma indywidualny moduł kodu, który zawiera różne zdarzenia specjalnie dla tego arkusza (nie dla wszystkich arkuszy). Obejmują one działania użytkownika, takie jak zmiana zawartości komórki, dwukrotne kliknięcie komórki lub kliknięcie komórki prawym przyciskiem myszy.
Aktywne zdarzenia kontrolne X - Kontrolki Active X można dodać do arkusza roboczego za pomocą ikony „Wstaw” na karcie „Programista” na wstążce programu Excel. Są to często kontrolki przycisków, które umożliwiają użytkownikowi podejmowanie różnych działań pod kontrolą kodu, ale mogą to być również obiekty, takie jak listy rozwijane. Używanie kontrolek Active X w przeciwieństwie do kontrolek formularzy w arkuszu daje cały zakres programowalności. Kontrolki Active X zapewniają znacznie większą elastyczność z punktu widzenia programowania w zakresie używania kontrolek formularzy w arkuszu.
Na przykład możesz mieć dwie rozwijane kontrolki w swoim arkuszu. Chcesz, aby dostępna lista w drugim menu rozwijanym była oparta na tym, co użytkownik wybrał w pierwszym menu rozwijanym. Korzystając ze zdarzenia „Zmień” w pierwszym menu rozwijanym, możesz utworzyć kod, aby odczytać to, co wybrał użytkownik, a następnie zaktualizować drugie menu. Możesz także dezaktywować drugie menu, dopóki użytkownik nie dokona wyboru w pierwszym menu
Zdarzenia UserForm - Możesz wstawić i zaprojektować profesjonalnie wyglądający formularz, który będzie używany jako wyskakujące okienko. Wszystkie kontrolki umieszczone w formularzu są kontrolkami Active X i zawierają te same zdarzenia, co kontrolki Active X, które można umieścić w arkuszu
Wydarzenia na wykresie - Te zdarzenia są związane tylko z arkuszem wykresu, a nie wykresem pojawiającym się jako część arkusza roboczego. Zdarzenia te obejmują zmianę rozmiaru wykresu lub wybór wykresu.
Zdarzenia aplikacji - Używają one obiektu Application w VBA. Przykłady pozwoliłyby na uruchomienie kodu po naciśnięciu określonego klawisza lub po osiągnięciu określonego czasu. Można zaprogramować sytuację, w której skoroszyt pozostaje otwarty 24/7 i importuje dane z zewnętrznego źródła w nocy we wcześniej ustalonym czasie.
Niebezpieczeństwa związane z używaniem kodu podczas wydarzeń
Kiedy piszesz kod, aby coś zrobić, gdy użytkownik wykona określoną akcję, musisz pamiętać, że Twój kod może wyzwalać inne zdarzenia, które mogą wprowadzić Twój kod w ciągłą pętlę.
Załóżmy na przykład, że używasz zdarzenia „Zmiana” w arkuszu, aby po wprowadzeniu przez użytkownika wartości do komórki obliczenia oparte na tej komórce były umieszczane w komórce bezpośrednio po jej prawej stronie.
Problem polega na tym, że umieszczenie obliczonej wartości w komórce wyzwala kolejne zdarzenie „Zmiana”, które z kolei wyzwala kolejne zdarzenie „Zmiana” i tak dalej, dopóki w kodzie nie zabraknie kolumn do użycia i pojawi się komunikat o błędzie.
Musisz dokładnie przemyśleć podczas pisania kodu zdarzenia, aby upewnić się, że inne zdarzenia nie zostaną wywołane przypadkowo
Wyłącz wydarzenia
Możesz użyć kodu, aby wyłączyć zdarzenia, aby obejść ten problem. To, co musisz zrobić, to włączyć kod, aby wyłączyć zdarzenia, gdy kod zdarzenia jest uruchomiony, a następnie ponownie włączyć zdarzenia na końcu kodu. Oto przykład, jak to zrobić:
| 1234 | Sub WyłączZdarzenia()Application.EnableEvents = FalseApplication.EnableEvents = PrawdaNapis końcowy |
Pamiętaj, że spowoduje to wyłączenie wszystkich zdarzeń w aplikacji Excel, więc wpłynie to również na inne funkcje w programie Excel. Jeśli używasz tego z jakiegokolwiek powodu, upewnij się, że wydarzenia są później ponownie włączane.
Znaczenie parametrów w zdarzeniach
Zdarzenia zwykle mają parametry, których możesz użyć, aby dowiedzieć się więcej o tym, co robi użytkownik i lokalizacji komórki, w której się znajduje.
Na przykład zdarzenie Worksheet Change wygląda tak:
| 1 | Private Sub Worksheet_Change (ByVal Target As Range) |
Korzystając z obiektu range, możesz znaleźć współrzędne wiersza/kolumny komórki, w której faktycznie znajduje się użytkownik.
| 1234 | Private Sub Worksheet_Change (ByVal Target As Range)MsgBox Target.ColumnMsgBox Target.RowNapis końcowy |
Jeśli chcesz, aby Twój kod działał tylko na określonym numerze kolumny lub wiersza, dodaj warunek, który powoduje wyjście z podprogramu, jeśli kolumna nie jest wymagana.
| 123 | Private Sub Worksheet_Change (ByVal Target As Range)Jeśli Target.Column 2 to wyjdź z SubNapis końcowy |
Pozwala to obejść problem związany z wyzwalaniem wielu zdarzeń w kodzie, ponieważ będzie działać tylko wtedy, gdy użytkownik zmienił komórkę w kolumnie 2 (kolumna B)
Przykłady zdarzeń ze skoroszytu (niewyczerpujące)
Zdarzenia ze skoroszytu znajdują się w obiekcie „ThisWorkbook” w Eksploratorze projektów VBE. Będziesz musiał wybrać „Skoroszyt” na pierwszym menu rozwijanym w oknie kodu, a następnie drugie menu pokaże wszystkie dostępne zdarzenia
Wydarzenie otwarte w skoroszycie
To zdarzenie jest uruchamiane za każdym razem, gdy skoroszyt zostanie otwarty przez użytkownika. Możesz go użyć, aby umieścić wiadomość powitalną dla użytkownika, przechwytując jego nazwę użytkownika
| 123 | Prywatny skoroszyt podrzędny_Open()MsgBox "Witamy" i Application.UserNameNapis końcowy |
Możesz również sprawdzić ich nazwę użytkownika na liście przechowywanej w ukrytym arkuszu, aby sprawdzić, czy są upoważnieni do dostępu do skoroszytu. Jeśli nie są autoryzowanym użytkownikiem, możesz wyświetlić komunikat i zamknąć skoroszyt, aby nie mogli z niego korzystać.
Skoroszyt Nowe zdarzenie arkusza
To zdarzenie jest wywoływane, gdy użytkownik doda nowy arkusz do skoroszytu
Możesz użyć tego kodu, aby pozwolić sobie tylko na dodawanie nowego arkusza, zamiast zmuszać różnych użytkowników do dodawania arkuszy i robienia bałaganu w skoroszycie
| 1234567 | Private Sub Workbook_NewSheet(ByVal Sh As Object)Application.DisplayAlerts = FalseJeśli Application.UserName "Richard" WtedySh.UsuńZakończ, jeśliApplication.DisplayAlerts = PrawdaNapis końcowy |
Pamiętaj, że musisz wyłączyć alerty, ponieważ po usunięciu arkusza pojawi się ostrzeżenie użytkownika, co pozwoli mu obejść Twój kod. Upewnij się, że później włączysz alerty!
Masz dość wyszukiwania przykładów kodu VBA? Wypróbuj Automakro!
Skoroszyt przed zapisaniem wydarzenia
To zdarzenie jest wywoływane, gdy użytkownik kliknie ikonę „Zapisz”, ale zanim faktycznie nastąpi „Zapisz”
Jak opisano wcześniej, możesz uniemożliwić użytkownikom zapisywanie zmian w oryginalnym skoroszycie i wymusić na nich utworzenie nowej wersji za pomocą przycisku w arkuszu. Wszystko, co musisz zrobić, to zmienić parametr „Anuluj” na True, a skoroszytu nigdy nie można zapisać konwencjonalną metodą.
| 123 | Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)Anuluj = PrawdaNapis końcowy |
Zeszyt ćwiczeń przed zamknięciem wydarzenia
Możesz użyć tego zdarzenia, aby uniemożliwić użytkownikom zamknięcie skoroszytu i ponownie zmusić ich do wyjścia za pomocą przycisku arkusza. Ponownie ustawiasz parametr „Anuluj” na „Prawda”. Czerwony X w prawym górnym rogu okna Excela już nie działa.
| 123 | Prywatny skoroszyt pomocniczy_BeforeClose (Anuluj jako Boolean)Anuluj = PrawdaNapis końcowy |
Przykłady zdarzeń w arkuszu roboczym (niewyczerpujące)
Zdarzenia arkusza roboczego znajdują się pod określonym obiektem nazwy arkusza w Eksploratorze projektu VBE. Będziesz musiał wybrać „Arkusz pracy” na pierwszym menu rozwijanym w oknie kodu, a następnie drugie menu pokaże wszystkie dostępne zdarzenia
Zmiana arkusza roboczego
To zdarzenie jest wywoływane, gdy użytkownik dokona zmiany w arkuszu, na przykład wprowadzenia nowej wartości do komórki
Możesz użyć tego zdarzenia, aby umieścić dodatkową wartość lub komentarz obok zmienionej komórki, ale jak wspomniano wcześniej, nie chcesz rozpoczynać uruchamiania pętli zdarzeń.
| 12345 | Private Sub Worksheet_Change (ByVal Target As Range)Jeśli Target.Column 2 to wyjdź z SubAktywnyArkusz.Komórki(Docelowy.Wier, Cel.Kolumna + 1). Wartość = _ActiveSheet.Cells(Target.Row, Target.Column). Wartość * 1,1Napis końcowy |
W tym przykładzie kod będzie działał tylko wtedy, gdy wartość zostanie wprowadzona w kolumnie B (kolumna 2). Jeśli to prawda, doda 10% do liczby i umieści ją w następnej dostępnej komórce
Arkusz roboczy przed zdarzeniem podwójnego kliknięcia
To zdarzenie uruchomi kod, jeśli użytkownik dwukrotnie kliknie komórkę. Może to być niezwykle przydatne w przypadku raportów finansowych, takich jak bilans lub rachunek zysków i strat, gdzie dane liczbowe mogą być kwestionowane przez menedżerów, zwłaszcza jeśli wynik końcowy jest ujemny!
Możesz to wykorzystać, aby udostępnić funkcję drążenia, dzięki czemu gdy kierownik kwestionuje konkretny numer, wystarczy, że kliknie dwukrotnie ten numer, a podział pojawi się jako część raportu.
Jest to bardzo imponujące z punktu widzenia użytkownika i oszczędza mu ciągłego pytania „dlaczego ta liczba jest tak wysoka?”
Trzeba by napisać kod, aby znaleźć nagłówek/kryteria dla numeru (za pomocą właściwości obiektu Target), a następnie przefiltrować dane tabelaryczne, a następnie skopiować je do raportu.
Programowanie VBA | Generator kodu działa dla Ciebie!
Arkusz roboczy Aktywuj wydarzenie
To zdarzenie występuje, gdy użytkownik przechodzi z jednego arkusza do drugiego. Dotyczy nowego arkusza, do którego przechodzi użytkownik.
Może być używany do zapewnienia, że nowy arkusz jest całkowicie obliczony, zanim użytkownik zacznie na nim cokolwiek robić. Można go również użyć do ponownego obliczenia tylko tego konkretnego arkusza bez ponownego obliczania całego skoroszytu. Jeśli skoroszyt jest duży i zawiera skomplikowaną formułę, to ponowne obliczenie jednego arkusza oszczędza dużo czasu
| 123 | Prywatny arkusz pomocniczy_Aktywuj()Aktywny arkusz.ObliczNapis końcowy |
Zdarzenia kontroli Active X (niewyczerpujące)
Jak wspomniano wcześniej, możesz dodać kontrolki Active X bezpośrednio do arkusza. Mogą to być przyciski poleceń, listy rozwijane i pola listy
Zdarzenia Active X znajdują się pod określonym obiektem nazwy arkusza (w którym dodano kontrolkę) w Eksploratorze projektów VBE. Będziesz musiał wybraćnazwękontrolki Active X na pierwszej liście rozwijanej w oknie kodu, a następnie druga lista pokaże wszystkie dostępne zdarzenia
Przycisk polecenia Kliknij zdarzenie
Po umieszczeniu przycisku polecenia w arkuszu kalkulacyjnym będziesz chciał, aby wykonał jakąś akcję. Robisz to, umieszczając kod w zdarzeniu Click.
Możesz łatwo umieścić na tym komunikat „Czy na pewno?”, aby sprawdzić, zanim Twój kod zostanie uruchomiony
| 12345 | Private Sub CommandButton1_Click ()Przycisk ściemniania Powt jako wariantButtonRet = MsgBox("Czy na pewno chcesz to zrobić?", vbQuestion lub vbYesNo)Jeśli ButtonRet = vbNo, to wyjdź z SubNapis końcowy |
Rozwijane (pole kombi) Zmień wydarzenie
Lista rozwijana Active X zawiera zdarzenie zmiany, więc jeśli użytkownik wybierze określony element z listy rozwijanej, można przechwycić jego wybór za pomocą tego zdarzenia, a następnie napisać kod, aby odpowiednio dostosować inne części arkusza lub skoroszytu.
| 123 | Prywatne pole podrzędne ComboBox1_Change ()MsgBox "Wybrałeś " & ComboBox1.TextNapis końcowy |
Programowanie VBA | Generator kodu działa dla Ciebie!
Zaznacz pole (pole wyboru) Kliknij Zdarzenie
Możesz dodać zaznaczenie lub pole wyboru do arkusza roboczego, aby zapewnić użytkownikowi możliwość wyboru opcji. Możesz użyć zdarzenia kliknięcia, aby sprawdzić, czy użytkownik coś w tym zmienił. Zwracane wartości to True lub False w zależności od tego, czy zostało zaznaczone, czy nie.
| 123 | Prywatne pole wyboru 1_Click ()Pole wyboru MsgBox1.WartośćNapis końcowy |
Zdarzenia UserForm (niewyczerpujące)
Excel umożliwia projektowanie własnych formularzy. Mogą być bardzo przydatne do wykorzystania jako wyskakujące okienka do zbierania informacji lub zapewniania użytkownikowi wielu możliwości wyboru. Używają kontrolek Active X, jak opisano wcześniej i mają dokładnie te same zdarzenia, chociaż zdarzenia zależą w dużej mierze od typu kontrolki.
Oto przykład prostego formularza:
Kiedy jest wyświetlany, tak wygląda na ekranie
Możesz użyć zdarzeń w formularzu, aby zrobić takie rzeczy, jak wpisanie domyślnej nazwy firmy, gdy formularz jest otwarty, aby sprawdzić, czy wpisana nazwa firmy zgadza się z tą, która jest już w arkuszu kalkulacyjnym i nie została błędnie napisana, oraz aby dodać kod do kliknięcia zdarzenia na przyciskach „OK” i „Anuluj”
Kod i zdarzenia za formularzem można wyświetlić, klikając dwukrotnie w dowolnym miejscu formularza
Pierwsza lista rozwijana daje dostęp do wszystkich kontrolek w formularzu. Drugie menu rozwijane daje dostęp do wydarzeń
UserForm Aktywuj zdarzenie
To zdarzenie jest wyzwalane, gdy formularz jest aktywowany, zwykle gdy jest wyświetlany. To zdarzenie może być wykorzystane do ustawienia wartości domyślnych, np. domyślna nazwa firmy w polu tekstowym nazwy firmy
| 123 | Prywatna Sub UserForm_Activate()TextBox1.Text = "Nazwa mojej firmy"Napis końcowy |
Programowanie VBA | Generator kodu działa dla Ciebie!
Zmień wydarzenie
Większość kontrolek w formularzu ma zdarzenie zmiany, ale w tym przykładzie pole tekstowe nazwy firmy może użyć zdarzenia do wprowadzenia ograniczenia długości wprowadzanej nazwy firmy
| 123456 | Prywatne podrzędne pole tekstowe1_Zmień ()Jeśli Len (TextBox1.Text) > 20 WtedyMsgBox "Nazwa jest ograniczona do 20 znaków", vbCriticalPole tekstowe1.Tekst = ""Zakończ, jeśliNapis końcowy |
Kliknij Wydarzenie
Możesz użyć tego zdarzenia, aby wykonać akcję od użytkownika klikającego kontrolki w formularzu, a nawet sam formularz
Na tym formularzu znajduje się przycisk „OK”, a po zebraniu nazwy firmy chcielibyśmy umieścić ją w komórce w arkuszu kalkulacyjnym do wykorzystania w przyszłości
| 1234 | Private Sub CommandButton1_Click ()ActiveSheet.Range("A1"). Wartość = Pole tekstowe1.TekstJa.UkryjNapis końcowy |
Ten kod działa, gdy użytkownik kliknie przycisk „OK”. Umieszcza wartość w polu wprowadzania nazwy firmy w komórce A1 w aktywnym arkuszu, a następnie ukrywa formularz, dzięki czemu kontrola użytkownika jest zwracana z powrotem do arkusza.
Wydarzenia na wykresie
Zdarzenia wykresu działają tylko na wykresach znajdujących się w osobnym arkuszu wykresu, a nie na wykresie włączonym do standardowego arkusza
Zdarzenia wykresów są nieco ograniczone i nie można ich używać w arkuszu, w którym możesz mieć wiele wykresów. Ponadto użytkownicy niekoniecznie chcą przełączyć się z arkusza roboczego zawierającego liczby na arkusz wykresu - nie ma tu natychmiastowego efektu wizualnego
Najbardziej przydatnym zdarzeniem byłoby znalezienie elementu wykresu, na który kliknął użytkownik, np. segment na wykresie kołowym lub słupek na wykresie słupkowym, ale nie jest to zdarzenie dostępne w standardowym zakresie zdarzeń.
Ten problem można rozwiązać, używając modułu klasy, aby dodać zdarzenie „Naciśnięcie myszy”, które zwróci szczegóły komponentu wykresu, w który kliknął użytkownik. Jest to używane na wykresie w arkuszu.
Wiąże się to z bardzo skomplikowanym kodowaniem, ale wyniki są spektakularne. Możesz tworzyć zestawienia m.in. użytkownik klika segment wykresu kołowego i natychmiast ten wykres jest ukryty, a na jego miejscu pojawia się drugi wykres, przedstawiający szczegółowy wykres kołowy dla oryginalnego segmentu, lub można wygenerować dane tabelaryczne wspierające ten segment wykresu kołowego.
Zdarzenia aplikacji
Możesz użyć obiektu Application w VBA, aby odpalić kod zgodnie z konkretnym zdarzeniem
Programowanie VBA | Generator kodu działa dla Ciebie!
Application.OnTime
Dzięki temu możesz uruchamiać fragment kodu w regularnych odstępach czasu, dopóki skoroszyt jest ładowany do programu Excel. Możesz chcieć automatycznie zapisywać skoroszyt w innym folderze co 10 minut lub pozostawić arkusz działający na noc, aby pobrać najnowsze dane z zewnętrznego źródła.
W tym przykładzie podprogram jest wprowadzany do modułu. Wyświetla okno komunikatu co 5 minut, chociaż może to być inna zakodowana procedura. Jednocześnie resetuje timer na aktualny czas plus 5 minut więcej.
Za każdym razem, gdy jest uruchamiany, timer resetuje się, aby uruchomić tę samą podprogram w ciągu kolejnych 5 minut.
| 1234 | Czas podtestu()MsgBox "Testowanie na czas"Application.OnTime (Now () + TimeValue("0:05:00")), "TestOnTime"Napis końcowy |
Application.OnKey
Ta funkcja umożliwia zaprojektowanie własnych skrótów klawiszowych. Możesz sprawić, by dowolna kombinacja klawiszy wywołała podprogram swojego dzieła.
W tym przykładzie litera „a” jest przekierowywana, aby zamiast umieszczać „a” w komórce, wyświetliło się okno komunikatu. Ten kod należy umieścić we wstawionym module.
| 123456 | Sub TestKeyPress()Application.OnKey "a", "TestKeyPress"Napis końcowySub TestKeyPress()MsgBox "Nacisnąłeś 'a'"Napis końcowy |
Przede wszystkim uruchamiasz podprogram „TestKeyPress”. Wystarczy uruchomić to tylko raz. Mówi Excelowi, że za każdym razem, gdy zostanie naciśnięta litera „a”, wywoła podprogram „TestKeyPress”. Procedura podrzędna „TestKeyPress” wyświetla tylko okno komunikatu z informacją, że nacisnąłeś klawisz „a”. Może oczywiście załadować formularz lub zrobić wiele innych rzeczy.
Możesz użyć dowolnej kombinacji klawiszy, której możesz użyć z funkcją „Wyślij klawisze”
Aby anulować tę funkcję, uruchamiasz instrukcję „OnKey” bez parametru „Procedure”.
| 123 | Sub CancelOnKey()Application.OnKey „a”Napis końcowy |
Wszystko wróciło do normy.
