Programowanie w VBA

1. Programowaniew VBA Komunikacja z arkuszem. Iteracje. Funkcje.

2. Komunikacja z arkuszem • ActiveCell – oznacza aktualnie wybraną komórkę, jeśli wybrany jest zakres, to jest to komórka od której zaczęto zaznaczać; • Selection – wybrany zakres komórek; • Cells – bezpośrednie odwołanie do komórek adresując bezwzględnie (czyli niezależnie od zaznaczenia); Adresowanie jak w macierzach: A(1,2) – 1.wiersz, 2. kolumna

3. Komunikacja z arkuszem - Aktualnie zaznaczona komórka • ActiveCell.Value – zwraca wartość tej komórki lub umożliwia przypisanie jej wartości: Dim dana As Variant dana = ActiveCell.Value ActiveCell.Value = dana * 2 • Można też używać znanych z rejestracji makr metod związanych z formatowaniem: ActiveCell.Font, ActiveCell.FontSize, itd. • ActiveCell.Row – zwraca numer wiersza zaznaczonej komórki; • ActiveCell.Column – zwraca numer (liczbowy) kolumny zaznaczonej komórki;

4. Komunikacja z arkuszem - Aktualnie zaznaczony zakres • Selection.Rows.Count – zwraca ilość wierszy zaznaczenia; • Selection.Columns.Count – zwraca ilość kolumn zaznaczenia;

5. Komunikacja z arkuszem – Zmienna - zakres Dim zmienna as Range Set zmienna = Selection • zmienna.Rows.Count – zwraca ilość wierszy zakresu przechowywanego w zmiennej; • zmienna.Columns.Count – zwraca ilość kolumn zakresu przechowywanego w zmiennej; • zmienna.Row – zwraca numer pierwszego wiersza zakresu ze zmiennej; • zmienna.Column – zwraca numer pierwszej kolumny zmiennej; • Ostatni wiersz zakresu: zmienna.Row + zmienna.Rows.Count - 1

6. Komunikacja z arkuszem –Zakres komórek Range("A1:B2") • Range("A1:B2").Rows.Count • Range("A1:B2").Select • Range("A1:B2").Copy • Range("A1:B2").Interior.Color = 2

7. Komunikacja z arkuszem- bezwzględne odwołanie • Cells(<wiersz>,<kolumna>) – odwołanie do komórki w arkuszu (domyślnie jest to wartość); • Cells(w,k).Value – zarówno można przypisać wartość, jak i użyć do przypisania(w procedurach); • Inne metody działają tak jak w poprzednich przypadkach (np. formatowanie jakiejś komórki); dana = Cells(1,1).Value Cells(1,2).Value = dana (wynik: przepisanie zawartości komórki A1 do B1, bez znaczenia, jaka komórka jest zaznaczona)

8. Zadania Napisz program: • Obliczający liczbę komórek w zakresie zaznaczonym aktualnie w arkuszu (mnożenie liczby wierszy przez liczbę kolumn); • Wpisujący podaną przez użytkownika (inputbox) liczbę x w komórkę o koordynatach (x,x);

9. Iteracje dla n = 3

