Walczymy z makrami część 4 (ostatnia)

Każdy z nas powinien dbać o formę. W natłoku zdarzeń czasem trudno jest nam na to znaleźć czas, ale warto się starać. Dziś porozmawiamy o formach i zdarzeniach w VBA. Zapraszam do „czwartej i ostatniej części trylogii” o makrach w Excelu ;)

Na końcu części trzeciej napisaliśmy prostą funkcję tworzącą log wejść na nasz nowy raport. Nie wiem,czy pamiętasz, ale umieściliśmy ją w module Ten_skoroszyt, w makrze Workbook_Open() .

Makro Workbook_Open odpowiada za obsługę otwierania skoroszytu. Zobaczmy zatem edytor makr i sprawdźmy, na jakie inne zdarzenia możemy zareagować.

W oknie eksploratora Projektu VBA, klikamy podwójnie na obiekt "ten_skoroszyt" i zmieniamy (general) na workbook w polu wyboru. Po prawej mamy listę dostępnych opcji. Aby obsłużyć jakieś zdarzenie wybieramy je z listy, a następnie wpisujemy nasz kod w wygenerowany „szkielet makra”. W liście dla zeszytu mamy np. makro before save ( przed zapisem )

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)    
End Sub

zwróć uwagę na parametry makra SaveAsUI mówi nam czy zostanie wyświetlone okno „zapisz jako” ,a Cancel mówi nam czy zapisywanie zostanie anulowane. Zapewne dostrzegłeś także słowo BYVAL , oznacza ono, że zmienna SaveASUI została przekazana przez wartość i jej ewentualna zmiana na nic nie wpłynie(zagadnienie przekazywania zmiennych do funkcji wykracza poza ten wpis. Obiecałem się streszczać :) ) Cancel nie ma takiego przedrostka i możemy zmienić jego wartość. Zróbmy to!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)    
	cancel = true
End Sub

i zapiszmy naszą pracę..... ups. Nie da się! Przed zapisaniem do programu zostaje przekazana wartość zmiennej Cancel(anuluj) i zapis zostaje anulowany. My powinniśmy móc dokonać zmian, sprawmy więc, żeby nikt , poza naszym użytkownikiem, nie mógł zapisać tego pliku.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not Application.UserName = "molexor" Then       ' wpisz własny username 
            Cancel = True
    End If
End Sub

teraz żaden użytkownik nie zapisze tego pliku ( chyba że wyłączy obsługę makr).

Poeksperymentuj troszkę ze zdarzeniami, jest ich naprawdę sporo, od otwarcia pliku , po zmianę selekcji arkuszy, czy przeliczanie zeszytu. Warto się z nimi zapoznać.

Przykładowe praktyczne zastosowanie: raport, o którym mówiliśmy w poprzedniej części, jest bardzo rzadko odwiedzany. Jego uzupełnienie nie trwa długo i także zautomatyzowaliśmy je makrem, ale ponieważ raport jest mało istotny, nie pamiętamy o jego uzupełnianiu. Wykorzystując potęgę makr jesteśmy w stanie zaradzić temu problemowi. Kiedy szef otwiera raport, wywoływane jest makro uzupełniające dane, które samo sprawdza,czy raport jest uzupełniony i w razie konieczności naprawia to karygodne zaniedbanie. Wszyscy są szczęśliwi.

Zdarzenia zeszytu nie są jedyni na które możemy reagować. Klikając podwójnie w eksploratorze projektu w dowolny arkusz i wybierając pozycję Worksheet w polu wyboru , zobaczymy listę dostępnych opcji dla arkusza. Zajmiemy się tylko jednym dla przykładu. Będzie to :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
End Sub

Zmienna target to miejsce/komórka kliknięcia, a za pomocą zmiennej Cancel możemy powstrzymać program od domyślnego działania, którym jest w tym wypadku rozpoczęcie edycji komórki.

Weźmy przykładową tabelkę z ocenami końcowymi uczniów ( liczby są losowe)

Chciałbym, żeby po podwójnym kliknięciu na ocenie pokazało nam się okienko z ocenami ucznia z przedmiotu, który wskazaliśmy klikając. Dzięki obsłudze zdarzeń jesteśmy w stanie zrealizować coś takiego w bardzo prosty sposób

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row > 2 And Target.Row < 10 And Target.Column > 2 And Target.Column < 8 Then
            oceny = pobierzoceny(Cells(2, Target.Column).Value, Cells(Target.Row, 2).Value)
            strocen = "Oceny z przedmiotu: " & Cells(2, Target.Column).Value & Chr(10) & _
                     "Ucznia: " & Cells(Target.Row, 2).Value & Chr(10) & "To: "
            For Each el In oceny
                strocen = strocen & el & " , "
            Next el
            
            MsgBox strocen
            Cancel = True
    End If
