Głównym tematem tej lekcji będzie komunikacja VBA z arkuszem Excela, czyli pobieranie danych znajdujących się w arkuszu oraz wyświetlanie danych do arkusza. Dowiemy się też jak w wstawić w kodzie komentarz, jak uruchamić makro oraz nauczymy się jak korzystać przy pisaniu makr z doskonałego udogodnienia, jakim są autopodpowiedzi.

Lekcja została podzielona na następujące części:

• Wypisywanie danych w komórkach Excela
• Wstawianie komentarzy w kodzie VBA
• Przenoszenie kodu VBA do nowej linii
• Korzystanie z autopodpowiedzi
• Uruchamianie makr

Stwórz nowy moduł VBA i wklej do niego poniższy kod. Pod ramką z kodem znajdziesz jego szczegółową analizę.

Functions"> Polecenia, które mają być wykonane przez język VBA, nie mogą być tak po prostu wpisane w edytorze VBA. Język VBA (podobnie zresztą jak większość innych języków programowania) wymaga, aby wszystkie polecenia zawierały się w procedurach (Sub) lub funkcjach (Function).

Jest to zorganizowane w ten sposób co najmniej z dwóch powodów:

• Zachowanie porządku i przejrzystości w kodzie
• Oddzielenie od siebie poszczególnych zadań

Załóżmy, że chcesz, aby Twoje makro wykonywało kilka czynności: wypisywanie czegoś do arkusza Excela, kolorowanie jego komórek, tworzenie ramki itp. Do każdego z tych zadań chciałbyś potem przypisać oddzielny skrót klawiaturowy, który to zadanie uruchamia.

Gdyby cały kod został wpisany do edytora VBA jednym ciągiem, przy każdym uruchomieniu makra musiałyby się wykonywać wszystkie te zadania, ponieważ kompilator VBA nie miałby pojęcia, gdzie kończy się jedno zadanie, a zaczyna drugie.

Dzięki temu, że każde z tych zadań znajdzie się w oddzielnej procedurze, kompilator od razu wie, które wiersze kodu są odpowiedzialne za wykonanie poszczególnych zadań.

Konstrukcja tego makra wyglądałaby więc tak, jak pokazano poniżej:

1
2
3


Sub wypisywanieWartosciWKomorkach
    '(...)
End Sub

1
2
3


Sub kolorowanieKomorek
    '(...)
End Sub

Dzięki takiemu układowi kodu, każde zadanie jest oddzielone od innych i może być wykonane w  oderwaniu od pozostałych. Poza tym, jak już wcześniej wspomniano w punkcie pierwszym, kod jest przejrzysty i uporządkowany.

Tworzenie kodu trzeba rozpocząć od zapisania wiersza otwarcia procedury (lub funkcji):

 


Sub procedureName

Jeżeli wpiszesz w edytorze VBA powyższy wiersz i wciśniesz Enter, zauważysz, że poniżej automatycznie zostanie wstawiony wiersz oznaczający zakończenie danej procedury:

 


End Sub

W miejsce nazwaProcedury należy wpisać nazwę opisującą zadanie wykonywane przez tę procedurę.

Przy nadawaniu nazw elementom języka VBA, takim jak procedury, funkcje czy zmienne, obowiązuje kilka reguł, które zostały zestawione w poniższej tabelce:

Reguły obowiązujące przy nazywaniu procedur i funkcji

• Nazwa procedury nie może zawierać spacji ani żadnych znaków specjalnych oprócz podkreślenia _
• Nazwa musi rozpoczynać się literą.
• Nazwa nie powinna zawierać polskich znaków diaktrycznych (ani jakichkolwiek innych), jedynie litery alfabetu łacińskiego.
• Nazwa nie może być taka sama jak któreś ze słów kluczowych VBA - na razie poznałeś tylko słowa kluczowe Sub, Function i End, jednak z każdą kolejną lekcją będziesz poznawać ich więcej.
• Nazwa powinna dokładnie opisywać zadanie wykonywane przez procedurę, tak aby wracając do kodu napisanego na przykład rok wcześniej, na pierwszy rzut oka było wiadomo za co ta procedura jest odpowiedzialna.
• Wielkość liter w nazwach nie ma znaczenia, dlatego przykładowo nazwa nazwaProcedury jest dla kompilatora identyczna z nazwą NAZWAPROCEDURY.
• Przyjęło się, by nazwy procedur zapisywać małymi literami, a wielkimi literami rozpoczynać tylko poszczególne wyrazy w tej nazwie, np. NazwaProcedury (ewentualnie nazwaProcedury) zamiast nazwaprocedury lub NAZWAPROCEDURY.
• Nazwa procedury może liczyć maksymalnie 255 znaków, ale nikt o zdrowych zmysłach nie nadaje tak długich nazw, gdyż uciążliwe byłoby późniejsze korzystanie z nich.

