Kurs VBA, cz.I & II
description
Transcript of Kurs VBA, cz.I & II
Kurs VBA, cz.I&II
Tomasz MiklewiczMateusz Piękoś
Na podstawie: A. Snarska, Makropolecenia w Excelu
Agenda
1. Wstęp
2. Środowisko makr
3. Zmienne i ich typy
4. Komunikacja z użytkownikiem
5. Zmienne obiektowe Excela
1. Wstęp
• Sprawy organizacyjne
• VBA – do czego się przydaje?
• Przykłady zastosowań w pracy
2. Środowisko makr
2.1 Dostosowanie Excela2.2 Nagrywanie makr2.3 Edycja makr
2.1 Dostosowanie Excela
• Przycisk pakietu Office ->Opcje->Popularne->Pokaż kartę Deweloper na wstążce
• Deweloper->Bezpieczeństwo makr->Włącz wszystkie makra
• Zapisuj pliki z rozszerzeniem .xlsm, używanie innych formatów może spowodować utratę kodu
2.2 Nagrywanie makr
• Aby rozpocząć: Deweloper->Zarejestruj makro
• Aby zakończyć: Deweloper->Zatrzymaj rejestrowanie
• Odwołania względne/bezwględne – określają, czy makro wykonuje czynność na obszarze określonym względem aktualnej selekcji (względne) czy zawsze na tym samym obszarze (bezwględne)
• Aby usunąć niepotrzebne makro wybierz Deweloper->Makra->Usuń
2.2 Nagrywanie makr
Aby uruchomić nagrane makro z poziomu arkusza:• Deweloper->Makra->Uruchom
• Istnieje możliwość przypisania makru skrótu klawiszowy na etapie jego tworzenia lub później wybierając Makra->Opcje->Klawisz skrótu (uwaga: lepiej nie przypisywać popularnych skrótów jak Ctrl+C, Ctrl+V etc.)
• Alternatywnie można umieścić w arkuszu przycisk wybierając Dweloper->Wstaw->Formanty formularza->Przycisk, a następnie wybrać z listy makro, które chcemy mu przypisać
Przykład 2.1:Zarejestruj makro wpisujące dowolny tekst do komórki A1.
Zadanie 2.1: *Nagraj makro, które będzie wybierało Arkusz2. Umieść przycisk uruchamiający je w Arkuszu1
Zadanie 2.2: *Zarejestruj makro ustalające dla aktywnej komórki pogrubienie oraz podkreślenie czcionki. Wypróbuj jej działanie dla komórki z wpisanym tekstem.
Zadanie 2.3: *Zarejestruj makro selekcjonujące i otaczające w ramkę komórkę A1 aktywnego arkusza. Przypisz mu skrót klawiszowy.
Zadanie 2.4 **Zarejestruj dwa makra, spośród których jedno będzie zmieniać kolor aktywnych komórek na niebieski, a drugie będzie czyściło aktywne komórkę z wszelkich kolorów. Umieść obok siebie przyciski uruchamiające oba makra
2.3 Edycja makr• Developer->Edytor makr
2.3 Edycja makr
• Każde makro zaczyna się od wyrażenia Sub Nazwa() a kończy wyrażeniem End Sub.
• W przypadku długich wierszy poleceń, można przenieść część ich treści do następnej linijki stosując jako łącznik znak _ napisany po spacji.
• Znakiem komentarza w VBA jest apostrof ‘ umieszczony po lewej stronie
• Klawiszem F5 wywołujemy całe makro, w którym aktualnie znajduje się kursor, natomiast klawiszem F8 możemy wykonywać polecenia makra linijka po linijce
2.3 Edycja makr
• Nagrywanie makr powoduje rejestrowanie każdej wykonanej czynności, co powoduje odkładanie się „śmieci” w kodzie, zmniejszając jego szybkość działania oraz czytelność
• Dlatego należy uważać, aby podczas nagrywania nie wykonywać zbędnych czynności
• Otwierając nagrane makro w edytorze VBA, można usunąć z niego niepotrzebne elementy
2.3 Edycja makr• Kolory w VBA
.colorindex= .color=RGB .colorindex .color=RGB .colorindex .color=RGB .colorindex .color=RGB1 (0, 0, 0) 15 (192, 192, 192) 29 (128, 0, 128) 43 (153, 204, 0)2 (255, 255, 255) 16 (128, 128, 128) 30 (128, 0, 0) 44 (255, 204, 0)3 (255, 0, 0) 17 (153, 153, 255) 31 (0, 128, 128) 45 (255, 153, 0)4 (0, 255, 0) 18 (153, 51, 102) 32 (0, 0, 255) 46 (255, 102, 0)5 (0, 0, 255) 19 (255, 255, 204) 33 (0, 204, 255) 47 (102, 102, 153)6 (255, 255, 0) 20 (204, 255, 255) 34 (204, 255, 255) 48 (150, 150, 150)7 (255, 0, 255) 21 (102, 0, 102) 35 (204, 255, 204) 49 (0, 51, 102)8 (0, 255, 255) 22 (255, 128, 128) 36 (255, 255, 153) 50 (51, 153, 102)9 (128, 0, 0) 23 (0, 102, 204) 37 (153, 204, 255) 51 (0, 51, 0)10 (0, 128, 0) 24 (204, 204, 255) 38 (255, 153, 204) 52 (51, 51, 0)11 (0, 0, 128) 25 (0, 0, 128) 39 (204, 153, 255) 53 (153, 51, 0)12 (128, 128, 0) 26 (255, 0, 255) 40 (255, 204, 153) 54 (153, 51, 102)13 (128, 0, 128) 27 (255, 255, 0) 41 (51, 102, 255) 55 (51, 51, 153)14 (0, 128, 128) 28 (0, 255, 255) 42 (51, 204, 204) 56 (51, 51, 51)
• Zapis kolorów przy pomocy notacji RGB pozwala na dostęp do szerszej palety kolorów niż standardowe 56
Przykład 2.2:Przy pomocy Edytora VBA spraw, aby makro z zadania 1
wybierało Arkusz3 zamiast Arkusza2. Zapisz je jako oddzielne makro, również przy pomocy Edytora VBA.
Zadanie 2.5: *Przy pomocy Edytora VBA spraw, aby makro z zadania 4 zamiast koloru niebieskiego, zmieniało tło komórki na kolor inny niż niebieski. Zapisz je jako oddzielne makro, również przy pomocy Edytora VBA.
Zadanie 2.6: ** Przy pomocy Edytora VBA spraw, aby makro z zadania 2 zamiast pogrubienia, powodowało pochylenie czcionki. Usuń z kodu niepotrzebne elementy tak, aby zostały tylko niezbędne instrukcje.
3.Zmienne i ich typy
3.1 Typy zmiennych3.2 Deklaracja zmiennych3.3 Instrukcja przypisania3.4 Podstawowe funkcje matematyczne3.5 Funkcje tekstowe3.6 Funkcje czasowe
3.1 Typy zmiennych
Nazwa zmiennej Typ zmiennej Przyjmowane wartości
Integer Liczbowa Liczby całkowite
Double Liczbowa Liczby rzeczywiste
String Tekstowa Tekst
Date Daty Data
Boolean Logiczna True, False
Variant Wszystkie typy Dowolna
3.2 Deklaracja zmiennej
• Deklaracja:Dim zmienna1 as Integer, zmienna2 as String
• Option explicit – wymusza kontrole typów zmiennych
3.3 Instrukcja przypisania
• Instrukcja przypisania: Zmienna = wartość
a = 10b = a/4.5c = Range(„A1”).Valued = „Karuzela”e = #01/02/2013#Range(„A1”).Value = 70
Przykład 3.1:Obliczymy sumę kwadratów komórek A1 i A2, wynik wpiszemy
do komórki A2
Zadanie 3.1: *Napisz makro, które liczy średnią z dwóch liczb z komórek A7 i A8, wynik zapisuje w komórce A9
Zadanie 3.2: **Napisz makro, które przestawia zawartość komórek A1 i A2.
3.4 Podstawowe funkcje matematyczne
• Dozwolone operacje matematyczne:+ dodawanie- odejmowanie* Mnożenie/ dzielenie^ potęgowanie
3.4 Podstawowe funkcje matematyczne
Abs(x) - zwraca wartość bezwględną z xInt(x) – zwraca część całkowitą liczby xSqr(x) – zwraca pierwiastek kwadratowy z xSin(x), Cos(x), Tan(x) – funkcje trygonometryczneRnd – zwraca wartość losową z przedziału (0,1)
• Funkcje Excela:Application.WorksheetFunction.Average()
Zadanie 3.3 * - przeciwprostokątnaNapisz makro, które obliczy długość przeciwprostokątnej w trójkącie prostokątnym na podstawie długości boków a i b
Podstaw a=3, b=4 i sprawdź czy działa
Zadanie 3.4 ** – funkcja kwadratowaNapisz makro, które obliczy miejsca zerowe funkcji kwadratowej na podstawie parametrów a, b i c. y=ax2+bx+c
Podstaw a=1, b=-5, c=6Podstaw a=1, b=-4, c=4i sprawdź czy działa
Podpowiedź:Instrukcja warunkowa w VBA:If warunek thenOperacjeElseInstrukcjeEnd if
Przykład 3.2 - oceny w szkole :Do komórek B1:B5 wpiszemy liczby losowe z przedziału 1 – 6.
Zadanie 3.5 *** – oceny na studiachNapisz makro, które będzie losować liczby z przedziału 2 do 5,5, z krokiem 0,5
3.5 Funkcje tekstowe
Str(x) – zamienia liczbę x na tekst
Ucase(t) – zwraca tekst t, w którym wszystkie litery są duże
Left(t, x) – zwraca początkowe x znaków zmiennej tekstowej t
Right(t, x) – zwraca końcowe x znaków zmiennej zmiennej
tekstowej t
Mid(t, start, x) – zwraca x znaków zmiennej tekstowej t od
pozycji start
Przykład 3.3:Wytniemy z tekstu numer indeksu studenta
Jaś Fasola 34512.
Zadanie 3.6 ** – FakturyWytnij z poniższych tekstów numer faktury:
Faktura nr: 54205 wartość: 2500 złFaktura nr: 24510 wartość: 35 000 złFaktura nr: 22301 wartość: 1 000 000 zł
3.6 Funkcje czasowe
Date – zwraca dzisiejszą dateDay(d) – zwraca numer dnia podanej daty dMonth(d) – zwraca numer miesiąca podanej daty dYear(d) – zwraca rok w postaci liczbyWeekDay(d, vbMonday) – zwraca numer dnia tygodnia
Przykład 3.4:Sprawdzimy w jaki dzień tygodnia wypada Sylwester 2014.
Zadanie 3.7* – UrodzinySprawdź w jaki dzień tygodnia się urodziłeś, urodziłaś.
Przykład 3.5:Obliczymy ile dni minęło od końca Świata (od 21.12.2012)
Zadanie 3.8*Napisz makro, które podaje ile przeżyłeś (przeżyłaś) dni
Zadanie 3.9**Napisz makro, które podaje ile przeżyłeś (przeżyłaś) pełnych miesięcy
Zadanie 3.10***Napisz makro, które oblicza liczbę miesięcy pomiędzy dwoma dowolnymi datami z dokładnością do jednego miesiąca
Ciekawostka - Debugowanie
• Przycisk F8
• Okno zmiennych lokalnych:View -> Locals Window
4. Komunikacja z użytkownikiem
4.1 Wyświetlanie informacji4.2 Wczytywanie danych
4.1 Wyświetlanie informacji
• Do wyświetlenia informacji z programu służy funkcja MsgBoxMsgBox(Prompt, Buttons, Title)
• Parametry:Prompt – wyrażenie String wyświetlane w oknieTitle – wyrażenie String wyświetlane na pasku tytułowym Buttons – liczba określająca ilość i rodzaj przycisków, stanowi unikalną sumę wartości liczbowych cech określonych w tabelach na następnych slajdach
4.1 Wyświetlanie informacji
Symbol stałej Wartość liczbowa Wyświetlane przyciski
vbOKOnly 0 OK
vbOKCancel 1 OK, Anuluj
vbAbortRetryIgnore 2 OK, Przerwij, Ponów próbę, Zignoruj
vbYesNoCancel 3 Tak, Nie, Anuluj
vbYesNo 4 Tak, Nie
vbRetryCancel 5 Ponów próbę, Anuluj
4.1 Wyświetlanie informacji
Symbol stałej Wartość liczbowa Symbol graficzny
vbCritical 16 Komunikat błędu
vbQuestion 32 Pytanie
vbExclamation 48 OstrzeżenievbInformation 64 Informacja
4.1 Wyświetlanie informacji
• Do stworzenia treści komunikatu w MsgBoxie możemy wykorzystać różne ciągi znaków łącząc je znakiem &. Zmienne można zamieszczać w treści bez dodawania cudzysłowu, natomiast dla ciągów znaków jest on konieczny.
• Aby przejść do nowej linijki należy w treści wpisać & char(10) lub & vbNewLine
Przykład 4.1:Napisz makro powodujące wyświetlenie okna z przyciskami
Przerwij, Ponów Próbę i Zignoruj, o tytule „Brak miejsca na dysku”, treści „Zapis niemożliwy z powodu braku miejsca na dysku” i symbolem graficznym ostrzeżenia.
.
Przykład 4.2:Napisz makro powodujące wyświetlenie okna o przyciskach Tak i
Nie i treści:”Czy wyświetlić wynik działania 64 * 48?”. Zamiast posługiwać się jednym ciągiem znaków, spróbuj zapisać ten tekst posługując się zmiennymi liczbowymi.
Korzystając z instrukcji warunkowej if spraw ,aby przycisk Tak powodował wyświetlenie okna o treści „Wynik to:”, natomiast samego wyniku w następnej linijce jako zmiennej. Przycisk Nie ma natomiast zamykać pierwsze okno.
Zadanie 4.1*Napisz makro, które wyświetla okno komunikatu zawierające treść „Błąd krytyczny– kontynuować?”, tytule „Błąd”, przyciskach Tak, Nie, Anuluj oraz z symbolem błędu. Wartość liczbową przycisku wybranego przez użytkownika wyświetl w następnym oknie.
4.1 Wyświetlanie informacji
Stała Wartość liczbowa Wybrany przycisk
vbOK 1 OK
vbCancel 2 Anuluj
vbAbort 3 Przerwij
vbRetry 4 Ponów próę
vbIgnore 5 ZignorujvbYes 6 Tak
vbNo 7 Nie
• Wartości zwracane przez funkcję MsgBox zależą od przycisku, jaki wybierze użytkownik. Możesz je odczytać przypisując funkcję MsgBox do zdefiniowanej przez siebie zmiennej całkowitej.
Zadanie 4.2*Napisz makro, które wyświetla okno komunikatu zawierające aktualny czas i datę. Zastosuj typ okna z przyciskiem i symbolem informacyjnym. W jednym wierszu dodaj czas, a w drugim datę.
Wskazówka: Czas uzyskasz z funkcji Time$, a datę z Date$
4.2 Wczytywanie danych
• Do pobierania danych od użytkownika służą nie tylko przyciski w MsgBox, ale także w szerszym stopniu funkcja InputBox. W tym przypadku użytkownik może samemu wpisać własną odpowiedź.
InputBox(Prompt, Title, Default)
Default – wyrażenie typu string wyświetlane w polu edycji jako wartość domyślna
4.2 Wczytywanie danych
• Wczytywanie danych przy pomocy okna InputBox niesie ze sobą ryzyko wprowadzenia błędnego typu danych rzez użytkownika. Dlatego też wprowadzono obsługę błędów poprzez instrukcję On Error GoTo Et, aby nie powodowało to zawieszenia całego makra.
• Instrukcja On Error GoTo Et sprawia, że w razie wystąpienia błędu w wyniku wykonania kodu, makro automatycznie przenosi się do dodatkowej instrukcji Et: <Kod>, która znajduje się na końcu makra, w następnym wierszu po Exit Sub.
Przykład 4.3:Napisz makro, które wczytuje liczbę od użytkownika i wyświetla
jej potrojoną wartość. Zaimplementuj obsługę błędów.
Zadanie 4.3*Napisz makro wczytujące liczbę z następującą obsługą błędu – jeśli wczytany ciąg znaków nie jest liczbą różną od 0, wyświetl komunikat „Błąd danych”. Jeśli liczba jest poprawna, wyświetl komunikat zawierający jej wartość.
Nie używaj instrukcji warunkowej if – dopisz do makra taki kod, aby błąd danych był faktycznie błędem, dla którego należy stworzyć obsługę.
Zadanie 4.4 ** – pasek poborówPrzygotuj Excel do wydruku paska poborów według rysunku
poniżej. Stosując Input Box wpisz dane do pustych komórek (przy czym nazwisko i imię wczytuj w 2 oddzielnych oknach).
Następnie wywołaj podgląd wydruku (np. pomagając sobie w tym celu nagrywaniem makr). Formuły Premia, Brutto i Netto powinny działać automatycznie na podstawie wprowadzonych danych
Zadanie 4.5 ** – funkcja kwadratowaZmodyfikuj makro z zadania 3.4 tak, aby użytkownik mógł sam
wprowadzić parametry funkcji kwadratowej przy pomocy funkcji InputBox. Miejsca zerowe lub komunikat o ich braku wyświetl w następnym oknie.
Wskazówka – przydatna okaże się instrukcja warunkowa If
Ciekawostka: czy można stworzyć grę w Excelu?http://carywalkin.wordpress.com/2013/03/17/arena-xlsm-released/
5. Obiekty Excela
5.1 Co to jest obiekt?5.2 Deklaracja zmiennych obiektowych5.3 Instrukcja with5.4 Kolekcja obiektów5.5 Właściwość offset5.6 Właściwość CurrentRegion
5.1 Co to jest obiekt?
• Element składowy aplikacji Excel• Np. arkusz, komórka, zakres• Każdy obiekt ma swoje:
- Właściwości – np. kolor komórki- Metody – np. zaznaczenie obszaru, czyszczenie komórki
• Po kropce wyświetlają się możliwe metody i właściwości:• Obiekt.metoda / obiekt.właściwość
5.2 Deklaracja zmiennych obiektowych
Dim nazwa_zmiennej as obiekt
Set nazwa_zmiennej = wartość początkowa
Zadanie 5.1* - na rozgrzewkęPrzy pomocy zmiennej obiektowej zaznacz komórkę B2, zwiększ
jej wartość trzykrotnie i wyświetl.
5.3 Instrukcja with
• Pozwala skrócić kodWith obiektEnd with
• Instrukcje wykonywane na obiekcie, metody i właściwości poprzedzamy kropką
Przykład 5.1 – towary:Zbudujemy makro, które będzie wpisywać dane do tabeli
postaci:
Nazwa towaru Cena VAT
Zadanie 5.2*Przerób makro z przykładu wykorzystując instrukcje with.
5.4 Kolekcja obiektów
• Kolekcja to grupa obiektów. Wyróżniamy:Workbooks – otwarte skoroszytyWorksheets – arkusze w skoroszytachRows – wiersze w arkuszachColumns – kolumny w arkuszachCells – kolekcja komórek
5.4 Kolekcja obiektów
• Do obiektów w kolekcji odwołujemy się poprzez podanie indeksu lub nazwy:
Workbooks(„Zadania”).worksheets(1).cells(2,2)
5.5 Właściwość offset
Offset(2,0)
Offset(0,-1) Offset(0,-1)
Offset(-2,0)
5.5 Właściwość offset
Offset(2,0)
Offset(0,-1) Offset(0,-1)
Offset(-2,0)
Offset(?,?)
5.5 Właściwość offset
Offset(2,0)
Offset(0,-1) Offset(0,-1)
Offset(-2,0)
Offset(?,?)
5.5 Właściwość offset
Offset(2,0)
Offset(0,-1) Offset(0,-1)
Offset(-2,0)
Offset(?,?)
5.5 Właściwość offset
Offset(2,0)
Offset(0,-1) Offset(0,-1)
Offset(-2,0)
Offset(?,?)
• Jak to działa?
Range(„A1”).offset(3,0).value = 10
czyli wpisujemy wartość 10 do komórki o 3 wiersze niżej niż komórka A1
5.5 Właściwość offset
Zadanie 5.4** - koloryNadaj dowolnej komórce nazwę „Start” i pokoloruj ją na
czerwono. Napisz makro, które maluje na niebiesko komórkę, która znajduje się 2 wiersze poniżej i 4 wiersze w prawo
Zadanie 5.5*** - kolorowy krzyżStosując właściwość komórki Interior.ColorIndex i przesunięcie
offset napisz makro, które maluje pięć komórek w kształcie krzyża na wybrany przez użytkownika kolor. Nazwę komórki centralnej wprowadzaj z okna InputBox.
Wykorzystaj instrukcje with aby kod był bardziej „elegancki”
Zadanie 5.7*** - kantor wymiany walutNapisz makro, które:• Po wczytaniu nazwy waluty i rodzaju transakcji wyświetla
wartość kursu• Po podaniu kwoty w złotych i rodzaju transakcji podaje
wartość transakcji w nowej walucie – wynik zaokrąglić round()
Kupno Sprzedaż
Dolar 3,10 3,25
Euro 4,00 4,20
Frank szw. 3,25 3,40
Funt 4,65 4,85
5.6 Właściwość CurrentRegion
• CurrentRegion wyczuwa zakres zwartych komórek
• Zakres CurrentRegion jest najmniejszym prostokątem zawierającym wskazaną komórkę i wszystkie wypełnione komórki z otoczenia
Przykład *Wypełnij wartościami kilka dowolnych komórek w sąsiedztwie
komórki A1. Przy pomocy właściwości CurrentRegion zaznacz zwarty obszar dowolnym kolorem a następnie zaznacz pierwszy wolny wiersz
Zadanie 5.9** - towaryOtwórz makro Towary z zadania 5.6. Napisz makro, które będzie
dopisywać towary do listy jeden pod drugim.
Dziękujemy za uwagę!