Excel 2003 PL. Programowanie w VBA. Vademecum profesjonalisty

Wydawnictwo Helion

ul. Chopina 6

44-100 Gliwice

tel. (32)230-98-63

e-mail: [email protected]

PRZYK£ADOWY ROZDZIA£PRZYK£ADOWY ROZDZIA£

IDZ DOIDZ DO

ZAMÓW DRUKOWANY KATALOGZAMÓW DRUKOWANY KATALOG

KATALOG KSI¥¯EKKATALOG KSI¥¯EK

TWÓJ KOSZYKTWÓJ KOSZYK

CENNIK I INFORMACJECENNIK I INFORMACJE

ZAMÓW INFORMACJEO NOWO�CIACH

ZAMÓW INFORMACJEO NOWO�CIACH

ZAMÓW CENNIKZAMÓW CENNIK

CZYTELNIACZYTELNIA

FRAGMENTY KSI¥¯EK ONLINEFRAGMENTY KSI¥¯EK ONLINE

SPIS TRE�CISPIS TRE�CI

DODAJ DO KOSZYKADODAJ DO KOSZYKA

KATALOG ONLINEKATALOG ONLINE

Excel 2003 PL.Programowanie w VBA.Vademecum profesjonalisty

Excel jeszcze bogatszy, porêczniejszy i dostosowany do Twoich potrzeb

Mo¿liwo�ci Excela s¹ ogromne, jednak nie zawsze w pe³ni odpowiadaj¹ naszym

potrzebom. Najpopularniejszy obecnie arkusz kalkulacyjny zawiera tak¿e wiele

interesuj¹cych funkcji ukrytych w tle i nieznanych typowemu u¿ytkownikowi.

Czy istnieje sposób na wykorzystanie ukrytych funkcji Excela oraz dostosowanie tych

znanych do wymagañ stawianych przez konkretne zadanie? Tak -- tym sposobem jest

Visual Basic for Applications (VBA). VBA to jêzyk programowania umo¿liwiaj¹cy

tworzenie aplikacji bazuj¹cych na mo¿liwo�ciach innych aplikacji, w tym przypadku —

na mo¿liwo�ciach Excela.

Ksi¹¿ka „Excel 2003 PL. Programowanie w VBA. Vademecum profesjonalisty”

przeznaczona jest dla wszystkich u¿ytkowników Excela, którzy chc¹ pog³êbiæ swoj¹

wiedzê o tej aplikacji i stworzyæ w³asne, powi¹zane z ni¹, programy. Przedstawia

podstawy programowania w Excelu oraz techniki zaawansowane — wszystko, co jest

zwi¹zane z projektowaniem aplikacji w Excelu i programowaniem w jêzyku VBA.

• Niezbêdne wiadomo�ci o Excelu i wykorzystywanych przez

niego formatach plików

• Zasady projektowania aplikacji arkusza kalkulacyjnego

• Programowanie w jêzyku VBA

• Tworzenie niestandardowych okien dialogowych — formularzy UserForm

• Pisanie narzêdzi dla programu Excel

• Praca z wykresami

• Programowanie obs³ugi zdarzeñ

• Wspó³praca z innymi aplikacjami

• Tworzenie elementów aplikacji — menu, pasków narzêdzi i systemów pomocy

• Operacje na plikach

• Komponenty jêzyka VBA

Niewielu u¿ytkowników Excela jest tak naprawdê �wiadomych jego mo¿liwo�ci.

Przeczytaj niniejsz¹ ksi¹¿kê i do³¹cz do tej elitarnej grupy.

Autor: John Walkenbach

T³umaczenie: Rados³aw Meryk, Piotr Pilch

ISBN: 83-7361-504-0

Tytu³ orygina³u: Excel 2003 Power Programming with VBA

Format: B5, stron: 872

http://helion.pl/view/2547w



http://helion.pl/add/2547w/e23pvb




mailto:[email protected]

http://helion.pl/view/2547w/e23pvb.htm

http://www.amazon.com/exec/obidos/ASIN/0764540726/eboinf-20

��

��

��

�� ! ��Historia arkuszy kalkulacyjnych w zarysie ...........................................................................29

Wszystko zaczęło się od programu VisiCalc ..................................................................29Program 1-2-3 firmy Lotus..............................................................................................30Program Quattro Pro........................................................................................................33Program Microsoft Excel.................................................................................................34

Excel jako dobre narzędzie dla projektantów aplikacji .........................................................36Rola Excela w strategii Microsoftu .......................................................................................38

�� "��#�� Myślenie obiektowe...............................................................................................................39Skoroszyty .............................................................................................................................40

Arkusze............................................................................................................................40Arkusze wykresów ..........................................................................................................41Arkusze makr XLM.........................................................................................................42Arkusze dialogowe programów Excel 5/95.....................................................................42

Interfejs użytkownika w Excelu ............................................................................................42Menu................................................................................................................................42Menu podręczne ..............................................................................................................43Paski narzędzi ..................................................................................................................43Okna dialogowe...............................................................................................................44Funkcja „przeciągnij i upuść”..........................................................................................45Skróty klawiaturowe........................................................................................................45Inteligentne tagi ...............................................................................................................45Panel zadań ......................................................................................................................46

Wprowadzanie danych...........................................................................................................46Formuły, funkcje i nazwy ......................................................................................................48Dostosowywanie zawartości okna Excela .............................................................................49Zaznaczanie obiektów............................................................................................................49Formatowanie ........................................................................................................................50

Formatowanie liczbowe...................................................................................................50Formatowane stylu ..........................................................................................................50

Kształty ..................................................................................................................................51Wykresy .................................................................................................................................52Makra i programowanie.........................................................................................................52

4 Excel 2003 PL. Programowanie w VBA. Vademecum profesjonalisty

Dostęp do bazy danych ..........................................................................................................53Arkuszowe bazy danych..................................................................................................53Zewnętrzne bazy danych .................................................................................................55

Funkcje internetowe...............................................................................................................55Funkcje związane z językiem XML ......................................................................................56Narzędzia analizy...................................................................................................................56

Konspekty........................................................................................................................56Automatyczne sumy częściowe.......................................................................................56Dodatek Analysis ToolPak ..............................................................................................56Tabele przestawne ...........................................................................................................57Dodatek Solver ................................................................................................................58

Dodatki...................................................................................................................................58Zgodność wersji .....................................................................................................................58Opcje ochrony........................................................................................................................58

Ochrona formuł przed nadpisaniem ................................................................................59Ochrona struktury skoroszytu..........................................................................................59Ochrona skoroszytu przy użyciu hasła ............................................................................60Ochrona kodu języka VBA przy użyciu hasła.................................................................60

System pomocy Excela ..........................................................................................................61

�� $#%��&��#��&��'�� ( Formuły..................................................................................................................................63Obliczanie formuł ..................................................................................................................63Odwołania do komórki lub zakresu .......................................................................................64

Dlaczego warto stosować odwołania, które nie są względne? ........................................65Notacja W1K1 .................................................................................................................65Odwołania do innych arkuszy lub skoroszytów..............................................................66

Zastosowanie nazw ................................................................................................................67Nadawanie nazw komórkom i zakresom.........................................................................67Nadawanie nazw istniejącym odwołaniom .....................................................................68Stosowanie nazw z operatorem przecięcia ......................................................................69Nadawanie nazw kolumnom i wierszom.........................................................................70Obszar obowiązywania nazw ..........................................................................................70Nadawanie nazw stałym ..................................................................................................70Nadawanie nazw formułom.............................................................................................71Nadawanie nazw obiektom..............................................................................................73

Błędy formuł ..........................................................................................................................73Formuły tablicowe .................................................................................................................74

Przykład formuły tablicowej ...........................................................................................74Kalendarz oparty na formule tablicowej .........................................................................75Zalety i wady formuły tablicowej....................................................................................75

Metody zliczania i sumowania ..............................................................................................76Zastosowanie funkcji LICZ.JEŻELI lub SUMA.JEŻELI ...............................................76Zastosowanie formuł tablicowych do zliczania i sumowania .........................................77Inne narzędzia zliczające .................................................................................................79

Używanie daty i czasu ...........................................................................................................79Wprowadzanie daty i czasu .............................................................................................79Stosowanie dat sprzed roku 1900 ....................................................................................80

Tworzenie megaformuł..........................................................................................................81

��)� �� *+Uruchamianie Excela .............................................................................................................85Obsługiwane formaty plików arkuszy kalkulacyjnych..........................................................87

Pliki arkusza kalkulacyjnego 1-2-3 firmy Lotus .............................................................87Pliki arkusza kalkulacyjnego Quattro Pro .......................................................................88

Spis treści 5

Formaty plików baz danych ............................................................................................88Formaty plików tekstowych ............................................................................................89Inne formaty plików ........................................................................................................89

Pliki tworzone przez Excel ....................................................................................................89Pliki formatu XLS ...........................................................................................................90Pliki obszaru roboczego...................................................................................................90Pliki szablonów................................................................................................................91Pliki pasków narzędzi......................................................................................................91Pliki dodatków.................................................................................................................92

