T. Zię b a k o ws k i : P RO G R A MO W A N I E M A K R . . . strona 1

PROGRAMOWANIE MAKR W PROGRAMIE EXCEL W JĘZYKU VISUAL BASIC W programie Microsoft Excel można często powtarzane czynności zautomatyzować przy użyciu tzw. makr. Makro jest serią poleceń i funkcji, które są zapisywane za pomocą języka programowania i może być uruchomione zawsze, gdy zachodzi potrzeba ich wykonania. Firma Microsoft dostarcza z pakietem Office język programowania Visual Basic. Makra można rejestrować, jak rejestruje się muzykę przy użyciu magnetofonu lub można napisać za pomocą języka Visual Basic, i dalej można uruchomić, aby wykonać zarejestrowane bądź zapisane polecenia. Przykład 1. Rejestrowanie makra. W nowym arkuszu wykonaj następujące czynności: 1. Z menu narzędzia wybierz polecenia makro zarejestruj nowe makro. 2. Zmień nazwę makra na. „Jan” i wybierz klawisz skrótu na Ctrl-j i potwierdź OK –

rozpoczął się tryb nagrywania makra. 3. W wybranej komórce wpisz napis „Jaś”, potwierdź wpisany napis myszką na pasku

formuły. 4. Zakończ nagrywanie makra ikoną na pasku zadań. 5. Wybierz nową komórkę i naciśnij Ctrl-j. Przykład 2. Edycja Makra. 1. Z menu narzędzia wybierz polecenia makro makra... 2. Wybierz makro „Jan” i kliknij Edycja. 3. W oknie edytora Visual Basic powinieneś zobaczyć tekst podobny do wypisanego

piniżej: Sub Jan() ' ' Jan Makro ' Makro zarejestrowane 00-02-25, autor Tadeusz Ziębakowski ' ' Klawisz skrótu: Ctrl+j ' ActiveCell.FormulaR1C1 = "Jaś" End Sub

4. Dopisz następujący tekst: Sub Małgorzata()

ActiveCell.FormulaR1C1 = "Małgosia" End Sub

5. Przejdź do arkusza kalkulacyjnego i wybierz polecenia makro makra...

T .Z ię b a k o ws k i : PRO G R A MO W AN IE M A K R . . . strona 2

6. Wybierz makro „Małgorzata” i kliknij Opcje i wpisz klawisz skrótu Ctrl-m. 7. Wybierz nową komórkę i naciśnij Ctrl-m. Uruchamianie makra niekoniecznie musi odbywać się poprzez klawisz skrótu. Można to robić z menu Narzędzia bądź poprzez umieszczenie w arkuszu specjalnego przycisku. Przejdziemy teraz do omówienia podstawowych elementów składni języka Visual Basic. Na potrzeby niniejszego zbioru zadań ograniczymy się do jego skrótowego omówienia bez wchodzenia w istotę bardziej złożonych zapisów. 1. Zmienne Każde makro jest zapisem czynności, które wykonywane są na pewnym określonym zbiorze informacji, który będziemy nazywać danymi. Dane mogą być zapisane bądź w komórkach arkusza kalkulacyjnego bądź w pewnym zarezerwowanym obszarze pamięci i są reprezentowane wewnątrz makra poprzez zmienne. Każda zmienna określona jest poprzez podanie nazwy, którą ustala piszący makro tworząc ją z liter i cyfr (pierwszym znakiem w nazwie powinna być litera). Elementy struktury arkusza kalkulacyjnego są w Visual Basicu for Applications reprezentowane przez tzw. obiekty i opisywane za pomocą „specjalnych” zmiennych, służących do identyfikowania m.in. arkuszy, komórek czy zakresów komórek W niniejszym opracowaniu ograniczymy się do opisu trzech obiektów: Range………., ActiveCell………… i Selection…………..

ActiveCell.Value lub ActiveCell

przechowuje wartość wybranej komórki ActiveCell.FormulaR1C1

przechowuje formułę wybranej komórki

Selection.Value lub Selection

pozwala wpisać wartości do wybranego zakresu komórek

Range("A1:C5").Value lub Range("A1:C5")

pozwala wpisać wartości do zakresu „A1:C5”

