Arkusze kalkulacyjne i VBA

Arkusze kalkulacyjne i VBA

Uniwersytet Łódzki

Wydział Matematyki i Informatyki

Tadeusz Krasiński

Lekcja 4. Okna dialogowe oraz dodatkowe informacje

Studia Podyplomowe „Analiza danych i data mining”

Rok akademicki 2018/19

DODATKOWE INFORMACJE O OKNACH DIALOGOWYCH 1. Przypomnienie o oknach dialogowych

Ćwiczenie 1. W Arkuszu 1 umieścić przycisk o tytule Wybierz liczbę który powoduje pojawienie się okna dialogowego z listą liczb od 1 do 10. Po wyborze liczby okno znika, ale w aktywnej komórce (lub konkretnej np. H8) pojawia się wybrana wartość.

2. Przyciski w formularzu można połączyć i przenieść do okna Toolbox jako nowy przycisk.

Ćwiczenie 2. Utworzyć nowe okno dialogowe, umieścić w nim np. 3 przyciski CheckBox, połączyć je i przenieść do okna Toolbox. Wypróbować ten nowy wielokrotny przycisk w nowym oknie dialogowym.

2. Wpisywanie danych z okna w arkuszu. Wybrane dane lub dokonane wybory w oknie możemy umieszczać w arkuszu.

Ćwiczenie 3. Utworzyć okno dialogowe proszące o podanie nazwiska, imienia i wyboru liczby. Dane te kolejno umieszczać w kolejnych wierszach arkusza po zatwierdzeniu wyborów.

Wsk. Wykorzystać instrukcję

Cells(Rows.Count,1).End(xlUp).Row podającą numer ostatniej niepustej komórki w pierwszej kolumnie.

Skoki do podprogramów

Gdy chcemy przejść do innej instrukcji w programie:

…

GoTo Podprogram3

…

Podprogram3:

Instrukcje Exit Sub

…

Nie ma End Podprogramu – wykonywane są dalsze instrukcje.

Ćwiczenie 4. Napisać proste makro z instrukcjami i skokami do podprogramów z wstawionymi między instrukcjami Exit Sub np. w zależności od wartości liczbowej w danej komórce pojawia się inny komunikat.

Debugowanie

Gdy chcemy wykonać makro krok po kroku (by znaleźć np. błąd) i obserwować co się dzieje w arkuszu) to debugujemy makro:

• Umieszczamy kursor w kodzie makro.

• Zmniejszamy okno Visual Editor by widzieć odpowiednią część arkusza

• Klikamy Debug/Step Into lub F8.

• I tak dalej klikamy F8.

• Wyłączamy debugowanie Step Out

Ćwiczenie 5. Wypróbować działanie debugowania na dowolnym makro (najlepiej pętli) np. wpisać w przekątną kwadratu liczby 1,2,3,…,10 i debugować makro.

Dodatkowe opcje:

• Na szarym marginesie okna z makrami możemy umieszczać punkty brązowe. Uruchomione makro działa do tego punktu.

• Na szarym marginesie okna z makrami możemy przesuwać żółtą strzałkę.

Obsługa błędów Podstawowe instrukcje obsługujące błędy w makrach:

1. On Error Resume Next – wypadku błędu przechodzimy do wykonania kolejnej linii programu..

Przykład 1.

Sub Bledy1()

Worksheets("Arkusz5").Name = "Dane"

On Error Resume Next

Worksheets("Arkusz5").Range("A5").Select End Sub

2. On Error GoTo 0 – wyłączenie obsługi błędów Przykład 2.

Sub Bledy2()

Worksheets("Arkusz5").Name = "Dane"

On Error Resume Next

Worksheets("Arkusz5").Range("A5").Select On Error GoTo 0

Worksheets("Arkusz5").Range("A5").Select End Sub

3. On Error GoTo Instrukcja – przeskok do specjalnej instrukcji np. MsgBox z ostrzeżeniem.

Przykład 3.

Sub Bledy3()

Worksheets("Arkusz5").Name = "Dane"

On Error GoTo Ostrzezenie

Worksheets("Arkusz5").Range("A5").Select Exit Sub

Ostrzezenie:

MsgBox "Zmieniłeś nazwę arkusza"

End Sub

4. Resume - powraca do instrukcji, w której wystąpił błąd

5. Resume linia - skacze do linii o numerze linia (lub etykiety) w obrębie tej samej procedury. linia nie może być zerem.

6. Kod błędu zwraca funkcja Err, zaś komunikat wyjaśniający co się stało (opis błędu), zwraca funkcja Error(Err).

