Uniwersytet Łódzki
Wydział Matematyki i Informatyki
Arkusze kalkulacyjne i VBA
Tadeusz Krasiński
Lekcja 1. Wprowadzenie do VBA Adres: www.math.uni.lodz.pl/~krasinsk
StudiaPodyplomowe/20172018
Studia Podyplomowe „Analiza danych i data mining”
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 podyplo- mowych tzn. do 30 września 2018 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
1. Podstawowa znajomość programu Excel.
Visual Basic for Applications I
1. 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.
2. Podstawowe zalety:
a. VBA jest wbudowany (zespolony) w programy Office, w szczególności w program Excel,
b. Rozbudowany system pomocy (poprzez Internet).
3. Historia VBA:
a. Język programowania BASIC – lata sześćdziesiąte ubiegłego wieku,
b. Język programowania Visual Basic for Windows - lata dziewięćdziesiąte ubiegłego wieku,
c. Język programowania Visual Basic for Applications – 1993 (do wersji Excel nr 5).
4. Makro – sekwencja instrukcji (podprogram, procedura) napisana w języku programowania VBA - Visual Basic for Applications.
5. Za pomocą makr możemy np.:
a. Wstawiać automatycznie dany tekst, dane, tabele, tworzyć raporty itp.
b. Zautomatyzować często wykonywaną procedurę lub operację, c. Utworzyć własne polecenie,
d. Utworzyć nową funkcję,
e. Tworzyć okna dialogowe, w których wybierając odpowiednie możliwości generujemy inne procedury (zdarzenia).
f. Tworzyć nowe aplikacje, które możemy dodać do poleceń Excela.
Bibliografia:
1. John Walkenbach, Excel 2013 PL. Biblia. Helion 2013.
2. John Walkenbach, Excel 2013 PL Programowanie w VBA.
Helion 2013.
Do wypożyczenia w bibliotece.
Przykłady zastosowania:
1. Prosty program porządkujący dane Demo1Porzadek.xlsm
2. Program z oknem dialogowym dotyczącym wczasów
Demo2Wczasy.xlsm
3. Program do generowania logo Demo3Logo.xlsm
Programowanie w VBA
Aby programować w VBA musimy umieścić kartę Deweloper na wstążce (uruchamiamy poprzez polecenie Plik\Opcje\Dostosowywanie Wstążki -zaznaczyć Deweloper).
Dwa typy makr:
4. Makra typu Sub - podstawowy typ programu w VBA, 5. Makra typu Function - do tworzenia nowych funkcji.
Makra typu Sub
Makra VBA typu Sub tworzymy na dwa sposoby:
I. 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.
II. 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?
Jak utworzyć i zapisać automatycznie makro?
Kolejność czynności:
1. W karcie Deweloper klikamy Zarejestruj makro (lub na pasku stanu).
2. 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.
3. Wykonujemy dowolne instrukcje Excela np. wpisujemy tekst, tworzymy tabelę.
4. 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.
Ćwiczenie 2.
Zarejestrować makro tworzące tabelę z pewnymi danymi i z wierszem sumy.
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:
6. W oknie Immediate wpisujemy nazwę makra i klikamy Enter, 7. Umieszczamy makro na wstążce (Plik/Opcje/Dostosowywanie
wstążki/Wybierz polecenia/Makra/Wybór makra/Dodaj do odpowiedniej karty).
8. Umieszczamy makro na rozwijanym pasku Szybki Dostęp (Plik/Opcje/Pasek narzędzi Szybki Dostęp/Wybierz
polecenia/Makra/Wybór makra/Dodaj).
9. Podobnie do 4 sposobu z użyciem dowolnych kształtów (Clipart, Word Art., Smart Art).
Ćwiczenie 3. a) Wypróbować działanie makr z poprzedniego ćwiczenia w nowym arkuszu,
b)Utworzyć przyciski dla makr utworzonych wcześniej, c) 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).
Dodatkowe możliwości podczas rejestrowania automatycznego makra
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 pierwszej wybranej komórki.
Ćwiczenie 4. Zarejestować 2 makra z użyciem odwołań względnych w tym jedno z konstrukcją tabeli z wpisanymi danymi. Wypróbować ich działanie.
2. 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 4. Zarejestować 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.
Ćwiczenie 5. Zmodyfikować którekolwiek makro w jego kodzie (np.
zmieniając dane) i wypróbować jego działanie.
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.
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
Poza obiektami w języku VBA występują: zmienne, pętle, zdarzenia, słowa kluczowe, stałe.
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
Elementy tworzenia makra (procedury, aplikacji):
1. Napisanie kodu źródłowego, 2. Testowanie makra,
3. Wywoływanie makra,
Kod źródłowy macra typu Sub
1. Program (macro) typu Sub. Ma on podstawową strukturę:
Sub NazwaProgramu() Instrukcje
End Sub
Program wpisujemy w module w edytorze Visual Basic Editor:
1. Otwieramy kartę Deweloper, 2. Zakładka Visual Basic,
3. Tworzymy Module1 w zakładce Insert lub prawym przyciskiem myszy.
4. Wpisujemy program (makro) VBA.
Ćwiczenie.Napisać pierwsze makro, wykorzystując funkcję MsgBox:
Sub MojeNazwisko()
MsgBox „Tadeusz Krasiński”
End Sub
Ćwiczenie. 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 1. 1.Potoczny: Piłkę(2).Kopnij
3. Z VBA: Range(„A1”).ClearContents, Worksheets.Add Przykład 2. 1.Potoczny: Piłkę(2).Kolor
6. Z VBA: Range(„A1”).Value Metody mają parametry:
Piłkę(2).Kopnij Kierunek:=Lewo, Moc:=Duża itp. Lub
Piłkę(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. 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:
1. Parametry Range(„A2”).Characters Start:=3, Length:=2 lub Range(„A2”).Characters(3, 2)
Ćwiczenie. Napisać makro podające w MsgBoxie symbole słowa w pewnej komórce od 2 do 6 symbolu (wykorzystać własność Text) 2. Wartości Range(„A3”).Value, lub Range(„A3”).Address
3. Nadawanie wartości własnościom (o ile ta własność nie jest tylko do odczytu jak np. address) Range(„A3”).Value=90
Ćwiczenie. 1.Napisać makro nadające wartość 100 we wszystkich komórkach pewnego zakresu.
2. Sprawdzić, czy w danej komórce jest formuła (wykorzystać własność HasFormula obiektu Range.
4 Wartościami własności mogą być obiekty. Standardowy przykład: ActiveWorkbook, ActiveSheet, ActiveCell obiektu Application.
Ćwiczenie. 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:
i. Instrukcji VBA(np. GoTo, If-Then-Else, Dim,…), ii. Wbudowanych funkcji (podobnych do funkcji
Excela ale o nazwach angielskich (np. DŁ w Excelu, a Len w VBA),
iii. 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) – wszystkie w Chmurze OneDrive pod adresem:
http://1drv.ms/1vagarf lub http://1drv.ms/1ykgWD8 lub w książce Walkenbacha.
2. Pod tym adresem znajdują się też numery błędów z ich
krótkimi opisami w języku polskim (z książki Walkenbacha).
3. Dokładne opisy i składnie instrukcji oraz funkcji znajdują się w systemie pomocy Microsoftu (w dowolnym module
Ćwiczenie. funkcji InputBox, Len itp.
Ćwiczenie. Zobacz wyjaśnienia pomocy związane z instrukcją GoTo języka VBA. Napisać makro, które w zależności od wartości w komórce np. A3 wykonuje różne podmakra. Wykorzystać
warunek: If……Then…..Else….
Ćwiczenie. Zobacz wyjaśnienia pomocy związane z funkcją Len.
Napisać makro, które wykorzystuje funkcję Len.
Ćwiczenie. Zobacz wyjaśnienia pomocy związane z funkcją InputBox. Napisać makro, które wykorzystuje tę funkcję
