Makropolecenia w arkuszu kalkulacyjnym i wybrane możliwości wbudowanego języka programowania

Wszystkie treści na stronie ir.migra.pl chronione są prawami autorskimi. Więcej informacji znajdziesz tutaj.

Uwaga: Zapoznaj się wcześniej z tematami B1 i B2 z podręcznika „Teraz bajty. Informatyka dla szkół ponadpodstawowych. Zakres podstawowy. Klasa II”. Wykonaj zawarte tam ćwiczenia i zadania.

Spis treści

1. Rejestrowanie makra w arkuszu kalkulacyjnym
2. Przypisywanie makra do przycisku
3. Edytor języka programowania Visual Basic
4. Modyfikowanie makra w edytorze VBE
   1. Stosowanie instrukcji iteracyjnej for w języku VBA
   2. Stosowanie instrukcji warunkowej if w języku VBA
5. Otwieranie dokumentów zawierających makra

1. Rejestrowanie makra w arkuszu kalkulacyjnym

Problem: Przygotowaliśmy tabelę arkusza kalkulacyjnego z tabliczką mnożenia liczb od 1 do 20. Chcielibyśmy jak najprościej wyróżnić komórki z kwadratami liczb, leżące w tabliczce na przekątnej.

Podczas pracy w arkuszu kalkulacyjnym często wykonujemy ciąg takich samych czynności. Możemy je wykonać krok po kroku, stosując poznane do tej pory funkcje arkusza (tu: formatowanie komórek), ale w przypadku dużych arkuszy czynności te zajęłyby dużo czasu oraz byłyby monotonne.

Niektóre programy, m.in. arkusze kalkulacyjne i edytory tekstu, umożliwiają zapisanie powtarzających się czynności w postaci makropolecenia (inaczej makrodefinicji, w skrócie: makra).

Aby utworzyć makro, nie jest konieczna znajomość języka programowania oraz metod programowania. Po włączeniu rejestrowania makra wystarczy po prostu wykonać odpowiednie czynności, a odpowiadający im ciąg instrukcji zostanie zapisany przez program w postaci makra.

Przed utworzeniem nowego makra warto dokładnie zaplanować wszystkie czynności, ponieważ wszelkie błędy i poprawki będą niepotrzebnie rejestrowane, a następnie odtwarzane podczas wykonywania makra.

Utworzone makro należy nazwać. Makro może być wywoływane (uruchamiane) za pomocą klawisza lub klawiszy skrótu, opcji menu lub przycisku na pasku narzędzi.

Po zakończeniu rejestracji makra każde jego uruchomienie spowoduje wykonanie zarejestrowanych w nim czynności. Przed przystąpieniem do tworzenia makra warto przeczytać w Pomocy, jakie są zasady tworzenia i rejestrowania makr w danej wersji programu.

Przykład 1. Tworzenie makra poprzez rejestrowanie

Utworzymy makro, które będzie formatowało zaznaczoną komórkę tak, aby wprowadzony do niej tekst był wyrównany do prawej i pogrubiony, a komórka została obramowana i wyróżniona zielonym tłem.

1. Wybierz kartę Deweloper.
2. Wybierz rejestrowanie makra z odwołaniami względnymi (opcja Użyj odwołań względnych).
3. Uaktywnij pierwszą komórkę, która powinna zostać sformatowana: B2.
4. Na karcie Deweloper wybierz opcję rejestrowanie makra (np. Zarejestruj makro).
5. W oknie Rejestrowanie makra: nazwij makro (np. FormatujKomorke), ustal klawisz skrótu, określ miejsce przechowywania makra (np. Ten skoroszyt), dodaj opis (rys. 2.).
6. Rozpocznij rejestrację makra (kliknij przycisk OK).
7. Wykonaj czynności, które mają być zarejestrowane w makrze: wybierz pogrubienie tekstu, wyrównywanie do prawej strony w poziomie, zastosuj obramowanie i tło komórki.
8. Zatrzymaj rejestrację makra (kliknij przycisk Zatrzymaj rejestrowanie).

