Microsoft Word - pi_st2009z_lab06_excel

PODSTAWY INFORMATYKI — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2009

Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

1/6

Z.ST

Laboratorium 6

1 Makropolecenia — wprowadzenie

Często powtarzane zadania można zautomatyzować za
pomocą

makropoleceń (inaczej: makr). Makro to seria

poleceń  wykonywanych  w  module  języka  MS  Visual
Basic for Applications (VBA), która może być wielokrotnie
uruchamiana,  gdy  tylko  zachodzi  taka  potrzeba.  Makro
może  np.  automatycznie  filtrować  dane,  formatować
wygląd tabel czy przenosić zawartość komórek.

Aby tworzyć proste makra języka VBA i używać ich, nie trzeba być zaawansowanym użytkownikiem.
Typowi użytkownicy aplikacji Excel mogą po prostu skorzystać z funkcji

rejestrowania makr. Excel

rejestruje wykonane czynności i zamienia je do postaci makra języka VBA. Po uruchomieniu makra
Excel ponownie wykona te operacje. Bardziej zaawansowani użytkownicy mogą

tworzyć kod

ródłowy nakazujący Excelowi wykonywanie zadań, których rejestracja nie jest możliwa.

Makropolecenia pozwalają nie tylko na operowanie na obiektach w obrębie Excela, ale mogą także
oddziaływać na inne aplikacje czy system operacyjny. Stąd możliwe jest napisanie makra o działaniu
destrukcyjnym. Wykorzystują to niekiedy autorzy wirusów. Dlatego

należy zachować ostrożność

przy uruchamianiu nieznanych nam makr, a nawet otwieraniu skoroszytów zawierających makra,
bo niektóre mogą być uruchamiane automatycznie bez pytania o zgodę użytkownika.

W Excelu 2007 narzędzia makr dostępne są w sekcji Kod na karcie Deweloper. Jeśli nie jest widoczna
należy ją włączyć:

http://office.microsoft.com/pl-pl/excel/HA101730521045.aspx

. Można też skorzystać

z sekcji Makra na karcie Widok.

2 Formatowanie komórek

Zacznijmy od prostego makra, które będzie formatowało wygląd zaznaczonych komórek.

2.1 Rejestrowanie makra

1. Otwórz skoroszyt

agencja_reklamowa2.xls

(materiały

pomocnicze na stronie WWW).

2. Przejdź do arkusza

dane fikcyjne.

3. Z karty Deweloper wybierz Zarejestruj makro.

4. Pojawi się okno Rejestrowanie makra. Podaj

nazwę:

FormatowanieKomorek

oraz

upewnij się, że makro będzie przechowywane
w bieżącym skoroszycie. Kliknij OK, by
przejść do właściwego rejestrowania makra.

5. Od

tego

momentu

rozpoczyna

się

rejestrowanie

wszystkich

czynności

wykonywanych w Excelu (za wyjątkiem
ruchów wskaźnikiem myszy). Powinien o tym
przypominać

niewielki

mały

niebieski

kwadrat

w lewym dolnym rogu paska stanu u dołu okna
Excela. Klikając na niego lub na podobną ikonę
z napisem

Zatrzymaj

rejestrowanie

karcie

