- 1 -

Writing Macros in Microsoft Excel 2003

http://www.ssc.rdg.ac.uk/software/excel/home.html

Przekład

Robert Wiśniewski

http://chomikuj.pl/bobwis

Pisanie makropoleceń w MS Excel 2003

SPIS TREŚCI

1. Wprowadzenie

2. Odwołania bezwzględne i względne

2.1. Korzystanie z odwołań bezwzględnych

2.2. Korzystanie z odwołań względnych

3. Edycja makropoleceń

3.1. Powtarzanie operacji

3.2. Korzystanie ze zmiennych

4. Zmiana skrótów klawiszowych

5. Usuwanie makropoleceń

6. Ustawianie poziomów bezpieczeństwa makr

- 2 -

1. Wprowadzenie

Makropolecenie Macro jest ciągiem instrukcji, które można wykonywać za pomocą jednego polecenia.
Makropolecenia (lub w skrócie makra) można wywoływać różnymi sposobami – z klawiatury
(w połączeniu z klawiszem CTRL), za pomocą specjalnej ikony w pasku narzędzi, albo przy
korzystaniu z menu. W warunkach idealnych, do tworzenia realnie przydatnych makropoleceń może
być wymagana znajomość języka programowania (Microsoft Visual Basic). Poniższe przykłady
stanowią wprowadzenie do pisania makr.

2. Odwołania bezwzględne i względne

Ilekroć piszemy makro, mamy do dyspozycji opcję korzystania z odwołań bezwzględnych lub
względnych do komórek. Gdy chcemy wykonać instrukcje na określonych komórkach (np. przejście do
komórki A14, obliczenie sumy wartości znajdujących się powyżej i sformatowanie wyświetlonego
wyniku w postaci pogrubionej i walutowej), po czym przejść dalej o określoną liczbę wierszy / kolumn
od obecnego położenia (gdziekolwiek się znajdujemy), wówczas korzystamy z odwołania względnego.
Na szczęście, poniższe przykłady objaśniają to lepiej. Najpierw trzeba otworzyć przykładowy plik oraz
przygotować go do stosowania – użytkownicy kampusu mogą ściągnąć ten plik przez kliknięcie nazwy
pliku w poniższym kroku 1.

1. Uruchomić Excel, wybrać polecenie menu Otwórz i otworzyć plik o nazwie

phoenix.xls

.

2. Kliknąć prawym klawiszem myszki zakładkę PHOENIX i wybrać opcję Wstaw | Arkusz, po

czym kliknąć przycisk OK lub wcisnąć klawisz ENTER.

3. Kliknąć ponownie zakładkę PHOENIX aby wrócić do danych.

2.1. Korzystanie z odwołań bezwzględnych

W naszym pierwszym przykładzie, napiszemy makro, które sumuje wartości w kolumnie A, po czym
wprowadza do wyniku format pogrubienia.

1. Wybrać polecenie menu Narzędzia | Makro | Zarejestruj nowe makro.

2. Ustawić nazwę makra jako Suma

3. Ustawić klawisze skrótu np. jako CTRL + SHIFT + S

Zwrócić uwagę na wielkość liter – unikać kombinacji klawiszy CTRL + S która jest zarezerwowana
dla polecenia Zapisz (zapisywanie pliku).

4. Przechować makro w Ten skoroszyt – alternatywą jest przechowanie w Skoroszyt makr

osobistych, co udostępnia makro przy każdym korzystaniu z Excela.

- 3 -

5. Wprowadzić opis – jeśli chcemy (z tekstem mówiącym do czego służy makro oraz kiedy i kto

go zarejestrował, np. Makro zarejestrowane 08-05-2011, autor Bob).

6. Wcisnąć klawisz ENTER lub kliknąć przycisk OK aby zacząć rejestrację.

Pojawi się pasek narzędzi rejestracji – NIE ZAMYKAĆ GO. Dwa przyciski tego paska noszą
nazwę Zatrzymaj rejestrowanie oraz Odwołanie względne. Nie klikać na żadnego z tych
przycisków na tym etapie.

Teraz można wykonywać polecenia, które mają tworzyć makro.

7. Wcisnąć klawisze CTRL + HOME (lub kliknąć komórkę A1) aby makro rozpoczynało się od

komórki A1.

8. Przytrzymać klawisz CTRL i wcisnąć klawisz kierunkowy  aby przesunąć aktywną komórkę

na koniec kolumny A.

9. Zwolnić klawisz CTRL i wcisnąć klawisz kierunkowy  aby przesunąć aktywną komórkę do

