Microsoft Excel 2000
- 228 -
Makra, przyciski i Visual Basic for Applications
Wielokrotnie pracuj
ąc z arkuszem obliczeniowym
wykonujemy operacje powtarzalne. Załó
żmy na przykład, że co
miesi
ąc tworzymy listę płac jak w Zadaniu 1. Przed wpisaniem
nowych warto
ści w kolumnie % premii powinniśmy usunąć wartości
z poprzedniego miesi
ąca. Można to robić ręcznie: zaznaczyć obszar
mysz
ą i następnie użyć klawisz Delete (czy Del) z klawiatury. Jeśli
jednak lista byłaby długa lub na arkuszu byłoby kilka/kilkana
ście
rozł
ącznych obszarów do czyszczenia, to trudno to robić ręcznie:
operacja staje si
ę żmudna i podatna na błędy (można skasować
zawarto
ść nie tych komórek i usunąć niechcący jakieś parametry
stałe). W takiej sytuacji wygodna i po
żądana jest automatyzacja, czyli
zastosowanie mechanizmu makra. Utworzenie makra polega na
zleceniu
Excelowi
zarejestrowania
ci
ągu czynności, które
wykonujemy. Ten zarejestrowany ci
ąg poleceń można potem
powtórzy
ć w odpowiedniej chwili.
Rejestracja makra
Rozpoczynaj
ąc rejestrację wybieramy Narzędzia | Makro |
Zarejestruj nowe makro
W oknie Zarejestruj makro mo
żemy zaakceptować
sugerowan
ą nazwę (Makro1, Makro2, ...) albo wpisać własną i
zatwierdzi
ć przyciskiem O.K. Od tego momentu wszystkie czynności
(nawet wykonane przypadkowo) podlegaj
ą rejestracji.
Microsoft Excel 2000
- 229 -
Uwaga!
1. W czasie rejestracji makra na pasku stanu widoczny jest napis
Rejestruj.
2. W czasie rejestracji powinien by
ć także widoczny
1
pasek narz
ędzi
3. Zatrzymanie rejestracji nast
ępuje przez kliknięcie w na
powy
ższym pasku lub z menu przez Narzędzia | Makro |
Zatrzymaj rejestrowanie.
4. Nie nale
ży przyciskać sąsiedniego do przycisku Odwołania
wzgl
ędne, ponieważ makro może nie działać poprawnie
2
5. Je
żeli zorientujemy się, że zostały zarejestrowane niepotrzebne
czynno
ści, najlepiej zarejestrować ponownie makro pod tą samą
nazw
ą.
Zadanie 65.
Zarejestrowa
ć makro o nazwie "Wyczyść" usuwające zawartość
kolumny "% premii" w arkuszu z Zadania 1.
Uruchamianie makra
Zarejestrowane makro mo
żna uruchomić poprzez Narzędzia |
Makro | Makra. Po wyborze nazwy makra wybieramy przycisk
Uruchom i w przypadku uruchomienia makra "Wyczyść" liczby
1
Je eli u ytkownik zamknie ten pasek narz
dzi przez klikni
cie w x w prawym
górnym rogu paska, to przy rejestracji nast
pnego makra pasek ten nie b
dzie
widoczny. aby był ponownie dostepny, nalezy zarejestrowa
makro wy
wietlania
tego paska: Widok | Paski narz
dzi
2
Przycisk ten bywa bardzo przydatny w bardziej zaawansowanych zastosowaniach
Microsoft Excel 2000
- 230 -
zostan
ą skasowane. Pamiętajmy, że obszar kasowania mógłby mieć
bardzo zło
żoną postać i wtedy zysk z użycia makra byłby wyrażnie
widoczny!
Uruchamianie makra z menu jest raczej uci
ążliwe. Wygodnym
sposobem jest umieszczenie na arkuszu odpowiednio opisanego
przycisku, który zostanie zwi
ązany z naszym makrem. Przycisk jest
jednym z elementów paska Formularze, znanego ju
ż z zastosowań
we wcze
śniejszych zadaniach.. Wybór przycisku i narysowanie go na
arkuszu, powoduje wy
świetlenie okna dialogowego z listą wcześniej
utworzonych makr. Wybór makra powoduje powi
ązanie go z
przyciskiem.
Uwaga!
Zwykłe klikni
ęcie w przycisk powoduje uruchomienie związanego z
nim makra, natomiast zmiany samego przycisku, np. zmiana rozmiaru
lub napisu na nim stan
ą się możliwe po kliknięciu w niego prawym
przyciskiem myszy.
Zadanie 66.
Utworzy
ć przycisk do uruchamiania makra utworzonego w
poprzednim zadaniu.
Zadanie 67.
Utworzy
ć makro, które wybranemu uprzednio (tj. przed
uruchomieniem makra) obszarowi nada tło koloru czerwonego po
naci
śnięciu przycisku opisanego „NA CZERWONO”
Microsoft Excel 2000
- 231 -
Zadanie 68.
Zarejestrowa
ć makro (uruchamiane przyciskiem), które tworzy
podwójn
ą ramkę wokół (być może złożonego z wielu części)
wybranego obszaru.
Zadanie 69.
Zarejestrowa
ć makra:
a) tworz
ące sumy pośrednie dla sprzedawców w arkuszu
"Sprzeda
ż" w pliku sprzedaż.xls (zob. zadanie 44)
b) powoduj
ące powrót listy do poprzedniej postaci
Utworzy
ć dla nich przyciski.
Makro mo
żemy uruchomić także własnym przyciskiem
umieszczonym na pasku narz
ędziowym. Tworzymy go poprzez
Narz
ędzia | Dostosuj | Polecenia | Kategorie | pozycja: Makra a w
prawym panelu wybieramy Przycisk niestandardowy i przeci
ągamy
go na pasek narz
ędziowy. Następnie klikając na nim prawym
przyciskiem myszy mo
żemy (wg podręcznego menu) zmienić wygląd
przycisku i przypisa
ć doń makro.
Gdy formularz Narz
ędzia | Dostosuj jest aktywny można
zlikwidowa
ć dodany przycisk przeciągając go poza paski narzędzi.
Makra i Visual Basic for Applications (VBA)
W procesie rejestracji Excel zapami
ętuje makro w postaci
procedury j
ęzyka Visual Basic for Applications (VBA). Język ten jest
rozbudowanym
narz
ędziem
tworzenia
nawet
bardzo
skomplikowanych aplikacji. Poni
żej podamy nieco wyrywkowych
informacji o nim, by zainspirowa
ć Czytelnika do dalszego
poznawania go we własnym zakresie.
Microsoft Excel 2000
- 232 -
Menu Narz
ędzia | Makro | Makra, potem wybór myszą
makra i nast
ępnie przycisk „Edycja” powoduje otwarcie okna edytora
VBA. Makro ma posta
ć procedury (inaczej podprogramu, czyli ang.
subroutine). Makro utworzone w Zadaniu 65 w VBA ma posta
ć:
Sub Wyczy
ść()
Range(”D4:D10”).Select
Selection.ClearContents
End Sub
Makro ko
ńczy się wierszem End Sub, a każdy wiersz jest
poleceniem w formie instrukcji j
ęzyka VBA. Tworząc różne makra i
podgl
ądając teksty procedur można się dowiedzieć w jaki sposób
VBA zapisuje interesuj
ące nas polecenia. Instrukcje te można
nast
ępnie zmieniać, kasować i dopisywać tworząc nowe polecenia.
Uwaga!
1. Z okna edytora wraca si
ę do arkusza obliczeniowego wybierając z
paska narz
ędzi przycisk Pokaż Microsoft Excel lub przycisk
Microsoft Excel... na pasku zada
ń u dołu ekranu.
2. Je
żeli użytkownik nie zadecyduje inaczej, wszystkie makra
zapisywane s
ą automatycznie razem ze skoroszytem i są dostępne
po jego otwarciu.
Zadanie 70.
Jak b
ędzie wyglądać instrukcja VB zaznaczania obszaru składającego
si
ę jednocześnie z pól B1:B5 i C3:D5 i E8? (Zarejestrować
odpowiednie makro i je obejrze
ć).
Microsoft Excel 2000
- 233 -
Pisanie procedur w VBA
Now
ą procedurę możemy utworzyć pisząc w module
instrukcje uj
ęte między wiersze Sub... oraz End Sub zamiast
rejestrowa
ć nasze działania na arkuszu obliczeniowym. Co więcej,
napisana procedura mo
że mieć większe możliwości niż mechanizm
rejestrowanego makra: w szczególno
ści żadne kombinacje funkcji
arkusza obliczeniowego nie pozwalaj
ą na automatyczne zmiany
zawarto
ści komórek arkusza, które nie zawierają formuł. Ale
u
żywając elementarnych instrukcji języka VB można tego dokonać
bardzo łatwo!
Zadanie 71.
Utworzy
ć procedurę w VBA, która spowoduje przenoszenie wartości
umieszczanych w komórce A1 kolejno do komórek E1, E2, E3 ...
tworz
ąc tam ciąg danych. W wielu zagadnieniach można
wykorzystywa
ć taki mechanizm wprowadzania danych.
Rozwi
ązanie.
Otwieramy edytor VBA (dost
ęp do niego uzyskamy również przez
Narz
ędzia | Makro | Edytor Visual Basic). W module
programowym ustawiamy kursor przed lub za istniej
ącą procedurą i
piszemy w nowym wierszu:
Sub Lista
VB automatycznie poprawi ten wiersz i dopisze wiersz ostatni:
Sub Lista()
End Sub
Microsoft Excel 2000
- 234 -
Uwaga!
1. Je
żeli po otwarciu edytora nie widać m. in. modułów, trzeba
klikn
ąć w ikonę Eksplorator projektu
2. Je
żeli w skoroszycie nie istnieją żadne moduły, należy wybrać
Wstaw | Moduł (Insert | Module). Nie ma znaczenia, w którym z
modułów utworzymy procedur
ę
Tre
ść procedury powinna wyglądać następująco:
Sub Lista()
Static wiersz
wiersz = wiersz + 1
Cells(wiersz, 5).Value=Cells(1,1).Value
End Sub
Wiersz Static... definiuje zmienn
ą, która tak jak komórka
przechowuje warto
ść. Na początku jest to 0. Słowo Static przed
nazw
ą zmiennej zapewnia, że jej wartość nie "zginie" po zakończeniu
procedury .
Nast
ępny wiersz powoduje, że każde wywołanie procedury
zwi
ększy tę wartość o 1, czyli kolejno będzie to 1, 2, 3, itd.Trzeci
wiersz
odwołuje
si
ę do komórek arkusza za pomocą
Cells(nr_wiersza, nr_kolumny), pobieraj
ąc zawartość (.Value)
komórki A1 i umieszczaj
ąc ją w komórkach E1, potem (w drugim
wywołaniu) w E2 itd.
W ten sposób pobieramy cech
ę (w VBA cechę nazywamy
wła
ściwością) jednego obiektu (tu cechą jest wartość obiektu
komórka) i ustawiamy j
ą jako cechę drugiego obiektu (tu: kolejnych
komórek kolumny E, tj. 5-tej kolumny arkusza).
Pisz
ąc złożone makra, czyli programując w języku VB można
wykona
ć dowolnie skomplikowane operacje na danych – stworzyć
Microsoft Excel 2000
- 235 -
skomplikowany system przetwarzania danych, zdefiniowa
ć nowe
potrzebne nam funkcje, korzysta
ć z zewnętrznych baz danych itp.
Uwaga!
Pomoc | Spis tre
ści | Informacje o programowaniu zawiera
znale
żć całkowity opis języka VBA wraz z przykładami (w języku
angielskim).
Zadanie 72.
Napisa
ć procedurę, która będzie pobierać za każdym kolejnym
wywołaniem warto
ść z komórki B2 i w kolumnie C wyświetlać
kolejno stan narastaj
ący (sumę). Komórkę B2 można tu traktować
jako kolejn
ą wpłatę/wypłatę na/z konta, a kolumnę C uznać za historię
stanu konta.
Uwaga!
Jest to niewielka modyfikacja demonstrowanego przykładu – trzeba
zdefiniowa
ć dodatkową zmienną „stan”.
Zadanie 73.
Napisa
ć procedurę stanowiącą rozszerzenie procedury z Zadania 71 i
która spowoduje,
że liczby dodatnie przeniesione do kolumny E będą
wy
świetlane na pastelowobłękitnym tle, a ujemne na żółtym. Zero ma
by
ć wyświetlane bez zmian.
Rozwi
ązanie.
Dla sprawdzenia znaku liczby nale
ży zastosować konstrukcję VB
odpowiadaj
ącą funkcji arkusza JEŻELI().
Microsoft Excel 2000
- 236 -
Jest to zło
żona instrukcja IF ...:
IF warunek THEN
instrukcja
...
instrukcja
ELSE
instrukcja
...
instrukcja
END IF
Oczywi
ście dozwolone jest zagłębianie tego typu instrukcji.
Zastosowanie tej konstrukcji w zadaniu mo
że mieć postać:
Sub Lista_kolor()
Static wiersz
wiersz = wiersz + 1
Cells(wiersz, 5).Value = Cells(1, 1).Value
Cells(wiersz, 5).Select
If Selection.Value >0 Then
Selection.Interior.ColorIndex = 34
Else
Selection.Interior.ColorIndex = 36
End If
End Sub
Uwaga!
Numery kolorów poznamy rejestruj
ąc makro zmiany tła.
Microsoft Excel 2000
- 237 -
Funkcje u
ż
ytkownika w VBA
Utworzenie własnej funkcji (prywatnej, czyli tzw. funkcji
u
żytkownika - w odróżnieniu od funkcji wbudowanych Excela)
poka
żmy na przykładzie funkcji średnia_ważona(...). Takiej funkcji
nie ma w zestawie funkcji wbudowanych Excela, a jest to do
ść
typowy algorytm u
żywany w różnych zagadnieniach.
Funkcj
ę tę można wyrazić poprzez formułę z funkcji wbudowanych w
nast
ępujący sposób:
= SUMA.ILOCZYNÓW(A;X) / SUMA(A)
gdzie A – n-elementowy wektor współczynników wagowych (tzw.
wag), a X to n-elementowy wektor danych wej
ściowych (oczywiście
wpisuj
ąc formułę do arkusza zamiast symboli A i X podajemy
odpowiednie zakresy komórek)
Utworzymy jednak własn
ą wersję tej funkcji. Przyjmiemy, że wektory
A i X s
ą w postaci wierszowej. Uniwersalność w tym zakresie
(wektory jako wiersze lub kolumny) byłaby komplikowaniem funkcji.
Funkcj
ę tworzymy tymi samymi środkami co procedurę Sub().
Ró
żnice polegają na tym, że
1. Funkcja posiada zwykle argumenty, które trzeba zdefiniowa
ć w
nagłówku funkcji
2. Funkcja zwraca warto
ść określonego typu
3. Funkcji nie wi
ążemy z przyciskiem, tylko wpisujemy ją w
arkuszu w formułach, tak jak funkcje wbudowan
ą Excela.
Microsoft Excel 2000
- 238 -
Oto tekst proponowanej funkcji.
Function
średnia_ważona(A As Range, X As Range)
' W nazwie nie mo
żna użyć kropki jak w f-cjach wbudowanych
' Zmienne nie musz
ą być deklarowane ani inicjowane – w tej funkcji
s
ą to dl, i, sil, sa
' Pocz
ątkowe wartości zmiennych to 0
dl = X.Columns.Count ' Pobranie liczby elementów(kolumn)
wiersza wektora X (czyli tak
że wektora
A)
For i = 1 To dl ' P
ętla kolejnego pobierania komórek
wektorów: od i=1 a
ż do i=dl
sil = A.Cells(1, i).Value * X.Cells(1, i).Value + sil 'dodawanie
kolejnych
iloczynów
do
sumy
sa = A.Cells(1, i).Value + sa 'sumowanie wag
Next i 'Powtarzanie p
ętli – powrót do pobierania kolejnej pary
komórek z A i X
średnia_ważona = sil / sa 'Obliczony iloraz staje się
wynikiem funkcji
End Function
Uwagi
1. Tekst po apostrofach to komentarz, czyli wyja
śnienia, które nie
nale
żą do algorytmu i są ignorowane przez VB.
2. A i X s
ą zdefiniowane jako obiekty typu zakres (czyli Range).
Microsoft Excel 2000
- 239 -
3. W przykładzie wida
ć tzw. obiektowy dostęp do cech obiektów –
notacj
ę kropkową. W VBA są setki zdefiniowanych obiektów
(opis dost
ępny w pomocy), choć zwykle posługiwanie się nimi jest
do
ść
intuicyjne,
np.
X.Columns.Count to liczba (Count) komórek wiersza X (czyli
liczba kolumn - Columns), a
A.Cells(1,i).Value to warto
ść (Value) i-tej komórki (Cells)
wektora (a wła
ściwie zakresu!) A.
Nowo utworzona funkcja jest dost
ępna w arkuszu na takiej samej
zasadzie jak funkcje wbudowane i nale
ży do grupy Funkcje
u
żytkownika.
Zdarzenia i procedury zdarzeniowe
Zdarzenia to sytuacje, które zachodz
ą w komputerze i na które
program w VB mo
że reagować. Pewne procedury (o z góry
okre
ślonych nazwach) czyli procedury zdarzeniowe można
wyposa
żyć w akcje (zdefiniować ciąg instrukcji). Procedury te
zostan
ą wykonane, gdy zdarzenie „zajdzie”, np. gdy aplikacja
wystartuje, gdy załadowany zostanie skoroszyt itp.
Do zdarze
ń należą także momenty czasowe. Tu podamy przykład
cyklicznego wywoływania pewnej procedury. Mo
żna znależć wiele
zastosowa
ń dla cyklicznego wywoływania pewnych zadań. Pokazany
prosty przykład b
ędzie co 5 sekund zwiększał liczbę w komórce A1.
U
żyjemy procedury o nazwie OnTime:
Sub Zacznij()
Microsoft Excel 2000
- 240 -
Application.OnTime
Now
+
TimeValue("00:00:05"),
"Arkusz1.wypisz"
End Sub
Sub wypisz()
Cells(1, 1).Value = Cells(1, 1).Value + 1
Application.OnTime
Now
+
TimeValue("00:00:05"),
"Arkusz1.wypisz"
End Sub
Procedura Zacznij jest makrem, które mo
żemy wywołać z menu lub
podł
ączyć do przycisku. Wywołuje ona procedurę OnTime, która
rejestruje procedur
ę wypisz jako procedurę zdarzeniową. Procedura
wypisz ma zosta
ć wykonana po 5 sekundach od chwili rejestracji
(czas bie
żący dostarcza funkcja Now języka Visual Basic).
Zwi
ększenie komórki A1 o 1 jest głównym zadaniem procedury
wypisz. Na zako
ńczenie odświeżane jest wywołanie OnTime, by
zaszło kolejne takie samo zdarzenie. Je
śli umieścimy w komórce A1
jak
ąkolwiek liczbę i wykonamy procedurę Zacznij, to zainicjujemy
cykliczne zwi
ększanie się komórki A1 co mniej więcej 5 sekund.
Aby wi
ęcej dowiedzieć się o procedurze OnTime należy zapoznać się
z opisem VB.