excell-makra, Dokumenty - Przykłady
[ Pobierz całość w formacie PDF ]
1
Makropolecenie formatowania i wstęp do
Visual Basic for Applications (VBA)
Ćwiczenie1.
1) Wprowadźmy poniższe dane do arkusza nowego kalkulacyjnego (i nadajmy mu swoją nazwę).
Aby skorzystać z powyższych danych upewnijmy się, że sumy w miesięczne i kwartalne są obliczone
przy zastosowaniu funkcji
SUMA.
2) Procedura formatowania:
-
wybierz (kliknij) jakąś dowolną komórkę
-
z menu
Edycja
wybierz
Przejdź Do
-
w oknie dialogowym
Przejdź Do
kliknij przycisk
Specjalne
-
kliknij opcję
Stałe
następnie skasuj zaznaczenie opcji
Liczby, Logiczne i Błędy
(pozostaw
zaznaczony
Tekst
)
-
kliknij
OK
-
zauważ, że są zaznaczone komórki zawierające tekst (postaraj się nie anulować zaznaczenia, gdyż
będziemy je formatować)
-
formatuj zaznaczone komórki za pomocą menu
Format > Komórki –
kliknij zakładkę
Czcionka
-
wybierz czcionkę
pogrubioną
i zmień kolor na granatowy
-
kliknij
OK.
-
wybierz (kliknij) jakąś dowolną komórkę
-
z menu
Edycja
wybierz
Przejdź Do
-
w oknie dialogowym
Przejdź Do
kliknij przycisk
Specjalne
-
kliknij opcję
Stałe
następnie skasuj zaznaczenie opcji
Tekst, Logiczne i Błędy
(pozostaw
zaznaczone
Liczby
)
-
kliknij
OK
-
zauważ, że są zaznaczone komórki zawierające liczby (postaraj się nie anulować zaznaczenia,
gdyż będziemy je formatować)
-
formatuj zaznaczone komórki za pomocą menu
Format > Komórki –
kliknij
zakładkę
Czcionka
-
wybierz czcionkę
pogrubioną
i zmień kolor na zielony
-
kliknij
OK.
-
wybierz (kliknij) jakąś dowolną komórkę
-
z menu
Edycja
wybierz
Przejdź Do
-
w oknie dialogowym
Przejdź Do
kliknij przycisk
Specjalne
-
kliknij opcję
Formuły
-
kliknij
OK
2
-
zauważ, że są zaznaczone komórki zawierające liczby będące wynikiem
wykonania funkcji (postaraj się nie anulować zaznaczenia, gdyż będziemy je formatować)
-
formatuj zaznaczone komórki za pomocą menu
Format > Komórki –
kliknij zakładkę
Czcionka
-
wybierz czcionkę
pogrubioną
i zmień kolor na czerwony
-
kliknij
OK.
Teraz dodajmy jeszcze opis.
-
zaznacz zakres
A1:A3
i wybierz
Wstaw > Wiersze
-
kliknij w komórce
A1
-
wybierz menu
Format > Komórki,
zakładka
Desenie i wybierz
granatowy kolor jako
Cieniowanie komórki
-
kliknij
OK.
-
kliknij w komórce
B1
i wpisz:
Tekst
-
kliknij w komórce
A2
-
wybierz menu
Format > Komórki,
zakładka
Desenie i wybierz
zielony kolor jako
Cieniowanie
komórki
-
kliknij
OK.
-
kliknij w komórce
B2
i wpisz:
Liczby
-
kliknij w komórce
A3
-
wybierz menu
Format > Komórki,
zakładka
Desenie i wybierz
czerwony kolor jako
Cieniowanie komórki
-
kliknij
OK.
-
kliknij w komórce
B3
i wpisz:
Formuły
-
wciśnij
Enter
Efekt naszej pracy powinien wyglądać tak jak na poniższym rysunku:
Ćwiczenie 2
Mając już dokładnie zaplanowane czynności, które chcemy wykorzystać, możemy przystąpić do
zarejestrowania makra. W tym celu usuńmy formatowanie z naszego arkusza. Aby tego dokonać użyjmy
kombinacji klawiszy
CTRL+A
(by zaznaczyć cały arkusz) a potem menu
Edycja > Wyczyść > Format.
Zaznaczamy jeszcze zakres
A1:A3
i wybieramy
Edycja > Usuń
(w oknie dialogowym
Usuń
zaznaczamy
Cały wiersz
) i klikamy
OK.
Procedura zarejestrowania nowego makra:
1) Wybierz pojedynczą komórkę arkusza
2) Z menu
Narzędzia > Makro
wybierz
Zarejestruj nowe makro
3) Pojawia się okno dialogowe
Rejestruj makro
4) Nadaj swoją nazwę (np. „FormatowanieZawartości”)
5) Możesz też dodać swój opis w polu
Opis
(np. „różnicuje tekst, liczby i formuły”)
6) Kliknij
OK.
7) Teraz kolejno powtórz czynności z poprzedniego ćwiczenia i po ich wykonaniu kliknij
Zatrzymaj
rejestrowanie
na pasku narzędzi lub menu
Narzędzia > Makro > Zatrzymaj rejestrowanie
8) Po zarejestrowaniu makropolecenia trzeba je przynajmniej raz odtworzyć, by sprawdzić czy działa ono
poprawnie
9) W celu obejrzenia wyników działania naszego makra usuńmy formatowanie z naszego arkusza. Aby
tego dokonać:
-
naciśnij kombinację klawiszy
CTRL + A
(by zaznaczyć cały arkusz)
-
potem z menu
Edycja
wybierz
Wyczyść > Formaty
-
zaznacz zakres
A1:A3
i wybierz
Edycja > Usuń
, a w oknie dialogowym
Usuń
zaznacz opcję
Cały wiersz
i kliknij
OK.
Ćwiczenie 3
Samodzielnie zarejestruj makro usuwające formatowanie z arkusza.
Ćwiczenie 4
Czasami po pierwszym odtworzeniu makra dostrzegamy potrzebę poprawek. W excelu możemy dokonać
poprawek bez konieczności ponownego nagrywania. Aby rozpocząć modyfikacje należy znaleźć miejsce
w którym umieścił je rejestrator makr (my wybraliśmy domyślną opcję
Ten skoroszyt
); najprościej
będzie otworzyć okno dialogowe
Makro
:
1) Z menu
Narzędzia
wybierz
Makro > Makra
2) Wybierz z listy nazwę swojego makra („FormatowanieZawartości” jeśli tak je nazwałeś)
3) Wciśnij przycisk
Edycja
4) Otworzyło się (jeśli nic nie namieszałeś…) okno
Visual Basic Editor,
a okno
Excela
jest nadal
otwarte w tle. Między tymi oknami możemy się poruszać za pomocą skrótu
ALT+F11
(lub standardowo
ALT+TAB
).
W razie gdybyśmy chcieli zamknąć ekran edytora Visual Basic (ale na razie tego nie robimy!), wybieramy
ostatnie polecenie z menu
Plik:
Zamknij i powróć do Microsoft Excel
.
Okno edytora Visual Basic służy do modyfikacji makr oraz do pisania procedur w języku Visual Basic for
Applications (VBA). W trakcie pracy z nagranymi makrami (i w rezultacie pisania własnych procedur)
zapoznamy się dokładnie ze wszystkimi elementami tego ekranu. Opcje menu oraz paski narzędzi w oknie
edytora Visual Basic (VB) różnią się one od menu i pasków narzędzi, które są dostępne w oknie Microsoft
Excel. W oknie edytora VB pasek menu i pasek narzędzi mają polecenia i przyciski pomocne w trakcie
programowania i testowania procedur. Ekran edytora VB to głownie trzy okna
Projekt, Właściwości
oraz
Kod programu
. W oknie
Projek
t w otwartym folderze
Moduły
zaznaczony jest
Moduł 1
. MS Excel
wpisuje zarejestrowane czynności w specjalnych arkuszach o nazwie Moduł l, Moduł 2 itp. W przyszłości
będziemy używać modułów do wpisywania kodu swoich procedur. Moduł przypomina swoim wyglądem
pusty dokument w edytorze tekstów MS Word.
5) W oknie
Kod programu
wyświetla nam się kod makra „FormatowanieZawartości”. Zwróć uwagę na
linie poprzedzone znakiem apostrofu – to są linie komentarza. Linie te są pomijane w trakcie
wykonywania procedury (a dokładniej wszystko co znajduje się po znaku apostrofu aż do końca linii).
Komentarze dodaje się by ułatwić „czytanie” kodu procedury.
6) Dodajmy teraz kilka komentarzy:
a)
Przejdź do okna edytora Visual Basic.
b)
Kliknij przed linią kodu:
Selection.SpecialCells(xlCellTypeConstants, 2).Select
i naciśnij
Enter
.
c)
Przesuń kursor do wolnej linii, wstawionej powyżej i dodaj komentarz przedstawiony poniżej,
pamiętając o poprzedzeniu go znakiem apostrofu:
Znajd• i sformatuj komórki zawierajce tekst
d)
Kliknij przed instrukcją:
Selection.SpecialCells(xlCellTypeConstants, 1).Select
3
i naciśnij
Enter
.
e)
Przesuń kursor do pustej linii powyżej i dodaj następujący komentarz:
‘Znajd i sformatuj komórki zawierajce liczby
f)
Kliknij przed instrukcją:
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
i naciśnij
Ente
r.
g)
Przesuń kursor do pustej linii i dodaj następujący komentarz:
‘Znajd i sformatuj komórki zawierajce formuły
h)
Kliknij przed instrukcją
Range("A1:A3").Select
i naciśnij
Enter
.
i)
Przesuń kursor do pustej linii powyżej i dodaj następujący komentarz:
'Sporzd legend
Kilka słów o komentarzach:
Na ekranie VBE (edytor Visual Basic) w oknie kodu każda linia rozpoczynająca się znakiem apostrofu
jest komentarzem. Komentarze wyświetlane są w kolorze zielonym. Kolor komentarzy możemy zmienić w
oknie dialogowym Opcje. W tym celu wybieramy
Narzędzia > Opcje
, po czym klikamy kartę
Edytor
Format
. Komentarz można również dodać po prawej stronie wpisanego kodu. Na przykład, aby dodać
komentarz na końcu linii kodu
.ColorIndex = 11
,
klikamy na końcu tego wiersza, naciskamy
klawisz tabulacji
TAB
, wpisujemy apostrof, a następnie tekst komentarza. Omawiana linia kodu powinna
wyglądać tak:
ColorIndex = 11 ' ustawia kolor czcionki na ciemnoniebieski
Linie komentarza nie wykonują innego zadania poza informowaniem użytkownika o tym jakie zadanie
spełnia dana procedura lub pewna jej część. W czasie pisania własnych procedur pamiętajmy o
umieszczeniu w nich szczegółowych komentarzy zarówno dla siebie, jak i dla innych, którzy będą
wprowadzić w przyszłości zmiany do napisanej przez nas procedury.
Ćwiczenie 5
„Czytanie” kodu makra
Kilka słów o makroprocedurach:
Makroprocedury zaczynają się od słowa kluczowego
Sub
i kończą się słowami kluczowymi
End Sub
. Po
słowie kluczowym
Sub
znajduje się nazwa makra i para nawiasów. Pomiędzy słowami kluczowymi
Sub
i
End
Sub
mieszczą się instrukcje procedury, które Visual Basic wykonuje za każdym razem, gdy
przystąpimy do odtwarzania makra. Visual Basic czyta wiersze procedury od góry do dołu, pomijając
instrukcje poprzedzone apostrofem (patrz Informacje odnośnie komentarzy w zagadnieniu omówionym
powyżej) i zatrzymuje się po dotarciu do słów kluczowych
End
Sub
.
Zwróćmy uwagę na dużą liczbę kropek w zarejestrowanym kodzie makra. Występują one dosłownie w
każdej linii kodu. Kropki te wiążą poszczególne elementy języka Visual Basic. Instrukcje procedury
czytamy zaczynając od prawej strony po ostatniej kropce. Prześledźmy teraz całą procedurę
„FormatowanieZawartości”
(ze względu na możliwą różną kolejność wykonywanych czynności u różnych
studentów zajmiemy się poszczególnymi instrukcjami w dowolnej kolejności).
Range("A1:A3").Select
Wybierz komórki A1, A2, A3.
Selection.EntireRow.Insert
Wstaw nowy wierz w wybranym obszarze. Ponieważ poprzednia linia kodu wybrała komórki w
trzech wierszach, Visual Basic wstawi trzy puste wiersze.
ActiveCell.FormulaRlC1 = "Tekst"
Niech wpisem w aktywnej komórce będzie słowo "Tekst". Ponieważ poprzednia linia kodu,
Range("Bl").Select
, zaznaczyła komórkę B1, komórka ta jest w tej chwili aktywną
komórką i w niej właśnie Visual Basic wpisze podany tekst.
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Ten specjalny blok kodu należy zinterpretować następująco: niech kolor wnętrza obecnie
wybranych komórek będzie czerwony (
ColorIndex = 3)
, deseń niech będzie pełny
(
xlSolid
), a kolor desenia wybranych komórek będzie automatyczny (
xlAutomatic
).
4
5
Blok kodu rozpoczynający się od słowa kluczowego
With
i kończący się łowami
kluczowymi
End With
przyśpiesza wykonanie kodu procedury. Zamiast powtarzać za każdym
razem podobne instrukcje:
Selection.Interior.ColorIndex = 3
Selection.Interior.Pattern = xlSolid
Selection.Interior.PatternColorIndex = xlAUtomatic
Rejestrator makr skorzystał ze skróconego zapisu umieszczając powtarzający się tekst instrukcji
Selection.Interior po prawej stronie słowa kluczowego
With
i zakończył blok instrukcji
odnoszący się do tego samego zakresu komórek słowami kluczowymi
End With
.
Ćwiczenie 6
„Poprawianie” kodu makra
W trakcie przeglądania i analizowania kodu makra linia po linii z pewnością zauważymy, że rejestrator
makr zarejestrował również zbędne polecenia, których nie zamierzaliśmy wcale umieścić w makrze. Na
przykład, po wybraniu komórek zawierających tekst, oprócz ustawienia stylu czcionki pogrubionej i
fioletowego koloru, Excel również nagrał bieżące ustawienia innych opcji na Czcionka - nazwę czcionki,
j jej rozmiar, efekty przekreślenia, indeksu dolnego/górnego, cienie i styl podkreślenia.
With Selection.Font
.Name = "Arial CE"
.FontStyle = "Pogrubiony"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 11
End With
Automatyczny rejestrator procedur zawsze zapisuje wszystkie ustawienia, bez względu na to, czy zależy
nam na nich, czy nie. Wszystkie dodatkowe instrukcje niepotrzebnie wydłużają procedurę. Dlatego też,
po zakończeniu rejestracji makra możemy przeanalizować nagrane instrukcje i zrezygnować ze zbędnych.
1) Z podanego bloku kodu usuń przekreślone instrukcje
With Selection.Font
.Name = "Arial CE"
.FOntStyle = "Pogrubiony"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFOnt = False
.Shadow = False
.Underline = xlUnderlineStyleNOne
.COlorIndex = 11
End With
2) Znajdź w kodzie makroprocedury blok instrukcji formatujący komórki zawierające liczby i dokonaj
zmian w kodzie procedury według wzoru poniżej:
'Znajd i sformatuj komórki zawierajce liczby
Selection.SpecialCells(xlCellTypeConstants, 1).Select
With Selection.Font
.ColorIndex = 10 ' ustawia kolor czcionki na zielony
End With
Range("C6").Select
3) Znajdź kod makra, który formatuje komórki zawierające wzory i zmodyfikuj go według wzoru poniżej:
'Znajd i sformatuj komórki zawierajce formuły
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
With Selection.Font
.FontStyle = "Pogrubiony"
.ColorIndex = 3
[ Pobierz całość w formacie PDF ]