10. Iteracje • Jest to powtarzanie tej samej instrukcji (bloku instrukcji) w pętli; • Pętla musi mieć podaną ilość powtórzeń, niebezpośrednio – tworzy się ją przez zakres od liczby do liczby (przy rozpoczęciu sprawdzany jest warunek wykonania); For <licznik> = <pocz> To <koniec> Step <krok> <instrukcje> Next <licznik> • Człon Step jest opcjonalny; • Komenda „For i = 1 To 5” powtórzy następującą po niej (aż do komendy Next) sekwencję instrukcji 5 razy; • Komenda „For i = 0 To 10 Step 5” powtórzy czynności 3 razy (0,5,10), a po zakończeniu pętli i=15 (wartość ta nie spełniła warunku, więc pętla zakończyła działanie;

11. Iteracje • Wykonywanie pętli można przerwać z wewnątrz instrukcją Exit For – wywoływaną na przykład przez instrukcję warunkową; • <początek>, <koniec> i <krok> mogą być zmiennymi, nawet zmienianymi w trakcie wykonywania pętli; • Stan zmiennych określających rozpoczęcie powtarzania sprawdzane jest na początku każdej pętli; • Komenda Next dodaje liczbę <krok> do zmiennej <licznik> (inkrementacja zmiennej); • <początek> może być większy od <koniec>, ale albo musi być to rozwiązane w trakcie pętli, albo <krok> musi mieć wartość ujemną;

12. Iteracje • Po co takie kombinacje? Żeby nie trzeba było robić skomplikowanych przekształceń i używać bezpośrednio danych z zadania (na przykład) – ogólnie – w celu ułatwienia sobie życia; • Pętla w pętli z dobrze dobranymi zakresami to idealny sposób na poradzenie sobie z operacjami na dowolnych macierzach, tablicach, itd.

13. Iteracje • Inna składni iteracji specjalnie dla tablic: For Each <element> In <tablica> <blok instrukcji> Next • <element> i <tablica> muszą być typu Variant; • zmienna <element> może być używana w bloku instrukcji wewnątrz pętli i w każdej iteracji zawiera w sobie zawartość kolejnego elementu wektora/tablicy <tablica>;

14. Inne iteracje While <wyrażenie logiczne> <instrukcje jeśli wyrażenie = prawda> Wend Do Until <wyrażenie logiczne> <instrukcje wykonywane póki wyrażenie = fałsz> Loop (Until <wyrażenie logiczne>) Do While <wyrażenie logiczne> <instrukcje wykonywane póki wyrażenie = prawda> Loop (While <wyrażenie logiczne>) Umiejscowienie warunku decyduje czy warunek jest sprawdzany po każdej pętli czy przed nią.

15. Inne iteracje i=5 Do While i<5 MsgBox("bum") Loop Do MsgBox("brzdek") Loop While i<5

16. Zadania Napisz programy: • Obliczającą sumę liczb naturalnych od 1 do n (n podane przez użytkownika); • Wypisującą dzielniki liczby podanej przez użytkownika;

17. Zadania i = i +1 – kumulowanie 1 do i (dopisanie 1, bez usunięcia dotychczasowej wartości i); suma = …? - też nie chcemy, żeby znikła dotychczasowa wartość, ale żeby dodawał kolejne wartości do już istniejącej sumy wcześniejszych.

18. Instrukcja skoku • GoTo <etykieta> • Gdzies w kodzie: <etykieta>: <Instrukcje> • Może służyć jako pętla (skok do wcześniejszego miejsca) lub do ominięcia kodu (do dalszego). Ma sens tylko przy warunkowości wykonania (podobnie jak Exit For/Exit Sub/Exit Do Sub abc() Dim x As Byte petla: x = InputBox("Podaj liczbę:") If x <> 0 Then GoTo petla Else MsgBox ("Koniec programu!") End If End Sub

19. Funkcje • Funkcja to podstawowy sposób wykonywania operacji na komórkach w MS Excel; • Funkcja przenosi wartość przez swoją nazwę, a więc funkcja Abc wewnątrz swojego kodu musi zawierać instrukcję: Abc = <zwracana do komórki wartość funkcji>

20. Funkcja jednej i więcej zmiennych • Pobiera argument(y), które są potrzebne do obliczenia i zwrócenia jej wartości; • Funkcja powinna mieć przypisany typ, wtedy trudniej o błąd; Function T_absolutna(T_Celsjusz) As Single T_absolutna = T_Celsjusz + 273.15 End Function

21. Funkcja stała • Zwraca wartość stałą, np. funkcja: Function Avogadro() Avogadro = 6.022E+23 End Function Po wpisaniu do komórki „=Avogadro()” otrzymamy wynik: „6.022E+23”

22. Idiotoodporność • Idiotoodporność (foolproof)! – warto stosować, abyśmy sami mogli stwierdzić, gdzie leży błąd w programie; • Na czym polega? Należy przewidzieć wszystkie potencjalne błędy i możliwości, jakie mogą wystąpić przy przyjmowaniu danych wejściowych do programu. Każdą potencjalną opcję sprawdzać instrukcją warunkową i w przypadku wystąpienia błędu nie dopuszczać do jego wystąpienia, tylko odsyłać, na przykład, do wypisania własnego tekstu informującego o błędzie i z czego on wynika; • Aby móc używać tej „techniki”, trzeba możliwie szeroko definiować zmienne wejściowe – np. poprzez typ Variant, a w miarę upewniania się co do prawidłowości danej zawartej w takiej zmiennej (przez komendy typu Is...) – przenosić ją do kolejnych bardziej zawężonych typów i operacji wykonywanych na niej.

23. Funkcje Przydatne komendy: • IsNumeric(<zmienna>) – zwraca prawdę, jeśli zmienna jest liczbą (jeśli jest, można zmienną wsadzić do typu liczbowego); • IsArray(<zmienna>) – prawda, jeśli zmienna jest tablicą (wektorem); • IsEmpty(<komórka>) – prawda, jeśli komórka jest pusta (zmienna typu Range); • IsDate (<zmienna>) – prawda, jeśli zmienna może być zapisana jako data; • Len(<string>) – zwraca długość ciągu znaków; • Rnd() – zwraca liczbę losową z zakresu <0;1)

24. Przykład Function przyklad(zakres As Variant) As String Dim tablica As Range If IsArray(zakres) Then Set tablica = zakres przyklad ="Liczba wierszy: " & tablica.rows.count End If End Function

25. Przykład Function przyklad(liczba As Variant) As String If IsNumeric(liczba) Then If liczba = liczba \ 1 Then przyklad = "calkowita" Else przyklad = "niecalkowita" End If End If End Function

26. Zadania do domu Napisz funkcję: • Obliczającą sumę liczb od 1 do n (gdzie n jest argumentem funkcji); • Obliczającą ciśnienie jednego mola gazu doskonałego na podstawie temperatury i objętości (dwa argumenty); • Obliczającą n-ty element ciągu Fibonaciego; Napisz program: • Wypełniający zakres komórek (kolejne komórki w zakresie rzędami) kolejnymi liczbami zaczynając od 1 używając pętli typu „for each element in zakres”; • Wypełniający zakres komórek (kolejne komórki w zakresie rzędami) kolejnymi liczbami zaczynając od 1 używając pętli typu „For i = 1 to zakres.rows.count”;