Arkusze kalkulacyjne i VBA
Uniwersytet Łódzki
Wydział Matematyki i Informatyki
Tadeusz Krasiński
Lekcja 2. Dalsze elementy VBA
Studia Podyplomowe „Analiza danych i data mining”
Rok akademicki 2018/19
Zmienne
Zmienne (dowolna nazwa zaczynająca się od litery np. num, j23, JP2,…). Może reprezentować liczby, zakresy, wartości logiczne, obiekty. Nadajemy im wartości za pomocą znaku równości:
x = 1
num = 0.3
WartoscLogiczna = True x = x + 1
Myname = "Tadeusz"
Dat = #12/14/2016#
Set Tab = Range("A1:D4") – kluczowe słowo Set do obiektów Możemy deklarować typ zmiennych. Nie jest to konieczne, np.
Dim MojaZmienna As Integer Dim x As Double
Dim MojaTablica(1 To 10, 1 To 100) As Integer - Zmienna tablicowa.
Domyślnie bez deklaracji zmienne mają typ Variant.
Typy zmiennych:
Ćwiczenie 1. Zadeklarować zmienną xx jako całkowitą, nadać jej wartość całkowitą i obliczyć z niej pierwiastek (użyć funkcji Sqr) i wyświetlić w MsgBox wartość tej zmiennej i jej pierwiastka. Wypróbować inne warianty:
• nadać xx wartość niecałkowitą,
• zadeklarować wynik jako całkowity,
Ćwiczenie 2. Zadeklarować zmienną Zakres jako Range, (Range jest obiektem, a więc należy przy definiowaniu zmiennej obiektowej użyć słowa kluczowego Set) nadać jej wartość np. w kwadracie komórek wpisać wartość np. 100.
Zasięg zmiennych Przykłady deklarowania zasięgu zmiennych:
Sub makro1()
Dim x1 as Integer
….Instrukcje…
End Sub
Zmienna x1 jako liczba całkowita dostępna tylko w tym makro Dim x2 as String
Sub makro1()
….Instrukcje…
End Sub + Kolejne makra
Zmienna x2 jako ciąg symboli dostępna tylko w tym module
Public x3 as String Sub makro1()
….Instrukcje…
End Sub + Kolejne makra
Zmienna x3 jako ciąg symboli dostępna we wszystkich modułach (globalna).
Const Proc as Integer = 3
Deklaracja stałej Proc równej 3.
Ćwiczenie 3. Zadeklarować zmienną xxx jako całkowitą dla całego modułu, nadać jej wartość w pewnym makro i w innym makro wyświetlić ją (w MsgBox).
Zakresy i odwoływanie się do komórek
W większości przypadków makra wykonują operacje na zakresach komórek. Podstawowym obiektem jest Range, nie ma obiektu Cells – jest to własność obiektu Range, ale zwracająca obiekt typu Range
Zakresy (Range)
Najczęściej instrukcje, funkcje, polecenia dotyczą zakresów (pewnego zbioru komórek). Możemy je w makrach określać na różne sposoby:
• Range("A2:C7") – prostokąt komórek - to samo [A2:C7]
• Range("A2", "C7") – prostokąt
• Range("A2:C7, D1:E5") – 2 prostokąty komórek
• Range("A2:C7 C1:E5") – część wspólna 2 prostokątów komórek
• Range("A:A") – jedna kolumna
• Range("A:F") – kolumny od A do F
• Range("1:5") – wiersze od 1 do 5
• Range("A2:C7").Columns(1) – 1 kolumna w zakresie
• Range("A2:C7").Rows(3) – 3 wiersz w zakresie
• Range("A2:C7").Cells(2,3) – komórka o numerze (2,3) w zakresie
• Range("A2:C7").Cells(3) – komórka o numerze 3 w zakresie, liczonych od lewej do prawej
• Range("B7").Offset(-1,2) – komórka przesunięta o jeden wiersz w górę i dwie kolumny w prawo.
Aby nazwać zakres używamy słowa Set xx= Range("A2:C7"), dalej np. xx.Cells(6)
• Cells(3,5) – komórka o współrzędnych 3 wiersz, 5 kolumna w aktywnym arkuszu, czyli E3
• Cells(Rows.Count,1) – ostatnia komórka w 1 kolumnie (Count – liczba obiektów w danej kolekcji).
• Cells(Rows.Count,2).End(xlUp) – ostatnia niepusta komórka w kolumnie 2.
• Range("B3").Resize(10,3) – rozciągnięcie zakresu 10 razy w dół i 3 w prawo.
• CurrentRegion - aktualnie wykorzystany zakres komórek z aktualną komórką w tym zakresie.
• UsedRange - aktualnie wykorzystany zakres komórek w całym arkuszu.
Ćwiczenie 4. Wypróbować 5 lub więcej sposobów określenia zakresów wykorzystując Metodę Select do zaznaczenia zakresu np. Range("A1:B3").Select
Ćwiczenie 5. Napisać makro, które w kwadratowy zakres (8 na 8) wpisuje kwadraty kolejnych liczb naturalnych.
Wykorzystać pętlę For i=1 to 64 ... Next i
Ćwiczenie 6. Napisać makro, które w kwadratowy zakres (6 na 6) koloruje na jakiś kolor (własność Interior.Color obiektu Range) i wpisuje po przekątnej kwadraty kolejnych liczb naturalnych.
Uwaga. Kolory określamy przez RGB(1,100,255) wartości kolorów: czerwony (R od 0 do 255), zielony (G od 0 do 255), niebieski (B od 0 do 255) lub przez wybrane stałe np. vbYellow lub numer koloru od 0 do 255*255*255.
Ćwiczenie 7. Napisać program typu Sub wpisujący kolejne liczby 1,2,…,1000 w komórkach B1,B2,…,B1000 i wyświetlający okno z napisem ”Koniec”.
Wsk. Wykorzystać właściwość Cells obiektu Range.
Ćwiczenie 8. Napisać program typu Sub usuwający co drugą liczbę z poprzedniego zadania.
Wsk. Wykorzystać metodę Clear .
Ćwiczenie 9. Utwórz makro, które wykorzystuje InputBox i MsgBox do pojawienia się tekstu powitalnego w oknie, gdy podamy swoje nazwisko i tekstu zniechęcającego lub o ponowną próbę, gdy podamy nieprawidłowe nazwisko.
Wskazówka. Składnia
InputBox ("Zapytanie", "Tytuł okna", "Wartość domyślna w oknie") MsgBox ("Zapytanie",Możliwe przyciski, "Tytuł okna")
MsgBox "Tekst" wyświetla tekst
MsgBox Zmienna wyświetla wartość zmiennej
Różnica między tymi oknami polega na tym, że w InputBoxie na zapytanie wprowadzamy wartość, a w MsgBoxie mamy zadane zapytanie i udzielamy standardowej odpowiedzi (Yes, No, Cancel,…).
Użyć polecenia
If … Then … Else
Przykład 2. Makro, które w oknie dialogowym pyta o wiek a następnie po przekątnej od danej komórki wypisuje kolejne lata od wieku do wiek +4.
Sub Przyklad2()
Dim i As Integer, wiek As Integer
wiek = InputBox("Podaj liczbę:", "Wiek", "18") For i = 1 To 4
ActiveCell.Offset(i, i).Font.Bold = True ActiveCell.Offset(i, i).Value = i + wiek Next i
End Sub
Ćwiczenie 10. Napisać makro, które wpisuje Nazwisko w pierwszą pustą komórkę w prawym dolnym rogu użytego zakresu.
Wsk. Wykorzystać własność CurrentRegion
Użyty zakres
Nazwisko
Makra związane ze zdarzeniami
Możemy uruchamiać pewne makra (które sami napiszemy), gdy wystąpi pewne zdarzenie. Inaczej, gdy pewne zdarzenie nastąpi np. otworzymy arkusz, dodamy arkusz, dwa razy klikniemy pewien przycisk, zmienimy wartość w komórce itp., to uruchamia się automatycznie makro. Makra związane ze zdarzeniami umieszczamy w odpowiednim module:
• Zdarzenia związane z danym skoroszytem (np. podczas otwierania danego skoroszytu chcemy by pojawiło się okno z ostrzeżeniem lub okno powitalne) umieszczamy w module związanym z Ten skoroszyt (dwa razy klikamy ten moduł i wybieramy zdarzenie w rozwijanych 2 oknach: Workbook i w Declarations np. Open lub inne),
• Zdarzenia związane z danym arkuszem (np. by przed zamknięciem danego arkusza pojawiło się ostrzeżenie) zapisujemy je w module arkusza o odpowiednim numerze, np. Arkusz1.
• Zdarzenia związane z danym oknem dialogowym UserForm (np. kliknięcie okna dialogowego) zapisujemy je w module tego okna dialogowego (dwa razy klikamy samo okno dialogowe w edytorze).
• Są również zdarzenia związane z całym Excelem AppEvents i z Wykresami ChartEvents
Ćwiczenie 11. Utwórz makro, które po otwarciu Arkusza 2 wyświetla okno MsgBox z zapytaniem np. Ten arkusz już jest wypełniony. Czy chcesz kontynuować? Jeśli odpowiedź będzie Tak to można pracować w Arkuszu 2, jeśli Nie to otwierany jest Arkusz 1. Użyć polecenia
If … MsgBox = vbYes Then … Else
Ćwiczenie 12. Utwórz makro, które w Arkuszu 3 po każdej zmianie wyboru komórki wyświetla okno z zapytaniem np. Czy jesteś pewny tego?
Ćwiczenie 13. Utwórz nowy skoroszyt i wpisz makro, które wymusza podanie hasła podczas jego otwierania.
Makra typu Function Makra typu Function zapisujemy w zwykłym module.
Podstawowa składnia makra typu Function:
Function NazwaFunkcji(Arg1,x1) ‘np. od 2 argumentów Instrukcje
NazwaFunkcji = Wyrażenie od argumentów Arg1,x1 End Function
Jak stosować makro typu Function: W arkuszu Excela tak jak zwykłą funkcję Excela, tzn.
1. Zaznaczamy komórkę w której ma pojawić się wartość naszej funkcji.
• Klikamy Wstaw funkcję
• Wybieramy Zdefiniowane przez użytkownika.
• Z listy wybieramy nazwę funkcji.
• Wpisujemy argumenty.
2. W oknie Immediate Wpisujemy
? NazwaFunkcji(Argumenty) 3. W innym makro
Sub aaaa()
Instrukcje Makra
xy= NazwaFunkcji(Argumenty) Inne Instrukcje
End Sub
Ćwiczenie 14. Napisać makro z prostą funkcją dodającą dwie liczby.
Function Summa(x1 As Integer, x2 As Variant) As Variant Summa= x1+x2
End Function
Ćwiczenie 15. Napisać makro typu Function podającą aktualny czas lub datę w odpowiednim formacie:
Date – to liczba, reprezentująca numer dnia od 1900 roku, Format(Date, "Long Date") normalny format dnia Podobnie dla Now:
Now - to liczba, reprezentująca numer dnia od 1900 roku z ułamkiem dziesiętnym, reprezentującym część dnia jaka upłynęła od północy.
Format(Now, "Short Time") - normalny format dnia.
Przykład. Funkcja która usuwa z danego słowa samogłoski
Function UsunSam(Tekst) As String ‘Wymagane: słowo Function, nazwa makra, argument w nawiasach, deklaracja typu zmiennych
Dim i As Long
‘Opcjonalnie: deklaracja typu zmiennej i UsunSam = ""
For i = 1 To Len(Tekst)
If Mid(Tekst, i, 1) Like "[AEIOUYaeiouy]" Then
‘W funkcji Mid wymagana zmienna i typu Long UsunSam = UsunSam & ""
Else
UsunSam = UsunSam & Mid(Tekst, i, 1) End If
Next i
End Function
Ćwiczenie 16. Wykorzystaj powyższą funkcję do uruchomienia (poprzez makro) okien dialogowych InputBox do wprowadzenia tekstu i MsgBox do otrzymania wyniku działania funkcji.
Głos w VBA VBA ma wbudowany generator mowy do odczytywania na głos
Function Mowa(tekst)
Application.Speech.Speak (tekst) End Function
Ćwiczenie 17. Napisać makro, które wywołuje InputBox z poleceniem by wpisać dowolne słowo i które zostaje odczytane Pętle i instrukcje warunkowe
Standardowe pętle w VBA:
For … Next Podstawowa składnia:
Sub Petla1()
For i=1 To 100 Step 2 (domyślnie Step =1)
‘Instrukcje Next i End Sub
Ćwiczenie 18. Napisać makro, które po przekątnej wpisuje liczby od 1 do 100 i koloruje na kolor czerwony vbRed komórki obok.
Ćwiczenie 19. Napisać makro, które w kolumnie 3 koloruje komórki, aż do ostatniego zapisanego wiersza (Cells(Rows.Count,3).End(xlUp).Row ), zawierające liczby >10.
For Each … Next
Podobna pętla do For … Next ale dla obiektów np. dla arkuszy w skoroszycie, zakresów w arkuszu itp. Na przykład, For Each xx In Worksheets
‘Instrukcje Next xx
For Each komorka In Range("A2:C6")
‘Instrukcje Next komorka
Ćwiczenie 20. Napisać makro, które we wszystkich arkuszach aktywnego skoroszytu wpisuje w komórce B5 nazwisko.
With … End With
Podobna do powyższej przeznaczona do obiektów i kolekcji np. zmiana parametrów czcionki w wybranym zakresie Sub Zmiana() ‘zmienia własności czcionki w zakresie
With Range("A1:D6").Font .Name = "Arial"
.Bold = True .Size = 14 End With
End Sub
Select Case
Używana gdy mamy więcej opcji a nie tylko dwie (gdy dwie to używamy If …Then… Else) np.
Sub Petla4() ‘wybór
Liczba = InputBox("Podaj liczbę kupowanych książek: ") Select Case Liczba
Case 0 To 3 Rabat = 10 Case Is >=4 Rabat = 20 End Select
MsgBox "Rabat wynosi "&Rabat&"%"
End Sub
Ćwiczenie 21. Napisać makro, które w zależności od danego dnia pokazuje nam inne polecenie do wykonania (użyć funkcji WeekDay(Now) zwracającej numer dnia (od 1 do 7; od niedzieli do soboty).
Do…Loop
Pętla używana do wykonania pewnych czynności o nie zawsze określonej liczbie
Przykład. Wartość co drugiej komórki w pionowym zakresie przenosi do komórki obok.
Sub Petla6() Do
If Selection.Value = " " Then Exit Do
ActiveCell.Offset(1, 0).Range("A1").Select Selection.Cut
ActiveCell.Offset(-1, 1).Range("A1").Select ActiveSheet.Paste
ActiveCell.Offset(1, -1).Range("A1").Select Selection.EntireRow.Delete
ActiveCell.Select Loop
End Sub
Inne wersje pętli Do…Loop z warunkami Do While i < 10 … Loop wykonywane dla i < 10
Do Until i > 10 … Loop wykonywane dla i <= 10
Ćwiczenie 22. Napisać makro, które koloruje komórki w wierszu dopóki wartość jest <10. Makro Pętla7
If…Then…[Else]
Składnia
If warunek Then Instrukcje
[Else]
Instrukcje End If
Może być składnia prostsza w jednym wierszu: If warunek Then
Gdy chcemy rozdzielić na więcej niż dwa warunki używamy konstrukcji:
If warunek Then Instrukcje
ElseIf
Instrukcje ElseIf
Instrukcje Else
Instrukcje End If
Przykład. W zależności od wartości w komórce wykonujemy inne instrukcje. Jednocześnie przykład skoku do innej instrukcji:
Sub Petla8()
If Range("A10").Value > 10 Then GoTo Instr1
ElseIf Range("A10").Value <= 10 And Range("A10").Value > 5 Then GoTo Instr2
ElseIf Range("A10").Value <= 5 Then GoTo Instr3
Instr1:
MsgBox "Za duża liczba"
Exit Sub Instr2:
MsgBox "W sam raz"
Exit Sub Instr3:
MsgBox "Za mała suma"
Exit Sub Else
End If End Sub
Dodatkowe pożyteczne instrukcje:
• Gdy chcemy przerwać działanie makro lub pętli:
Exit Sub, Exit For, Exit Do….
• Gdy chcemy przejść do innej instrukcji:
...
GoTo Podprogram3 ...
Podprogram3:
Instrukcje ...
Nie ma End Podprogramu – wykonywane są dalsze instrukcje
Ćwiczenie 23. Napisać proste makro z dwiema instrukcjami i następnie drugie identyczne, z wstawionymi między instrukcjami Exit Sub - Makro Petla8.
Debugowanie
Gdy chcemy wykonać makro krok po kroku (obserwując np. w innym oknie co się dzieje) 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 24. Wypróbować działanie debugowania na dowolnym makro (najlepiej pętli).
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 w makro obsługujące błędy:
• 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
• 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
Zadania dodatkowe do samodzielnego zrobienia
Ćwiczenie 1. Napisz program, oceniający testy uczniów. Program powinien w zależności od wpisanej przez Ciebie liczby punktów wyświetlać ocenę:
• punkty 91 do 100 — cel
• punkty 81 do 90 — bdb
• punkty 71 do 80 — db
• punkty 61 do 70 — dst
• punkty 51 do 60 — dop
• punkty 0 do 50 — ndst
• > 100 lub < 0 — błąd
Ćwiczenie 2. Napisz program obliczający prowizję dla sprzedawcy.
Miesięczna sprzedaż Stawka prowizji
0- 9999 złotych 8,0%
10000-19999 złotych 10,5%
20000-39999 złotych 12,0%
Powyżej 40000 złotych 14,0%
Pobierz plik lekcja2.xlsx.
Ćwiczenie 3. Napisz funkcję VBA, która sprawdza czy liczba (Arkusz1 plik lekcja2.xlsx) z kolumny A jest dodatnia,
ujemna czy zero i wypisuje odpowiedni komunikat w kolumnie B (dotyczy podpunktu a)) i C (w przypadku b)). Zadanie rozwiąż dwoma sposobami:
a) Użyj instrukcji warunkowej If … Then b) Użyj instrukcji Select Case
Ćwiczenie 4. Napisz funkcję VBA, która wyznaczy datę ważności danego produktu (Arkusz2 plik lekcja2.xlsx).
Wykorzystaj instrukcję Select Case.
Ćwiczenie 5. Napisz funkcję VBA o nazwie lata_pracy1 zwracającą wartość ilości przepracowanych lat obliczanych na podstawie różnicy lat pomiędzy rokiem bieżącym a rokiem, w którym pracownik został zatrudniony. Do zadania
wykorzystaj funkcje Year() oraz Now(). Czy uzyskane w ten sposób wyniki są dokładne? Napisaną przez siebie funkcję wykorzystaj do uzupełnienia odpowiedniej kolumny w tabeli z Arkusza3 (plik lekcja2.xlsx.).
Ćwiczenie 6. Napisz funkcję VBA o podobnym nagłówku, lecz nazwie lata_pracy2, która zwraca wartość ilości
faktycznie przepracowanych lat obliczanych za pomocą funkcji WorksheetFunction.YearFrac(). Zapoznaj się w pomocy Visual Basic z opisem tej funkcji. Użyj funkcji Int do odrzucenia części ułamkowej wartości zwracanej przez funkcję YearFrac. Wykorzystaj napisaną przez siebie funkcję do uzupełnienia odpowiedniej kolumny w tabeli z Arkusza3 (plik lekcja2.xlsx.).
Ćwiczenie 7. Zaprojektuj funkcję VBA o nagłówku
Function Wysługa1(lata As Integer, pensja As Currency) As Currency
Za pomocą której będzie można uzupełnić odpowiednią kolumnę w tabeli z Arkusza3 (plik lekcja2.xlsx.)
Uwaga: Dodatek do pensji o nazwie wysługa jest przyznawany po pięciu pełnych latach pracy i stanowi 5% pensji zasadniczej; zwiększa się co roku o 1% aż do 20%.
Oznacza to, że osoba, której staż pracy wynosi np. 25 lat otrzymuje dodatek w wysokości 20% * pensja zasadnicza.
Osoby, które pracują krócej niż 5 lat nie otrzymują dodatku.
W zadaniu wykorzystaj instrukcję warunkową If. Wykorzystaj napisaną przez siebie funkcję do uzupełnienia kolumny Wysługa1 w tabeli z Arkusza3 (plik lekcja2.xlsx.) .
Ćwiczenie 8. Napisz funkcję VBA o nagłówku:
Function Wysługa2(data As Date, pensja As Currency) As Currency
wyznaczającą podobnie jak poprzednia wysługę lat. Wywołaj funkcję lata_pracy1 wewnątrz tej funkcji, w celu wyznaczenia lat pracy na podstawie argumentu data. Ponadto zastosuj zamiast instrukcji If instrukcję
Select Case. Wykorzystaj napisaną przez siebie funkcję do uzupełnienia odpowiedniej kolumny w tabeli z Arkusza3 (plik lekcja2.xlsx.).
Ćwiczenie 9. Zaprojektuj funkcję VBA o nazwie Dodatek_szkodliwy, która wyznaczy wartość dodatku szkodliwego zgodnie z tabelą:
Stanowisko Lata pracy Dodatek szkodliwy
Spawacz, ślusarz, malarz <10 500 złotych
Spawacz, ślusarz, malarz >=10 1000 złotych
Inne dowolna 0 złotych
Wykorzystaj napisaną przez siebie funkcję do uzupełnienia odpowiedniej kolumny w tabeli z Arkusza3 (plik lekcja2.xlsx.).
Ćwiczenie 10. Zaprojektuj funkcję VBA które na podstawie kolumn Imię i Nazwisko wyznaczają
• płeć (wypisuje napisy mężczyzna albo kobieta),
• inicjały.
Wykorzystaj napisaną przez siebie funkcję do uzupełnienia odpowiedniej kolumny w tabeli z Arkusza3 (plik lekcja2.xlsx.).