Reasumując - wszystkie zadania, jakie mają zostać wykonane przez VBA, powinny być od siebie oddzielone.

Każde zadanie powinno znaleźć się w oddzielnej procedurze lub funkcji, które są ograniczone następującymi poleceniami:

1
2
3


Sub nazwaProcedury            'początek procedury
    '(...)
End Sub                       'koniec procedury


1
2
3


Function nazwaFunkcji         'początek funkcji
    '(...)
End Function                  'koniec funkcji


W kilku pierwszych lekcjach będziemy korzystać tylko z procedur (Sub). Temat funkcji zostanie poruszony w szóstej lekcji i wtedy też poznasz różnice pomiędzy procedurami a funkcjami.

Wróćmy do analizy przedstawionego wcześniej kodu. W drugiej linijce znajduje się następująca instrukcja:

2

Worksheets("Sheet1").Cells(1, 1) = 1

Kompilator otrzymuje tutaj trzy istotne informacje:

• nazwa arkusza, w którym mają być wypisane dane - w tym przypadku wybrany został arkusz o nazwie Arkusz1,
• komórka, w której mają być wypisane dane - w tym przypadku wybrana została komórka znajdującą się w pierwszym wierszu i w pierwszej kolumnie (oczywiście w wybranym wcześniej arkuszu Arkusz1),
• po znaku równości określony został tekst lub liczba, która zostanie wyświetlona w wybranej wcześniej komórce (czyli w naszym przypadku w komórce A1 arkusza Arkusz1).

Ogólna postać wstawiania wartości do komórek Excela wygląda więc następująco:

 

Worksheets("nazwaArkusza").Cells(wiersz, kolumna) = tekst

lub w wersji skróconej:

 

Cells(wiersz, kolumna) = tekst

Jeżeli skorzystasz z drugiego sposobu, w którym pominięte zostało odniesienia do nazwy arkusza, wartość zostanie wyświetlona w aktywnym arkuszu.

W kolejnych wierszach omawianego kodu znajdują się następne przykłady korzystające z powyższej konstrukcji:

3 
4 
5 


Worksheets("Arkusz1").Cells(2, 1) = 2
Worksheets("Arkusz1").Cells(3, 1) = 4
Worksheets("Arkusz1").Cells(4, 1) = 8

Wykonanie powyższych trzech linijek kodu spowoduje, że w komórkach A2, A3 i A4 arkusza Arkusz1 wyświetlone zostaną kolejno liczby 2, 4 i 8.

W ostatniej linijce kodu znajduje się polecenie, które poznaliśmy już wcześniej:

12

End Sub

Jak już wcześniej wspomniano, oznacza ono zakończenie danej procedury.

Procedura obejmuje tylko to, co znajduje się pomiędzy poleceniami Sub nazwaProcedury a End Sub .

Być może zauważyłeś, że w kilku przytoczonych wcześniej przykładach, część kodu była poprzedzona apostrofem i wyświetlona zieloną czcionką. Oznacza to, że te fragmenty kodu są komentarzami.

 

'To jest komentarz


Komentarze nie są brane pod uwagę przy wykonywaniu programu, więc można w nich umieścić dowolny tekst.

Komentarze spełniają dwie zasadnicze funkcje:

• pozwalają szczegółowo opisywać bardziej skomplikowane części kodu, tak by osoba czytająca kod szybko i łatwo zrozumiała za co jest on odpowiedzialny i dlaczego jest napisany w taki, a nie inny sposób. Nawet jeśli nie zamierzasz nikomu udostępniać swojego kodu, komentowanie trudniejszych fragmentów jest dobrą praktyką, bo bardzo często zdarza się, że nawet sam autor kodu, wracając do niego po długim czasie, nie rozumie dokładnie wszystkich jego zawiłości
• pozwalają łatwo analizować różne wersje kodu - załóżmy, że masz już napisaną jakąś bardzo długą procedurę i chcesz sprawdzić zachowałaby się ta procedura, gdybyś usunął z niej dwie linijki kodu. Wcale nie musisz usuwać tych dwóch linijek i zapisywać ich sobie w innym miejscu na wypadek, gdyby jednak okazały się przydatne. Wystarczy, że wstawisz na początku tych linijek znak apostrofu - wówczas staną się one komentarzem (o czym będzie świadczył zielony kolor ich czcionki) i będą zupełnie niewidoczne dla kompilatora. Jeżeli potem stwierdziłbyś, że te dwie linijki kodu są jednak niezbędne, wystarczy, że usuniesz poprzedzające je apostrofy i staną się one z powrotem pełnoprawną częścią kodu.

Często zdarza się, że któryś wiersz kodu jest tak długi, że nie mieści się na ekranie. Przykład takiej sytuacji przedstawiono na poniższym rysunku:

Oczywiście zawsze można w takiej sytuacji przewijać ekran poziomym paskiem przewijania znajdującym się na dole edytora kodu (na rysunku zaznaczony na czerwono).

Znacznie lepszym pomysłem jest jednak rozbicie zbyt długiej linijki kodu na kilka krótszych, tak aby cały kod był widoczny bez konieczności jego przewijania.

Nie możesz jednak zrobić tego w dowolny sposób, na przykład tak:

1
2
3


Worksheets("Arkusz1").Cells(1, 1) =
   "Bardzo długi wpis w linii kodu, który nie mieści się na ekranie"
    'Nieprawidłowe rozbicie długiej linijki kodu

W takim zapisie kompilator traktuje oba wiersze jako zupełnie od siebie niezależne, a żaden z nich samodzielnie nie spełnia podstawowych wymogów poprawności. Już podczas pisania kodu edytor podświetli je na czerwono, a próba uruchomienia takiego makra zakończy się niepowodzeniem.

Dlaczego są one niepoprawne?
W górnej linijce znajduje się operacja przypisania, jednak po znaku równości nie podano wartości, jaka ma zostać przypisana. W dolnej natomiast znajduje się tylko czysty tekst w cudzysłowie, który nie jest przypisywany ani wyświetlany - jednym słowem nic się z nim nie dzieje. Linijka kodu nie może zawierać jedynie samego tekstu!

Aby powyższy zapis był poprawny, trzeba więc w jakiś sposób poinformować kompilator, że oba te wiersze są w rzeczywistości jednym poleceniem, które zostało po prostu rozbite na dwa krótsze dla zwiększenia czytelności.

Operatorem odpowiedzialnym za to zadanie jest kombinacja spacji i podkreślenia ( _).

Wystarczy w miejscu, w którym ma zostać rozbity zbyt długi wiersz, wstawić spację i podkreślenie, a następnie przejść do nowej linii i kontynuować pisanie danego polecenia.

Napotkanie przez kompilator kombinacji znaków ( _) jest dla niego sygnałem, że kolejna linia kodu będzie kontynuacją tej, w której teraz się znajduje, i że obie stanowią razem jedno polecenie.

Na poniższym rysunku widać omawianą linię kodu, rozbitą na dwie krótsze, które, dzięki zastosowaniu operatora przeniesienia, nadal stanowią jedno polecenie.

Rozbicie długiego wiersza kodu nie musi ograniczać się do dwóch krótszych linijek - równie dobrze można rozbijać wiersze na większą liczbę linii.

Z technicznego punktu widzenia poniższy zapis też byłby całkowicie poprawny (chociaż w przedstawionej sytuacji rozbijanie kodu na tak dużą liczbę któtszych linii nie przynosi żadnych korzyści):

Wpisując kod w edytorze VBA, możesz znacznie zwiększyć tempo tworzenia kodu poprzez wykorzystanie oferowanego przez edytor VBA mechanizmu Intellisense, czyli autopodpowiedzi.

Aby przekonać się, jak działa usługa autopodpowiedzi, dopisz do analizowanego podczas tej lekcji kodu (nie przeklejaj, tylko dopisz ręcznie!) taki oto wiersz:

 

Worksheets("Arkusz2").Cells(6, 1) = 243

Po napisaniu dwóch pierwszych liter nie kontynuuj jednak wpisywania, a zamiast tego wciśnij kombinację klawiszy Ctrl + Spacja lub Ctrl + J (znacznie wygodniejsza w użyciu jest pierwsza opcja, wykorzystująca spację).

Jak widzisz na swoim ekranie oraz na powyższym rysunku, edytor pokazał listę dostępnych komend rozpoczynających się tak samo, jak wpisany już przez Ciebie fragment tekstu (czyli w tym przypadku wo).
Teraz wystarczy tylko wybrać strzałkami na klawiaturze odpowiednią komendę i kliknąć Spację lub Tab (można też zatwierdzić wybór Enterem, ale wówczas edytor przeskoczy do następnej linijki kodu, więc stosowanie Entera jest mniej wygodne).

Zauważ, że na liście podpowiedzi, wyświetlonej przez edytor, znajduje się też procedura, stworzona na początku tej lekcji, czyli wypisywaniePoteg.

Nadeszła pora na uruchomienie pierwszego stworzonego przez Ciebie makra.

Istnieje kilka metod uruchamiania makr. Zostały one przedstawione na poniższej liście (nie wszystkie jednak zostaną teraz szczegółowo omówione):

• Kliknięcie klawisza F5 w edytorze VBA

  Aby uruchomić konkretne makro za pomocą tego sposobu, musisz ustawić kursor w edytorze VBA gdzieś wewnątrz kodu tego makra (czyli gdzieś pomiędzy linijką Sub nazwaFunkcji a linijką End Sub ).

  Jeżeli wciśniesz klawisz F5 podczas, gdy kursor będzie się znajdował w pustej linijce, nie należącej do żadnej procedury, wyświetli się lista wszystkich dostępnych makr, z której będziesz musiał wybrać interesujące Ciebie makro.