Deweloper kończy się rejestrowanie makra (

jeszcze

tego nie rób!

Legenda:

aplikacja, program, arkusz

opcja menu, funkcja

plik, folder, ścieżka

pole, kod pola

tekst do wpisania

klawisz

PODSTAWY INFORMATYKI — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2009

Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

2/6

Z.ST

6. Nie zaznaczaj teraz żadnej komórki, a jedynie posługuj się

ikonami sekcji Czcionka na karcie Narzędzia główne:
pogrubienie, żółty kolor wypełnienia, czerwony kolor czcionki.

7. Zatrzymaj rejestrowanie makra, klikając na niebieskim

kwadracie na pasku stanu. Rejestrowanie makra można też
zatrzymać poprzez Deweloper

Kod

Zatrzymaj

rejestrowanie.

2.2 Testowanie makra

1. Zaznacz blok dowolnych komórek w arkuszu

dane fikcyjne.

2. Uruchom makro: Deweloper

Kod

Makra (lub Alt+F8), a następnie wskaż
makro

FormatowanieKomorek i kliknij

na przycisk Uruchom.

3. Jeśli

makro

zostało

poprawnie

zarejestrowane,  to  po  jego  wykonaniu
zaznaczone  komórki  zmienią  wygląd  na
taki,  jaki  został  ustawiony  podczas
rejestrowania makra.

4. Gdyby w czasie rejestrowania makra

użytkownik zaznaczył konkretną komórkę
(lub

zakres

komórek),

wówczas

uruchomienie

tego

makra

spowodowałoby sformatowanie właśnie
tej komórki (zakresu) zamiast obszaru
zaznaczonego przed uruchomieniem.

3 Dodawanie zleceń

W arkuszu

formularz znajduje się odwzorowanie nagłówków tabeli z arkusza dane fikcyjne. Aby

ograniczyć poruszanie się po tym arkuszu, ukryto w nim kolumny od C do IV oraz wiersze od 10 do
65536. Do tego w komórkach B6, B7 i B8 ułatwiono wprowadzanie danych przez użycie list
rozwijalnych z narzędzi sprawdzania poprawności. Kolejne ułatwienie znajduje się w komórce B2,
gdzie specjalna formuła wstawia kolejny numer zlecenia, większy o 1 od największego, jaki do tej pory
znajduje się w arkuszu

dane fikcyjne.

Stworzymy teraz makro, które pozwoli nam po wpisaniu informacji o zleceniu do arkusza

formularz

dodać je do listy zleceń w formularzu

dane fikcyjne.

Zacznijmy od wprowadzenia przykładowych danych do arkusza

formularz:

PODSTAWY INFORMATYKI — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2009

Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

3/6

Z.ST

3.1 Rejestrowanie makra

1. Mając wyświetlony arkusz

formularz z menu Narzędzia wybierz Deweloper

Kod

Zarejestruj

makro

2. Pojawi się okno Rejestrowanie makra. Podaj nazwę:

DodajProjekt

oraz upewnij się, że makro będzie

przechowywane w bieżącym skoroszycie. Kliknij
OK, by przejść do właściwego rejestrowania makra.

3. Od tego momentu rozpoczyna się rejestrowanie

wszystkich czynności wykonywanych w Excelu (za
wyjątkiem ruchów wskaźnikiem myszy).

4. Zaznacz w arkuszu

formularz komórki B2:B8

i skopiuj je do Schowka.

5. Przejdź do arkusza

roboczy i zaznacz w nim

komórkę A1.

6. Korzystając z opcji Wklej specjalnie… wklej

skopiowane wcześniej komórki z wybranymi opcjami
wklejania wartości i transpozycji (por. okienko obok).
Komórki A1:G1 powinny wypełnić się danymi.

7. Nie

usuwając

zaznaczenia

komórek

A1:G1

w arkuszu

roboczy, wybierz opcję Wytnij.

8. Przejdź do arkusza

dane fikcyjne i kliknij prawym

przyciskiem myszy na komórce A2. Z otwartego tym
sposobem  menu  kontekstowego  wybierz  opcję
Wstaw  wycięte  komórki.  Excel  otworzy  niewielkie
okno  z  pytaniem  o sposób  wstawienia  komórek.
Zaznacz opcję Przesuń komórki w dół.

9. Efektem będzie wstawienie do 2. wiersza arkusza

dane fikcyjne zawartości komórek pierwotnie
skopiowanych z arkusza

formularz. Będące

wcześniej w tabeli dane zostaną przesunięte
o jeden wiersz niżej.

10. Zaznacz komórkę C2 (znajduje się w niej wartość

wstawionego przed chwilą zamówienia) i ustaw dla niej format księgowy bez miejsc dziesiętnych.

11. To jeszcze nie koniec — niech makro czyści też informacje z formularza. Przełącz się do arkusza

formularz. Zaznacz komórki B3:B8 (zawartość komórki B2 zostawmy — ona automatycznie
nadaje kolejny numer zleceniom), a następnie usuń z nich zawartość (np. klawiszem Delete ).

12. Zatrzymaj rejestrowanie makra klikając na niebieskim kwadracie na pasku stanu. Rejestrowanie

makra można też zatrzymać poprzez Deweloper

Kod

Zatrzymaj rejestrowanie.

3.2 Uruchamianie makra

1. Wprowadź przykładowe dane do arkusza

formularz:

PODSTAWY INFORMATYKI — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2009

Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

4/6

Z.ST

2. Uruchom makro: Deweloper

Kod

Makra (lub Alt+F8), a następnie wskaż makro

DodajProjekt i kliknij na przycisk Uruchom.

3. Jeśli

makro

zostało

poprawnie

zarejestrowane, to po jego wykonaniu
aktywny

będzie

arkusz

formularz,

w którym przy numerze zlecenia będzie
widoczny numer 33, a pozostałe pola
(komórki B3:B8) będą puste.

4. Przejdź do arkusza

dane fikcyjne

i zobacz, czy w wierszu nr 2 znajdują się
dane

temat

zlecenia

32,

a w niższych

wierszach

odpowiednio

wpisy o zleceniach 31, 30…1.

5. Jeśli efekt jest zgodny z planem, możesz

sobie

pogratulować

dokładności

wykonania poleceń. Jeśli jednak makro
nie zadziałało, jak powinno, wykonaj
jeszcze raz polecenia z punktu 3.1.

3.3 Przycisk uruchamiający makro

Aby ułatwić korzystanie z makropolecenia, stwórzmy w arkuszu

Formularz przycisk, po naciśnięciu

którego zostanie uruchomione stworzone
przed chwilą makro.

1. Z paska sekcji Formanty na karcie

Deweloper

wstaw

Przycisk

(formant

formularza).

2. Narysuj przycisk w komórce 9. wiersza.

3. Excel

wyświetli

okno

służące

przypisywaniu makra do przycisku.

4. Zaznacz makro

DodajProjekt i kliknij OK.

5. Zmień opis przycisku na

Dodaj projekt.

6. Wypełnij komórki B3:B8 arkusza formularz

wymyślonymi przez siebie danymi.

7. Kliknij przycisk

Dodaj projekt.

8. Sprawdź efekty.

4 Kod VBA

Każde z zarejestrowanych makr zostaje zapisane jako ciąg poleceń języka VBA. Instrukcje tego
języka są w miarę czytelne dla osób znających język angielskim, a podstawy posługiwania się nim nie
są skomplikowane.

Warto o tym wiedzieć, bo czasami można uzyskać przydatny efekt najpierw rejestrując makro,
a potem zmieniając nieznacznie jego kod, by lepiej pasował, do naszych potrzeb, czego na przykład
nie daje się osiągnąć za pomocą samego nagrywania czynności.
Aby wyświetlić kod konkretnego makra można wybrać opcję Deweloper

Kod

Makra, wybrać

konkretne makro i nacisnąć przycisk Edycja.

Spróbujmy stworzyć proste makra za pomocą napisania kilku linijek kodu VBA. W tym celu stwórz
nowy, pusty skoroszyt. W tym skoroszycie:

1. Na karcie Deweloper wybierz opcję Visual Basic (albo naciśnij Alt + F11 ).

2. Po lewej stronie powinien być widoczny Eksplorator projektu. Kliknij obszarze dotyczącym

bieżącego skoroszytu prawym przyciskiem myszy i z menu kontekstowego wybierz Insert
Module, by wstawić do skoroszytu moduł przechowujący kod VBA.

3. Pojawi się grupa Modules, a w niej element

Module1. Po prawej stronie okna edytora VBA

powinno otworzyć się okno modułu. Tutaj będziemy wpisywali makra.

PODSTAWY INFORMATYKI — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2009

Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

5/6

Z.ST

4.1 Okno komunikatu

W edytorze VBA napisz makro o nazwie

Powitanie z następującą zawartością:

Sub Powitanie()

MsgBox "Cześć! Jak się masz?", vbQuestion

End Sub

Uruchom makropolecenie (Run /

/ F5 ).

4.2 Pobieranie danych, zmienne

Teraz nauczymy się odczytywać wartość z komórki, przypisywać ją do zmiennej, a tę zmienną używać
przy wyświetleniu okna z powitaniem:

Arkusz1 w komórkę A1 wpisz swoje imię.

Wróć do edytora VBA i zmodyfikuj makro

Powitanie, by wyglądało tak:

Sub Powitanie()

imie = Range("A1").Value

MsgBox "Cześć " & imie & "! Jak się masz?", vbQuestion

End Sub

Uruchom makropolecenie.

4.3 Wprowadzanie danych

Teraz nauczymy się wpisywać wartość do komórki. Wpisz jako nowe makro poniżej makra

Powitanie:

Sub UlubionyKolor()

kolor = InputBox("Jaki jest Twój ulubiony kolor?")

Range("A2").Value = kolor

End Sub

Uruchom to makro. Zobacz, co pojawiło się w komórce A2.

4.4 Instrukcja warunkowa

Zależnie od napotkanych warunków makro może wykonywać różne partie swojego kodu.

Przepisz następujący kod jako nowe makro, a następnie je uruchom:

Sub Decyzja()

dodomu = MsgBox("Czy chcesz już iść do domu?", vbYesNo)

If dodomu = vbYes Then

MsgBox "Najpierw zrób wszystkie zadania!", vbCritical

Else

MsgBox "I bardzo dobrze! Rób dalej zadania.", vbInformation

End If

Call Decyzja

End Sub

Na końcu makro samo uruchamia się znów (Call Decyzja). Aby wyjść z tego makra, musisz
wcisnąć klawisze Control + Pause , potem wybrać którąkolwiek z opcji okienka dialogowego,
a w kolejnym oknie przycisk End.

PODSTAWY INFORMATYKI — laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2009

Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

6/6

Z.ST

4.5 Pętla

Często przydatne jest wielokrotne wykonanie tego samego zestawu instrukcji. Do tego
w programowaniu używane są tzw. pętle. Zastosujemy jeden z jej typów (For…Next) do stworzenia
animowanego wykresu. Przepisz następujący kod jako nowe makro:

Sub Petelka()

For x = 1 To 100

Range("A4").Value = x

Range("A5").Value = 100 - x

Application.Wait (Now)

Next x

End Sub

To makro 100 razy wstawia wartości do komórek A4 i A5, za każdym razem robiąc krótką przerwę.
W pierwszej komórce (A4) odlicza od 1 do 100, a w drugiej (A5) w przeciwnym kierunku. Uruchom
makro i zobacz, co się stanie.

Efekt  jest  mało  widowiskowy.  Dlatego  zaznacz  komórki  A4  i  A5  i  wstaw  wykres  ostrosłupowy
grupowany.  Ustaw  stałe  wartości  dla  osi  wartości  (0  dla  minimum  i  100  dla  maksimum).  Teraz
uruchom  makro

Petelka i zobacz, co będzie się działo z wykresem. Możesz spróbować z innymi

typami wykresów.

5 Pytania sprawdzające wiedzę i umiejętności

1. Co to jest makro?

2. Do czego są przydatne makra?

3. Jak zarejestrować makro?

4. Gdzie Excel może przechowywać makra?

5. Jak należy zapisać skoroszyt w aplikacji MS Excel 2007, by zapisane zostały również makra?

6. Czy w trakcie nagrywania makra rejestrowany jest upływający czas oraz ruchy kursorem myszy?

7. Jak można uruchamiać makra?

8. Dlaczego uruchamianie makr może być niebezpieczne? Jak zmniejszyć ryzyko uruchomienia

szkodliwego makra?

9. Co to jest Visual Basic for Applications (VBA)?

10. Jaki jest związek pomiędzy rejestrowanymi makrami a językiem VBA?

11. Jak zarejestrować i zastosować makro, którego celem będzie przełączenie się z jednego arkusza

do innego, np. z

Arkusz1 do Arkusz2?

12. Czy wiesz, jak zmodyfikować makro

Petelka, by po zakończeniu pętli wykres animował się

w przeciwnym kierunku, czyli by pierwszy ze słupków najpierw urósł do 100, a potem się
zmniejszył do 0, a drugi odwrotnie — zmalał do 0, a potem urósł do 100?

6 Literatura

•

John Walkenbach.

Excel 2007 PL. Biblia. Helion. Gliwice 2007, s. 731–756.

•

John Walkenbach.

Excel 2003 PL. Programowanie w VBA. Helion. Gliwice 2004.

•

Maria Kopertowska, Witold Sikorski.

Europejski Certyfikat Umiejętności Komputerowych.

Poziom zaawansowany. Arkusze kalkulacyjne. Wydawnictwo Naukowe PWN. Warszawa 2006.
s. 136–141.

•

Mirosław Lewandowski.

Tworzenie makr w VBA dla Excela 2003/2007. Helion. Gliwice 2008.

•

Mirosław Lewandowski.

VBA dla Excela 2003/2007. Leksykon kieszonkowy. Helion, Gliwice

2007.

•

Makra.

http://office.microsoft.com/pl-pl/excel/CH101001571045.aspx