Nagrywanie Makr

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

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

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

Pozwala na to opcja nagrywania makr, którą omówimy na 5 przykładach omówionych w tej lekcji.

Zanim jednak będziemy mogli wykonać jakiekolwiek operacje na makrach musimy się upewnić czy obsługa makr jest w naszym Excelu włączona.

Wybieramy z Menu → Narzędzia → Opcje i wybieramy kartę ‘Zabezpieczenia’ a potem klikamy przycisk ‘Bezpieczeństwo makr...’.

Na karcie ‘Poziom zabezpieczeń’ wybieramy opcję:

‘Niskie’ –wszystkie makra będą uruchamiane, zgodnie z opisem opcja ta jest niezalecana przez Microsoft, jednak ja bez jakichkolwiek problemów używam jen od lat, lub też

‘Średnie’ – Excel każdorazowo będzie nas pytać czy makra mają być aktywne, opcja niezalecana przeze mnie, od setnego zapytania wzwyż używanie jej może mieć niekorzystny wpływ na system nerwowy użytkownika.

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.

Z Menu ‘Narzędzia’ → ‘Makro’ wybieramy opcję ‘Zarejestruj nowe 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’

Wyświetlone zostanie małe okienko iformujące nas, że trwa proces nagrywania makra.

Formatujemy komórki, dokładnie tak samo jak zrobilibyśmy to, gdyby nie trwało nagrywanie makr.

Po zakończeniu formatowania klikamy symbol w postaci niebieskiego kwadratu, tym samym zatrzymując nagrywanie makra.

Sprawdzamy czy nasze pierwsze makro działa.

W tym samym arkuszu znajduje się inna tabela, w której także należałoby poprawić format liczb. Zaznaczamy obszar, w którym znajdują się liczby.

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).

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ć lub stworzyć dla każdego z makr ikonę, która będzie się nam kojarzyć z tym makrem.

Jak przypisać makro do ikony i jak stworzyć własne ikony dowiemy się z lekcji: Zmiany w menu i własne ikony.

Trzecim sposobem na uruchomienie nagranego makra jest wybranie z Menu: ‘Narzędzia’ → ‘Makro’ → ‘Makra...’

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

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 będzie otwarty.

Przykład 2.

(Arkusz: ‘Nagrywanie Makr 2’)

Kompletując dane dość często musimy używać opcji transpozycja i wklej wartości. Poniżej dane z 4 tabel należy zgromadzić w 1 tabeli o innym formacie. Ponieważ dość często i dla różnych tabel wykonujemy takie operacje łatwiej będzie nagrać makro i posłużyć się nim.

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, przed rozpoczęciem nagrywania musimy zaznaczyć obszar C19:N19, skopiować go i ustawić się w miejscu, w którym chcielibyśmy, aby dane się znalazły. Po czym rozpoczynamy rejestrowanie makra.

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.

2. Wybieramy z Menu: ‘Narzędzia’ → ‘Makro’ → ‘Zarejestruj nowe makro’, (lub klikamy ikonę ) wprowadzamy nazwę makra i opcjonalnie klawisz skrótu.

3. Kopiujemy wybrany wcześniej obszar (np wciskając Ctrl+C)

4. Klikamy obszar prawym klawiszem myszki i wybieramy opcję ‘Wklej Specjalnie’

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

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

Wybieramy z Menu: ‘Narzędzia’ → ‘Makro’ → ‘Zatrzymaj rejestrowanie’ bądź klikamy ikonę jeśli jest widoczna.

Sprawdzamy działanie naszego makra na innej formule.

W tym przykładzie 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.

Przykład 4.

(Arkusz: ‘Nagrywanie Makr 3’)

Autofiltr można włączyć wybierając z Menu: ‘Dane’ → ‘Filtr’ → ‘Autofiltr’ lub co jest znacznie wygodniejsze klikając ikonę

