Wstęp do makr

26 XI 2014

• środowiska pracy programisty VBA• bezpieczeństwo – poziom zabezpieczeń• tworzenie makr przy pomocy nagrywania• przypiswanie makr do przycisków i paska

„szybki dostęp”

Wstęp do makr

Co można uzyskać dzięki językowi VBA?

• Automatyzacja czynności, które wykonujemy w regularnych odstępach czasu (np. aktualizacja Monitora Konwergencji) albo wielokrotnie (ta sama operacja w wielu wierszach lub arkuszach)

• Własne funkcje• Własne interfejsy służące do wprowadzania danych i

prezentacji wyników

Jak wyświetlić kartę Deweloper?Plik / Informacje / Dostosowywanie wstążki / Deweloper

Jak utworzyć makro?• Poprzez rejestrację• Poprzez napisanie kodu źródłowego w module Visual Basic

Karta Deweloper

Bezpieczeństwo makr

• Plik• Opcje programu Excel• Centrum zaufania• Ustawienia centrum zaufania

– Ustawienia makr– Zaufane lokalizacje (dodaj nową lokalizację)

Ćwiczenie: przykładowe makro• Wpisz dowolną liczbę w dowolną komórkę• Deweloper/Użyj odwołań względnych lub bezwzględnych• Deweloper/Zarejestruj makro• Nazwij makro (brak odstępów), nadaj skrót, dodaj komentarz• Jeśli chcesz używać makra we wszystkich skoroszytach, zapisz

je w skoroszycie makr osobistych• Po naciśnięciu OK, wciśnij ctrl+c• Przejdź do drugiego arkusza i wciśnij ctrl+v• Zatrzymaj rejestrowanie makra• Zarejestrowałeś/-aś pierwsze makro! • Możesz teraz wypróbować, jak działa za pomocą przypisanego

uprzednio skrótu

Ćwiczenie – ciąg dalszy• Przejrzyj zarejestrowane makro: Alt+F11 lub Deweloper/Visual Basic lub

Deweloper/makra/edycja

Sub Przeklej()'' Przeklej Makro <- KOMENTARZE SĄ IGNOROWANE PRZEZ KOMPILATORA' Wkleja zawartość komórki do drugiego arkusza'' Klawisz skrótu: Ctrl+i' Selection.Copy ' Kopiuj Sheets("Arkusz2").Select ' Wybierz Arkusz2 ActiveSheet.Paste ' WklejEnd Sub

• Zmień powyższy kod procedury tak, aby zawartość komórki źródłowej była wklejana do komórki wynikowej położonej w tym samym miejscu, co komórka źródłowa (choć w innym arkuszu)

• Skorzystaj z następujących linijek:

Dim adres As String ' Deklarowanie zmiennejadres = ActiveCell.Address ' Przypisanie adresu do zmiennejRange(adres).Select ' Wybór komórki o uprzednio zapisanym adresie

Jak przypisać makro do przycisku?• Deweloper/Wstaw/Formanty formularza/Przycisk

• Wybierz makro, które chcesz przypisać i naciśnij OK.• Jeśli chcesz zmienić etykietę (napis) na przycisku, naciśnij

prawym klawiszem myszy na przycisk, a potem „edytuj tekst”

Jak przypisać makro do paska „Szybki dostęp”?

• Kliknij na wstążkę prawym klawiszem myszy• Dostosuj pasek narzędzi „Szybki dostęp”

• Wybierz polecenia z: Makra• Zaznacz makro, które chcesz dodać, dodaj i naciśnij OK

Podstawy Visual Basic for Applications (cz. I)

• główne elementy okna edytora VBA (eksplorator projektu, właściwości, kod programu, formularz)

• zasady tworzenia procedur• zmienne – definiowanie i ich cechy• definicja pojęć: obiekt, atrybut (właściwość),

metoda, argument, kolekcja• sterowanie przebiegiem programu: konstrukcje

warunkowe i pętle

Okno Edytor projektu VB• Skrót Atl+F11 lub Deweloper/Visual Basic

Pasek menuPasek narzędziowy

Okno Eksplorator projektu

Okno kodu

Okno bezpośrednich instrukcji

Okno właściwości

Okno Eksplorator projektu• Dojście na skróty: (alt+F11) ctrl+r • Zawiera trzy przyciski:

– Pokaż kod– Pokaż obiekt– Przełącz foldery

• Wyświetla hierarchiczną listę aktualnie otwartych projektów oraz zawartych w nich elementów, m.in.:

