W drugim odcinku naszych Przygód z VBA Dla Początkujących napiszemy maleńki, jednolinijkowy żarcik.
Najpierw jednak krótki wstęp na temat zabezpieczeń Excela przeciwko wirusom napisanym w VBA oraz dlaczego przeszkadza nam to w nauce.
Jak ogólnie wiadomo, Homo Sapiens Sapiens ma tę smutną cechę, że każdą bez wyjątku ideę potrafi obrócić w broń przeciwko innemu Homo Sapiens Sapiens. Kamień do tłuczenia mięsa równie dobrze nadaje się do tłuczenia czaszek wrogów. Laser, nadający się świetnie do transmisji danych tudzież schładzania substancji do zera absolutnego (na poziomie kwantowym - cóż za możliwości dla naukowców!), służy jednak w pierwszej kolejności do zestrzeliwania satelitów i rakiet. Które to satelity i rakiety... Wiadomo. Łapiecie to, prawda?
Podobnie jest z dowolną technologią komputerową. Flash, PDF, Java, JS, VBScript, VBA - i z setka innych tu nie wymienionych technologii - wszystkie na jakimś tam etapie rozwoju były (a niektóre nadal są) używane do wciskania ludziom wirusów.
Na zasadzie tarczy i miecza, firmy dostarczające tych technologii starają się zabezpieczyć Kowalskiego przed złośliwym kodem - co ma oczywiście swoje skutki uboczne. W przypadku VBA na ten przykład, domyślnie wykonanie kodu jest wyłączone. Otwieramy plik XLS z kodem VBA i nic się nie dzieje.
W poprzednich wersjach Office (od 97 do 2003) włączenie kodu VBA odbywało się za pomocą zmiany ustawień bezpieczeństwa. Począwszy od wersji 2007, Małomiękki dorzucił jeszcze jedną warstwę zabezpieczeń w postaci nowego rozszerzenia nazwy plików. Żeby móc przechowywać kod VBA, trzeba teraz zapisać arkusz w pliku z rozszerzeniem .XLSM. Dzięki temu jeszcze przed otwarciem dokumentu widać w Exploratorze inną (od standardowej XLS) ikonkę. Do tego trzeba jeszcze włączyć kod VBA w opcjach zabezpieczeń - teraz pokażę jak to zrobić.
Niestety nie mam wersji innej niż 2007 więc jeżeli Czytelnik ma 2003 lub niżej, polecam poguglać za "Enable VBA Excel" czy coś w ten deseń.
Uruchamiamy Excela. Klikamy w menu File (czyli duży okrągły przycisk w górnym lewym rogu aplikacji), potem Trust Center, Trust Center Settings, Macro Settings, zaznaczamy ostatnią z czterech opcji: Enable all macros. Ignorujemy ostrzeżenie w nawiasie. Do tego jeszcze zaznaczamy opcję poniżej, w sekcji Developer Macro Settings. Gotowe.
Od tej pory wszystkie pliki Excela z kodem VBA będą się otwierały bez zadawania żadnych głupich pytań, a wykonywanie VBA będzie domyślnie włączone. Jeżeli ktoś jest przewrażliwiony, może zaraz po wykonaniu dzisiejszego ćwiczenia przywrócić ustawienia oryginalne. Można też pokombinować z wygenerowaniem podpisu cyfrowego dla lokalnych projektów VBA, dzięki czemu nasz własny kod będzie się wykonywał bez mrygnięcia okiem, a obcy już nie. Zainteresowanych tematem odsyłam do Google / dokumentacji Excela.
Tymczasem, żarcik.
Zamierzamy stworzyć dokument Excela, który będzie się otwierał tylko w parzyste minuty. Próba otwarcia go w minucie nieparzystej nie uda się - plik po prostu nie otworzy się.
Podobnie jak w poprzednim ćwiczeniu, przydatność takiego kodu z biznesowego punktu jest przyzerowa (i to od strony ujemnej). Jednak ma ono niewątpliwy walor edukacyjny, a więc do dzieła.
Najpierw tworzymy nowy dokument XLSM - czyli zapisujemy pusty arkusz jako Excel Macro-enabled Workbook. Dzięki temu będziemy w stanie zachować kod VBA razem z plikiem. Ja nazwałem plik "parzyste-minuty.xlsm".
Następnie uruchamiamy edytor VBA (Alt-F11). W drzewku po lewej stronie klikamy dwa razy w "ThisWorkbook" - to jest specjalny moduł przeznaczony do pisania kodu dla całego skoroszytu (a nie dla poszczególnych arkuszy). Ponieważ otwarcie dokumentu jest zdarzeniem właściwym dla skoroszytu a nie arkusza, właśnie tutaj napiszemy nasz żarcik.
Przy okazji, przez wiele lat denerwowała mnie dwoistość nazewnictwa w Excelu. Dokument to skoroszyt. A arkusz to ... no, arkusz, zakładka w dokumencie. Pardon, w skoroszycie. W VBA arkusz to Sheet (albo Worksheet), a skoroszyt to Workbook. Uporządkowało mi się to dopiero po wyemigrowaniu do kraju angielskojęzycznego. Warto zapamiętać te angielskie nazwy bo będą się często pojawiały w kodzie.
OK, wracamy do meritum. W kodzie skoroszytu (czyli w module ThisWorkbook), wybieramy z górnej lewej listy obiekt Workbook, następnie upewniamy się, że na liście po prawej wybrane jest zdarzenie Open.
W kodzie powinny się w tym momencie pojawić dwie linie kodu:
Private Sub Workbook_Open()
End Sub
To jest (na razie pusta) obsługa zdarzenia Open obiektu Workbook. Czyli, na nasze, kod, który wykona się po otwarciu naszego skoroszytu.
Ponieważ jest to pchełka, wpisujemy tylko jedną linię:
Private Sub Workbook_Open()
If Minute(Now) Mod 2 = 1 Then ThisWorkbook.Close False
End Sub
I gotowe! Zapisujemy skoroszyt. Zamykamy. Próbujemy otworzyć. Jeżeli aktualna minuta jest nieparzysta (na przykład 13:47 albo 22:11), dokument nie otworzy się (a tak naprawdę, otworzy się na bardzo krótki czas i natychmiast się zamknie). Jeżeli natomiast bieżąca minuta jest parzysta (np 17:28 albo 14:00), dokument otworzy się jakby nigdy nic.
Wyjaśnienie kodu poniżej:
If - to słówko kluczowe VBA otwiera warunkowy blok kodu. Warunkowy, czyli taki, który wykona się pod jakimś warunkiem. Warunek jest zapisany zaraz po słowie If, aż do słowa Then. Po słowie Then pojawia się kod, który ma być wykonany jeżeli warunek jest prawdziwy. Dodatkowo, blok If-Then może jeszcze zawierać blok Else, który wykona się jeżeli warunek jest fałszywy.
Warunkiem jest instrukcja Minute(Now) Mod 2 = 1. Minute to funkcja VBA zwracająca numer minuty z czasu podanego jako parametr. Now to funkcja systemowa zwracająca bieżący czas. A więc Minute(Now) zawsze zwróci liczbę całkowitą z przedziału 0-59. Kilka przykładów (można je sobie wykonać w okienku Immediate - Control-G):
? Minute(Now)
? Minute(#11 Feb 2045 12:39:21#)
? Minute(#11:01#)
Pierwszy przykład zwróci bieżącą minutę. Drugi zwróci 39. Trzeci zwróci 1 (nie 01 tylko 1 - zwracana wartość jest liczbą całkowitą).
Mod to funkcja matematyczna modulo. A więc, zwraca resztę z dzielenia.
Ostatni element wymagający wytłumaczenia to: ThisWorkbook.Close False
ThisWorkbook to jest obiekt VBA reprezentujący skoroszyt, w którym znajduje się aktualnie wykonywany kod VBA. Oprócz tego istnieje jeszcze obiekt ActiveWorkbook, który jest obecnie aktywnym, otwartym skoroszytem. Zauważmy, że w przypadku kiedy otwarty jest tylko jeden dokument, ThisWorkbook oraz ActiveWorkbook to ten sam obiekt. Jeżeli jednak otworzymy za pomocą naszego kodu jakiś inny dokument, dokument ten stanie się aktywny, a więc będzie to ActiveWorkbook. Może brzmi to trochę zawile, ale jest całkiem proste jak się dobrze zastanowić.
No dobra. ThisWorkbook.Close
Tutaj wywołujemy metodę Close obiektu ThisWorkbook. Czyli, na nasze, zamykamy dokument.
Parametr False na samym końcu mówi, czy podczas zamknięcia dokumentu Excel ma zapytać o zapisanie zmian czy nie. Ponieważ żadne zmiany nie zostały wprowadzone (dokument jest właśnie otwierany), równie dobrze moglibyśmy wpisać tam True (pytanie zostaje zadane wyłącznie jeżeli faktycznie coś było zmienione w dokumencie).
Zagadka na koniec: co zrobić, żeby dokument otwierał się tylko w minuty nieparzyste?
a czy If nie musi mieć swojego enda?
A propos zagadki to mam kolejną anegdotę studencką: oświeciłem ich że sprawdzanie parzystych to właśnie x mod 2 = 0 i dałem analogiczną zagadkę. Jeden student, łysy z wyboru, chciał zabłysnąć nie tylko łysiną więc się wyrwał z odpowiedzią dość niepewną bo w formie pytającej: "x mod 3 = 0?" Aby go nie zniechęcić do podejmowania arcytrudnych procesów myślowych to podpowiedziałem, że prawie (!) dobrze tylko trzeba kombinować w nieco innym kierunku z tym właśnie warunkiem w ifie. No to on wysilił swój moduł zaawansowanej abstrakcji i analogii i po chwili rzucił triumfalne "x mod 0 = 0!"
If musi mieć swojego Enda o ile blok warunkowy jest wielolinijkowy (a w szczególności, jednolinijkowy, ale w linijce poniżej If-a)
Jeżeli blok warunkowy jest w tej samej linijce co If (pojedyncza instrukcja), wówczas End jest zbędny a wręcz niewskazany 😉