Arkusze kalkulacyjne i VBAUniwersytet Łódzki

Wydział Matematyki i Informatyki

Justyna Walewska

Lekcja 1. Wprowadzenie do VBA

Studia Podyplomowe „Analiza danych i data mining”

Rok akademicki 2018/19

Zasady zaliczenia przedmiotu:

Arkusze kalkulacyjne i VBA

1. Pod koniec zajęć każdy wylosuje jeden projekt do zrealizowania. Przykładowy:

Zadanie 16 Utworzyć skoroszyt o własnościach:

1) Chroniony jest hasłem (swoim nazwiskiem),

2) Zawiera okno typu UserForm z co najmniej 4 przyciskami (lub polami wyboru) w którym przynajmniej 2 mają

przypisane makra.

3) Temat okna: zakup programu telewizyjnego wybieranego z listy.

4) Wybieramy lub wpisujemy okres abonamentu, jakość sygnału, programy itp.

5) Wyliczenie opłaty z wpisaniem kolejnej osoby na listę w arkuszu.

2. Czas realizacji: do końca studiów podyplomowych tzn. do 30 września 2019 roku.

3. Projekt tzn. program w VBA przesyłacie państwo do mnie: justyna.walewska@wmii.uni.lodz.pl. Po sprawdzeniu

przesyłam program do poprawki lub zaliczam na ocenę.

4. Jeśli ktoś już zna dobrze VBA może tylko zrealizować projekt.

Wymagania wstępne:

Podstawowa znajomość programu Excel.

Bibliografia:1. John Walkenbach, Excel 2013 PL. Biblia. Helion 2013.2. John Walkenbach, Excel 2013 PL Programowanie w VBA. Helion 2013.

Visual Basic for Applications I

Visual Basic for Applications – język programowania Microsoftu do tworzenia aplikacji (w pakiecie Office, a więc w szczególności w programie Word, Excel, Power Point, Access, Outlook i innych produktach Microsoftu). Są pewne obiekty, metody, funkcje i własności wspólne dla wszystkich programów Office (np. Okna Dialogowe) i są takie specyficzne dla poszczególnych programów.

Podstawowe zalety:

1. VBA jest wbudowany (zespolony) w programy Office, w szczególności w program Excel,2. Rozbudowany system pomocy (poprzez Internet).

Historia VBA:

1. Język programowania BASIC – lata sześćdziesiąte ubiegłego wieku,2. Język programowania Visual Basic for Windows - lata dziewięćdziesiąte ubiegłego wieku,3. Język programowania Visual Basic for Applications – 1993 (do wersji Excel nr 5).

Makro – sekwencja instrukcji (podprogram, procedura) napisana w języku programowania VBA - Visual Basic for Applications.

Za pomocą makr możemy np.:

1. Wstawiać automatycznie dany tekst, dane, tabele, tworzyć raporty itp.

2. Zautomatyzować często wykonywaną procedurę lub operację.

3. Utworzyć własne polecenie.

4. Utworzyć nową funkcję.

5. Tworzyć okna dialogowe, w których wybierając odpowiednie możliwości generujemy inne procedury

(zdarzenia).

6. Tworzyć nowe aplikacje, które możemy dodać do poleceń Excela.

Karta Deweloper

Aby programować w VBA musimy umieścić kartę Deweloper na wstążce:

Plik → Opcje → Dostosowywanie Wstążki-zaznaczyć Deweloper

Bezpieczeństwo makr

Zmiana ustawienia zabezpieczeń przed makrami:

Plik → Opcje → Centrum zaufania → Ustawienia Centrum zaufania – kliknij Ustawienia Centrum zaufania i zaznacz:

• Wyłącz wszystkie makra i wyświetl powiadomienia• Ufaj dostępowi do modelu obiektowego

Dwa typy makr:

• Makra typu Sub - podstawowy typ programu w VBA,

• Makra typu Function - do tworzenia nowych funkcji.

