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

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.
   

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

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

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.
   

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.

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.

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.

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.

- 10 -

---