Arkusze kalkulacyjne i VBA

Arkusze kalkulacyjne i VBA

Uniwersytet Łódzki

Wydział Matematyki i Informatyki

Tadeusz Krasiński

Lekcja 1. Wprowadzenie do VBA Adres: www.math.uni.lodz.pl/~krasinsk

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: krasinsk@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. J. Walkenbach, Excel 2013 PL. Biblia. Helion 2013. (do wypożyczenia w bibliotece Wydziału)

2. J. Walkenbach, Excel 2013 PL Programowanie w VBA. Helion 2013. (do wypożyczenia w bibliotece Wydziału).

3. M. Aleksander, D. Kusleika. Excel 2016 PL Programowanie w VBA. Helion 2017.

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.

Przykłady zastosowania:

Przykład 1. Prosty program porządkujący dane: Demo1Porzadek.xlsm

Przykład 2. Program z oknem dialogowym dotyczącym wczasów: Demo2Wczasy.xlsm Przykład 3. Program do generowania logo: Demo3Logo.xlsm

Karta Deweloper

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

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

Dwa typy makr (=programów):

• Makra typu Sub - podstawowy typ programu w VBA,

• Makra typu Function - do tworzenia nowych funkcji.

Makra typu Sub Makra 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ć automatycznie 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 lub ) 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. przycisk.

Ćwiczenie 4. a)Utworzyć przyciski dla makr utworzonych wcześniej, b) zastosować inne sposoby uruchamiania makra.

Uwaga 1. Powyższe sposoby uruchamiania będą dotyczyć również makr napisanych przez nas (a nie tylko

zarejestrowanych). Zatem nie będziemy już wracać do tematu uruchamiania makr (przypisujemy skrót do takiego makra poprzez kliknięcie go prawym przyciskiem na liście makr i wybieramy przycisk Opcje).

Odwołania względne i bezwzględne

Podczas rejestrowania makr Excel domyślnie używa odwołań bezwzględnych, tzn. jeśli np. w trakcie rejestracji makra coś wpisaliśmy w komórkę B1, to po uruchomieniu makra, makro to wpisze to coś również w komórkę B1. Można to było zaobserwować w Ćwiczeniu 1.

Opcjonalnie można włączyć Użyj odwołań względnych aby czynności Excela, przy uruchomieniu zarejestrowanego makra, były wykonywane względem aktualnej komórki.

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

Przetestuj działanie takiego makra dla różnych wyborów aktualnych komórek.

Ć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 poniższy kod nowego makra i uruchom je:

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

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.

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

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, funkcjami (Methods). Każdy obiekt ma swoje specyficzne metody.

• Obiekty mają własności (Properties). Każdy obiekt ma swoje specyficzne własności.

• 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 (uruchamianie) makra,

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

Instrukcje End 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łki(2).Kopnij

Z VBA: Range(”A1”).ClearContents, Worksheets.Add Przykład 5. Potoczny: Piłki(2).Kolor

Z VBA: Range(”A1”).Value

Metody mają parametry:

Piłki(2).Kopnij Kierunek:=Lewo, Moc:=Duża itp. lub

Piłki(2).Kopnij Lewo, Duża (gdy znamy kolejność parametrów)

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

Worksheets.Add After:=Worksheets(1) lub Worksheets.Add ,Worksheets(1) lub Worksheets.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 różnych miejscach.

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ę (np. x1, x2, xyz, aa34, data1; nie można go, data).

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:

:

• Deklarujemy zmienne: Dim imie, nazwisko, osoba As String

• Pobieramy dane od użytkownika: imie=InputBox(„Podaj swoję imię”, ”Dane osobowe”)

• W analogiczny sposób pytamy o nazwisko

• Łączymy imie i nazwisko symbolem &

• Zwróć w formie komunikatu wynik funkcją MsgBox(osoba)

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

X=Range(”A5 ”).Value –zmienna X ma wartość komórki A5,

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

Set xx=Range(„A4:C8”) –zmienna xx jest obiektem (zakresem komórek)

Ć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 Then

MsgBox(”Dziękuję za podanie danych”) Else

MsgBox(”Popraw dane”) End If

Ćwiczenie 21. Wyjaśnij, co jest źle w poniższym makro i napraw to:

Sub input_zle()

If InputBox(”Podaj imie”) = ”Hania” Then MsgBox ”Dzien dobry, Haniu”

ElseIf InputBox(”Podaj imie”) = ”Janusz” Then MsgBox ”Dzien dobry, Januszu”

End If End Sub

Przykład 6. Użycie instrukcji GoTo (skok do podprogramu) i przerwanie wykonywania programu (Exit Sub)

Sub gotoprzyklad()

UserName = InputBox(”Podaj imię: ”)

If UserName <> ”Jan” Then GoTo Wrongname MsgBox (”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.