komórki A52.

10. Kliknąć przycisk Autosumowanie w pasku narzędzi, po czym wcisnąć klawisz ENTER w celu

potwierdzenia formuły =SUM(A2:A51)

11. Wcisnąć klawisz kierunkowy  aby wrócić do komórki A52, po czym kliknąć przycisk

Pogrubienie w celu pogubienia wyniku.

12. Kliknąć przycisk Zatrzymaj rejestrowanie w specjalnym pasku narzędzi w celu zakończenia

rejestracji makro.

Mamy teraz utworzone makro, które sumuje wartości w kolumnie A. Przetestować to makro
w poniższy sposób:

13. Najpierw wcisnąć klawisz DEL aby opróżnić komórkę A52.

14. Następnie przytrzymać klawisze CTRL + SHIFT + s. Suma (1275) pojawi się w komórce A52

w postaci pogrubionej.

15. Spróbować dodać nową wartość. Wybrać polecenie menu Wstaw| Wiersze

16. W nowej, pustej komórce A52, wpisać wartość 51, po czym wcisnąć klawisz ENTER. Zwrócić

uwagę że suma w komórce A53 ulegnie zmianie.

17. Uruchomić ponownie makro (CTRL + SHIFT + s) i zobaczymy, że nowa wartość zastąpi

poprzednią sumę i komórka A53 pokaże powiększoną wartość sumy.

Jest to prosty przykład odwołania bezwzględnego – makro to działa tylko na komórkach dla których
zostało ono zarejestrowane. Chociaż skorzystanie z kombinacji klawiszy sterujących spowodowało
przejście do stopki tej kolumny, to następne przesunięcie (do komórki A52 jak w kroku 9), zostało
zarejestrowane jako przesunięcie do ustalonej komórki.

W drugim przykładzie pozostawione jest odwołanie bezwzględne, ale nie ma to wpływu na wynik.
Celem tego makra jest wyszukanie jajek w kolorze niebiesko-zielonym Blue-Green i podświetlenie
komórek o odpowiednim kolorze.

1. Wybrać polecenie menu Narzędzia | Makro | Zarejestruj nowe makro.

2. Wpisać nazwę makra. np. Colour.

3. Ustawić skrót klawiszowy, np. CTRL + SHIFT + C. Zwrócić uwagę na wielkość liter – unikać

kombinacji klawiszy CTRL + c która jest zarezerwowana dla polecenia Kopiuj.

- 4 -

4. Przechować makro w Ten skoroszyt – lub wprowadzić opis, jeśli chcemy.

5. Wcisnąć klawisz ENTER lub kliknąć przycisk OK aby zacząć rejestrowanie makra.

6. Wybrać polecenie menu Edycja | Znajdź.

7. W otworzonym okienku wyszukiwania, w polu Znajdź wpisać Blue-Green.

8. Wcisnąć klawisz ENTER lub kliknąć przycisk Znajdź następny.

9. Wcisnąć klawisz ESC lub kliknąć przycisk Zamknij aby zamknąć to okienko dialogowe.

10. Kliknąć przycisk z listą Kolor wypełnienia w pasku narzędzi aby ją rozwinąć.

11. Wybrać odpowiedni kolor w palecie, np. Akwamaryna.

12. Kliknąć przycisk Zatrzymaj rejestrowanie w specjalnym pasku narzędzi w celu zakończenia

rejestracji makro.

Mamy teraz utworzone makro, które komórce Blue-Green nadaje odpowiedni kolor. Przetestować
to makro przez wciśniecie odpowiedniej kombinacji klawiszy.

13. Wcisnąć klawisze CTRL + SHIFT + c i powtarzać to kilkakrotnie.

Makro to działa od aktywnej komórki w dół, przechodząc do następnej komórki Blue-Green
zamiast do określonej komórki. W celu dalszej demonstracji:

14. Przesunąć aktywną komórkę w dół kilka razy.

15. Wcisnąć klawisze CTRL + SHIFT + c i powtarzać to kilkakrotnie.

Zobaczymy później jak edytować makro o nazwie Colour dla wszystkich komórek Blue-Green,
w tym tych, które zostały pominięte.

2.2. Korzystanie z odwołań względnych

Niniejszy rozdział jest poświęcony kopiowaniu co 5-tego wiersza danych do nowego arkusza
(Arkusz1).

Tutaj będziemy korzystali z odwołań względnych, ponieważ każdy następny obszar źródła danych
oraz obszar docelowy są znane jako oddalone od siebie o o znaną odległość (odpowiednio 5 wierszy
w dół i 1 wiersz w dół).