Selection.Cells(1,2).Value lub Selection(1,2)

pozwala wpisać wartości do komórki w pierwszym wierszu i drugiej kolumnie w wybranym obszarze.

Range("A1:C5").Cells(5).Value lub Range("A1:C5")(5)

pozwala wpisać wartości do 5-tej komórki w zakresie „A1:C5” tj. komórki B2.

Range("A1:C5").Cells.Count podaje liczbę komórek w zakresie, w tym przypadku 15.

T. Zię b a k o ws k i : P RO G R A MO W A N I E M A K R . . . strona 3

Range("A1:C5").Columns.Count podaje liczbę kolumn w zakresie, w tym przypadku 3.

Range("A1:C5").Rows.Count podaje liczbę wierszy w zakresie, w tym przypadku 5.

Podobnie jak komórki w programie Excel zmienne mogą przechowywać dane różnych typów, przy czym w Visual Basic różnorodność typów jest znacznie większa (co więcej można tworzyć własne typy danych, czego nie będziemy omawiać). Typy danych mają swoje nazwy. Wymienimy tylko kilka podstawowych typów:

typ opis operacje symbol Byte

liczby całkowite: Od 0 do 255

Integer

liczby całkowite: Od -32 768 do 32 767

dodawanie odejmowanie mnożenie dzielenie całkowite reszta z dzielenia

+ - * Div Mod

Single

liczby rzeczywiste: Od ok.-3,41038 do ok. 3,41038

Double liczby rzeczywiste: Od ok.-1,810308 do ok. 1,810308

Currency liczby wyrażające walutę -922 337 203 685 477,5808 do 922 337 203 685 477,5807

dodawanie odejmowanie mnożenie dzielenie potęgowanie

+ - * / ^

Boolean wartości logiczne: True (prawda), False (fałsz)

koniunkcja alternatywa alternatywa wykluczna negacja równoważność implikacja

And Or Xor Not Eqv Imp

String łańcuchy znaków łączenie łańcuchów & , + Date daty i czas specjalne funkcje daty i czasu Variant Połączenie Double i String jak dla Double i String operatory relacji

(wynik jest typu Boolean): =,<,<=,>,>=,<>

W języku Visual Basic nadanie wartość zmiennej określa typ zmiennej, jednak w bardziej złożonych makrach w celu uniknięcia błędów powinno się określić typ zmiennej za pomocą specjalnej instrukcji deklaracji Dim. 2. Wyrażenia Wyrażenia tworzymy podobnie jak formuły w arkuszu, z tym, że zamiast adresów komórek wstawiamy zmienne. W wyrażeniach możemy wykorzystywać funkcje wewnętrzne Visual Basic podobne do funkcji Excela1, a także istnieje możliwość definiowania własnych funkcji.

1 W Visual Basic-u należy bardziej niż w Excel-u uważać na zgodność typów argumentów funkcji z używanymi zmiennymi. Wykaz funkcji i ich opis można znaleźć w pomocy Visual Basic.

T .Z ię b a k o ws k i : PRO G R A MO W AN IE M A K R . . . strona 4

Przy tworzeniu wyrażeń należy zwrócić szczególną uwagę na zgodność typów zmiennych, funkcji i używanych operatorów. 3. Instrukcje Czynności, które ma realizować makro zapisujemy w postaci tzw. instrukcji. Instrukcje zapisujemy w kolejnych liniach. Jeśli chcemy kilka instrukcji zapisać w jednej linii oddzielamy je dwukropkami. Uwaga: napisy zaczynające się od apostrofu nie są instrukcjami lecz komentarzami. Omówimy kilka najważniejszych instrukcji: Instrukcja przypisania Instrukcja ta ma postać:

zmienna = wyrażenie W wyniku wykonania instrukcji zmienna otrzymuje wartość wyrażenia. Przykłady:

d = b^2 – 4*a*c i = i + 1 ActiveCell.FormulaR1C1 = "Jaś"

Pierwsza instrukcja nadaje zmiennej d wartość wyrażenia b^2 – 4*a*c , gdzie a,b,c są zmiennymi. Druga powiększa wartość zmiennej i o jeden. Trzecia wstawia do aktywnej komórki napis "Jaś" . Instrukcja wywołania procedury Instrukcja ta ma postać:

