Jak nagrać makro w programie Excel - przewodnik krok po kroku

Nawet jeśli jesteś kompletnym nowicjuszem w świecie Excel VBA, możesz łatwo nagrać makro i zautomatyzować część swojej pracy.

W tym szczegółowym przewodniku omówię wszystko, co musisz wiedzieć, aby rozpocząć nagrywanie i używanie makr w programie Excel.

Jeśli interesuje Cię nauka VBA w prosty sposób, zajrzyj na mój Szkolenie Excel VBA online.

Co to jest makro?

Jeśli jesteś nowicjuszem w VBA, pozwól, że najpierw powiem, czym jest makro - w końcu będę używał tego terminu w całym tutorialu.

Makro to kod napisany w języku VBA (Visual Basic for Applications), który umożliwia uruchamianie fragmentu kodu za każdym razem, gdy jest wykonywany.

Często zdarza się, że ludzie (w tym ja) odnoszą się do kodu VBA jako do makra – niezależnie od tego, czy został wygenerowany za pomocą rejestratora makr, czy został napisany ręcznie.

Gdy nagrywasz makro, program Excel uważnie obserwuje podejmowane przez Ciebie kroki i zapisuje je w zrozumiałym dla siebie języku - czyli VBA.

A ponieważ Excel jest naprawdę dobrym narzędziem do robienia notatek, tworzy bardzo szczegółowy kod (jak zobaczymy w dalszej części tego samouczka).

Teraz, gdy zatrzymasz nagrywanie, zapiszesz makro i uruchomisz je, Excel po prostu wraca do wygenerowanego kodu VBA i wykonuje dokładnie te same kroki.

Oznacza to, że nawet jeśli nic nie wiesz o VBA, możesz zautomatyzować niektóre zadania, pozwalając programowi Excel na zapisanie twoich kroków, a następnie ponowne ich wykorzystanie później.

Teraz zanurkujmy i zobaczmy, jak nagrać makro w programie Excel.

Uzyskiwanie karty Deweloper na Wstążce

Pierwszym krokiem do zarejestrowania makra jest umieszczenie karty Deweloper na wstążce.

Jeśli widzisz już kartę dewelopera na wstążce, przejdź do następnej sekcji, w przeciwnym razie wykonaj poniższe czynności:

• Kliknij prawym przyciskiem myszy dowolną z istniejących kart na wstążce i kliknij opcję „Dostosuj wstążkę”. Otworzy się okno dialogowe Opcje programu Excel.
• W oknie dialogowym Opcje programu Excel będziesz mieć opcje Dostosuj wstążkę. Po prawej stronie, w panelu Główne karty, zaznacz opcję Deweloper.
• Kliknij OK.

Powyższe kroki sprawią, że karta Deweloper będzie dostępna w obszarze wstążki.

Nagrywanie makra w Excelu

Teraz, gdy mamy wszystko na swoim miejscu, nauczmy się nagrywać makro w programie Excel.

Nagrajmy bardzo proste makro – takie, które wybiera komórkę i wpisuje w niej tekst „Excel”. Używam tekstu „Excel” podczas nagrywania tego makra, ale możesz wpisać swoje imię i nazwisko lub dowolny inny tekst, który Ci się podoba.

Oto kroki, aby nagrać to makro:

1. Kliknij kartę Deweloper.
2. W grupie Kod kliknij przycisk Makro. Spowoduje to otwarcie okna dialogowego „Nagraj makro”.
3. W oknie dialogowym Rejestruj makro wprowadź nazwę makra. używam imienia Wprowadź tekst. Podczas nazywania makra należy przestrzegać pewnych warunków dotyczących nazewnictwa. Na przykład nie możesz używać spacji pomiędzy. Zwykle wolę, aby nazwy makr były jednym słowem, z różnymi częściami i pierwszym alfabetem pisanym wielką literą. Możesz także użyć podkreślenia, aby oddzielić dwa słowa - na przykład Enter_Text.
4. (Krok opcjonalny) Jeśli chcesz, możesz przypisać skrót klawiaturowy. W takim przypadku użyjemy skrótu Control + Shift + N. Pamiętaj, że przypisany tutaj skrót zastąpiłby wszystkie istniejące skróty w skoroszycie. Na przykład, jeśli przypiszesz skrót Control + S, nie będziesz mógł użyć tego do zapisania skoroszytu (zamiast tego za każdym razem, gdy go użyjesz, wykona makro).
5. W opcji „Przechowuj makro w” upewnij się, że wybrano „Ten skoroszyt”. Ten krok zapewnia, że ​​makro jest częścią skoroszytu. Będzie tam, gdy go zapiszesz i ponownie otworzysz, a nawet jeśli udostępnisz go komuś.
6. (Krok opcjonalny) Wprowadź opis. Zwykle tego nie robię, ale jeśli jesteś bardzo zorganizowany, możesz dodać, o czym jest makro.
7. Kliknij OK. Jak tylko klikniesz OK, zacznie rejestrować twoje działania w programie Excel. Możesz zobaczyć przycisk „Zatrzymaj nagrywanie” na karcie Deweloper, który wskazuje, że trwa nagrywanie makra.
8. Wybierz komórkę A2.
9. Wpisz tekst Przewyższać (lub możesz użyć swojego imienia i nazwiska).
10. Naciśnij klawisz Enter. Spowoduje to wybranie komórki A3.
11. Kliknij przycisk Zatrzymaj nagrywanie na karcie Deweloper.

Gratulacje!

Właśnie nagrałeś swoje pierwsze makro w programie Excel. Nie jesteś już makro dziewicą.

Chociaż makro nie robi niczego użytecznego, spełni swoje zadanie, wyjaśniając, jak działa rejestrator makr w programie Excel.

Teraz przejdźmy dalej i przetestujmy to makro.

Wykonaj poniższe czynności, aby przetestować makro:

1. Usuń tekst w komórce A2. Ma to na celu sprawdzenie, czy makro wstawia tekst do komórki A2, czy nie.
2. Wybierz dowolną komórkę - inną niż A2. Ma to na celu sprawdzenie, czy makro wybiera komórkę A2, czy nie.
3. Kliknij kartę Deweloper.
4. W grupie Kod kliknij przycisk Makra.
5. W oknie dialogowym Makro kliknij nazwę makra - EnterText.
6. Kliknij przycisk Uruchom.

Zauważysz, że zaraz po kliknięciu przycisku Uruchom tekst „Excel” zostanie wstawiony do komórki A2, a komórka A3 zostanie wybrana.

To wszystko może się wydarzyć w ułamku sekundy, ale w rzeczywistości makro – podobnie jak posłuszny elf – wykonało dokładnie te kroki, które pokazałeś podczas nagrywania makra.

Tak więc makro najpierw wybiera komórkę A2, a następnie wprowadza tekst Przewyższać w nim, a następnie zaznacza komórkę A3.

Uwaga: możesz również uruchomić makro za pomocą skrótu klawiaturowego Control + Shift + N (przytrzymaj klawisze Control i Shift, a następnie naciśnij klawisz N). Jest to ten sam skrót klawiaturowy, który przypisaliśmy do makra podczas jego nagrywania.

Co robi nagrywanie makra w backendzie

Przejdźmy teraz do zaplecza programu Excel - edytora VB - i zobaczmy, co naprawdę robi nagrywanie makra.

Oto kroki, aby otworzyć edytor VB w programie Excel:

1. Kliknij kartę Deweloper.
2. W grupie Kod kliknij przycisk Visual Basic.

Możesz też użyć skrótu klawiaturowego - ALT + F11 (przytrzymaj klawisz ALT i naciśnij F11), zamiast powyższych dwóch kroków. Ten skrót otwiera również ten sam edytor VB.

Teraz, jeśli widzisz edytor VB po raz pierwszy, nie bądź przytłoczony.

Pozwól, że szybko zapoznam Cię z anatomią VB Editor.

