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.