Programowanie w VBA

Funkcje c.d. Strukturalność. Algorytmy.

Ćwiczenia przed kolokwium.

• • •

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: 1.

Wpisujemy formułę, zaznaczamy obszar z komórką z formułą w rogu, F2, CTRL+SHIFT+ENTER 2.

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

1.

Znaleźć najmniejszy element zbioru (podanych w wierszu); 2.

Znaleźć największy element zbioru; 3.

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

1.

2.

3.

4.

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: 1.

Otwórz szafkę; 2.

3.

4.

5.

Weź pudełko z szafki; Otwórz pudełko; Weź torebkę z pudełka; Zamknij pudełko; 6.

7.

8.

Odłóż pudełko do szafki; Zamknij szafkę; Włóż torebkę do kubka;

Strukturalność c.d.

w Także czynność otwarcia szafki oznacza 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 () • 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