Wprowadzenie do VBA Excel 2013

Wprowadzenie do VBA Excel 2013 Źródło:Excel 2013PL. Programowanie w VBA dla bystrzakówJ.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

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

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

Plusy i minusy VBA • Powtarzalność • Szybkość • Postulowana bezbłędność • Możliwość delegowaniaobsł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

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

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

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

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

Visual Basic Editor • Uruchamianie • Komponenty:pasek narzędzi (View | Tools)okno Codepasek menuokno Propertiesokno Project (Ctrl+R),okno Immediate (Ctrl+G)okna Locals i Watches • Pamiętajmy o Undo (Ctrl+Z)

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

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

Ustawienia środowiska • VBE, Tools | Options • Karta Editor • Karta Editor Format • Karta General • Karta Docking

Hierarchia obiektów Excela • Application • Addin, Window, Workbook, WorksheetFunction • HierachiaWorkbook: Chart, Name, VBProject, Window, Worksheet • Hierarchia Worksheet: Comment, Hyperlink, Name, PageSetup, PivotTable, Range

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?

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

Procedury i ich rodzaje • Procedura Sub • Procedura Function (funkcja) • Wymagania formalne względem nazw • Sposoby wywołań (wiele!) oraz z poziomu arkusza (kilka!)

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

Rejestrator makr • Czego rejestrator nie jest w stanie wykonać? • Co jest rejestrowane? • Opcje rejestratora makr • Przydatna konstrukcja With … End With • Komentarze i ich znaczenie

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

Tablice • Deklarowanie • Instrukcja Option Base 1 • Tablice dynamiczne (Redim)

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

Obiekty Range; Metody • Select (uwaga na konieczność aktywacji arkusza!) • Goto • Copy i Paste • Clear i Delete

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

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

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łęduitd.

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 skoroszytuzdarzenia BeforeClose / BeforeSave dla skoroszytuzdarzenia aktywacji i dezaktywacji arkusza i skoroszytuzdarzenia BeforeDoubleClick/BeforeRightClickzdarzenie Changezdarzenia niezwiązane z obiektami

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

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 – kopiowanieSubKopiowanieZakresu1()Range("A1:A5").Select Selection.Copy Range("B1").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub Sub KopiowanieZakresu2() Range("A1:A5").CopyRange(”B1”)End Sub Kopiowanie zakresu o zmiennej wielkości SubKopiowanieBiezacegoRegionu1() 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.CopySheets("Arkusz2").Range("A1") End Sub Sub KopiowanieBiezacegoRegionu3() ' tylko dla specjalnie wstawionej tabeli Range("Tabela1").CopySheets("Arkusz2").Range("A1") End Sub

Techniki programowania warte uwagi Zaznaczenie komórek do końca wiersza lub kolumnyRange(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 SubPrzeniesZakres1() Range("A1:C6").Select Selection.Cut Range("A10").Select ActiveSheet.Paste End Sub SubPrzeniesZakres2() Range("A1:C6").CutRange("A10") End Sub

Techniki programowania warte uwagi Wprowadzenie wartości do komórkiRange("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 SubSprawdzenieSelection() IfTypeName(Selection) <> ”Range” Then MsgBox ”Zaznacz zakres komórek.” ExitSub End If … tutaj dalsza część kodu, wykonywana gdy wszystko w porządku End Sub Automatyczne dodawanie wykresów SubDodawanieWykresow() 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 ChartObjectsSubPetlaWWykresach() Dimcht As ChartObject For Eachcht In ActiveSheet.ChartObjects cht.Chart.Type = xlArea Nextcht 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

Wprowadzenie do VBA Excel 2013