Arkusze kalkulacyjne i VBA

Uniwersytet Łódzki

Wydział Matematyki i Informatyki

Arkusze kalkulacyjne i VBA

Lekcja 4

DODATKOWE INFORMACJE Word:

Excel:

Tadeusz Krasiński

Studia Podyplomowe „Analiza danych i data mining”

Rok akademicki 2016/17

1

Dodatkowe informacje 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 komórce A4 pojawia się wybrana wartość.

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

Ćwiczenie. Utworzyć nowe okno dialogowe, umieścić w nim np. 3 przyciski CommandButton, połączyć je i przenieść do okna Toolbox.

Wypróbować ten nowy wielokrotny przycisk w nowym oknie

dialogowym.

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. Makro które wybiera wiersze aktualnego zakresu zawierające w kolumnie 3 podsłowo „now” lub „12”

Sub filtr1()

Range(„A1”).CurrentRegion.Autofilter Field:=3, Criteria1:=”*now*”,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()

3

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

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

End Sub

Ćwiczenie. Wpisać pewne dane, odfiltrować pewnym kryterium i

usunąć te wiersze.

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. W nowym arkuszu utworzyć tabliczkę mnożenia od 1 do 100 używając styl R1C1.

5

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