Dziś maleństwo, które może samo w sobie nie jest niczym szczególnym, ale powinno trochę pomóc w zrozumieniu modelu zdarzeń VBA.
Kod VBA wpisany do modułu można uruchomić na jeden z trzech sposobów:
- Bezpośrednio z edytora VBA, ustawiając się wewnątrz procedury, którą chcemy uruchomić, i naciskając F5
- Poprzez wywołanie za pośrednictwem funkcji arkusza.
- Poprzez obsługę zdarzenia.
O ile pierwsze dwa sposoby są intuicyjnie zrozumiałe, o tyle trzeci często sprawia trudność początkującym programistom - zwłaszcza tym, którzy przesiadają się z jakiegoś starożytnego, nieobiektowego i nie-zdarzeniowego języka programowania.
Zdarzeniem może być dowolne... no, zdarzenie 😉 zachodzące w aplikacji. Aplikacją w naszym przypadku jest Excel, i są w nim zdefiniowane setki (jeżeli nie tysiące) różnych rodzajów zdarzeń.
Przykłady zdarzeń:
- kliknięcie w komórkę arkusza,
- zmiana wartości komórki
- przełączenie do innego arkusza
- zmiana szerokości kolumny
- przeliczenie arkusza
- zmiana rozmiaru okna Excela
- otwarcie skoroszytu
Każde takie zdarzenie - o ile tylko zostało to przewidziane i dozwolone przez twórców Excela - daje się oprogramować własnym kodem VBA. Dziś pokażę jednolinijkową pchełkę, która obsługuje takie pojedyncze zdarzenie arkusza.
Jak wiadomo, podwójne kliknięcie w obszar pomiędzy nagłówkami kolumn arkusza, powoduje automatyczne dopasowanie szerokości kolumny znajdującej się po lewej stronie od klikanego obszaru, do zawartości "najdłuższej" komórki w tej kolumnie. Podobnie, ale bardziej "lokalnie", działa kombinacja klawiszy Alt-O-C-A - dopasowuje ona szerokość kolumny do zawartości "najdłuższej" komórki w zaznaczeniu (zaznaczenie nie musi obejmować całej kolumny).
Dzisiejsza pchełka zautomatyzuje ten proces powodując, że każdorazowe wpisanie dowolnej wartości do dowolnej komórki arkusza, spowoduje automatyczne dopasowanie szerokości kolumny, w której ta komórka się znajduje, w taki sam sposób, jakbyśmy kliknęli dwa razy myszą w obszar pomiędzy nagłówkami kolumn.
Pamiętajmy, że takie podejście może drastycznie spowolnić pracę z arkuszem w przypadku, gdy ilość danych będzie bardzo duża. Każdorazowe dopasowanie szerokości kolumny wymaga bowiem przeskanowania całej kolumny z danymi.
OK, do dzieła.
Dziś wyjątkowo nie zaczniemy od wstawienia nowego modułu - wykorzystamy istniejący moduł arkusza, który domyślnie istnieje dla każdego arkusza w skoroszycie.
Przełączamy się do edytora VBA (Alt-F11), klikamy dwa razy w Sheet1 (na drzewku po lewej stronie).
Z listy obiektów na górze (tej która domyślnie pokazuje (General)), wybieramy Worksheet.
W tym momencie edytor VBA wstawia nam pustą procedurę zdarzenia SelectionChange - kasujemy ją, ponieważ interesuje nas inne zdarzenie.
Z listy zdarzeń na górze po prawej wybieramy Change. Edytor VBA wstawił nam teraz pustą obsługę zdarzenia Change.
Pomiędzy linijkami Private Sub... i End Sub wpisujemy:
Target.EntireColumn.AutoFit
Cały kod powinien więc teraz wyglądać następująco:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireColumn.AutoFit
End Sub
Gotowe!
Przełączamy się na arkusz i wpisujemy kilka losowych wartości do różnych komórek arkusza. Faktycznie, widzimy, że za każdym razem szerokość kolumny dopasowuje się do zawartości.
Teraz część wyjaśniająca.
Private Sub Worksheet_Change(ByVal Target As Range)
To jest nagłówek procedury obsługującej zdarzenie. Treść tego nagłówka jest ściśle zdefiniowana i nie należy jej zmieniać. W tym przypadku mamy do czynienia ze zdarzeniem Change obiektu Worksheet, które jest generowane za każdym razem, kiedy zmienia się wartość komórki (bądź komórek) w tym arkuszu. Zauważmy przy tym, że zmiana wartości nie musi pochodzić z interfejsu użytkownika - zdarzenie zostanie wygenerowane również wtedy, gdy zmienimy wartość komórki za pomocą kodu VBA.
Do procedury obsługującej zdarzenie jest przekazywana informacja o tym, które konkretnie komórki to zdarzenie wygenerowały (innymi słowy, które komórki zostały zmienione). Informacja ta to parametr Target typu Range.
Target.EntireColumn.AutoFit
Tu odbywa się właściwa obsługa zdarzenia, czyli dopasowanie (.AutoFit) całej kolumny (.EntireColumn), w której znajduje się zmieniana komórka (Target). Zauważmy, że działa to niezależnie od ilości jednocześnie zmienionych komórek - właściwość EntireColumn zwróci tyle kolumn ile trzeba, a następnie dla każdej z nich wykona się metoda AutoFit, dopasowując w efekcie każdą zmienioną kolumnę.
End Sub
Kończymy obsługę zdarzenia i przekazujemy sterowanie do miejsca, w którym została ona wywołana.
Proste?
No pewnie, że proste...
Działa tylko do aktualnie zmienianej komórki, ale w innej kolumnie, w jednej z komórek jest odwołanie do komórki, którą właśnie wypełniliśmy i ta już zmianie nie uległa. Jest na to sposób ?
Faktycznie, kolumna się nie dopasowuje do komórek zmienianych za pomocą formuły. Aczkolwiek wystarczy w taką komórkę kliknąć i wtedy się dopasuje.
Jeżeli klikanie nie wchodzi w grę, trzeba by napisać jakąś pętlę, która leci po wszystkich kolumnach z danymi. Postaram się na dniach dorzucić kolejną pchełkę pod temat.
Szybki przykład jak zrobić, żeby kolumny z komórkami zależnymi też się dopasowały:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Excel.Range
Target.EntireColumn.AutoFit
For Each r In Target.Dependents
r.EntireColumn.AutoFit
Next r
End Sub