• Pasek menu: Tutaj masz wszystkie opcje edytora VB. Potraktuj to jako wstążkę VBA. Zawiera polecenia, których można używać podczas pracy z edytorem VB.
• pasek narzędzi - To jest jak pasek narzędzi szybkiego dostępu edytora VB. Zawiera kilka przydatnych opcji i możesz dodać do niego więcej opcji. Jego zaletą jest to, że opcja na pasku narzędzi to tylko jedno kliknięcie.
• Okno Eksploratora Projektu - W tym miejscu program Excel wyświetla listę wszystkich skoroszytów i wszystkich obiektów w każdym skoroszycie. Na przykład, jeśli mamy skoroszyt z 3 arkuszami roboczymi, pojawi się on w Eksploratorze projektów. Jest tu kilka dodatkowych obiektów, takich jak moduły, formularze użytkownika i moduły klas.
• Okno kodu - Tutaj jest nagrywany lub zapisywany kod VBA. Dla każdego obiektu wymienionego w Eksploratorze projektów znajduje się okno kodu - takie jak arkusze, skoroszyty, moduły itp. W dalszej części tego samouczka zobaczymy, że zarejestrowane makro trafia do okna kodu modułu.
• Okno właściwości - W tym oknie możesz zobaczyć właściwości każdego obiektu. Często używam tego okna do nazywania obiektów lub zmiany ukrytych właściwości. Możesz nie widzieć tego okna po otwarciu edytora VB. Aby to wyświetlić, kliknij kartę Widok i wybierz Okno właściwości.
• Okno bezpośrednie - Często używam okna bezpośredniego podczas pisania kodu. Jest to przydatne, gdy chcesz przetestować niektóre instrukcje lub podczas debugowania. Może nie być domyślnie widoczny i można go wyświetlić, klikając kartę Widok i wybierając opcję Okno natychmiastowe.

Kiedy nagraliśmy makro - Wprowadź tekst, w Edytorze VB wydarzyły się następujące rzeczy:

• Włożono nowy moduł.
• Nagrano makro o podanej przez nas nazwie - Wprowadź tekst
• Kod został zapisany w oknie kodu modułu.

Jeśli więc klikniesz dwukrotnie moduł (w tym przypadku Moduł 1), pojawi się okno kodu, jak pokazano poniżej.

Oto kod, który dał nam rejestrator makr:

Sub EnterText() ' ' Makro EnterText ' ' Skrót klawiaturowy: Ctrl+Shift+N ' Range("A2").Select ActiveCell.FormulaR1C1 = "Excel" Range("A3").Select End Sub

W VBA każdy wiersz następujący po ‘ (znak apostrofu) nie jest wykonywany. To komentarz umieszczony wyłącznie w celach informacyjnych. Jeśli usuniesz pierwsze pięć wierszy tego kodu, makro będzie nadal działać zgodnie z oczekiwaniami.

Teraz szybko omówię, co robi każdy wiersz kodu:

Kod zaczyna się od Sub, po którym następuje nazwa makra i pusty nawias. Sub to skrót od Subroutine. Każdy podprogram (zwany również procedurą) w VBA zaczyna się od Sub i kończy na End Sub.

• Zakres („A2”). Wybierz - Ta linia wybiera komórkę A2.
• ActiveCell.FormulaR1C1 = „Excel” - ta linia wprowadza tekst Excel w aktywnej komórce. Ponieważ wybraliśmy A2 jako pierwszy krok, staje się on naszą aktywną komórką.
• Zakres („A3”). Wybierz - Wybiera komórkę A3. Dzieje się tak, gdy po wpisaniu tekstu wciskamy klawisz Enter, czego efektem było wybranie komórki A3.

Mam nadzieję, że już wiesz, jak nagrywać makro w programie Excel.

Należy pamiętać, że kod napisany przez rejestrator makr nie jest w żadnym wypadku kodem wydajnym.

Rejestrator makr czasami dodaje do kodu dużo puchu, co czasami nie jest konieczne. Ale to nie znaczy, że nie jest przydatne. Dla kogoś, kto uczy się VBA, rejestrator makr może być świetnym sposobem na przeanalizowanie, jak wszystko działa w VBA.

Bezwzględne i względne nagrywanie makro

Wiesz już o odniesieniach bezwzględnych i względnych w Excelu… prawda?

Jeśli nie - przeczytaj najpierw ten samouczek na temat referencji.

Przeczytaj to? Przejdźmy dalej.

W dalszej części tej sekcji zobaczymy, jak rejestrować makra w odniesieniach bezwzględnych i względnych. Ale wcześniej pozwól mi szybko podsumować różnicę między bezwzględnym a względnym odniesieniem w VBA (na wypadek, gdybyś był leniwy i nie przeczytał linku, który podałem kilka linijek temu):

