Microsoft Excel 2000
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.
- 228 -
 Microsoft Excel 2000
Uwaga!
1. W czasie rejestracji makra na pasku stanu widoczny jest napis
Rejestruj.
2. W czasie rejestracji powinien być także widoczny1 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ć poprawnie2
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
- 229 -
 Microsoft Excel 2000
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
- 230 -
 Microsoft Excel 2000
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.
- 231 -
 Microsoft Excel 2000
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ć).
- 232 -
 Microsoft Excel 2000
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
- 233 -
 Microsoft Excel 2000
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ć
- 234 -
 Microsoft Excel 2000
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().
- 235 -
 Microsoft Excel 2000
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.
- 236 -
 Microsoft Excel 2000
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.
- 237 -
 Microsoft Excel 2000
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).
- 238 -
 Microsoft Excel 2000
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()
- 239 -
 Microsoft Excel 2000
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.
- 240 -