Ćwiczenie 6.

a. Mając dany program Sub zad6() Dim a As Long

a = InputBox("Podaj liczbę całkowitą") MsgBox CStr(a), ,"Jest ok"

Msgbox "Koniec"

End Sub

zrób tak, żeby w przypadku błędu wyświetlał się komunikat ”Źle” i program powracał do polecenia

”a = Input...”.

b. Zamiast „MsgBox” powrotu do „a = InputBox(”Podaj liczbę całkowitą”)” niech wyświetla się

InputBox(”Zła liczba. Musi być całkowita!” & vbCrLf & ”Spróbuj ponownie...”) i sterowanie wraca do wiersza ,,MsgBox CStr(a), ,”Jest ok” ”.

c. Zrób tak, żeby użytkownik widząc komunikat „Źle” miał wybór:

(i) powrócić do wiersza „a = Input...”,

(ii) przenieść się do linii z „MsgBox ”Koniec” ”.

Filtrowanie danych za pomocą Makr

Gdy mamy dane w tabeli to możemy filtrować te dane za pomocą makr z użyciem metody AutoFilter z parametrami:

AutoFilter([Field], [Criteria1], [Operator As XlAutoFilterOperator = xlAnd], [Criteria2], [VisibleDropDown])

Przykład 4. Makro które wybiera wiersze aktualnego zakresu zawierające w kolumnie 3 podsłowo „ford” lub „12”

Sub filtr1()

Range(”A1”).CurrentRegion.Autofilter Field:=3, Criteria1:=”*ford*”,Operator:=xlOr, Criteria2:=”12”

End Sub

Inne operatory można zobaczyć w Browser Objects np. wybierający 3 największe rekordy w kolumnie 4 Sub filtr3()

Range(”A1”).CurrentRegion.Autofilter Field:=4, Criteria1:=”3”,Operator:=xlTop10Items End Sub

Z „odfiltrowanym” zakresem możemy wykonać różne czynności np. pokolorować. Wracamy do pierwotnej postaci instrukcją Range("A1").CurrentRegion.AutoFilter

Sub ser()

Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:="sd"

Range("A1").CurrentRegion.Interior.Color = RGB(0, 255, 0) Range("A1").CurrentRegion.AutoFilter

End Sub

Ćwiczenie 6. Ze strony przedmiotu pobierz arkusz Dane.xlsx i odfiltruj wszystkich mężczyzn.

• Wiersze, w których występują zaznacz na zielono, a następnie wróć do wyjściowej postaci.

• Wiersze, w których występują mężczyźni, usuń.

R1C1 numeracja komórek

W Excelu komórki numerujemy literami i liczbami np. komórka A3, czyli 1 kolumna i 3 wiersz. W stylu R1C1 jest to komórka R3C1 – wiersz (Row) 3, kolumna (Column) 1. Odwołania względne R[3]C[1].

Przykład 5. Następujące 2 makra dają ten sam rezultat:

Sub obliczenia1()

Range("B1").Formula = "=A2 * A3"

End Sub

Sub obliczenia2()

Range("B1").FormulaR1C1 = "=R2C1 * R3C1"

End Sub

Uwaga. Można globalnie zmienić numerację komórek w Opcjach/Formuły.

Zaleta:

Dla ustalonej komórki komórka R[-1]C[1], to komórka powyżej i na prawo, RC[-1] to komórka w tym samym wierszu pierwsza na lewo.

Przykład 6.

Makro

Sub obliczenia3()

Range("I3:I12").FormulaR1C1 = "=RC[-2] * RC[-1]"

End Sub

Wymnaża liczby z dwóch komórek na lewo w całym zakresie.

Ćwiczenie 7. W nowym arkuszu utworzyć tabliczkę mnożenia od 1 do 100 używając stylu R1C1.

System Pomocy Podstawowe sposoby otrzymania informacji o VBA:

1. Rejestrujemy automatycznie Makro z czynnościami które nas interesują i przeglądamy kod programu.

Ćwiczenie 8. Sprawdzić kod kopiowania i przenoszenia zakresu komórek.

2. W Object Browser znajdujemy odpowiednie pojęcie (lub wpisujemy w okno wyszukiwania) i wciskamy F1.

Ćwiczenie 9. Znaleźć informacje na temat AutoFilter 3. W internetowej systemie pomocy (angielskiej):

Help/Microsoft VBA ffor Appl./Excel VBA Reference/

Np. o oknach dialogowych w Office Developments/Office Clients/Offic Shared/ Office VBA language reference/Reference/Objects