Excel i język HTML ..............................................................................................................92W jaki sposób Excel korzysta z formatu HTML? ...........................................................93Zwiększanie złożoności...................................................................................................94Tworzenie interaktywnych plików HTML......................................................................94

Importowanie i eksportowanie plików XML.........................................................................96Czym jest XML? .............................................................................................................96Importowanie zawartości plików XML przy użyciu mapy .............................................97Importowanie zawartości plików XML do listy..............................................................99Eksportowanie zawartości plików XML z Excela ..........................................................99

Ustawienia Excela w rejestrze systemu Windows...............................................................100Rejestr systemu Windows .............................................................................................100Ustawienia Excela .........................................................................................................101

�� !"

��+� ,�#��-��.��-��#-&"�! ��+Robocza definicja aplikacji arkusza kalkulacyjnego ...........................................................105Projektant i użytkownik końcowy .......................................................................................106

Kim są projektanci i czym się zajmują? ........................................................................107Klasyfikacja użytkowników arkuszy kalkulacyjnych ...................................................108Odbiorcy aplikacji arkusza kalkulacyjnego...................................................................108

Rozwiązywanie problemów przy użyciu aplikacji arkusza kalkulacyjnego........................109Podstawowe kategorie aplikacji arkusza kalkulacyjnego....................................................110

Arkusze kalkulacyjne tworzone szybko i niestarannie..................................................111Arkusze kalkulacyjne przeznaczone wyłącznie do użytku prywatnego........................111Aplikacje jednego użytkownika ....................................................................................112Aplikacje typu „spaghetti”.............................................................................................112Aplikacje użytkowe .......................................................................................................112Dodatki zawierające funkcje arkusza ............................................................................113Jednoblokowe budżety ..................................................................................................113Modele warunkowe .......................................................................................................114Aplikacje przechowujące dane i udzielające do nich dostępu.......................................114Aplikacje komunikujące się z bazami danych...............................................................115Aplikacje „pod klucz” ...................................................................................................115

