Pchełki VBA – Odcinek 6: między arkuszami

In Pchełki VBA by xpil0 Comments

Dziś rano Google skierowało na mojego bloga jakiegoś człowieka, który próbował wykombinować jak to zrobić, żeby kliknięcie w komórkę spowodowało przełączenie do innego arkusza. Dokładna fraza, jaką ów człowiek wpisał do wyszukiwarki, brzmiała:

otwarcie innego arkusza po kliknięciu w komórkę

Jakkolwiek wątpię, że ów delikwent kiedykolwiek powróci na mojego bloga (zapewne podczas dzisiejszej przypadkowej wizyty zasnął z nudów i w efekcie wywalili go z pracy), uznałem, że temat jest na tyle prosty, że można zeń wyrzeźbić kolejną pchełkę. Co też niniejszym czynię.

Żeby pchełkę uczynić ciekawszą, doszczegółowię ją w następujący sposób:

Zbudować arkusz, który po każdorazowym kliknięciu dowolnej komórki odczyta tekst z tej komórki a następnie przełączy do arkusza odpowiadającego nazwą temu tekstowi – o ile rzecz jasna taki arkusz istnieje.

Zaczynamy od – niespodzianka! – uruchomienia Excela.

Przełączamy się do edytora VBA (Alf-F11… wspominałem już, że Alt-F11 przełącza do edytora VBA?)

Wstawiamy nowy moduł: prawomysz na drzewku po lewej, Insert => Module

Wpisujemy procedurkę:

Public Sub PrzelaczArkusz(ByVal nazwa As String)
 On Error Resume Next
 ThisWorkbook.Sheets(nazwa).Activate
 On Error GoTo 0
End Sub

Teraz przełączamy się do modułu arkusza Sheet1 i tam wpisujemy:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count = 1 Then
    PrzelaczArkusz Target.Value
  End If
End Sub

I gotowe! Sprawdźmy czy działa. Najpierw wpiszmy do pierwszego arkusza, w losowo wybranych komórkach, różne teksty. Niech niektóre z nich są takie same jak nazwy innych arkuszy. Na koniec spróbujmy kliknąć jedną z komórek zawierających nazwę innego arkusza – okazuje się, że faktycznie zostaliśmy przełączeni do tego arkusza. A więc – działa!

Teraz tradycyjnie odrobina wyjaśnień odnośnie kodu VBA:

On Error Resume Next

Tu musimy na chwilę odejść od wątku głównego i pokrótce wspomnieć o obsłudze wyjątków. Wyjątkiem nazywamy sytuację, w której w trakcie wykonywania kodu następuje błąd. Na przykład, ktoś próbuje dzielić przez zero, albo ustawić datę na 30 lutego, albo usunąć nieistniejący plik. W momencie wystąpienia wyjątku, program na ogół wyrzuca komunikat błędu po czym przerywa pracę. Można jednakowoż taki wyjątek obsłużyć. Czyli powiedzieć aplikacji jak ma się zachować w przypadku wystąpienia wyjątku.

W porządnych językach programowania (ostatnio nawet w SQL) obsługa wyjątków jest zrobiona dość solidnie i działa wyśmienicie. W VBA nie jest pod tym względem różowo, ale jest wystarczająco dobrze, żeby sobie poradzić z większością znanych wyjątków.

W naszym przypadku, wyjątkiem jest sytuacja, w której próbujemy się przełączyć do arkusza o nieistniejącej nazwie. Siłą rzeczy, próba taka skończyć się powinna komunikatem błędu – jednak ponieważ wiemy z góry, że zdecydowana większość klikanych komórek nie zawiera nazw arkuszy, świadomie obsłużymy ten błąd.

W VBA są dwa sposoby na obsłużenie błędu. Pierwszy to:

On Error Goto etykieta

a drugi to:

On Error Resume Next

Wariant z Goto przeniesie wykonywanie programu do bloku rozpoczynającego się etykietą. Wariant z Resume Next nakazuje aplikacji zignorowanie błędu i wykonanie kolejnej linii kodu.

Obydwa powyższe podejścia do obsługi błędów powinny zjeżyć włos na głowie większości programistów, ponieważ są koszmarnie nieeleganckie, nieefektywne i – przy bardziej skomplikowanych projektach programistycznych – wyjątkowo błędogenne.

W naszym przypadku jednak mamy do czynienia z pchełką, a więc możemy śmiało użyć wersji z Resume Next.

ThisWorkbook.Sheets(nazwa).Activate

Tu wykonujemy próbę przełączenia na arkusz o podanej nazwie. Obiekt ThisWorkbook reprezentuje bieżący skoroszyt. Kolekcja Sheets reprezentuje wszystkie arkusze tego skoroszytu.

Element kolekcji Sheets możemy zaadresować używając jego numeru kolejnego (pierwszy arkusz ma numer 1, drugi 2 itd) bądź też nazwy arkusza w cudzysłowie.

Sheets(nazwa) zwraca arkusz (obiekt typu Worksheet) o podanej nazwie. Właśnie tutaj może nastąpić błąd, kiedy nazwa odnosi się do nieistniejącego arkusza (albo jest tekstem niedozwolonym w miejscu nazwy arkusza – na przykład zbyt długim albo zawierającym niedozwolone znaki).

Wreszcie metoda Activate obiektu Worksheet przełącza na ten arkusz.

On Error GoTo 0

Tutaj wyłączamy obsługę wyjątku. Jeżeli tego nie zrobimy, a błąd nie wystąpi, obsługa wyjątku zostanie zastosowana do następnego błędu, który może wystąpić całkiem gdzie indziej. Pamiętajmy więc: starajmy się zawsze umieszczać jak najmniej kodu między linijką On Error Resume Next a linijką On Error Goto 0. Dzięki temu sprawimy, że nasza obsługa wyjątku będzie bardziej niezawodna.

If Target.Count = 1 Then

Tutaj sprawdzamy, czy kliknięto jedną czy więcej komórek. Jeżeli kliknięto więcej niż jedną, ignorujemy kliknięcie.

PrzelaczArkusz Target.Value

Tutaj wywołujemy naszą, przed chwilą napisaną procedurę PrzelaczArkusz i przekazujemy jej parametr nazwa równy wartości klikniętej komórki.

I to w zasadzie by było na tyle… Dodam jeszcze (jeżeli ktoś tego już nie zauważył), że obsłużyliśmy tutaj zdarzenie SelectionChange ponieważ nie mamy możliwości obsługi zdarzenia Click. Stąd też wynika, że przełączenie na inny arkusz następi nie tylko po kliknięciu w komórkę, ale również po wybraniu tej komórki za pomocą klawiatury bądź kodu VBA.

Dodaj komentarz

Bądź pierwszy!

Powiadom o
avatar
wpDiscuz