1. Przed rozpoczęciem tworzenia makra, przejść do pierwszego arkusza klikając zakładkę

Arkusz1.

2. Wybrać polecenie menu Narzędzia | Makro | Zarejestruj nowe makro.

3. Wpisać nazwę makra, np. Every5.

4. Ustawić skrót klawiszowy makra, np. CTRL + e.

5. Przechować makro w Ten skoroszyt – lub wprowadzić opis, jeśli chcemy.

6. Wcisnąć klawisz ENTER lub kliknąć przycisk OK aby zacząć rejestrowanie makra.

Zachować ostrożność i wykonywać dokładnie poniższe instrukcje – gdy popełnimy pomyłkę,
zapewne najlepiej przerwać rejestrację i zacząć od nowa (można wtedy skorzystać z innego
skrótu klawiszowego).

- 5 -

7. Kliknąć przycisk Odwołanie względne po prawej stronie przycisku Zatrzymaj rejestrowanie

w specjalnym pasku narzędzi.

8. Kliknąć zakładkę PHOENIX, po czym wcisnąć klawisze CTRL + HOME aby uczynić aktywną

komórkę A1.

9. Wcisnąć klawisze SHIFT + CTRL +  aby zaznaczyć wiersz 1.

10. Kliknąć przycisk Kopiuj lub wybrać polecenie menu Edycja | Kopiuj.

11. Kliknąć zakładkę Arkusz1 aby przejść do pustego arkusza.

12. Wcisnąć klawisze CTRL + HOME aby przejść do komórki A1, po czym wcisnąć klawisz

ENTER w celu wklejania.

13. Wcisnąć klawisz  w celu przesunięcia aktywnej komórki w Arkusz1 do komórki A2.

14. Kliknąć zakładkę PHOENIX aby wrócić do oryginalnych danych.

15. Wcisnąć 5-ktrotnie klawisz  aby przesunąć aktywną komórkę do A6 (lub kliknąć ją).

16. Wcisnąć klawisze SHIFT + CTRL +  aby zaznaczyć ten wiersz.

17. Kliknąć przycisk Kopiuj lub wybrać polecenie menu Edycja | Kopiuj.

18. Kliknąć zakładkę Arkusz1 i wcisnąć klawisz ENTER w celu wklejenia.

19. Kliknąć przycisk Zatrzymaj rejestrowanie w specjalnym pasku narzędzi w celu zakończenia

rejestracji makro.

Przetestować to makro jeśli chcemy (najpierw usunąć dane w komórkach arkusza1, po czym
wcisnąć klawisz CTRL + e), jednak wszystko co się pojawi będzie wklejeniem tego samego
podzestawu danych do tych samych komórek w arkuszu 1. W celu uzupełnienia tego makra,
musimy teraz poddać go edycji.

3. Edycja makropoleceń

Chociaż rejestrowanie makra jest stosunkowo proste, to jego zmiana nie musi być łatwa. Podczas
edycji makra korzystamy z edytora Visual Basic Editor i musimy dobrze zrozumieć co wykonuje kod
programu. Nawet gdy nie potrafimy programować, możemy wprowadzić proste zmiany i gdy dokładnie
dodamy poniższe wiersze programowe, zmienimy nasze makro w bardzo użyteczne.

1. Wybrać polecenie menu Narzędzia | Makro | Makra.

2. W otworzonym okienku dialogowym wybrać makro Every5 i kliknąć przycisk Edycja.

Znajdziemy się w edytorze makr, w którym zostanie wyświetlony poniższy kod:

Sub Every5()
'
' Every5 Macro
' Macro recorded 16/08/2005 to copy every fifth row to Sheet1
' Keyboard Shortcut: Ctrl+e
'

Sheets("PHOENIX").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select

- 6 -