Makra typu SubMakra VBA typu Sub tworzymy na dwa sposoby:

1. Automatycznie rejestrujemy wykonywane w Excelu czynności, które zostają zapisane jako program. Później

możemy przywołać to makro by Excel automatycznie powtórzył wszystkie czynności.

2. Sami piszemy program korzystając z Edytora Visual Basic w karcie Deweloper.

Sposób I. Makra typu Sub rejestrowane automatycznie

1. Jak utworzyć i zapisać automatycznie makro?2. Jak stosować makro?3. Jak modyfikować makro?

Ad. 1. Jak utworzyć i zapisać automatycznie makro?

Kolejność czynności:o W karcie Deweloper klikamy Zarejestruj makro (lub na pasku stanu).o W pojawiającym się oknie: wpisujemy skrót literowy (może być dodatkowo z klawiszem Shift), wybieramy miejsce

zapisu (np. Ten skoroszyt i klikamy OK.o Wykonujemy dowolne instrukcje Excela np. wpisujemy tekst, tworzymy tabelę.o W karcie Deweloper (lub na pasku stanu) klikamy Zatrzymaj rejestrację makro.

Makro zostało zarejestrowane. Program tego makra możemy zobaczyć w oknie edytora Visual Basic na karcie Deweloper.

Ćwiczenie 1. Zarejestrować makro wpisujące własne nazwisko i imię kursywą, dużymi literami na żółtym tle, rozszerzyć odpowiednio całą kolumnę. Zatrzymać rejestrowanie makra.

Uruchom makro używając kombinacji klawiszy Alt+F8. Co zauważasz? Dodaj nowy arkusz i ponownie uruchom makro. Co zauważasz?

Jak stosować zarejestrowane makro?

Makro możemy uruchomić poprzez:

1. Użycie ustalonego skrótu.

2. W karcie Deweloper /Makra uruchamiamy odpowiednie makro.

3. W karcie Widok klikamy zakładkę Makra.

4. Utworzenie przycisku dla tego makra (w danym arkuszu): w Deweloper/Wstaw wybieramy jeden przycisk Kontrolki

Formularza, zaznaczamy miejsce przycisku w arkuszu, wstawiamy opis, przypisujemy Makro (uruchamiamy prawym

przyciskiem myszy).

5. Z poziomu edytora Visual Basic przycisk Run (lubF5) gdy kursor znajduje się w makrze,

Inne sposoby uruchomienia makr:

1. W oknie Immediate wpisujemy nazwę makra i klikamy Enter,

2. Umieszczamy makro na wstążce (Plik → Opcje → Dostosowywanie wstążki → Wybierz polecenia → Makra →

Wybór makra → Dodaj do odpowiedniej karty).

3. Umieszczamy makro na rozwijanym pasku Szybki Dostęp (Plik → Opcje → Pasek narzędzi Szybki Dostęp → Wybierz

polecenia → Makra → Wybór makra → Dodaj).

4. Podobnie do 4 sposobu z użyciem dowolnych kształtów (Clipart, Word Art., Smart Art).

Ćwiczenie 2. Zmodyfikuj swoje makro z Ćwiczenia 1. tak, aby można je było uruchomić za pomocą skrótu klawiaturowego. Przetestuj w nowym arkuszu działanie.

Ćwiczenie 3. Przypisz do makra utworzonego w Ćwiczeniu 1. ikonę/przycisk.

Ćwiczenie 4. Otwórz okno edytora VBA i zakomentuj to co Twoim zdaniem jest niepotrzebne.

Odwołania względne i bezwzględne

Podczas rejestrowania makr Excel domyślnie używa odwołań bezwzględnych. Można to było zaobserwować w Ćwiczeniu 1.

Ćwiczenie 5. Wykonaj polecenie z Ćwiczenia 1. pamiętając, aby kliknąć na karcie Deweloper – użyj odwołań względnych. Powiedz, co zauważyłeś.

Ćwiczenie 6. Stwórz tabelkę (3x3). Zarejestruj makro, które formatuje tabelkę (3x3) w następujący sposób:

• Napisy w pierwszym wierszu pogrubia, ustawia rozmiar na 15, czcionka: Consolas, kolor czcionki: czerwony, kolor tła: jasny zielony.

• W drugim wierszu kolor czcionki: niebieski • W trzecim wierszu kolor czcionki: biały, wypełnienie: czarne

Zastanów się, czy użyć odwołań względnych, czy bezwzględnych.

Ćwiczenie 7. Zarejestruj makro wypisujące kolejne miesiące (od stycznia do czerwca) w odpowiedniej kolumnie. W tym celu:

• Uaktywnij arkusz i rozpocznij rejestrowanie makra. • Kliknij komórkę B1 i wpisz w niej styczeń• Przejdź do komórki C1 i wpisz luty• Kontynuuj wpisywanie, aż do wprowadzenia do zakresu B1:G1 nazw pierwszych sześciu miesięcy roku• Kliknij komórkę B1, aby ją ponownie uaktywnić.• Zakończ rejestrowanie makra.

Ćwiczenie 8. Wykonaj polecenia z Ćwiczenia 7. klikając w trakcie rejestrowania makra na przycisk – Użyj odwołań względnych. Otwórz edytor Visual Basic i porównaj oba kody.

Ćwiczenie 9. Skopiuj kod z powyższego ćwiczenia i zamień na następujący:

Sub zad 9()ActiveCell.Offset(0, 0) = ”Styczeń”ActiveCell.Offset(0, 1) = ”Luty”ActiveCell.Offset(0, 2) = ”Marzec”ActiveCell.Offset(0, 3) = ”Kwiecień”ActiveCell.Offset(0, 4) = ”Maj”ActiveCell.Offset(0, 5) = ”Czerwiec”

End Sub

Ćwiczenie 10. Zastosuj konstrukcję With … End With do poprzedniego zadania i sprawdź działanie makra. Twój kod powinien wyglądać następująco:

Sub cwiczenie10()With ActiveCell

.Offset(0, 0) = ”Styczeń”

.Offset(0, 1) = ”Luty”

.Offset(0, 2) = ”Marzec”

.Offset(0, 3) = ”Kwiecień”

.Offset(0, 4) = ”Maj”

.Offset(0, 5) = ”Czerwiec”End WithEnd Sub

Uwaga 1. Pamiętaj, że rejestrator makr nie zawsze generuje najwydajniejszy kod.

1. Rejestrowane makro można zapisać w:

a. Ten skoroszyt – dostępne tylko w arkuszach tego skoroszytu.

b. Nowy skoroszyt – zapisze się w tym nowym skoroszycie i będzie dostępne gdy ten skoroszyt będzie otwarty.

c. Skoroszyt makr osobistych – dostępne zawsze po uruchomieniu Excela.

Ćwiczenie 11. Zarejestrować 2 proste makra: jedno w nowym skoroszycie, a drugie w Skoroszycie makr osobistych oraz

wypróbować ich działanie.

Uwaga 2. Rejestrowanie makr ogranicza się tylko do poleceń Excela, a więc ma ograniczone możliwości (np. nie można

stosować pętli). Pełną funkcjonalność mają makra pisane ręcznie i korzystające z poleceń VBA. O tym dalej.

Uwaga 3. Zarejestrowane makro można modyfikować zmieniając („ręcznie”) kod programu makra. Do tego potrzebna

jest znajomość instrukcji VBA, które poznamy dalej.

Uwaga 4. Zarejestrowane makro można wykorzystać do pisania własnych makr. Wykonujemy odpowiednie czynności w Excelu, rejestrujemy makro i podglądamy jak wyglądają instrukcje w kodzie programu.

Wygląd Edytora

Wygląd edytora możesz łatwo zmienić dostosowując go do swoich potrzeb. Po uruchomieniu edytora Visual Basic z menu Tools wybierz polecenie Options. Na ekranie pojawi się okno dialogowe zawierające cztery karty – Editor, Editor Format, General oraz Docking.Nie będziemy tu omawiać szczegółowo wszystkich opcji ustawień. Dla przykładu sprawdzimy czy opcje:

• Auto Syntax Check (wyświetlanie okna dialogowego po wykryciu błędu składni), • Auto list Members (pomoc w trakcie wprowadzania kodu), • Auto Quick Info (wyświetla informacje o argumentach funkcji), • Auto Data Tips (po ustawieniu wskaźnika myszy nad wybraną zmienną edytor wyświetli na ekranie jej

wartość)

są włączone i zmienimy sobie kolor słów kluczowych na niebieski:

Tools → Options → Editor Format → Code Colors –wybierz Keyword Text i w polu Foreground zmień kolor na granatowy

Sposób II. Makra typu Sub pisane osobiście (ręcznie)

Struktura języka VBA

Język programowania VBA jest językiem obiektowym. Oznacza to, że najważniejszymi elementami tego języka są obiekty. Każdy produkt Microsoftu (Word, Excel itp.) ma swój zestaw obiektów (w tym wiele wspólnych). Będziemy omawiać obiekty związane z Excelem. Obiektami są np. skoroszyty (Workbooks), arkusze (Worksheets), wykresy (Charts), zakresy komórek (Ranges), wiersze (Rows) itp. Obiekty mogą być kolekcjami złożonymi z innych obiektów np. Worksheets jest obiektem złożonym z arkuszy danego skoroszytu, które też są obiektami. Do elementu kolekcji odwołujemy się poprzez numer np. Worksheets(2) lub przez nazwę np. Worksheets(„Arkusz1”)

• Obiekty uporządkowane są hierarchicznie np. Workbook → Worksheet → Range

• Na obiektach wykonujemy operacje zwane metodami (Methods)

• Obiekty mają własności (Properties).

• Lista obiektów Excela z wymienionymi metodami i własnościami: Editor Visual Basic → Help → Excel VBA reference

→ Object model

Poza obiektami w języku VBA występują: zmienne, pętle, zdarzenia, słowa kluczowe, stałe.

Elementy tworzenia makra (procedury, aplikacji):

• Napisanie kodu źródłowego,• Testowanie makra,• Wywoływanie makra,

Kod źródłowy makra typu Sub - struktura

Sub NazwaProgramu()InstrukcjeEnd Sub

Program wpisujemy w module w edytorze Visual Basic Editor:

Deweloper → Visual Basic →Insert → Module

Ćwiczenie 12. Napisać pierwsze makro, wykorzystując funkcję MsgBox:

Sub MojeNazwisko()MsgBox ”Imie i Nazwisko”End Sub

Ćwiczenie 13. Przeczytać pomoc dotyczącą funkcji MsgBox i napisać makro z rozbudowanym MsgBox.

Podstawowe instrukcje związane z obiektami w VBA

Object.Method lub Object.Property

Przykład 4. Potoczny: Piłkę(2).KopnijZ VBA: Range(”A1”).ClearContents, Worksheets.Add

Przykład 5. Potoczny: Piłkę(2).KolorZ VBA: Range(”A1”).Value

Metody mają parametry:

Piłkę(2).Kopnij Kierunek:=Lewo, Moc:=Duża itp. lubPiłkę(2).Kopnij Lewo, Duża (gdy znamy kolejność parametrów)

Worksheets.Add Before:=Worksheets(1) lub Worksheets.Add Worksheets(1) lubWorksheets.Add(Worksheets(1))

Worksheets.Add After:=Worksheets(1) lub Worksheets.Add ,Worksheets(1) lubWorksheets.Add( ,Worksheets(1))

Ćwiczenie 14. Sprawdzić w oknie Object Browser parametry i ich kolejność dla metody Add obiektu Worksheets i przeczytać pomoc przez użycie klawisza F1. Napisać makro dodające 3 nowe arkusze.

Własności również mają: parametry, wartości, możemy nadawać wartości i mogą zwracać obiekty:

• Parametry Range(”A2”).Characters Start:=3, Length:=2 lub Range(”A2”).Characters(3, 2)

• Wartości Range(”A3”).Value, lub Range(”A3”).Address

• Nadawanie wartości własnościom (o ile ta własność nie jest tylko do odczytu jak np. address)

Range(”A3”).Value=90

• Wartościami własności mogą być obiekty. Standardowy przykład: ActiveWorkbook, ActiveSheet, ActiveCell obiektu

Application.

Ćwiczenie 15. Napisać makro podające w MsgBoxie symbole słowa w pewnej komórce od 2 do 6 symbolu (wykorzystać własność Text).

Ćwiczenie 16. a) Napisać makro nadające wartość 100 we wszystkich komórkach pewnego zakresu.b) Sprawdzić, czy w danej komórce jest formuła (wykorzystać własność HasFormula obiektu Range).

