Visual Basic w programie Excel
dla Windows
Ćwiczenie nr 1
Makrodefinicje.
Zakres ćwiczenia:
Nagrywanie, odtwarzanie, modyfikowanie i upraszczanie makrodefinicji.
Makrodefinicje lokalne i globalne. Przyporządkowanie makrodefinicji do pozycji w menu,
pasku narzędziowym i do przycisków. Kilka użytecznych funkcji do dialogu z
użytkownikiem.
Wstęp
Microsoft Excel pozwala na zautomatyzowanie często wykonywanych sekwencji
czynności za pomocą Visual Basica będącego wbudowanym językiem programowania
o dużych możliwościach.
Znajomość zagadnień programowania nie jest niezbędna do korzystania z tych możli-
wości. Microsoft Excel posiada wbudowany Rejestrator Makrodefinicji, który tworzy kod
Visual Basica.
Microsoft Excel automatyzuje zadania za pomocą makrodefinicji. Makrodefinicja jest
serią komend wykonywanych automatycznie. Rejestrowanie własnych makrodefinicji
pozwala na dopasowanie Excela do własnych potrzeb i uczynienie swej pracy bardziej
efektywną. Każda sekwencja czynności może być zarejestrowana. Następnie makrodefinicję
można uruchomić aby zarejestrowane czynności zostały powtórzone automatycznie. Zapisaną
makrodefinicję można powiązać z elementem menu lub przyciskiem
Nagrywanie makrodefinicji
Pierwszym krokiem do zautomatyzowania zadań w Excelu jest zapisanie makrodefini-
cji. Patrz rys. 1.
1. Wybierz Narzędzia / Zarejestruj makro / Zarejestruj nowe makro.
2. W polu Nazwa Makra wpisz nazwę makrodefinicji. Nazwa może zawierać litery cyfry
i znaki podkreślenia, musi zaczynać się od litery. Nazwa nie może zawierać spacji i
znaków przystankowych.
3. W polu Opis wprowadź opis makrodefinicji.
4. Aby ustawić opcje dla makrodefinicji wybierz przycisk Opcje. Dla pełnego opisu opcji
skorzystaj z pomocy programu Microsoft Excel.
5. Wybierz przycisk OK. Podczas pracy Rejestratora makr na ekranie pojawia się przycisk
Koniec.
6. Przeprowadź operacje, które chcesz zapisać.
7. Naciśnij przycisk Koniec. Rejestrację makrodefinicji można także zakończyć poleceniem
Narzędzia / Zarejestruj makro / Zatrzymaj rejestrowanie.
Rys. 1. Okno dialogowe Zarejestruj nowe makro.
Uruchamianie makrodefinicji
Zapisaną makrodefinicję można uruchomić w dowolnej chwili. Microsoft Excel
wykona wtedy wszystkie polecenia zapisane w makrze (rys.2).
1. Wybierz Narzędzia / Makro.
2. W polu Nazwa / adres makra wpisz lub wybierz nazwę makrodefinicji.
3. Wciśnij przycisk Uruchom.
Rys. 2. Okno dialogowe Makro.
Używanie paska narzędzi Visual Basic
Pasek
narzędzi Visual Basic (rys. 3) można wyświetlić wybierając Widok / Paski
narzędzi. Kilka z przycisków na tym pasku można używać do rejestrowania, uruchamiania
bądź zatrzymywania makrodefinicji.
Rys. 3. Pasek narzędzi Visual Basic.
Przyporządkowanie makrodefinicji do pozycji w menu
Zarejestrowane makro może zostać przypisane do menu Narzędzia, a następnie
używane tak jak każda inna komenda z menu (rys. 6).
1. Wybierz Narzędzia / Makro.
2. W polu Nazwa / adres makra wpisz lub wybierz nazwę makrodefinicji.
3. Wciśnij przycisk Opcje.
4. W polu Przypisz do zaznacz pole Pozycja menu w menu "Narzędzia", a następnie wpisz
nazwę komendy jaka ma pojawić się w menu Narzędzia.
5. Wciśnij przycisk OK.
6. Wciśnij przycisk Zamknij.
Przyporządkowanie makrodefinicji do przycisku na arkuszu.
W Microsoft Excel można stworzyć na arkuszu lub wykresie przycisk, a następnie
przypisać do niego makrodefinicję. Wciśnięcie takiego przycisku spowoduje uruchomienie
powiązanej z nim makrodefinicji. makrodefinicja taka jest dostępna zawsze gdy otwarty jest
dokument zawierający przycisk, do którego została przypisana.
1. Wciśnij przycisk Utwórz przycisk na pasku narzędziowym Rysowanie.
2. Wskaż kursorem myszy położenie pierwszego rogu przycisku.
3. Ustaw odpowiadający ci rozmiar i kształt przycisku. Kiedy zwolnisz przycisk myszy
zostanie otworzone okno dialogowe Przypisz makro.
4. Aby przypisać do tworzonego przycisku istniejącą makrodefinicję wpisz lub wybierz
nazwę makrodefinicji w polu Nazwa / adres makra i wciśnij przycisk OK.
Aby utworzyć nową makrodefinicję i przypisać ją do przycisku wciśnij przycisk Rejestruj
i dalej postępuj jak przy rejestracji makrodefinicji.
Przyporządkowanie makrodefinicji do przycisku na pasku narzędziowym.
Jeżeli makrodefinicja zostanie przypisana do przycisku na pasku narzędziowym, jest
dostępna na każdym arkuszu należącym do dokumentu. Zwykle przycisk, z którym
powiązujemy makrodefinicję jest nie używanym przyciskiem użytkownika. Makrodefinicje
można przypisać także do wbudowanego przycisku, który jest już przez Microsoft Excel
używany. Powoduje to nadpisanie wbudowanej funkcji przez przypisywaną makrodefinicję.
Po
przyporządkowaniu makrodefinicji do przycisku, można przycisk umieścić na
dowolnym wbudowanym pasku narzędziowym lub stworzyć własny. Przycisk taki
funkcjonuje podobnie jak wszystkie oryginale przyciski Microsoft Excel (rys. 4 i rys. 5).
Przyporządkowanie makrodefinicji do przycisku wbudowanego:
1.
Wybierz Widok / Paski narzędzi.
2.
Wciśnij przycisk Dostosuj. Pojawi się okno dialogowe Dostosuj.
3.
Jeżeli odpowiedni przycisk jest już wyświetlany na pasku narzędziowym wciśnij go, jeżeli
nie wybierz odpowiednią kategorie w polu Kategorie i przeciągnij przycisk do dowolnego
widocznego paska narzędziowego.
4.
Wybierz Narzędzia / Przypisz makro.
5.
Aby przypisać do tworzonego przycisku istniejącą makrodefinicję wpisz lub wybierz
nazwę makrodefinicji w polu Nazwa / adres makra i wciśnij przycisk OK.
Aby utworzyć nową makrodefinicję i przypisać ją do przycisku wciśnij przycisk Rejestruj
i dalej postępuj jak przy rejestracji makrodefinicji.
6.
Jeśli istnieje taka konieczność wciśnij przycisk Zamknij aby zamknąć okno dialogowe
Dostosuj.
Przyporządkowanie makrodefinicji do przycisku użytkownika:
1.
Wybierz Widok / Paski narzędzi.
2.
Aby umieścić przycisk na nowym pasku narzędziowym wprowadź nazwę paska w polu
Nazwa paska narzędzi i wciśnij przycisk Nowy.
Aby dodać przycisk do istniejącego paska narzędzi wybierz Dostosuj.
3.
W polu Kategorie wybierz przyciski użytkownika.
4.
Przeciągnij przycisk do dowolnego widocznego paska narzędzi. Przycisk zostanie dodany
do paska na wskazanej przez ciebie pozycji i otworzy się okno dialogowe Przypisz makro.
5.
Aby przypisać do tworzonego przycisku istniejącą makrodefinicję wpisz lub wybierz
nazwę makrodefinicji w polu Nazwa / adres makra i wciśnij przycisk OK.
Aby utworzyć nową makrodefinicję i przypisać ją do przycisku wciśnij przycisk Rejestruj
i dalej postępuj jak przy rejestracji makrodefinicji.
6.
Jeśli istnieje taka konieczność wciśnij przycisk Zamknij aby zamknąć okno dialogowe
Dostosuj.
Rys. 4. Okno dialogowe Paski narzędzi.
Rys. 5. Okno dialogowe Dostosuj dla pasków narzędzi.
Zmiana makrodefinicji przypisanej do przycisku
Podczas pracy może okazać się konieczne zmienienie makrodefinicji przypisanej do
przycisku, zarejestrowanie dla niego nowej makrodefinicji lub przypisanie już istniejącej
makrodefinicji do nowego przycisku.
1. Trzymając wciśnięty klawisz CTRL wskaż wybrany przycisk.
2. Wybierz Narzędzia / Przypisz makro.
3. Aby przypisać do przycisku istniejącą makrodefinicję wpisz lub wybierz nazwę
makrodefinicji w polu Nazwa / adres makra i wciśnij przycisk OK.
Aby usunąć makrodefinicję powiązaną z przyciskiem usuń nazwę makrodefinicji z pola
Nazwa / adres makra i wciśnij przycisk OK.
Aby utworzyć nową makrodefinicję i przypisać ją do przycisku wciśnij przycisk Rejestruj
i dalej postępuj jak przy rejestracji makrodefinicji.
Procedura zmiany makrodefinicji powiązanej z przyciskiem na pasku narzędzi
przebiega podobnie, jednak najpierw należy wybrać polecenie Widok / Paski narzędzi,
a następnie wcisnąć przycisk Dostosuj.
Ustawianie i zmiana opcji makrodefinicji
Po zarejestrowaniu makrodefinicji istnieje możliwość zmiany jej następujących opcji:
•
opisu makrodefinicji;
•
klawisza skrótu, który uruchamia makrodefinicję;
•
nazwy makrodefinicji pojawiającej się w menu Narzędzia.
1. Wybierz Narzędzia / Makro.
2. W polu Nazwa / adres makra wpisz lub wybierz nazwę makrodefinicji, której opcje chcesz
zmienić.
3. Wciśnij przycisk Opcje.
4. Zmień wymagane opcje.
5. Wciśnij przycisk OK.
Adresy względne i bezwględne
Jedną z opcji, którą można zmieniać podczas rejestracji makrodefinicji jest rodzaj
stosowanych podczas rejestracji adresów.
W przypadku adresów bezwzględnych Microsoft Excel zapisuje dokładną pozycję
każdej wybranej komórki, w przypadku adresów względnych pozycja każdej nowo wybranej
komórki jest liczona względem komórki poprzednio wybranej.
Aby
włączyć używanie adresów względnych należy wybrać Narzędzia / Rejestruj
makro / Używaj adresów względnych.
Rys. 6. Opcje okna dialogowego Zarejestruj nowe makro.
Makrodefinicje lokalne i globalne
Jedną z opcji możliwych do ustawienia przed rejestracją nowej makrodefinicji jest
opcja wskazująca gdzie rejestrowana makrodefinicja zostanie zapisana (pole Przechowaj w
okna dialogowego Zarejestruj nowe makro): w aktywnym skoroszycie, a nowym skoroszycie,
czy w Osobistym skoroszycie makr (rys. 6).
Osobisty skoroszyt makr jest ukrytym skoroszytem, który zawsze pozostaje otwarty.
Wszystkie zapisane w nim makrodefinicje są zawsze dostępne.
Przeglądanie i modyfikowanie zarejestrowanych makrodefinicji
Pierwszym krokiem w edycji makrodefinicji jest wyświetlenie jej. Makrodefinicje są
napisane w języku programowania Visual Basic i zapisane w specjalnych arkuszach
nazywanych modułami Visual Basica.
Wyświetlenie makrodefinicji z wykorzystaniem okna dialogowego Makro:
1.
Wybierz Narzędzia / Makro.
2.
W polu Nazwa / adres makra wpisz lub wybierz nazwę makrodefinicji, którą chcesz
wyświetlić.
3.
Wciśnij przycisk Edytuj.
Wyświetlenie makrodefinicji powiązanej z przyciskiem na arkuszu:
1. Wciśnij przycisk prawym przyciskiem myszy.
2. Wybierz z menu Przypisz makro.
3. W polu Nazwa / adres makra wpisz lub wybierz nazwę makrodefinicji, którą chcesz
wyświetlić.
4. Wciśnij przycisk Edytuj.
Czytanie kodu Visual Basica
Kiedy rejestrujesz makrodefinicję Microsoft Excel tworzy serię wyrażeń
odpowiednich do akcji jakie podejmujesz. Wyrażenia te mogą składać się z słów kluczowych,
operatorów, zmiennych i wywołań procedur. Np.:
ActiveCell.FormulaR1C1 = ”Tytuł Arkusza”
jest wyrażeniem Visual Basica wstawiającym tekst „Tytuł Arkusza” do aktywnej komórki.
Słowa kluczowe są słowami mającymi w Visual Basicu specjalne znaczenie. Np.
słowa kluczowe Sub i End Sub oznaczają początek i koniec makrodefinicji.
Początek komentarza oznacza pojedynczy apostrof. Komentarze zawsze kończą się
z końcem linii, w której pojawił się rozpoczynający je apostrof.
Usuwanie zbędnego kodu
Rejestrator makrodefinicji tworzy kod, który odpowiada większości
przeprowadzanych przez ciebie czynności. Często jednak w module Visual Basica zostaje
zapisanych więcej instrukcji niż chciałbyś aby występowało w makrodefinicji. Powinieneś
usunąć nadmiarowy kod, aby makrodefinicja robiła dokładnie to, czego sobie życzysz.
Rejestracja dodatkowego kodu w istniejącej makrodefinicji
Rejestracja dodatkowego kodu w istniejącej makrodefinicji jest użyteczna kiedy po
zapisaniu i poprawieniu długiej sekwencji czynności okazuje się, że któreś z zadań należy
zmienić lub też dołączyć nowe.
1. Ustaw kursor w miejscu, gdzie chcesz dołączyć kod.
2. Wybierz Narzędzia / Rejestruj makro / Ustaw znacznik dla rejestrowania.
3. Przełącz się na arkusz, z którego chcesz rejestrować nowe zadania.
4. Wybierz Narzędzia / Rejestruj makro / Zarejestruj od znacznika.
5. Przeprowadź operację, które chcesz zarejestrować.
6. Wciśnij przycisk Koniec.
Rozszerzanie makrodefinicji o możliwości Visual Basica
Visual Basic oferuje znacznie więcej niż można osiągnąć poprzez rejestrację
makrodefinicji. Używając Visual Basica twoje makrodefinicje mogą testować różne warunki
i w zależności od nich wykonywać odpowiednie działania, mogą pytać o dane użytkownika
i informować go o postępie w wykonywaniu makrodefinicji.
Zmiana stanu opcji
Poniższa makrodefinicja zmienia stan opcji określającej włączenie bądź wyłączenia
wyświetlania linii podziału, poprzez zanegowanie i przypisanie opcji dotychczasowej jej
wartości.
Sub SetGridlines ( )
ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
End Sub
Dodanie do makrodefinicji elementów interaktywnych
Przedstawiona
niżej makrodefinicja podczas wykonywania wyświetla okno
dialogowe, za pomocą którego pobiera od użytkownika dane do zmiennej służącej jako
chwilowe miejsce przechowywania informacji.
Sub SetTitle ( )
TitleText = InputBox(prompt := ”Podaj tytuł”, default := ”Tytuł domyślny”)
ActiveCell.FormulaR1C1 = TitleText
End Sub
Dodanie do makrodefinicji struktur kontrolnych
Można uczynić makrodefinicję bardziej użyteczną poprzez wprowadzenie do niej
struktur kontrolnych. Pozwalają one na wielokrotne powtarzanie wyrażeń Visual Basica i na
podejmowanie decyzji, które wyrażenia mają zostać wykonane.
Poniższy przykład ustawia czcionkę pogrubioną dla wszystkich komórek arkusza
w zakresie A1 : A10, które zawierają wartość większą od 500. Wykorzystuje ona wyrażenie
warunkowe If...Then i pętlę For...Next.
Sub BoldCells ( )
For I = 1 To 10
If Cells(I,1).Value > 500 Then
Cells(I,1).Font.Bold
=
True
End
If
Next
I
End Sub
Zadania
1. Zarejestruj makrodefinicję ukrywającą linie podziału. Makrodefinicję powiąż z
przyciskiem na własnym pasku narzędzi.
2. Zarejestruj makrodefinicję otwierającą nowy arkusz, ukrywającą na nim linie podziału,
w komórce A1 wpisującą imię i nazwisko autora arkusza, zaś w komórce C4 tytuł arkusza
pogrubioną i podkreśloną czcionką Arial CE o rozmiarze 16 pkt. Makrodefinicję przypisz
do menu Narzędzia.
3. Zarejestruj makrodefinicję, która na aktualnie wybranej komórce arkusza przeprowadza
następujące operacje:
•
ustawia czcionkę Times New Roman CE, pogrubioną, kursywę, o rozmiarze 12 pkt.,
o kolorze niebieskim;
•
ustawia tło komórki na kolor czerwony;
•
do komórki przepisuje zawartość komórki C4;
•
dla sąsiednich komórek ustawia kolor tła zielony i kasuje ich zawartość.
Makrodefinicję powiąż z przyciskiem na jednym z wbudowanych pasków narzędzi.
4. Usuń zbędny kod w stworzonych makrodefinicjach.
5. Makrodefinicję z zad. 1 zmodyfikuj tak, aby za każdym wywołaniem zmieniała stan opcji
odpowiedzialnej za wyświetlanie linii podziału. Utwórz dla niej przycisk na arkuszu
i powiąż ją z nim.
6. Makrodefinicję z zad. 2 zmodyfikuj tak, aby przy uruchomieniu użytkownik proszony był
o podanie swego imienia i nazwiska oraz tytułu arkusza.
7. Utwórz makrodefinicję, która dla wszystkich komórek z zakresu D5-M14, których
zawartość jest większa od 500 będzie ustawiała czcionkę pogrubioną i tło czerwone, zaś
dla wszystkich, których zawartość jest mniejsza od 100 będzie ustawiała czcionkę
podkreśloną i kolor tła zielony.