<nazwa procedury> parametr1, parametr2,... Przykład:

MsgBox "Czas na naukę pisania makr!" Instrukcja ta wyświetla komunikat "Czas na naukę pisania makr!" wykorzyst-ując procedurę Visual Basica MsgBox. Instrukcja warunkowa Instrukcja ta ma postać:

If warunek Then instrukcje Else instrukcje_else

lub w wersji krótszej:

If warunek Then instrukcje

T. Zię b a k o ws k i : P RO G R A MO W A N I E M A K R . . . strona 5

Inną możliwością jest zastosowanie składni blokowej: If warunek Then instrukcje Else instrukcje_else End If

(w wersji bardziej rozbudowanej:) If warunek Then instrukcje ElseIf warunek1 Then Instrukcje1 ElseIf warunek2 Then Instrukcje2 Else instrukcje_else End If

(lub w wersji krótszej:) If warunek Then instrukcje End If

warunek powinien być wyrażeniem logicznym. W przypadku gdy jest on prawdziwy wykonywane są instrukcje w przeciwnym przypadku instrukcje_else. W wersji krótszej gdy warunek nie jest spełniony następuje przejście do instrukcji następnej po If. W składni jednowierszowej instrukcje oddziela się dwukropkami. Instrukcja pętli For

For licznik = początek To koniec Step krok instrukcje

Next licznik licznik jest zmienną numeryczną, początek i koniec są wartościami początkowymi i końcowymi licznika. Wykonywane są instrukcje, po każdym wykonaniu licznik zwiększany jest o wartość krok lub o 1 gdy fraza Step jest pominięta. Wykonywanie zostanie przerwane gdy licznik przekroczy wartość koniec. Działanie pętli for można dodatkowo przerwać instrukcją Exit For umieszczoną wewnątrz pętli. Instrukcja pętli Do Instrukcja ta maże mieć następujące warianty składni:

Do While warunek instrukcje Loop

albo Do Until warunek instrukcje Loop

albo

Do instrukcje Loop While warunek

albo Do instrukcje Loop Until warunek

Jedna lub kilka instrukcji oznaczonych przez instrukcje powtarzanych jest tak długo, jak długo warunek jest spełniony – wariant ze słowem While - albo dopóki nie stanie się

T .Z ię b a k o ws k i : PRO G R A MO W AN IE M A K R . . . strona 6

prawdziwy - wariant ze słowem Until, przy czym warunek można sprawdzać na początku lub na końcu. Instrukcja deklaracji Instrukcja ta przydziela pamięć zmiennej i określa jej typ i w uproszczeniu jest następującej postaci: Dim zmienna1 As typ1, zmienna2 As typ2, ... Przykłady: Poniżej zadeklarowano3 zmienne: pierwszą typu Variant (typ domyślny), drugą typu Double, trzecią typu String. Dim liczba, numer As Double, napis As String W poniższym wierszu zadeklarowano tablicę o stałym rozmiarze jako tablicę o elementach typu Integer zawierającą 11 wierszy i 11 kolumn: Dim MojaTab(10, 10) As Integer Pierwszy argument reprezentuje wiersze; drugi argument reprezentuje kolumny, oba standar-dowo numerowane od 0 - inaczej niż ma to miejsce w przypadku wyrażenia Cells(i,j).

4. Procedury i Funkcje Każde makro w Excelu jest zapisywane jako procedura Visual Basic. Podczas pisania bardziej rozbudowanych makr często zdarza się, że taki sam lub podobny fragment makra występuję w kilku miejscach. Można wówczas taki powtarzający się fragment zapisać w postaci dodatkowej procedury lub funkcji. Procedura lub funkcja - wyodrębniona sekwencja instrukcji, stanowiąca pewną całość, posiadająca jednoznaczną nazwę i ustalony sposób wymiany informacji z pozostałymi częściami programu lub makra. Stosowanie procedur i funkcji na ogół skraca zapis, a także ułatwia pisanie dużych rozbudowanych makr dzięki podzieleniu go na odrębne logicznie spójne części. Różnica pomiędzy procedurą a funkcją polega na przekazywaniu wartości końcowych. Procedury i funkcje definiuje się przy pomocy instrukcji Sub i Function:

Sub nazwa (lista_argumentów) instrukcje End Sub Function nazwa (lista_argumentów) As typ instrukcje nazwa = wyrażenie End Function

lista_argumentów jest listą zmiennych oddzielonych przecinkami i jest nieobowiąz-kowa podobnie jak typ, który określa typ zwracanego wyniku przez funkcję. W definicji funkcji typ wyrażenia (wyrażenie) powinien być zgodny z typem funkcji . Ponadto w

T. Zię b a k o ws k i : P RO G R A MO W A N I E M A K R . . . strona 7

definicji procedury i funkcji mogą się pojawić instrukcje Exit Sub i odpowiednio Exit Function przerywające działanie procedury lub funkcji. Przykład 3.

Makro Main wywołuje dwie procedury: Sygnał, która wysyła krótkie sygnały dźwiękowe (procedura Beep) w ilości określonej przez parametr i procedurę Komunikat , która wypisuje napis "Czas na kolejny przykład!".

Sub Main() 'źródło: plik pomocy Visual Basic Sygnał 100 Komunikat End Sub Sub Sygnał(ile_dźw) For licznik = 1 To ile_dźw Beep Next licznik End Sub Sub Komunikat() MsgBox "Czas na kolejny przykład!" End Sub

Przykład 4 Makro rozwiązuje równanie kwadratowe 02 cbxax w liczbach rzeczywistych. Współczynniki a,b,c należy wpisać do komórek A1,B1,C1. Pierwiastki, jeśli są, wypisane będą w komórkach A3 i A4. Makro zawiera jawną deklarację zmiennych instrukcją Dim, bez której makro będzie działać, lecz zmienne a,b,c i delta będą typu Variant.

Sub równanie_kwadratowe() Dim a As Single, b As Single, c As Single, delta As Single a = Range("A1") b = Range("B1") c = Range("C1") delta = b ^ 2 - 4 * a * c If a = 0 Then MsgBox "To nie jest równanie kwadratowe"

Exit Sub End If If delta < 0 Then MsgBox "Nie ma rozwiązań": Exit Sub If delta = 0 Then Range("A3") = -b / (2 * a) Else Range("A3") = (-b + Sqr(delta)) / (2 * a) Range("A4") = (-b - Sqr(delta)) / (2 * a) End If End Sub

T .Z ię b a k o ws k i : PRO G R A MO W AN IE M A K R . . . strona 8

Przykład 5 Makro w kolumnie A mnoży po kolei przez 2 każdą liczbę, aż do napotkania komórki pustej.

Sub Podwajaj() i = 1 Do While Range("A1").Cells(i, 1) <> "" Range("A1").Cells(i, 1) = 2 * Cells(i, 1) i = i + 1 Loop End Sub

Przykład 6 Funkcja wyznacza normę euklidesową wektora o współrzędnych x,y,x. Funkcja ta jest dostępna w arkuszu jako funkcja użytkownika. Należy pamiętać aby w formule Excela parametry funkcji oddzielać średnikami.

Function Norma(x As Single,y As Single,z As Single) _ As Single

Norma = Sqr(x ^ 2 + y ^ 2 + z ^ 2) End Function

Uwaga: Podkreślenie poprzedzone spacją oznacza kontynuację instrukcji w następnym wierszu Zadania

1. Napisz makro, które przepisuje wartości co drugiej komórki kolumny A do kolumny B po kolei, aż do napotkania komórki pustej.

2. Napisz makro, które przepisuje wartość komórki A1 do A2, A2 do A3,...,A19 do A20 i A20 do A1.

3. Napisz makro, które w zaznaczonym zakresie komórek odwraca kolejność danych tzn. zamienia wartości pierwszej i ostatniej komórki, drugiej i przedostatniej itd.

4. Zaprojektuj makro, które rozwiązuje układ dwóch równań liniowych z dwoma niewiadomymi:

222

111

cybxacybxa

