Podstawy Visual Basic for Applications (cz. II)

1. Podstawy Visual Basic for Applications (cz. II) 16 XII

2. Podstawy Visual Basic for Applications (cz. II). Tworzenie własnych aplikacji Podstawy Visual Basic for Applications (cz. II)• tworzenie i wykorzystywanie własnych funkcji arkusza • przekazywanie argumentów przez zmienne• obsługa błędów • manipulowanie obiektami z poziomu VBA Tworzenie własnych aplikacji• tworzenie okien dialogowych• procedury zdarzeń• automatyczne importowanie, uzupełnianie danych

3. Funkcje niestandardowe • Aby skrócić i uprościć formuły, można za pomocą edytora VBA tworzyć własne funkcje • Funkcja: • może zwracać tylko wartość • nie może przeprowadzać żadnych operacji na obiektach • nie może zmieniać zawartości arkusza

4. wynik sektora GG (po uwzględnieniu kosztów reformy emerytalnej) w t-1 < -3% PKB? Początek Koniec tak nie korekta -2 p.p. państwowy dług publiczny w t-1 > 55% PKB? tak korekta -1,5 p.p. nie nie państwowy dług publiczny w t-1 > 50% PKB? złe czasy? tak brak korekty w t+1 tak nie skumulowane odchylenia wyniku GG od celu? złe czasy? < -6% PKB > +6% PKB od -6% do +6% PKB nie dobre czasy? tak tak nie bonus +1,5 p.p. brak korekty w t+1 korekta -1,5 p.p.

5. Własna funkcja: korekta SRW Function Korekta(Wynik, Dlug, Suma_roznic, Dynamika_PKB, Srednia_PKB) 'Podaje wielkość korekty zgodnie ze stabilizującą regułą wydatkową If (Wynik < -0.03 Or Dlug > 0.55) Then Korekta = -0.02 ElseIf (Dlug > 0.5 Or Suma_roznic < -0.06) Then If (Dynamika_PKB - Srednia_PKB) >= -0.02 Then Korekta = -0.015 Else Korekta = 0 End If ElseIf Suma_roznic > 0.06 Then If (Dynamika_PKB - Srednia_PKB) <= 0.02 Then Korekta = 0.015 Else Korekta = 0 End If Else Korekta = 0 End If End Function