Uwaga: Zastosowanie odwołań względnych w makrze powoduje, że makro możemy zastosować również do komórki o innym adresie, ponieważ litera kolumny i numer wiersza odpowiednio się zmieniają.

Ćwiczenie 1. Rejestrujemy własne makro

1. Otwórz plik TB3_c1_Makro.xls. Utwórz makro na podstawie przykładu 1.
2. Zapisz plik pod tą samą nazwą właściwą, ale z obsługą makr (czyli z rozszerzeniem xlsm).
3. Kliknij komórkę C3 i uruchom makro, aby sprawdzić jego działanie.

2. Przypisywanie makra do przycisku

Do makra można przypisać przycisk, którego kliknięcie spowoduje uruchomienie makra. Przycisk możemy umieścić w obszarze tabeli arkusza.

Przykład 2. Przypisywanie makra do przycisku

Umieścimy w skoroszycie przycisk, do którego przypiszemy makro FormatujKomórke.

1. Na karcie  Developer wybierz opcję Wstaw.
2. Wybierz odpowiedni formant (z Kontrolek formularza) – rys. 5.
3. Kliknij na arkuszu miejsce, gdzie ma być umieszczony przycisk – otworzy się okno Przypisywanie makra.
4. W oknie Przypisywanie makra wybierz nazwę makra, które ma być przypisane do przycisku.

Ćwiczenie 2. Przypisujemy makro do przycisku

1. Utwórz przycisk wywołujący makro FormatujKomorke. Wstaw przycisk po prawej stronie tabliczki mnożenia.
2. Użyj przycisku do sformatowania komórki o adresie U21.
Wskazówka: Aby uruchomić makro z przycisku, kliknij najpierw komórkę, a potem przycisk.

Jeśli chcemy mieć łatwiejszy dostęp do utworzonych makr, możemy umieścić ikonę Makra na pasku narzędzi arkusza kalkulacyjnego Szybki dostęp (rys. 6.).
W tym celu należy otworzyć okno Opcje programu Excel (Plik/Opcje), w którym możemy dostosować pasek narzędzi Szybki dostęp, umieszczając na nim wybrane opcje.
Okno Opcje programu Excel możemy również otworzy z menu kontekstowego paska narzędzi Szybki dostęp, wybierając polecenie Dostosuj pasek narzędzi Szybki dostęp.

Ćwiczenie 3. Przypisujemy ikonę makr do paska narzędzi Szybki dostęp

Umieść ikonę Makra na pasku Szybki dostęp.

3. Edytor języka programowania Visual Basic

W programach z pakietu Microsoft Office (w tym w programie Microsoft Excel) makra są zapisywane automatycznie w języku programowania Visual Basic for Applications (w skrócie VBA).

Każde makro jest rejestrowane jako osobny podprogram – w języku VBA procedura.

Procedura  zaczyna się od słowa kluczowego Sub, po którym występuje nazwa procedury (odpowiadająca nazwie makra) oraz para nawiasów: Sub FormatujKomorke().

Procedura kończy się słowami kluczowymi End Sub.

Wewnątrz procedury umieszczone są instrukcje języka VBA, służące do wywołania odpowiednich opcji arkusza. Na przykład za pogrubienie tekstu odpowiada instrukcja:
Selection.Font.Bold = True

W języku VBA komentarze zaczynają się od znaku ' (apostrof). Microsoft Excel wykorzystuje komentarze do zapisania informacji o autorze makra i dacie stworzenia makra.

Ćwiczenie 4. Poznajemy kod makra w języku VBA

Otwórz makro FormatujKomorke w edytorze VBA. Przejrzyj kod makra. Wskaż początek i koniec procedury FormatujKomorke oraz instrukcję pogrubiającą tekst.

4. Modyfikowanie makra w edytorze VBE