Selection.Copy Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("PHOENIX").Select
ActiveCell.Offset(5, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy Sheets("Sheet1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Nie trzeba martwić się tym, że nie rozumiemy tego co się pojawiło na ekranie. Gdy porównany to
dokładnie z oryginalnymi instrukcjami, wówczas zauważymy pewne idee tego co się pojawia.

Zwrócić uwagę na wiersz

ActiveCell.Offset(1,0).Range("A1").Select

.

Jest to odwołanie względne – tzn. powoduje ono przesunięcie aktywnej komórkę w dół o 1 wiersz i 0
kolumn w bok względem aktywnej komórki.

Gdy korzystamy z odwołań bezwzględnych, wiersz ten przybiera poniższą postać:

Range("A2").Select

tzn. powoduje on przesunięcie aktywnej komórki do A2 i jej zaznaczenie.

Wskazówka: Można korzystać z klawiszy ALT + F11 w celu otwierania edytora Visual Basic Editor
(lub przełączania się do Excela).
Po skończeniu wprowadzania zmian do naszego makra, wciśnięcie klawiszy ALT + Q pozwala na
zamknięcie tego edytora.

3.1. Powtarzanie operacji

Teraz spróbujemy wprowadzić pewne zmiany do naszego makra. Gdy chcemy wybierać co 10-ty
wiersz, wówczas musimy wprowadzić drugie odwołanie względne do czytania:

ActiveCell.Offset(10,0).Range("A1").Select

.

Jednak pierwotnym celem tego makra było wybieranie co 5-tego wiersza – instrukcje jakie zostały
zarejestrowane muszą być powtarzane 10-krotnie (aby uwzględnić wszystkie 50 wierszy całego
zestawu danych).

W tym celu trzeba dodać dodatkowe wiersze w środku i na końcu programu.

1. Przesunąć kursor na początek kodu, który ma być powtarzany:

ActiveCell.Offset(1, 0).Range("A1").Select

2. Dodać poniższy wiersz kodu:

for k=1 to 10

po czym wcisnąć klawisz ENTER.

- 7 -

3.

Przenieść kursor na koniec przedostatniego wiersza:

Application.CutCopyMode = False

4. Wcisnąć klawisz ENTER i wpisać poniższy kod:

Next k

Te dwa dodatkowe wiersze kodu utworzą pętlę Loop, która będzie powtarzana 10-krotnie. Licznik
pętli k zaczyna się od 1 i wzrasta o 1 po każdym uruchomieniu tego kodu. Gdy licznik osiągnie
wartość 10, pętla się kończy.

5. Zamknąć okno edytora przez z kliknięcie przycisku Zamknij okno

(lub przez wciśnięcie

klawiszy ALT + Q) – zmiany kodu zostaną automatycznie zapisane.

6. Zaznaczyć i usunąć klawiszem DEL wartości przechowywane w arkuszu 1, po czym wcisnąć

klawisze CTRL + e aby uruchomić nasze makro.

Zobaczymy, że do arkusza 1 zostało skopiowane 10 wierszy (co 5-ty).

7. Upewnić się czy można wykonać podobną edycję w makro Colour w celu wprowadzania

koloru do wszystkich jajek niebiesko zielonych Blue-Green.

Można rozszerzyć to makro na wprowadzanie koloru do jajek czerwono-brązowych Red-Brown.
Wystarczy w tym celu zastąpić

Blue-Green

kolorem

Red-Brown

oraz ustawić wiersz koloru

indeksu na

.ColorIndex = 45.

3.2. Korzystanie ze zmiennych

Licznik pętli k w powyższym przykładzie jest znany jako zmienna programowa. Ulega ona zmianie od
wartości początkowej 1 w górę do wartości końcowej 10. Można jednak skorzystać tej zmiennej
w kodzie naszego modułu.

1. Wybrać polecenie menu Narzędzia | Makro | Makra.

2. W otworzonym okienku dialogowym wybrać makro Every5 i kliknąć przycisk Edycja.

3.

Zmienić wiersz z kodem:

ActiveCell.Offset(5, 0).Range("A1").Select

tzn. zmienić w nim 5 na k.

4. Zamknąć okno MS Visual Basic klikając przycisk Zamknij okno

.

5. Zaznaczyć i usunąć klawiszem DEL wartości przechowywane w arkuszu 1, po czym wcisnąć

klawisze CTRL + e aby uruchomić nasze makro.

Zobaczymy, że rekordy 1, 3, 6, 10, 15, 21, 28, 36 oraz 45 zostały skopiowane – Przesunięcie (k)
zwiększało się za każdym razem o 1. Po osiągnięciu wartości k=10, i pustego rekordu, liczba 55
z wiersza 56 została również skopiowana. W tym przykładzie musimy trochę więcej dowiedzieć
się o programowaniu.

Instrukcja

ActiveCell.Offset

…

ma parametry liczbowe – liczby rzeczywiste takie jak 1, 5 lub

0 albo zmienne liczbowe takie jak k. Jednak z drugiej strony, instrukcja

Range("A1")

ma

parametr łańcuchowy (tekstowy) A1, (zamknięty w cudzysłowach).
Gdy chcemy korzystać ze zmiennej liczbowej k, w instrukcji

Range

, musimy najpierw

przekształcić ją na łańcuch znakowy.

- 8 -

6. Wybrać polecenie menu Narzędzia | Makro | Makra.

7. W otworzonym okienku dialogowym wybrać makro Every5 i kliknąć przycisk Edycja.

8. Kliknąć na końcu wiersza

for k=1 to 10

i wcisnąć klawisz ENTER.

9. Wpisać dodatkowy wiersz:

m$ = "A" & Format(k)

Funkcja

Format

przekształci liczbę przechowywana w k na łańcuch.

10. Zmienić wiersz:

ActiveCell.Offset(1, 0).Range("A1").Select

na:

ActiveCell.Offset(1,0).Range(m$).Select

tzn. zmienić

"A1"

na

m$

11. Zamknąć okno edytora przez z kliknięcie przycisku Zamknij okno

(lub przez wciśnięcie

klawiszy ALT + Q).

12. Zaznaczyć i usunąć klawiszem DEL wartości przechowywane w arkuszu 1, po czym wcisnąć

klawisze CTRL + e aby uruchomić nasze makro.

Tym razem nasze makro kopiuje te same wiersze z arkusz PHOENIX tak jak poprzednio, ale wkleja je
w odpowiadających wierszach pierwszego arkusza Arkusz1. Dzieje się tak dlatego, że wklejanie jest
wykonywane nie od komórki 1 w dół, począwszy od aktywnej komórki (w kolumnie A), lecz od
komórek

m$

w dół.

Oczywiście, można napisać więcej makr niż przytoczono w tym prostym wprowadzeniu, ale mimo tego
uzyskaliśmy już pewien pogląd na to jak działają makra i jak można je zmieniać.

4. Zmiana skrótów klawiszowych

Gdy chcemy zmienić klawisze skrótów uruchamiające określone makr:

1. Wybrać polecenie menu Narzędzia | Makro | Makra.

2. Wybrać makro, które chcemy zmienić.

3. Kliknąć przycisk Opcje.

4. Ustawić nowy skrót klawiszowy, po czym wcisnąć klawisz ENTER lub kliknąć przycisk OK.

- 9 -

5. Wcisnąć ponownie klawisz ENTER aby zamknąć to okienko dialogowe.

Podkreśla się, że można tu również zmienić opis makra.

5. Usuwanie makropoleceń

Aby usunąć makro, które nam już dłużej nie jest potrzebne:

1. Wybrać polecenie menu Narzędzia | Makro | Makra.

2. Wybrać makro, które chcemy usunąć i kliknąć przycisk Usuń.

3. Zostaniemy poproszeni o potwierdzenie decyzji. Wcisnąć klawisz ENTER lub kliknąć Tak.

6. Ustawianie poziomów bezpieczeństwa makr

Z uwagi na to, że makra mogą być stosowane do rozprowadzania wirusów między komputerami,
Microsoft wprowadził ustawienia bezpieczeństwa do Office XP. Aktualnie, w komputerach PC
zawierających IT Services Labs, opcja ta jest w Excelu ustawiona na najniższym poziomie, ponieważ
mamy w systemie rozszerzone zabezpieczenie antywirusowe. W naszych własnych komputerach PC
zaleca się ustawić tą opcje na poziomie średnim, co pozwala na odpowiadanie na pytanie czy
uruchamiać makra czy nie.

Aby ustawić poziom bezpieczeństwa:

1. Wybrać polecenie menu Narzędzia | Makro | Zabezpieczenia otworzy się poniższe okienko

dialogowe z dwiema zakładkami:

2. Wybrać wymagany poziom bezpieczeństwa (tutaj – Średnie), po czym kliknąć przycisk OK.

Ustawienie bezpieczeństwa na poziomie Wysokie wyłącza zupełnie wszystkie makra.

Ustawienie bezpieczeństwa na poziomie Niskie ładuje wszystkie plik bez pytania o zgodę na
włączanie lub wyłączanie makr. Ustawienie to wybieramy zwykle gdy nasz program antywirusowy jest
nieaktualny lub ma starą bazę wirusów.

Ustawienie bezpieczeństwa na poziomie Średnie wyświetla poniższe okienko dialogowe przy
otwieraniu pliku.

- 10 -

1. Kliknąć przycisk Włącz makra aby otworzyć plik z aktywnymi makrami

Wskazówka: Gdy otrzymamy plik zawierający makra, warto je wyłączyć przy pierwszym otwieraniu
pliku i zbadać go na obecność wirusów.