Jeśli użyjesz opcji odwołania bezwzględnego do zarejestrowania makra, kod VBA zawsze będzie odwoływał się do tych samych komórek, których użyłeś. Na przykład, jeśli wybierzesz komórkę A2, wpisz tekst Excel i naciśnij Enter, za każdym razem - bez względu na to, gdzie jesteś w arkuszu i bez względu na wybraną komórkę, twój kod najpierw wybierze komórkę A2, wpisz tekst Excel i następnie przejdź do komórki A3.

Jeśli użyjesz opcji odniesienia względnego do zarejestrowania makra, VBA nie zakoduje na stałe odwołań do komórek. Koncentruje się raczej na ruchu w porównaniu z aktywną komórką. Załóżmy na przykład, że masz już wybraną komórkę A1 i rozpoczynasz nagrywanie makra w trybie odwołań względnych.

Teraz wybierz komórkę A2, wpisz tekst Excel i naciśnij klawisz Enter. Teraz, gdy uruchomisz to makro, nie powróci ono do komórki A2, zamiast tego przesunie się względem aktywnej komórki. Na przykład, jeśli zaznaczona jest komórka K3, przeniesie się ona do K4, wpisz tekst Excel, a następnie wybierz komórkę K5.

Teraz powiem, jak nagrać makro w trybie odwołań względnych:

1. Wybierz komórkę A1.
2. Kliknij kartę Deweloper.
3. W grupie Kod kliknij przycisk „Użyj referencji względnych”. Zmieni kolor na zielony, wskazując, że jest włączony.
4. Kliknij przycisk Nagraj makro.
5. W oknie dialogowym Rejestruj makro wprowadź nazwę makra. używam imienia EnterTextRef.
6. W opcji Store macro in upewnij się, że wybrano „Ten skoroszyt”.
7. Kliknij OK.
8. Wybierz komórkę A2.
9. Wpisz tekst Przewyższać (lub możesz wpisać swoje imię).
10. Naciśnij klawisz Enter. Spowoduje to przeniesienie kursora do komórki A3.
11. Kliknij przycisk Zatrzymaj nagrywanie na karcie Deweloper.

Spowoduje to nagranie makro w trybie odniesienia względnego.

Zrób to teraz.

1. Wybierz dowolną komórkę (inną niż A1).
2. Kliknij kartę Deweloper.
3. W grupie Kod kliknij przycisk Makra.
4. W oknie dialogowym Makro kliknij makro Nazwa - EnterTextRef.
5. Kliknij przycisk Uruchom.

Co się dzieje? Czy kursor wrócił do komórki A3.

nie będzie - ponieważ nagrałeś makro w trybie odniesienia względnego. Kursor poruszałby się więc względem aktywnej komórki. Na przykład, jeśli zrobisz to po zaznaczeniu komórki K3, wprowadzi tekst Excel to komórka K4 i ostatecznie wybierze komórkę K5.

Oto kod, który jest rejestrowany w backendzie (okno kodu modułu VB Editor):

Sub EnterTextRelRef() ' ' Makro EnterTextRelRef ' ' ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "Excel" ActiveCell.Offset(1, 0).Range("A1"). Wybierz Koniec Sub

Zauważ, że cały ten kod nie odnosi się nigdzie do komórek K3 lub K4. Zamiast tego używa Activecell do odwoływania się do wybranej komórki i Offset do poruszania się względem aktywnej komórki.

Nie martw się o część Range („A1”), którą ma kod. Jest to jeden z tych niepotrzebnych kodów, które dodaje rejestrator makr, który jest bezcelowy i można go usunąć. Kod działałby dobrze bez tego.

Przycisk „Użyj odniesienia względnego” na karcie Deweloper to przycisk przełączania. Możesz go wyłączyć (i przełączyć z powrotem na odniesienie bezwzględne), klikając go.

Czego nie może zrobić rejestrator makr

Rejestrator makr świetnie nadaje się do śledzenia Cię w programie Excel i rejestrowania dokładnych kroków, ale może Cię zawieść, gdy będziesz potrzebować więcej.

