TytuÅ‚ oryginalny: Excel® VBA Programming For Dummies®, 3rd Edition
Tłumaczenie: Ryszard Górnowicz, Grzegorz Kowalczyk
ISBN: 978-83-246-7950-8
Original English language edition Copyright © 2013 by John Wiley & Sons, Inc.,
Hoboken, New Jersey. All rights reserved including the right of reproduction in whole
or in part any form. This translation published by arrangement with Wiley Publishing, Inc.
Oryginalne angielskie wydanie © 2013 by John Wiley & Sons, Inc., Hoboken, New Jersey.
Wszelkie prawa, włączając prawo do reprodukcji całości lub części w jakiejkolwiek formie, zarezerwowane.
TÅ‚umaczenie opublikowane na mocy porozumienia z Wiley Publishing, Inc.
Translation copyright © 2014 by Helion S.A.
Wiley, the Wiley logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!,
The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, Making Everything Easier,
and related trade dress are trademarks or registered trademarks of John Wiley and Sons, Inc.
and/or its affiliates in the United States and/or other countries. Used under License.
Wiley, the Wiley logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way,
Dummies Daily, The Fun and Easy Way, Dummies.com, Making Everything Easier, i zwiÄ…zana z tym szata graficzna sÄ…
markami handlowymi John Wiley and Sons, Inc. i/lub firm stowarzyszonych w Stanach Zjednoczonych i/lub innych
krajach. Wykorzystywane na podstawie licencji.
Polish language edition published by Wydawnictwo Helion.
Copyright © 2014.
All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or
mechanical, including photocopying, recording or by any information storage retrieval system, without permission from
the Publisher.
Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej publikacji
w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną, fotograficzną, a także kopiowanie
książki na nośniku filmowym, magnetycznym lub innym powoduje naruszenie praw autorskich niniejszej publikacji.
Autor oraz Wydawnictwo HELION dołożyli wszelkich starań, by zawarte w tej książce informacje były kompletne
i rzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie, ani za związane z tym ewentualne
naruszenie praw patentowych lub autorskich. Autor oraz Wydawnictwo HELION nie ponoszą również żadnej
odpowiedzialności za ewentualne szkody wynikłe z wykorzystania informacji zawartych w książce.
Drogi Czytelniku!
Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres
http://dlabystrzakow.pl/user/opinie/e13pvb
Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję.
Wydawnictwo HELION
ul. Kościuszki 1c, 44-100 Gliwice
tel. 32 231 22 19, 32 230 98 63
e-mail: dlabystrzakow@dlabystrzakow.pl
WWW: http://dlabystrzakow.pl
Pliki z przykładami omawianymi w książce można znalezć pod adresem:
ftp://ftp.helion.pl/przyklady/e13pvb.zip
Printed in Poland.
" Kup książkę " Księgarnia internetowa
" Poleć książkę " Lubię to! Nasza społeczność
" Oceń książkę
Spis tre ci
O autorze .......................................................................................................................15
Podzi kowania autora ...................................................................................................17
Wst p ............................................................................................................................19
Czy ta ksi ka jest dla Ciebie? ...........................................................................................................19
A wi c chcesz by programist & .......................................................................................................20
Dlaczego warto? ...............................................................................................................................20
Co powiniene wiedzie ? ...................................................................................................................21
Obowi zkowy podrozdzia o konwencjach typograficznych ...................................................................22
Sprawd ustawienia zabezpiecze ......................................................................................................22
Jak podzielona jest ksi ka? ...............................................................................................................24
Cz I: Wst p do programowania w VBA ....................................................................................24
Cz II: Jak VBA wspó pracuje z Excelem? .................................................................................24
Cz III: Podstawy programowania ..............................................................................................24
Cz IV: Komunikacja z u ytkownikiem .......................................................................................24
Cz V: Od teorii do praktyki ......................................................................................................24
Cz VI: Dekalogi ......................................................................................................................24
Ikony u ywane w ksi ce ....................................................................................................................25
Pobieranie plików z przyk adami ........................................................................................................25
Co dalej? ..........................................................................................................................................26
Cz I: Wst p do programowania w VBA ................ 27
Rozdzia 1: Czym jest VBA? ............................................................................................29
No dobrze, czym jest wi c VBA? ......................................................................................................29
Co mo na zrobi za pomoc VBA? ...................................................................................................30
Wprowadzanie bloków tekstu ........................................................................................................31
Automatyzacja cz sto wykonywanego zadania ................................................................................31
Automatyzacja powtarzalnych operacji ...........................................................................................31
Tworzenie w asnego polecenia .......................................................................................................31
Tworzenie w asnego przycisku .......................................................................................................31
Tworzenie w asnych funkcji arkusza kalkulacyjnego .........................................................................31
Tworzenie w asnych dodatków do Excela .......................................................................................32
Tworzenie kompletnych aplikacji opartych na makrach ....................................................................32
Kup książkę Poleć książkę
Excel 2013 PL. Programowanie w VBA dla bystrzaków
6
Plusy i minusy j zyka VBA ...............................................................................................................32
Plusy j zyka VBA ........................................................................................................................32
Minusy j zyka VBA .....................................................................................................................33
VBA w pigu ce ................................................................................................................................33
Wycieczka po wersjach Excela ...........................................................................................................35
Rozdzia 2: Szybkie zanurzenie ..................................................................................... 39
Przygotowanie do pracy ....................................................................................................................39
Plan dzia ania ..................................................................................................................................40
Stawiamy pierwsze kroki ....................................................................................................................40
Rejestrowanie makra .........................................................................................................................41
Testowanie makra .............................................................................................................................42
Podgl d kodu makra .........................................................................................................................42
Modyfikacja makra ............................................................................................................................44
Zapisywanie skoroszytów zawieraj cych makra ...................................................................................45
Bezpiecze stwo makr ........................................................................................................................45
Wi cej o makrze NameAndTime .......................................................................................................47
Cz II: Jak VBA wspó pracuje z Excelem? ..............49
Rozdzia 3: Praca w edytorze VBE ................................................................................. 51
Czym jest Visual Basic Editor? ..........................................................................................................51
Uruchamianie edytora VBE .........................................................................................................51
Zapoznanie z komponentami edytora VBE ....................................................................................52
Praca z oknem Project ......................................................................................................................54
Dodawanie nowego modu u VBA .................................................................................................55
Usuwanie modu u VBA ...............................................................................................................55
Eksportowanie i importowanie obiektów .........................................................................................56
Praca z oknem Code .........................................................................................................................56
Minimalizowanie i maksymalizowanie okien ...................................................................................56
Tworzenie modu u ........................................................................................................................57
Wprowadzanie kodu VBA do modu u ..........................................................................................58
Bezpo rednie wprowadzanie kodu .................................................................................................58
U ywanie rejestratora makr ...........................................................................................................61
Kopiowanie kodu VBA ................................................................................................................63
Dostosowywanie rodowiska VBA .....................................................................................................63
Karta Editor ................................................................................................................................64
Karta Editor Format .....................................................................................................................66
Karta General ..............................................................................................................................67
Karta Docking .............................................................................................................................68
Rozdzia 4: Wprowadzenie do modelu obiektowego w Excelu ..................................... 69
Czy Excel to obiekt? .........................................................................................................................70
Wspinaczka po hierarchii obiektów .....................................................................................................70
Zapoznanie z kolekcjami ...................................................................................................................71
Odwo ywanie si do obiektów ............................................................................................................71
Nawigacja po hierarchii obiektów ...................................................................................................72
Upraszczanie odwo a do obiektów ...............................................................................................73
Kup książkę Poleć książkę
Spis tre ci
7
W a ciwo ci i metody obiektów ..........................................................................................................74
W a ciwo ci obiektów ...................................................................................................................74
Metody obiektów ..........................................................................................................................76
Zdarzenia obiektów ......................................................................................................................77
Poszukiwanie dodatkowych informacji ................................................................................................78
System pomocy VBA ...................................................................................................................78
Narz dzie Object Browser ............................................................................................................79
Automatyczna lista w a ciwo ci i metod ..........................................................................................80
Rozdzia 5: Procedury Sub i Function w j zyku VBA ......................................................81
Procedury Sub a funkcje ....................................................................................................................81
Rzut oka na procedury Sub ...........................................................................................................82
Rzut oka na procedury Function ....................................................................................................82
Nazwy procedur Sub i Function ....................................................................................................83
Uruchamianie procedur Sub ..............................................................................................................83
Bezpo rednie uruchamianie procedur Sub ......................................................................................85
Uruchamianie procedur w oknie dialogowym Makro .......................................................................85
Uruchamianie makr za pomoc skrótów klawiszowych .....................................................................86
Uruchamianie procedur przy u yciu przycisków i kszta tów ..............................................................87
Uruchamianie procedur z poziomu innych procedur ........................................................................89
Uruchamianie procedur Function .......................................................................................................89
Wywo ywanie funkcji z poziomu procedur Sub ...............................................................................90
Wywo ywanie funkcji z poziomu formu arkusza ..............................................................................90
Rozdzia 6: U ywanie rejestratora makr ........................................................................93
Czy to rzeczywisto , czy to VBA? ....................................................................................................93
Podstawy rejestrowania makr .............................................................................................................93
Przygotowania do rejestrowania makr .................................................................................................95
Wzgl dne czy bezwzgl dne? ..............................................................................................................96
Rejestrowanie makr w trybie odwo a bezwzgl dnych ......................................................................96
Rejestrowanie makr w trybie odwo a wzgl dnych ...........................................................................97
Co jest rejestrowane? .........................................................................................................................98
Opcje rejestratora makr ...................................................................................................................100
Nazwa makra .............................................................................................................................100
Klawisz skrótu ............................................................................................................................100
Przechowuj makro w ...................................................................................................................101
Opis ..........................................................................................................................................101
Czy to co jest wydajne? ..................................................................................................................101
Cz III: Podstawy programowania ...................... 105
Rozdzia 7: Kluczowe elementy j zyka VBA .................................................................107
Stosowanie komentarzy w kodzie VBA ............................................................................................107
U ywanie zmiennych, sta ych i typów danych ...................................................................................109
Poj cie zmiennej .........................................................................................................................109
Czym s typy danych w j zyku VBA? .........................................................................................110
Deklarowanie zmiennych i okre lanie ich zasi gu ..........................................................................111
Kup książkę Poleć książkę
Excel 2013 PL. Programowanie w VBA dla bystrzaków
8
Sta e ..........................................................................................................................................117
Sta e predefiniowane ...................................................................................................................118
a cuchy znaków .......................................................................................................................118
Daty i godziny ............................................................................................................................119
Instrukcje przypisania ......................................................................................................................120
Przyk ady instrukcji przypisania ...................................................................................................120
O znaku równo ci .......................................................................................................................121
Proste operatory .........................................................................................................................121
Praca z tablicami ............................................................................................................................123
Deklarowanie tablic ....................................................................................................................123
Tablice wielowymiarowe .............................................................................................................124
Tablice dynamiczne ....................................................................................................................124
Stosowanie etykiet ...........................................................................................................................125
Rozdzia 8: Praca z obiektami Range .......................................................................... 127
Szybka powtórka .............................................................................................................................127
Inne sposoby odwo ywania si do zakresu .........................................................................................129
W a ciwo Cells ........................................................................................................................129
W a ciwo Offset ......................................................................................................................130
Wybrane w a ciwo ci obiektu Range ................................................................................................131
W a ciwo Value ......................................................................................................................131
W a ciwo Text ........................................................................................................................132
W a ciwo Count ......................................................................................................................133
W a ciwo ci Column i Row .........................................................................................................133
W a ciwo Address ...................................................................................................................133
W a ciwo HasFormula .............................................................................................................134
W a ciwo Font ........................................................................................................................134
W a ciwo Interior ....................................................................................................................136
W a ciwo ci Formula i FormulaLocal ..........................................................................................136
W a ciwo NumberFormat ........................................................................................................137
Wybrane metody obiektu Range ......................................................................................................137
Metoda Select ............................................................................................................................137
Metody Copy i Paste ..................................................................................................................138
Metoda Clear .............................................................................................................................138
Metoda Delete ...........................................................................................................................139
Rozdzia 9: Praca z funkcjami VBA i arkusza kalkulacyjnego ...................................... 141
Co to jest funkcja? ...........................................................................................................................141
Stosowanie wbudowanych funkcji VBA ...........................................................................................142
Przyk ady funkcji VBA ..............................................................................................................142
Funkcje VBA, które robi co wi cej ni tylko zwracanie warto ci ..................................................144
Odkrywanie funkcji VBA ...........................................................................................................144
U ycie funkcji arkusza kalkulacyjnego w VBA ..................................................................................145
Przyk ady funkcji arkusza kalkulacyjnego ......................................................................................148
Wprowadzanie funkcji arkusza kalkulacyjnego ..............................................................................150
Wi cej o u yciu funkcji arkusza kalkulacyjnego .............................................................................151
U ycie w asnych funkcji ...................................................................................................................151
Kup książkę Poleć książkę
Spis tre ci
9
Rozdzia 10: Sterowanie przep ywem i podejmowanie decyzji ...................................153
Zabierz si za przep yw, kolego .......................................................................................................153
Instrukcja GoTo .............................................................................................................................154
Decyzje, decyzje ..............................................................................................................................155
Struktura If-Then .......................................................................................................................155
Struktura Select Case .................................................................................................................159
Entliczek, p tliczek czyli jak u ywa p tli? ...................................................................................162
P tle For-Next ...........................................................................................................................162
P tla Do-While ..........................................................................................................................167
P tla Do-Until ...........................................................................................................................168
U ycie p tli For Each-Next z kolekcjami ..........................................................................................168
Rozdzia 11: Automatyczne procedury i zdarzenia .......................................................171
Przygotowanie do wielkiego zdarzenia ..............................................................................................171
Czy zdarzenia s przydatne? .......................................................................................................173
Programowanie procedur obs ugi zdarze .....................................................................................173
Gdzie jest umieszczony kod VBA? ..................................................................................................174
Tworzenie procedury obs ugi zdarzenia ............................................................................................175
Przyk ady wprowadzaj ce ................................................................................................................176
Zdarzenie Open dla skoroszytu ...................................................................................................176
Zdarzenie BeforeClose dla skoroszytu ..........................................................................................179
Zdarzenie BeforeSave dla skoroszytu ...........................................................................................179
Przyk ady zdarze aktywacyjnych .....................................................................................................180
Zdarzenia aktywacji i dezaktywacji arkusza ..................................................................................180
Zdarzenia aktywacji i dezaktywacji skoroszytu ..............................................................................181
Zdarzenia aktywacji skoroszytu ....................................................................................................182
Inne zdarzenia dotycz ce arkusza .....................................................................................................183
Zdarzenie BeforeDoubleClick .....................................................................................................183
Zdarzenie BeforeRightClick .......................................................................................................184
Zdarzenie Change ......................................................................................................................184
Zdarzenia niezwi zane z obiektami ..................................................................................................186
Zdarzenie OnTime ....................................................................................................................186
Zdarzenia naci ni cia klawisza ....................................................................................................188
Rozdzia 12: Techniki obs ugi b dów .........................................................................191
Rodzaje b dów ..............................................................................................................................191
B dny przyk ad ..............................................................................................................................192
To makro nie jest idealne ............................................................................................................192
Makro wci nie jest idealne ........................................................................................................193
Czy teraz makro jest idealne? .......................................................................................................194
Rezygnacja z idea u ....................................................................................................................195
Inny sposób obs ugi b dów .............................................................................................................195
Korekta procedury EnterSquareRoot ...........................................................................................195
O instrukcji On Error .................................................................................................................196
Obs uga b dów szczegó owe informacje ......................................................................................197
Wznawianie wykonywania kodu po wyst pieniu b du ..................................................................197
Obs uga b dów w pigu ce ...........................................................................................................199
Kup książkę Poleć książkę
Excel 2013 PL. Programowanie w VBA dla bystrzaków
10
Kiedy ignorowa b dy? ..............................................................................................................199
Rozpoznawanie okre lonych b dów ............................................................................................200
Zamierzony b d ............................................................................................................................201
Rozdzia 13: Dezynsekcja kodu, czyli jak walczy z pluskwami ................................. 203
Rodzaje pluskiew ............................................................................................................................203
Podstawy entomologii, czyli jak zidentyfikowa pluskw .....................................................................205
Metody i techniki walki z pluskwami ................................................................................................205
Przegl danie kodu VBA ............................................................................................................206
Umieszczanie funkcji MsgBox w kluczowych miejscach kodu .........................................................206
Umieszczanie polecenia Debug.Print w kluczowych miejscach kodu ...............................................208
Korzystanie z wbudowanych narz dzi Excela wspomagaj cych odpluskwianie kodu VBA ...............209
Kilka s ów o debuggerze ..................................................................................................................209
Ustawianie punktów przerwa w kodzie programu ........................................................................209
Zastosowanie okna Watch ..........................................................................................................212
Zastosowanie okna Locals ..........................................................................................................213
Jak zredukowa liczb b dów w kodzie programu? ............................................................................215
Rozdzia 14: Przyk ady i techniki programowania w j zyku VBA ................................. 217
Przetwarzanie zakresów komórek .....................................................................................................217
Kopiowanie zakresów .................................................................................................................218
Kopiowanie zakresu o zmiennej wielko ci .....................................................................................219
Zaznaczanie komórek do ko ca wiersza lub kolumny ....................................................................220
Zaznaczanie ca ego wiersza lub ca ej kolumny ..............................................................................221
Przenoszenie zakresów ................................................................................................................222
Wydajne przetwarzanie komórek zaznaczonego zakresu przy u yciu p tli ........................................222
Wydajne przetwarzanie komórek zaznaczonego zakresu przy u yciu p tli (cz II) .........................224
Wprowadzanie warto ci do komórki .............................................................................................225
Okre lanie typu zaznaczonego zakresu .........................................................................................226
Identyfikowanie zaznacze wielokrotnych .....................................................................................226
Zmiana ustawie Excela .................................................................................................................227
Zmiana ustawie logicznych (opcje typu Boolean) ........................................................................227
Zmiana innych opcji (typu non-Boolean) .....................................................................................228
Praca z wykresami ..........................................................................................................................229
Metoda AddChart kontra metoda AddChart2 .............................................................................230
Modyfikowanie typu wykresu .......................................................................................................231
Przechodzenie w p tli przez elementy kolekcji ChartObjects ..........................................................232
Modyfikowanie w a ciwo ci wykresu ............................................................................................232
Zmiana formatowania wykresów ..................................................................................................233
Jak przyspieszy dzia anie kodu VBA? .............................................................................................234
Wy czanie aktualizacji ekranu ....................................................................................................234
Wy czenie automatycznego przeliczania skoroszytu ......................................................................235
Wy czanie irytuj cych ostrze e .................................................................................................236
Upraszczanie odwo a do obiektów .............................................................................................236
Deklarowanie typów zmiennych ...................................................................................................237
Zastosowanie struktury With-End With .......................................................................................238
Kup książkę Poleć książkę
Spis tre ci
11
Cz IV: Komunikacja z u ytkownikiem ................ 239
Rozdzia 15: Proste okna dialogowe ...........................................................................241
Co zamiast formularzy UserForm? ...................................................................................................241
Funkcja MsgBox ............................................................................................................................242
Wy wietlanie prostych okien dialogowych .....................................................................................243
Pobieranie odpowiedzi z okna dialogowego ..................................................................................243
Dostosowywanie wygl du okien dialogowych do w asnych potrzeb .................................................244
Funkcja InputBox ...........................................................................................................................247
Sk adnia funkcji InputBox ...........................................................................................................248
Przyk ad zastosowania funkcji InputBox .......................................................................................248
Inny rodzaj okna dialogowego InputBox .......................................................................................249
Metoda GetOpenFilename ..............................................................................................................250
Sk adnia metody GetOpenFilename ............................................................................................251
Przyk ad zastosowania metody GetOpenFilename ........................................................................251
Metoda GetSaveAsFilename ...........................................................................................................253
Pobieranie nazwy folderu ................................................................................................................254
Wy wietlanie wbudowanych okien dialogowych programu Excel ........................................................254
Rozdzia 16: Wprowadzenie do formularzy UserForm ..................................................257
Kiedy u ywa formularzy UserForm? ...............................................................................................257
Tworzenie formularzy UserForm wprowadzenie ..........................................................................258
Praca z formularzami UserForm ......................................................................................................259
Wstawianie nowego formularza UserForm ...................................................................................259
Umieszczanie formantów na formularzu UserForm .......................................................................260
Modyfikacja w a ciwo ci formantów formularza UserForm ............................................................261
Przegl danie okna Code formularza UserForm ............................................................................263
Wy wietlanie formularzy UserForm .............................................................................................263
Pobieranie i wykorzystywanie informacji z formularzy UserForm ...................................................264
Przyk ad tworzenia formularza UserForm .........................................................................................264
Tworzenie formularza UserForm .................................................................................................265
Dodawanie przycisków polece (formanty CommandButton) ........................................................265
Dodawanie przycisków opcji (formanty OptionButton) .................................................................267
Dodawanie procedur obs ugi zdarze ...........................................................................................268
Tworzenie makra, które wy wietla formularz na ekranie .................................................................270
Udost pnianie makra u ytkownikowi ...........................................................................................271
Testowanie dzia ania makra ........................................................................................................272
Rozdzia 17: Praca z formantami formularza UserForm ...............................................275
Rozpoczynamy prac z formantami formularzy UserForm .................................................................275
Dodawanie formantów ................................................................................................................276
Wprowadzenie do w a ciwo ci formantów ....................................................................................277
Formanty okien dialogowych szczegó y ........................................................................................278
Formant CheckBox (pole wyboru) ...............................................................................................279
Formant ComboBox (pole kombi) ...............................................................................................280
Formant CommandButton (przycisk polecenia) ............................................................................281
Formant Frame (pole grupy) .......................................................................................................281
Formant Image (pole obrazu) ......................................................................................................282
Kup książkę Poleć książkę
Excel 2013 PL. Programowanie w VBA dla bystrzaków
12
Formant Label (pole etykiety) .....................................................................................................283
Formant ListBox (pole listy) .......................................................................................................283
Formant MultiPage ....................................................................................................................284
Formant OptionButton (przycisk opcji) .......................................................................................285
Formant RefEdit (pole zakresu) ..................................................................................................286
Formant ScrollBar (pasek przewijania) ........................................................................................286
Formant SpinButton (pokr t o) ...................................................................................................287
Formant TabStrip (pole karty) ....................................................................................................288
Formant TextBox (pole tekstowe) ...............................................................................................288
Formant ToggleButton (przycisk prze cznika) ............................................................................289
Praca z formantami w oknach dialogowych .......................................................................................289
Zmiana rozmiarów i przenoszenie formantów w inne miejsce .........................................................289
Rozmieszczanie i wyrównywanie po o enia formantów w oknie dialogowym ....................................290
Obs uga u ytkowników preferuj cych korzystanie z klawiatury .......................................................291
Testowanie formularzy UserForm ................................................................................................293
Estetyka okien dialogowych .............................................................................................................293
Rozdzia 18: Techniki pracy z formularzami UserForm ................................................ 295
Zastosowanie w asnych okien dialogowych .......................................................................................295
Przyk adowy formularz UserForm ...................................................................................................296
Tworzenie okna dialogowego .......................................................................................................296
Tworzenie kodu procedury wy wietlaj cej okno dialogowe .............................................................298
Udost pnianie makra u ytkownikowi ...........................................................................................299
Testowanie okna dialogowego .....................................................................................................299
Dodawanie procedur obs ugi zdarze ...........................................................................................300
Sprawdzanie poprawno ci danych ...............................................................................................302
Teraz okno dialogowe dzia a tak, jak powinno! .............................................................................302
Wi cej przyk adów formularzy UserForm .........................................................................................302
Zastosowanie formantów ListBox ................................................................................................303
Zaznaczanie zakresów ................................................................................................................307
Praca z wieloma grupami formantów OptionButton ......................................................................309
Zastosowanie formantów SpinButton oraz TextBox .....................................................................310
Wykorzystywanie formularza UserForm jako wska nika post pu zadania .......................................312
Tworzenie niemodalnych okien dialogowych z wieloma kartami .....................................................315
Wy wietlanie wykresów na formularzach UserForm ......................................................................317
Lista kontrolna tworzenia i testowania okien dialogowych ..................................................................318
Rozdzia 19: Udost pnianie makr z poziomu interfejsu u ytkownika .......................... 321
Dostosowywanie Wst ki ................................................................................................................321
R czne dopasowywanie Wst ki do w asnych potrzeb ...................................................................322
Dodawanie do Wst ki przycisku w asnego makra ........................................................................324
Dostosowywanie Wst ki za pomoc kodu XML .........................................................................324
Dostosowywanie menu podr cznego .................................................................................................329
Rodzaje obiektów CommandBar .................................................................................................329
Wy wietlanie wszystkich menu podr cznych .................................................................................329
Odwo ania do elementów kolekcji CommandBars .........................................................................330
Odwo ania do formantów obiektu CommandBar ..........................................................................331
W a ciwo ci formantów obiektu CommandBar .............................................................................332
Kup książkę Poleć książkę
Spis tre ci
13
Przyk ady zastosowania VBA do modyfikacji menu podr cznego .......................................................334
Resetowanie wszystkich wbudowanych menu podr cznych .............................................................334
Dodawanie nowego elementu do menu podr cznego Cell ..............................................................335
Wy czanie menu podr cznego ....................................................................................................337
Tworzenie w asnych pasków narz dzi ...............................................................................................338
Cz V: Od teorii do praktyki .............................. 341
Rozdzia 20: Jak tworzy w asne funkcje arkuszowe i jak prze y ,
aby o tym opowiedzie ? ..............................................................................................343
Dlaczego tworzymy w asne funkcje? .................................................................................................343
Podstawowe informacje o funkcjach VBA ........................................................................................344
Tworzenie funkcji ...........................................................................................................................345
Praca z argumentami funkcji ............................................................................................................345
Przyk ady funkcji ............................................................................................................................346
Funkcje bezargumentowe ............................................................................................................346
Funkcje jednoargumentowe .........................................................................................................346
Funkcje z dwoma argumentami ....................................................................................................348
Funkcje pobieraj ce zakres jako argument .....................................................................................349
Funkcje z argumentami opcjonalnymi ...........................................................................................351
Funkcje opakowuj ce ......................................................................................................................353
Funkcja NumberFormat ..............................................................................................................353
Funkcja ExtractElement ..............................................................................................................354
Funkcja SayIt .............................................................................................................................354
Funkcja IsLike ...........................................................................................................................355
Funkcje zwracaj ce tablice ...............................................................................................................355
Zwracanie tablicy zawieraj cej nazwy miesi cy .............................................................................355
Zwracanie posortowanej listy .......................................................................................................356
Okno dialogowe Wstawianie funkcji .................................................................................................358
Wy wietlanie opisów funkcji ........................................................................................................358
Opisy argumentów ......................................................................................................................360
Rozdzia 21: Tworzenie dodatków ...............................................................................361
No dobrze& czym zatem s dodatki? ...............................................................................................361
Po co tworzy si dodatki? ................................................................................................................362
Praca z dodatkami ..........................................................................................................................363
Podstawy tworzenia dodatków .........................................................................................................364
Tworzymy przyk adowy dodatek ......................................................................................................365
Konfiguracja skoroszytu ..............................................................................................................365
Testowanie skoroszytu ................................................................................................................367
Tworzenie opisów dodatku ..........................................................................................................368
Ochrona kodu VBA ...................................................................................................................369
Tworzenie dodatku .....................................................................................................................369
Otwieranie dodatku ....................................................................................................................369
Dystrybucja dodatków .................................................................................................................370
Modyfikowanie dodatków ............................................................................................................371
Kup książkę Poleć książkę
Excel 2013 PL. Programowanie w VBA dla bystrzaków
14
Cz VI: Dekalogi ...............................................373
Rozdzia 22: Dziesi pyta na temat VBA (wraz z odpowiedziami) ........................... 375
Rozdzia 23: (Prawie) dziesi róde informacji na temat Excela ............................. 379
System pomocy j zyka VBA ...........................................................................................................379
Wsparcie techniczne firmy Microsoft ................................................................................................380
Inne strony internetowe ....................................................................................................................380
Blogi po wi cone Excelowi ..............................................................................................................380
Google ...........................................................................................................................................381
Bing ...............................................................................................................................................381
Lokalne grupy u ytkowników ...........................................................................................................381
Moje inne ksi ki ............................................................................................................................381
Rozdzia 24: Dziesi rzeczy, które powiniene robi w j zyku VBA
i których nie powiniene robi ................................................................................... 383
Zawsze deklaruj wszystkie zmienne ..................................................................................................383
Nigdy nie powiniene myli has a chroni cego kod VBA z bezpiecze stwem aplikacji .........................384
Zawsze staraj si wyczy ci i zoptymalizowa kod aplikacji ................................................................384
Nigdy nie umieszczaj wszystkiego w jednej procedurze .......................................................................385
Zawsze powiniene rozwa y zastosowanie innego oprogramowania ...................................................385
Nigdy nie zak adaj, e ka dy u ytkownik zezwala na uruchamianie makr ............................................386
Zawsze staraj si eksperymentowa z nowymi rozwi zaniami .............................................................386
Nigdy z góry nie zak adaj, e Twój kod b dzie poprawnie dzia a z innymi wersjami Excela .................386
Zawsze pami taj o u ytkownikach Twojej aplikacji ...........................................................................387
Nigdy nie zapominaj o tworzeniu kopii zapasowych ...........................................................................387
Skorowidz ................................................................................................................... 389
Kup książkę Poleć książkę
Rozdzia 14
Przyk ady i techniki
programowania w j zyku VBA
W tym rozdziale:
poznasz szereg przyk adów technik programowania w j zyku VBA,
dowiesz si , jak mo esz przyspieszy dzia anie kodu VBA w Twojej aplikacji.
ierz , e nauka programowania jest znacznie szybsza i zdecydowanie bardziej
W
efektywna, kiedy pracujemy na konkretnych przyk adach. Dobrze opracowany
przyk ad o wiele lepiej obja nia zagadnienie ni najbardziej rozbudowany i szczegó owy,
ale teoretyczny opis. Poniewa czytasz t ksi k , prawdopodobnie zgadzasz si ze mn
w tej materii. W tym rozdziale znajdziesz szereg przyk adów demonstruj cych u yteczne,
praktyczne techniki programowania w j zyku VBA.
Przyk ady omawiane w tym rozdziale zosta y podzielone na kilka kategorii. Oto one.
Praca z zakresami.
Modyfikacja ustawie Excela.
Praca z wykresami.
Przyspieszanie i optymalizacja dzia ania kodu VBA.
Niektóre z prezentowanych przyk adów b dziesz móg od razu wykorzysta w swoich
aplikacjach, jednak w wi kszo ci przypadków b d wymaga y pewnego dostosowania
do Twoich aplikacji.
Przetwarzanie zakresów komórek
Wi kszo zada , z jakimi b dziesz si styka , programuj c w j zyku VBA, b dzie zapewne
wymaga a mniejszego b d wi kszego przetwarzania zakresów komórek (aby od wie y
sobie wiadomo ci na temat obiektu Range, powiniene zajrze do rozdzia u 8.). Kiedy
pracujesz z obiektami Range, powiniene pami ta o nast puj cych sprawach.
Kod VBA nie musi zaznacza danego zakresu, aby go przetwarza .
Je eli kod VBA zaznacza wybrany zakres, przechowuj cy go skoroszyt musi by
aktywny.
Kup książkę Poleć książkę
Cz III: Podstawy programowania
218
Rejestrator makr nie zawsze b dzie w stanie wygenerowa optymalny kod VBA.
Bardzo cz sto jednak mo esz za jego pomoc utworzy bazowe makro i potem
odpowiednio zmodyfikowa kod tak, aby sta si bardziej efektywny.
Zazwyczaj bardzo dobrym rozwi zaniem jest nadawanie nazw zakresom komórek
wykorzystywanym w kodzie VBA. Przyk adowo polecenie Range("Total")jest
znacznie lepszym rozwi zaniem ni Range("D45"). Je li w tym drugim przypadku
pó niej wstawisz dodatkowy wiersz powy ej wiersza 45, to eby wszystko dzia a o
poprawnie, b dziesz musia zmodyfikowa makro tak, aby korzysta o z nowego,
poprawnego adresu komórki, a ta po wykonaniu takiej operacji b dzie mia a inny
adres (D46). Aby nada nazw wybranemu zakresowi komórek, powiniene przej
na kart FORMU Y i wybra polecenie Definiuj nazw , znajduj ce si w grupie
polece Nazwy zdefiniowane.
Kiedy tworzysz makro, które b dzie przetwarza o zakres komórek zaznaczony
przez u ytkownika, pami taj, e u ytkownik mo e zaznaczy kilka ca ych kolumn
czy wierszy. W wi kszo ci przypadków z pewno ci nie b dziesz chcia , aby makro
w p tli przechodzi o w takiej sytuacji przez wszystkie zaznaczone komórki (w cznie
z pustymi), co mog oby zaj bardzo wiele czasu. Dobre makro powinno odszuka
i przetwarza tylko komórki, które nie s puste.
Excel pozwala na jednoczesne zaznaczanie wielu zakresów komórek. Aby to zrobi ,
powiniene zaznaczy pierwszy zakres komórek, potem wcisn i przytrzyma
klawisz Ctrl, i zaznacza kolejne zakresy komórek przy u yciu myszy. Kod Twojej
aplikacji powinien by przygotowany na takie sytuacje i podejmowa odpowiednie
akcje.
Skoroszyty z wybranymi przyk adami omawianymi w tym rozdziale znajdziesz na stronie
internetowej naszej ksi ki.
Je eli chcesz samodzielnie wpisywa kod omawianych przyk adów, przejd do edytora
VBE, naciskaj c kombinacj klawiszy lewyAlt+F11, a nast pnie wstaw nowy modu VBA
i wpisz kod prezentowanych procedur. Upewnij si , e Twój skoroszyt jest poprawnie
skonfigurowany. Je eli na przyk ad kod danego przyk adu odwo uje si do arkuszy
o nazwach Arkusz1 i Arkusz2, upewnij si , e takie arkusze istniej w Twoim skoroszycie.
Kopiowanie zakresów
Kopiowanie zakresów komórek mo e mia o pretendowa do miana jednej z najcz ciej
wykonywanych operacji w Excelu. Kiedy w czysz rejestrator makr i skopiujesz zakres
komórek o adresie A1:A5 do zakresu B1:B5, otrzymasz nast puj ce makro.
Sub CopyRange()
Range("A1:A5").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Kup książkę Poleć książkę
Rozdzia 14: Przyk ady i techniki programowania w j zyku VBA
219
Zwró uwag na ostatnie polecenie. Zosta o ono wygenerowane przez naci ni cie
klawisza Esc po skopiowaniu zakresu komórek, co spowodowa o usuni cie przerywanej
linii, reprezentuj cej na arkuszu obramowanie kopiowanego zakresu.
Przedstawione makro dzia a poprawnie, ale zakresy komórek mo na kopiowa w znacznie
bardziej efektywny sposób. Identyczny rezultat mo esz osi gn za pomoc procedury,
która sk ada si z tylko jednego wiersza polecenia i nie zaznacza adnych komórek
(dzi ki czemu nie wymaga ustawiania w a ciwo ci CutCopyMode na warto False).
Sub CopyRange2()
Range("A1:A5").Copy Range("B1")
End Sub
Procedura przedstawiona powy ej korzysta z tego, e metoda Copy mo e u ywa argumentu
wywo ania reprezentuj cego miejsce docelowe kopiowanego zakresu. Informacje o tym
znalaz em w pomocy systemowej VBA. Powy szy przyk ad doskonale równie ilustruje
fakt, e rejestrator makr nie zawsze generuje najbardziej efektywny kod.
Kopiowanie zakresu o zmiennej wielko ci
W wielu przypadkach konieczne jest skopiowanie zakresu komórek, dla którego dok adna
liczba wierszy i kolumn okre laj cych jego wielko nie jest z góry znana. Przyk adowo
mo esz dysponowa skoroszytem ledz cym tygodniow sprzeda , w którym liczba wierszy
zmienia si ka dego tygodnia po wprowadzeniu nowych danych.
Na rysunku 14.1 przedstawiam przyk ad cz sto spotykanego typu arkusza. Znajduj cy
si w nim zakres komórek sk ada si z kilku wierszy, których liczba zmienia si ka dego
dnia. Poniewa nie wiesz, jaki jest rozmiar zakresu w danej chwili, musisz utworzy
kod, który b dzie dzia a bez u ywania adresu zakresu kopiowanych komórek.
Rysunek 14.1.
Przyk ad zakre-
su, który mo e
sk ada si
z dowolnej
liczby wierszy
Makro przedstawione poni ej ilustruje sposób kopiowania zakresu komórek z arkusza
Arkusz1 do arkusza Arkusz2 (pocz wszy od komórki A1). Makro wykorzystuje w a ciwo
CurrentRegion, która zwraca obiekt Range odpowiadaj cy blokowi komórek otaczaj cych
okre lon komórk (w tym przypadku o adresie A1).
Kup książkę Poleć książkę
Cz III: Podstawy programowania
220
Sub CopyCurrentRegion()
Range("A1").CurrentRegion.Copy
Sheets("Arkusz2").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Arkusz1").Select
Application.CutCopyMode = False
End Sub
Zastosowanie w a ciwo ci CurrentRegion jest równowa ne z przej ciem na kart
NARZ DZIA G ÓWNE i wybraniem polecenia Znajd i zaznacz/Przejd do
specjalnie, znajduj cego si w grupie opcji Edytowanie, i nast pnie zaznaczeniem opcji
Bie cy obszar. Aby przekona si , jak to dzia a, podczas wykonywania tych polece
powiniene u y rejestratora makr. Zazwyczaj warto w a ciwo ci CurrentRegion
reprezentuje prostok tny blok komórek otoczony przez puste wiersze i kolumny.
Oczywi cie, mo esz zoptymalizowa kod makra przedstawionego powy ej i nie
zaznacza obszaru docelowego dla kopiowanych komórek. Makro przedstawione poni ej
korzysta z faktu, e metoda Copy mo e u ywa argumentu wywo ania reprezentuj cego
miejsce docelowe kopiowanego zakresu.
Sub CopyCurrentRegion2()
Range("A1").CurrentRegion.Copy _
Sheets("Arkusz2").Range("A1")
End Sub
Je eli zakres komórek, który chcesz skopiowa , jest tabel (zdefiniowan przy u ycia
polecenia WSTAWIANIE/Tabele/Tabela), ca e zadanie b dzie jeszcze atwiejsze. Ka da
tabela posiada swoj nazw (na przyk ad Tabela1) i automatycznie rozszerza si w miar
dodawania nowych wierszy.
Sub CopyTable()
Range("Tabela1").Copy Sheets("Arkusz2").Range("A1")
End Sub
Je eli spróbujesz wykona procedur przedstawion powy ej, przekonasz si , e wiersz
nag ówka tabeli nie jest kopiowany, poniewa obiekt Tabela1 nie obejmuje tego
wiersza. Je li chcesz, aby wiersz nag ówka równie by kopiowany, powiniene zmieni
odwo anie do tabeli tak, jak to zosta o przedstawione poni ej.
Range("Tabela1[#All]")
Zaznaczanie komórek
do ko ca wiersza lub kolumny
Prawdopodobnie bardzo cz sto u ywasz kombinacji klawiszy, takich jak Ctrl+Shift+ ,
czy Ctrl+Shift+ , do zaznaczania zakresów sk adaj cych si ze wszystkich komórek,
od komórki aktywnej, a do ko ca kolumny czy wiersza. Nie jest wi c chyba zaskoczeniem,
e mo esz napisa makro, które b dzie zaznacza komórki w podobny sposób.
Kup książkę Poleć książkę
Rozdzia 14: Przyk ady i techniki programowania w j zyku VBA
221
Do zaznaczania ca ego bloku komórek mo esz u y w a ciwo ci CurrentRegion. Ale co
powiniene zrobi , je eli chcesz zaznaczy , powiedzmy, tylko jedn kolumn z tego
bloku komórek? Na szcz cie, VBA pozwala na wykonywanie takich operacji. Procedura,
której kod przedstawiam poni ej, zaznacza zakres komórek, pocz wszy od bie cej
aktywnej komórki w dó kolumny, a do komórki znajduj cej si o jeden wiersz powy ej
pierwszej pustej komórki tej kolumny. Po zaznaczeniu zakresu mo esz przetwarza go
w dowolny sposób kopiowa komórki, przenosi je w inne miejsce arkusza, zmienia
formatowanie i tak dalej.
Sub SelectDown()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub
Oczywi cie, taki sam obszar mo esz równie zaznaczy r cznie. Aby to zrobi , powiniene
zaznaczy pierwsz komórk , nast pnie wcisn i przytrzyma klawisz Shift, nacisn
klawisz End i wreszcie nacisn klawisz (strza ka w dó ).
W przyk adzie wykorzystuj metod End obiektu ActiveCell, która zwraca obiekt typu
Range. Metoda End pobiera jeden argument okre laj cy kierunek, w którym zostanie
wykonane zaznaczenie. Argumentami tej metody mo e by dowolna ze sta ych
przedstawionych poni ej:
xlUp,
xlDown,
xlToLeft,
xlToRight.
Pami taj, e zaznaczanie zakresu nie jest potrzebne do jego przetwarzania. Makro
przedstawione poni ej zmienia czcionk w komórkach zmiennego zakresu (pojedyncza
kolumna) na pogrubion bez uprzedniego zaznaczenia zakresu.
Sub MakeBold()
Range(ActiveCell, ActiveCell.End(xlDown)) _
.Font.Bold = True
End Sub
Zaznaczanie ca ego wiersza lub ca ej kolumny
Procedura przedstawiona poni ej ilustruje sposób zaznaczania kolumny, w której znajduje
si aktywna komórka. Makro wykorzystuje w a ciwo EntireColumn, która zwraca
obiekt typu Range reprezentuj cy ca kolumn .
Sub SelectColumn()
ActiveCell.EntireColumn.Select
End Sub
Jak pewnie si spodziewasz, w j zyku VBA dost pna jest równie w a ciwo EntireRow,
która zwraca obiekt typu Range reprezentuj cy ca y wiersz.
Kup książkę Poleć książkę
Cz III: Podstawy programowania
222
Przenoszenie zakresów
Zazwyczaj, aby przenie zakres komórek, zaznaczasz go, wycinasz do schowka systemowego
i nast pnie wklejasz w inne miejsce. Je eli u yjesz rejestratora makr do zapisania takiej
operacji, przekonasz si , e wygenerowany zostanie kod podobny do przedstawionego
poni ej.
Sub MoveRange()
Range("A1:C6").Select
Selection.Cut
Range("A10").Select
ActiveSheet.Paste
End Sub
Podobnie jak podczas kopiowania komórek, takie rozwi zanie nie jest najbardziej
efektywnym sposobem przenoszenia zakresu komórek w inne miejsce. W praktyce tak
operacj mo esz wykona za pomoc procedury sk adaj cej si z jednego wiersza kodu,
co prezentuj poni ej.
Sub MoveRange2()
Range("A1:C6").Cut Range("A10")
End Sub
Makro przedstawione powy ej korzysta z faktu, e metoda Cut mo e u ywa argumentu
wywo ania reprezentuj cego miejsce docelowe przenoszonego zakresu. Zwró równie
uwag na fakt, e podczas przenoszenia aden zakres komórek nie jest zaznaczany.
Wska nik aktywnej komórki przez ca y czas pozostaje w tym samym miejscu arkusza.
Wydajne przetwarzanie komórek
zaznaczonego zakresu przy u yciu p tli
Jednym z zada cz sto wykonywanych przez makra jest sprawdzanie poszczególnych
komórek zakresu i wykonywanie okre lonych operacji na podstawie ich zawarto ci. Takie
makra zazwyczaj wykorzystuj p tl For-Next, za pomoc której przetwarzane s komórki
zakresu.
Przyk ad przedstawiony ni ej ilustruje sposób przechodzenia kolejno przez wszystkie
komórki danego zakresu. W naszym przypadku przetwarzany jest aktualnie zaznaczony
zakres komórek. Zmienna obiektowa o nazwie Cell reprezentuje aktualnie przetwarzan
komórk . W p tli For Each-Next znajduje si jedno polecenie, które sprawdza aktualnie
przetwarzan komórk i zmienia jej czcionk na pogrubion , je eli warto przechowywana
w komórce jest dodatnia.
Sub ProcessCells()
Dim Cell As Range
For Each Cell In Selection
If Cell.Value > 0 Then Cell.Font.Bold = True
Next Cell
End Sub
Kup książkę Poleć książkę
Rozdzia 14: Przyk ady i techniki programowania w j zyku VBA
223
Taka procedura dzia a poprawnie, ale co si stanie, je eli u ytkownik zaznaczy ca
kolumn lub ca y wiersz? To wcale nie jest takie nieprawdopodobne, bo przecie Excel
pozwala na wykonywanie operacji na ca ych wierszach i kolumnach. W takiej sytuacji
wykonanie makra mo e zaj naprawd du o czasu, poniewa nasza p tla przetwarza
ka d komórk zaznaczonego zakresu, a cznie z pustymi w jednej kolumnie komórek
jest a 1 048 576& Aby zatem nasze makro by o bardziej wydajne, musimy je tak
zmodyfikowa , eby przetwarzane by y tylko i wy cznie komórki, które nie s puste.
Procedura przedstawiona poni ej przetwarza wy cznie niepuste komórki zaznaczonego
zakresu dzi ki zastosowaniu metody SpecialCells (wi cej szczegó owych informacji na
temat tej metody znajdziesz w pomocy systemowej VBA). Nasza procedura za pomoc
polecenia Set tworzy dwa obiekty typu Range: pierwszy z nich to podzakres komórek
zakresu wej ciowego, zawieraj cy wy cznie komórki z warto ciami sta ymi (na przyk ad
teksty, warto ci liczbowe, litera y i tak dalej), a drugi sk ada si z komórek zawieraj cych
formu y. Procedura przetwarza tylko komórki nale ce do tych podzakresów, co w efekcie
powoduje pomini cie przetwarzania wszystkich pozosta ych, pustych komórek zakresu
wej ciowego. Sprytne, prawda?
Sub SkipBlanks()
Dim ConstantCells As Range
Dim FormulaCells As Range
Dim cell As Range
' Ignoruj b dy
On Error Resume Next
' Przetwarzaj komórki zawieraj ce warto ci sta e
Set ConstantCells = Selection _
.SpecialCells(xlConstants)
For Each cell In ConstantCells
If cell.Value > 0 Then
cell.Font.Bold = True
End If
Next cell
' Przetwarzaj komórki zawieraj ce formu y
Set FormulaCells = Selection _
.SpecialCells(xlFormulas)
For Each cell In FormulaCells
If cell.Value > 0 Then
cell.Font.Bold = True
End If
Next cell
End Sub
Procedura SkipBlanks dzia a tak samo szybko, niezale nie od tego, jaki zakres komórek
zaznaczy e . Mo esz na przyk ad zaznaczy zakres sk adaj cy si z kilku komórek, zaznaczy
wszystkie kolumny w danym zakresie albo wszystkie wiersze w danym zakresie, albo
nawet ca y arkusz. Jak wida , jest to ogromne usprawnienie w stosunku do oryginalnej
procedury ProcessCells, któr omówi em nieco wcze niej.
Zwró uwag , e w kodzie procedury u yli my polecenia:
On Error Resume Next
Kup książkę Poleć książkę
Cz III: Podstawy programowania
224
Polecenie to powoduje, e Excel po prostu ignoruje b dy (inaczej mówi c, je eli próba
wykonania danego polecenia ko czy si b dem, Excel ignoruje ten b d i po prostu
przechodzi do kolejnego polecenia; wi cej szczegó owych informacji na temat obs ugi
b dów znajdziesz w rozdziale 12.). W naszym przypadku zastosowanie polecenia On
Error jest konieczne, poniewa metoda SpecialCells generuje b d, gdy adna komórka
nie spe nia podanego kryterium.
Zastosowanie metody SpecialCells jest równowa ne z przej ciem na kart NARZ DZIA
G ÓWNE, wybraniem polecenia Znajd i zaznacz/Przejd do specjalnie, znajduj cego
si w grupie opcji Edytowanie, i nast pnie zaznaczeniem opcji Sta e lub Formu y. Aby
przekona si , jak to dzia a, podczas wykonywania tych polece powiniene u y
rejestratora makr i zaznacza ró ne opcje.
Wydajne przetwarzanie komórek
zaznaczonego zakresu przy u yciu p tli
(cz II)
A teraz ci g dalszy naszej opowie ci. W tym punkcie przedstawi inny sposób efektywnego
przetwarzania komórek znajduj cych si w zaznaczonym zakresie. Tym razem procedura
b dzie korzysta a z w a ciwo ci UsedRange, która zwraca obiekt typu Range, reprezentuj cy
u ywany zakres arkusza. Procedura korzysta równie z metody Intersect, która zwraca
obiekt typu Range zawieraj cy komórki b d ce cz ci wspóln dwóch zakresów.
Poni ej przedstawiam zmodyfikowan wersj procedury SkipBlanks, omawianej
w poprzednim punkcie.
Sub SkipBlanks2()
Dim WorkRange As Range
Dim cell As Range
Set WorkRange = Intersect(Selection, ActiveSheet.UsedRange)
For Each cell In WorkRange
If cell.Value > 0 Then
cell.Font.Bold = True
End If
Next cell
End Sub
Zmienna obiektowa WorkRange zawiera komórki, które s cz ci wspóln zakresu
zaznaczonego przez u ytkownika oraz zakresu u ywanych komórek arkusza. Je li
zatem u ytkownik zaznaczy ca kolumn , zmienna WorkRange b dzie zawiera a tylko
komórki, które znajduj si jednocze nie w zaznaczonej kolumnie i w u ywanym
zakresie arkusza. Jak wida , jest to bardzo szybka i efektywna metoda pozwalaj ca na
unikni cie przetwarzania komórek znajduj cych si poza zakresem u ywanych
komórek arkusza.
Kup książkę Poleć książkę
Rozdzia 14: Przyk ady i techniki programowania w j zyku VBA
225
Wprowadzanie warto ci do komórki
Na rysunku 14.2 pokazuj , w jaki sposób mo esz u y funkcji InputBox j zyka VBA do
pobierania od u ytkownika warto ci, która nast pnie mo e zosta zapisana w wybranej
komórce. W procedurze przedstawionej poni ej demonstruj , jak poprosi u ytkownika
o podanie warto ci i wstawi j do komórki A1 aktywnego arkusza (i to wszystko
za pomoc jednego polecenia).
Sub GetValue()
Range("A1").Value = InputBox( _
"Wprowad warto dla komórki A1:")
End Sub
Rysunek 14.2.
Zastosowanie
funkcji Input-
Box do pobie-
rania warto ci
od u ytkownika
Przedstawiona procedura mo e jednak sprawia pewien problem. Je eli u ytkownik
naci nie w oknie dialogowym przycisk Cancel, procedura usunie wszelkie dane ju
znajduj ce si w komórce A1, a takie zachowanie nie jest zbyt dobr praktyk programistyczn .
Naci ni cie przycisku Cancel powinno po prostu usuwa z ekranu okno dialogowe bez
wykonywania adnych dodatkowych operacji.
Makro przedstawione poni ej ilustruje znacznie lepsze podej cie do takiego zagadnienia
i do zapami tania warto ci wprowadzonej przez u ytkownika wykorzystuje zmienn x.
Je eli zmienna zawiera co innego ni pusty ci g znaków (czyli kiedy u ytkownik
wprowadzi jak warto ), warto zmiennej jest zapisywana w komórce A1. W przeciwnym
przypadku procedura ko czy dzia anie, nie wykonuj c adnych dodatkowych operacji.
Sub GetValue2()
Dim x as Variant
x = InputBox("Wprowad warto dla komórki A1:")
If x <> "" Then Range("A1").Value = x
End Sub
Zmienna x zosta a zdefiniowana jako zmienna typu Variant, poniewa jej warto ci
mo e by warto albo pusty ci g znaków (je eli u ytkownik naci nie przycisk Cancel).
Kup książkę Poleć książkę
Cz III: Podstawy programowania
226
Okre lanie typu zaznaczonego zakresu
Je eli zadaniem Twojego makra b dzie przetwarzanie zaznaczonego zakresu, takie
makro musi mie zdolno sprawdzenia, czy przed jego wywo aniem u ytkownik
rzeczywi cie zaznaczy zakres komórek. W przeciwnym razie, je eli przed wywo aniem
makra zaznaczony zostanie inny obiekt (na przyk ad wykres lub kszta t), próba wykonania
makra najprawdopodobniej zako czy si niepowodzeniem. Polecenie przedstawione
poni ej wykorzystuje funkcj TypeName j zyka VBA do wy wietlania na ekranie typu
aktualnie zaznaczonego obiektu.
MsgBox TypeName(Selection)
Je eli aktualnie zaznaczony jest obiekt typu Range, wykonanie takiego polecenia spowoduje
wy wietlenie s owa Range. Je li Twoje makro dzia a tylko z zakresami komórek, mo esz
u y polecenia If do sprawdzenia, czy aktualnie zaznaczony obiekt to zakres (obiekt
typu Range). Procedura przedstawiona poni ej sprawdza typ zaznaczonego obiektu
i je eli nie jest to obiekt typu Range, na ekranie wy wietlany jest odpowiedni komunikat
i procedura ko czy dzia anie.
Sub CheckSelection()
If TypeName(Selection) <> "Range" Then
MsgBox "Zaznacz zakres komórek."
Exit Sub
End If
' & [Tutaj wstaw dalsz cz kodu procedury]
End Sub
Identyfikowanie zaznacze wielokrotnych
Jak pami tasz, Excel pozwala na jednoczesne zaznaczanie wielu obiektów. Aby to zrobi ,
powiniene podczas zaznaczania obiektów lub zakresów trzyma wci ni ty klawisz Ctrl.
Zaznaczenia wielokrotne mog by przyczyn problemów z wykonywaniem niektórych
makr. Przyk adowo nie mo esz skopiowa zakresu komórek, który zosta utworzony
poprzez wiele zaznacze nieci g ych zakresów komórek. Je eli spróbujesz wykona
tak operacj , Excel wy wietli na ekranie komunikat przedstawiony na rysunku 14.3.
Makro przedstawione ni ej pokazuje, w jaki sposób mo esz sprawdzi , czy u ytkownik
dokona zaznaczenia wielokrotnego, i na tej podstawie wykona odpowiedni operacj .
Sub MultipleSelection()
If Selection.Areas.Count > 1 Then
MsgBox "Zaznaczenia wielokrotne nie s dozwolone."
Exit Sub
End If
' & [Tutaj wstaw dalsz cz kodu procedury]
End Sub
Przedstawiona procedura wykorzystuje metod Areas, która zwraca kolekcj wszystkich
zakresów w danym zaznaczeniu. W a ciwo Count zwraca liczb obiektów tej kolekcji.
Kup książkę Poleć książkę
Rozdzia 14: Przyk ady i techniki programowania w j zyku VBA
227
Rysunek 14.3.
Excel nie lubi,
kiedy próbujesz
kopiowa nie-
ci g e zakresy
komórek
Zmiana ustawie Excela
Chyba najbardziej u ytecznymi makrami s proste procedury, które zmieniaj jedno
lub kilka ustawie Excela. Je li na przyk ad dojdziesz do wniosku, e bardzo cz sto
przywo ujesz na ekran okno opcji programu Excel i zmieniasz jakie ustawienie, taka
operacja z pewno ci b dzie bardzo dobrym kandydatem do napisania makra
oszcz dzaj cego Twój czas i u atwiaj cego zmian takiego ustawienia.
W tym podrozdziale zaprezentuj dwa przyk ady procedur, które pokazuj , w jaki
sposób mo na zmienia ustawienia Excela. Ogólne zasady dzia ania tych procedur
mo esz z powodzeniem zastosowa do napisania w asnych makr zmieniaj cych inne
ustawienia Excela.
Zmiana ustawie logicznych
(opcje typu Boolean)
Podobnie jak wy cznik wiat a, opcje logiczne (typu Boolean) mog by albo w czone,
albo wy czone. Mo esz na przyk ad utworzy makro, które b dzie w cza o lub wy cza o
wy wietlanie podzia u arkusza na strony. Kiedy wydrukujesz arkusz (lub skorzystasz
z trybu podgl du wydruku), Excel wy wietla na arkuszu przerywane linie reprezentuj ce
miejsca podzia u arkusza na strony wydruku. Niektórych u ytkowników (w cznie
z autorem tej ksi ki) takie zachowanie Excela irytuje. Niestety, jedynym sposobem
wy czenia wy wietlania podzia u arkusza na strony jest otwarcie okna dialogowego Opcje
programu Excel, przej cie na kart Zaawansowane, a nast pnie przewijanie zawarto ci tej
karty, a do odszukania opcji Poka podzia y stron. Je eli podczas wy czania tej opcji
korzysta e z rejestratora makr, przekonasz si , e Excel generuje poni szy kod.
Kup książkę Poleć książkę
Cz III: Podstawy programowania
228
ActiveSheet.DisplayPageBreaks = False
Z drugiej strony, je eli podczas rejestrowania makra podzia y stron nie s widoczne,
Excel generuje taki kod.
ActiveSheet.DisplayPageBreaks = True
Takie informacje mog doprowadzi do wniosku, e b dziesz musia napisa a dwa
makra jedno do w czania podgl du podzia u stron, a drugie do jego wy czania.
Na szcz cie, to nieprawda. Procedura przedstawiona poni ej wykorzystuje operator
Not do zmiany warto ci logicznej True na False i odwrotnie. Wykonanie procedury
TogglePageBreaks to prosty sposób na cykliczne w czanie i wy czanie podgl du podzia u
stron arkusza.
Sub TogglePageBreaks()
On Error Resume Next
ActiveSheet.DisplayPageBreaks = Not _
ActiveSheet.DisplayPageBreaks
End Sub
Pierwsze polecenie informuje Excel, e powinien ignorowa ewentualne b dy. Przyk adowo
podzia y stron nie s wy wietlane na arkuszach wykresów. Kiedy wprowadzisz takie
polecenie i spróbujesz wykona t procedur dla arkusza wykresu, na ekranie nie pojawi
si komunikat o b dzie.
Techniki u ytej w procedurze TogglePageBreaks mo esz u ywa do zmiany dowolnych
opcji logicznych (czyli takich, których warto ciami s True albo False).
Zmiana innych opcji (typu non-Boolean)
Do zmiany opcji, które nie s typu logicznego, mo esz u ywa konstrukcji Select Case.
W przyk adzie przedstawionym poni ej zmieniam tryb przeliczania skoroszytu z r cznego
na automatyczny i odwrotnie, i nakazuj wy wietlenie na ekranie komunikatu opisuj cego
aktualny tryb przeliczania arkusza.
Sub ToggleCalcMode()
Select Case Application.Calculation
Case xlManual
Application.Calculation = xlCalculationAutomatic
MsgBox "Automatyczne przeliczanie skoroszytu"
Case xlAutomatic
Application.Calculation = xlCalculationManual
MsgBox "R czne przeliczanie skoroszytu"
End Select
End Sub
Techniki u ytej w procedurze ToggleCalcMode mo esz u ywa do zmiany dowolnych
opcji, które nie posiadaj warto ci logicznych.
Kup książkę Poleć książkę
Rozdzia 14: Przyk ady i techniki programowania w j zyku VBA
229
Praca z wykresami
Wykresy w Excelu s wr cz prze adowane najró niejszymi obiektami, st d ich
przetwarzanie za pomoc kodu VBA mo e by niez ym wyzwaniem.
Uruchomi em Excel 2013, w zakresie komórek A1:A3 wpisa em kilka liczb i zaznaczy em
ten obszar arkusza. Nast pnie w czy em rejestrator makr i dla tych trzech punktów
danych utworzy em prosty wykres kolumnowy. Pó niej usun em wy wietlanie siatki
wykresu i zmieni em jego tytu . Oto zarejestrowane makro.
Sub Macro1()
' Zarejestrowane w Excelu 2013
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Arkusz1!$A$1:$A$3")
ActiveChart.SetElement (msoElementPrimaryValueGridLinesNone)
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "To jest mój wykres"
End Sub
Kiedy zobaczy em ten kod, by em nieco zaskoczony, poniewa nigdy wcze niej nie
s ysza em o metodzie AddChart2. Okaza o si , e metoda AddChart2 to nowo , która
zosta a wprowadzona w Excelu 2013. Je eli wykonasz podobn operacj z rejestrowaniem
makra w Excelu 2010, wynik b dzie nast puj cy.
Sub Macro1()
' Zarejestrowane w Excelu 2010
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Arkusz1!$A$1:$A$3")
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.ChartTitle.Text = "To jest mój wykres"
End Sub
Co to wszystko oznacza? Ano tyle, e makra zarejestrowane w Excelu 2013 po prostu nie
b d dzia a y w Excelu 2010, ale makra rejestrowane w Excelu 2010 dzia aj w Excelu 2013.
Innymi s owy, makra Excela 2010 s kompatybilne w przód (czyli zachowuj zgodno
z przysz ymi wersjami Excela; forward compatibility), a makra Excela 2013 zosta y pozbawione
kompatybilno ci wstecznej (backward compatibility), czyli nie zachowuj zgodno ci
z poprzednimi wersjami.
Przeci tny u ytkownik Excela 2013 prawdopodobnie nie wie nic na temat kompatybilno ci
makr w odniesieniu do tworzenia wykresów. Je eli jednak udost pnisz takie makro
komu , kto u ywa starszej wersji Excela, bardzo szybko si o tym dowiesz. Wnioski?
Gdy u ywasz rejestratora makr do tworzenia makr przetwarzaj cych wykresy, powiniene
przetestowa takie makra na wszystkich wersjach Excela, które b d wykorzystywane
do uruchamiania takiego makra.
Kup książkę Poleć książkę
Cz III: Podstawy programowania
230
Metoda AddChart kontra metoda AddChart2
Poni ej przedstawiam oficjaln sk adni metody AddChart (metoda jest kompatybilna
z Excelem 2007 i wersjami pó niejszymi).
.AddChart(Type, Left, Top, Width, Height)
A oto sk adnia metody AddChart2 (która jest kompatybilna wy cznie z Excelem 2013).
.AddChart2 (Style, XlChartType, Left, Top, Width, Height, NewLayout)
Jak wida , metoda AddChart2 pobiera kilka dodatkowych argumentów, które okre laj styl
wykresu, typ wykresu oraz jego uk ad. Z drugiej strony, metoda AddChart tworzy po
prostu pusty wykres, a wszystkie detale musz by zdefiniowane za pomoc dodatkowych
polece .
Analiza zarejestrowanego kodu ujawnia kilka rzeczy, które mog by pomocne podczas
tworzenia w asnych makr przetwarzaj cych wykresy. Je eli jeste ciekawy, rzu okiem
na zmodyfikowan r cznie procedur , której zadaniem jest utworzenie wykresu na bazie
zaznaczonego zakresu komórek.
Sub CreateAChart()
Dim ChartData As Range
Dim ChartShape As Shape
Dim NewChart As Chart
' Tworzenie zmiennych obiektowych
Set ChartData = ActiveWindow.RangeSelection
Set ChartShape = ActiveSheet.Shapes.AddChart
Set NewChart = ChartShape.Chart
With NewChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Range(ChartData.Address)
.SetElement (msoElementLegendRight)
.SetElement (msoElementChartTitleAboveChart)
.ChartTitle.Text = "To jest mój wykres"
End With
End Sub
To makro jest kompatybilne z Excelem 2007 i wersjami pó niejszymi. Makro tworzy
grupowany wykres kolumnowy wraz z legend i tytu em. Jest to podstawowa wersja
makra, która w atwy sposób mo e by dostosowana do Twoich indywidualnych wymaga .
Jednym ze sposobów mo e by rejestrowanie makra podczas modyfikowania wykresu
i nast pnie u ywanie takiego kodu jako wzorca w swoich procedurach.
Swoj drog , dalej w tym rozdziale omówi konstrukcj With End-With, która znakomicie
u atwia prac z obiektami, oszcz dza sporo stukania w klawiatur i znakomicie
przyczynia si do zwi kszenia przejrzysto ci kodu.
Kup książkę Poleć książkę
Rozdzia 14: Przyk ady i techniki programowania w j zyku VBA
231
Je eli musisz napisa makro VBA, którego zadaniem b dzie przetwarzanie wykresów,
musisz zapozna si z kilkoma wa nymi okre leniami. Wykres osadzony (embedded chart)
na arkuszu to obiekt typu ChartObject. Obiekt ChartObject mo esz aktywowa podobnie
jak aktywujesz arkusz. Polecenie przedstawione poni ej aktywuje obiekt ChartObject
o nazwie Wykres 1.
ActiveSheet.ChartObjects("Wykres 1").Activate
Po aktywowaniu danego wykresu mo esz si do niego odwo ywa w kodzie VBA za
pomoc obiektu ActiveChart. Je eli wykres znajduje si na osobnym arkuszu wykresu,
staje si wykresem aktywnym w chwili, kiedy aktywujesz arkusz wykresu.
Obiekt ChartObject jest równie obiektem typu Shape, co mo e by nieco myl ce.
W rzeczywisto ci, kiedy Twój kod VBA tworzy wykres, ca a operacja rozpoczyna si
od utworzenia nowego obiektu Shape (kszta t). Wykres mo esz równie aktywowa
poprzez zaznaczenie obiektu Shape przechowuj cego wykres.
ActiveSheet.Shapes("Wykres 1").Select
W moich programach wol u ywa obiektu ChartObject, dzi ki czemu nie mam
adnych w tpliwo ci, e pracuj z wykresami.
Kiedy klikasz wykres osadzony lewym przyciskiem myszy, Excel zaznacza obiekt znajduj cy
si wewn trz obiektu ChartObject. Je eli chcesz zaznaczy sam obiekt ChartObject, powiniene
klikaj c wykres, trzyma wci ni ty klawisz Ctrl.
Modyfikowanie typu wykresu
A teraz przeczytasz zdanie, które mo e Ci nieco zdezorientowa : obiekty ChartObject
spe niaj rol kontenerów dla obiektów Chart. Je li masz jakie w tpliwo ci, powiniene
to zdanie kilka razy spokojnie przeczyta i wtedy na pewno wszystko stanie si jasne.
Aby zmodyfikowa wykres za pomoc VBA, nie musisz tego wykresu aktywowa . Metoda
Chart mo e zwraca wykres przechowywany w kontenerze ChartObject. Nadal niejasne?
Procedury przedstawione poni ej daj taki sam efekt zmieniaj typ wykresu o nazwie
Wykres 1 na wykres powierzchniowy. Pierwsza procedura najpierw aktywuje wykres
i nast pnie pracuje z aktywnym wykresem. Druga procedura nie aktywuje wykresu,
a zamiast tego wykorzystuje w a ciwo Chart, która zwraca obiekt Chart zawarty
w kontenerze ChartObject.
Sub ModifyChart1()
ActiveSheet.ChartObjects("Wykres 1").Activate
ActiveChart.Type = xlArea
End Sub
Sub ModifyChart2()
ActiveSheet.ChartObjects("Wykres 1").Chart.Type = xlArea
End Sub
Kup książkę Poleć książkę
Cz III: Podstawy programowania
232
Przechodzenie w p tli
przez elementy kolekcji ChartObjects
Procedura przedstawiona poni ej wprowadza zmiany do wszystkich wykresów
osadzonych na aktywnym arkuszu. Procedura wykorzystuje p tl For Each-Next
do przechodzenia kolejno przez wszystkie obiekty kolekcji ChartObjects i dla ka dego
obiektu Chart zmienia jego w a ciwo Type.
Sub ChartType()
Dim cht As ChartObject
For Each cht In ActiveSheet.ChartObjects
cht.Chart.Type = xlArea
Next cht
End Sub
Makro przedstawione poni ej wykonuje tak sam operacj , ale na wszystkich arkuszach
wykresów w aktywnym skoroszycie.
Sub ChartType2()
Dim cht As Chart
For Each cht In ActiveWorkbook.Charts
cht.Type = xlArea
Next cht
End Sub
Modyfikowanie w a ciwo ci wykresu
Procedura przedstawiona ni ej zmienia czcionk legendy wykresu dla wszystkich
wykresów osadzonych na aktywnym arkuszu. Makro wykorzystuje p tl For-Next
do przetwarzania wszystkich obiektów ChartObject.
Sub LegendMod()
Dim chtObj As ChartObject
For Each chtObj In ActiveSheet.ChartObjects
With chtObj.Chart.Legend.Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 12
End With
Next chtObj
End Sub
Zwró uwag na fakt, e obiekt Font jest zawarty w obiekcie Legend, który jest zawarty
w obiekcie Chart, który z kolei jest zawarty w kolekcji ChartObjects. Czy teraz rozumiesz,
dlaczego to wszystko jest nazywane hierarchi obiektów?
Kup książkę Poleć książkę
Rozdzia 14: Przyk ady i techniki programowania w j zyku VBA
233
Zmiana formatowania wykresów
Ten przyk ad odnosi si do kilku ró nych typów formatowania aktywnego wykresu.
Utworzy em to makro, rejestruj c moje poczynania podczas formatowania wykresu.
Nast pnie oczy ci em nieco uzyskany kod poprzez usuni cie zb dnych wierszy.
Sub ChartMods()
ActiveChart.Type = xlArea
ActiveChart.ChartArea.Font.Name = "Calibri"
ActiveChart.ChartArea.Font.FontStyle = "Regular"
ActiveChart.ChartArea.Font.Size = 9
ActiveChart.PlotArea.Interior.ColorIndex = xlNone
ActiveChart.Axes(xlValue).TickLabels.Font.Bold = True
ActiveChart.Axes(xlCategory).TickLabels.Font.Bold = _
True
ActiveChart.Legend.Position = xlBottom
End Sub
Przed wykonaniem tego makra musisz aktywowa wykres. Wykresy osadzone mo esz
aktywowa poprzez ich klikni cie lewym przyciskiem myszy. Aby aktywowa wykres
na arkuszu wykresu, kliknij kart arkusza.
Aby upewni si , e wykres jest zaznaczony, mo esz w kodzie procedury umie ci
polecenie, które b dzie sprawdza o, czy wykres jest aktywny. Poni ej znajdziesz kod
zmodyfikowanej procedury, która je eli wykres nie jest aktywny wy wietla na
ekranie odpowiedni komunikat i ko czy dzia anie.
Sub ChartMods2()
If ActiveChart Is Nothing Then
MsgBox "Aktywuj wykres!"
Exit Sub
End If
ActiveChart.Type = xlArea
ActiveChart.ChartArea.Font.Name = "Calibri"
ActiveChart.ChartArea.Font.FontStyle = "Regular"
ActiveChart.ChartArea.Font.Size = 9
ActiveChart.PlotArea.Interior.ColorIndex = xlNone
ActiveChart.Axes(xlValue).TickLabels.Font.Bold = True
ActiveChart.Axes(xlCategory).TickLabels.Font.Bold = _
True
ActiveChart.Legend.Position = xlBottom
End Sub
Poni ej znajdziesz kolejn wersj procedury, która wykorzystuje konstrukcj With-End
With do zaoszcz dzenia klepania w klawiatur i (co wa niejsze) zwi kszenia optymalno ci
i przejrzysto ci kodu. I znowu wyskakujemy nieco przed orkiestr , ale je eli chcesz, mo esz
ju teraz przeskoczy par stron do przodu i przeczyta opis polecenia With-End With.
Sub ChartMods3()
If ActiveChart Is Nothing Then
MsgBox "Aktywuj wykres!"
Exit Sub
End If
Kup książkę Poleć książkę
Cz III: Podstawy programowania
234
With ActiveChart
.Type = xlArea
.ChartArea.Font.Name = "Calibri"
.ChartArea.Font.FontStyle = "Regular"
.ChartArea.Font.Size = 9
.PlotArea.Interior.ColorIndex = xlNone
.Axes(xlValue).TickLabels.Font.Bold = True
.Axes(xlCategory).TickLabels.Font.Bold = True
.Legend.Position = xlBottom
End With
End Sub
No có & w zakresie zastosowania VBA do przetwarzania wykresów uda o Ci si w tym
rozdziale jedynie nieco lizn podstawowe elementy tego rozbudowanego zagadnienia.
Temat jest niezwykle szeroki, ale mam nadziej , e to, czego dowiedzia e si w tym
rozdziale, pobudzi o Twoj ciekawo i nakierowa o poszukiwania we w a ciwym
kierunku.
Jak przyspieszy dzia anie kodu VBA?
VBA jest szybki, ale nie zawsze wystarczaj co szybki (inna sprawa, e programy
komputerowe nigdy nie s wystarczaj co szybkie, przynajmniej w opinii wi kszo ci
u ytkowników). W tym podrozdziale poka kilka trików i sztuczek, które b dziesz
móg wykorzysta do przyspieszenia dzia ania swoich makr.
Wy czanie aktualizacji ekranu
Kiedy uruchomisz makro, mo esz wygodnie wyci gn si na fotelu i ze spokojem
obserwowa na ekranie jego post py. Cho takie post powanie mo e by do pewnego
czasu ciekawe, to jednak, kiedy makro zostanie ju napisane i przetestowane, wy wietlanie
bie cych wyników dzia ania mo e by irytuj ce i niepotrzebnie zwalnia dzia anie
makra. Na szcz cie, Excel pozwala na wy czenie aktualizacji ekranu na czas dzia ania
makra, co mo e znacz co przyspieszy jego dzia anie. Aby wy czy aktualizacj ekranu,
powiniene u y polecenia:
Application.ScreenUpdating = False
Je eli chcesz, aby u ytkownicy widzieli, co si dzieje na ekranie podczas dzia ania
makra, powiniene w czy aktualizacj ekranu za pomoc polecenia:
Application.ScreenUpdating = True
Aby zademonstrowa ró nic w szybko ci dzia ania, powiniene uruchomi makro
przedstawione poni ej, którego zadaniem jest wype nianie liczbami du ego zakresu
komórek.
Sub FillRange()
Dim r as Long, c As Long
Dim Number as Long
Kup książkę Poleć książkę
Rozdzia 14: Przyk ady i techniki programowania w j zyku VBA
235
Number = 0
For r = 1 To 50
For c = 1 To 50
Number = Number + 1
Cells(r, c).Select
Cells(r, c).Value = Number
Next c
Next r
End Sub
Procedura zaznacza ka d komórk zakresu i wpisuje do niej kolejn liczb . Teraz na
pocz tku procedury wstaw polecenie przedstawione poni ej i ponownie uruchom
procedur .
Application.ScreenUpdating = False
Z pewno ci zauwa y e , e zakres zosta wype niony znacznie szybciej, a rezultaty nie
by y widoczne na ekranie, a do zako czenia dzia ania procedury i automatycznego
przywrócenia aktualizacji ekranu.
Kiedy pracujesz nad testowaniem procedury i wyszukiwaniem b dów w kodzie, dzia anie
programu mo e nagle zosta przerwane, bez automatycznego przywrócenia aktualizacji
ekranu (tak, te mi si to zdarza& ). W takiej sytuacji okno Excela pozostaje martwe
i wydaje si , e program nie reaguje na Twoje pro by i gro by. Rozwi zanie tego problemu
jest proste przejd do okna edytora VBE i w oknie Immediate wpisz polecenie:
Application.ScreenUpdating = True
Wy czenie automatycznego
przeliczania skoroszytu
Za ó my, e masz skoroszyt zawieraj cy wiele z o onych formu . Mo esz znacz co
przyspieszy dzia anie makra, je eli na czas jego realizacji prze czysz Excel w tryb
r cznego przeliczania skoroszytu. Kiedy makro zako czy dzia anie, powiniene ponownie
prze czy Excel w tryb automatycznego przeliczania skoroszytu.
Polecenie przedstawione poni ej prze cza Excel w tryb r cznego przeliczania skoroszytu.
Application.Calculation = xlCalculationManual
Aby przywróci tryb automatycznego przeliczania skoroszytu, u yj polecenia:
Application.Calculation = xlCalculationAutomatic
Je eli kod VBA Twojego makra wykorzystuje wyniki dzia ania formu arkuszowych,
pami taj, e prze czenie Excela w tryb r cznego przeliczania arkusza oznacza, i warto ci
komórek nie zostan zaktualizowane, a do momentu, kiedy jawnie nie poprosisz
Excela, aby to zrobi .
Kup książkę Poleć książkę
Cz III: Podstawy programowania
236
Wy czanie irytuj cych ostrze e
Jak wiesz, makra mog automatycznie wykonywa ca e mnóstwo ró nych operacji. W wielu
przypadkach mo esz po prostu uruchomi makro i spokojnie wybra si do kuchni
i zaparzy fili ank swojej ulubionej kawy. Jednak niektóre operacje wykonywane przez
Excel mog spowodowa wy wietlenie na ekranie komunikatu, którego potwierdzenie
wymaga interakcji ze strony u ytkownika. Je li na przyk ad Twoje makro próbuje usun
arkusz, na którym znajduj si niepuste komórki, dzia anie makra zostanie automatycznie
zatrzymane, na ekranie pojawi si komunikat przedstawiony na rysunku 14.4 i Excel
b dzie oczekiwa na Twoj reakcj . Obecno tego typu komunikatów oznacza, e nie
mo esz pozostawi Excela bez nadzoru na czas dzia ania makra& dopóki nie poznasz
pewnego triku.
Rysunek 14.4.
Mo esz naka-
za Excelowi
zawieszenie
wy wietlania
takich komuni-
katów podczas
dzia ania makra
Oto ca a sztuczka: aby unikn wy wietlania takich komunikatów z ostrze eniami,
w kodzie procedury VBA umie polecenie:
Application.DisplayAlerts = False
Excel wykonuje domy lne operacje dla tego typu komunikatów. Podczas usuwania
arkusza domy ln operacj jest Delete (co w a nie przed chwil zobaczy e ). Je eli nie
jeste pewien, jaka operacja jest domy lna, przeprowad test i przekonaj si sam.
Kiedy procedura ko czy dzia anie, Excel automatycznie nada w a ciwo ci DisplayAlerts
warto True (czyli przywróci jej normalny stan). Je eli chcesz przywróci wy wietlanie
komunikatów przed zako czeniem dzia ania procedury, powiniene u y w kodzie
polecenia:
Application.DisplayAlerts = True
Upraszczanie odwo a do obiektów
Jak ju sam zd y e si zorientowa , odwo ania do obiektów mog by bardzo
rozbudowane. Przyk adowo pe ne, kwalifikowane odwo anie do obiektu Range mo e
wygl da nast puj co.
Workbooks("MójSkoroszyt.xlsx").Worksheets("Arkusz1") _
.Range("StawkaProwizji")
Kup książkę Poleć książkę
Rozdzia 14: Przyk ady i techniki programowania w j zyku VBA
237
Je eli Twoje makro cz sto korzysta z takiego zakresu, powiniene rozwa y utworzenie
zmiennej obiektowej za pomoc polecenia Set. Przyk adowo polecenie przedstawione
poni ej przypisuje obiekt Range do zmiennej obiektowej o nazwie Rate.
Set Rate = Workbooks("MójSkoroszyt.xlsx").Worksheets("Arkusz1") _
.Range("StawkaProwizji")
Po zdefiniowaniu zmiennej obiektowej mo esz zamiast d ugiego odwo ania u ywa
nowo utworzonej zmiennej obiektowej. Aby na przyk ad zmieni warto komórki
o nazwie StawkaProwizji, mo esz u y polecenia:
Rate.Value = .085
Jak wida , jest to znacznie atwiejsze do wpisania (i zrozumienia) ni to samo polecenie
w pe nej postaci.
Workbooks("MójSkoroszyt.xlsx").Worksheets("Arkusz1") _
.Range("StawkaProwizji").Value = .085
Oprócz upraszczania kodu, zastosowanie zmiennych obiektowych powoduje równie
znaczne zwi kszenie szybko ci dzia ania kodu Twojego makra. Wiele razy widzia em
ju makra, które po utworzeniu zmiennych obiektowych zwi kszy y szybko dzia ania
nawet dwukrotnie.
Deklarowanie typów zmiennych
Zazwyczaj nie musisz si martwi o typ danych, który przypisujesz do zmiennej. Excel
potrafi si tym doskonale zaj . Je eli masz zmienn o nazwie MyVar, mo esz do niej
przypisa dowoln liczb , a pó niej, w dalszej cz ci procedury, mo esz do tej samej
zmiennej przypisa na przyk ad ci g tekstu.
Je eli chcesz, aby Twoje procedury VBA dzia a y tak szybko, jak to tylko mo liwe (i aby
przy okazji unikn kilku potencjalnych i naprawd paskudnych problemów), powiniene
zawsze poinformowa Excel o tym, jakie typy danych b d przypisywane do poszczególnych
zmiennych. Takie post powanie jest nazywane deklarowaniem typów zmiennych (wi cej
szczegó owych informacji na ten temat znajdziesz w rozdziale 7.). Powiniene jak
najszybciej wyrobi sobie nawyk deklarowania wszystkich zmiennych, których u ywasz
w swoich programach.
Ogólnie rzecz bior c, powiniene zawsze u ywa takich typów danych, które wystarcz
do obs ugi Twoich danych przy wykorzystaniu jak najmniejszej liczby bajtów pami ci.
Kiedy VBA przetwarza dane, szybko dzia ania programu zale y od liczby bajtów, jakie
VBA ma do przerobienia . Innymi s owy, im mniej bajtów zajmuj dane, tym szybciej
VBA mo e je przetwarza . Wyj tkiem od tej regu y s dane typu Integer je eli
szybko dzia ania programu jest czynnikiem krytycznym, powiniene zawsze stosowa
dane typu Long.
Je eli u ywasz zmiennych obiektowych (takich jakie opisywa em w poprzednim
podrozdziale), mo esz zadeklarowa tak zmienn jako zmienn okre lonego typu
obiektowego. A oto przyk ad takiej deklaracji.
Kup książkę Poleć książkę
Cz III: Podstawy programowania
238
Dim Rate as Range
Set Rate = Workbooks("MójSkoroszyt.xlsx").Worksheets("Arkusz1") _
.Range("StawkaProwizji")
Zastosowanie struktury With-End With
Czy chcesz ustawi szereg w a ciwo ci wybranego obiektu? Twój kod b dzie dzia a
znacznie szybciej, gdy u yjesz struktury With-End With. Dodatkow zalet zastosowania
tej struktury jest znaczne zwi kszenie czytelno ci kodu.
Fragment kodu przedstawiony poni ej nie wykorzystuje struktury With-End With.
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.WrapText = True
Selection.Orientation = 0
Selection.ShrinkToFit = False
Selection.MergeCells = False
A teraz ten sam fragment kodu, ale zapisany z u yciem struktury With-End With.
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Je eli przedstawiona struktura wydaje Ci si znajoma, jest tak prawdopodobnie dlatego,
e rejestrator makr u ywa struktury With-End With w ka dej sytuacji, w której jest to mo liwe,
a poza tym struktura taka pojawi a si ju wcze niej w kilku przyk adach w tym rozdziale.
Kup książkę Poleć książkę
Skorowidz
poprawianie, Patrz: odpluskwianie
A
programowania, 191, 203, 204, 205
Add-In, 34 kontekstem operacji, 204
aplikacja logiczny, 204
bezpiecze stwo, 384 typ danych, 204
jako niezale ny program, 33 warunki graniczne, 204
oparta na makrach, 32 wersja programu, 204
arkusz, 34 przechwytywanie, 195
aktywacja, 180 rozpoznawanie, 200
aktywny, 169 sk adni, 64, 136, 204
dodawanie, 61 Subscript out of range, 376
nazwa, 169 wykonania, 191, 196
okno kodu, 180 zakresu, 127
wykresu, 72 zamierzony, 201
Auto Data Tips, 65 breakpoint, Patrz: punkt przerwania
Auto Indent, 65
Auto List Members, 64, 78, 80, 144, 150
C
Auto Quick Info, 65
Auto Syntax Check, 64
Chart, 34, 70, 71, 231, 232
automatyzacja, 30
Charts, 72
ci g znaków, 142, 147, 249
collection, Patrz: kolekcja
B
Comment Block, 108
biblioteka obiektów, Patrz: obiekt biblioteka Custom UI Editor for Microsoft Office, 325
Bie cy obszar, 220 czas, 147, 186
blok systemowy, 156
instrukcji jako komentarz, 108 zapis liczbowy, 187
tekstu, 31 zegarowy, 119
b d, 76, 146 czcionka, 134
#WARTO !, 352
czasu wykonania, 116
D
eliminowanie, 215
graniczny, 204
dane
ignorowanie, 197, 199
kopiowanie, 185
kod, 144, 146
poprawno , 185
kompilacji, 60, 110
tekstowe, 118
komunikat, 146, Patrz: komunikat o b dzie
typ, 82, 107, 110, 237
numer, 200
b dy, 204
obs uga, 192, 195, 196, 197, 199
Boolean, 111, 134
Kup książkę Poleć książkę
Excel 2013 PL. Programowanie w VBA dla bystrzaków
390
dane element graficzny, 261
typ embedded chart, Patrz: wykres osadzony
Byte, 111 etykieta, 125, 154
Currency, 111 Excel
Date, 111, 119 ustawienia, 227, 228
definiowanie, 95 wersja, 35, 229, 387
domy lny, 111
Double, 111
F
Integer, 111
Long, 111
folder
Object, 111
nazwa, 146
predefiniowany, 111
zaufany, 23
przydzia dynamiczny, 110
formant, 259, 260, 275
Single, 111
ActiveX, 88
String, 111
CheckBox, 261, 279
Variant, 111, 132, 134
ComboBox, 261, 280, 303
wklejanie, 185
CommandButton, 261, 265, 281
data, 119, 146, 147
dodawanie, 276, 277
cz , 146
etykieta, 278
format, 120
Frame, 261, 281, 292
krótka, 120
grafika, 278
obliczenia, 146
Image, 261, 282
systemowa, 146
jakoo kontener, 292
zamiana na liczb seryjn , 146
klawisz skrótu, 292
debugger, 216
kolejno tabulacji, 291
debugowanie, Patrz: odpluskwianie
Label, 261, 283, 287, 310
Default to Full Module View, 66
ListBox, 261, 283, 284, 303
deklaracja, 58
MultiPage, 261, 284, 292, 294, 315, 316
Deweloper, 39
nawigacja za pomoc klawiatury, 291
dodatek, 34, 48, 54, 361, 362, 363
nazwa, 264, 278
Analysis ToolPak, 208, 361, 363
obiektu CommandBar, 331, 332
bezpiecze stwo, 369
OptionButton, 261, 267, 276, 280, 285, 309
modyfikowanie, 371
pozycja w oknie, 278, 289, 290
obiekt UserForm, 362
RefEdit, 261, 286
opis, 368
rozmiar, 278, 290
otwieranie, 363, 369
ScrollBar, 261, 286
Power Utility Pak, 361
SpinButton, 261, 287, 310
Solver, 361
TabStrip, 261, 288
tworzenie, 32, 364, 365, 369
TextBox, 261, 288, 296, 310
udost pnianie, 370
t o, 278
Dostosowywanie Wst ki, 256
ToggleButton, 261, 289
Drag-and-Drop Text Editing, 66
warto , 278
drzewo, 53, 54
widoczno , 278
w a ciwo , 261, 262, 277, 278
E
Accelerator, 278, 279, 285
AutoSize, 278, 288
edytor VBE, Patrz: VBE
BackColor, 278
ekran
BackStyle, 278
aktualizacja, 235, 377
wy czenie aktualizacji, 234 BeginGroup, 332
Kup książkę Poleć książkę
Skorowidz
391
BorderStyle, 282 formu a, 134
BuiltIn, 332 nazwa, 44
Cancel, 281 odpowiednik angielski, 44
Caption, 278, 282, 332 tablicowa, 355
ControlSource, 280, 284, 285, 287, 288 funkcja, 33, 58, 59, 81, 141, 344,
Default, 281 Patrz te : metoda
Enabled, 332 Abs, 146
FaceID, 332 argument, 82
GroupName, 285 argumenty, 345, 349
Height, 278 opcjonalne, 351
IntegralHeight, 284, 288 opis, 360
LargeChange, 287 arkuszowa, 343, 358, 375
Left, 278 ograniczenia, 344
ListCount, 303 Array, 146
ListIndex, 303, 305 bezargumentowa, 346
ListRows, 280 Choose, 146
ListStyle, 280, 284 Chr, 146
Max, 287 CurDir, 146
MaxLength, 288 Date, 142, 146
metody, 303 DateAdd, 146
Min, 287 DateDiff, 146
MultiLine, 288 DatePart, 146
MultiSelect, 284, 303, 306 DateSerial, 146
Name, 278 DateValue, 146
OnAction, 332 Day, 146
Picture, 278, 282 Dir, 146
PictureSizeMode, 282 D , 142
RowSource, 280, 284 dwuargumentowa, 348
ScrollBars, 289 Err, 146
Selected, 303 Error, 146
SmallChange, 287 Exp, 146
Style, 280, 285 FileLen, 143, 146
TextAlign, 278, 289 Fix, 146
ToolTipText, 333 Format, 146
Top, 278 GetOpenFilename, 250
Value, 278, 280, 284, 285, 287, 303 GetSetting, 146
Visible, 278, 332 Hour, 146
Width, 278 InputBox, 144, 146, 149, 154, 195, 225, 242,
WordWrap, 289 247, 248, 249
zmiana, 277 argumenty, 248
zaznaczanie, 290 pobranie liczby, 249
format InStr, 146
XLA, 362 InStrRev, 146
XLAM, 362 Int, 146
xlsm, 45, 376 IsArray, 146
XLSM, 362 IsDate, 146
xlsx, 45, 376 IsEmpty, 146
formularz UserForm, Patrz: UserForm IsError, 146
IsMissing, 146
Kup książkę Poleć książkę
Excel 2013 PL. Programowanie w VBA dla bystrzaków
392
funkcja wbudowana
IsNull, 147 j zyka VBA, 141, 142, 144
IsNumeric, 147, 193 podpowiedzi, 144
jednoargumentowa, 346 programu Excel, 141, 145, 150, 151
LARGE, 148 Weekday, 147
LBound, 147 w asna, Patrz: funkcja u ytkownika
LCase, 147, 270 wykrywanie b dów, 352
Left, 147 WYSZUKAJ.PIONOWO, 149
Len, 142, 147 wy wietlanie informacji, 65
MAX, 148 wywo anie z procedury Sub, 352
Mid, 147 wywo ywanie, 82, 89, 90
MIN, 148 Year, 147
Minute, 147 Z.WIELKIEJ.LITERY, 270
MOD, 122, 151
Month, 147
G
MonthName, 143
MsgBox, 74, 142, 144, 147, 149, 206, 242, 247
generator liczb pseudolosowych, 353
argumenty, 242
godzina, 146
przyciski, 245
d uga, 120
nazwa, 83, 112
format, 119
Now, 142, 147
Graphical User Interface, Patrz: GUI
opakowuj ca, 353
GUI, 259
opis, 358
pasywna, 344
H
PMT, 148
Proper, 270
has o, 54, 362, 369, 376, 384
Replace, 147
RGB, 135, 147
I
Right, 147
Rnd, 147
identyfikator zadania, 144
Second, 147
instrukcja
Shell, 144, 147
ElseIf, 157
Space, 147
Exit For, 163
Split, 147
Exit Sub, 154
Sqr, 147
GoTo, 125, 153, 154, 155
StrConv, 270
If-Then, Patrz: struktura If-Then
String, 147
On Error, 195, 196, 197
Time, 142, 147
On Error GoTo, 197
Timer, 147
On Error Resume, 197, 198
TimeSerial, 147
On Error Resume Next, 189, 197, 199, 215
TimeValue, 147, 187
Option Explicit, 60, 64, 112
Trim, 147
przypisania, 120
tworzenie, 31, 345
ReDim, 124
TypeName, 144, 147
Resume, 197, 198
UBound, 147
Resume Next, 197
UCase, 147, 257, 270
Step, 163
u ytkownika, 141, 151
warunkowa, 95
Val, 147
IntelliSense, 64
VLOOKUP, 149
interfejs u ytkownika graficzny, Patrz: GUI
Kup książkę Poleć książkę
Skorowidz
393
warto ci wprowadzanie, 225
J
zaznaczanie, 219, 221
j zyk ca ego wiersza, 221
makr, 30 ca ej kolumny, 221
programowania, 30 do ko ca kolumny, 220, 377
XLM, 36 do ko ca wiersza, 220
XML, 325 komunikat
o b dzie, 146, 174
wymagaj cy potwierdzenia, 236
K
komunikatem, 147
karta kontener, 34
Deweloper, 363, Patrz: Deweloper kontrolka formularza, 87, 88
DODATKI, 329 kszta t, 34, 84, 87, 88
Plik, 363
zawieraj ca formanty, 261
L
klawisz Esc, 219
kod liczba
ANSI, 146 ca kowita, 110
spaghetti, 155 cz ca kowita, 146
kolekcja, 34, 71 e, 146
Addins, 361 formatowanie, 132
ChartObject, 232 pseudolosowa, 353
CommandBars, 330 rzeczywista, 110
element, 71 warto bezwzgl dna, 146
metoda, 77 lista, 261, 303
przegl danie, 168 element, 304, 306
Sheets, Patrz: Sheets rozwijana, 261, 280
zakresów, 226 sortowanie, 356
kolor, 135 logarytm naturalny, 146
motywu, 135 lokalizacja zaufana, 23, 46, 47
RGB, 147
standardowy, 135
TintAndShade, 135
vbBlack, 135
a cuch znaków, 110, 118, 119, 136, 142, 354
vbBlue, 135
o sta ej d ugo ci, 119
vbCyan, 135
o zmiennej d ugo ci, 119
vbGreen, 135
porównywanie, 355
vbMagenta, 135
vbRed, 135
M
vbWhite, 135
vbYellow, 135
makro, 30, 60, 82, Patrz te : procedura Sub,
wype nienia, 135
program
kolumny ukrywanie, 61
bezpiecze stwo, 45, 47
komentarz, 44, 107, 108, 216, 385
instrukcje nadmiarowe, 44
komórka, 73
klawisz skrótu, 100
adresu wprowadzanie, 261
kod, 42
format, 185
lista, 63
niepusta, 223
lokalizacja, 101
pusta, 218
modyfikacja, 44
Kup książkę Poleć książkę
Excel 2013 PL. Programowanie w VBA dla bystrzaków
394
makro modu , 54, 154
nazwa, 100 Code, 263
rejestrator, 58, 61, 82, 93, 95, 98, 375 dodawanie, 55, 94
ograniczenia, 95 limit znaków, 58
opcje, 100 przewijanie w oknie, 66
wydajno , 101, 218 sekcja Declarations, 115
rejestrowanie, 31, 41, 55, 88, 93, 95 tworzenie, 57, 58
w trybie odwo a bezwzgl dnych, 96 usuwanie, 55
w trybie odwo a wzgl dnych, 97
testowanie, 272
N
ustawienia, 23, 46
menu, 331
narz dzie Object Browser, Patrz: Object Browser
podr czne, 329
Cell, 335
O
Excel 2003, 338
Excel 2013, 336
obiekt, 34, 69
modyfikacja, 334
ActiveChart, 231
resetowanie, 334
Addin, 70
wy czanie, 337
Add-In, Patrz: Add-In
wy wietlanie, 329
Application, 34, 70, 72
metoda, 35, 127, Patrz te : funkcja
biblioteka, 79
Add, 77
Chart, Patrz: Chart
AddChart, 230
ChartObject, 231
AddChart2, 229, 230
CommandBar, 329, 330, 338
Areas, 226
formanty, 331, 332
argument, 76
CommandBars, 255
Cells, 129
Comment, 70
Clear, 138
eksportowanie, 56
ClearContents, 76 Err, 200
Copy, 138, 219 FileDialog, 254
Delete, 139 hierarchia, 34, 69
End, 221 Hyperlink, 70
ExecuteMso, 255 importowanie, 56
Export, 318 kontener, Patrz: kontener
FileDialog, 242 metoda, 74, 76, 80, Patrz: metoda
GetOpenFilename, 242, 251 Name, 70
argumenty, 251 numer indeksu, 72
GetSaveAsFilename, 242, 253 odwo anie, Patrz: odwo anie
InputBox, 242, 249 okno Code, 53
Intersect, 224 PageSetup, 70
PivotTable, 70, Patrz: PivotTable
OnTime, 187, 188
Range, 70, Patrz: Range
Paste, 138
Shape, 231
SaveCopyAs, 179
Ten_skoroszyt, 54
Select, 137
UserForm, 259
SpecialCells, 223, 224, 273
VBProject, 70
miesi c, 143, 147, 355
Window, 70
model obiektowy, 34, 69, 110
w a ciwo , Patrz: w a ciwo
Workbook, Patrz: Workbook
Kup książkę Poleć książkę
Skorowidz
395
Worksheet, Patrz: Worksheet implikacji, 122
WorksheetFunction, 70, 145 koniunkcji, 122
wskazywanie, 71 negacji, 122
zakresu, Patrz: Range Not, 122
zdarzenie, Patrz: zdarzenie Or, 122
Object Browser, 78, 79 równowa no ci, 122
object-oriented programming, Patrz: XoR, 122
programowanie zorientowane obiektowo logiczny:, 122
obs uga techniczna, 33 mno enia, 121
odpluskwianie, 33, 54, 204, 205, 208, 273 Mod, 121, 122, 151
metody, 205 modulo, 121
narz dzia, 209 odejmowania, 121
odwo anie pot gowania, 121
bezwzgl dne, 94, 96, 133 priorytet, 122
do obiektu, 236 znaku równo ci, 109
do zakresu, 129, 130 Option Explicit, 215, 384
jednoznaczne, Patrz: odwo anie pe ne
pe ne, 73, 74
P
upraszczanie, 73
w pe ni kwalifikowane, Patrz: odwo anie pe ne
pasek
wzgl dne, 94, 96, 97, 130
post pu zadania, Patrz: wska nik
Office Compatibility Pack, 37
post pu zadania
okno
przewijania, 261, 286, 289
dialogowe, 241, 253, 258
szybkiego dost pu, 272
dostosowywanie, 244
umieszczanie procedur, 299, 328
pobieranie odpowiedzi, 243
p tla, 95, 162, Patrz te : struktura
u ytkownika, Patrz: UserForm
Do-Until, 153, 154, 168
wbudowane, 242, 254
Do-While, 153, 154, 167
Wstawianie funkcji, 358
For Each-Next, 168, 222, 232
wy wietlanie, 243
For-Next, 153, 154, 162, 232
Immediate, 84
czas wykonania, 165
Properties, 261
z instrukcj Exit For, 163
Toolbox, 260
z instrukcj Step, 163
wprowadzania danych, 146
zagnie d ona, 165
OOP, Patrz: programowanie zorientowane
pierwiastek kwadratowy, 147
obiektowo
PivotTable, 34
operator, 121
plik
dodawania, 121
liczba bajtów, 146
dzielenia, 121
nazwa, 146, 250
dzielenia ca kowitego, 121
PERSONAL.XLSB, 54, 101
konkatenacji ci gów znaków, 121, 122, 207
cie ka, 146
kropki, 72, 73
wielko , 143
Like, 355
pluskwa, Patrz: b d programowania
logiczny
podprogram, 59
alternatywy, 122
Poka podzia y stron, 227
alternatywy wykluczaj cej, 122
pokr t o, 261, 287, 310
And, 122
pole
Eqv, 122
etykiety, 261, 283
Imp, 122
grupy, 261, 281
Kup książkę Poleć książkę
Excel 2013 PL. Programowanie w VBA dla bystrzaków
396
pole wywo anie, Patrz: procedura Sub
karty, 261, 288 substandardowa, 33
kombi, 261, 280 udost pnienie u ytkownikowi, 299
listy, 261, 280, 283, 303 uruchamianie, 60
obrazu, 261, 282 wy wietlaj cea okno dialogowe, 298
opcji, 261, 285, 296 Procedure Separator, 66
strony, 261, 284 program, Patrz: makro
tekstowe, 261, 288, 289, 296, 310 wykonywalny, 147
wyboru, 261, 279 wykonywanie krokowe, 211, 212
zakresu, 261, 286 wymuszanie zatrzymania, 207
polecenie programowanie
Add Watch, 213 przyk ady, 217
Debug.Print, 208, 384 strukturalne, 154
DisplayAlerts, 236, 378 zorientowane obiektowo, 69
MsgBox, 384 projekt, 54
On Error Resume Next, 223 przycisk, 84, 87, 331
Print, 212 na pasku narz dzi Szybki dost p, 31, 84
Randomize, 353 na Wst ce, 31
Set, 237 opcji, 267
procedura polecenia, 261, 281
argumenty, 82 polece , 265
dysfunkcyjna, 34 prze cznika, 261, 289
Function, Patrz: funkcja tworzenie, 31
obs ugi b dów wstawianie, 87, 88
wbudowana, 196, 197 pu apka, Patrz: punkt przerwania
w asna, 196 punkt przerwania, 210, 211, 352
obs ugi zdarzenia, 173, 268 usuwanie, 210
aktywacja arkusza, 180 wstawianie, 209
aktywacja skoroszytu, 181
Open, 176
R
tworzenie, 173, 175
obs uguj ce zdarzenie, 300 Range, 34, 73, 127, 129, 138, 217, 226
separator, 66
metoda, 137
Sub, 33, 44, 58, 59, 60, 81, 82, 173,
rata po yczki, 148
Patrz te : makro
rejestr Windows, 146
argumenty, 85, 87
rejestrator makr, Patrz: makro rejestrator
nazwa, 83, 100
Require Variable Declaration, 64
skrót klawiszowy, 41, 47, 86, 87, 271
Require Variable Definition, 112
tworzenie, 84
RibbonX, 321
uruchamianie, 83
runtime error, Patrz: b d czasu wykonania
uruchamianie bezpo rednie, 85
uruchamianie w oknie dialogowym
S
Makro, 85
uruchamianie z poziomu innych
Sheets, 72
procedur, 89
skoroszyt, 34, 54
uruchamianie za pomoc przycisków
dezaktywacja, 183
i kszta tów, 87, 88
konwersja na plik dodatku, 48
uruchamianie za pomoc skrótów
kopia zapasowa, 179
klawiszowych, 86
Kup książkę Poleć książkę
Skorowidz
397
makr osobistych, 47, 54, 101 struktura, Patrz te : p tla
otwarty, 201 End If, 156
przekszta canie na dodatek, 364, 367 For Each-Next, 168, 222, 232
testowanie, 367 If-Then, 153, 155, 156, 157, 158, 199
tryb obliczania If-Then-Else, 154, 155, 156, 157
automatyczny, 118 Select Case, 153, 154, 159, 228
prze czanie, 228 zagnie d ona, 160
r czny, 118, 235, 377 With-End With, 233, 238, 377
XLSM, 361 suwak, 261, 286
zapisywanie, 45 syntezator mowy, 354
zawieraj cy makro, 45 system pomocy, 53, 78, 379
skrót klawiszowy, 86 formanty, 279
s owo kluczowe, 109, 112 funkcje wbudowane, 144
Call, 89 zakres, 129
Case, 159
Const, 117
T
Dim, 109, 113, 119, 123
End, 109
tabela, 146, 147, 220
End Function, 82
kopiowanie, 220
End Sub, 82
nazwa, 220
End With, 103
przestawna, 34
For, 109
wiersz nag ówka, 220
Function, 82
tablica, 107, 123, 132
Next, 109
deklarowanie, 123
Preserve, 125
dynamiczna, 124
Print, 212
liczba elementów, 124
Private, 113
wielowymiarowa, 124
Public, 113, 115, 123
TintAndShade, 135
Static, 113
tryb Break, 211, 212, 214
Stop, 210
Sub, 82, 109
U
With, 103, 109
sta a, 107, 117, 244
UserForm, 54, 241, 257, 295
predefiniowana, 118
lista kontrolna, 318
vbNewLine, 207, 377
niemodalne, 315, 316
vbProperCase, 270
poprawno danych, 302
xlCalculationAutomatic, 235
prowadnice, 276
xlCalculationManual, 118, 235
testowanie, 293, 299, 318
xlCalculationSemiautomatic, 118
tworzenie, 258, 259, 264, 265, 318
xlDown, 377
w a ciwo ci, 261, 262
xlToLeft, 377
wykres, 317
xlToRight, 377
wy wietlanie, 263
xlUp, 377
wy wietlanie na ekranie, 270
zasi g, 117
z wieloma kartami, 315
string, Patrz: a cuch znaków
zamienniki, 241
strona podgl d podzia u, 227, 228
ustawienia regionalne, 132
Kup książkę Poleć książkę
Excel 2013 PL. Programowanie w VBA dla bystrzaków
398
Cells, 129
V
Color, 135
VBA, 29 Column, 133
fundamenty, 33 Columns, 133
kod, 53, 56, 57 Count, 133
kopiowanie, 63 CurrentRegion, 219, 221
lokalizacja, 173, 174 DisplayAlerts, 169
optymalizacja, 234, 377, 384 EntireRow, 221
wci cia, 58, 65, 161, 215, 384 Font, 134
modu , Patrz: modu Formula, 136
Project, 43 FormulaLocal, 136
Project Explorer, Patrz: VBA Project HasFormula, 134
wady, 33 Interior, 136
zalety, 32 IsAddin, 361
VBE, 33, 42, 51, 79 NumberFormat, 137
funkcje, 144 Offset, 130
menu podr czne, 52 Path, 143
okno, 52 Row, 133
Code, 53, 56 Rows, 133
dokowanie, 68 Text, 132
Immediate, 53, 54, 208, 211, 212 ThemeColor, 135
Locals, 214 UsedRange, 224
Project, 53, 54 Value, 131
Watch, 212, 213 Visible, 169
pasek Workbook, 54, 70, 71, 75
menu, 52 Worksheet, 34, 70, 71, 127, 138
narz dzi Edit, 66 wrapper function, Patrz: funkcja opakowuj ca
narz dzi Standard, 53 wska nik post pu zadania, 312
rodowiska dostosowanie, 63 Wst ka, 84, 255, 321, 333
Tools Options, 63, 66, 67, 68 dostosowywanie, 321, 324
uruchamianie, 51 za pomoc kodu XML, 324, 329
wygl d, 66 wykres, 34, 72, 229, 230
Visual Basic for Applications, Patrz: VBA aktywowanie, 233
formatowanie, 233
na UserForm, 317
W
osadzony, 231
warto przetwarzanie, 231
False, 134 w a ciwo ci modyfikowanie, 232
Null, 134 wyra enie, 120, 147
True, 134 monitoruj ce, 212, 213
watch expression, Patrz: wyra enie monitoruj ce
w ze
Z
Forms, 54
Modules, 54 zabezpiecze ustawienia, 23
wiersza ukrywanie, 61 zakres, 34
wirus, 22 ca a kolumna, 128, 218
w a ciwo , 74, 80, 127 ca y wiersz, 128, 218
Accelerator, 268 komórek, 34, 137, 217, 261, 307
Address, 131, 133 jako argument funkcji, 349
Kup książkę Poleć książkę
Skorowidz
399
kopiowanie, 218
zmienna, 35, 107, 130, 157
nazwa, 218
czas ycia, 116
nieci g y, 226
deklarowanie, 111, 112, 215, 237, 383
przenoszenie, 222
globalna, 117
nazwa, 127
licznikowa, 162
nieci g y, 128
lokalna, 114, 116, 214
o zmiennej wielko ci kopiowanie, 219
a cuchowa, 119
okre lanie typu, 226
nazwa, 72, 109
zaznaczenie wielokrotne, 226
niezainicjowana, 146
zdarzenie, 77, 84, 171, 173
o zasi gu
Activate, 172
jednego modu u, 115, 117
aktywacyjne, 180
jednej procedury, Patrz: zmienna lokalna
BeforeClose, 172, 179
o zasi gu globalnym, Patrz: zmienna globalna
BeforeDoubleClick, 172, 183
obiektowa, 237, 377
BeforePrint, 172
przypisywanie warto ci, 95
BeforeRightClick, 172, 184
publiczna, Patrz: zmienna globalna
BeforeSave, 172, 179, 180
statyczna, 116
Change, 172, 184
tekstowa, 377
Deactivate, 172
typ, Patrz: dane typ
dotycz ce
usuwanie z pami ci, 116
arkusza, 172, 180, 181, 183
zasi g, 113, 114
skoroszytu, 172, 176, 179, 182
znak
NewSheet, 172
>=, 156
niezwi zane z obiektami, 186, 188
", 121
OnKey, 189
#, Patrz: znak krzy yka
OnTime, 186, 187, 188
&, 121, 331
Open, 172, 176
*, 121
SelectionChange, 172
/, 121
SheetActivate, 172
^, 121
SheetBeforeDoubleClick, 172
+, 121
SheetBeforeRightClick, 172
apostrofu, 107
SheetChange, 172
cudzys owu, 108, 127, 136
SheetDeactivate, 172
cudzys owu podwójnego, 136
SheetSelectionChange, 172
dolara, 133
WindowActivate, 172
Esc, 219
WindowDeactivate, 172
kontynuacji wiersza, 59, 121, 156, 378
zegar analogowy, 188
kropki, 72, 73, 119
krzy yka, 119
amania wiersza, 246
nawias, 122, 142
przecinka, 119
równo ci, 77, 109, 121
rednika, 77, 125
zapytania, 212
Kup książkę Poleć książkę
Excel 2013 PL. Programowanie w VBA dla bystrzaków
400
Kup książkę Poleć książkę
Wyszukiwarka
Podobne podstrony:
Excel 2010 PL cwiczenia praktyczne dla bystrzakowExcel 2013 PL cwiczenia praktyczneExcel 10 PL Rozwiazywanie problemow dla kazdego ex21rpExcel 2013 PL cwiczenia zaawansowaneExcel 2013 PL cwiczenia praktyczneElektronika dla Wszystkich 10 (2013) [PL] [pdf]Word 07 PL dla bystrzakow wo27byVBA dla Excela 10 PL5 praktycznych przykladow vbae10VBA dla Excela 07 PL? praktycznych przykladow vbae27więcej podobnych podstron