• Arkusze• Wykresy• ThisWorkbook (skoroszyt)• Moduły (w tym klasy)• Formularze (UserForm)• Odwołania do innych projektów

Standardowy pasek narzędzi VB

Pokaż Microsoft

Excel

Uruchom procedurę

Sub

Wstaw UserForm Zresetuj

Tryb projekto-

waniaPrzerwij

Wiersz i kolumna w oknie kodu

Przybornik

Przeglądarka obiektów

Okno właściwości

Eksplorator projektu

Okno właściwości• Dojście na skróty: (alt+F11) F4• Służy do przeglądania i

ustawiania właściwości zaznaczonego obiektu, np. formularza, projektu lub modułu

• Właściwości przeglądamy alfabetycznie lub według kategorii

Okno Formularz• Umożliwia tworzenie

okien dialogowych• Do rysowania

formantów służą przyciski (kontrolki) przybornika (toolbox)

• Do ustawiania właściwości formantów służy okno właściwości

Ćwiczenie: projektujemy formularzObiekt Name Caption/Text Inne

USERFORM usfRegula SRW

FRAME frmRamka Stabilizująca reguła wydatkowa

LABEL lblKwota_1 Kwota wydatków w roku n-1

LABEL lblKwota_wynik BorderStyle 1

TEXTBOX txtKwota_1 600000000

TEXTBOX txtKorektaCPI 1

TEXTBOX txtPrognozaCPI 1,025

TEXTBOX txtSredniPKB 1,03

TEXTBOX txtKorekta -0,02

BUTTON cmdOblicz Oblicz

BUTTON cmdZakoncz Zakończ

Okno Kod programu• Dojście na skróty: (alt+F11) F7 lub • nacisnąć prawym przyciskiem myszy na

dany formularz / moduł, a następnie „view code”

• Służy do wpisywania, przeglądania i edytowania kodu

• U samej góry okna – dwa rozwijane pola listy (obiekt i procedury), dzięki którym można szybko poruszać się w obrębie programu

• Prawe ramię gwiazdki wskazuje linię podziału okna

• W lewym dolnym rogu znajdują się ikonki o nazwie widok i pełny widok modułu

Procedury: Sub i Function

• Procedura Sub (zwykła)– wykonuje określone zadania, – nie zwraca jawnie żadnych wartości,– zaczyna się Sub, a kończy End Sub– Private Sub zabezpiecza przed wykorzystaniem

procedur z innych formularzy• Procedura Function (funkcja)

– wykonuje określone zadania,– zwraca pojedynczą wartość,– Zaczyna się Function, a kończy End Function

Jak pisać kod procedury w VB?

• Z klawiatury / edytować zarejestrowane makra / kopiować kod z innego modułu

• Wcięcia wiersza za pomocą tab (szczególnie przy pętlach i warunkach logicznych)

• Łamanie wierszy za pomocą podkreślnika _• Generalnie wielkość liter nie ma znaczenia• Cofamy operację za pomocą ctrl+z• Zapisujemy za pomocą ctrl+s, jeśli skoroszyt posiada

makro, to rozszerzenie .xlsm

Ćwiczenie: piszemy programOption Explicit ‘To polecenie wymusza deklarację zmiennychDim Kwota_wynik As Single ‘Deklarujemy zmienneDim Kwota_1 As SingleDim KorektaCPI As SingleDim PrognozaCPI As SingleDim SredniPKB As SingleDim Korekta As Single

Private Sub cmdOblicz_Click()'Procedura oblicza kwotę wydatków na rok nKwota_1 = txtKwota_1.ValueKorektaCPI = txtKorektaCPI.ValuePrognozaCPI = txtPrognozaCPI.ValueSredniPKB = txtSredniPKB.ValueKorekta = txtKorekta.ValueKwota_wynik = Kwota_1 * KorektaCPI * PrognozaCPI * (SredniPKB + Korekta)lblKwota_wynik = Kwota_wynikEnd Sub

Private Sub cmdZakoncz_Click()Unload MeEnd Sub

Typy danych w Visual BasicTyp danych Wielkość (w bajtach) Charakterystyka (zakres)Boolean 2 Wartość logiczna PRAWDA / FAŁSZByte 1 Mała liczba całkowita od 0 do 255Integer 2 Liczba całkowita od -32768 do 32767Long 4 Liczba całkowita długa (miliardy) -2147483648

do 2147483648Single 4 Liczba rzeczywista od -3,402823E38 do

1,401298E45Double 8 Liczba rzeczywista dwukrotnie większa od liczby