Problem: Makro FormatujKomorke pozwala na sformatowanie pojedynczej komórki. W jaki sposób sformatować wiele komórek jednocześnie?

4.1. Stosowanie instrukcji iteracyjnej for w języku VBA

Wielokrotnie podczas rozwiązywania problemów algorytmicznych korzystaliśmy z instrukcji iteracyjnych (instrukcji pętli). Język Visual Basic również pozwala na korzystanie z pętli, w tym pętli for.

Aby sformatować komórki leżące na przekątnej w tabliczce mnożenia utworzonej w arkuszu kalkulacyjnym (rys. 1.), musimy wywołać wielokrotnie makro FormatujKomorke dla kolejnych komórek. Do tego celu wykorzystamy instrukcję iteracyjną for.

Rys. 8. Ogólna postać instrukcji iteracyjnej for

Przykład 3. Stosowanie instrukcji iteracyjnej for

Utworzymy nowe makro o nazwie FormatujPrzekatna, w którym wywołamy makro FormatujKomorke.

Wewnątrz procedury korzystamy z pętli for, ze zmienną sterującą i zmieniającą się w zakresie od 2 do 21.

Wewnątrz pętli najpierw wskazujemy odpowiednią komórkę za pomocą instrukcji:
Cells(i, i)

Pierwszy parametr instrukcji odpowiada numerowi wiersza, drugi – numerowi kolumny.

Polecenie .Select wskazuje odpowiednią komórkę jako aktywną.

Tym samym instrukcja:
Cells(i, i).Select
odpowiada ustawieniu kursora arkusza w komórce w wierszu i kolumnie o numerze i.

Po wybraniu odpowiedniej komórki wywołujemy makro (procedurę) FormatujKomorke za pomocą instrukcji:
Call NazwaProcedury

Ćwiczenie 5. Stosujemy instrukcję iteracyjną for w języku VBA

1. Otwórz plik zapisany w ćwiczeniu 3.
2. Otwórz makro FormatujKomorke w edytorze VBA. Dodaj pod kodem makra polecenia pokazane w przykładzie 3. (umieść je za poleceniem End Sub).
3. Zamknij okno edytora.
4. Uruchom makro FormatujPrzekatna.
5. Zapisz plik pod tą samą nazwą.

Język Visual Basic jest językiem zorientowanym obiektowo (inaczej językiem programowania obiektowego, o czym piszemy w temacie C4 części II Materiału edukacyjnego).

W rzeczywistości instrukcja Cells() zwraca obiekt typu Range. Polecenie Select jest w istocie metodą tego obiektu. Korzystając z odpowiednich obiektów, ich właściwości i metod, możemy odwoływać się do dowolnych elementów arkusza (skoroszytów, arkuszy, zakresów komórek, pojedynczych komórek) i wykonywać na nich operacje.

Instrukcja With ... End With widoczna na rys. 7. pozwala na skrócenie zapisu. Ciąg instrukcji:

można za jej pomocą skrócić do postaci:

4.2. Stosowanie instrukcji warunkowej if w języku VBA

Język Visual Basic posiada również typowe instrukcje strukturalne – instrukcję warunkową If ... Then ... Else, instrukcje pętli warunkowej: While ... End While oraz Do While / Until ... Loop, instrukcję wielokrotnego wyboru Select ... Case, procedury i funkcje z parametrami.

Rys. 9. Ogólna postać instrukcji iteracyjnej if

Operatory porównania	=	równy
	<	mniejszy
	>	większy
	<=	mniejszy lub równy
	>=	większy lub równy
	<>	różny
Operatory logiczne	And	koniunkcja logiczna
	Or	alternatywa logiczna
	Not	negacja logiczna

Tabela 1. Operatory porównania i logiczne w języku VBA

Przykład 4. Stosowanie instrukcji warunkowej if

Utworzymy nowe makro o nazwie PochylParzyste, w którym wartości parzyste umieszczone na przekątnej zostaną pochylone.