Ćwiczenie 17. Napisać makro które w komórce D7 podaje wartość aktualnej komórki.

Oprócz obiektów, ich własności i metod na nich program VBA dodatkowo składa się z:

• Instrukcji VBA (np. GoTo, If-Then-Else, Dim,…),

• Wbudowanych funkcji (podobnych do funkcji Excela ale o nazwach angielskich (np. DŁ w Excelu, a Len w

VBA),

• Zmiennych – dowolny ciąg symboli zaczynający się od litery i różny od wbudowanych instrukcji - o nich za

chwilę.

1. Wbudowane instrukcje i funkcje (krótki opis w języku polskim) znajdują się na przykład w książce Walkenbacha.

2. Dokładne opisy i składnie instrukcji oraz funkcji znajdują się w systemie pomocy Microsoftu (w dowolnym module

VBA wpisujemy daną instrukcję lub funkcję i klikamy F1).

Ćwiczenie 18. Zobacz wyjaśnienia pomocy związane z funkcją Len. Napisać makro, które wykorzystuje funkcję Len.

Ćwiczenie 19. Zobacz wyjaśnienia pomocy związane z funkcją InputBox. Napiszemy makropolecenie o nazwie Dane które pobiera od użytkownika Imię i nazwisko, a następnie zwraca go jako komunikat:

Zadeklarujemy zmienne:

• Dim imie, nazwisko, osoba As String• Pobierzemy dane od użytkownika:• Imie=InputBox(”Podaj swoję imię”, ”Dane osobowe”)• W analogiczny sposób zapytaj o nazwisko• Zwróć w formie komunikatu wynik komendą• MsgBox(osoba)

Uwaga 5. Dane możemy pobierać/zwracać również z/do komórek:

X=Range(” ”).Value – wprowadzi do zmiennej X zawartość komórki,Range(” ”).Value=X – wprowadzi do komórki zawartość zmiennej X.

Ćwiczenie 20. W Ćwiczeniu 19. w makro Dane usuń linię MsgBox(osoba), a dopisz na końcu linie:

i = MsgBox(”Pan/Pani ” & imie & ” ” & nazwisko, 4, ”Potwierdź dane”)If i=6 ThenMsgBox(”Dziękuję za podanie danych”)ElseMsgBox(”Popraw dane”)End If

Ćwiczenie 21. Wyjaśnij, co jest źle i napraw to:

Sub input_zle()If InputBox(”Podaj imie”) = ”Hania” ThenMsgBox ”Dzien dobry, Haniu”ElseIf InputBox(”Podaj imie”) = ”Janusz” ThenMsgBox ”Dzien dobry, Januszu”End If

End Sub

Przykład 6. Użycie instrukcji GoTo

Sub gotoprzyklad()UserName = InputBox(”Podaj imię: ”)If UserName <> ”Jan” Then GoTo WrongnameMsgBox (”Witaj Janku! ”)Exit Sub

Wrongname:MsgBox ”Przykro mi, ale tylko Jan może uruchomić tę procedurę. ”

End Sub

Uwaga 6. Ta prosta instrukcja działa, ale nie jest to dobry przykład jej użycia. W praktyce instrukcja GoTo powinna być używana tylko i wyłącznie wtedy, gdy danej operacji nie można wykonać w inny sposób. Tak naprawdę jedyną sytuacją, w której naprawdę konieczne jest użycie instrukcji GoTo, jest przechwytywanie i obsługa błędów.

Zadania dodatkowe do samodzielnego zrobienia

Ćwiczenie 1. Zadeklaruj stałą pi (do 12 miejsc po przecinku) wewnątrz makra jako Double i wyświetl wMsgBoksie. Zmień typ na Single i porównaj wyniki.

Ćwiczenie 2. a) Zadeklaruj stałą pi w jednym makrze i spróbuj wyświetlić ją w MsgBoksie w drugim makrze.b) Przenieś deklarację pi na początek modułu (przed wszystkie makra, sekcja: Declarations). Czy teraz działa

