Programowanie w VBA

Programowaniew VBA Funkcje c.d. Strukturalność. Algorytmy. Ćwiczenia przed kolokwium.

Deklaracje tablic • Dim tablica(1 to 10) As Long • Dim tablica(10) = Dim tablica(0 to 10) • Dim tablica() – nieokreślony wymiar (deklaracja Dim przyjmuje tylko liczby) • ReDim tablica(0 to 10) • Array tablica("a", "b", "c") tożsame z: ReDim tablica(0 to 2) tablica(0) = "a" tablica(0) = "b" tablica(0) = "c"

Wstęp do funkcji bloków • UBound(<tablica>, <wymiar>) – zwraca ostatni indeks tablicy w podanym wymiarze (1 to wiersze (y), 2 to kolumny (x)); • LBound(<tablica>, <wymiar>) – zwraca pierwszy indeks tablicy w podanym wymiarze; • Wymiar tablicy to (UBound-LBound+1);

Funkcje bloków komórek (wektorów, tablicy) • Funkcja bloku: Function przyklad(zakres As Variant) As String Dim tablica As Variant Dim x As Integer Dim y As Integer If IsArray(zakres) Then tablica = zakres x = UBound(tablica, 2) – LBound(tablica, 2) + 1 y = UBound(tablica, 1) – LBound(tablica, 1) + 1 przyklad = "Tablica ma " & x & " kolumn i " & y & " wierszy" Else przyklad = "Argument nie jest zakresem." Endif

Funkcje bez zdefiniowanej liczby argumentów • Używa się ich na przykład do operacji na rozdzielonych blokach komórek i innych zastosowań, gdy nie chcemy mieć ograniczonej liczby argumentów (Optional daje możliwość użycia mniejszej liczby argumentów, ale nie większej); • Funkcja opiera się na tablicy ParamArray; Function <nazwa>(Paramarray <zmienna>) As <typ> • Np.: Function Przyklad(Paramarray parametry()) As String ‘() bo musi być typu Variant Dim arg1 As Variant Dim arg2 As Variant Dim arg3 As Variant If IsArray(parametry) Then If (Ubound(parametry,1)-LBound(parametry,1)+1) > 3 Then Przyklad = "Za duzo argumentów." Else arg1 = parametry(0) arg2 = parametry(1) arg3 = parametry(2) Przyklad = "Argument 1: " & arg1 & ", Argument 2: " & arg2 & ", Argument 3: " & arg3 End If Else Przyklad = "Brak argumentów." End If End Function

Funkcje zwracające tablice • REGLINP(lista_y; lista_x) (regresja liniowa, zwraca a i b równania) • MACIERZ.ODW(macierz_kwadratowa) • MACIERZ.ILOCZYN(macierz1;macierz2) Wstawianie funkcji tablicowej: • Wpisujemy formułę, zaznaczamy obszar z komórką z formułą w rogu, F2, CTRL+SHIFT+ENTER • Zaznaczamy obszar potrzebny do działania funkcji, wpisujemy formułę (bez odznaczania zaznaczenia), CTRL+SHIFT+ENTER

Funkcje zwracające tablice • Funkcja musi być typu Variant i przypisywana jej wartość w kodzie musi być typu tablicy; • Tworzenie takiej funkcji: zaznaczenie zakresu, wpisanie nazwy funkcji, naciśnięcie razem: ctrl+shift+enter; Function Przyklad() As Variant Dim tablica(0 To 2, 0 To 2) As Integer Dim i As Byte Dim j As Byte For i = 0 To 2 For j = 0 To 2 tablica(i, j) = (i + j) Next j Next i Przyklad = tablica End Function

Zadania • Znaleźć najmniejszy element zbioru (podanych w wierszu); • Znaleźć największy element zbioru; • Sortujące od najmniejszego do największego elementy zbioru liczb (podanych w wierszu); Funkcja tablicowa – zaznaczamy obszar do poddania funkcji tablicowej, wpisujemy w róg obszaru funkcję, wciskamy ctrl+shift+enter;

Znalezienie najmniejszego elementu w zbiorze Algorytm: Weź pierwszy element jako minimum pętla: czy i_element jest mniejszy od minimum? -> tak? minimum to i_element -> nie? minimum sie nie zmienia koniec pętli minimum to najmniejszy element zbioru

Złożoność obliczeniowa • Przy problemie, który trzeba rozwiązać dla n elementów, ile operacji trzeba wykonać? • Wyszukiwanie najmniejszej liczby – złożoność n (między 1n a 2n : dla każdej porównanie, dla niektórych przypisanie) • a*n dla dużych n zawsze będzie mniejsze niż a*n^2 (Porównywanie wartości wszystkich ze wszystkimi), więc mówimy o złożoności n lub n^2 • Złożoność obliczeniowa może też być np.: n! n^a (a również ułamkowe) n^n n^n^n n*log(n)

Złożoność obliczeniowa • Generalnie dąży się do zmniejszania złożoności obliczeniowej algorytmów. • Niekiedy dąży się do zwiększenia złożoności obliczeniowej niektórych algorytmów, np. zabezpieczanie poprzez szyfrowanie ma celowo stworzony system tak, aby rozszyfrowywanie było szybkie z pomocą klucza, natomiast rozszyfrowywanie na chybił-trafił miało jak największą złożoność obliczeniową, tak, żeby nawet dla krótkich haseł było to zbyt skomplikowane.

Sortowanie zbioru • Złożoność obliczeniowa nie mniejsza niż n (nie da się nie sprawdzić czy wszystkie elementy są w dobrej kolejności). • Podstawowe metody n^2 • Niektóre metody dla optymistycznych warunków mogą być szybsze niż n^2, na przykład rzędu n*log(n)