6. Elementy funkcji [Public | Private] Function nazwa ([lista_argumentow]) [As typ] [instrukcje] [Exit Function] [nazwa = wyrażenie] End Function • Public – funkcja osiągalna dla wszystkich innych procedur zawartych we wszystkich modułach w skoroszycie • Private – funkcja osiąalna wyłącznie dla procedur znajdujących się w tym samym module • Nazwa może zawierać litery, cyfry i niektóre znaki interpunkcyjne (bez spacji, kropek, $, %, !, #, &), pierwszy znak musi być literą • Exit function – natychmiastowe wyjście z funkcji • Po zakończeniu funkcja zwraca wynik przypisany do nazwy

7. Przekazywanie argumentów przez zmienne Sub LiczbaLiter() Dim i As Integer Dim n As Integer i = Len(InputBox("Wpisz swoje imię:")) n = Len(InputBox("Wpisz swoje nazwisko:")) MsgBox WszystkoRazem(i, n) End Sub Function WszystkoRazem(i, n) As Integer WszystkoRazem = i + n End Function

8. Przekazywanie argumentów – rola ByVal Sub LiczbaLiter() Dim i As Integer Dim n As Integer i = Len(InputBox("Wpisz swoje imię:")) n = Len(InputBox("Wpisz swoje nazwisko:")) MsgBox WszystkoRazem(i, n) MsgBox (i) MsgBox (n) End Sub Function WszystkoRazem(ByVal i, n) As Integer WszystkoRazem = i + n i = i + 10 n = n + 10 End Function

9. Argumenty opcjonalne Sub LiczbaLiter() Dim i As Integer Dim n As Integer i = Len(InputBox("Wpisz swoje pierwsze imię:")) j = Len(InputBox("Wpisz ewentualnie swoje drugie imię:")) n = Len(InputBox("Wpisz swoje nazwisko:")) MsgBox WszystkoRazem(i, n) MsgBox (i) MsgBox (n) End Sub Function WszystkoRazem(ByVal i, n, Optional j) As Integer If IsMissing(j) Then WszystkoRazem = i + n Else WszystkoRazem = i + n + j End If i = i + 10 n = n + 10 End Function

10. Rodzaje błędów • Błąd synktatyczny – występuje w chwili pisania kodu, Excel automatycznie wyszukuje błędy i stara się pomóc (patrz alt+F11/Tools/Options/Editor) • Błąd wykonania – występuje w chwili wykonania procedury, np. odwołanie do nieistniejącego pliku lub obiektu w bieżącym arkuszu • Błąd logiczny – wszystko się wykonuje, nie ma żadnych komunikatów o błędzie, ale rezultat procedury nie jest poprawny

11. Jak sprawdzić poprawność kodu? • Można sprawdzać kod krok po kroku, czyli linijka po linijce (Debug/Step into albo F8) • Można wykonywać kod do miejsca określonego kursorem (Debug/ Run to cursor albo ctrl+F8) • Można wstawić w danym miejscu tzw. punkt przerwania (Debug/Toggle Breakpoint albo F9), jeśli podejrzewa się, że w danej linijce jest błąd • Można wstawić czujkę, która obserwuje na bieżąco wartość danej zmiennej (Debug/Add Watch albo shift+F9) lub okno zmienne lokalne (View/Locals Window) • Można zatrzymać program poprzez: • Ctrl+Break • Ustawienie punktów przerwania • Wpisanie instrukcji stop • Ustawienie czujek (przerywa, gdy spełnione są zdefiniowane warunki)

12. Co zrobić po wychwyceniu błędu? Sposób I Sub Iloraz() Dim a As Single Dim b As Single Dim c As Single a = InputBox("Wpisz dzielną:") b = InputBox("Wpisz dzielnik:") On Error GoTo Uwaga c = a / b MsgBox (a / b) Exit Sub Uwaga: MsgBox ("Pamiętaj użytkowniku, nie dziel przez zero!") End Sub

13. Co zrobić po wychwyceniu błędu? Sposób nr 2 Sub Iloraz1() Dim a As Single Dim b As Single Dim c As Single a = InputBox("Wpisz dzielną:") b = InputBox("Wpisz dzielnik:") On Error Resume Next c = a / b If Err = 0 Then MsgBox (CStr(a) + " / " + CStr(b) + " = " + CStr(a / b)) Else MsgBox ("Pamiętaj użytkowniku, nie dziel przez zero!") End If On Error GoTo 0 End Sub

14. Okno wprowadzania danych • Przykładowe okno dialogowe to okno wprowadzania danych (Input Box), służące do wprowadzania: tekstu, liczb lub zakresu • Oprócz komunikatu, okno może zawierać: • tytuł, • wartość domyślną, • współrzędne położenia • typ danych zwracanej wartości

15. Okno wprowadzania danych - przykład Sub Iloraz2() Dim a As Single Dim b As Single Dim c As Single a = InputBox("Wpisz dzielną:", "Program Iloraz", "250416") b = InputBox("Wpisz dzielnik:", "Program Iloraz", "376") On Error Resume Next c = a / b If Err = 0 Then MsgBox (CStr(a) + " / " + CStr(b) + " = " + CStr(a / b)) Else MsgBox ("Pamiętaj użytkowniku, nie dziel przez zero!") End If On Error GoTo 0 End Sub

16. Okno komunikatu • Okno komunikatu (MsgBox) wyświetla komunikat lub pobiera prostą odpowiedź od użytkownika • Podczas zajęć korzystaliśmy z tego okna wielokrotnie, lecz bez tytułu i przycisków • Można określić, które przyciski pojawią się na oknie (patrz slajd 17) • Następnie można pobrać odpowiedź użytkownika (patrz slajd 18)

17. Stałe odpowiadające przyciskom obsługiwanym przez funkcję MsgBox

18. Wartości zwracane przez funkcję MsgBox

19. Okno komunikatu - przykład Sub Iloraz3() Dim a As Single Dim b As Single Dim c As Single a = InputBox("Wpisz dzielną:", "Program Iloraz", "250416") b = InputBox("Wpisz dzielnik:", "Program Iloraz", "376") On Error Resume Next c = a / b If Err = 0 Then MsgBox (CStr(a) + " / " + CStr(b) + " = " + CStr(a / b)) Else Ans = MsgBox("Czy będziesz pamiętał(-a) Szanowny(-a) Użytkowniku(-czko), żeby nie dzielić przez zero?", vbYesNo + vbQuestion + vbDefaultButton2) Select Case Ans Case vbYes Iloraz3 Case No Exit Sub End Select End If On Error GoTo 0 End Sub

20. Ćwiczenie – projektowanie własnego okna dialogowego i procedur zdarzeń • Zadanie polega na utworzeniu formularza, który będzie zawierał: - listę makr - przycisk wykonujący zaznaczone makro - kontrolkę edycji zakresu komórek - pole wyboru określające, czy wypełnienie zakresu ma być jasne czy nie - trzy przyciski opcji określające kolor wypełnienia - przycisk przełączający wypełnienie zakresu lub jego brak

21. Ćwiczenie - ściągawka

22. Procedura zdarzeń Initialize() Private Sub UserForm_Initialize() With lstLista .AddItem "Iloraz" .AddItem "Iloraz1" .AddItem "Iloraz2" .AddItem "Iloraz3" .AddItem "LiczbaLiter" End With OptionButton1.Value = True End Sub

23. Procedura zdarzeń Click() Private Sub cmdWykonaj_Click() Select Case lstLista.ListIndex Case -1 MsgBox "Wybierz makro z listy" Exit Sub Case 0: Call Iloraz Case 1: Call Iloraz1 Case 2: Call Iloraz2 Case 3: Call Iloraz3 Case 4: Call LiczbaLiter End Select End Sub

24. Procedury zdarzeń AfterUpdate() i Change() Private Sub tglWypelnienie_AfterUpdate() Call Wypelnienie End Sub Private Sub OptionButton1_Change() Call Wypelnienie End Sub Private Sub OptionButton2_Change() Call Wypelnienie End Sub Private Sub OptionButton3_Change() Call Wypelnienie End Sub Private Sub chkJasne_Change() Call Wypelnienie End Sub

25. Procedura zmieniająca wypełnienie komórki Private Sub Wypelnienie() If tglWypelnienie.Value = True Then With Range(refZakres.Text).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic If chkJasne.Value = True Then If OptionButton1.Value = True Then .ColorIndex = 19 ElseIf OptionButton2.Value = True Then .ColorIndex = 22 Else .ColorIndex = 23 End If Else If OptionButton1.Value = True Then .ColorIndex = 6 ElseIf OptionButton2.Value = True Then .ColorIndex = 3 Else .ColorIndex = 5 End If End If .TintAndShade = 0 .PatternTintAndShade = 0 End With Else With Range(refZakres.Text).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ColorIndex = 0 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If End Sub

26. Uzupełnianie danych po imporcie z internetu • Zarejestrujmy makro polegające na pobraniu danych z internetu (Dane/Z sieci Web) • Dodajmy w makrze przejście do właściwej komórki za pomocą instrukcji: • Range(„A1”).Select • Selection.End(xlDown).Select • Cells(ActiveCell.Row + 1, 1).Select • Wklejmy wektor bieżących danych za pomocą • Możemy usunąć zakres skojarzony z kwerendą internetową