Nagrywanie Makr

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: VBA.xlsm tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji

Przykłady dla wszystkich lekcji szkolenia Excel 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

Wiele rozwiązań z wykorzystaniem makr można używać nawet bez znajomości poleceń i struktury języka programowania Visual Basic for Applications.

Pozwala na to opcja nagrywania makr, którą omówimy na podstawie trzech poniższych przykładów.

Przed rozpoczęciem pracy z makrami musimy udostępnić kartę poleceń ‘DEWELOPER’, na której znajdują się niezbędne nam polecenia.

Po kliknięciu Menu ‘PLIK’ wybieramy ‘Opcje’.

Na karcie ‘Dostosowywanie Wstążki’ zaznaczamy kartę ‘Deweloper’.

Po kliknięciu OK karta ‘DEWELOPER’ zostaje wyświetlona na końcu Wstążki, będzie widoczna przy kolejnych uruchomieniach Excela.

Musimy jeszcze zdecydować się na odpowiedni poziom zabezpieczeń.

Na karcie ‘Developer’ wybieramy polecenie ‘Bezpieczeństwo makr’.

Na karcie ‘Ustawienia makr’ mamy do wyboru 4 opcje.

Sugeruje rozważenie 2 możliwości:

‘Włącz wszystkie makra’, opcja niezalecana przez Microsoft ale z powodzeniem stosowana przez wielu użytkowników.

Wszystkie makra są uruchamiane, jest to bardzo wygodne i śmiało może być używane o ile użytkownicy uruchamiają wyłącznie pliki z makrami, które sami utworzyli lub zostały utworzone przez ich współpracowników, nie ponoszą więc ryzyka uruchomienia niebezpiecznego kodu.

Mimo wielu lat pracy nie spotkałem się jeszcze z niebezpiecznym kodem zapisanym w VBA w Excelu.

‘Wyłącz wszystkie makra i wyświetl powiadomienie’ – Excel każdorazowo wyświetli informacje o tym, że makra zostały wyłączone i pozwoli je nam włączyć, opcja niezalecana przeze mnie, od setnego zapytania wzwyż korzystanie z niej może mieć niekorzystny wpływ na system nerwowy użytkownika.

Jeżeli zdecydujemy się na ‘Wyłącz wszystkie makra i wyświetl powiadomienie’, po uruchomieniu pliku zawierającego makra zostanie wyświetlony pasek pokazany na poniższym rysunku.

Możemy wybrać ‘Włącz zawartość’ i makra będą mogły być użytkowane.

Przykład 1.

(Arkusz: ‘Nagrywanie Makr 1’)

W poniższym przykładzie użytkownik Excela bardzo często zmienia format liczb tak, aby były bez miejsc dziesiętnych i używały separatora (spacji), co trzy cyfry. Aby to zrobić musi każdorazowo zaznaczyć obszar, dla którego chce wprowadzić taki format, kliknąć wewnątrz niego prawym przyciskiem myszy i w oknie ‘Formatuj Komórki’ wybrać format liczbowy, zmniejszyć ilość cyfr po przecinku do zera oraz zaznaczyć opcję Użyj Separatora.

Użytkownik ten zamiast wielokrotnie powtarzać te same czynności preferuje poświęcić swój czas na ciekawsze rzeczy, postanowił więc zautomatyzować tą pracę używając makra.

Jako aktywną komórkę ustawiamy jedną z komórek zawierających liczby wymagające sformatowania (np. C4).

Z karty ‘Deweloper’ wybieramy polecenie ‘Zarejestruj makro’.

Wyświetlone zostanie okno ‘Rejestrowanie makra’.

Wprowadzamy nazwę makra np.: ‘format_liczb’ (w nazwie nie może być znaku spacji ani znaków specjalnych czyli ? ‘ / etc.)

Wprowadzamy klawisz skrótu np. ‘e’ oraz

Wybieramy opcję przechowywania makra w ‘Skoroszycie makr osobistych’

Wciskamy ‘OK’

W dolnym lewym rogu ekranu zostanie wyświetlona ikona umożliwiająca zatrzymanie rejestrowania makra, tak długo jak jest ona wyświetlana wiemy, że makro jest nagrywane.

Formatujemy liczbę w komórce C4 - bez miejsc dziesiętnych, z użyciem separatora 1000 (formatujemy komórkę, dokładnie tak samo jak zrobilibyśmy to, gdyby nie trwało nagrywanie makr).

Nie klikamy nic innego i nie zaznaczamy żadnych komórek, nie klikamy także na komórce C4, przed ani po formatowaniu, ponieważ wszystko co zrobimy zostanie nagrane.

Bezpośrednio po kliknięciu ‘OK.’ w oknie ‘Formatowanie komórek’, zatrzymujemy rejestrowanie klikając ikonę w dolnym lewym rogu lub na karcie ‘DEWELOPER’.

Sprawdzamy czy nasze pierwsze makro działa.

Stajemy w komórce C5 i wciskamy ‘Ctrl’+e i format liczb zmienia się od razu na właściwy. Makro działa prawidłowo.

Klawisz skrótu ‘Ctrl+e’ wprowadziliśmy w oknie Rejestruj makro. Klawisz skrótu jest opcjonalny i nie musi być wybierany dla każdego naszego makra.

Używanie go przy makrach często używanych znacznie usprawnia i przyspiesza pracę.