W procedurze PochylParzyste() zastosowano instrukcję warunkową if z warunkiem prostym:
Cells(i, i).Value Mod 2 = 0

oznaczającym sprawdzenie, czy reszta z dzielenia (operator Mod) wartości komórki leżącej na przekątnej przez 2 jest równa zero.

Ćwiczenie 5. Stosujemy instrukcję warunkową if

1. Utwórz makro PochylParzyste, które pochyli parzyste wyniki kwadratów liczb umieszczone na przekątnej.
2. Umieść makro za procedurą FormatujPrzekatna.
3. Zamknij okno edytora.
4. Uruchom makro PochylParzyste.
5. Zapisz plik arkusza kalkulacyjnego pod tą samą nazwą.

Ćwiczenie 6. Modyfikujemy makro

1. Zmodyfikuj procedurę PochylParzyste, aby pozostałe wartości w komórkach leżących na przekątnej były podkreślone.
2. Uruchom makro PochylParzyste.
3. Zapisz plik arkusza kalkulacyjnego pod tą samą nazwą.

Ćwiczenie 7. Poznajemy wybrane instrukcje języka VBA

1. Zapoznaj się z opisami instrukcji While ... End While oraz Do While / Until ... Loop, dostępnymi w Pomocy edytora VBA.
2. Ułóż zadanie, które będzie wymagało użycia jednej z tych instrukcji i utwórz odpowiednie makro.
Wskazówka: Aby przywołać opis instrukcji, wystarczy w edytorze VBA napisać słowo kluczowe, umieścić kursor tekstowy w obrębie tego słowa i nacisnąć klawisz F1.

5. Otwieranie dokumentów zawierających makra

Makra utworzone w arkuszu kalkulacyjnym zapisywane są razem z nim w pliku. Podczas otwierania takiego pliku pojawia się ostrzeżenie przed potencjalnym zagrożeniem (rys. 10).

Makra mogą zawierać dowolny kod, w tym niebezpieczny dla naszego komputera (np. modyfikujący pliki znajdujące się na komputerze). Z tego powodu, otwierając pliki pochodzące od innych osób, powinniśmy zawsze wybierać opcję Wyłącz makra.

Przed otwarciem w arkuszu kalkulacyjnym pliku zawierającego makra i pochodzącego od innej osoby powinniśmy sprawdzić (za pomocą programu antywirusowego), czy nie zawiera on wirusów. Dopiero potem możemy ponownie otworzyć dokument i włączyć makra.

Ustawiania bezpieczeństwa makr można zmienić w oknie Centrum zaufania po wybraniu opcji Bezpieczeństwo makr na karcie Developer.

Zadania

1. Zarejestruj makro, które w aktualnie zaznaczonej komórce będzie wpisywać ustalony przez ciebie tekst (np. twoje imię i nazwisko), a następnie doda do tej komórki obramowanie i zmieni kolor czcionki na niebieski.
2. Zarejestruj makro, które treść aktualnie zaznaczonej komórki (w której będą umieszczone dwie informacje rozdzielone spacją) będzie rozdzielać na dwie komórki. Przypisz makro do przycisku.
3. Otwórz plik TB3_z3_Makro.xls. Utwórz makro, które w całej tabliczce mnożenia wyróżni żółtym kolorem liczby podzielne przez 3, a pozostałe oznaczy szarym kolorem.
   Wskazówka: Zastosuj pętle zagnieżdżone.

Dla zainteresowanych

4. Ułóż zadanie, które będzie wymagało użycia instrukcji for i if z tych instrukcji i utwórz odpowiednie makro. Możesz wykorzystać plik ćwiczeniowy z zadania 3.
5. Znajdź w Pomocy edytora VBA informacje na temat obiektów Application, Worksheet, Range. Sprawdź, jakie właściwości (ang. properties) i metody (ang. methods) są dostępne dla obiektu typu Range.