Kurs VBA, cz.I & II

1. Kurs VBA, cz.I&II Tomasz Miklewicz Mateusz Piękoś Na podstawie: A. Snarska, Makropolecenia w Excelu

2. Agenda • Wstęp • Środowisko makr • Zmienne i ich typy • Komunikacja z użytkownikiem • Zmienne obiektowe Excela

3. 1. Wstęp • Sprawy organizacyjne • VBA – do czego się przydaje? • Przykłady zastosowańw pracy

4. 2. Środowisko makr 2.1 Dostosowanie Excela 2.2 Nagrywanie makr 2.3 Edycja makr

5. 2.1 Dostosowanie Excela • Przycisk pakietu Office ->Opcje->Popularne->Pokaż kartę Deweloper na wstążce • Deweloper->Bezpieczeństwomakr->Włącz wszystkie makra • Zapisuj pliki z rozszerzeniem .xlsm, używanie innych formatów może spowodować utratę kodu

6. 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ń

7. 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->Klawiszskró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->Formantyformularza->Przycisk, a następnie wybrać z listy makro, które chcemy mu przypisać

8. Przykład 2.1: Zarejestruj makro wpisujące dowolny tekst do komórki A1.

9. Zadanie 2.1: * Nagraj makro, które będzie wybierało Arkusz2. Umieść przycisk uruchamiający je w Arkuszu1

10. 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.

11. Zadanie 2.3: * Zarejestruj makro selekcjonujące i otaczające w ramkę komórkę A1 aktywnego arkusza. Przypisz mu skrót klawiszowy.

12. 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

13. 2.3 Edycja makr • Developer->Edytor makr

14. 2.3 Edycja makr • Każde makro zaczyna się od wyrażenia Sub Nazwa() a kończy wyrażeniem EndSub. • 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

15. 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

16. 2.3 Edycja makr • Kolory w VBA • Zapis kolorów przy pomocy notacji RGB pozwala na dostęp do szerszej palety kolorów niż standardowe 56

17. 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.

18. 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.

19. 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.

20. 3.Zmienne i ich typy 3.1 Typy zmiennych 3.2 Deklaracja zmiennych 3.3 Instrukcja przypisania 3.4 Podstawowe funkcje matematyczne 3.5 Funkcje tekstowe 3.6 Funkcje czasowe

21. 3.1 Typy zmiennych

22. 3.2 Deklaracja zmiennej • Deklaracja: Dimzmienna1 as Integer, zmienna2 as String • Optionexplicit – wymusza kontrole typów zmiennych

23. 3.3 Instrukcja przypisania • Instrukcja przypisania: Zmienna = wartość a = 10 b = a/4.5 c = Range(„A1”).Value d = „Karuzela” e = #01/02/2013# Range(„A1”).Value = 70

24. Przykład 3.1: Obliczymy sumę kwadratów komórek A1 i A2, wynik wpiszemy do komórki A2

25. 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

26. Zadanie 3.2: ** Napisz makro, które przestawia zawartość komórek A1 i A2.

27. 3.4 Podstawowe funkcje matematyczne • Dozwolone operacje matematyczne: + dodawanie - odejmowanie * Mnożenie / dzielenie ^ potęgowanie

28. 3.4 Podstawowe funkcje matematyczne Abs(x) - zwraca wartość bezwględną z x Int(x) – zwraca część całkowitą liczby x Sqr(x) – zwraca pierwiastek kwadratowy z x Sin(x), Cos(x), Tan(x) – funkcje trygonometryczne Rnd – zwraca wartość losową z przedziału (0,1) • Funkcje Excela: Application.WorksheetFunction.Average()

29. Zadanie 3.3 * - przeciwprostokątna Napisz 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

30. Zadanie 3.4 ** – funkcja kwadratowa Napisz 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=6 Podstaw a=1, b=-4, c=4 i sprawdź czy działa

31. Podpowiedź: Instrukcja warunkowa w VBA: Ifwarunek then Operacje Else Instrukcje Endif

32. Przykład 3.2 - oceny w szkole : Do komórek B1:B5 wpiszemy liczby losowe z przedziału 1 – 6.

33. Zadanie 3.5 *** – oceny na studiach Napisz makro, które będzie losować liczby z przedziału 2 do 5,5, z krokiem 0,5

34. 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

35. Przykład 3.3: Wytniemy z tekstu numer indeksu studenta Jaś Fasola 34512 .

36. Zadanie 3.6 ** – Faktury Wytnij 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ł

37. 3.6 Funkcje czasowe Date – zwraca dzisiejszą date Day(d) – zwraca numer dnia podanej daty d Month(d) – zwraca numer miesiąca podanej daty d Year(d) – zwraca rok w postaci liczby WeekDay(d, vbMonday) – zwraca numer dnia tygodnia

38. Przykład 3.4: Sprawdzimy w jaki dzień tygodnia wypada Sylwester 2014 .

39. Zadanie 3.7* – Urodziny Sprawdź w jaki dzień tygodnia się urodziłeś, urodziłaś.

40. Przykład 3.5: Obliczymy ile dni minęło od końca Świata (od 21.12.2012)

41. Zadanie 3.8* Napisz makro, które podaje ile przeżyłeś (przeżyłaś) dni

42. Zadanie 3.9** Napisz makro, które podaje ile przeżyłeś (przeżyłaś) pełnych miesięcy

43. Zadanie 3.10*** Napisz makro, które oblicza liczbę miesięcy pomiędzy dwoma dowolnymi datami z dokładnością do jednego miesiąca

44. Ciekawostka - Debugowanie • Przycisk F8 • Okno zmiennych lokalnych: View -> LocalsWindow

45. 4. Komunikacja z użytkownikiem 4.1 Wyświetlanie informacji 4.2 Wczytywanie danych

46. 4.1 Wyświetlanie informacji • Do wyświetlenia informacji z programu służy funkcja MsgBox MsgBox(Prompt, Buttons, Title) • Parametry: Prompt – wyrażenie String wyświetlane w oknie Title – 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

47. 4.1 Wyświetlanie informacji

48. 4.1 Wyświetlanie informacji

49. 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

50. 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. .