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 2007: ExcelSzkolenie.pl Cwiczenia Excel 2007.zip

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 przykładów omówionych w tej lekcji.

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

Po kliknięciu przycisku ‘Microsoft Office’ wybieramy ‘Opcje programu Excel’.

Na karcie ‘Popularne’ zaznczamy opcję ‘Pokaż kartę Deweloper na Wstążce’.

Karta ‘Deweloper’ zostaje wyświetlona końcu Wstążki, będzie widoczna przy kolejnych uruchomieniach Excela.

Musimy jeszcze zdecydować się an 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 prze 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.

‘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ż kożystanie 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.

Po kliknięciu przycisku ‘Opcje…’ na tym pasku...

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

Przykład 1.

(Arkusz: ‘VBA 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.

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

Wyświetlone zostanie okno ‘Rejestruj makro’.

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ć 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: ‘Zmiany w Menu i Własne Ikony.

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 byłoby dostępne tylko wtedy, kiedy plik, w którym się znajduje jest otwarty.

Przykład 2.

(Arkusz: ‘VBA 1a’)

Kompletując dane dość często musimy używać opcji transpozycja + 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 mogli posługiwać się za każdym razem.

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 więc:

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

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

3. 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 specjalnie…’ i w oknie ‘Wklejanie specjalnie’ 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 na raz, 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: ‘VBA 1a’)

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, proponuje wybrać komórkę C15, klikamy ‘Zarejestruj makro’

2. Wprowadzamy nazwę makra 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 opcję ‘Wklej Specjalnie’

5. Zaznaczamy ‘Wartości’ i klikamy ‘OK’

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

7. Zatrzymujemy rejestrowanie makra.

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

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 kolejnej lekcji zapoznamy się z tym, jak wprowadzać zmiany kodzie VBA zarejestrowanym podczas nagrywania.