Pchełki VBA – odcinek 1: początki

https://xpil.eu/0ei1v 📋

Zauważyłem ostatnio, że kategoria "Branżowe" mojego bloga kuleje okrutnie. Postanowiłem w związku z tym rozpocząć serię pchełek VBA. Co prawda zawodowo nie używam VBA już od dość dawna, jednak ponieważ używałem go przez większość swojego życia zawodowego (i to od samego początku, kiedy tylko pojawiła się pierwsza wersja MS Office wyposażona w tę technologię), a także ponieważ uważam VBA za jedno z najbardziej elastycznych i wszechstronnych narzędzi programistycznych w każdym biurze, podzielę się swoją Mroczną Wiedzą z wszystkimi moimi trzema Czytelnikami.

Ta seria ma być z założenia przeznaczona dla tych, którzy znają dość dobrze Excela, oraz - być może - mają jakieś tam podstawowe pojęcie o programowaniu. Ponieważ będą to pchełki (a więc niewielkie fragmenty kodu pokazujące jedną, konkretną funkcjonalność, bądź też rozwiązujące jeden, konkretny problem), mam nadzieję, że będą pomocne każdemu kto chce zacząć naukę VBA a nie bardzo wie jak lub gdzie.

Aha, jeszcze jedno. VBA to nie tylko Excel ale też cały Office. Tutaj jednak ograniczę się wyłącznie do Excela; od czasu do czasu - być może - wrzucę również jakieś przykłady dotyczące Accessa.

Dzisiejszy odcinek będzie może niezbyt praktyczny z biurowego punktu widzenia, jednak zahaczy o kilka najbardziej podstawowych pojęć i zagadnień.

Zadanie

Napisać kod VBA, który na bieżąco wpisuje do komórki A1 adres aktualnie zaznaczonej komórki (bądź komórek) bieżącego arkusza.

Rozwiązanie

1. Włączamy Excela
2. Uruchamiamy edytor kodu VBA (Alt-F11)
3. Wstawiamy nowy moduł: w drzewku po lewej stronie (tam gdzie widać "Sheet1", "Sheet2"... a na końcu "ThisWorkbook") klikamy prawym myszem, wybieramy Insert => Module. Pojawia się nowy moduł nazwany Module1. Po prawej stronie pokazuje się okno edycji tego modułu.
4. W oknie edycji Module1 wpisujemy:

Option Explicit

Bardzo możliwe, że Option Explicit zostało już wpisane na początek modułu w momencie jego tworzenia. Warto sobie na stałe włączyć tę opcję, żeby nie musieć tego wpisywać do każdego nowego modułu. Opcja znajduje się w menu Tools => Options i nazywa się "Require Variable Declaration" - ma być włączona na stałe.

Opcja Option Explicit powoduje, że nie będziemy mogli użyć zmiennej dopóki jej nie zadeklarujemy. Co to znaczy? Na razie nie powiem, żeby nie rozwlekać. Wyjaśnię przy najbliższej sposobności na przykładzie.

OK, jedziemy dalej.

5. Poniżej Option Explicit wpisujemy następujący kod:

Public Sub PokazAdresZaznaczenia(dokad As Excel.Range)
 Dim adres As String
 adres = Selection.Address
 dokad.Value = adres
End Sub

Teraz objaśnienie:
Sub to nic innego jak blok kodu ("procedura"), który można wywołać za pomocą zdefiniowanej po słowie Sub nazwy. W naszym przypadku nazwą jest PokazAdresZaznaczenia.
Słówko Public, poprzedzające Sub, mówi, że procedura ta jest publicznie dostępna (a więc można ją wywołać z każdego miejsca, również z innych modułów kodu). Jeżeli Public zamienilibyśmy na Private, procedurę można by było wołać wyłącznie z bieżącego modułu. Jeżeli zupełnie pominiemy słówko Public, procedura będzie domyślnie publiczna, o ile znajduje się w osobnym module kodu (tak jak tutaj), ale jeżeli wpiszemy ją do modułu arkusza, będzie domyślnie prywatna.

dokad As Excel.Range - to jest pierwszy (i jedyny) parametr procedury. Czym jest parametr nie będę tłumaczył - każdy wie jak policzyć sumę albo średnią w Excelu, tam też się używa parametrów. Jedyna różnica jest taka, że w VBA oprócz nazwy parametru dobrze jest jeszcze podać jego typ, po słówku As. W naszym przypadku parametr dokad jest typu Excel.Range.