Single (E-324 do E308)Currency 8 Liczba całkowita z 4 miejscami po przecinkuDecimal 14 Duże liczby zapisywane w systemie dziesiętnymDate 8 Data w przedziale 1/1/100 do 31/12/9999String Długość ciągu znaków Łańcuch znaków od 0 do 65535 znakówObject 4 Umożliwa dostęp do któregokolwiek obiektu,

rozpoznawanego przez ExcelArray Zależy od wielkości tablicy

Definicje

• Obiekt – element manipulowany przy użyciu VBA, np. zakresy, wykresy, rysunki itp.

• Atrybut/właściwość/własność – określona cecha obiektu, np. pole tekstowe ma czcionkę, tekst, rodzaj ramki, nazwę itp.

• Metoda – operacja powiązana z obiektem, np. metoda Show służy do pokazania (usfRegula.Show)

• Argument – dane konieczne do tego, aby dana procedura mogła działać

• Kolekcja – zbiór podobnych obiektów, np. kolekcja Sheets (zawiera arkusze i wykresy), kolekcja Workbooks, kolekcja Worksheets

Konstrukcje warunkowe (cz. I): IF, THEN, ELSE• If warunek Then instrukcja

Np. If (PrognozaCPI > 1.05 And SredniPKB > 1.04) Or PrognozaCPI <= 1 Then MsgBox ("Zła prognoza")

• If warunek Then[instrukcje]

End If

• If warunek Then Instrukcje1 Else Instrukcje2

• If warunek1 Then[instrukcje, gdy warunek1 jest prawdą]

ElseIf warunek2 Then[instrukcje2, gdy warunek2 jest prawdą]

ElseIf warunek3 Then[instrukcje3, gdy warunek3 jest prawdą] …

Else[instrukcje, gdy wszystkie warunki są fałszywe] …

End If

Konstrukcje warunkowe (cz. II): SELECT CASE

Select Case wyrażenieCase wartość1

Instrukcje, gdy wartość1 równa się wyrażeniuCase wartość2

Instrukcje, gdy wartość2 równa się wyrażeniuCase wartość3

Instrukcje, gdy wartość3 równa się wyrażeniu …End Select

Pętle UNTIL, WHILE i FOR

DoIf warunek Then Exit Do[instrukcje]

Loop

Do Until warunek [instrukcje]

Loop

Do While warunek [instrukcje]

Loop

Do [instrukcje]

Loop While warunek

For licznik=start To Stop If warunek Then Exit For [instrukcje]

Next licznik

For Each element In Grupa [instrukcje]

Next element

27

Pętle – ćwiczenie (cz. I)

utrzymywanie wyniku sektora GG na odpowiednim poziomie w stosunku do wzrostu gospodarczego w danym kraju pozwala na stopniowe obniżanie relacji długu do PKB zgodnie z następującym wzorem:

gdzie:d* - relacja długu do PKB w długim okresieb – wynik w relacji do PKBg – wzrost nominalnego PKB

Np.

cel dla wyniku

sektora GG (w % PKB)

tempo wzrostu

nominalnego PKB

poziom długu w długim

okresie (w % PKB)

-1% 6,5% 16%-1% 5,5% 19%

-1% 4,5% 23%-1% 3,5% 30%

-1% 2,5% 41%

-0,5% 6,5% 8%

-0,5% 5,5% 10%

-0,5% 4,5% 12%

-0,5% 3,5% 15%

-0,5% 2,5% 21%

g)·(1 gbd*

0,160,065)·(1 0,065

0,01

Pętle – ćwiczenie (cz. II)

• Zapisz program, który poda wskaźnik długu GG do PKB w długim okresie w zależności od wyniku sektora GG i średniookresowego wzrostu nominalnego PKB

Sub Przycisk1_Kliknięcie()Dim d As SingleDim b As SingleDim g As Single

For g = 2 To 6Cells(1, g) = g

For b = -3 To 1Cells(b + 5, 1) = b

Cells(b + 5, g) = (-b / g) * (1 + g / 100) NextNextEnd Sub

Okno Instrukcje bezpośrednie

• Dojście na skróty (alt+F11) ctrl+g• Dzięki temu oknu można przećwiczyć różne funkcje i instrukcje• Po wpisaniu instrukcji, Excel poda wynik• Wypróbuj:

– Worksheets("Arkusz2").Activate– Range("B2:B5").Select– [B2:B5].Value = 13– ?Range("B5")– Selection.ClearContents– ?2+2– ?Selection.Address– ?Selection.Rows.Count– ?ActiveWorkbook.Name