��(� ��#�.��-��&��.��-��#-&"� ��/Podstawowe etapy projektowania........................................................................................117Określenie wymagań użytkownika ......................................................................................118Planowanie aplikacji spełniającej wymagania użytkownika ...............................................119Określenie najwłaściwszego interfejsu użytkownika ..........................................................121

Tworzenie niestandardowych okien dialogowych ........................................................122Zastosowanie kontrolek ActiveX w arkuszu .................................................................122Dostosowanie menu.......................................................................................................123Dostosowywanie pasków narzędzi................................................................................125Tworzenie skrótów klawiaturowych .............................................................................127Rozpoczęcie prac projektowych....................................................................................127


Zadania realizowane z myślą o końcowym użytkowniku ...................................................127Testowanie aplikacji ......................................................................................................128Zwiększanie odporności aplikacji .................................................................................128Tworzenie aplikacji intuicyjnej i estetycznie wyglądającej ..........................................131Tworzenie systemu pomocy przeznaczonego dla użytkownika....................................132Dokumentowanie prac projektowych............................................................................132Przekazanie aplikacji użytkownikom ............................................................................132Uaktualnianie aplikacji w razie konieczności ...............................................................133

Pozostałe kwestie dotyczące projektowania ........................................................................134Wersja Excela zainstalowana przez użytkownika .........................................................134Wersja językowa............................................................................................................134Szybkość systemu..........................................................................................................135Tryby karty graficznej ...................................................................................................135

�� #��$��%�� & ��'�� "�

��/� $.��&��-��#��01� � �Podstawowe informacje o języku BASIC ...........................................................................139Język VBA...........................................................................................................................139

Modele obiektowe .........................................................................................................140Porównanie języka VBA z językiem XLM...................................................................140

Wprowadzenie do języka VBA ...........................................................................................140Edytor Visual Basic .............................................................................................................144

Uruchomienie edytora VBE ..........................................................................................144Okna edytora Visual Basic ............................................................................................145

Zastosowanie okna Project Explorer ...................................................................................146Dodanie nowego modułu VBA .....................................................................................147Usuwanie modułu VBA.................................................................................................148Eksportowanie i importowanie obiektów......................................................................148

Zastosowanie okien Code ....................................................................................................148Minimalizacja i maksymalizacja okien .........................................................................149Przechowywanie kodu źródłowego języka VBA ..........................................................149Wprowadzanie kodu źródłowego języka VBA .............................................................150

Dostosowywanie edytora Visual Basic................................................................................155Zakładka Editor .............................................................................................................156Zakładka Editor Format.................................................................................................158Zakładka General...........................................................................................................159Zastosowanie zakładki Docking....................................................................................160

Rejestrator makr Excela.......................................................................................................160Co właściwie rejestrator makr zapisuje? .......................................................................161Względne czy bezwzględne?.........................................................................................162Opcje związane z rejestrowaniem .................................................................................165Modyfikowanie zarejestrowanych makr .......................................................................166

Obiekty i zbiory ...................................................................................................................167Hierarchia obiektów ......................................................................................................168Zbiory ............................................................................................................................168Odwoływanie się do obiektów ......................................................................................169

Właściwości i metody..........................................................................................................169Właściwości obiektów...................................................................................................170Metody obiektów...........................................................................................................171

Obiekt Comment..................................................................................................................172Pomoc dotycząca obiektu Comment .............................................................................172Właściwości obiektu Comment .....................................................................................173Metody obiektu Comment .............................................................................................173

Spis treści 7

Zbiór Comments ............................................................................................................174Właściwość Comment ...................................................................................................175Obiekty podlegające obiektowi Comment ....................................................................175Sprawdzanie, czy komórka zawiera komentarz ............................................................177Dodanie nowego obiektu Comment ..............................................................................177Kilka przydatnych właściwości obiektu Application ....................................................178

Obiekty Range .....................................................................................................................179Właściwość Range.........................................................................................................180Właściwość Cells...........................................................................................................181Właściwość Offset.........................................................................................................182

Co należy wiedzieć o obiektach?.........................................................................................184Podstawowe zagadnienia, które należy zapamiętać ......................................................184Dodatkowe informacje na temat obiektów i właściwości .............................................185

��*� ��#�.��"��&��-��#��01� �*�Przegląd elementów języka VBA........................................................................................189Komentarze ..........................................................................................................................191Zmienne, typy danych i stałe ...............................................................................................192

Definiowanie typów danych..........................................................................................193Deklarowanie zmiennych ..............................................................................................196Zasięg zmiennych ..........................................................................................................197Zastosowanie stałych.....................................................................................................201Zastosowanie łańcuchów...............................................................................................202Zastosowanie dat ...........................................................................................................203

Instrukcje przypisania ..........................................................................................................204Tablice .................................................................................................................................205

Deklarowanie tablic.......................................................................................................205Deklarowanie tablic wielowymiarowych ......................................................................206

Zmienne obiektowe..............................................................................................................207Typy danych definiowane przez użytkownika ....................................................................208Funkcje wbudowane ............................................................................................................208Manipulowanie obiektami i zbiorami ..................................................................................211

Konstrukcja With ... End With ......................................................................................211Konstrukcja For Each ... Next .......................................................................................212

Sterowanie wykonywaniem procedur..................................................................................214Instrukcja GoTo.............................................................................................................214Konstrukcja If ... Then...................................................................................................215Konstrukcja Select Case ................................................................................................218Wykonywanie bloku instrukcji w ramach pętli .............................................................221

�� 2��&��.��%�-��#��01� ��/Deklarowanie i tworzenie procedur Sub języka VBA.........................................................227

Deklarowanie procedury Sub ........................................................................................227Zasięg procedury ...........................................................................................................228

Wykonywanie procedur Sub................................................................................................229Wykonywanie procedury przy użyciu polecenia Run Sub/UserForm ..........................230Uruchamianie procedury z poziomu okna dialogowego Makro ...................................230Wykonywanie procedury przy użyciu skrótu opartego na klawiszu Ctrl......................231Wykonywanie procedury przy użyciu niestandardowego menu...................................232Wywoływanie procedury z innej procedury..................................................................233Wykonywanie procedury przy użyciu przycisku paska narzędzi..................................237Wykonywanie procedury poprzez kliknięcie obiektu ...................................................238Wykonywanie makra po wystąpieniu zdarzenia ...........................................................239Wykonywanie procedury z poziomu okna Immediate ..................................................239


Przekazywanie argumentów procedurom............................................................................240Metody obsługi błędów .......................................................................................................243

Przechwytywanie błędów..............................................................................................243Przykłady kodu źródłowego obsługującego błędy ........................................................245

Rzeczywisty przykład wykorzystujący procedury Sub .......................................................247Cel..................................................................................................................................247Wymagania projektowe.................................................................................................247Dostępne informacje......................................................................................................248Sposób realizacji............................................................................................................248Co należy wiedzieć? ......................................................................................................249Wstępne rejestrowanie makra........................................................................................249Wstępne przygotowania ................................................................................................250Pisanie kodu źródłowego...............................................................................................251Tworzenie procedury sortującej ....................................................................................252Dodatkowe testy ............................................................................................................255Usuwanie problemów....................................................................................................256Dostępność narzędzia ....................................................................................................258Ocenianie projektu.........................................................................................................259

�� 3��&��.��4�&��& �(�Porównanie procedur Sub i Function...................................................................................261Dlaczego tworzy się funkcje niestandardowe?....................................................................261Pierwszy przykład procedury Function ...............................................................................262

Funkcja niestandardowa ................................................................................................262Zastosowanie funkcji w arkuszu....................................................................................263Zastosowanie funkcji w procedurze języka VBA .........................................................264Analiza funkcji niestandardowej ...................................................................................264

Procedury Function..............................................................................................................265Deklarowanie funkcji ....................................................................................................265Zasięg funkcji ................................................................................................................266Wykonywanie procedur Function .................................................................................267

Argumenty procedury Function...........................................................................................268Przykłady funkcji .................................................................................................................268

Funkcja pozbawiona argumentów.................................................................................269Kolejna funkcja pozbawiona argumentów ....................................................................270Funkcja z jednym argumentem......................................................................................270Funkcja z dwoma argumentami.....................................................................................273Funkcja pobierająca tablicę jako argument ...................................................................274Funkcja używająca opcjonalnych argumentów.............................................................274Funkcja zwracająca tablicę języka VBA.......................................................................276Funkcja zwracająca wartość błędu ................................................................................278Funkcja o nieokreślonej liczbie argumentów ................................................................280

Emulowanie funkcji SUMA Excela ....................................................................................280Funkcje wykrywające i usuwające błędy.............................................................................283Okno dialogowe Wstawianie funkcji...................................................................................284

Definiowanie kategorii funkcji ......................................................................................285Dodanie opisu funkcji....................................................................................................286

Dodatki przechowujące funkcje niestandardowe ................................................................287Interfejs API systemu Windows ..........................................................................................287

Przykłady zastosowania funkcji interfejsu API systemu Windows ..............................288Identyfikacja katalogu systemu Windows.....................................................................288Wykrywanie wciśnięcia klawisza Shift .........................................................................289Dodatkowe informacje na temat funkcji interfejsu API................................................290

Spis treści 9

�� #��#��#�.��"��&��-��#��01� ��Przetwarzanie zakresów.......................................................................................................291

Kopiowanie zakresu ......................................................................................................292Przenoszenie zakresu.....................................................................................................293Kopiowanie zakresu o zmiennej wielkości ...................................................................293Zaznaczanie różnego typu zakresów i identyfikowanie ich ..........................................294Wprowadzanie wartości do komórki.............................................................................296Wprowadzanie wartości do następnej pustej komórki ..................................................297Wstrzymywanie wykonywania makraw celu pobrania zakresu zaznaczonego przez użytkownika.......................................298

Zliczanie zaznaczonych komórek..................................................................................299Określanie typu zaznaczonego zakresu .........................................................................300Wydajne przetwarzanie komórek zaznaczonego zakresu przy użyciu pętli..................302Usuwanie wszystkich pustych wierszy..........................................................................303Określanie, czy zakres zawiera się w innym zakresie ...................................................304Określanie typu danych zawartych w komórce.............................................................304Odczytywanie i zapisywanie zakresów .........................................................................305Lepsza metoda zapisywania zakresu .............................................................................306Przenoszenie zawartości tablic jednowymiarowych .....................................................308Przenoszenie zawartości zakresu do tablicy typu Variant.............................................308Zaznaczanie maksymalnej wartości zakresu .................................................................309Zaznaczanie wszystkich komórek określonego formatu ...............................................310

Przetwarzanie skoroszytów i arkuszy ..................................................................................312Zapisywanie wszystkich skoroszytów...........................................................................312Zapisywanie i zamykanie wszystkich skoroszytów ......................................................312Korzystanie z właściwości skoroszytu ..........................................................................312Synchronizowanie arkuszy ............................................................................................313

Metody programowania w języku VBA..............................................................................314Przełączanie wartości właściwości typu logicznego .....................................................314Określanie liczby drukowanych stron ...........................................................................315Wyświetlanie daty i czasu .............................................................................................315Pobieranie listy czcionek ...............................................................................................317Sortowanie tablicy .........................................................................................................318Przetwarzanie grupy plików ..........................................................................................319

Funkcje przydatne w procedurach języka VBA ..................................................................320Funkcja FileExists .........................................................................................................321Funkcja FileNameOnly..................................................................................................321Funkcja PathExists ........................................................................................................321Funkcja RangeNameExists............................................................................................322Funkcja SheetExists.......................................................................................................322Funkcja WorkbookIsOpen.............................................................................................322Pobieranie wartości z zamkniętego skoroszytu .............................................................322

Funkcje przydatne w formułach arkusza .............................................................................324Funkcje zwracające informacje o formatowaniu komórki ............................................324Wyświetlanie daty w trakcie zapisywania lub drukowania pliku..................................325Obiekty nadrzędne.........................................................................................................326Zliczanie komórek, których wartości zawierają się pomiędzy dwoma wartościami ....327Zliczanie widocznych komórek zakresu........................................................................328Określanie ostatniej niepustej komórki kolumny lub wiersza.......................................328Czy łańcuch jest zgodny z wzorcem?............................................................................330Wydzielanie n-tego elementu łańcucha.........................................................................331Funkcja wielofunkcyjna ................................................................................................332Funkcja SHEETOFFSET ..............................................................................................333Zwracanie maksymalnej wartości ze wszystkich arkuszy.............................................334


Zwracanie tablicy zawierającej unikatowe, losowo uporządkowane liczby całkowite ....335Porządkowanie zakresu w losowy sposób.....................................................................336

Funkcje interfejsu API systemu Windows...........................................................................338Określanie skojarzeń plików .........................................................................................338Określenie informacji dotyczących domyślnej drukarki ...............................................339Określenie aktualnej rozdzielczości karty graficznej ....................................................340Dodanie dźwięku do aplikacji .......................................................................................340Odczytywanie zawartości rejestru systemu Windows i zapisywanie w nim danych ....342

��%� ( �� $�)��*�� "+,

�� &��#�&��#��&��&��&��#�5��&��"��#�5 )/Okno wprowadzania danych................................................................................................347

Funkcja InputBox języka VBA .....................................................................................347Metoda InputBox Excela ...............................................................................................349

Okno komunikatu — funkcja MsgBox języka VBA...........................................................351Metoda GetOpenFilename Excela .......................................................................................354Metoda GetSaveAsFilename Excela ...................................................................................357Okno wybierania katalogu ...................................................................................................357

Wybieranie katalogu przy użyciu funkcji interfejsu API systemu Windows................358Wybieranie katalogu przy użyciu obiektu FileDialog...................................................360

Wyświetlanie wbudowanych okien dialogowych Excela....................................................360Zastosowanie zbioru Dialogs.........................................................................................361Dodatkowe informacje na temat wbudowanych okien dialogowych............................362Zastosowanie argumentów z wbudowanymi oknami dialogowymi..............................363Bezpośrednie wybieranie pozycji menu ........................................................................363

�� $.��&��'��#�6��4�� (+Wstawianie nowego formularza UserForm .........................................................................365Dodawanie kontrolek do formularza UserForm ..................................................................366Kontrolki okna Toolbox.......................................................................................................367

Kontrolka CheckBox .....................................................................................................367Kontrolka ComboBox....................................................................................................367Kontrolka CommandButton ..........................................................................................367Kontrolka Frame............................................................................................................367Kontrolka Image ............................................................................................................369Kontrolka Label.............................................................................................................369Kontrolka ListBox .........................................................................................................369Kontrolka MultiPage .....................................................................................................369Kontrolka OptionButton ................................................................................................369Kontrolka RefEdit..........................................................................................................370Kontrolka ScrollBar.......................................................................................................370Kontrolka SpinButton....................................................................................................370Kontrolka TabStrip ........................................................................................................370Kontrolka TextBox ........................................................................................................370Kontrolka ToggleButton................................................................................................370

Modyfikowanie kontrolek formularza UserForm................................................................370Modyfikowanie właściwości kontrolki................................................................................371

Zastosowanie okna Properties .......................................................................................372Wspólne właściwości ....................................................................................................373Zdobywanie dodatkowych informacji o właściwościach..............................................373Uwzględnienie wymagań użytkowników preferujących korzystanie z klawiatury ......373

Spis treści 11

Wyświetlanie i zamykanie formularzy UserForm ...............................................................375Wyświetlanie formularza UserForm .............................................................................375Zamykanie formularza UserForm .................................................................................377Procedury obsługi zdarzeń.............................................................................................378

Przykład tworzenia formularza UserForm...........................................................................378Tworzenie formularza UserForm ..................................................................................378Pisanie kodu źródłowego procedury wyświetlającej okno dialogowe ..........................381Testowanie okna dialogowego ......................................................................................381Dodawanie procedur obsługi zdarzeń............................................................................382Sprawdzanie poprawności danych.................................................................................384Ukończenie tworzenia okna dialogowego.....................................................................384

Zdarzenia powiązane z formularzem UserForm..................................................................385Zdobywanie informacji na temat zdarzeń .....................................................................385Zdarzenia formularza UserForm ...................................................................................386Zdarzenia związane z kontrolką SpinButton .................................................................386Współpraca kontrolki SpinButton z kontrolką TextBox ...............................................388

Odwoływanie się do kontrolek formularza UserForm ........................................................390Dostosowywanie okna Toolbox do własnych wymagań.....................................................391

Modyfikacja ikon lub tekstu podpowiedzi ....................................................................391Dodawanie nowych zakładek ........................................................................................391Dostosowywanie lub łączenie kontrolek ......................................................................392Dodawanie nowych kontrolek ActiveX ........................................................................392

Tworzenie szablonów formularzy UserForm ......................................................................393Lista kontrolna związana z tworzeniem formularzy UserForm...........................................394

��)� ��#��#�'��#�6��4�� +Tworzenie formularza UserForm pełniącego funkcję menu ...............................................395

Zastosowanie w formularzu UserForm kontrolek CommandButton ............................395Zastosowanie w formularzu UserForm kontrolki ListBox............................................396

Zaznaczanie zakresów przy użyciu formularza UserForm..................................................397Tworzenie okna powitalnego...............................................................................................398Wyłączanie przycisku Zamknij formularza UserForm........................................................400Zmiana wielkości formularza UserForm .............................................................................401Powiększanie i przewijanie arkusza przy użyciu formularza UserForm.............................402Zastosowania kontrolki ListBox..........................................................................................404

Kontrolka ListBox .........................................................................................................404Umieszczanie pozycji w kontrolce ListBox ..................................................................405Identyfikowanie zaznaczonej pozycji............................................................................408Identyfikowanie wielu zaznaczonych pozycji kontrolki ListBox .................................409Wiele list w jednej kontrolce ListBox ...........................................................................410Przenoszenie pozycji kontrolki ListBox........................................................................410Przemieszczanie pozycji kontrolki ListBox ..................................................................412Stosowanie wielokolumnowych kontrolek ListBox......................................................413Zastosowanie kontrolki ListBox do wybierania wierszy arkusza .................................415Uaktywnianie arkusza za pomocą kontrolki ListBox....................................................417

Zastosowania kontrolki MultiPage ......................................................................................418

��+� 2��&��&��5&��#��&��'��#�6��4�� )��Wyświetlanie wskaźnika postępu zadania ...........................................................................421

Tworzenie samodzielnego wskaźnika postępu zadania.................................................422Wyświetlanie wskaźnika postępu zadania za pomocą kontrolki MultiPage .................424Wyświetlanie wskaźnika postępu zadania bez korzystania z kontrolki MultiPage.......426

Kreatory — interaktywne sekwencje okien dialogowych ...................................................427Konfigurowanie kontrolki MultiPage w celu utworzenia kreatora ...............................428Dodawanie przycisków do formularza UserForm kreatora...........................................428


Programowanie przycisków kreatora ............................................................................429Zależności programowe w kreatorach...........................................................................430Wykonywanie zadań za pomocą kreatorów ..................................................................431

Emulacja funkcji MsgBox ...................................................................................................432Emulacja funkcji MsgBox: kod funkcji MyMsgBox ....................................................433Jak działa funkcja emulująca MsgBox ..........................................................................434Wykorzystanie funkcji MyMsgBox ..............................................................................435

Niemodalne okna dialogowe ...............................................................................................435Obsługa wielu przycisków formularza UserFormza pomocą jednej procedury obsługi zdarzeń...................................................................438

Wybór koloru za pomocą formularza UserForm.................................................................441Wyświetlanie wykresów w formularzach UserForm...........................................................442

Metoda 1. zapisanie wykresu do pliku ..........................................................................443Metoda 2. zastosowanie kontrolki ChartSpace z pakietu OWC....................................444

Wyświetlanie arkuszy w formularzach UserForm...............................................................446Udostępnianie kontrolki Spreadsheet ............................................................................447Dodawanie kontrolki Spreadsheet w formularzu UserForm .........................................447Prosty przykład zastosowania kontrolki Spreadsheetkomponentów sieci WWW pakietu Office ................................................................447

UserForm Deluxe: ulepszony formularz danych .................................................................450Opis ulepszonego formularza danych............................................................................450Instalacja dodatku — ulepszonego formularza danych.................................................450Wykorzystanie ulepszonego formularza danych...........................................................451

��%� ( � �� -��.� �� +,"

��(� 3��&��&��-��#��01� )++Wprowadzenie .....................................................................................................................455Zastosowanie języka VBA do tworzenia narzędzi ..............................................................456Co decyduje o przydatności narzędzia?...............................................................................456Operacje tekstowe — anatomia narzędzia...........................................................................457

Podstawy tworzenia narzędzia Operacje tekstowe........................................................458Określenie wymagań dla narzędzia Operacje tekstowe ................................................458Jak działa narzędzie Operacje tekstowe?.......................................................................458Skoroszyt narzędzia Operacje tekstowe ........................................................................459Formularz UserForm dla narzędzia Operacje tekstowe ................................................459Moduł kodu ThisWorkbook ..........................................................................................461Moduł VBA Module1....................................................................................................462Moduł formularza UserForm1.......................................................................................463Poprawa wydajności narzędzia Operacje tekstowe .......................................................464Zapisywanie ustawień narzędzia Operacje tekstowe.....................................................465Implementacja procedury Cofnij ...................................................................................466Ocena realizacji projektu ...............................................................................................468Działanie narzędzia Operacje tekstowe.........................................................................469

Dodatkowe informacje na temat narzędzi Excela................................................................469

��/� 3�%��.��& )/�Wykorzystanie języka VBA do tworzenia tabel przestawnych...........................................471

Tworzenie tabel przestawnych ......................................................................................471Analiza zarejestrowanego kodu tworzenia tabeli przestawnej ......................................473Ulepszanie zarejestrowanego kodu tworzenia tabeli przestawnej.................................473

Złożone tabele przestawne...................................................................................................474Dane dla złożonej tabeli przestawnej ............................................................................475Kod tworzący tabelę przestawną ...................................................................................475Jak działa złożona tabela przestawna?...........................................................................477

Spis treści 13

Tworzenie tabel przestawnych na podstawie zewnętrznej bazy danych .............................478Jednoczesne tworzenie wielu tabel przestawnych ...............................................................479Modyfikowanie tabel przestawnych ....................................................................................482

��*� $#��# )*+Podstawowe wiadomości o wykresach................................................................................485

Położenie wykresu.........................................................................................................485Model obiektu Chart......................................................................................................486

Rejestrowanie makr dotyczących wykresów.......................................................................487Zarejestrowany kod makra tworzącego wykres ............................................................487Ulepszanie zarejestrowanego kodu tworzenia wykresu ................................................489

Popularne techniki języka VBA dotyczące wykresów ........................................................489Wykorzystanie VBA do uaktywnienia wykresu ...........................................................490Wykorzystanie VBA do deaktywacji wykresu..............................................................491Sprawdzanie, czy wykres uaktywniono.........................................................................492Usuwanie elementów z kolekcji ChartObjects lub Charts ............................................492Formatowanie wykresów za pomocą VBA...................................................................493Przetwarzanie wszystkich wykresów w pętli ................................................................494Zmiana rozmiarów i wyrównywanie obiektów ChartObject ........................................494

Inne techniki przetwarzania wykresów................................................................................496Zastosowanie nazw w formule SERIE ..........................................................................496Zastosowanie języka VBA w celu określenia danych wykorzystywanych na wykresie.................................................................................497

Zastosowanie języka VBA w celu określenia zakresu danychwykorzystywanych na wykresie.................................................................................500

Wykorzystanie VBA do wyświetlania dowolnych etykiet danych na wykresie ...........503Wyświetlanie wykresu w oknie UserForm....................................................................505

Zdarzenia związane z wykresami ........................................................................................507Przykład wykorzystania zdarzeń związanych z wykresami ..........................................507Obsługa zdarzeń dla wykresów wbudowanych.............................................................511Zastosowanie zdarzeń dla wykresów wbudowanych ....................................................512

Jak ułatwić sobie pracę z wykresami? .................................................................................514Drukowanie wbudowanych wykresów..........................................................................515Tworzenie „martwych” wykresów ................................................................................515Wykorzystanie zdarzenia MouseOver do wyświetlania tekstu .....................................517Wykresy animowane .....................................................................................................518Tworzenie wykresu krzywych hipocykloidalnych ........................................................519Tworzenie wykresu-zegara............................................................................................520

Co można zrobić z wykresami bez użycia makr?................................................................522Sterowanie seriami danych za pomocą automatycznego filtrowania............................522Zapisywanie wielu wykresów w arkuszu-wykresie ......................................................523Tworzenie samorozszerzającego się wykresu ...............................................................524Tworzenie interaktywnego wykresu..............................................................................530

�� %��"��7 + +Typy zdarzeń, które można monitorować w Excelu............................................................535Najważniejsze informacje o zdarzeniach.............................................................................536

Sekwencje zdarzeń ........................................................................................................536Gdzie należy umieścić procedury obsługi zdarzeń?......................................................537Wyłączanie obsługi zdarzeń ..........................................................................................538Wprowadzanie kodu procedury obsługi zdarzeń...........................................................539Procedury obsługi zdarzeń z argumentami....................................................................540

Zdarzenia poziomu skoroszytu ............................................................................................541Zdarzenie Open..............................................................................................................541Zdarzenie Activate.........................................................................................................543


Zdarzenie SheetActivate................................................................................................543Zdarzenie NewSheet......................................................................................................544Zdarzenie BeforeSave....................................................................................................544Zdarzenie Deactivate .....................................................................................................544Zdarzenie BeforePrint....................................................................................................545Zdarzenie BeforeClose ..................................................................................................546

Zdarzenia poziomu arkusza .................................................................................................547Zdarzenie Change ..........................................................................................................547Monitorowanie modyfikacji w wybranym zakresie komórek.......................................549Zdarzenie SelectionChange ...........................................................................................551Zdarzenie BeforeRightClick..........................................................................................552

Zdarzenia dotyczące wykresów ...........................................................................................553Zdarzenia dotyczące aplikacji..............................................................................................553

Włączenie obsługi zdarzeń poziomu aplikacji ..............................................................554Sprawdzanie, czy skoroszyt jest otwarty.......................................................................556Monitorowanie zdarzeń poziomu aplikacji ...................................................................557

Zdarzenia dotyczące formularzy UserForm ........................................................................558Zdarzenia niezwiązane z obiektami .....................................................................................559

Zdarzenie OnTime.........................................................................................................560Zdarzenie OnKey...........................................................................................................561

�� 8&��-��&&#��.��-�� +( Uruchamianie innych aplikacji z poziomu Excela ..............................................................563

Zastosowanie funkcji Shell języka VBA.......................................................................563Zastosowanie funkcji Windows API ShellExecute .......................................................566

Uaktywnianie aplikacji z poziomu Excela...........................................................................567Wykorzystanie instrukcji AppActivate .........................................................................567Uaktywnianie aplikacji pakietu Microsoft Office .........................................................567

Uruchamianie okien dialogowych Panelu sterowania .........................................................568Wykorzystanie automatyzacji..............................................................................................569

Działania z obiektami innych aplikacji z wykorzystaniem automatyzacji....................569Wczesne i późne wiązanie.............................................................................................570Prosty przykład późnego wiązania ................................................................................572Zarządzanie Wordem z poziomu Excela .......................................................................573Zarządzanie Excelem z poziomu innej aplikacji ...........................................................576

Wysyłanie spersonalizowanych wiadomości e-mail z wykorzystaniem Outlooka .............577Działania z obiektami danych ActiveX (ADO)...................................................................579Wysyłanie wiadomości e-mail z załącznikami z poziomu Excela ......................................581Zastosowanie metody SendKeys .........................................................................................582

�� 3��&��#��#��&��9� +*+Czym są dodatki?.................................................................................................................585

Porównanie dodatku ze standardowym skoroszytem....................................................585Po co tworzy się dodatki?..............................................................................................586

Menedżer dodatków Excela.................................................................................................587Tworzenie dodatków............................................................................................................588Przykładowy dodatek...........................................................................................................589

Konfiguracja skoroszytu dla przykładowego dodatku ..................................................589Testowanie skoroszytu użytego do utworzenia przykładowego dodatku .....................589Wprowadzanie opisu dla przykładowego dodatku........................................................590Utworzenie dodatku.......................................................................................................590Instalowanie dodatku.....................................................................................................592Dystrybucja dodatków...................................................................................................592Modyfikowanie dodatku................................................................................................592

Spis treści 15

Porównanie plików XLA i XLS ..........................................................................................594Rozmiar i struktura plików XLS i XLA ........................................................................594Pliki XLA — przynależność do kolekcji z poziomu VBA ...........................................594Widoczność plików XLS i XLA ...................................................................................594Arkusze i wykresy w plikach XLS i XLA.....................................................................595Dostęp do procedur VBA w dodatku ............................................................................596

Przykłady przetwarzania dodatków za pomocą kodu VBA ................................................597Kolekcja AddIns ............................................................................................................598Właściwości obiektu AddIn...........................................................................................599Zdarzenia związane z obiektami Addin.........................................................................602

Optymalizacja wydajności dodatków ..................................................................................602Maksymalizacja szybkości kodu dodatków ..................................................................602Kontrolowanie rozmiaru pliku dodatku.........................................................................603

Problemy z dodatkami .........................................................................................................604Zapewnienie zainstalowania dodatku............................................................................604Odwoływanie się do innych plików z poziomu dodatku...............................................605Wykrywanie właściwej wersji Excela dla dodatku .......................................................605

��%�� /�� 0!�

�� 3��&��.��9��&�� (��Paski narzędzi ......................................................................................................................609Działania z paskami narzędzi ..............................................................................................609Jak Excel zarządza paskami narzędzi? ................................................................................610

Przechowywanie pasków narzędzi ................................................................................610Problemy z działaniem pasków narzędzi.......................................................................611

Ręczne dostosowywanie pasków narzędzi ..........................................................................611Tryb dostosowywania pasków narzędzi ........................................................................612Dystrybucja pasków narzędzi użytkownika ..................................................................615

Działania z kolekcją CommandBars....................................................................................617Rodzaje pasków narzędzi ..............................................................................................617Wyświetlenie wszystkich obiektów CommandBar .......................................................617Tworzenie pasków narzędzi ..........................................................................................618Odwoływanie się do pasków narzędzi w VBA .............................................................619Usuwanie paska narzędzi za pomocą kodu VBA..........................................................620Właściwości pasków narzędzi .......................................................................................620Odwoływanie się do kontrolek na pasku narzędzi ........................................................625Wyszczególnienie kontrolek na pasku narzędzi ............................................................626Wyświetlanie wszystkich kontrolek na wszystkich paskach narzędzi ..........................627Dodawanie kontrolki na pasku narzędzi........................................................................628Usuwanie kontrolki z paska narzędzi ............................................................................628Właściwości kontrolek pasków narzędzi.......................................................................629

�� 3��&��&� ( �Pasek menu w Excelu ..........................................................................................................639Modyfikowanie menu Excela przez użytkownika...............................................................640

Pojęcia związane z systemem menu Excela ..................................................................640Usuwanie elementów menu Excela...............................................................................641Dodawanie elementów menu Excela.............................................................................641Modyfikacja elementów menu Excela ..........................................................................642

Wykorzystanie języka VBA do dostosowywania menu w Excelu......................................642Wyświetlanie informacji o menu Excela.......................................................................643Dodawanie nowego menu na pasku menu ....................................................................644Usuwanie menu z paska menu.......................................................................................646Dodawanie pozycji menu ..............................................................................................647


Wyświetlanie klawisza skrótu wraz z nazwą polecenia ................................................650Odtwarzanie menu, które zostało usunięte ....................................................................651

Wykorzystanie zdarzeń do programowania menu...............................................................652Automatyczne dodawanie i usuwanie menu .................................................................652Dezaktywacja lub ukrywanie menu...............................................................................653Działania z poleceniami menu powiązanymi z polami wyboru ....................................654

Tworzenie menu użytkownika — łatwy sposób..................................................................657Utworzenie zastępczego paska menu arkusza kalkulacyjnego............................................659Operacje z menu podręcznymi ............................................................................................660

Dodawanie pozycji do menu podręcznych....................................................................662Usuwanie pozycji z menu podręcznych ........................................................................663Dezaktywacja pozycji menu podręcznych ....................................................................663Dezaktywacja menu podręcznych .................................................................................663Przywracanie ustawień menu podręcznych...................................................................664Tworzenie nowych menu podręcznych .........................................................................664

��)� 3��&��#��9��.��#��.��-��5 ((/Dlaczego należy tworzyć systemy pomocy w aplikacjach? ................................................667Systemy pomocy wykorzystujące komponenty Excela.......................................................668

Wykorzystanie komentarzy w celu tworzenia systemów pomocy................................668Wykorzystanie pól tekstowych w celu utworzenia systemu pomocy ...........................670Wykorzystanie arkusza do wyświetlania tekstu pomocy ..............................................670Wyświetlanie pomocy w oknie UserForm ....................................................................671Wykorzystanie asystenta pakietu Office do wyświetlania pomocy ..............................674

Symulacja właściwości Co to jest? za pomocą formularza UserForm................................676Wykorzystanie systemu HTML Help ..................................................................................677Powiązanie pliku pomocy z aplikacją..................................................................................678Wiązanie tematów pomocy z funkcjami VBA ....................................................................679Inne sposoby wyświetlania plików pomocy HTML Help ...................................................680

Wykorzystanie metody Help .........................................................................................680Wyświetlanie pomocy z okna informacyjnego .............................................................680Wyświetlanie pomocy z okna InputBox........................................................................681

��+� 3��&��.��-��#"��&#�5��:#��&�� (* Co to jest aplikacja user-oriented?.......................................................................................683Przykładowa aplikacja — Kreator amortyzacji pożyczek ...................................................683

Obsługa Kreatora amortyzacji pożyczek.......................................................................683Struktura skoroszytu Kreatora amortyzacji pożyczek ...................................................685Jak działa Kreator amortyzacji pożyczek? ....................................................................686Potencjalne usprawnienia Kreatora amortyzacji pożyczek ...........................................690

Wskazówki dotyczące projektowania aplikacji ...................................................................690

��%�� . �� 01"

��(� ��%��#��"��&�;�� (�+Co to jest zgodność? ............................................................................................................695Rodzaje problemów zgodności ............................................................................................696Obsługiwane formaty plików Excela...................................................................................697Kiedy trzeba unikać nowych właściwości? .........................................................................698Czy aplikacja będzie działać na komputerach Macintosh?..................................................698Tworzenie aplikacji dla wielu wersji narodowych ..............................................................699

Aplikacje obsługujące wiele języków ...........................................................................701Obsługa języka w kodzie VBA .....................................................................................702Wykorzystanie właściwości lokalnych..........................................................................702Identyfikacja ustawień systemu.....................................................................................703Ustawienia daty i godziny .............................................................................................705

Spis treści 17

��/� �.��-�&��.��5��#��&#��&��.��01� /�/Wykonywanie popularnych operacji na plikach..................................................................707

Wykorzystanie poleceń języka VBA do wykonywania operacji na plikach.................707Wykorzystanie obiektu FileSearch................................................................................710Wykorzystanie obiektu FileSystemObject ....................................................................711Wyszukiwanie plików zawierających określony tekst ..................................................713

Operacje z plikami tekstowymi ...........................................................................................714Otwieranie plików tekstowych ......................................................................................714Czytanie plików tekstowych..........................................................................................715Zapisywanie danych do plików tekstowych..................................................................716Uzyskanie numeru pliku................................................................................................716Określanie lub ustawianie pozycji w pliku....................................................................716Instrukcje pozwalające na odczytywanie i zapisywanie plików ...................................716

Przykłady wykonywania operacji na plikach ......................................................................717Importowanie danych z pliku tekstowego .....................................................................717Eksportowanie zakresu do pliku tekstowego.................................................................718Importowanie pliku tekstowego do zakresu ..................................................................719Rejestrowanie wykorzystania Excela ............................................................................720Filtrowanie pliku tekstowego ........................................................................................721Importowanie więcej niż 256 kolumn danych do skoroszytu .......................................721Eksportowanie zakresu do pliku HTML .......................................................................723Eksportowanie zakresu do pliku XLM..........................................................................726

��*� �.��-�&��.�&&��5�-��#��01� /��Podstawowe informacje o środowisku IDE.........................................................................729Model obiektów środowiska IDE ........................................................................................730

Kolekcja VBProjects .....................................................................................................731Wyświetlanie wszystkich komponentów projektu VBA.....................................................733Zastępowanie modułu uaktualnioną wersją .........................................................................734Wykorzystanie języka VBA do generowania kodu VBA....................................................736Wykorzystywanie kodu VBA do umieszczenia kontrolek w formularzu UserFormw fazie projektowania.......................................................................................................738Operacje z formularzami UserForm w fazie projektowania i wykonania.....................738Dodanie 100 przycisków CommandButton w fazie projektowania ..............................739

Programowe tworzenie formularzy UserForm ....................................................................741Prosty przykład formularza UserForm ..........................................................................741Skomplikowany przykład dynamicznego formularza UserForm..................................743

�� <��#�� /)�Czym jest moduł klasy?.......................................................................................................749Przykład: utworzenie klasy NumLock.................................................................................750

Wstawianie modułu klasy..............................................................................................750Dodawanie kodu VBA do modułu klasy.......................................................................751Wykorzystanie klasy NumLock ....................................................................................753

Dodatkowe informacje na temat modułów klas ..................................................................754Nadawanie nazwy klasie obiektów................................................................................754Programowanie właściwości obiektów .........................................................................754Programowanie metod obiektów...................................................................................756Zdarzenia definiowane w module klasy ........................................................................757

Przykład: klasa CSVFileClass .............................................................................................757Zmienne poziomu modułu dla klasy CSVFileClass......................................................757Definicje właściwości klasy CSVFileClass...................................................................758Definicje metod klasy CSVFileClass ............................................................................758Wykorzystanie obiektów CSVFileClass .......................................................................760


�� ,��&�.#��&��&��.��"��&�� /( Pytania dotyczące Excela.....................................................................................................764Pytania dotyczące edytora Visual Basic ..............................................................................768Pytania dotyczące procedur .................................................................................................771Pytania dotyczące funkcji ....................................................................................................776Pytania dotyczące obiektów, właściwości, metod i zdarzeń................................................779Pytania dotyczące formularzy UserForm ............................................................................788Pytania dotyczące dodatków................................................................................................793Pytania dotyczące pasków poleceń......................................................................................795

2 �� 11

=�� 2��%#��&��&��#�� *��Pomoc techniczna Microsoftu .............................................................................................801

Opcje pomocy technicznej.............................................................................................801Baza wiedzy firmy Microsoft ........................................................................................802Macierzysta strona programu Microsoft Excel .............................................................802Narzędzia pakietu Microsoft Office w internecie..........................................................802

Internetowe grupy dyskusyjne .............................................................................................802Grupy dyskusyjne poświęcone arkuszom kalkulacyjnym.............................................803Grupy dyskusyjne Microsoftu .......................................................................................803Wyszukiwanie informacji w grupach dyskusyjnych .....................................................804

Witryny WWW....................................................................................................................805Strona Spreadsheet ........................................................................................................805Pearson Software Consulting.........................................................................................805Strona o Excelu Stephena Bullena.................................................................................805Strony o Excelu Davida McRitchie ...............................................................................806Strona o Excelu Jona Peltiera ........................................................................................806Mr. Excel .......................................................................................................................806Najczęściej zadawane pytania .......................................................................................806

=��1� 8&��-��'�&��-�01� *�/Wywoływanie funkcji Excela w instrukcjach VBA............................................................810

=��,� >��#�%��9��01� *�/

=��=� 2��;?�.�#�#�,=@��< *��Wymagania systemowe .......................................................................................................821Korzystanie z płyty CD-ROM w systemie Windows..........................................................821Co znajduje się na płycie CD-ROM ....................................................................................822

Materiały utworzone przez autora tej książki................................................................822Aplikacje........................................................................................................................834

Rozwiązywanie problemów.................................................................................................835

�� * /

Rozdział 17.

�� Według niektórych użytkowników Excela tabele przestawne są jego najbardziej inno-wacyjną i oferującą największe możliwości właściwością. Jest to także unikatowa cechaExcela (żaden inny arkusz kalkulacyjny nią nie dysponuje). Po raz pierwszy pojawiłasię w Excelu 5.

Ten rozdział nie jest wprowadzeniem w tematykę tabel przestawnych. Zakładam, żeczytelnicy znają tę właściwość i potrafią ręcznie tworzyć i modyfikować tabele. Jakwiadomo, utworzenie tabeli przestawnej na podstawie danych z bazy danych lub listyumożliwia wykonywanie podsumowań danych w sposób, który bez ich zastosowanianie byłby możliwy. Poza tym jest to sposób niezwykle szybki. Dodatkowo tabele prze-stawne można tworzyć i modyfikować za pomocą kodu w języku VBA.

Tabele przestawne Excela znacznie ulepszono w Excelu 2000. Wykorzystano tamwydajniejsze metody buforowania danych, a także wprowadzono obsługę wykresówpowiązanych z tabelami przestawnymi. Z tego powodu pewna część materiałuzawartego w niniejszym rozdziale nie dotyczy Excela 97 lub wersji wcześniejszych.

��

W tym podrozdziale zaprezentowano prosty przykład wykorzystania języka VBA w celuutworzenia tabeli przestawnej. Baza danych, pokazana na rysunku 17.1, zawiera czterypola: ��, ��, �� i ��. Każdy rekord opisuje wielkość sprzedażyjednego reprezentanta handlowego w określonym miesiącu.

��

Na rysunku 17.2 pokazano tabelę przestawną utworzoną na podstawie danych. Wyko-nuje ona podsumowania sprzedaży według reprezentantów handlowych oraz miesięcy.Zawiera następujące pola:

� �� — pole strony w tabeli przestawnej;

� �� — pole wiersza w tabeli przestawnej;

� �� — pole kolumny w tabeli przestawnej;

� �� — pole danych w tabeli przestawnej, w którym wykorzystano funkcję ��.

472 Część V � Zaawansowane techniki programowania

��Z tej prostej bazydanych łatwo możnautworzyć tabelęprzestawną

��Tabela przestawnautworzonana podstawie danychzamieszczonychna rysunku 17.1

Zanim utworzyłem tę tabelę przestawną, włączyłem rejestrator makr. Wygenerowanykod zaprezentowano poniżej:

�� !��"��##�$��%&'�()*+��,�$��+��()��-�.��!�(��/!0��$��!�� %��%��+��()��,�$��%��1��()�%��'�-��,�$��+�2��3��()* ��%��3��4

��"�� %��-��#�$��%��+��()��"��!��/,��"��!��/,��

��"��"�� %��%��'�-��$�� 5��#��6��)�* ��5��#�� )��

Rozdział 17. � Tabele przestawne 473

7�#��"

��"��"�� %��%��'�-��$�� 5��#��'8��#��6��)�*��5��#�� )��7�#��"

��"��"�� %��%��'�-��$�� 5��#��9��6��)�*!��5��#�� )��7�#��"��"�� %��%��'�-��$��##+��5��#��"�� %��%��'�-��$�� 5��#��'�-�#�:��,��-��'�-�#�:�,�*��7�#��

Kod wygenerowany przez rejestrator makr zależy od sposobu, w jaki utworzyliśmy ta-belę przestawną. W przykładzie utworzyłem tabelę przestawną, która była pusta, dopókinie przeciągnąłem pola z paska narzędzi Tabela przestawna. Alternatywną metodą jestkliknięcie przycisku Układ w drugim kroku kreatora Tabela przestawna i zdefiniowanieukładu tabeli przestawnej przed jej utworzeniem.

Można oczywiście wykonać zarejestrowane makro, co spowoduje utworzenie kolejnej,identycznej tabeli przestawnej.

��

Kod języka VBA dotyczący działań z tabelami przestawnymi może się wydawać niejasny.Aby zarejestrowane makro nabrało sensu, trzeba poznać kilka obiektów (opis każdegoz nich znajduje się w pomocy online):

� �� — zbiór obiektów �� obiektu ��;

� �� — zbiór obiektów �� obiektu �� ;

� �� — zbiór pól obiektu ��;

� �� ! — zbiór danych dla pola określonej kategorii;

� �� — metoda obiektu �� tworząca tabelę przestawnąna podstawie danych w buforze tabeli przestawnej;

� �� — metoda obiektu �� tworząca tabelę przestawną.Jak się przekonamy w następnym punkcie, korzystanie z tej metody nie jestkonieczne.

� ��

Podobnie jak w przypadku większości zarejestrowanych makr, zaprezentowany wcześniejprzykład nie jest tak wydajny, jak mógłby być. Można go uprościć i uczynić bardziejzrozumiałym. Kod z listingu 17.1 pozwoli utworzyć identyczną tabelę przestawną.


�� Lepszy sposób generowania tabeli przestawnej za pomocą kodu VBA

��!�� %��+�� %!��"�� !��"��+�� %�� %��

�� %!��"��)�� !��"��##�$��%&'�()*+��,�$��+��()��!��##��

�� %�)� %!��"��!�� %��$��%��+��()��,�$��%��1��()�%��'�-��

��"� %�� 5��#��6��)�* ��5��#�� 5��#��9��6��)�*!��5��#�� 5��#��'8��#��6��)�*��5��#�� 5��#��'�-�#�:��6��)�*+��5��#��7�#��"7�#��

Procedurę �� uproszczono (co może przyczynić się do zwiększenia jejprzejrzystości), deklarując tylko dwie zmienne obiektowe: �� oraz ��. Zastępująone indeksowane odwołania do pól ��"�� oraz ��"��#�� . Nowy obiekt �� tworzy się za pomocą metody ��, a nowy obiekt�� za pomocą metody �� obiektu ��. W ostatniej sekcjikodu następuje dodanie pól do tabeli przestawnej i określenie lokalizacji wewnątrz niej(pola strony, kolumny, wiersza i danych).

W pierwotnie wygenerowanym makrze znajdował się zakodowany „na twardo” zakresdanych służący do utworzenia obiektu �� (tzn. $��% �&'�&�&(�&)�*$). W pro-cedurze �� tabela przestawna jest tworzona na podstawie bieżącego re-gionu otaczającego komórkę �&. Dzięki temu zyskujemy pewność, że makro będziedziałać poprawnie po wprowadzeniu dodatkowych danych.

Kod byłby bardziej uniwersalny, gdyby w zbiorze ��5��#� zastosowano indeksyzamiast literałów. W ten sposób makro działałoby nawet wtedy, gdy użytkownikzmieniłby nagłówki kolumn. Na przykład bardziej uniwersalny kod zawierałby odwołanie ��5��#�� zamiast ��5��#��. Kod zastosowany w pokazanymprzykładzie najlepiej pasuje do sytuacji, kiedy układ kolumn nie będzie się zmieniać.

Najlepszym sposobem poznania potrzebnych obiektów, metod i właściwości jest jak zwyklezarejestrowanie wykonywanych działań w makrze. Następnie warto przestudiować tematypomocy online, aby zrozumieć sposób użycia poszczególnych elementów. Niemal w każ-dym przypadku zarejestrowane makra wymagają modyfikacji. Można też napisać kod odpoczątku, bez wykorzystywania rejestratora makr, ale to wymaga pewnego doświadczenia.

��

W tym podrozdziale zaprezentuję kod VBA tworzący stosunkowo złożoną tabelę prze-stawną.


��

Na rysunku 17.3 zaprezentowano fragment bazy danych w arkuszu, zbudowanej z 15 840

wierszy zawierających hierarchiczne dane budżetowe firmy. Korporacja składa się z 5

oddziałów, a każdy oddział z 11 wydziałów. W każdym wydziale są cztery kategorie

budżetowe, a każda kategoria zawiera po kilka pozycji. Kwoty budżetu oraz rzeczywiście

wydane środki są zapisane dla każdego z 12 miesięcy.

��Dane z tegoskoroszytu będązestawione w tabeliprzestawnej

Ten przykład jest dostępny na płycie CD-ROM dołączonej do książki.

Na rysunku 17.4. pokazano tabelę przestawną utworzoną na podstawie zaprezentowa-

nych wyżej danych. Warto zwrócić uwagę, że tabela przestawna zawiera obliczane pole

+��,�� oraz cztery obliczane pola -.&, -./, -.) i -.*, zawierające podsumowania

dla poszczególnych kwartałów.

��

Kod VBA z listingu 17.2 tworzy tabelę przestawną.

�� Tworzenie tabeli przestawnej dla wielu oddziałów

��!�� %��+�� %!��"�� !��"��+�� %�� %��

��''��;'#��)�5��


��Tabela przestawnautworzonana podstawie danychz rysunku 17.3

��;��<��-��-%�� -��,��=��6��7��1�*��''��+��'�&��)�5��

��"��-%�� -��+��6��7��>%�4��''��+��'�&��)�%��

��;��-��2��'�-�� %!��"��)�� !��"��##��$��%&'�()*+��,�$��+��()��!��##��

��+#��-��"��##��"��1��)��-%�� -��

��;��-��'�-��'#��2�� %�)� %!��"��!�� %��$��%��+��()�"��-%�� -��,�$��%��1��()�%�� -��?�#:��

��"� % ��+#��'@�� 5��#��A+�B�C��6��)�*��5��#�� 5��#��B7�BD!��6��)�*!E��5��#�� 5��#��6++�B�C��6��)�* ��5��#�� 5��#��?;+F7%��6��)�*+��5��#�� 5��#��7!�A�B�%7��A+�%GB��6��)�*+��5��#

��+#��'��H�:9��#��-��#�"&��!��#5��#��##��6#�"&��,��)?;+F7%I��7!�A�B�%7��A+�%GB�� 5��#��6#�"&��6��)�*+��5��#


��+#��'@��-��&�"�� 5��#��B7�BD!��!��#B��##�$��G��,��)��&JE��J�� 5��#��B7�BD!��!��#B��##�$��G�K�,��)�G��J��J!-�� 5��#��B7�BD!��!��#B��##�$��G�/�,��)�E�'J��J��-�� 5��#��B7�BD!��!��#B��##�$��G�0�,��)� �LJE��J>��

�� -��-��&�"�'-&�� 5��#��B7�BD!�� B��G�� )�0�� 5��#��B7�BD!�� B��G�K�� )�M�� 5��#��B7�BD!�� B��G�/�� )��K�� 5��#��B7�BD!�� B��G�0�� )��N

��#&2��'#'��@�� 5��#��-�?;+F7%��!�'��)��?�#:��-H�� 5��#��-��7!�A�B�%7��A+�%GB��!�'��)��-��-&��&#��-H�� 5��#��-�6#�"&��!�'��)��6#�"&��-H��7�#��"

��''��;'#��)�%��7�#��

�� !

Procedura �� z listingu 17.2 najpierw usuwa arkusz ��% ��# ��.��0, jeżeli taki wcześniej istniał. Następnie tworzy obiekt ��, wstawia nowy

arkusz o nazwie ��% �� .��0 i w końcu tworzy tabelę przestawną. Do tabeli

przestawnej dodawane są następujące pola:

� �,��1 — pole wiersza;

� �� — pole kolumny;

� +��1 — pole strony;

� 2%�� — pole danych;

� ��,. ��3.,�� — pole danych.

Następnie wykorzystano metodę �� zbioru ��%�� w celu utworzenia oblicza-

nego pola +��,��, którego wartość jest wyliczana poprzez odjęcie wartości faktycznie

wydanej kwoty (pole �45�46� ��53 �67��- ) od kwoty budżetu (pole 2�785�). Cztery

obliczane pola służą do wyliczenia kwartalnych podsumowań. Domyślnie obliczane pola

są dodawane po prawej stronie tabeli przestawnej, a zatem potrzebny jest dodatkowy

kod, aby umieścić je obok miesięcy, których dotyczą (np. pole -.� jest umieszczone za

polem ��). Na koniec kod modyfikuje podpisy wyświetlane w tabeli przestawnej (np.

nazwa �%!�3�32�785� jest zastępowana nazwą 2%��39�1:).

Tworząc tę procedurę, najpierw wygenerowałem kod za pomocą rejestratora makr,a następnie go poprawiłem, aby stał się czytelniejszy i wydajniejszy.


��

��

W poprzednim przykładzie źródłem danych był arkusz. Jak wiadomo, Excel umożliwia

wykorzystanie zewnętrznych źródeł danych do tworzenia tabel przestawnych. Przykład

zaprezentowany w tym podrozdziale demonstruje kod VBA potrzebny do utworzenia

tabeli przestawnej na podstawie danych zapisanych w bazie danych Access.

Baza danych Accessa zawiera pojedynczą tabelę, w której umieszczono te samedane, których użyto w poprzednim przykładzie.

Kod z listingu 17.3 tworzy tabelę przestawną. Założono, że plik bazy danych budżet.

mdb jest zapisany w tym samym katalogu, co arkusz.

�� Generowanie tabeli przestawnej na podstawie danych z zewnętrznej bazy danych

��!�� %��5��+?��+�� %!��"�� !��"��+�� %�� %��+��+?5��+��!��+��O��&��

��;��<��-�� -��,��:��6��7��1�*��''��+��'�&��)�5��"�� -��+��6��7��>%�4

��;��-��2��'�-�� %!��"��)�� !��"��##�$��%&'�()*7*��

��P��:��#�'��-&�#��&�"��+?5��)�%"�� "�Q��R��#:��#��

��C�S��"�'H9�-��!��)��6+?!(+�1)��+��T+?O)��Q�+?5��

��'&��O��&��)��7E7!%�U�5�6��?;+F7%�

��"� %!��"��!��)�!��!��#%�*��)�O��&��7�#��"

��+#��-��"��##��"��1��)�� -��

��;��-��'�-�� %�)� %!��"��!�� %��$


��%��+��()�"�� -��,�$��%��1��()�?�#:�� -��

��"� % ��+#��'@�� 5��#��A+�B�C��6��)�*��5��#�� 5��#��B7�BD!��6��)�*!E��5��#�� 5��#��6++�B�C��6��)�* ��5��#�� 5��#��?;+F7%��6��)�*+��5��#�� 5��#��7!�A�B�%7��A+�%GB��6��)�*+��5��#��7�#��"7�#��

Argument ��%��,�� metody �� zbioru �� określono jako ;�5;��.W przykładzie zaprezentowanym w poprzednim podrozdziale (w którym wykorzystano

dane z bazy danych w arkuszu) argument ��%��,�� był typu ;�7�� . Obiekt

�� w celu pobrania danych z pliku zewnętrznego potrzebuje następujących in-

formacji:

� łańcucha połączenia opisującego typ źródła danych oraz nazwę pliku,

� łańcucha zapytania, którym jest instrukcja SQL określająca zwracane rekordy i pola.

W tym przykładzie łańcuch połączenia określa źródło danych ODBC, którym jest plik

Accessa o nazwie budżet.mdb. Łańcuch zapytania określa w tym przypadku całą tabelę

2�785�. Informacje te są przekazywane do obiektu �� poprzez ustawienie właści-

wości �� oraz ��!!��;�. Po zapisaniu danych w buforze tabeli przestawnej

następuje utworzenie tabeli za pomocą metody ��.

SQL jest standardowym językiem służącym do tworzenia zapytań do bazy danych.Więcej informacji można uzyskać w pomocy online. Warto również zakupić książkępoświęconą temu językowi, co pozwoli poznać go dokładniej.

��

W ostatnim przykładzie utworzymy kilka tabel przestawnych, które będą zawierały

podsumowania danych pobranych z ankiety wypełnianej przez klientów. Dane są zapisane

w bazie danych w arkuszu (rysunek 17.5) złożonej ze 100 wierszy. Każdy wiersz zawiera

dane o płci respondenta oraz odpowiedź od 1 do 5 dla każdej z 14 pozycji ankiety.

Na rysunku 17.6 pokazano kilka tabel przestawnych. Dla każdej pozycji ankiety utworzono

dwie tabele przestawne — jedna wyświetla dane w procentach, druga rzeczywiste wartości.

Kod VBA z listingu 17.4 tworzy te tabele.

�� Tworzenie wielu tabel przestawnych na podstawie danych ze złożonej zewnętrznej bazy

danych

�� %�� %��'��#��-&�KM��'�-��&�"��+�� %!��"�� !��"��+�� %�� %��


��Wyniki ankietybędą podsumowanew kilku tabelachprzestawnych

�� Tabele przestawne utworzone za pomocą procedury VBA


+��&�"��"��+��B��1��+��B��,��B��

��''��;'#��)�5��

��;��<��-�� #��,��:��6��7��1�*��''��+��'�&��)�5��"�� #��+��

6��7��>%�4

��+#��-�� #��&�"��)��"��##��"��1��)�� #��

��;��-��2��'�-�� %!��"��)�� !��"��##��$��%&'�()*+��,�$��+��()�"��+��&��$��!��##��

��)��5��)��%��0��B��1��)��"��+��&��!��,��J�K�

��;��-��'�-�� %�)� %!��"��!�� %��$��%��+��()��&�"��!��,�N�,�$��%��1��()B��1��)��J��

��+#��'@��"� %� ��5��#��B��1��6��)�*+��5��#��1��)��E��-��#'��#-��5��)�*!��7�#��"

�� %��##5��#��5��#�()��&�B��1��,��+�� %� ��5��#�� H�V��6��)�*!��5��#��1�*��

��)��5��)��%��0��B��1��)��"��+��&��!��,��J�K� ��;��-��'�-�� %�)� %!��"��!�� %��$��%��+��()��&�"��!��,��,�$��%��1��()B��1��Q��K��)��J��

��"� %� ��5��#��B��1��6��)�*+��5��#��1��)�� 5��)�*!��


��!��)�* ��62!��7�#��"�� %��##5��#��5��#�()��&�B��1��,��+�� %� ��5��#�� H�V��6��)�*!��5��#��1�*��

��-��'��&��&��"��"��(�,5(5��'��,��G��&�-��<��-��#-��'��K�,��1��-��#-��<��'��/�,��W��-#��&#��&��'��0�,��#-��<��'��X�,��!�H��<�-��#-��7�#��"

��&��-��=��!��(B��7��!��5��7�#��

Tabele przestawne są tworzone w dwóch pętlach, wszystkie na podstawie jednego obiektu

��. Początek każdej kolejnej tabeli przestawnej jest wyliczany na podstawie

zmiennej ��.. Po utworzeniu tabeli kod zamienia kategorie liczbowe w pierwszej kolumnie

na tekst (np. wartość & jest zastępowana tekstem Kategorycznie się nie zgadzam). Na

końcu są korygowane szerokości kolumn.

��!��

Tabele przestawne Excela są elastyczne. Użytkownicy mogą z łatwością zamienić pole

wiersza z polem kolumny lub ukryć niektóre pozycje w tabeli, nieistotne w danym mo-

mencie. Można wprowadzić własne elementy interfejsu, które jeszcze bardziej ułatwią

modyfikacje. W przykładzie zaprezentowanym w tym podrozdziale pokazano tabelę

przestawną, której wyglądem można sterować za pomocą przełączników oraz pól wyboru

tak, jak pokazano na rysunku 17.7.

��Użytkownik możeskorzystaćz formantów w celumodyfikowania tabelprzestawnych


Tabela przestawna zawiera cztery dodatkowe pola obliczane (-.�, -./, -.) i -.*), któresłużą do obliczania podsumowań kwartalnych. Kod wykonywany w przypadku zaznacze-

