Arkusze kalkulacyjne i VBA

Uniwersytet Łódzki

Wydział Matematyki i Informatyki

Arkusze kalkulacyjne i VBA

Lekcja 4

OKNA DIALOGOWE + DODATKOWE INFORMACJE

www.math.uni.lodz.pl/~krasinsk

Tadeusz Krasiński

Dodatkowe informacje o oknach dialogowych 1.Przypomnienie o oknach dialogowych.

Ćwiczenie. W arkuszu 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. 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.

3. Wpisywanie danych z okna w arkuszu.

Wybrane dane lub dokonane wybory w oknie możemy umieszczać w arkuszu.

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

4. Skoki do podprogramów

1.

…….

GoTo Podprogram3

……..

Podprogram3:

Instrukcje Exit Sub

…..

Nie ma End Podprogramu – wykonywane są dalsze instrukcje.

Ćwiczenie. Napisać proste makro z instrukcjami i skokami do

podprogramów z wstawionymi między instrukcjami Exit Sub

Makro Skoki 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:

1. Umieszczamy kursor w kodzie makro.

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

3. Klikamy Debug/Step Into lub F8.

4. I tak dalej klikamy F8.

5. Wyłączamy debugowanie Step Out

Ćwiczenie. 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:

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

2. 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 – pomiń ten błąd.

Przykład Sub Bledy1()

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

On Error Resume Next

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

Instrukcja On Error Resume Next pomija wszystkie dalsze błędy w makro. By wyłączyć to musimy wpisać instrukcję On Error GoTo 0

Przykład.

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

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

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.

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.

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

Na przykład 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 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.

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