Transcript Document 169511

Wprowadzenie do VBA Excel
2013
Źródło:
Excel 2013PL. Programowanie w VBA dla
bystrzaków
J.Walkenbach, tł. R.Górnowicz, G.Kowalczyk
Ustawienia makr
Plik / Opcje / Centrum zaufania / Ustawienia
centrum zaufania
Karta Ustawienia makr:
- Wyłącz wszystkie makra bez powiadamiania
- Wyłącz wszystkie makra i wyświetl
powiadomienie
- Wyłącz wszystkie makra oprócz makr
podpisanych cyfrowo
- Włącz wszystkie makra
2
O czym warto wiedzieć
• Można wskazać Zaufany folder (zaufane
lokalizacje); przydaje się do przechowywania
skoroszytów z zaufanych źródeł a Excel nie
pyta o włączenie makr
3
Czym jest VBA
• Co to jest VBA ?
• Automatyzacja
• Jakie zadania VBA może wspomagać ?
Przykłady szczegółowych czynności i funkcjonalności:
- Wprowadzanie dużych ilości tekstu
- Automatyzacja często wykonywanych zadań
- Automatyzacja powtarzalnych operacji
- Własne polecenia
- Własne przyciski
- Własne funkcje stosowane z poziomu arkusza
- Własne dodatki aplikacji MS Office
- Aplikacje oparte na makrach
4
Plusy i minusy VBA
•
•
•
•
•
•
•
•
•
•
Powtarzalność
Szybkość
Postulowana bezbłędność
Możliwość delegowania
obsługi
Większe „możliwości”
Oszczędność czasu
Konieczność zdobycia wiedzy
Konieczność posiadania aplikacji
Zwykle niezbędna jest obsługa techniczna
Konieczność nadążania za zmianami w środowisku
5
O czym warto wiedzieć
• Rola VBE i modułu
• Procedury Sub i procedury Function (funkcje)
• VBA operuje na obiektach o strukturze hierarchicznej (pojęcie
kontenera, model obiektowy)
• Kolekcje obiektów tego samego typu
• Odwołanie się do obiektu przez podanie położenia w
hierarchii (rola znaku . czyli kropki)
• Pojęcie obiektu aktywnego
• Właściwości obiektów (rola znaku . czyli kropki)
• Zmienna i przypisywanie jej wartości
• Metody obiektów i jej wywołanie
• Inne elementy – odpowiedniki w innych językach
programowania
6
Warto wiedzieć
Wersje Excela z którymi możemy się spotkać
Excel 97, 2000, 2002 (XP), 2003, 2007 (większe
arkusze), 2010 (wersja 64-bitowa), 2013 (online i
tablety).
7
Przygotowanie do pracy
• Konieczność dostępu do karty DEWELOPER
• Wstążka – Dostosuj wstążkę – Opcje programu
Excel – Deweloper
• Rejestrowanie makra (Deweloper | Kod |
Zarejestruj makro)
• Testowanie makra, modyfikacja, zapisywanie
• Podgląd kodu makra (ALT+F11) i struktura
interfejsu VBE
8
Uwagi strukturalizujące wiedzę
• Makro działa wtedy gdy jest zawarte w otwartym
skoroszycie
• Skoroszyt nie musi być aktywny
• Nagrywanie makr kasuje poprzedni tekst makra
• Jest wiele sposobów uruchamiania makr
• Makro można napisać ręcznie
• Makro można zapisać w skoroszycie makr
osobistych (PERSONAL.XLSB) aby było
automatycznie dostępne
• Makra można skonwertować do tzw. pliku dodatku
9
Visual Basic Editor
• Uruchamianie
• Komponenty:
pasek narzędzi (View | Tools)
okno Code
pasek menu
okno Properties
okno Project (Ctrl+R),
okno Immediate (Ctrl+G)
okna Locals i Watches
• Pamiętajmy o Undo (Ctrl+Z)
10
Do zrobienia
• Pierwsze makro
Co zauważymy:
- Definiowanie procedury typu Sub
- Przypisywanie wartości zmiennym
- Łączenie łańcuchów znaków
- Użycie wbudowanej funkcji VBA
- Użycie predefiniowanych stałych
- Użycie konstrukcji warunkowej
- Zakończenie procedury typu Sub
11
Rejestrowanie makr
• Przebieg czynności
(Wyłączamy odwołania względne)
- dodajemy nowy arkusz
- schowamy dwie kolumny (Ctrl+Shift+strzałka
w prawo)
- schowamy jeden wiersz
- zaznaczymy komórkę A1
12
Ustawienia środowiska
•
•
•
•
•
VBE, Tools | Options
Karta Editor
Karta Editor Format
Karta General
Karta Docking
13
Hierarchia obiektów Excela
• Application
• Addin, Window, Workbook,
WorksheetFunction
• Hierachia Workbook: Chart, Name, VBProject,
Window, Worksheet
• Hierarchia Worksheet: Comment, Hyperlink,
Name, PageSetup, PivotTable, Range
14
Kolekcje
•
•
•
•
Workbooks
Worksheets
Charts
Sheets
Po co kolekcje?
Jak do nich się odwołać?
Jak „wędrować” po hierarchii obiektów?
Jak uprościć odwołania do obiektów?
15
Obiekty
• Właściwości obiektów: sprawdzenie i zmiana
• Metody obiektów: wywoływanie
• Zdarzenia obiektów: Activate
Gdzie szukać informacji?
• System pomocy VBA
• Object Browser (F2)
• Auto List Members (automatyczna lista wł. i met.)
16
Procedury i ich rodzaje
• Procedura Sub
• Procedura Function (funkcja)
• Wymagania formalne względem nazw
• Sposoby wywołań (wiele!) oraz z poziomu
arkusza (kilka!)
17
Rejestrator makr
• Przemyśleć jaką czynność makro ma wykonać
• Wybrać ustawienia nagrywania makr
(adresowanie względne czy bezwzględne)
• Określić nazwę makra, klawisz skrótu, miejsce
przechowywania, opis
• Nagrać makro
• Przetestować
• Uporządkować kod
18
Rejestrator makr
• Czego rejestrator nie jest w stanie wykonać?
• Co jest rejestrowane?
• Opcje rejestratora makr
• Przydatna konstrukcja With … End With
• Komentarze i ich znaczenie
19
Zasady korzystania ze zmiennych itp.
• Pojęcie zmiennej
• Wymagania względem nazw zmiennych
• Typy danych; Option Explicit, Dim, Variant i
automatyka
• Static, Public, Private – zasięg zmiennych
• Ważne: jedna zmienna – jeden wiersz
• Czas życia zmiennych
• Stałe predefiniowane
• Przypisanie i operatory
20
Tablice
• Deklarowanie
• Instrukcja Option Base 1
• Tablice dynamiczne (Redim)
21
Obiekty Range
•
•
•
•
•
•
•
•
•
Odwołanie przez zakres (w tym komórkę)
Odwołanie przez nazwę zakresu
Właściwość Cells
Właściwość Offset
Właściwość Value
Właściwość Text
Właściwości Count, Column, Row, Address
Właściwość HasFormula, Font
Właściwości Interior, Formula, FormulaLocal
(„polskie” formuły Excela), NumberFormat
22
Obiekty Range; Metody
• Select (uwaga na konieczność aktywacji
arkusza!)
• Goto
• Copy i Paste
• Clear i Delete
23
Korzystanie z funkcji VBA i arkusza
•
•
•
•
•
•
•
Co to jest funkcja
Trzy rodzaje funkcji
Przykłady użycia funkcji VBA
Określanie typu wybranego obiektu
Odkrywanie funkcji VBA
Przykłady użycia funkcji arkusza
Przykłady użycia własnych funkcji
24
Programowanie strukturalne i
podejmowanie decyzji
•
•
•
•
Sterowanie przepływem
Programowanie strukturalne
(1 wejście i 1 wyjście kodu)
Struktury
If-Then;
If-Then-Else, ElseIf
Select-Case
Pętle i ich prędkość
For-Next
For-Next-Step
For-Next-Exit For
Do-While
Do-Until
For Each-Next z kolekcjami
25
Procedury automatyczne i zdarzenia
• Dotyczą skoroszytów i arkuszy
• Przykłady:
– Zamknięcie, aktywacja i otwarcie skoroszytu
– Aktywacja okna i jego dezaktywacja
– Kliknięcie obiektu
– Wprowadzenie danych do komórki
– Wystąpienie błędu
itd.
26
Kod VBA obsługi zdarzeń
•
•
•
•
Gdzie znajduje się kod? Nie w standardowym module!
Tworzenie procedury obsługi zdarzenia
Korzystanie z edytora VBE
Przykłady:
zdarzenie Open dla skoroszytu
zdarzenia BeforeClose / BeforeSave dla skoroszytu
zdarzenia aktywacji i dezaktywacji arkusza i skoroszytu
zdarzenia BeforeDoubleClick/BeforeRightClick
zdarzenie Change
zdarzenia niezwiązane z obiektami
27
Obsługa błędów
• Rozpoznawanie błędów
• Obsługiwanie występujących błędów
• Wznawianie działania programu po błędzie
• Wywoływanie błędów
Unikanie błędów
Ignorowanie błędów
Instrukcja On Error GoTo etykieta; Resume; Resume
Next; Resume etykieta
28
Debugowanie
•
•
•
•
•
•
•
Przyczyny błędów w programach
Metody i techniki walki z „pluskwami”
Rola funkcji MsgBox
Polecenie Debug.Print
Debugger; punkty przerwań
Okna Immediate, Watch, Locals
Zalecenia redukcji liczby błędów
Techniki programowania warte uwagi
Przetwarzanie zakresów komórek – kopiowanie
Sub KopiowanieZakresu1()
Range("A1:A5").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Sub KopiowanieZakresu2()
Range("A1:A5").Copy Range(”B1”)
End Sub
Kopiowanie zakresu o zmiennej wielkości
Sub KopiowanieBiezacegoRegionu1()
Range("A1").CurrentRegion.Copy
Sheets("Arkusz2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Arkusz1").Select
Application.CutCopyMode = False
End Sub
Sub KopiowanieBiezacegoRegionu2()
Range("A1").CurrentRegion.Copy Sheets("Arkusz2").Range("A1")
End Sub
Sub KopiowanieBiezacegoRegionu3() ' tylko dla specjalnie wstawionej tabeli
Range("Tabela1").Copy Sheets("Arkusz2").Range("A1")
End Sub
Techniki programowania warte uwagi
Zaznaczenie komórek do końca wiersza lub kolumny
Range(ActiveCell,ActiveCell.End(xlDown)).Select
Range(ActiveCell,ActiveCell.End(xlDown)).Font.Bold = True
Zaznaczenie całego wiersza lub całej kolumny
ActiveCell.EntireColumn.Select
ActiveCell.EntireRow.Select
Przenoszenie zakresów
Sub PrzeniesZakres1()
Range("A1:C6").Select
Selection.Cut
Range("A10").Select
ActiveSheet.Paste
End Sub
Sub PrzeniesZakres2()
Range("A1:C6").Cut Range("A10")
End Sub
Techniki programowania warte uwagi
Wprowadzenie wartości do komórki
Range("A1").Value = InputBox(”Wprowadź wartość dla komórki A1: ”)
Dim x As Variant
x = InputBox(”Wprowadź wartość dla komórki A1: ”)
If x <> ”” Then Range("A1").Value = x
Uzależnienie operacji od typu zaznaczonego zakresu
Sub SprawdzenieSelection()
If TypeName(Selection) <> ”Range” Then
MsgBox ”Zaznacz zakres komórek.”
Exit Sub
End If
… tutaj dalsza część kodu, wykonywana gdy wszystko w porządku
End Sub
Automatyczne dodawanie wykresów
Sub DodawanieWykresow()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Arkusz1!$A$1:$A$3")
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = "To jest mój wykres"
End Sub
Techniki programowania warte uwagi
Pętla w kolekcji ChartObjects
Sub PetlaWWykresach()
Dim cht As ChartObject
For Each cht In ActiveSheet.ChartObjects
cht.Chart.Type = xlArea
Next cht
End Sub
Sposoby przyspieszania działania programów w VBA
Application.ScreenUpdating = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = False
Korzystanie ze zmiennych obiektowych (także przyspiesza):
Set Rate=Workbooks(”MójSkoroszyt.xlsx”).WorkSheets(”Arkusz1”).Range(”StawkaProwizji”)
Rate.Value = 0.085
Techniki programowania warte uwagi
Praca z formularzami UserForm
Sub PokazDialog()
UserForm1.Show
End Sub
Private Sub OKButton_Click()
Range("A1").Value = CheckBox1.Value
Unload UserForm1
End Sub