Arkusze kalkulacyjne i VBA

Uniwersytet Łódzki

Wydział Matematyki i Informatyki

Arkusze kalkulacyjne i VBA

Tadeusz Krasiński

Lekcja 2. Dalsze elementy VBA

Adres:

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

Studia Podyplomowe „Analiza danych i data mining”

Rok akademicki 2017/18

Zmienne

1. 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 maja typ Variant.

Typy zmiennych:

Ćwiczenie. 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:1. nadać xx wartość niecałkowitą, 2.zadeklarować wynik jako całkowity,

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

1. Sub makro1() Dim x1 as Integer

….Instrukcje…

End Sub

Zmienna x1 jako liczba całkowita dostępna tylko w tym makro

2. Dim x2 as String Sub makro1()

….Instrukcje…

End Sub +Kolejne makra

Zmienna x2 jako ciąg symboli dostępna tylko w tym module 3. Public x3 as String

Sub makro1()

….Instrukcje…

End Sub +Kolejne makra

Zmienna x3 jako ciąg symboli dostępna we wszystkich modułach (globalna).

4. Const Proc as Integer = 3

Deklaracja stałej Proc równej 3.

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

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:

1. Range(„A2:C7”) – prostokąt komórek to samo [A2:C7]

2. Range(„A2”,”C7”) – prostokąt

3. Range(„A2:C7, D1:E5”) – 2 prostokąty komórek

4. Range(„A2:C7 C1:E5”) – część wspólna 2 prostokątów komórek

5. Range(„A:A”) – kolumna 6. Range(„A:F”) – kolumny 7. Range(„1:5”) – wiersze

8. Range(„A2:C7”).Columns(1) – 1 kolumna w zakresie 9. Range(„A2:C7”).Rows(3) – 3 wiersz w zakresie

10. Range(„A2:C7”).Cells(2,3) – komórka o numerze (2,3) w zakresie

11. Range(„A2:C7”).Cells(3) – komórka o numerze 3 w zakresie, liczonych od lewej do prawej

12. Range(„b7”).Offset(-1,2) – komórka przesunięta o jeden wiersz w górę i dwie kolumny w prawo.

13. Aby nazwać zakres Set xx= Range(„A2:C7”), dalej np.

xx.Cells(6)

14. Cells(3,5) – komórka o współrzędnych 3 wiersz, 5 kolumna w aktywnym arkuszu, czyli E3

15. Cells(Rows.Count,1) – ostatnia komórka w 1 kolumnie (Count – liczba obiektów w danej kolekcji).

16. Cells(Rows.Count,2).End(xlUp) – ostatnia niepusta

komórka w kolumnie 2.

17. Range(„B3”).Resize(10,3) – rozciągnięcie zakresu 10 razy w dół i 3 w prawo.

18. CurrentRegion - aktualnie wykorzystany zakres komórek z aktualną komórką w tym zakresie.

19. UsedRange - aktualnie wykorzystany zakres komórek w całym arkuszu.

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

1. Napisać program typu Sub usuwający co drugą liczbę z poprzedniego zadania.

Wsk. Wykorzystać metodę Clear .

Ćwiczenie 20. 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 po nowną 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

1. 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,…).

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

1. 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),

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

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

4. Są również zdarzenia związane z całym Excelem AppEvents i z Wykresami ChartEvents

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

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

3. Utwórz nowy skoroszyt i wpisz makro, które wymusza

podanie hasła podczas jego otwierania.

Makra typu Function

Zapisujemy je 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:

i. W arkuszu Excela tak jak zwykłą funkcję Excela, tzn.

1. Zaznaczamy komórkę w której ma pojawić się wartość naszej funkcji.

2. Klikamy Wstaw funkcję

3. Wybieramy Zdefiniowane przez użytkownika.

4. Z listy wybieramy nazwę funkcji.

5. Wpisujemy argumenty.

ii. W oknie Immediate

Wpisujemy

? NazwaFunkcji(Argumenty)

iii. W innym makro Sub aaaa()

Instrukcje Makra

xy= NazwaFunkcji(Argumenty)

Inne Instrukcje

End Sub

Ćwiczenie. 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. 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 21. 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. 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. Napisać makro, które po przekątnej wpisuje liczby od 1 do 100 i koloruje na kolor czerwony vbRed komórki obok.

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

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

1. Gdy chcemy przerwać działanie makro lub pętli:

Exit Sub, Exit For, Exit Do….

2. Gdy chcemy przejść do innej instrukcji:

…….

GoTo Podprogram3

……..

Podprogram3:

Instrukcje

…..

Nie ma End Podprogramu – wykonywane są dalsze instrukcje

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

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