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.