Jeśli chodzi o wybór litery to równie dobrze mogłaby to być inna litera, sugeruję jednak nie używać skrótów już zarezerwowanych przez Excela czyli np. Ctrl+c (kopiuj), Ctrl+v (wklej), Ctrl+x (wytnij), Ctrl+s (zapisz), Ctrl+p (drukuj). Gdybyśmy użyli którejś z tych liter jej oryginalna funkcjonalność przestałaby działać.

Makro zadziała prawidłowo nie tylko dla pojedynczej komórki, ale także dla dowolnego obszaru komórek. Proponuje zaznaczyć na raz wszystkie dane w obu tabelach arkusza ‘VBA 1’ i wcisnąć ‘Ctrl’+e

Jeżeli zdecydujemy się nie używać klawisza skrótu, makro możemy uruchamiać poprzez klikanie ikony, do której makro to przypiszemy, jest to bardzo wygodne szczególnie, że możemy dobrać dla każdego z makr ikonę, która będzie się nam kojarzyć z tym makrem.

Jak przypisać makro do ikony i jak dodać własne ikony dowiemy się z lekcji ‘Pasek Narzędzi Szybki Dostęp’.

Trzecim sposobem na uruchomienie nagranego makra jest wybranie z karty: ‘DEWELOPER’ polecenia ‘Makra’.

Wyświetlone zostanie okno ‘Makro’, w którym wybieramy makro, jakie chcemy uruchomić.

(Lista dostępnych makr będzie inna u każdego użytkownika.)

Podczas nagrywania makra wybraliśmy opcję Przechowuj makro w ‘Skoroszyt makr osobistych’, dzięki temu makro to będzie dostępne zawsze gdy uruchomimy Excela.

Gdybyśmy wybrali opcję ‘Ten skoroszyt’ makro będzie dostępne tylko wtedy, kiedy plik, w którym się znajduje jest otwarty.

Przykład 2.

(Arkusz: ‘Nagrywanie Makr 2)

Kompletując dane dość często musimy używać opcji transpozycja i wklej wartości.

W poniższym przykładzie dane z 4 poziomych tabel należy zgromadzić w 1 pionowej tabeli. Ponieważ dość często i dla różnych tabel wykonujemy takie operacje łatwiej będzie utworzyć makro, którym będziemy się mogli posługiwać.

Postępujemy analogicznie do tego jak nagrywaliśmy makro w przykładzie pierwszym.

Ponieważ nasze makro ma tylko wklejać dane jako wartości z wykorzystaniem opcji transpozycja, musimy przed rozpoczęciem nagrywania makra:

1. Zaznaczyć obszar C19:N19 i skopiować go (Ctrl+C)

2. Jako aktywną komórkę wybrać C3

Dopiero po tych czynnościach kliknąć polecenie ‘Zarejestruj makro’.

Proponuje nazwę transpozycja, klawisz skrótu q, oraz zachowanie w Skoroszycie makr osobistych.

Klikamy komórkę C3 prawym klawiszem, wybieramy polecenie ‘Wklej specjalne…’ i w oknie ‘Wklejanie specjalne’ zaznaczamy ‘Wartości’ i ‘Transpozycja’.

Bezpośrednio po kliknięciu OK zatrzymujemy rejestrowanie makra.

Sprawdzamy jego działanie dla rosyjskich danych, po czym sprawdzamy działanie dla danych z Ukrainy i Węgier naraz, zaznaczając oba obszary, kopiując je i ustawiając aktywną komórkę w E3.

Makro jest na tyle uniwersalne że będzie działać dla dowolnego obszaru również złożonego z kilku zakresów.

Przykład 3.

(Arkusz: ‘Nagrywanie Makr 2’)

Dość często zachodzi konieczność zastąpienia formuł w arkuszu Excela wartościami, które aktualnie przyjmują.

Dzięki takiemu zabiegowi Excel będzie pracował znacznie szybciej.

Postanowiliśmy zautomatyzować tą operację nagrywając makro, które będzie ją wykonywać.

Przeprowadzamy następujące czynności:

1. Zaznaczamy obszar, na którym chcemy zamienić formuły na wartości, proponuję wybrać komórkę C15, klikamy ‘Zarejestruj makro’

2. Wprowadzamy nazwę makra (bez polskich liter) i opcjonalnie klawisz skrótu.

3. Kopiujemy wybraną wcześniej komórkę (np. wciskając Ctrl+C)

4. Klikamy tą samą komórkę prawym klawiszem myszki i wybieramy ikonę z ‘123’ która wklei w to miejsce wartości.

5. Wciskamy klawisz ‘Esc’ (aby zakończyć proces kopiowania)

6. Zatrzymujemy rejestrowanie makra.

7. Sprawdzamy działanie naszego makra na formule w komórce D15, a następnie na komórkach E15 i F15 naraz.

Ważne jest, aby podczas nagrywania makra nie zaznaczać komórek ani nie wybierać innych komórek, nawet wtedy gdybyśmy ostatecznie wrócili na to samo miejsce.

Wszystkie operacje przeprowadzone przez nas podczas nagrywania zostaną zapamiętane i w niektórych przypadkach mogą uniemożliwić wykonanie makra, a naszym celem jest przygotowanie makra, które będzie jak najbardziej uniwersalne.

We wszystkich powyższych przykładach przygotowaliśmy bardzo użyteczne makra, nawet bez obejrzenia tego, jak wygląda ich kod źródłowy.

Aby przygotować jeszcze bardziej użyteczne i wyspecjalizowane makra w kolejnych lekcjach zapoznamy się z tym, jak wprowadzać zmiany w kodzie VBA zarejestrowanym podczas nagrywania.