• Nie możesz wykonać kodu bez wybrania obiektu. Jeśli chcesz, aby rejestrator makr przeszedł do następnego arkusza roboczego i zaznaczył wszystkie wypełnione komórki w kolumnie A, bez opuszczania bieżącego arkusza roboczego, nie będzie w stanie tego zrobić. To dlatego, że jeśli cię o to poproszę, nawet ty nie będziesz w stanie tego zrobić (bez opuszczania bieżącego arkusza). A jeśli nie możesz zrobić tego sam, w jaki sposób rejestrator makr przechwyci Twoje działania. W takich przypadkach musisz ręcznie przejść i utworzyć/edytować kod.
• Za pomocą rejestratora makr nie można utworzyć funkcji niestandardowej. Dzięki VBA możesz tworzyć niestandardowe funkcje, których możesz używać w arkuszu jako zwykłych funkcji. Możesz to stworzyć, pisząc kod ręcznie.
• Nie możesz uruchamiać kodów na podstawie Zdarzeń: W VBA możesz użyć wielu zdarzeń - takich jak otwarcie skoroszytu, dodanie arkusza roboczego, dwukrotne kliknięcie komórki itp., aby uruchomić kod powiązany z tym zdarzeniem. W tym celu możesz użyć rejestratora makr.
• Za pomocą rejestratora makr nie można tworzyć pętli. Po ręcznym wprowadzeniu kodu można wykorzystać moc pętli w języku VBA (takich jak For Next, For Each Next, Do While, Do until). Ale nie możesz tego zrobić, gdy nagrywasz makro.
• Nie możesz analizować warunków: Możesz sprawdzić warunki w kodzie za pomocą rejestratora makr. Jeśli ręcznie piszesz kod VBA, możesz użyć instrukcji IF Then Else, aby przeanalizować warunek i uruchomić kod, jeśli jest prawdziwy (lub inny kod, jeśli jest fałszywy).
• Nie możesz przekazać argumentów w procedurze makr: Kiedy nagrywasz makro, nigdy nie będzie ono miało żadnych argumentów. Podprogram może przyjmować argumenty wejściowe, których można użyć w makrze do wykonania zadania. Podczas nagrywania makra nie można tego zrobić, ponieważ zarejestrowane makra są niezależne i nie są połączone z żadnymi innymi istniejącymi makrami.

Rozszerzenia plików z obsługą makr

Podczas rejestrowania makra lub ręcznego pisania kodu VBA w programie Excel należy zapisać plik z rozszerzeniem obsługującym makra (xlsm).

Przed Excelem 2007 wystarczał jeden format pliku - .xls.

Ale od 2007 r. .xlsx został wprowadzony jako standardowe rozszerzenie pliku. Pliki zapisane jako .xlsx nie mogą zawierać makra. Więc jeśli masz plik z rozszerzeniem .xlsx i nagrasz/zapiszesz makro i zapiszesz je, wyświetli ostrzeżenie przed zapisaniem go w formacie z obsługą makr i wyświetli okno dialogowe (jak pokazano poniżej):

W przypadku wybrania opcji Nie program Excel umożliwia zapisanie go w formacie obsługującym makra. Ale jeśli klikniesz Tak, program Excel automatycznie usunie cały kod ze skoroszytu i zapisze go jako skoroszyt .xlsx.

Jeśli więc masz w skoroszycie makro, musisz je zapisać w formacie .xlsm, aby zachować to makro.

Różne sposoby uruchamiania makra w programie Excel

Do tej pory widzieliśmy tylko jeden sposób uruchamiania makra w Excelu - jest to użycie okna dialogowego Makro.

Istnieje jednak wiele sposobów uruchamiania makr.

1. Uruchom makro ze Wstążki (karta Deweloper)
2. Korzystanie ze skrótu klawiaturowego (który musisz przypisać)
3. Przypisz makro do kształtu
4. Przypisz makro do przycisku
5. Uruchom makro z edytora VB

Wniosek - nagraj makro, gdy utknie

Wspomniałem już, że rejestrator makr jest przydatnym narzędziem dla każdego, kto pracuje z VBA w Excelu.

Gdy użyjesz rejestratora makr kilka razy, zauważysz, że wypluwa on dużo niepotrzebnego kodu. Jednak nadal jest przydatny i daje kilka pomysłów, od czego zacząć. Na przykład, jeśli poproszę o filtrowanie kolumny komórek za pomocą VBA, a nie masz pojęcia, jaka jest składnia, możesz szybko nagrać makro i sprawdzić kod.

Rejestrator makr jest niezbędnym narzędziem i nawet po latach doświadczeń w kodowaniu VBA często korzystam z pomocy rejestratora makr.