Typ Excel.Range (można też ryzykować i używać samego Range, ale polecam zawsze poprzedzać typy nazwą biblioteki z kropką) to w zasadzie najważniejszy z wielu dostępnych typów danych w VBA. Jest to typ złożony, a więc reprezentuje pewną strukturę a nie - w odróżnieniu od typów prostych - pojedynczą wartość (przykładem typu prostego jest np. Integer - całkowitoliczbowy - albo String - tekstowy).

Zmienna typu Excel.Range reprezentuje zakres komórek w arkuszu. W szczególnym przypadku może to być pojedyncza komórka, ale może to też być prostokątny obszar komórek bądź nawet wiele takich obszarów.

W przypadku naszej procedury PokazAdresZaznaczenia parametr dokad określa miejsce docelowe, do którego będzie wpisany adres aktualnie zaznaczonej komórki. A więc, jeżeli wywołamy procedurę PokazAdresZaznaczenia z parametrem, dajmy na to, Range("A1"), to efekt będzie taki, że do komórki A1 bieżącego arkusza zostanie wpisany adres aktualnie zaznaczonego zakresu komórek.

Kolejna linijka naszego kodu to:

Dim adres As String

Tutaj deklarujemy zmienną o nazwie adres, typu String. Oznacza to, że rezerwujemy sobie miejsce w pamięci, określamy, że miejsce to będzie przechowywać tekst oraz nazywamy je adres. Od tej pory możemy do takiej zmiennej "wkładać" dowolne wartości tekstowe. Na ogół dobrym pomysłem jest nazywać zmienne w taki sposób, żeby było wiadomo co konkretnie planujemy w nich przechowywać. W zmiennej adres zamierzamy za chwilę przechować adres aktualnie zaznaczonego zakresu komórek, stąd nazwa.

Patrzymy dalej:

adres = Selection.Address

Tutaj umieszczamy w naszej - dopiero co utworzonej - zmiennej, adres aktualnie zaznaczonego obszaru. Selection to jedna z wielu zmiennych globalnych Excela. Jest ona typu Excel.Range i reprezentuje aktualnie zaznaczone komórki. Ponieważ jest ona typu Excel.Range, ma wiele właściwości i metod (o tym kiedy indziej) właściwych dla tego typu. Jedną z takich właściwości jest właśnie Address - czyli adres danego obszaru. Przy okazji warto wiedzieć, że adres jest zawsze przechowywany w postaci bezwzględnej, czyli "z dolarkami". Czym się różni adres "D3" od adresu "$D$3" nie będę tłumaczył, proszę sobie poszukać. Aha, coś co jest oczywiste, ale i tak chcę to zaznaczyć: właściwość Address obiektu typu Excel.Range jest tylko do odczytu. Nie możemy z wnętrza naszego kodu VBA zmodyfikować bezpośrednio adresu zaznaczenia. Jedyny sposób na zmianę adresu zaznaczenia to wybranie innego zakresu komórek (bądź bezpośrednio na arkuszu, bądź też za pomocą VBA).

OK, lecimy dalej. Ostatnia linijka naszej procedury to:

dokad.Value = adres

Tutaj modyfikujemy pole Value naszego zakresu docelowego, umieszczając w nim pozyskany w poprzedniej linijce adres aktualnie zaznaczonego zakresu. Value jest kolejną właściwością (albo kolejnym "polem") zmiennej typu Excel.Range; reprezentuje ona wartość komórki (bądź komórek) reprezentowanej przez tą zmienną.

No i wreszcie na koniec:

End Sub

Koniec procedury. W momencie napotkania End Sub wykonanie kodu wraca do miejsca, w którym procedura była wywołana.

Czy to już koniec? Nie, jeszcze nie. Trzeba jeszcze tą procedurę "podwiesić" pod jakieś zdarzenie arkusza, bo na chwilę obecną mamy tylko kod, który jednak nie jest przez nic wywoływany. Zanim jednak to zrobimy, dobrze byłoby upewnić się, że procedura działa poprawnie.

W tym celu musimy wywołać procedurę. Najprościej można to zrobić przechodząc do okna poleceń bezpośrednich ("Immediate"). Wybieramy więc z menu View => Immediate Window - zamiast klikać w menu można też użyć kombinacji Control-G. Następnie, w okienku Immediate, wpisujemy:

PokazAdresZaznaczenia Range("A1")

I pukamy Enter. Zaglądamy teraz do arkusza... I rzeczywiście, w komórce A1 pokazał się adres aktualnie zaznaczonej komórki. Ponieważ domyślnie zaznaczoną komórką jest, dla odmiany, komórka A1, widzimy tam adres $A$1. Sprawdźmy, co stanie się jak zaznaczymy inną komórkę. Klikamy w komórkę, dajmy na to, C3, wracamy do edytora VBA, w oknie Immediate przechodzimy do linii, w której wywołujemy procedurę i znów pukamy Enter. Zaglądamy w arkusz... Bingo. W komórce A1 pokazał się teraz adres $C$3. Jeszcze tylko ostatni test i możemy przystąpić do finalizacji. Ostatnim testem jest sprawdzenie, czy procedura zadziała dla innego obszaru docelowego (a więc, czy adres może być wpisany gdzie indziej niż do A1). Zmieniamy PokazAdresZaznaczenia Range("A1") na PokazAdresZaznaczenia Range("D7"), pukamy Enter, zaglądamy do arkusza... i widzimy w komóce D7 (poprzednio pustej) adres aktualnie zaznaczonej komórki. Działa!

Ostatnim krokiem będzie "podczepienie" naszej procedury pod jakieś zdarzenie arkusza. Po co? No po to, żeby po każdorazowej zmianie zaznaczenia, zaktualizować adres tego zaznaczenia. Dziś tylko pokażę jak to zrobić, bez wnikania w teorię. W którymś z kolejnych odcinków opowiem trochę więcej o zdarzeniach.

OK, jedziemy:

6. W oknie VBA, w drzewku obiektów po prawej stronie, klikamy dwa razy w Sheet1 (w polskiej wersji Excela to będzie chyba Arkusz1, nie jestem pewien). W efekcie otwiera się okno edycji modułu arkusza Sheet1. Na górze, tuż powyżej okna z kodem, znajdują się dwie listy rozwijane. Z listy po lewej wybieramy Worksheet. Z listy po prawej wybieramy następnie zdarzenie SelectionChange (prawdopodobnie będzie ono już wybrane domyślnie - upewniamy się więc tylko, czy tak faktycznie jest). W efekcie zostanie wygenerowana pusta procedura obsługi zdarzenia SelectionChange, o taka:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

W treści tej procedury (a więc w pustej linijce pomiędzy Sub... a End Sub) wpisujemy wywołanie naszej procedury:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PokazAdresZaznaczenia Range("A1")
End Sub

I dzieło gotowe! Przełączamy się teraz do arkusza i na próbę zaznaczamy kilka losowo wybranych komórek. Obserwujemy co dzieje się w komórce A1. Faktycznie, każda zmiana zaznaczenia skutkuje wpisaniem do komórki A1 adresu nowego zaznaczenia. Spróbujmy zaznaczyć obszar prostokątny. Spróbujmy zaznaczyć kilka obszarów, używając myszy i klawisza Ctrl. Jak widać, za każdym razem poprawny adres dowolnie skomplikowanego zaznaczenia zostaje wpisany do komórki A1.

Proste?

Dla starych wyjadaczy VBA to jest wczesny żłobek. Jednak dla początkujących takie zabawy adresami to świetny wstęp do nauki. W każdym razie jak tak zaczynałem 😉

Jeszcze dwie uwagi na zakończenie:

1. Nie ma potrzeby tworzyć za każdym razem osobnej procedury, żeby obsłużyć zdarzenia arkusza. W naszym przypadku, zamiast pisać procedurę PokazAdresZaznaczenia i potem ją wołać w procedurze zdarzenia, można od razu:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim adres As String
 adres = Selection.Address
 Range("A1").Value = adres
End Sub

2. Zmienna adres jest tak naprawdę zbędna - wprowadziłem ją w celu pokazania, że istnieje coś takiego jak zmienne i z czym to jeść. W naszym przypadku jednak możnaby kod jeszcze uprościć:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("A1").Value = Selection.Address
End Sub

To by było na tyle.

Jeżeli starczy mi rozpędu i zapału, za jakiś czas powinny pojawić się tutaj kolejne odcinki.

https://xpil.eu/0ei1v 📋