plik

INFORMATYKA EKONOMICZNA

— laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

1/6

ST.IiE

Laboratorium 6

1.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.

1.2

Formatowanie komórek

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

1.2.1 Rejestrowanie makra

Otwórz skoroszyt
agencja_reklamowa2.xlsm

(materiały

pomocnicze).

Przejdź do arkusza dane fikcyjne.

3. Z karty Deweloper wybierz Zarejestruj makro.

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.

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 na karcie Deweloper kończy się rejestrowanie makra
(

jeszcze tego nie rób!

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.

Zatrzymaj

rejestrowanie

makra,

klikając

Legenda:

aplikacja, program, arkusz, pole

opcja menu, funkcja

plik, folder, ścieżka

KLAWISZ

tekst do wpisania

miejsce częstych błędów

INFORMATYKA EKONOMICZNA

— laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

2/6

ST.IiE

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



Kod



Zatrzymaj rejestrowanie.

1.2.2 Testowanie makra

Zaznacz blok dowolnych komórek w arkuszu dane fikcyjne.

1. Uruchom makro: Deweloper



Kod



Makra (lub Alt+F8), a

następnie wskaż makro

FormatowanieKomorek
i kliknij

przycisk

Uruchom.

Jeśli

makro

zostało

poprawnie zarejestrowane,
to

jego

wykonaniu

zaznaczone

komórki

zmienią wygląd na taki, jaki
został ustawiony podczas
rejestrowania makra.

3. Gdyby

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.

1.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:

1.3.1 Rejestrowanie makra

Mając wyświetlony arkusz formularz z menu Narzędzia wybierz Deweloper



Kod



Zarejestruj

makro

INFORMATYKA EKONOMICZNA

— laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

3/6

ST.IiE

Pojawi się okno Rejestrowanie makra. Podaj nazwę:

DodajProjekt

oraz upewnij się, że makro będzie

przechowywane w bieżącym skoroszycie. Kliknij
OK, by prz

ejść do właściwego rejestrowania makra.

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.

Przejdź do arkusza roboczy i zaznacz w nim
komórkę A1.

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.

Nie usuwając zaznaczenia komórek A1:G1
w arkuszu roboczy

, wybierz opcję Wytnij.

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ół.

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.

1.3.2 Uruchamianie makra

Wprowadź przykładowe dane do arkusza formularz:

2. Uruchom makro: Deweloper



Kod



Makra

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

DodajProjekt i kliknij na przycisk Uruchom.

INFORMATYKA EKONOMICZNA

— laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

4/6

ST.IiE

Jeśli

makro

zostało

poprawnie

zarejestrowane, to po jego wykonaniu
aktywny

będzie

arkusz

formularz,

którym przy numerze zlecenia będzie

widoczny numer 33, a

pozostałe pola

(komórki B3:B8) będą puste.

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.

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 1.3.1.

1.3.3 Przycisk urucham

iają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. Narysu

j przycisk w komórce 9. wiersza.

Excel

wyświetli

okno

służące

przypisywaniu makra do przycisku.

4. Zaznacz makro DodajProjekt i kliknij OK.

Zmień opis przycisku na Dodaj projekt.

Wypełnij

komórki

B3:B8

arkusza

formularz wymyślonymi przez siebie
danymi.

7. Kliknij przycisk Dodaj projekt.

Sprawdź efekty.

Zapisz plik jako skoroszyt programu Excel z obsługą makr (XLSM).

1.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 w nowym pustym skoroszycie 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 ).

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.

Pojawi się grupa Modules, a w niej element Module1. Po prawej stronie okna edytora VBA
powin

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

INFORMATYKA EKONOMICZNA

— laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

5/6

ST.IiE

1.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 ).

1.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:
W 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.

1.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.

1.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ż skończyć pracę domową?", 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.

INFORMATYKA EKONOMICZNA

— laboratorium

arkusz kalkulacyjny: MS Excel

mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej

6/6

ST.IiE

1.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.

2 Pytan

ia sprawdzające wiedzę i umiejętności

Sprawdź, czy potrafisz odpowiedzieć na poniższe pytania. Pomocą, poza wykonanymi ćwiczeniami,
służy też polecana niżej literatura.
4. Co to jest makro?

Do czego są przydatne makra?

Jak zarejestrować makro?

Gdzie Excel może przechowywać makra?

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

9. Czy w trakcie nagrywania makra

rejestrowany jest upływający czas oraz ruchy kursorem myszy?

10.

Jak można uruchamiać makra?

11.

Dlaczego uruchamianie makr może być niebezpieczne? Jak zmniejszyć ryzyko uruchomienia
szkodliwego makra?

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

13.

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

14.

Jak zarejestrować i zastosować makro, którego celem będzie przełączenie się z jednego arkusza
do innego, np. z Arkusz1 do Arkusz2?

15.

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?

3 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. 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

Document Outline