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ę!