Niestety nie ma w Excelu ikony, która wyłączałaby Autofiltr.

W tym przykładzie nagramy makro, które będzie wykonywać tą operację i dodamy je do ikony, którą umieścimy na pasku ikon.

Przeprowadzamy następujące czynności:

1. Zaznaczamy nagłówki tabeli i włączamy Autofiltr.

2. Wybieramy z Menu: ‘Narzędzia’ → ‘Makro’ → ‘Zarejestruj nowe makro’, (lub klikamy ikonę ) wprowadzamy nazwę makra i opcjonalnie klawisz skrótu.

3. Wybieramy z Menu: ‘Dane’ → ‘Filtr’ → ‘Autofiltr’

4. Wybieramy z Menu: ‘Narzędzia’ → ‘Makro’ → ‘Zatrzymaj rejestrowanie’, bądź klikamy ikonę .

5. Dodajemy przycisk niestandardowy do Menu, najlepiej przy ikonie Autofiltra i rysujemy na niej symbol, który będzie się nam kojarzył z wyłączaniem Autofiltra np. taki: Więcej na temat dodawania ikon do Menu oraz przypisywania do nich makr w lekcji: Zmiany w menu i własne ikony

6. Sprawdzamy działanie naszego makra włączając filtr i wyłączając go klikając na nowo utworzoną ikonę.

Przykład 5.

(Arkusz: Nagrywanie Makr 4 )

W poniższym przykładzie użytkownik ma za zadanie zapewnienie sprawnego funkcjonowania pliku w który kilkudziesięciu innych pracowników wpisuje koszty swoich podróży służbowych.

Niestety osoby te często kopiują dane z innych plików zapominając o tym aby skopiować tylko dane bez formatowania. Zdarza się też im wyróżniać wiersze którymi są zainteresowani poprzez zmianę ich koloru, wielkości czcionki lub ukrycie pozostałych wierszy.

Już po tygodniu intensywnego używania tego pliku jego formatowanie pozostawia bardzo wiele do życzenia.

Zamiast każdorazowo poprawiać je ręcznie znacznie prościej jest nagrać makro które najpierw usuwa wszelkie formatowanie oraz odkrywa wszystkie kolumny i wiersze, a później tworzy właściwe formatowanie i ukrywa wiersze i kolumny które mają być schowane.

Ponieważ trudno jest przewidzieć jakie zmiany formatowania może wprowadzić duża ilość osób, najwygodniej będzie użyć opcji Edycja à Wyczyść à Formaty, która kasuje wszelkie formatowanie w arkuszu.

Po czym formatujemy tabelę tak aby wyglądała jak poprzednio i kończymy nagrywanie makra.

Podczas formatowania należy unikać przeprowadzania operacji na kilku zakresach naraz ponieważ rejestrator kodu VBA nie radzi sobie z takimi operacjami.

Makro możemy np. podpiąć do przycisku w arkuszu.

Podczas nagrywania makr Excel rejestruje je w postaci kodu VBA, poniżej prezentuje niewielki fragment kodu nagranego przez nas makra. Prawdę mówiąc duża jego część jest zbędna, ale ponieważ makro działa szybko i bezbłędnie na tym etapie naszej wiedzy o VBA nie będziemy ingerować w ten kod i optymalizować go. Makro jest gotowe do użycia bez konieczności wprowadzania w nim zmian.

Każde nowe makro warto przetestować, proponuję więc popsuć formatowanie a potem uruchomić nasze makro.

We wszystkich powyższych przykładach przygotowaliśmy użyteczne makra, bez ingerowania w ich kod źródłowy, nie musieliśmy nawet oglądać ich kodu.

Aby przygotować bardziej użyteczne makra będziemy już musieli zapoznać się z tym jak wprowadzać zmiany w kodzie VBA, zarejestrowanym podczas nagrywania.

Szkolenie Excel 2003 Szkolenie Excel 2007