Microsoft Word - Pisanie makropoleceń w MS Excel 2003.doc

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

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.

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

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"

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

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.