nia pierwszej wartości przełącznika (Tylko miesiące) pokazuje listing 17.5. Procedura

jest prosta, przypomina procedury obsługi zdarzeń dla innych przełączników.

�� Modyfikacja tabeli przestawnej

��6'��?��$!�� %&��9��''��;'#��)�5��"��"�� %�� 5��#��9�� B��&��3��)%�� B��E��3��)%�� B��3��)%�� B��G��3��)%�� B��3��)%�� B��!-��3��)%�� B��E�'��3��)%�� B��3��)%�� B��-��3��)%�� B�� L��3��)%�� B��E��3��)%�� B��>��3��)%�� B��G��3��)5�� B��G�K��3��)5�� B��G�/��3��)5�� B��G�0��3��)5��7�#��"7�#��

Pola wyboru powodują włączanie (wyłączanie) podsumowań dla wierszy i kolumn.

Procedury obsługi tych zdarzeń zaprezentowano poniżej:

��!"��?*�$!�� #��''��;'#��)�5��"�� %��!��>��#�)�!"��?*��3��7�#��Y

��!"��?*K$!�� #��-&��''��;'#��)�5��"�� %��>��#�)�!"��?*K�3��7�#��

Tabele przestawne można oczywiście modyfikować w różny sposób. Jak wspomniałem,

najprostszym sposobem utworzenia kodu VBA, który modyfikuje tabele przestawne,

jest wykorzystanie rejestratora makr podczas ręcznych modyfikacji. Następnie wystarczy

wprowadzić poprawki w kodzie i skopiować go do procedur obsługi zdarzeń wybranych

formantów.

Excel 2003 PL. Programowanie w VBA. Vademecum profesjonalisty

Documents

Transcript of Excel 2003 PL. Programowanie w VBA. Vademecum profesjonalisty