dobrze?

Ćwiczenie 3. Napisz makro proszące w InputBoksie o hasło. Poprawne hasło to ``admin''. MsgBox ma wyświetlać komunikat ``Twoje hasło [tu: wprowadzone hasło] jest poprawne/niepoprawne''. Zwróć uwagę na działanie makra, gdy zadeklarujesz zmienną odpowiedzialną za hasło jako String*5 i użytkownik poda hasło ``admin1'' oraz, kiedy zadeklarujesz String*4 i użytkownik poda poprawne hasło.

Ćwiczenie 4. Napisz makropolecenie, które pobiera dane dotyczące stężenia tlenku węgla w powietrzu(mg/m 3 ) i następnie w oparciu o wybrane granice zwraca odpowiedni komunikat:a) Stan dobry,b) Stan umiarkowany,c) Stan alarmowy.Zaopatrz okna komunikatów w stosowne ikony.

Ćwiczenie 5. Napisz Makropolecenia o nazwie Przekrój obliczające pole przekroju rury o zadanej średnicy.Wynik ma być wprowadzony do wybranej komórki. W tym celu:• Zadeklaruj zmienne D i P typu rzeczywistego podwójnej precyzji (komenda Dim),• Pobierz wartość argumentu D komendą InputBox (użyj stosownych opisów),• Oblicz wartość funkcji za pomocą formuły P=3,14*D^2/4 (jeżeli chcemy użyć dokładniejszej wartości π, skorzystaj z

Excel.WorksheetFunction.Pi).• Wprowadź wynik do wybranej komórki (np. B2) komendą Range(”…”).Value=P.

Ćwiczenie 6. Napisz Makropolecenie o nazwie Srednia obliczające średnią z dwóch liczb.• NIE deklaruj zmiennych x, y typu Double,• Pobierz wartości x, y od użytkownika,• Oblicz wartość średniej do komórek B6 i B7 formułami:

Range(”B6”).Value = (0 + x + y) / 3 Range(”B7”).Value = (x + y) / 2

• Sprawdź działanie makropolecenia na liczbach 1 i 2.• Zapoznaj się z działaniem instrukcji CTyp, a następnie popraw makropolecenie.• Usuń instrukcje CDbl, ale zadeklaruj zmienne x,y typu Double.

Ćwiczenie 7. Napisz Makropolecenie o nazwie Zamiana, które zamienia wartości dwóch komórek, np. A2 i B2.(Wskazówka: wprowadź dodatkową zmienną typu Variant w celu zapamiętania wartości jednej z komórek).

Uwaga: W VBA istnieje również prostsza forma zapisu zmiennej związanej z komórką arkuszakalkulacyjnego. Zamiast np. Range(”C1”) można też stosować zapis [C1].