Sortowanie zbioru Algorytm: pętla zewnętrzna pętla wewnętrzna (przepychanie elementu) weź element(i) sprawdź czy jest większy od element(i+1) -> tak? Zamień je miejscami -> nie? Nic nie rób koniec pętli wewnętrznej koniec pętli zewnętrznej

Żeby posortować, trzeba stworzyć sobie przykładowy zbiór do posortowania – czyli przypadkowe liczby nie po kolei…

Przedeklarowanie zmiennej • Elementów tablicy będącej argumentem funkcji nie można zmieniać; • Trzeba zadeklarować zmienną tablicową, a następnie przepisać zawartość zmiennej-argumentu do niej (przepisanie w formie pętli przepisującej element po elemencie); • Aby zadeklarować zmienną tablicową, której wielkość będzie zależna od argumentu, trzeba wykonać następujące operacje: - zadeklarować zmienną tablicową (ale bez zakresów, puste nawiasy) typu takiego, jakiego mają być elementy tablicy; - PRZEDEKLAROWAĆ (komenda ReDim) zmienną tablicową bezwymiarową na zmienną tablicową zwymiarowaną zmiennymi (normalnie deklaracja musi mieć stałe wartości zakresów);

Sortowanie zbioru Function SORT(zakres_komorek As Range) As Variant Dim wynik() As Double Dim i As Integer Dim j As Integer Dim tmp As Double Dim element As Variant ReDim wynik(0 To zakres_komorek.Count - 1) i = 0 j = 0 <przepisanie tablicy zakres_komorek do tablicy wynik po elemencie> <podwójna pętla, w której wewnętrzna pętla będzie przepychała elementy po jednym> SORT = wynik End Function

Zadania Napisz funkcje: • Sprawdzająca ile jest liczb nieparzystych w zakresie podanym jako argument (z obsługą błędu wystąpienia nie-liczby); • Funkcję tablicową (bezargumentową), której wartością jest tabliczka mnożenia 10x10.

Strukturalność Względność opisu i szczegółowości operacji: Wyobraźmy sobie operacje parzenia herbaty: • Nalej wodę do czajnika; • Zagotuj wodę; • Wrzuć herbatę do kubka; • Zalej herbatę. Na przykład trzeci element w rzeczywistości składa się z operacji: • Otwórz szafkę; • Weź pudełko z szafki; • Otwórz pudełko; • Weź torebkę z pudełka; • Zamknij pudełko; • Odłóż pudełko do szafki; • Zamknij szafkę; • Włóż torebkę do kubka;

Strukturalność c.d. Także czynność otwarcia szafki oznacza w rzeczywistości podniesienie ręki, złapanie za uchwyt, itd. Podniesienie ręki również oznacza wysłanie sygnałów przez mózg do nerwów, mięśni, itd. Odpowiednikami sygnałów mózgu są sygnały procesor-pamięć-peryferia, oraz przerzucanie danych do rejestrów, pamięci, itd. (to nas nie interesuje jako programistów VBA), podniesienie ręki to podstawowe komendy, czynności to proste operacje jak instrukcje w pętlach czy instrukcje warunkowe, a wykonanie całych algorytmów (jak zrobienie herbaty) to odpowiednie dobranie kolejnych operacji które składają się na program realizujący dany algorytm.

Procedury • Sub, End Sub (makra); • Private Sub (makra niedostępne z poziomu Excela, tylko z poziomu VBA); • Wywoływanie podprocedur (normalne procedury, ale robiące tylko jakąś czynność mającą sens dopiero w większej całości (robi się to po to, żeby był czytelniejszy kod): • Call <nazwa procedury> (<ew. zmienne które mają być dane procedurze>) • Funkcji napisanych przez siebie w danym arkuszu (grupie modułów) można używać normalnie jak innych poleceń (naturalnie podając argumenty); Zwrócą one wartość takiego typu, jakiego typu jest funkcja;

Odwołania do procedur Sub przyklad1() Dim dana As Variant Dim dana2 As Variant dana = 1 Call podprogram1(dana) MsgBox ("Wszystko ok, wynik to " & dana & ".") dana2 = funkcja1(dana) MsgBox ("Wszystko ok, wynik to " & dana2 & ".") End Sub Private Sub podprogram1(dana) dana = dana * 2 End Sub Function funkcja1(dana) As Integer funkcja1 = dana * 3 End Sub

Odwołania do procedur c.d. • Odwołanie standardowe, jeśli podprogram operuje na zmiennej, zmieni ją i dalszy ciąg programu głównego będzie już używał nowej wartości zmiennej; • Aby podprogram mógł operować na przydzielonej zmiennej, a żeby główny program nadal używał starej wartości w dalszym ciągu wykonywania, używa się opcji byVal, wówczas na potrzeby podprocedury/funkcji tworzy się kopia zmiennej;

Odwołania do procedur c.d. Sub glowny() Dim x As Integer x = 5 Call podprogram(x) MsgBox (x) End Sub Sub podprogram(ByVal dana) dana = dana ^ 2 MsgBox (dana) End Sub

Żeby zrozumieć rekurencję, trzeba najpierw zrozumieć rekurencję…

Trik – odwołanie funkcji do samej siebie Function siln(wartosc As Integer) wartosc = wartosc - 1 If wartosc = 0 Then siln = 1 Exit Function End If siln = (wartosc + 1) * siln(wartosc) End Function

Programowanie w VBA