End Sub

Function pobierzoceny(przedmiot, nazwiskoImie)
'zrezygnowałem z jakiejkolwiek logiki w funkcji. trzeba ją dopisać
    pobierzoceny = Array(1, 2, 3, 4, 5, 6)
End Function

za pomocą rozciągniętego „ifa” określiliśmy dla których kolumn i rzędów będziemy działać. Zmienna target ma typ Range, czyli z angielskiego zakres, stąd właściwości row i column( wiersz i kolumna) funkcja pobierzocenmy powinna wyglądać nieco inaczej, jednak na potrzeby naszego przykładu wystarczy taki mały „implant” .

Podobnie obsługuję się zdarzenia w formach i kontrolkach ActiveX. Po dodaniu takiej kontrolki AX na arkusz, w edytorze będziemy mieli do wyboru zdarzenia z nią związane (do wyboru obok worksheet).

Proponuję żebyś drogi czytelniku dodał sobie kilka kontrolek i spróbował je obsłużyć . Wprowadzenie kontrolek ActiveX pozwoli ci na tworzenie już całkiem ciekawych aplikacji w Excelu. Pamiętaj że kontrolki takie jak combobox należałoby w którymś momencie zainicjalizować listą wyboru. Realizacja zależy już od danej aplikacji.

I w ten oto sposób mamy już wszystkie elementy jakie potrzebne będą do poznania form. Formy to oczywiście okienka przygotowane przez użytkownika. Zawierają one kontrolki , które przed chwileczką nauczyłeś się obsługiwać. Stwórzmy sobie zatem jakieś okienko ( Insert->UserForm) edytor form VBA jest wizualny (WYSIWYG) , więc poukładanie kontrolek nie będzie na pewno problemem( przeciągnij upuść). Poprzez menu ( View->Code) przełączamy się pomiędzy kodem, a edytorem. Jak widzisz, znów mamy dostępne zdarzenia, na które możemy reagować. Na pewno interesująca nas opcja to UserForm_Initialize , wywoływane, gdy forma jest tworzona. Pozwala to odpowiednio inicjalizować formę i wszystkie jej kontrolki. Zróbmy sobie prostą formę z obrazkiem klepsydry i z napisem proszę czekać. Przyda nam się ona, kiedy będziemy chcieli, żeby użytkownik wiedział,że np. dane są przeliczane, ściągane, czy uzupełniane. Jeśli taką formę mamy to z dużym prawdopodobieństwem będziemy chcieli ją pokazać ;) Jak to zrobić? Możemy bezpośrednio korzystać z Formy jak z obiektu statycznego( jeśli nie wiesz o co chodzi to nie szkodzi) ,czy li w kodzie wywołać np.:

UserForm1.Show

lub też możemy stworzyć obiekty klasy Formy w znany nam z poprzedniej części sposób

set obj = new  UserForm1
obj.show

Funkcja show przyjmuje jeden tylko argument. Jeśli do funkcji przekażemy wartość fałsz to okienko nie zablokuje dalszego wykonywania makra. Jeśli natomiast przekażemy wartość prawda, lub nie przekażemy wartości, to okno zablokuje dalsze wykonywanie makra aż do schowania formy. Aby ukryć okienko korzystamy z metody hide. Formy blokujące działanie makra są najczęściej wykorzystywane gdy oczekujemy wyboru, lub akcji od użytkownika, natomiast typowym zastosowaniem form nieblokujących są różnego rodzaju paski postępu, paski stanu i tym podobne rzeczy. Przy tworzeniu form i zmianach ich właściwości w kodzie należy pamiętać o metodzie repaint, która odświeża zawartość formy. Bez tej metody wspomniane już paski postępu nie zmieniałyby się. To co zrobisz z formami , zależy tylko od twojej wyobraźni. Jeśli będziesz pisał w VBA, z pewnością formy jakich użyjesz także będą się rozwijać. Pamiętaj tylko ,żeby nie był to przerost formy nad treścią ;)

Walczymy z Makrami - epilog

To tyle. Doszliśmy do końca serii "Walczymy z Makrami" .W czterech krótkich częściach starałem się przybliżyć główne zagadnienia związane z edycją i pisaniem Makr. Miał to być wstęp do zagadnienia i zajawka dla chcących się uczyć. Mam nadzieję,że zachęciłem was do pogłębiania swojej wiedzy z zakresu VBA. W internecie mona znaleźć naprawdę wiele wspaniałych materiałów, o wiele lepiej wyczerpujących omawiane tutaj zagadnienia. Wiem, że seria nie emocjonowała tak,jak inne wpisy, ale mam nadzieję,że nie nudziliście się bardzo. Dziękuję za wszystkie pozytywne komentarze, jakie znalazły sie pod poprzednimi częściami.

Do zobaczenia i powodzenia z Makrami!