Ocena brak

Makra, przyciski i Visual Basic for Applications

Autor /Liliana Dodano /26.03.2011

Wymagany Adobe Flash Player wesja 10.0.0 lub nowsza.

praca w formacie pdf Makra, przyciski i Visual Basic for Applications

Transkrypt

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
poprzednim zadaniu.

uruchamiania

makra

utworzonego

w

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
wektorów: od i=1 a do i=dl

' Pętla kolejnego pobierania komórek

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
Next i

'sumowanie wag

'Powtarzanie pętli – powrót do pobierania kolejnej pary
komórek z A i X

średnia_wa ona = sil / sa
wynikiem funkcji

'Obliczony iloraz staje się

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
"Arkusz1.wypisz"

Now

+

TimeValue("00:00:05"),

End Sub
Sub wypisz()
Cells(1, 1).Value = Cells(1, 1).Value + 1
Application.OnTime
"Arkusz1.wypisz"

Now

+

TimeValue("00:00:05"),

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 -

Podobne prace

Do góry