Wskazówki: Przyjmijmy 2121 abbaD , 2112 cbcbDx , 2112 cacaDy . Z algebry wiadomo, że jeśli 0D to mamy jedno rozwiązanie DDx x / i DDy y / . Jeśli 00,0 yx DiDD to mamy nieskończenie wiele rozwiązań: dla dowolnego x, 111 /)( bxacy spełnia układ równań. Jeśli 0lub0,0 yx DDD to układ nie ma rozwiązań. Współczynniki iii cba ,, można wpisać do zakresu A1:C2. Makro powinno zbadać również „złośliwe” przypadki, gdy współczynniki iii cba ,, nie wyznaczają układu równań z dwoma niewiadomymi.

5. Należy zaprojektować arkusz i makra, tworzące grę 15+1. Plansza tej gry jest pokazana poniżej:

T. Zię b a k o ws k i : P RO G R A MO W A N I E M A K R . . . strona 9

Zadaniem grającego jest ustawienie liczb od 1 do 15 po kolei przez przesuwanie liczb. Można przesuwać liczby pionowo lub poziomo na puste miejsce (w obrębie prostokąta). Po wskazaniu komórki, przycisk Przesuń powinien uruchomić makro, które wykona przesunięcie np. w powyższej sytuacji liczba 6 powinna się przesunąć w prawo. Zadaniem przycisku Nowa gra jest ponowne, losowe rozmieszczenie liczb wewnątrz prostokąta. Liczby losowe można otrzymać za pomocą funkcji Rnd, - jej omówienie można znaleźć w pomocy Visual Basic.

T .Z ię b a k o ws k i : PRO G R A MO W AN IE M A K R . . . strona 10

Ćwiczenie: Podstawy tworzenia makr za pomocą języka Visual Basic z wykorzystaniem edytora VBA.

Zagadnienia: Podstawowe elementy języka VB: zmienne, typy danych, instrukcje deklaracji i

podstawienia. Dostęp do danych arkusza kalkulacyjnego za pomocą obiektów typu Range. Procedura MsgBox.

1. Obiekt Range Zadanie 1 Wpisz w edytorze VBA makro: Sub Dodaj()

Range(”C1”)= Range(”A1”)+ Range(”B1”) End Sub W Arkuszu wpisz do komórek A1 i B1 dowolne wartości liczbowe i wykonaj makro. Zadanie 2 Należy obliczyć wartość wyrażenia:

(*)

dla wartości a,b znajdujących się w komórkach odpowiednio A1 i B1. W tym celu napisz następujące makro: Sub Wynik()

Dim a as Double ,b as Double ,c as Double a = Range(”A1”) b = Range(”B1”) c = Range(”C2”) = c

End Sub Po uruchomieniu makra w komórce C2 pojawi wynik. Zadanie 2 Napisz makro, które zamienia wartości dwóch komórek, np. A2 i B2. (Wskazówka: wprowadź dodatkową zmienną w celu zapamiętania wartości jednej z komórek)

2. Obiekt Selection i procedura MsgBox Zadanie 3 W edytorze VBA wpisz makro:

tu należy wypisać wyrażenie zmiennych a i b obliczające (*), skonstruowane na zasadach podobnych do pisania formuł EXCELA

T. Zię b a k o ws k i : P RO G R A MO W A N I E M A K R . . . strona 11

Sub obszar() Dim n As Integer, w As Integer, k As Integer n = Selection.Count w = Selection.Rows.Count k = Selection.Columns.Count MsgBox "liczba komórek " & n MsgBox "liczba wierszy " & w MsgBox "liczba kolumn " & k End Sub W arkusz kalkulacyjnym zaznacz zakres komórek a następnie uruchom powyższe makro. Wewnętrzna procedura MsgBox powoduje wypisanie komunikatów.

3. Obiekt ActiveCell

Zadanie 4 Wpisz w edytorze VBA makro: Sub Dodaj_1()

ActiveCell = ActiveCell + 1 End Sub Każde uruchomienie makra zwiększy wartość wybranej komórki o 1 . Zadanie 5

Należy wyznaczyć kolejne sumy częściowe szeregu geometrycznego

1

1

n

nq . Szereg

ten jest zbieżny gdy |q| < 1. Każda suma częściowa jest postaci 121 n

n qqqS . Sumy te spełniają związek rekurencyjny 11 nn qSS , który wykorzystamy w tym ćwiczeniu. W edytorze VBA wpisz makro: Sub szereg() Dim S as Double, q as Double

q = 0.5 S = ActiveCell ActiveCell.Offset(1, 0).Select ActiveCell = q * S + 1

End Sub Każde uruchomienie makra oblicza kolejną sumę szeregu geometrycznego. Instrukcja ActiveCell.Offset(1, 0).Select przesuwa wybranie komórki w dół. Zadanie 6 Oblicz przybliżoną wartość liczby wykorzystując szereg:

111

91

71

51

311

4

T .Z ię b a k o ws k i : PRO G R A MO W AN IE M A K R . . . strona 12

Przykłady makr i funkcji: 1. Dotyczy pliku Dochód.xls Function grupa(dochód As Currency) As Integer ‘ ‘ Wyznacza grupe podatkowa ‘ If dochód < 37024 Then grupa = 1 If (dochód >= 37024) And (dochód < 74048) Then grupa = 2 If dochód >= 74048 Then grupa = 3 End Function ------------------------------------------------------------------ Function grupa1(dochód As Variant) As Integer ‘ ‘ Wyznacza grupe podatkowa – drugi sposób ‘ If dochód < 37048 Then grupa1 = 1 ElseIf dochód < 74048 Then grupa1 = 2 Else grupa1 = 3 End If End Function ------------------------------------------------------------------ Sub dochód1() ‘ ‘ Kopiuje do nowej kolumny dochody 1 grupy podatkowej ‘ Dim doch As Range, doch1 As Range, i As Integer Set doch1 = Range("I12:I71") Set doch = Range("dochód") For i = 1 To doch.Count If doch(i) < 37024 Then doch1(i) = doch(i) Next i End Sub ------------------------------------------------------------------ Function podatek(dochód As Currency) As Currency ‘ ‘ Oblicza podatek dochodowy na podstawie dochodu ‘ If dochód < 37048 Then podatek = 0.19 * dochód - 530.08 ElseIf dochód < 74048 Then podatek = 0.3 * (dochód - 37024) + 6504.48 Else podatek = 0.4 * (dochód - 74048) + 17611.68 End If End Function ------------------------------------------------------------------ Sub Dane1grupy() ‘ ‘ Podaje ilość osób i całkowity dochód w 1 grupie podatkowej ‘ Dim doch As Range Set doch = Range("dochód") Dim Licz As Integer, suma As Currency, i As Integer, max As Currency Licz = 0 suma = 0 For i = 1 To doch.Count If doch(i) < 37024 Then

T. Zię b a k o ws k i : P RO G R A MO W A N I E M A K R . . . strona 13

Licz = Licz + 1 suma = suma + doch(i) End If Next i MsgBox "Liczba osób w 1 grupie podatkowej wynosi: " & Licz MsgBox "Suma dochodów w 1 grupie podatkowej wynosi: " & suma max = 0 For i = 1 To doch.Count If doch(i) < 37024 And doch(i) > max Then max = doch(i) End If Next i MsgBox "maks. dochód w 1 grupie podatkowej wynosi: " & max End Sub 2. Dotyczy pliku Rodzina.xls W przykładach dotyczących arkusza RODZINA należy nazwać obszary komórek wg wzoru przedstawionego poniżej.

T .Z ię b a k o ws k i : PRO G R A MO W AN IE M A K R . . . strona 14

1. Makro wypisuje zakupy z grupy rozrywka w zakresie K3 do K100.

2. Makro wypisuje w zakresie H3 do H5 trzy maksymalne kwoty wydatków, a w zakresie

I3:I5 wypisuje daty, w których te wydatki były poniesione.

3. Makro zaznacza na żółto zakupy jajek.

T. Zię b a k o ws k i : P RO G R A MO W A N I E M A K R . . . strona 15

4. Makro w kolumnie H wypisuje zakupy z grupy rozrywka.

5. Makro w kolumnie G wypisuje bez powtórzeń wszystkie rodzaje zakupów.

6. Funkcja podaje dzień (datę), w którym po raz ostatni wydano na rozrywkę. Funkcję można

użyć w formule w komórce arkusza pisząc: =ostatni_dzień_rozrywka(data)