• Kliknięcie na pasku narzędzi edytora VBA ikony uruchamiania makra

  Drugim sposobem uruchomienia makra jest kliknięcie na pasku narzędzi edytora VBA ikony uruchamiania makra, zaznaczonej na poniższym rysunku na czerwono

  

  Obowiązują identyczne zasady jak w poprzednim podpunkcie - aby uruchomić konkretne makro należy przed wciśnięciem tej ikony ustawić się w jego kodzie.

  Jeżeli przy wciśnięciu ikony kursor znajduje się na obszarze nie należącym do żadnego konkretnego makra, zostanie wyświetlona lista wszystkich dostępnych makr.

• Uruchomienie makra z paska menu Excela

  Makra można również uruchamiać z poziomu Excela.

  W tym celu należy wybrać z paska menu następujące polecenia:

  • Narzędzia
  • Makro
  • Makra ...

  

  Spowoduje to wyświetlenie okna z listą wszystkich dostęnych makr, spośród których należy wybrać makro, które ma zostać uruchomione.

  Ten sposób uruchamiania makr jest przeznaczony raczej dla ich końcowych użytkowników, którzy powinni wykonywać wszystko z poziomu Excela, nie zaglądając nawet do edytora VBA.
  Podczas nauki VBA ten sposób uruchamiania makr nie jest zalecany, gdyż wymaga przechodzenia z okna edytora do Excela, zajmując tym samym zdecydowanie za dużo czasu.

• Uruchomienie makra poprzez kliknięcie ikony na pasku narzędzi Excela

  Kolejnym sposobem uruchomienia makra jest kliknięcie odpowiedniej ikony na pasku narzędzi Excela (wyświetlanie w Excelu paska narzędzi VBA zostało omówione w poprzedniej lekcji).

  Ikona, którą należy kliknąć, wygląda identycznie jak ikona, o której była mowa w drugim podpunkcie tej listy (została oznaczona na poniższym rysunku na czerwono).

  

  Po kliknięciu tej ikony na ekranie pojawi się okno z listą wszystkich dostępnych makr, z której należy wybrać makro do uruchomienia.

  Podobnie jak w przypadku poprzedniego podpunktu, ten sposób uruchamiania jest przeznaczony raczej dla końcowych użytkowników makra i nie powinieneś z niego korzystać podczas nauki programowania w VBA.

• Przypisanie do makra skrótu klawiaturowego

  Aby przypisać do makra skrót klawiaturowy, trzeba wywołać z poziomu Excela okno z listą wszystkich dostępnych makr.

  W dwóch poprzednich podpunktach opisane zostały sytuacje, w których takie okno się pojawia. Dla przypomnienia są to: kliknięcie ikony uruchomienia makra na pasku narzędzi Visual Basic lub wybór polecenia

  • Narzędzia
  • Makro
  • Makra ...

  na pasku menu Excela.

  Lista makr, o której tutaj mowa, wygląda tak jak na poniższym rysunku. Aby dopisać do któregoś z makr skrót klawiaturowy, należy zaznaczyć go na liście i kliknąć przycisk Opcje... (zaznaczony na rysunku na czerwono).

  
  

  Spowoduje to wyświetlenie okna opcji makra, takiego jak przedstawiono na kolejnym obrazku. W miejscu zaznaczonym czerwoną ramką znajduje się pole, w którym możesz przypisać dla danego makra skrót klawiszowy.

  
  

  Możliwe jest również przypisanie skrótu klawiaturowego do makra dynamicznie w trakcie jego wykonywania, co zostanie omówione w dalszej części kursu.

• Przypisanie do makra zdarzenia, które je wywołuje

  W dalszej części kursu nauczysz się wywoływania makra poprzez przypisanie do niego zdarzenia. Nauczysz się na przykład, jak sprawić aby makro uruchamiało się po dwukrotnym kliknięciu myszą w komórce A1 albo przy otwieraniu pliku (możliwości jest tu nieskończenie wiele).

• Dodanie nowego elementu na pasku menu Excela

  Podczas kolejnych lekcji nauczysz się również dodawania własnych opcji menu do paska menu Excela, które będą zawierały polecenia wywołujące Twoje makra, tak jak to wygląda na poniższym obrazku.

  

• Uruchamianie makra z okna Immediate

  Na razie nie omawialiśmy jeszcze nawet czym jest i do czego służy okno Immediate, dlatego nie będziemy też się na tym etapie zagłębiać w szczegóły uruchamiania makra za pomocą tej metody. Do tego tematu powrócimy jednak w jednej z kolejnych lekcji.