Idź do
• Spis treści
• Przykładowy rozdział
Helion SA
ul. Kościuszki 1c
44-100 Gliwice
tel. 32 230 98 63
© Helion 1991–2010
Katalog książek
Twój koszyk
Cennik i informacje
Czytelnia
Kontakt
Excel 2010 PL.
Programowanie w VBA.
Vademecum Walkenbacha
Autor:
Tłumaczenie: Grzegorz Kowalczyk
ISBN: 978-83-246-2863-6
Tytuł oryginału:
Excel 2010 Power Programming with VBA
Format: 172×245, stron: 1024
Opanuj możliwości VBA z największym autorytetem w dziedzinie Excela!
• Jak projektować przyjazne użytkownikom okna dialogowe?
• Jak stosować VBA do tworzenia użytecznych aplikacji dla Excela?
• Jak rozszerzać możliwości Excela i tworzyć praktyczne dodatki?
Nie należysz do osób, które onieśmiela potencjał Excela? Sprawnie tworzysz skoroszyty, wprowadzasz
formuły, używasz funkcji arkuszowych i swobodnie posługujesz się Wstążką programu? Czujesz,
że drzemie w nim jeszcze ogrom niezwykłych możliwości, ale nie wiesz, jak po nie sięgnąć?
Najwyższa pora na naukę z Johnem Walkenbachem – najsłynniejszym ekspertem w dziedzinie
Excela! Jeśli poznałeś już podstawowe funkcje tego programu, dzięki tej książce bez trudu
opanujesz narzędzia zaawansowane, czyli takie, które naprawdę ułatwią i przyspieszą Twoją
codzienną pracę!
Swoją naukę pod okiem mistrza zaczniesz od odświeżenia informacji na temat używania rozmaitych
formuł oraz plików stosowanych i generowanych przez Excel. Zaraz potem przejdziesz do fascynującej
części, poświęconej projektowaniu aplikacji w tym programie. Dowiesz się, czym taka aplikacja
jest i jak szczegółowo wyglądają etapy jej tworzenia. Następnie opanujesz całą niezbędną wiedzę
na temat języka VBA, aby sprawnie w nim programować oraz tworzyć funkcje i procedury.
Nauczysz się również wykorzystywać jego możliwości podczas używania tabel przestawnych
i wykresów. Ponadto wzbogacisz się o informacje na temat projektowania niestandardowych,
przyjaznych okien dialogowych UserForm, automatycznej obsługi zdarzeń czy tworzenia
praktycznych dodatków dla Excela.
• Przegląd możliwości Excela 2010
• Projektowanie aplikacji w programie Excel
• Język Visual Basic for Applications
• Zastosowanie formularzy UserForm
• Niestandardowe okna dialogowe
• Zaawansowane metody programowania
• Tabele przestawne, wykresy i obsługa zdarzeń
• Projektowanie dodatków do Excela
• Tworzenie systemów pomocy dla aplikacji
• Tworzenie aplikacji przyjaznych dla użytkownika
• Metody użycia VBA do pracy z plikami
Posiądź wiedzę profesjonalistów – wykorzystaj wszystkie
możliwości Excela i poszerzaj je!
Spis treci
O
autorze
............................................................................................... 19
Przedmowa
............................................................................................ 21
Cz I
Podstawowe informacje ....................................................29
Rozdzia 1.
Skd si wzi Excel 2010? ................................................................... 31
Krótka historia arkuszy kalkulacyjnych ..............................................................................31
Wszystko zaczo si od programu VisiCalc .................................................................31
Lotus 1-2-3 ....................................................................................................................32
Quattro Pro ....................................................................................................................35
Microsoft Excel .............................................................................................................36
Excel jako dobre narzdzie dla projektantów aplikacji ........................................................41
Rola Excela w strategii Microsoftu .....................................................................................43
Rozdzia 2.
Program Excel w zarysie ...................................................................... 45
Mylenie w kategoriach obiektów .......................................................................................45
Skoroszyty ...........................................................................................................................46
Arkusze .........................................................................................................................46
Arkusze wykresów ........................................................................................................48
Arkusze makr XLM ......................................................................................................49
Arkusze dialogowe programów Excel 5 i 95 .................................................................50
Interfejs uytkownika programu Excel ................................................................................50
Wprowadzenie do Wstki ............................................................................................51
Menu podrczne i minipasek narzdzi ..........................................................................57
Okna dialogowe ............................................................................................................58
Skróty klawiszowe ........................................................................................................59
Tagi inteligentne ............................................................................................................59
Panel zada ...................................................................................................................60
Dostosowywanie wywietlania do wasnych potrzeb ..........................................................61
Wprowadzanie danych ........................................................................................................61
Formuy, funkcje i nazwy ....................................................................................................61
Zaznaczanie obiektów .........................................................................................................63
Formatowanie ......................................................................................................................64
Opcje ochrony .....................................................................................................................65
Ochrona formu przed nadpisaniem ..............................................................................65
Ochrona struktury skoroszytu .......................................................................................66
Ochrona skoroszytu przy uyciu hasa ..........................................................................66
Ochrona kodu VBA przy uyciu hasa ..........................................................................67
Wykresy ..............................................................................................................................68
Ksztaty i obiekty typu SmartArt .........................................................................................68
4 Spis
treci
Dostp do baz danych ..........................................................................................................69
Arkuszowe bazy danych ................................................................................................69
Zewntrzne bazy danych ...............................................................................................70
Funkcje internetowe ............................................................................................................71
Narzdzia analizy danych ....................................................................................................72
Dodatki ................................................................................................................................73
Makra i programowanie ......................................................................................................74
Zgodno formatu plików ....................................................................................................74
System pomocy Excela ........................................................................................................74
Rozdzia 3.
Wybrane zasady stosowania formu ................................................... 77
Formuy ...............................................................................................................................77
Obliczanie formu ................................................................................................................78
Odwoania do komórki lub zakresu .....................................................................................79
Dlaczego warto uywa odwoa, które nie s wzgldne? ............................................79
Notacja W1K1 ...............................................................................................................80
Odwoania do innych arkuszy lub skoroszytów ............................................................81
Zastosowanie nazw ..............................................................................................................83
Nadawanie nazw komórkom i zakresom .......................................................................83
Nadawanie nazw istniejcym odwoaniom ...................................................................83
Stosowanie nazw z operatorem przecicia ....................................................................84
Nadawanie nazw kolumnom i wierszom .......................................................................85
Obszar obowizywania nazw ........................................................................................85
Nadawanie nazw staym ................................................................................................86
Nadawanie nazw formuom ...........................................................................................87
Nadawanie nazw obiektom ...........................................................................................88
Bdy wystpujce w formuach ..........................................................................................89
Formuy tablicowe ...............................................................................................................89
Przykadowa formua tablicowa ....................................................................................90
Kalendarz oparty na formule tablicowej .......................................................................91
Zalety i wady formu tablicowych .................................................................................92
Metody zliczania i sumowania ............................................................................................93
Przykady formu zliczajcych ......................................................................................94
Przykady formu sumujcych .......................................................................................95
Inne narzdzia zliczajce ...............................................................................................95
Przetwarzanie daty i czasu ...................................................................................................96
Wprowadzanie daty i czasu ...........................................................................................96
Przetwarzanie dat sprzed roku 1900 ..............................................................................97
Tworzenie megaformu .......................................................................................................98
Rozdzia 4.
Pliki programu Excel ........................................................................... 101
Uruchamianie Excela .........................................................................................................101
Formaty plików .................................................................................................................103
Formaty plików obsugiwane w programie Excel .......................................................104
Formaty plików tekstowych ........................................................................................104
Formaty plików baz danych ........................................................................................104
Inne formaty plików ....................................................................................................105
Praca z plikami szablonów ................................................................................................106
Przegldanie dostpnych szablonów ...........................................................................108
Tworzenie szablonów ..................................................................................................109
Tworzenie szablonów skoroszytu ................................................................................110
Budowa plików programu Excel .......................................................................................111
Zagldamy do wntrza pliku .......................................................................................112
Dlaczego format pliku jest taki wany? ......................................................................115
Spis treci
5
Plik OfficeUI .....................................................................................................................116
Plik XLB ...........................................................................................................................117
Pliki dodatków ...................................................................................................................117
Ustawienia Excela w rejestrze systemu Windows .............................................................118
Rejestr systemu Windows ...........................................................................................118
Ustawienia Excela .......................................................................................................120
Cz II
Projektowanie aplikacji w Excelu ...................................123
Rozdzia 5.
Czym jest aplikacja arkusza kalkulacyjnego? ................................ 125
Aplikacje arkuszy kalkulacyjnych .....................................................................................125
Projektant i uytkownik kocowy .....................................................................................126
Kim s projektanci i czym si zajmuj? ......................................................................127
Klasyfikacja uytkowników arkuszy kalkulacyjnych ..................................................128
Odbiorcy aplikacji arkusza kalkulacyjnego .................................................................129
Rozwizywanie problemów przy uyciu Excela ...............................................................129
Podstawowe kategorie arkuszy kalkulacyjnych .................................................................130
Arkusze robocze ..........................................................................................................131
Arkusze przeznaczone wycznie do uytku prywatnego ............................................131
Aplikacje jednego uytkownika ..................................................................................132
Aplikacje typu „spaghetti” ..........................................................................................132
Aplikacje narzdziowe ................................................................................................133
Dodatki zawierajce funkcje arkusza ..........................................................................133
Arkusze jednoblokowe ................................................................................................134
Modele warunkowe .....................................................................................................134
Aplikacje bazodanowe (przechowujce i udostpniajce dane) ..................................134
Aplikacje komunikujce si z bazami danych .............................................................135
Aplikacje „pod klucz” .................................................................................................135
Rozdzia 6.
Podstawy projektowania aplikacji arkusza kalkulacyjnego .......... 137
Podstawowe etapy projektowania ......................................................................................137
Okrelanie wymaga uytkownika ....................................................................................138
Planowanie aplikacji speniajcej wymagania uytkownika .............................................139
Wybieranie odpowiedniego interfejsu uytkownika ..........................................................141
Dostosowywanie Wstki do potrzeb uytkownika ....................................................144
Dostosowywanie menu podrcznego do potrzeb uytkownika ...................................144
Tworzenie klawiszy skrótu ..........................................................................................145
Tworzenie niestandardowych okien dialogowych .......................................................146
Zastosowanie formantów ActiveX w arkuszu .............................................................146
Rozpoczcie prac projektowych ..................................................................................148
Zadania realizowane z myl o kocowym uytkowniku ..................................................149
Testowanie aplikacji ....................................................................................................149
Uodpornianie aplikacji na bdy popeniane przez uytkownika ................................150
Nadawanie aplikacji przyjaznego, intuicyjnego i estetycznego wygldu ....................152
Tworzenie systemu pomocy i dokumentacji przeznaczonej dla uytkownika .............154
Dokumentowanie prac projektowych ..........................................................................155
Przekazanie aplikacji uytkownikom ..........................................................................155
Aktualizacja aplikacji (kiedy to konieczne) ................................................................156
Pozostae kwestie dotyczce projektowania ......................................................................156
Wersja Excela zainstalowana przez uytkownika .......................................................157
Wersje jzykowe .........................................................................................................157
Wydajno systemu .....................................................................................................157
Tryby karty graficznej .................................................................................................158
6 Spis
treci
Cz III
Jzyk Visual Basic for Applications ................................159
Rozdzia 7.
Wprowadzenie do jzyka VBA .......................................................... 161
Podstawowe informacje o jzyku BASIC .........................................................................161
Jzyk VBA ........................................................................................................................162
Modele obiektowe .......................................................................................................162
Porównanie jzyka VBA z jzykiem XLM .................................................................162
Wprowadzenie do jzyka VBA .........................................................................................163
Edytor VBE .......................................................................................................................165
Wywietlanie karty Deweloper ...................................................................................166
Uruchamianie edytora VBE ........................................................................................167
Okna edytora VBE ......................................................................................................167
Tajemnice okna Project Explorer ......................................................................................169
Dodawanie nowego moduu VBA ...............................................................................170
Usuwanie moduu VBA ..............................................................................................171
Eksportowanie i importowanie obiektów ....................................................................171
Tajemnice okna Code ........................................................................................................171
Minimalizacja i maksymalizacja okien .......................................................................172
Przechowywanie kodu ródowego jzyka VBA ........................................................172
Wprowadzanie kodu ródowego jzyka VBA ...........................................................173
Dostosowywanie edytora Visual Basic ..............................................................................179
Karta Editor .................................................................................................................180
Karta Editor Format ....................................................................................................183
Karta General ..............................................................................................................184
Zastosowanie karty Docking .......................................................................................185
Rejestrator makr Excela ....................................................................................................185
Co waciwie zapisuje rejestrator makr? .....................................................................186
Odwoania wzgldne czy bezwzgldne? .....................................................................187
Opcje zwizane z rejestrowaniem makr ......................................................................191
Modyfikowanie zarejestrowanych makr .....................................................................191
Obiekty i kolekcje .............................................................................................................192
Hierarchia obiektów ....................................................................................................193
Kolekcje ......................................................................................................................194
Odwoywanie si do obiektów ....................................................................................195
Waciwoci i metody ........................................................................................................196
Waciwoci obiektów .................................................................................................196
Metody obiektowe .......................................................................................................197
Tajemnice obiektu Comment .............................................................................................198
Pomoc dla obiektu Comment ......................................................................................199
Waciwoci obiektu Comment ...................................................................................199
Metody obiektu Comment ...........................................................................................199
Kolekcja Comments ....................................................................................................201
Waciwo Comment .................................................................................................202
Obiekty zawarte w obiekcie Comment ........................................................................202
Sprawdzanie, czy komórka posiada komentarz ...........................................................203
Dodawanie nowego obiektu Comment ........................................................................204
Kilka przydatnych waciwoci obiektu Application .........................................................205
Tajemnice obiektów Range ...............................................................................................206
Waciwo Range ......................................................................................................207
Waciwo Cells ........................................................................................................209
Waciwo Offset ......................................................................................................210
Co naley wiedzie o obiektach? .......................................................................................212
Podstawowe zagadnienia, które naley zapamita ....................................................212
Dodatkowe informacje na temat obiektów i waciwoci ............................................213
Spis treci
7
Rozdzia 8.
Podstawy programowania w jzyku VBA ......................................... 217
Przegld elementów jzyka VBA ......................................................................................217
Komentarze .......................................................................................................................219
Zmienne, typy danych i stae .............................................................................................220
Definiowanie typów danych ........................................................................................222
Deklarowanie zmiennych ............................................................................................222
Zasig zmiennych ........................................................................................................226
Zastosowanie staych ...................................................................................................229
Praca z acuchami tekstu ...........................................................................................232
Przetwarzanie dat ........................................................................................................232
Instrukcje przypisania ........................................................................................................233
Tablice ...............................................................................................................................235
Deklarowanie tablic .....................................................................................................236
Deklarowanie tablic wielowymiarowych ....................................................................236
Deklarowanie tablic dynamicznych ............................................................................237
Zmienne obiektowe ...........................................................................................................237
Typy danych definiowane przez uytkownika ..................................................................238
Wbudowane funkcje VBA .................................................................................................239
Praca z obiektami i kolekcjami ..........................................................................................242
Konstrukcja With ... End With ....................................................................................242
Konstrukcja For Each ... Next .....................................................................................243
Sterowanie wykonywaniem procedur ................................................................................244
Polecenie GoTo ...........................................................................................................245
Konstrukcja If ... Then ................................................................................................245
Konstrukcja Select Case ..............................................................................................249
Wykonywanie bloku instrukcji w ramach ptli ...........................................................252
Rozdzia 9.
Tworzenie procedur w jzyku VBA .................................................... 261
Kilka sów o procedurach ..................................................................................................261
Deklarowanie procedury Sub ......................................................................................262
Zasig procedury .........................................................................................................263
Wykonywanie procedur Sub .............................................................................................264
Uruchamianie procedury przy uyciu polecenia Run Sub/UserForm ..........................265
Uruchamianie procedury z poziomu okna dialogowego Makro ..................................265
Uruchamianie procedury przy uyciu skrótu z klawiszem Ctrl ...................................266
Uruchamianie procedury za pomoc Wstki .............................................................267
Uruchamianie procedur za porednictwem niestandardowego menu podrcznego .....267
Wywoywanie procedury z poziomu innej procedury .................................................267
Uruchamianie procedury poprzez kliknicie obiektu ..................................................271
Wykonywanie procedury po wystpieniu okrelonego zdarzenia ...............................273
Uruchamianie procedury z poziomu okna Immediate .................................................274
Przekazywanie argumentów procedurom ..........................................................................275
Metody obsugi bdów .....................................................................................................278
Przechwytywanie bdów ............................................................................................278
Przykady kodu ródowego obsugujcego bdy ......................................................279
Praktyczny przykad wykorzystujcy procedury Sub ........................................................282
Cel ...............................................................................................................................283
Wymagania projektowe ...............................................................................................283
Co ju wiesz ................................................................................................................283
Podejcie do zagadnienia ............................................................................................284
Co musimy wiedzie? .................................................................................................285
Wstpne rejestrowanie makr .......................................................................................285
Wstpne przygotowania ..............................................................................................286
Tworzenie kodu ródowego .......................................................................................288
8 Spis
treci
Tworzenie procedury sortujcej ..................................................................................289
Dodatkowe testy ..........................................................................................................292
Usuwanie problemów ..................................................................................................293
Dostpno narzdzia ..................................................................................................296
Ocena projektu ............................................................................................................296
Rozdzia 10. Tworzenie funkcji w jzyku VBA ......................................................... 299
Porównanie procedur Sub i Function .................................................................................299
Dlaczego tworzymy funkcje niestandardowe? ..................................................................300
Twoja pierwsza funkcja .....................................................................................................301
Zastosowanie funkcji w arkuszu .................................................................................301
Zastosowanie funkcji w procedurze jzyka VBA ........................................................302
Analiza funkcji niestandardowej .................................................................................302
Procedury Function ...........................................................................................................304
Zasig funkcji ..............................................................................................................306
Wywoywanie procedur Function ...............................................................................306
Argumenty funkcji ............................................................................................................310
Przykady funkcji ..............................................................................................................311
Funkcja bezargumentowa ............................................................................................311
Funkcja jednoargumentowa ........................................................................................313
Funkcje z dwoma argumentami ...................................................................................316
Funkcja pobierajca tablic jako argument .................................................................317
Funkcje z argumentami opcjonalnymi ........................................................................318
Funkcje zwracajce tablic VBA ................................................................................319
Funkcje zwracajce warto bdu ..............................................................................322
Funkcje o nieokrelonej liczbie argumentów ..............................................................323
Emulacja funkcji arkuszowej SUMA ................................................................................324
Rozszerzone funkcje daty ..................................................................................................327
Wykrywanie i usuwanie bdów w funkcjach ...................................................................329
Okno dialogowe Wstawianie funkcji .................................................................................330
Zastosowanie metody MacroOptions ..........................................................................332
Definiowanie kategorii funkcji ....................................................................................333
Dodawanie opisu funkcji .............................................................................................334
Zastosowanie dodatków do przechowywania funkcji niestandardowych ..........................335
Korzystanie z Windows API .............................................................................................336
Przykady zastosowania funkcji interfejsu API systemu Windows .............................336
Identyfikacja katalogu domowego systemu Windows ................................................337
Wykrywanie wcinicia klawisza Shift .......................................................................338
Dodatkowe informacje na temat funkcji interfejsu API ..............................................339
Rozdzia 11. Przykady i techniki programowania w jzyku VBA ........................ 341
Nauka poprzez praktyk ....................................................................................................341
Przetwarzanie zakresów ....................................................................................................342
Kopiowanie zakresów .................................................................................................342
Przenoszenie zakresów ................................................................................................344
Kopiowanie zakresu o zmiennej wielkoci ..................................................................344
Zaznaczanie oraz identyfikacja rónego typu zakresów ..............................................345
Wprowadzanie wartoci do komórki ...........................................................................346
Wprowadzanie wartoci do nastpnej pustej komórki .................................................348
Wstrzymywanie dziaania makra w celu umoliwienia pobrania zakresu
wyznaczonego przez uytkownika ...........................................................................350
Zliczanie zaznaczonych komórek ................................................................................351
Okrelanie typu zaznaczonego zakresu .......................................................................352
Wydajne przetwarzanie komórek zaznaczonego zakresu przy uyciu ptli ................353
Spis treci
9
Usuwanie wszystkich pustych wierszy ........................................................................356
Powielanie wierszy ......................................................................................................357
Okrelanie, czy zakres zawiera si w innym zakresie .................................................358
Okrelanie typu danych zawartych w komórce ...........................................................359
Odczytywanie i zapisywanie zakresów .......................................................................360
Lepsza metoda zapisywania zakresu ...........................................................................361
Przenoszenie zawartoci tablic jednowymiarowych ....................................................363
Przenoszenie zawartoci zakresu do tablicy typu Variant ...........................................363
Zaznaczanie komórek na podstawie wartoci ..............................................................364
Kopiowanie niecigego zakresu komórek ..................................................................365
Przetwarzanie skoroszytów i arkuszy ................................................................................367
Zapisywanie wszystkich skoroszytów .........................................................................367
Zapisywanie i zamykanie wszystkich skoroszytów .....................................................368
Ukrywanie wszystkich komórek arkusza poza zaznaczonym zakresem .....................368
Synchronizowanie arkuszy ..........................................................................................369
Techniki programowania w jzyku VBA ..........................................................................370
Przeczanie wartoci waciwoci typu logicznego ....................................................370
Okrelanie liczby drukowanych stron .........................................................................371
Wywietlanie daty i czasu ...........................................................................................372
Pobieranie listy czcionek .............................................................................................373
Sortowanie tablicy .......................................................................................................374
Przetwarzanie grupy plików ........................................................................................376
Ciekawe funkcje, których moesz uy w swoich projektach ...........................................378
Funkcja FileExists .......................................................................................................378
Funkcja FileNameOnly ...............................................................................................378
Funkcja PathExists ......................................................................................................379
Funkcja RangeNameExists ..........................................................................................379
Funkcja SheetExists ....................................................................................................380
Funkcja WorkbookIsOpen ..........................................................................................381
Pobieranie wartoci z zamknitego skoroszytu ...........................................................381
Uyteczne, niestandardowe funkcje arkuszowe .................................................................382
Funkcje zwracajce informacje o formatowaniu komórki ...........................................382
Gadajcy arkusz? ........................................................................................................384
Wywietlanie daty zapisania lub wydrukowania pliku ................................................384
Obiekty nadrzdne ......................................................................................................385
Zliczanie komórek, których wartoci zawieraj si pomidzy dwoma wartociami ...386
Wyznaczanie ostatniej niepustej komórki kolumny lub wiersza .................................387
Czy dany acuch tekstu jest zgodny z wzorcem? .......................................................388
Wyznaczanie n-tego elementu acucha ......................................................................390
Zamiana wartoci na sowa .........................................................................................390
Funkcja wielofunkcyjna ..............................................................................................391
Funkcja SheetOffset ....................................................................................................392
Zwracanie maksymalnej wartoci ze wszystkich arkuszy ...........................................393
Zwracanie tablicy zawierajcej unikatowe, losowo uporzdkowane
liczby cakowite .......................................................................................................394
Porzdkowanie zakresu w losowy sposób ...................................................................395
Wywoania funkcji interfejsu Windows API .....................................................................396
Okrelanie skojarze plików .......................................................................................397
Pobieranie informacji o napdach dyskowych ............................................................397
Pobieranie informacji dotyczcych drukarki domylnej ..............................................398
Pobieranie informacji o aktualnej rozdzielczoci karty graficznej ..............................399
Dodanie dwiku do aplikacji .....................................................................................400
Odczytywanie zawartoci rejestru systemu Windows i zapisywanie w nim danych .....402
10 Spis
treci
Cz IV
Praca z formularzami UserForm ......................................405
Rozdzia 12. Tworzenie wasnych okien dialogowych .......................................... 407
Zanim rozpoczniesz tworzenie formularza UserForm .......................................................407
Okno wprowadzania danych .............................................................................................407
Funkcja InputBox jzyka VBA ...................................................................................408
Metoda InputBox Excela .............................................................................................409
Funkcja MsgBox jzyka VBA ...........................................................................................412
Metoda GetOpenFilename programu Excel ......................................................................415
Metoda GetSaveAsFilename programu Excel ...................................................................419
Okno wybierania katalogu .................................................................................................419
Wywietlanie wbudowanych okien dialogowych Excela ..................................................420
Wywietlanie formularza danych ......................................................................................421
Wywietlanie formularza wprowadzania danych ........................................................423
Wywietlanie formularza wprowadzania danych za pomoc VBA .............................424
Rozdzia 13. Wprowadzenie do formularzy UserForm ........................................... 425
Jak Excel obsuguje niestandardowe okna dialogowe .......................................................425
Wstawianie nowego formularza UserForm .......................................................................426
Dodawanie formantów do formularza UserForm ..............................................................426
Formanty okna Toolbox ....................................................................................................428
Formant CheckBox .....................................................................................................428
Formant ComboBox ....................................................................................................429
Formant CommandButton ...........................................................................................429
Formant Frame ............................................................................................................429
Formant Image ............................................................................................................429
Formant Label .............................................................................................................429
Formant ListBox .........................................................................................................429
Formant MultiPage ......................................................................................................430
Formant OptionButton ................................................................................................430
Formant RefEdit ..........................................................................................................430
Formant ScrollBar .......................................................................................................430
Formant SpinButton ....................................................................................................430
Formant TabStrip ........................................................................................................430
Formant TextBox ........................................................................................................431
Formant ToggleButton ................................................................................................431
Modyfikowanie formantów formularza UserForm ............................................................432
Modyfikowanie waciwoci formantów ...........................................................................432
Zastosowanie okna Properties .....................................................................................432
Wspólne waciwoci ..................................................................................................435
Uwzgldnienie wymaga uytkowników preferujcych korzystanie z klawiatury .....435
Wywietlanie formularza UserForm ..................................................................................438
Wywietlanie niemodalnych okien formularzy UserForm ..........................................438
Wywietlanie formularza UserForm na podstawie zmiennej ......................................439
adowanie formularza UserForm ................................................................................439
Procedury obsugi zdarze ..........................................................................................439
Zamykanie formularza UserForm ......................................................................................439
Przykad tworzenia formularza UserForm .........................................................................441
Tworzenie formularza UserForm ................................................................................441
Tworzenie kodu procedury wywietlajcej okno dialogowe .......................................444
Testowanie okna dialogowego ....................................................................................444
Dodawanie procedur obsugi zdarze ..........................................................................445
Sprawdzanie poprawnoci danych ..............................................................................447
Zakoczenie tworzenia okna dialogowego ..................................................................447
Spis treci
11
Zdarzenia powizane z formularzem UserForm ................................................................447
Zdobywanie informacji na temat zdarze ....................................................................448
Zdarzenia formularza UserForm .................................................................................449
Zdarzenia zwizane z formantem SpinButton .............................................................449
Wspópraca formantu SpinButton z formantem TextBox ...........................................451
Odwoywanie si do formantów formularza UserForm .....................................................453
Dostosowywanie okna Toolbox do wasnych wymaga ...................................................454
Dodawanie nowych kart ..............................................................................................455
Dostosowywanie lub czenie formantów ...................................................................455
Dodawanie nowych formantów ActiveX ....................................................................456
Tworzenie szablonów formularzy UserForm ....................................................................457
Lista kontrolna tworzenia i testowania formularzy UserForm ...........................................458
Rozdzia 14. Przykady formularzy UserForm .......................................................... 459
Tworzenie formularza UserForm penicego funkcj menu ..............................................459
Zastosowanie w formularzu UserForm formantów CommandButton .........................460
Zastosowanie w formularzu UserForm formantu ListBox ..........................................460
Zaznaczanie zakresów przy uyciu formularza UserForm ................................................461
Tworzenie okna powitalnego .............................................................................................463
Wyczanie przycisku Zamknij formularza UserForm ......................................................465
Zmiana wielkoci formularza UserForm ...........................................................................466
Powikszanie i przewijanie arkusza przy uyciu formularza UserForm ............................468
Zastosowania formantu ListBox ........................................................................................470
Tworzenie listy elementów formantu ListBox ............................................................471
Identyfikowanie zaznaczonego elementu listy formantu ListBox ...............................475
Identyfikowanie wielu zaznaczonych elementów listy formantu ListBox ........................475
Wiele list w jednej kontrolce ListBox .........................................................................476
Przenoszenie elementów listy formantu ListBox ........................................................478
Zmiana kolejnoci elementów listy formantu ListBox ................................................479
Wielokolumnowe formanty ListBox ...........................................................................480
Zastosowanie formantu ListBox do wybierania wierszy arkusza ................................482
Uaktywnianie arkusza za pomoc formantu ListBox ..................................................484
Zastosowanie formantu MultiPage na formularzach UserForm ........................................487
Korzystanie z formantów zewntrznych ............................................................................488
Animowanie etykiet ..........................................................................................................490
Rozdzia 15. Zaawansowane techniki korzystania z formularzy UserForm ......... 493
Niemodalne okna dialogowe .............................................................................................493
Wywietlanie wskanika postpu zadania .........................................................................497
Tworzenie samodzielnego wskanika postpu zadania ...............................................498
Wywietlanie wskanika postpu zadania za pomoc formantu MultiPage ................502
Wywietlanie wskanika postpu zadania bez korzystania z kontrolki MultiPage .....504
Tworzenie kreatorów .........................................................................................................505
Konfigurowanie formantu MultiPage w celu utworzenia kreatora ..............................506
Dodawanie przycisków do formularza UserForm kreatora .........................................507
Programowanie przycisków kreatora ..........................................................................508
Zalenoci programowe w kreatorach .........................................................................509
Wykonywanie zada za pomoc kreatorów ................................................................511
Emulacja funkcji MsgBox .................................................................................................511
Emulacja funkcji MsgBox: kod funkcji MyMsgBox ..................................................512
Jak dziaa funkcja MyMsgBox ....................................................................................513
Wykorzystanie funkcji MyMsgBox do emulacji funkcji MsgBox ..............................515
Formularz UserForm z formantami, których pooenie mona zmienia ..........................515
Formularz UserForm bez paska tytuowego ......................................................................516
Symulacja paska narzdzi za pomoc formularza UserForm ............................................518
12 Spis
treci
Formularze UserForm z moliwoci zmiany rozmiaru ....................................................520
Obsuga wielu przycisków formularza UserForm za pomoc jednej procedury
obsugi zdarze ...............................................................................................................524
Wybór koloru za pomoc formularza UserForm ...............................................................527
Wywietlanie wykresów na formularzach UserForm ........................................................528
Zapisywanie wykresu w postaci pliku GIF .................................................................529
Modyfikacja waciwoci Picture formantu Image .....................................................530
Tworzenie póprzezroczystych formularzy UserForm .......................................................530
Zaawansowane formularze danych ....................................................................................531
Opis ulepszonego formularza danych ..........................................................................533
Instalacja dodatku — ulepszonego formularza danych ...............................................533
Puzzle na formularzu UserForm ........................................................................................535
Wideo Poker na formularzu UserForm ..............................................................................536
Cz V
Zaawansowane techniki programowania ....................537
Rozdzia 16. Tworzenie narzdzi dla Excela w jzyku VBA .................................. 539
Kilka sów o narzdziach dla programu Excel ..................................................................539
Zastosowanie jzyka VBA do tworzenia narzdzi ............................................................540
Co decyduje o przydatnoci narzdzia? .............................................................................541
Operacje tekstowe: anatomia narzdzia .............................................................................541
Kilka sów o programie Operacje tekstowe .................................................................542
Okrelenie wymaga dla narzdzia Operacje tekstowe ...............................................543
Skoroszyt narzdzia Operacje tekstowe ......................................................................543
Jak dziaa narzdzie Operacje tekstowe? .....................................................................544
Formularz UserForm dla narzdzia Operacje tekstowe ...............................................545
Modu VBA Module1 .................................................................................................546
Modu formularza UserForm1 .....................................................................................548
Poprawa wydajnoci narzdzia Operacje tekstowe .....................................................550
Zapisywanie ustawie narzdzia Operacje tekstowe ...................................................551
Implementacja procedury Cofnij .................................................................................553
Wywietlanie pliku pomocy ........................................................................................554
Umieszczanie polece na Wstce ..............................................................................556
Ocena realizacji projektu .............................................................................................556
Dziaanie narzdzia Operacje tekstowe .......................................................................558
Dodatkowe informacje na temat narzdzi Excela ..............................................................558
Rozdzia 17. Tabele przestawne .............................................................................. 559
Przykad prostej tabeli przestawnej ...................................................................................559
Tworzenie tabel przestawnych ....................................................................................560
Analiza zarejestrowanego kodu tworzenia tabeli przestawnej ....................................561
Optymalizacja wygenerowanego kodu tworzcego tabel przestawn .......................562
Tworzenie zoonych tabel przestawnych .........................................................................565
Kod tworzcy tabel przestawn .................................................................................567
Jak dziaa zoona tabela przestawna? .........................................................................568
Jednoczesne tworzenie wielu tabel przestawnych .............................................................569
Tworzenie odwróconych tabel przestawnych ....................................................................572
Rozdzia 18. Wykresy ............................................................................................... 575
Podstawowe wiadomoci o wykresach ..............................................................................575
Lokalizacja wykresu ....................................................................................................576
Rejestrator makr a wykresy .........................................................................................576
Model obiektu Chart ....................................................................................................577
Tworzenie wykresów osadzonych na arkuszu danych .......................................................578
Spis treci
13
Tworzenie wykresu na arkuszu wykresu ...........................................................................579
Wykorzystanie VBA do uaktywnienia wykresu ................................................................580
Przenoszenie wykresu .......................................................................................................582
Wykorzystanie VBA do deaktywacji wykresu ..................................................................582
Sprawdzanie, czy wykres zosta uaktywniony ...................................................................583
Usuwanie elementów z kolekcji ChartObjects lub Charts .................................................584
Przetwarzanie wszystkich wykresów w ptli .....................................................................585
Zmiana rozmiarów i wyrównywanie obiektów ChartObject .............................................587
Eksportowanie wykresów ..................................................................................................588
Eksportowanie wszystkich obiektów graficznych .......................................................589
Zmiana danych prezentowanych na wykresie ...................................................................590
Modyfikacja danych wykresu na podstawie aktywnej komórki ..................................592
Zastosowanie jzyka VBA do identyfikacji zakresu danych prezentowanych
na wykresie ..............................................................................................................593
Wykorzystanie VBA do wywietlania dowolnych etykiet danych na wykresie ................596
Wywietlanie wykresu w oknie formularza UserForm ......................................................598
Zdarzenia zwizane z wykresami ......................................................................................601
Przykad wykorzystania zdarze zwizanych z wykresami ........................................601
Obsuga zdarze dla wykresów osadzonych ...............................................................604
Przykad: zastosowanie zdarze dla wykresów osadzonych .......................................606
Jak uatwi sobie prac z wykresami przy uyciu VBA? ..................................................608
Drukowanie wykresów osadzonych na arkuszu ..........................................................608
Ukrywanie serii danych poprzez ukrywanie kolumn ...................................................608
Tworzenie wykresów, które nie s poczone z danymi ..............................................610
Wykorzystanie zdarzenia MouseOver do wywietlania tekstu ....................................611
Wykresy animowane .........................................................................................................614
Przewijanie wykresów .................................................................................................615
Tworzenie wykresu krzywych hipocykloidalnych ......................................................617
Tworzenie wykresu-zegara ..........................................................................................618
Tworzenie wykresu interaktywnego bez uycia VBA .......................................................619
Przygotowanie danych do utworzenia wykresu interaktywnego .................................620
Tworzenie przycisków opcji dla interaktywnego wykresu ..........................................620
Tworzenie listy miast dla wykresu interaktywnego ....................................................621
Tworzenie zakresów danych dla wykresu interaktywnego ..........................................621
Utworzenie wykresu interaktywnego ..........................................................................623
Tworzenie wykresów przebiegu w czasie ..........................................................................623
Rozdzia 19. Obsuga zdarze ................................................................................. 627
Co powiniene wiedzie o zdarzeniach .............................................................................627
Sekwencje zdarze ......................................................................................................628
Gdzie naley umieci procedury obsugi zdarze? ....................................................628
Wyczanie obsugi zdarze ........................................................................................630
Wprowadzanie kodu procedury obsugi zdarze .........................................................631
Procedury obsugi zdarze z argumentami ..................................................................632
Zdarzenia poziomu skoroszytu ..........................................................................................634
Zdarzenie Open ...........................................................................................................634
Zdarzenie Activate ......................................................................................................636
Zdarzenie SheetActivate .............................................................................................636
Zdarzenie NewSheet ...................................................................................................636
Zdarzenie BeforeSave .................................................................................................637
Zdarzenie Deactivate ...................................................................................................637
Zdarzenie BeforePrint .................................................................................................638
Zdarzenie BeforeClose ................................................................................................639
14 Spis
treci
Zdarzenia poziomu arkusza ...............................................................................................641
Zdarzenie Change ........................................................................................................641
Monitorowanie zmian w wybranym zakresie komórek ...............................................642
Zdarzenie SelectionChange .........................................................................................647
Zdarzenie BeforeDoubleClick .....................................................................................648
Zdarzenie BeforeRightClick ........................................................................................648
Zdarzenia dotyczce wykresów .........................................................................................649
Zdarzenia dotyczce aplikacji ...........................................................................................649
Wczenie obsugi zdarze poziomu aplikacji ............................................................651
Sprawdzanie, czy skoroszyt jest otwarty .....................................................................653
Monitorowanie zdarze poziomu aplikacji .................................................................654
Zdarzenia dotyczce formularzy UserForm .......................................................................655
Zdarzenia niezwizane z obiektami ...................................................................................655
Zdarzenie OnTime ......................................................................................................655
Zdarzenie OnKey ........................................................................................................658
Rozdzia 20. Interakcje z innymi aplikacjami ........................................................ 663
Uruchamianie innych aplikacji z poziomu Excela .............................................................663
Zastosowanie funkcji Shell jzyka VBA .....................................................................663
Zastosowanie funkcji ShellExecute interfejsu Windows API .....................................665
Uaktywnianie aplikacji z poziomu Excela .........................................................................667
Wykorzystanie instrukcji AppActivate .......................................................................667
Uaktywnianie aplikacji pakietu Microsoft Office .......................................................668
Uruchamianie okien dialogowych Panelu sterowania .......................................................668
Wykorzystanie automatyzacji w programie Excel .............................................................669
Dziaania z obiektami innych aplikacji z wykorzystaniem automatyzacji ...................670
Wczesne i póne wizanie ...........................................................................................670
Funkcja GetObject a CreateObject ..............................................................................673
Prosty przykad pónego wizania ..............................................................................673
Sterowanie Wordem z poziomu Excela .......................................................................674
Zarzdzanie Excelem z poziomu innej aplikacji .........................................................677
Wysyanie spersonalizowanych wiadomoci e-mail z wykorzystaniem Outlooka ............678
Wysyanie wiadomoci e-mail z zacznikami z poziomu Excela .....................................682
Zastosowanie metody SendKeys .......................................................................................684
Rozdzia 21. Tworzenie i wykorzystanie dodatków ............................................... 685
Czym s dodatki? ..............................................................................................................685
Porównanie dodatku ze standardowym skoroszytem ..................................................686
Po co tworzy si dodatki? ............................................................................................687
Meneder dodatków Excela ...............................................................................................688
Tworzenie dodatków .........................................................................................................689
Przykad tworzenia dodatku ..............................................................................................690
Tworzenie opisu dla dodatku ......................................................................................691
Tworzenie dodatku ......................................................................................................692
Instalowanie dodatku ...................................................................................................693
Testowanie dodatków ..................................................................................................694
Dystrybucja dodatków .................................................................................................694
Modyfikowanie dodatku .............................................................................................695
Porównanie plików XLAM i XLSM .................................................................................695
Pliki XLAM — przynaleno do kolekcji z poziomu VBA ......................................696
Widoczno plików XLSM i XLAM ..........................................................................697
Arkusze i wykresy w plikach XLSM i XLAM ............................................................697
Dostp do procedur VBA w dodatku ..........................................................................698
Spis treci
15
Przetwarzanie dodatków za pomoc kodu VBA ................................................................701
Waciwoci obiektu AddIn ........................................................................................703
Korzystanie z dodatku jak ze skoroszytu ....................................................................706
Zdarzenia zwizane z obiektami Addin .......................................................................706
Optymalizacja wydajnoci dodatków ................................................................................707
Problemy z dodatkami .......................................................................................................708
Zapewnienie, e dodatek zosta zainstalowany ...........................................................708
Odwoywanie si do innych plików z poziomu dodatku .............................................710
Wykrywanie waciwej wersji Excela dla dodatku .....................................................710
Cz VI
Tworzenie aplikacji ..........................................................711
Rozdzia 22. Tworzenie pasków narzdzi ............................................................... 713
Wprowadzenie do pracy ze Wstk .................................................................................713
VBA i Wstka ..................................................................................................................715
Dostp do polece Wstki .........................................................................................718
Praca ze Wstk .........................................................................................................719
Aktywowanie karty .....................................................................................................721
Dostosowywanie Wstki do wasnych potrzeb ................................................................721
Prosty przykad kodu RibbonX ...................................................................................722
Prosty przykad kodu RibbonX — podejcie 2 ...........................................................725
Kolejny przykad kodu RibbonX .................................................................................730
Demo formantów Wstki ...........................................................................................732
Przykad uycia formantu DynamicMenu ...................................................................738
Wicej wskazówek dotyczcych modyfikacji Wstki ...............................................741
Tworzenie pasków narzdzi w starym stylu ......................................................................742
Ograniczenia funkcjonalnoci tradycyjnych pasków narzdzi w Excelu 2010 ...........742
Kod tworzcy pasek narzdzi ......................................................................................743
Rozdzia 23. Praca z menu podrcznym ................................................................ 747
Obiekt CommandBar .........................................................................................................747
Rodzaje obiektów CommandBar .................................................................................748
Wywietlanie menu podrcznych ................................................................................748
Odwoania do elementów kolekcji CommandBars .....................................................749
Odwoania do formantów w obiekcie CommandBar ...................................................750
Waciwoci formantów obiektu CommandBar ..........................................................751
Wywietlanie wszystkich elementów menu podrcznego ...........................................752
Wykorzystanie VBA do dostosowywania menu podrcznego ..........................................754
Resetowanie menu podrcznego .................................................................................755
Wyczanie menu podrcznego ...................................................................................755
Wyczanie wybranych elementów menu podrcznego ..............................................756
Dodawanie nowego elementu do menu podrcznego Cell ..........................................756
Dodawanie nowego podmenu do menu podrcznego .................................................758
Menu podrczne i zdarzenia ..............................................................................................761
Automatyczne tworzenie i usuwanie menu podrcznego ............................................761
Wyczanie lub ukrywanie elementów menu podrcznego .........................................762
Tworzenie kontekstowych menu podrcznych ............................................................762
Rozdzia 24. Tworzenie systemów pomocy w aplikacjach .................................. 765
Systemy pomocy w aplikacjach Excela .............................................................................765
Pomoc online ...............................................................................................................766
Systemy pomocy wykorzystujce komponenty Excela .....................................................766
Wykorzystanie komentarzy do tworzenia systemów pomocy .....................................768
Wykorzystanie pól tekstowych do wywietlania pomocy ...........................................769
16 Spis
treci
Wykorzystanie arkusza do wywietlania tekstu pomocy .............................................770
Wywietlanie pomocy w oknie formularza UserForm ................................................771
Wywietlanie pomocy w oknie przegldarki sieciowej .....................................................774
Zastosowanie plików w formacie HTML ....................................................................774
Zastosowanie plików w formacie MHTML ................................................................775
Wykorzystanie systemu HTML Help ................................................................................776
Wykorzystanie metody Help do wywietlania pomocy w formacie HTML Help .......779
czenie pliku pomocy z aplikacj ....................................................................................780
Przypisanie tematów pomocy do funkcji VBA ...........................................................780
Rozdzia 25. Tworzenie aplikacji przyjaznych dla uytkownika ........................... 783
Czym jest aplikacja przyjazna dla uytkownika? ..............................................................783
Kreator amortyzacji poyczek ...........................................................................................783
Obsuga Kreatora amortyzacji poyczek .....................................................................784
Struktura skoroszytu Kreatora amortyzacji poyczek .................................................785
Jak dziaa Kreator amortyzacji poyczek? ..................................................................786
Potencjalne usprawnienia Kreatora amortyzacji poyczek ..........................................793
Wskazówki dotyczce projektowania aplikacji .................................................................793
Cz VII Inne zagadnienia .............................................................795
Rozdzia 26. Problem kompatybilnoci aplikacji .................................................. 797
Co to jest kompatybilno? ................................................................................................797
Rodzaje problemów ze zgodnoci ...................................................................................798
Unikaj uywania nowych funkcji i mechanizmów ............................................................799
Czy aplikacja bdzie dziaa na komputerach Macintosh? ................................................801
Praca z 64-bitow wersj Excela .......................................................................................802
Tworzenie aplikacji dla wielu wersji narodowych ............................................................803
Aplikacje obsugujce wiele jzyków .........................................................................805
Obsuga jzyka w kodzie VBA ...................................................................................805
Wykorzystanie waciwoci lokalnych ........................................................................806
Identyfikacja ustawie systemu ...................................................................................807
Ustawienia daty i godziny ...........................................................................................809
Rozdzia 27. Operacje na plikach wykonywane za pomoc kodu VBA ........... 811
Najczciej wykonywane operacje na plikach ...................................................................811
Zastosowanie polece jzyka VBA do wykonywania operacji na plikach ..................812
Zastosowanie obiektu FileSystemObject .....................................................................816
Wywietlanie rozszerzonych informacji o plikach ............................................................820
Operacje z plikami tekstowymi .........................................................................................821
Otwieranie plików tekstowych ....................................................................................822
Odczytywanie plików tekstowych ...............................................................................823
Zapisywanie danych do plików tekstowych ................................................................823
Przydzielanie numeru pliku .........................................................................................823
Okrelanie lub ustawianie pozycji w pliku ..................................................................824
Instrukcje pozwalajce na odczytywanie i zapisywanie plików ..................................824
Przykady wykonywania operacji na plikach ....................................................................825
Importowanie danych z pliku tekstowego ...................................................................825
Eksportowanie zakresu do pliku tekstowego ...............................................................827
Importowanie pliku tekstowego do zakresu ................................................................828
Rejestrowanie wykorzystania Excela ..........................................................................829
Filtrowanie zawartoci pliku tekstowego ....................................................................830
Eksportowanie zakresu komórek do pliku HTML ......................................................830
Eksportowanie zakresu komórek do pliku XLM .........................................................832
Spis treci
17
Pakowanie i rozpakowywanie plików ...............................................................................835
Pakowanie plików do formatu ZIP ..............................................................................836
Rozpakowywanie plików ZIP .....................................................................................838
Dziaania z obiektami danych ActiveX (ADO) .................................................................838
Rozdzia 28. Operacje na skadnikach jzyka VBA ............................................. 841
Podstawowe informacje o rodowisku IDE .......................................................................841
Model obiektowy rodowiska IDE ....................................................................................843
Kolekcja VBProjects ...................................................................................................844
Wywietlanie wszystkich skadników projektu VBA ........................................................846
Wywietlanie wszystkich procedur VBA w arkuszu .........................................................847
Zastpowanie moduu uaktualnion wersj .......................................................................848
Zastosowanie jzyka VBA do generowania kodu VBA .......................................................850
Zastosowanie VBA do umieszczenia formantów na formularzu UserForm ......................852
Operacje z formularzami UserForm w fazie projektowania i wykonania ....................852
Dodanie 100 przycisków CommandButton w fazie projektowania .............................854
Programowe tworzenie formularzy UserForm ..................................................................855
Prosty przykad formularza UserForm ........................................................................855
Uyteczny (ale ju nie tak prosty) przykad dynamicznego formularza UserForm .....857
Rozdzia 29. Moduy klas ......................................................................................... 863
Czym jest modu klasy? .....................................................................................................863
Przykad: utworzenie klasy NumLock ...............................................................................864
Wstawianie moduu klasy ...........................................................................................865
Dodawanie kodu VBA do moduu klasy .....................................................................865
Wykorzystanie klasy NumLock ..................................................................................867
Dodatkowe informacje na temat moduów klas .................................................................868
Programowanie waciwoci obiektów .......................................................................868
Programowanie metod obiektów .................................................................................870
Zdarzenia definiowane w module klasy ......................................................................871
Przykad: klasa CSVFileClass ...........................................................................................871
Zmienne poziomu moduu dla klasy CSVFileClass ....................................................872
Definicje waciwoci klasy CSVFileClass .................................................................872
Definicje metod klasy CSVFileClass ..........................................................................872
Wykorzystanie obiektów CSVFileClass .....................................................................874
Rozdzia 30. Praca z kolorami ................................................................................. 877
Definiowanie kolorów .......................................................................................................877
Model kolorów RGB ...................................................................................................878
Model kolorów HSL ....................................................................................................878
Konwersja kolorów .....................................................................................................879
Skala szaroci ....................................................................................................................880
Zamiana kolorów na skal szaroci .............................................................................883
Wywietlanie wykresów w skali szaroci ....................................................................883
Eksperymenty z kolorami ..................................................................................................885
Praca z motywami dokumentów ........................................................................................886
Kilka sów o motywach dokumentów .........................................................................886
Kolory motywów dokumentów ...................................................................................887
Wywietlanie wszystkich kolorów motywu ................................................................890
Praca z obiektami Shape ....................................................................................................893
Kolor ta ksztatu .........................................................................................................893
Ksztaty i kolory motywów .........................................................................................895
Przykady ksztatów ....................................................................................................897
Modyfikacja kolorów wykresów .......................................................................................897
18 Spis
treci
Rozdzia 31. Czsto zadawane pytania na temat programowania w Excelu .... 901
FAQ — czyli czsto zadawane pytania .............................................................................901
Ogólne pytania dotyczce programu Excel .......................................................................902
Pytania dotyczce edytora Visual Basic ............................................................................908
Pytania dotyczce procedur ...............................................................................................911
Pytania dotyczce funkcji ..................................................................................................916
Pytania dotyczce obiektów, waciwoci, metod i zdarze ..............................................919
Pytania dotyczce formularzy UserForm ...........................................................................928
Pytania dotyczce dodatków .............................................................................................932
Pytania dotyczce pasków polece ....................................................................................934
Cz VIII Dodatki ..............................................................................937
Dodatek A
Zasoby online dotyczce Excela ...................................................... 939
Pomoc systemowa programu Excel ...................................................................................939
Pomoc techniczna firmy Microsoft ...................................................................................940
Opcje pomocy technicznej ..........................................................................................940
Baza wiedzy firmy Microsoft ......................................................................................940
Strona domowa programu Microsoft Excel .................................................................940
Strona domowa pakietu Microsoft Office ...................................................................940
Internetowe grupy dyskusyjne ...........................................................................................941
Dostp do grup dyskusyjnych za pomoc czytników grup dyskusyjnych ................941
Dostp do grup dyskusyjnych za pomoc przegldarki sieciowej ...............................941
Wyszukiwanie informacji w grupach dyskusyjnych ...................................................942
Strony internetowe WWW ................................................................................................943
Strona domowa Spreadsheet .......................................................................................943
Strona Daily Dose of Excel .........................................................................................944
Strona o Excelu Jona Peltiera ......................................................................................944
Pearson Software Consulting ......................................................................................944
Contextures .................................................................................................................944
Pointy Haired Dilbert ..................................................................................................944
Strony o Excelu Davida McRitchie .............................................................................945
Mr. Excel .....................................................................................................................945
Dodatek B
Instrukcje i funkcje VBA ...................................................................... 947
Wywoywanie funkcji Excela w instrukcjach VBA ..........................................................950
Dodatek C
Kody bdów VBA ............................................................................... 957
Dodatek D
Zawarto pyty CD-ROM ................................................................... 961
Skorowidz ............................................................................................. 977
Rozdzia 11.
Przykady
i techniki programowania
w jzyku VBA
W tym rozdziale:
Zastosowanie VBA do pracy z zakresami
Zastosowanie VBA do pracy ze skoroszytami i arkuszami
Tworzenie wasnych funkcji i uywanie ich w formuach arkusza i procedurach VBA
Przykady technik programowania w jzyku VBA
Przykady zastosowania funkcji interfejsu API
Nauka poprzez praktyk
Wierz, e nauka programowania odbywa si znacznie szybciej, kiedy pracujemy na kon-
kretnych przykadach omawianych zagadnie, a Czytelnicy poprzednich wyda ksiki
zdecydowanie utwierdzaj mnie w tym przekonaniu. Takie podejcie sprawdza si zwasz-
cza dla programistów pracujcych z jzykiem VBA. Dobrze opracowany przykad o wiele
lepiej objania zagadnienie ni teoretyczny opis. W zwizku z tym zrezygnowaem z mate-
riau referencyjnego, w którym dokadnie opisywano by wszystkie, nawet najdrobniejsze
aspekty jzyka VBA, a zamiast tego przygotowaem przykady demonstrujce uyteczne,
praktyczne techniki programowania przy uyciu Excela.
Poprzednie rozdziay tej czci ksiki odpowiednio przygotoway Czytelników do pozna-
wania zagadnie omawianych w tym rozdziale, natomiast w systemie pomocy programu
Excel znajdziesz wszystkie informacje, które tutaj zostay pominite. W tym rozdziale
zwikszy si nieco tempo i zaprezentowanych zostanie sporo przykadów rozwizujcych
problemy spotykane w praktyce i pozwalajcych pogbi wiedz na temat jzyka VBA.
342
Cz III
Jzyk Visual Basic for Applications
Przykady omawiane w tym rozdziale zostay podzielone na sze kategorii:
Praca z zakresami
Praca ze skoroszytami i arkuszami
Techniki programowania w jzyku VBA
Uyteczne funkcje, których warto uywa w procedurach VBA
Uyteczne funkcje, których moesz uywa w formuach arkuszowych
Wywoania funkcji i procedur Windows API
W kolejnych rozdziaach naszej ksiki znajdziesz szereg przykadów procedur dotyczcych
m.in. takich zagadnie, jak wykresy, tabele przestawne, zdarzenia, formularze UserForm
i inne.
Przetwarzanie zakresów
Przykady zamieszczone w tym podrozdziale demonstruj, w jaki sposób za pomoc jzyka
VBA mona manipulowa zakresami arkusza.
W szczególnoci znajdziesz tutaj przykady procedur, które pozwalaj na kopiowanie
i przenoszenie zakresów komórek, zaznaczanie zakresów komórek, identyfikacj typów
danych przechowywanych w danym zakresie komórek, wprowadzanie wartoci do komórek
przez uytkownika, wyszukiwanie pierwszej pustej komórki w kolumnie, zatrzymywa-
nie makra w celu umoliwienia uytkownikowi zaznaczenia zakresu, zliczanie komórek
w zakresie, przechodzenie w ptli i przetwarzanie kolejnych komórek zakresu oraz kilka
innych operacji, czsto wykonywanych na zakresach komórek arkusza.
Kopiowanie zakresów
Rejestrator makr Excela jest bardzo przydatny nie tyle do generowania wydajnego,
uytecznego kodu ródowego, co do „odkrywania” nazw odpowiednich obiektów, metod
i waciwoci. Kod ródowy generowany przez rejestrator makr nie zawsze jest opty-
malny i efektywny, ale zwykle pozwala uzyska sporo przydatnych informacji.
Przykadowo po zarejestrowaniu prostej operacji kopiowania i wklejania, generowanych
jest pi wierszy kodu ródowego jzyka VBA:
Sub Makro1()
Range("A1").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Wygenerowany kod najpierw powoduje zaznaczenie i skopiowanie komórki
A1
, a nastpnie,
po zaznaczeniu komórki
B1
, procedura wykonuje operacj wklejania. Jednak w jzyku VBA
nie jest konieczne zaznaczanie obiektu, który bdzie przetwarzany. O tej istotnej sprawie
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
343
Jak korzysta z przykadów zamieszczonych w tym rozdziale?
Nie wszystkie przykady zamieszczone w tym rozdziale mog spenia rol samodzielnych progra-
mów, ale zawsze maj posta wykonywalnych procedur, które moesz dostosowa do wasnych
potrzeb i uy w swoich aplikacjach.
W trakcie lektury powiniene na bieco pracowa z komputerem i samodzielnie testowa opisy-
wane w tym rozdziale przykady (i nie tylko). Jeszcze lepiej bdzie, jeeli bdziesz próbowa samo-
dzielnie modyfikowa przykady i sprawdza, jaki bdzie efekt tych modyfikacji. Mog Ci zagwa-
rantowa, e takie praktyczne dowiadczenia bd o wiele bardziej pomocne ni przeczytanie od
deski do deski ksiki zawierajcej tylko teoretyczn stron programowania w jzyku VBA.
nie dowiedziaby si nigdy, gdyby wzorowa si tylko na kodzie ródowym zarejestro-
wanego makra, w którym w dwóch instrukcjach zostaa uyta metoda
Select
. Zamiast
tego, moesz posuy si znacznie prostsz procedur, która nie zaznacza adnych komórek
i korzysta z tego, e metoda
Copy
moe uy argumentu reprezentujcego miejsce doce-
lowe kopiowanego zakresu.
Sub CopyRange()
Range("A1").Copy Range("B1")
End Sub
W obu powyszych makrach przyjto zaoenie, e arkusz, w którym wykonywana jest
operacja, jest aktywny. Aby skopiowa zakres do innego arkusza lub skoroszytu, wystarczy
odpowiednio zdefiniowa odwoanie do zakresu docelowego. W poniszym przykadzie
zakres jest kopiowany z arkusza
Arkusz1
skoroszytu Plik1.xlsm do arkusza
Arkusz2
sko-
roszytu Plik2.xlsm. Poniewa odwoania s w peni kwalifikowane, procedura zadziaa
niezalenie od tego, który skoroszyt bdzie aktywny.
Sub CopyRange2()
Workbooks("Plik1.xlsm").Sheets("Arkusz1").Range("A1").Copy _
Workbooks("Plik2.xlsm").Sheets("Arkusz2").Range("A1")
End Sub
Kolejna metoda wykonania tej operacji polega na zastosowaniu zmiennych obiektowych
reprezentujcych zakresy, tak jak to zostao zilustrowane w kodzie poniszego przykadu:
Sub CopyRange3()
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = Workbooks("Plik1.xlsm").Sheets("Arkusz1").Range("A1")
Set Rng2 = Workbooks("Plik2.xlsm").Sheets("Arkusz2").Range("A1")
Rng1.Copy Rng2
End Sub
Jak si zapewne domylasz, kopiowanie nie jest ograniczone tylko do jednej komórki na
raz. Przykadowo, procedura przedstawiona poniej kopiuje duy zakres komórek. Zwró
uwag na fakt, e miejsce docelowe jest tutaj identyfikowane tylko przez jedn komórk —
górn lew komórk wklejanego zakresu. Uycie jednej komórki dziaa dokadnie tak,
jak podczas rcznego kopiowania i wklejania komórek arkusza.
Sub CopyRange4()
Range("A1:C800").Copy Range("D1")
End Sub
344
Cz III
Jzyk Visual Basic for Applications
Przenoszenie zakresów
Instrukcje jzyka VBA suce do przenoszenia zakresu s bardzo podobne do instrukcji
uywanych podczas kopiowania zakresów, tak jak to zostao zaprezentowane na poni-
szym przykadzie. Rónica polega na tym, e zamiast metody
Copy
uyta zostaa metoda
Cut
. Pamitaj, e musisz poda tylko lokalizacj górnej, lewej komórki zakresu docelowego.
W przykadzie przedstawionym poniej 18 komórek (z zakresu
A1:C6
) przenosimy
w nowy obszar, rozpoczynajcy si od adresu
H1
.
Sub MoveRange1()
Range("A1:C6").Cut Range("H1")
End Sub
Kopiowanie zakresu o zmiennej wielkoci
W wielu przypadkach konieczne jest skopiowanie zakresu komórek, dla którego dokadna
liczba wierszy i kolumn okrelajcych jego wielko nie jest z góry znana. Przykadowo
moesz dysponowa skoroszytem ledzcym tygodniow sprzeda, w którym liczba wierszy
zmienia si kadego tygodnia po wprowadzeniu nowych danych.
Na rysunku 11.1 pokazano bardzo czsto spotykany typ arkusza. Zawarty w nim zakres
skada si z kilku wierszy, których liczba zmienia si kadego tygodnia. Poniewa nie
wiesz, jaki jest adres zakresu w danej chwili, podczas pisania makra kopiujcego zakres
bdziesz uwzgldni nieco dodatkowego kodu ródowego.
Rysunek 11.1.
Liczba wierszy zakresu
danych zmienia si
kadego tygodnia
Ponisze makro ilustruje sposób kopiowania zakresu komórek z arkusza
Arkusz1
do arkusza
Arkusz2
(poczwszy od komórki
A1
). Makro wykorzystuje waciwo
CurrentRegion
,
która zwraca obiekt
Range
odpowiadajcy blokowi komórek otaczajcych okrelon
komórk (w tym przypadku o adresie
A1
).
Sub CopyCurrentRegion2()
Range("A1").CurrentRegion.Copy Sheets("Arkusz2").Range("A1")
End Sub
Zastosowanie waciwoci
CurrentRegion jest równowane przejciu na kart Narzdzia
gówne i wybraniu polecenia Znajd i zaznacz/Przejd do — specjalnie, znajdujcego
si w grupie opcji Edycja i nastpnie zaznaczeniu opcji Biecy obszar (zamiast tego
moesz równie nacisn kombinacj klawiszy Ctrl+Shift+*). Aby przekona si, jak
to dziaa, podczas wykonywania tych polece powiniene zarejestrowa makro. Zazwyczaj
warto waciwoci
CurrentRegion reprezentuje prostoktny blok komórek otoczony
przez puste wiersze i kolumny.
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
345
Wskazówki dotyczce przetwarzania zakresów
W trakcie przetwarzania zakresów powiniene pamita o kilku wanych kwestiach.
W jzyku VBA, do przetwarzania zakresu nie jest konieczne jego uprzednie zanaczenie.
Nie moesz zaznaczy zakresu, który znajduje si na nieaktywnym arkuszu, zatem, jeeli Twoja
procedura zaznacza zakres, powizany z nim arkusz musi by aktywny. W celu uaktywnienia
okrelonego arkusza mona uy metody
Activate kolekcji Worksheets.
Pamitaj, e rejestrator makr nie generuje zbyt wydajnego kodu ródowego. Najlepiej utworzy
makro przy uyciu rejestratora, a nastpnie jego kod ródowy zmodyfikowa w celu zwikszenia
efektywnoci.
W kodzie ródowym jzyka VBA warto stosowa nazwane zakresy. Przykadowo odwoanie
Range("Total") jest znacznie bardziej czytelne ni odwoanie Range("D45"). W tym drugim
przypadku dodanie wiersza powyej wiersza 45. spowoduje zmian adresu komórki
i w konsekwencji konieczne bdzie zmodyfikowanie makra tak, aby uywao zakresu
o poprawnym adresie (
D46).
Jeeli w trakcie zaznaczania zakresów korzystasz z rejestratora makr, upewnij si, e makro
rejestrowane jest przy uyciu odwoa wzgldnych. Aby to zrobi, przejd na kart Deweloper
i nacinij przycisk Uyj odwoa wzgldnych, znajdujcy si w grupie opcji Kod.
Po uruchomieniu makra przetwarzajcego kolejne komórki aktualnie zaznaczonego zakresu,
uytkownik moe zaznacza cae wiersze lub kolumny. W wikszoci przypadków nie ma potrzeby
przetwarzania wszystkich komórek zaznaczonego zakresu. Tworzone makro powinno definiowa
podzbiór zaznaczenia zawierajcy wycznie niepuste komórki. Wicej szczegóowych informacji
na ten temat znajdziesz w podrozdziale „Wydajne przetwarzanie komórek zaznaczonego zakresu
przy uyciu ptli” w dalszej czci rozdziau.
Excel pozwala zaznacza wiele obszarów jednoczenie. Na przykad moesz zaznaczy pierwszy
zakres, wcisn klawisz Ctrl i zaznaczy kolejny zakres. Makro powinno dokona sprawdzenia
zakresu i podj odpowiedni decyzj. Zapoznaj si z zawartoci punktu „Okrelanie typu
zaznaczonego zakresu” w dalszej czci rozdziau.
Jeeli zakres komórek, który chcesz skopiowa jest tabel (zdefiniowan przy uycia
polecenia Tabela, znajdujcego si nakrcie Narzdzia gówne, w grupie polece Tabele),
moesz uy kodu przedstawionego poniej (który zakada, e tabela ma nazw
Table1
.
Sub CopyTable
Range("Table1[#All]").Copy Sheets("Sheet 2").Range("A1")
End Sub
Zaznaczanie oraz identyfikacja rónego typu zakresów
Wikszo operacji wykonywanych przez instrukcje jzyka VBA opiera si na zakre-
sach — poprzez definiowanie zakresów lub identyfikowanie zakresów w celu wykony-
wania operacji na komórkach do nich nalecych.
Oprócz waciwoci
CurrentRegion
(o której mówilimy ju wczeniej) powiniene
równie pozna metod
End
obiektu
Range
. Metoda ta pobiera jeden argument okrelajcy
kierunek, w którym zostanie wykonane zaznaczenie. Ponisze polecenie zaznacza zakres
rozpoczynajcy si od aktywnej komórki i koczcy na ostatniej niepustej dolnej komórce:
Range(ActiveCell, ActiveCell.End(xlDown)).Select
346
Cz III
Jzyk Visual Basic for Applications
Poniej zamieszczamy kolejny przykad, w którym zostaa zdefiniowana komórka bdca
pocztkiem zakresu:
Range(Range("A2"), Range("A2").End(xlDown)).Select
Jak mona si domyli, trzy pozostae stae (
xlUp
,
xlToLeft
,
xlToRight
) symuluj kom-
binacje klawiszy zaznaczajce komórki w innych kierunkach.
Korzystajc z waciwoci
ActiveCell w powizaniu z metod End, powiniene zachowa
szczególn ostrono. Jeeli aktywna komórka znajduje si na kocu zakresu lub jeeli
w skad zakresu wchodzi jedna lub wicej pustych komórek, wyniki dziaania metody
End
mog by zupenie inne od oczekiwanych.
Na doczonym dysku CD-ROM znajduje si skoroszyt (Zaznaczanie zakresów.xlsm)
ilustrujcy najczciej spotykane rodzaje zaznacze zakresów. Po jego otwarciu, w menu
podrcznym pojawi si nowe podmenu o nazwie Przykady zaznacze. Poszczególne
polecenia menu umoliwiaj uytkownikowi zapoznanie si z przykadami rónych rodzajów
zaznacze (patrz rysunek 11.2).
Ponisze makro, o nazwie
SelectCurrentRegion
znajduje si w przykadowym skoro-
szycie i symuluje nacinicie kombinacji klawiszy Ctrl+Shift+*:
Sub SelectCurrentRegion()
ActiveCell.CurrentRegion.Select
End Sub
Bardzo czsto zaznaczanie komórek jest tylko wstpem do innych operacji, na przykad
formatowania. Procedur zaznaczajc komórki mona atwo przystosowa do tego celu.
Procedura przedstawiona poniej jest prost modyfikacj makra
SelectCurrentRegion
,
która nie zaznacza komórek, a jedynie formatuje zakres zdefiniowany jako biecy obszar
otaczajcy aktywn komórk. Inne procedury znajdujce si w przykadowym skoroszycie
te mog zosta przystosowane w ten sposób.
Sub FormatCurrentRegion()
ActiveCell.CurrentRegion.Font.Bold = True
End Sub
Wprowadzanie wartoci do komórki
Kolejna procedura przedstawiona poniej demonstruje, jak poprosi uytkownika o poda-
nie wartoci i wstawi j do komórki
A1
aktywnego arkusza:
Sub GetValue1()
Range("A1").Value = InputBox("Wprowad warto:")
End Sub
Na rysunku 11.3 pokazano wygld okna umoliwiajcego wprowadzenie wartoci.
Przedstawiona procedura moe sprawia jednak pewien problem. Jeeli uytkownik naci-
nie w oknie dialogowym przycisk Cancel, procedura usunie wszelkie dane ju znajdujce
si w komórce. Ponisza zmodyfikowana wersja procedury sprawdza, czy zosta naci-
nity przycisk Cancel i jeeli tak, nie dokonuje zmiany zawartoci komórki:
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
347
Rysunek 11.2. Niestandardowe menu podrczne w tym skoroszycie ilustruje zaznaczanie zakresów
o rónej wielkoci
Rysunek 11.3.
Funkcja InputBox
pobiera warto,
która zostanie
umieszczona
w komórce
Sub GetValue2()
Dim UserEntry As String
UserEntry = InputBox("Wprowad warto:")
If UserEntry <> "" Then Range("A1").Value = UserEntry
End Sub
348
Cz III
Jzyk Visual Basic for Applications
W wielu przypadkach konieczne bdzie sprawdzenie poprawnoci danych wprowadzo-
nych przez uytkownika. Na przykad chcesz, aby uytkownik wprowadzi liczb z zakresu
od 1 do 12. W poniszym przykadzie zademonstrowano jedn z metod sprawdzenia
poprawnoci danych. Niepoprawna warto jest ignorowana, a okno wywietlane ponow-
nie. Operacja jest powtarzana do momentu wprowadzenia prawidowej wartoci lub naci-
nicia przycisku Cancel.
Sub GetValue3()
Dim UserEntry As Variant
Dim Msg As String
Const MinVal As Integer = 1
Const MaxVal As Integer = 12
Msg = "Wprowad warto z zakresu od " & MinVal & " do " & MaxVal
Do
UserEntry = InputBox(Msg)
If UserEntry = "" Then Exit Sub
If IsNumeric(UserEntry) Then
If UserEntry >= MinVal And UserEntry <= MaxVal Then Exit Do
End If
Msg = "Wprowadzona warto jest NIEPOPRAWNA."
Msg = Msg & vbNewLine
Msg = Msg & "Wprowad warto z zakresu od " & MinVal & " do " & MaxVal
Loop
ActiveSheet.Range("A1").Value = UserEntry
End Sub
Jeeli uytkownik wprowadzi niepoprawn warto, program odpowiednio zmieni tre
wywietlanego komunikatu (patrz rysunek 11.4).
Rysunek 11.4.
Sprawdzenie
poprawnoci danych
wprowadzonych
przez uytkownika
przy uyciu funkcji
InputBox jzyka VBA
Skoroszyt zawierajcy wszystkie trzy przykady (Funkcja InputBox.xlsm) znajdziesz
na pycie CD-ROM doczonej do ksiki.
Wprowadzanie wartoci do nastpnej pustej komórki
Czsto wymagan operacj jest wprowadzenie wartoci do nastpnej pustej komórki
kolumny lub wiersza. Ponisza procedura prosi uytkownika o podanie imienia i wartoci,
a nastpnie wprowadza dane do nastpnego pustego wiersza (patrz rysunek 11.5).
Sub GetData()
Dim NextRow As Long
Dim Entry1 As String, Entry2 As String
Do
'Odszukaj nastpny pusty wiersz
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
349
Rysunek 11.5.
Makro wstawiajce
dane do nastpnego
pustego wiersza
arkusza
' Popro o wprowadzenie danych
Entry1 = InputBox("Podaj imi:")
If Entry1 = "" Then Exit Sub
Entry2 = InputBox("Podaj warto:")
If Entry2 = "" Then Exit Sub
' Zapisz dane w arkuszu
Cells(NextRow, 1) = Entry1
Cells(NextRow, 2) = Entry2
Loop
End Sub
Dla uproszczenia nasza procedura w aden sposób nie sprawdza poprawnoci wprowa-
dzanych danych. Zwró uwag, e w procedurze nie zosta okrelony warunek zakocze-
nia ptli. W celu jej opuszczenia uyto instrukcji
Exit Sub
, która jest wykonywana po
naciniciu przycisku Cancel w oknie wprowadzania danych.
Skoroszyt zawierajcy oba przykady (NastpnaPustaKomórka.xlsm) znajdziesz na pycie
CD-ROM doczonej do ksiki.
Zwró uwag na instrukcj okrelajc warto zmiennej
NextRow
. Jeeli nie rozumiesz,
w jaki sposób procedura dziaa, spróbuj rcznie wykona realizowan przez ni operacj:
uaktywnij ostatni komórk w kolumnie
A
(co w przypadku Excela 2010 oznacza komórk
o adresie
A1048576
), nacinij klawisz End i nastpnie nacinij klawisz (strzaka w gór).
W efekcie zostanie zaznaczona ostatnia niepusta komórka kolumny
A
. Waciwo
Row
zwraca numer wiersza tej komórki. W celu uzyskania numeru kolejnego pustego wiersza
(poniej) warto ta jest zwikszana o jeden. Zamiast umieszcza na „sztywno” adres
ostatniej komórki wiersza
A
, uyta zostaa metoda
Rows.Count
, dziki czemu nasza proce-
dura bdzie poprawnie dziaaa równie z poprzednimi wersjami programu Excel (w których
maksymalna liczba wierszy w arkuszu jest duo mniejsza).
Z tak metod zaznaczania nastpnej pustej komórki zwizany jest drobny problem. Jeeli
kolumna jest zupenie pusta, jako nastpny pusty wiersz metoda wyznaczy wiersz
2
. Na
szczcie dodanie odpowiedniego kodu, który bdzie zapobiega takiemu zachowaniu, nie
jest trudnym zadaniem.
350
Cz III
Jzyk Visual Basic for Applications
Wstrzymywanie dziaania makra w celu umoliwienia
pobrania zakresu wyznaczonego przez uytkownika
Zdarzaj si sytuacje, w których makro musi by w pewien sposób interaktywne. Na
przykad moesz utworzy makro, które wstrzymuje dziaanie, pozwalajc uytkownikowi
na zaznaczenie wybranego zakresu komórek. Procedura opisana w tym punkcie demonstruje
sposób wykonania zadania przy uyciu metody
InputBox
Excela.
Nie naley myli metody
InputBox Excela z funkcj jzyka VBA o takiej samej nazwie.
Co prawda obie funkcje maj tak sam nazw, ale nie s takie same.
Ponisza procedura
Sub
demonstruje sposób zatrzymania pracy makra i umoliwienia
uytkownikowi zaznaczenia zakresu komórek. Po wznowieniu dziaania procedura wstawia
odpowiedni formu do wszystkich komórek zaznaczonego zakresu.
Sub GetUserRange()
Dim UserRange As Range
Prompt = "Zaznacz wybrany zakres komórek."
Title = "Wybieranie zakresu komórek"
' Wywietlanie okna dialogowego
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) 'Zaznaczanie zakresu komórek
On Error GoTo 0
' Czy okno dialogowe zostao anulowane?
If UserRange Is Nothing Then
MsgBox "Operacja anulowana."
Else
UserRange.Formula = "=RAND()"
End If
End Sub
Okno dialogowe zostao przedstawione na rysunku 11.6.
Skoroszyt z tym przykadem (Zaznacz zakres.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki.
Podanie argumentu
Type
o wartoci
8
ma kluczowe znaczenie dla powyszej procedury.
Oprócz tego powiniene równie zwróci uwag na zastosowanie instrukcji
On Error
Resume Next
. To polecenie powoduje, e bd, który wystpi gdy uytkownik nacinie przy-
cisk Anuluj, bdzie ignorowany. Jeeli tak si stanie, nie zostanie zdefiniowana zmienna
obiektowa
UserRange
. W powyszym przykadzie jest wywietlane okno zawierajce komu-
nikat o treci Operacja anulowana. Gdy uytkownik nacinie przycisk OK, wykonywanie
makra bdzie kontynuowane. Instrukcja
On Error GoTo 0
przywraca standardow obsug
bdów.
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
351
Rysunek 11.6. Okno dialogowe uyte do wstrzymania dziaania makra
Nawiasem mówic, sprawdzanie poprawnoci zaznaczonego zakresu nie jest konieczne,
poniewa zajmie si tym Excel.
Pamitaj, podczas uywania metody
InputBox do zaznaczania zakresu komórek
odwieanie ekranu powinno by zawsze wczone. W przeciwnym wypadku nie bdziesz
w stanie zaznaczy zakresu komórek. Do sterowania odwieaniem ekranu w trakcie
wykonywania makra powiniene uy waciwoci
ScreenUpdating obiektu Application.
Zliczanie zaznaczonych komórek
Mona stworzy makro przetwarzajce zaznaczone komórki zakresu. Aby okreli liczb
komórek w zaznaczonym zakresie (lub dowolnym innym), naley uy waciwoci
Count
obiektu
Range
. Przykadowo ponisza instrukcja wywietla w oknie komunikatu liczb
aktualnie zaznaczonych komórek:
MsgBox Selection.Count
Jeeli aktywny arkusz zawiera zakres o nazwie
dane
, polecenie przedstawione poniej przy-
pisze liczb jego komórek zmiennej
CellCount
:
CellCount = Range("dane").Count
Moesz równie okreli liczb wierszy lub kolumn w zakresie. Ponisze wyraenie wyzna-
cza liczb kolumn znajdujcych si w aktualnie zaznaczonym zakresie:
Selection.Columns.Count
352
Cz III
Jzyk Visual Basic for Applications
Ze wzgldu na fakt, e w najnowszych wersjach Excela (2007 i 2010) maksymalne
rozmiary arkusza zostay znaczco powikszone, waciwo
Count moe w pewnych
sytuacjach generowa bd. Waciwo
Count wykorzystuje dane typu Long, zatem
najwiksza warto, jak ta waciwo moe przechowywa, to 2 147 483 647, std
jeeli uytkownik zaznaczy na przykad 2048 penych kolumn (czyli 2 147 483 648
komórek), waciwo
Count wygeneruje bd. Na szczcie firma Microsoft dodaa
w Excelu, poczwszy od wersji 2007, now waciwo:
CountLarge. Waciwo ta uywa
danych typu
Double, które pozwalaj na przechowywanie liczb z zakresu do 1,79+E308.
Wnioski? W przytaczajcej wikszoci przypadków waciwo
Count bdzie dziaaa
najzupeniej poprawnie. Jeeli jednak zakadasz, e bdziesz zlicza arkusze zawierajce
naprawd due iloci komórek (na przykad wszystkie komórki arkusza), to zamiast
waciwoci
Count powiniene uy waciwoci CountLarge.
Oczywicie liczb wierszy zakresu mona równie okreli przy uyciu waciwoci
Rows
. Ponisza instrukcja okrela liczb wierszy zakresu o nazwie
dane
i przypisuje war-
to zmiennej
RowCount
:
RowCount = Range("dane").Rows.Count
Okrelanie typu zaznaczonego zakresu
Excel obsuguje kilka typów zaznacze zakresów. Oto one:
pojedyncza komórka,
cigy zakres komórek,
jedna lub wicej kolumn,
jeden lub wicej wierszy,
cay arkusz,
dowolna kombinacja wyej wymienionych typów, czyli zaznaczenie wielokrotne.
Poniewa istnieje kilka typów zaznacze, w trakcie przetwarzania zakresu procedura jzyka
VBA nie moe przewidzie, jaki jest typ zaznaczenia. Na przykad zaznaczony obszar
moe skada si z dwóch zakresów komórek,
A1:A10
i
C1:C10
(aby utworzy zaznaczenie
wielokrotne, podczas zaznaczania kolejnych zakresów trzymaj wcinity klawisz Ctrl).
Jeeli zakres zosta zdefiniowany przez wiele zaznacze, obiekt
Range
bdzie si skada
z oddzielnych obszarów. Aby stwierdzi, czy zaznaczenie jest zaznaczeniem wielokrotnym,
naley uy metody
Areas
zwracajcej kolekcj
Areas
. Kolekcja reprezentuje wszystkie
obszary wchodzce w skad zakresu stworzonego poprzez zaznaczenie wielokrotne.
W celu stwierdzenia, czy wybrany zakres posiada wiele obszarów, naley zastosowa wyra-
enie podobne do poniszego:
NumAreas = Selection.Areas.Count
Jeeli zmienna
NumAreas
zawiera warto wiksz od
1
, zaznaczenie jest zaznaczeniem
wielokrotnym.
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
353
Poniej zamieszczono kod funkcji o nazwie
AreaType
, która zwraca acuch tekstu opisu-
jcy rodzaj zaznaczenia.
Function AreaType(RangeArea As Range) As String
' Funkcja okrela rodzaj zaznaczonego obszaru
Select Case True
Case RangeArea.Cells.CountLarge = 1
AreaType = "Komórka"
Case RangeArea.CountLarge = Cells.CountLarge
AreaType = "Arkusz"
Case RangeArea.Rows.Count = Cells.Rows.Count
AreaType = "Kolumna"
Case RangeArea.Columns.Count = Cells.Columns.Count
AreaType = "Wiersz"
Case Else
AreaType = "Blok"
End Select
End Function
Funkcja jako argument pobiera obiekt
Range
i zwraca jeden z piciu acuchów opisujcych
obszar —
Komórka
,
Arkusz
,
Kolumna
,
Wiersz
lub
Blok
. W celu okrelenia, które z piciu
wyrae porównujcych ma warto
True
, funkcja korzysta z konstrukcji
Select Case
.
Na przykad: jeeli zakres skada si z jednej komórki, funkcja zwróci acuch
Komórka
.
Jeeli liczba komórek zakresu jest równa liczbie komórek arkusza, funkcja zwróci a-
cuch
Arkusz
. Jeeli liczba wierszy zakresu jest równa liczbie wierszy arkusza, funkcja
zwróci acuch
Kolumna
. Jeeli liczba kolumn zakresu jest równa liczbie kolumn arkusza,
funkcja zwróci acuch
Wiersz
. Jeeli adne wyraenie instrukcji
Case
nie bdzie miao
wartoci
True
, funkcja zwróci acuch
Blok
.
Zauwa, e do liczenia komórek uyta zostaa waciwo
CountLarge
, poniewa — jak
ju wspominalimy wczeniej — cakowita liczba zaznaczonych komórek w arkuszu
moe teoretycznie przekroczy limit typu danych waciwoci
Count
.
Skoroszyt Zaznaczanie.xlsm znajdujcy si na doczonym dysku CD-ROM zawiera
procedur
RangeDescription, która posuguje si funkcj AreaType w celu wywietlenia
okna komunikatu opisujcego typ zaznaczenia aktualnego zakresu. Na rysunku 11.7
pokazano przykad jej dziaania. Zrozumienie zasad dziaania funkcji bdzie stanowio
dobre przygotowanie do wykonywania kolejnych operacji na obiektach klasy
Range.
Excel pozwala na wykonanie wielu identycznych zaznacze. Na przykad, jeeli trzymajc
wcinity klawisz Ctrl, piciokrotnie klikniesz komórk
A1, zaznaczenie bdzie zoone
z piciu identycznych obszarów. Procedura
RangeDescription uwzgldnia taka sytuacj
i nie zlicza wielokrotnie tych samych komórek.
Wydajne przetwarzanie komórek zaznaczonego zakresu
przy uyciu ptli
Jednym z czciej wykonywanych przez makra zada jest sprawdzanie poszczególnych
komórek zakresu i wykonywanie okrelonych operacji, jeeli komórka spenia zadane kryte-
rium. Kolejna procedura, której kod przedstawiamy poniej, jest wanie przykadem takiego
makra. Procedura
ColorNegaitve
ustawia czerwony kolor ta dla wszystkich komórek zazna-
czenia, które przechowuj warto ujemn. Kolor ta pozostaych komórek jest zerowany.
354
Cz III
Jzyk Visual Basic for Applications
Rysunek 11.7. Procedura AboutRangeSelection analizuje aktualnie zaznaczony zakres
Poniszy przykad zosta opracowany tylko i wycznie w celach edukacyjnych.
W zastosowaniach praktycznych o wiele lepszym rozwizaniem bdzie po prostu
uycie mechanizmu formatowania warunkowego.
Sub ColorNegative()
' Jeeli warto jest ujemna, zmienia kolor ta komórki na czerwony
Dim cell As Range
If TypeName(Selection) <> "Range" Then Exit Sub
Application.ScreenUpdating = False
For Each cell In Selection
If cell.Value < 0 Then
cell.Interior.ColorIndex = RGB(255, 0, 0)
Else
cell.Interior.ColorIndex = xlNone
End If
Next cell
End Sub
Z pewnoci procedura
ColorNegative
zadziaa, ale zawiera powany bd. Dla przykadu:
co si stanie, jeeli obszar danych na arkuszu jest bardzo may, a uytkownik zaznaczy
na przykad ca kolumn? Albo 10 kolumn? Albo moe nawet cay arkusz? Jak si
zapewne sam domylasz, nie ma adnej potrzeby sprawdzania wszystkich pustych, nie-
uywanych komórek arkusza, nie mówic ju o tym, e przy duych zaznaczonych obsza-
rach uytkownik z pewnoci poddaby si, zanim caa procedura dobiegaby do koca.
Lepszym rozwizaniem jest procedura
ColorNegative2
, której kod przedstawiamy poniej.
W tej poprawionej wersji utworzylimy zmienn obiektow
WorkRange
, której zawarto
odpowiada czci wspólnej zaznaczonego obszaru i uytego obszaru arkusza. Przyka-
dow sytuacj ilustruje rysunek 11.8. Jak wida, zaznaczona jest caa kolumna
D
(czyli
1 048 576 komórek), ale uyty zakres komórek sprowadza si ju tylko do obszaru
B2:I18
.
Zatem przeciciem tych dwóch obszarów jest zakres
D2:D18
, co jest zdecydowanie
mniejszym zakresem ni pocztkowe zaznaczenie. Rónica pomidzy czasem przetwa-
rzania 15 komórek a 1 048 576 komórek bdzie naprawd znaczca.
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
355
Rysunek 11.8.
Zastosowanie
przecicia
zaznaczonego zakresu
i uytego obszaru
arkusza skutkuje
znaczcym
zmniejszeniem
liczby komórek,
które bdziemy
musieli przetwarza
Sub ColorNegative2()
' Jeeli warto jest ujemna, zmienia kolor ta komórki na czerwony
Dim WorkRange As Range
Dim cell As Range
If TypeName(Selection) <> "Range" Then Exit Sub
Application.ScreenUpdating = False
Set WorkRange = Application.Intersect(Selection, _
ActiveSheet.UsedRange)
For Each cell In WorkRange
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End Sub
Procedura
ColorNegative2
jest znacznie lepsza, ale mimo to nadal nie jest tak wydajna,
jak by powinna, a to z prostego powodu — nadal niepotrzebnie przetwarza puste komórki.
Trzecia wersja naszej procedury,
ColorNegative3
, jest nieco dusza, ale jednoczenie
o wiele bardziej wydajna. W celu wygenerowania dwóch podzbiorów zaznaczenia uyem
metody
SpecialCells
. Pierwszy podzbiór obejmuje jedynie komórki zawierajce stae
numeryczne (
ConstantCells
), natomiast drugi — komórki przechowujce formuy nume-
ryczne (
FormulaCells
). Komórki obu podzbiorów s nastpnie przetwarzane za pomoc
dwóch konstrukcji
For Each ... Next
. W efekcie przetwarzane s tylko niepuste komórki
zawierajce wartoci numeryczne, dziki czemu uzyskujemy znaczce zwikszenie szyb-
koci dziaania makra.
Sub ColorNegative3()
' Jeeli warto jest ujemna, zmienia kolor ta komórki na czerwony
Dim FormulaCells As Range, ConstantCells As Range
Dim cell As Range
If TypeName(Selection) <> "Range" Then Exit Sub
356
Cz III
Jzyk Visual Basic for Applications
Application.ScreenUpdating = False
' Tworzy podzbiory oryginalnego obszaru zaznaczenia
On Error Resume Next
Set FormulaCells = Selection.SpecialCells(xlFormulas, xlNumbers)
Set ConstantCells = Selection.SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
' Przetwarzanie komórek zawierajcych formuy
If Not FormulaCells Is Nothing Then
For Each cell In FormulaCells
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
' Przetwarzanie komórek zawierajcych stae wartoci numeryczne
If Not ConstantCells Is Nothing Then
For Each cell In ConstantCells
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
End Sub
Zastosowanie instrukcji
On Error jest konieczne, poniewa metoda SpecialCells
generuje bd, gdy adna komórka nie spenia kryterium.
Skoroszyt z tymi przykadami (Tworzenie wydajnych ptli.xlsm), zawierajcy trzy wersje
procedury
ColorNegative, znajdziesz na pycie CD-ROM doczonej do ksiki.
Usuwanie wszystkich pustych wierszy
Ponisza procedura usuwa puste wiersze aktywnego arkusza. Procedura jest szybka
i wydajna, poniewa nie sprawdza wszystkich wierszy, a jedynie wiersze uywane przez
zakres, który jest identyfikowany za pomoc waciwoci
UsedRange
obiektu
Worksheet
.
Sub DeleteEmptyRows()
Dim LastRow As Long
Dim r As Long
Dim Counter As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.UsedRange.Rows.Count + _
ActiveSheet.UsedRange.Rows(1).Row - 1
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then
Rows(r).Delete
Counter = Counter + 1
End If
Next r
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
357
Application.ScreenUpdating = True
MsgBox Counter & " pustych wierszy zostao usunitych."
End Sub
Pierwszym krokiem jest okrelenie ostatnio uywanego wiersza, a nastpnie przypisanie
jego numeru zmiennej
LastRow
. Nie jest to takie proste, jak mogoby si wydawa, ponie-
wa uywany zakres moe, ale nie musi rozpoczyna si od wiersza
1
. A zatem warto
zmiennej
LastRow
jest obliczana poprzez okrelenie liczby wierszy uywanego zakresu,
dodanie numeru pierwszego wiersza zakresu i odjcie jedynki.
W celu stwierdzenia, czy wiersz jest pusty, procedura korzysta z funkcji arkuszowej
COUNTA
(
ILE.NIEPUSTYCH
) Excela. Jeeli dla okrelonego wiersza funkcja zwróci warto
0
, oznacza
to, e jest pusty. Procedura przetwarza wiersze od dou do góry, a ponadto w ptli
For ...
Next
uywa ujemnej wartoci skoku (
Step
). Jest to niezbdne, poniewa operacja usu-
wania wierszy powoduje, e wszystkie kolejne wiersze s przesuwane w gór arkusza.
Jeeli ptla przetwarzaaby wiersze od góry do dou, jej licznik po usuniciu wiersza nie
miaby waciwej wartoci.
Makro wykorzystuje równie inn zmienn,
Counter
, do ledzenia liczby usunitych wier-
szy. Liczba ta jest wywietlana w oknie dialogowym, kiedy procedura koczy dziaanie.
Skoroszyt z tym przykadem (Usu puste wiersze.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki.
Powielanie wierszy
Przykad, który omówimy w tym podrozdziale, ilustruje sposób wykorzystania VBA do
tworzenia duplikatów istniejcych wierszy. Na rysunku 11.9 przedstawiono wygld skoro-
szytu zawierajcego informacje o loterii biurowej. Kolumna A przechowuje imiona graczy,
kolumna B liczb losów zakupionych przez poszczególne osoby, a w kolumnie C znaj-
duje si liczba losowa (wygenerowana przy uyciu funkcji
RAND
). Zwycizca zostanie
wyoniony przez sortowanie danych w kolumnie C (wygrywa osoba, do której zostaa przy-
pisana najwiksza liczba losowa).
Rysunek 11.9.
Zadanie polega
na powieleniu
istniejcych wierszy
w oparciu o wartoci
z kolumny B
358
Cz III
Jzyk Visual Basic for Applications
Nasze zadanie polega na powieleniu istniejcych wierszy dla poszczególnych osób, tak
aby kada z nich miaa tyle osobnych wierszy, ile zakupia losów. Na przykad Barbara
kupia 2 losy, a zatem powinnimy dla niej utworzy 2 osobne wiersze. Kod procedury
realizujcej takie zadanie zosta zamieszczony poniej:
Sub DupeRows()
Dim cell As Range
' Pierwsza komórka z liczb losów
Set cell = Range("B2")
Do While Not IsEmpty(cell)
If cell > 1 Then
Range(cell.Offset(1, 0), cell.Offset(cell.Value - 1, _
0)).EntireRow.Insert
Range(cell, cell.Offset(cell.Value - 1, 1)).EntireRow.FillDown
End If
Set cell = cell.Offset(cell.Value, 0)
Loop
End Sub
Zmiennej obiektowej
cell
zostaje przypisana warto reprezentujca komórk
B2
, czyli
pierwsz komórk, w której przechowywana jest liczba zakupionych losów. Ptla
wstawia nowy wiersz i nastpnie kopiuje go odpowiedni ilo razy przy uyciu metody
FillDown
. Zmienna
cell
jest inkrementowana tak, aby wskazywaa na liczb losów zaku-
pionych przez kolejn osob i procedura kontynuuje dziaanie a do momentu napotkania
pierwszej pustej komórki. Na rysunku 11.10 przedstawiono wygld arkusza po zakocze-
niu dziaania procedury.
Skoroszyt z tym przykadem (Powielanie wierszy.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki.
Okrelanie, czy zakres zawiera si w innym zakresie
Ponisza funkcja
InRange
pobiera dwa argumenty (obiekty klasy
Range
) i zwraca warto
True
, jeeli pierwszy zakres zawiera si w drugim:
Function InRange(rng1, rng2) As Boolean
' Zwraca warto True, jeeli rng1 jest podzbiorem rng2
InRange = False
If rng1.Parent.Parent.Name = rng2.Parent.Parent.Name Then
If rng1.Parent.Name = rng2.Parent.Name Then
If Union(rng1, rng2).Address = rng2.Address Then
InRange = True
End If
End If
End If
End Function
Kod funkcji
InRange
moe si wydawa do zoony, poniewa program musi spraw-
dzi, czy dwa zakresy znajduj si w tym samym arkuszu i skoroszycie. Procedura posu-
guje si waciwoci
Parent
zwracajc kontener obiektu. Przykadowo ponisze wyra-
enie zwraca nazw arkusza bdcego kontenerem obiektu
rng1
, do którego jest
wykonywane odwoanie:
rng1.Parent.Name
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
359
Rysunek 11.10.
Procedura dodaa
do arkusza nowe
wiersze w oparciu
o wartoci
w kolumnie B
Kolejne wyraenie zwraca nazw skoroszytu obiektu
rng1
:
rng1.Parent.Parent.Name
Funkcja
Union
jzyka VBA zwraca obiekt klasy
Range
reprezentujcy sum dwóch
obiektów
Range
. Zakres sumy obejmuje wspólne komórki dwóch zakresów. Jeeli adres
sumy dwóch zakresów jest taki sam jak adres drugiego zakresu, oznacza to, e pierwszy
zakres zawiera si w drugim.
Skoroszyt z tym przykadem (Funkcja InRange.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki.
Okrelanie typu danych zawartych w komórce
Excel oferuje kilka wbudowanych funkcji arkuszowych, które pomagaj w okrelaniu typu
danych zawartych w komórce. Naley do nich zaliczy funkcje
CZY.TEKST
,
CZY.LOGICZNA
i
CZY.BD
. Dodatkowo jzyk VBA zawiera funkcje
IsEmpty
,
IsDate
i
IsNumeric
.
360
Cz III
Jzyk Visual Basic for Applications
Zamieszczona poniej funkcja
CellType
akceptuje argument
Range
i zwraca acuch
(
Pusta
,
Tekst
,
Logiczny
,
Bd
,
Data
,
Czas
lub
Liczba
) opisujcy typ danych zawartych
w górnej lewej komórce zakresu. Funkcja moe zosta uyta w formule arkusza lub wywo-
ana z innej procedury jzyka VBA.
Function CellType(Rng)
' Zwraca typ górnej lewej komórki zakresu
Dim TheCell As Range
Set TheCell = Rng.Range("A1")
Select Case True
Case IsEmpty(TheCell)
CellType = "Pusta"
Case Application.IsText(TheCell)
CellType = "Tekst"
Case Application.IsLogical(TheCell)
CellType = "Logiczny"
Case Application.IsErr(TheCell)
CellType = "Bd"
Case IsDate(TheCell)
CellType = "Data"
Case InStr(1, TheCell.Text, ":") <> 0
CellType = "Czas"
Case IsNumeric(TheCell)
CellType = "Liczba"
End Select
End Function
Zwró uwag na uycie polecenia
Set TheCell
. Funkcja
CellType
akceptuje argument
Range
dowolnej wielkoci, ale ta instrukcja powoduje, e funkcja przetwarza tylko górn
lew komórk zakresu reprezentowanego przez zmienn
TheCell
.
Skoroszyt z tym przykadem (Funkcja CellType.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki.
Odczytywanie i zapisywanie zakresów
Wiele operacji wykonywanych w arkuszach kalkulacyjnych wymaga przenoszenia warto-
ci z tablicy do zakresu lub z zakresu do tablicy. Z jakiego powodu Excel o wiele szyb-
ciej odczytuje dane z zakresu, ni je w nim zapisuje. Procedura
WriteReadRange
, której kod
przedstawiamy poniej, demonstruje porównanie wzgldnych szybkoci wykonywania
operacji zapisu i odczytu zakresu.
Procedura tworzy tablic, a nastpnie za pomoc ptli
For ... Next
zapisuje jej zawarto
w zakresie i ponownie wczytuje j do tablicy. Przy uyciu funkcji
Timer
jzyka VBA
oblicza czas wymagany do wykonania kadej operacji.
Sub WriteReadRange()
Dim MyArray()
Dim Time1 As Double
Dim NumElements As Long, i As Long
Dim WriteTime As String, ReadTime As String
Dim Msg As String
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
361
NumElements = 60000
ReDim MyArray(1 To NumElements)
' Wypenienie tablicy
For i = 1 To NumElements
MyArray(i) = i
Next i
' Zapis danych z tablicy do zakresu
Time1 = Timer
For i = 1 To NumElements
Cells(i, 1) = MyArray(i)
Next i
WriteTime = Format(Timer - Time1, "00:00")
' Odczytanie danych z zakresu i zaadowanie do tablicy
Time1 = Timer
For i = 1 To NumElements
MyArray(i) = Cells(i, 1)
Next i
ReadTime = Format(Timer - Time1, "00:00")
' Wywietlenie wyników
Msg = "Czas zapisu: " & WriteTime
Msg = Msg & vbCrLf
Msg = Msg & "Czas odczytu: " & ReadTime
MsgBox Msg, vbOKOnly, NumElements & " elementów"
End Sub
Na moim komputerze przepisanie tablicy liczcej 60 000 elementów do zakresu komórek
zajo 58 sekund, natomiast wczytanie zakresu komórek do tablicy tylko 1 sekund.
Lepsza metoda zapisywania zakresu
W poprzednim przykadzie, aby przenie zawarto tablicy do zakresu arkusza, uyto
ptli
For ... Next
. W tym podrozdziale zademonstrujemy wydajniejsz metod osigni-
cia tego samego celu.
Kod procedury przedstawiony poniej ilustruje najbardziej oczywisty (ale niestety nie naj-
wydajniejszy) sposób wypeniania zakresu danymi. Do umieszczenia danych w zakresie
ponownie zostaa uyta ptla
For ... Next
.
Sub LoopFillRange()
' Wypenia zakres przy uyciu ptli przetwarzajcej komórki
Dim CellsDown As Long, CellsAcross As Integer
Dim CurrRow As Long, CurrCol As Integer
Dim StartTime As Double
Dim CurrVal As Long
' Pobranie wymiarów
CellsDown = Val(InputBox("Ile komórek w pionie?"))
If CellsDown = 0 Then Exit Sub
CellsAcross = Val(InputBox("Ile komórek w poziomie?"))
If CellsAcross = 0 Then Exit Sub
362
Cz III
Jzyk Visual Basic for Applications
' Zarejestrowanie czasu rozpoczcia
StartTime = Timer
' Przy uyciu ptli przetwarza komórki i wstawia wartoci
CurrVal = 1
Application.ScreenUpdating = False
For CurrRow = 1 To CellsDown
For CurrCol = 1 To CellsAcross
ActiveCell.Offset(CurrRow - 1, _
CurrCol - 1).Value = CurrVal
CurrVal = CurrVal + 1
Next CurrCol
Next CurrRow
' Wywietla czas trwania operacji
Application.ScreenUpdating = True
MsgBox Format(Timer - StartTime, "00.00") & " sekund"
End Sub
Kolejny przykad demonstruje o wiele szybsz metod pozwalajc na uzyskanie tego
samego efektu. Procedura wstawia do tablicy poszczególne wartoci, a nastpnie za
pomoc jednej instrukcji przenosi zawarto tablicy do zakresu.
Sub ArrayFillRange()
' Wypenienie zakresu poprzez transfer tablicy
Dim CellsDown As Long, CellsAcross As Integer
Dim i As Long, j As Integer
Dim StartTime As Double
Dim TempArray() As Long
Dim TheRange As Range
Dim CurrVal As Long
' Pobranie wymiarów
CellsDown = Val(InputBox("Ile komórek w pionie?"))
If CellsDown = 0 Then Exit Sub
CellsAcross = Val(InputBox("Ile komórek w poziomie?"))
If CellsAcross = 0 Then Exit Sub
' Zarejestrowanie czasu rozpoczcia
StartTime = Timer
' Przeskalowanie wymiarów tablicy tymczasowej
ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
' Zdefiniowanie zakresu w arkuszu
Set TheRange = ActiveCell.Range(Cells(1, 1), _
Cells(CellsDown, CellsAcross))
' Wypenienie tablicy tymczasowej
CurrVal = 0
Application.ScreenUpdating = False
For i = 1 To CellsDown
For j = 1 To CellsAcross
TempArray(i, j) = CurrVal + 1
CurrVal = CurrVal + 1
Next j
Next i
' Transfer tablicy tymczasowej do arkusza
TheRange.Value = TempArray
' Wywietlanie czas trwania operacji
Application.ScreenUpdating = True
MsgBox Format(Timer - StartTime, "00.00") & " sekund"
End Sub
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
363
W moim systemie wypenienie zakresu o wymiarach 1000
u250 komórek (250 000 komó-
rek) przy uyciu ptli zajo 10,05 sekundy. Metoda oparta na transferze zawartoci tablicy
do uzyskania identycznego efektu potrzebowaa zaledwie 0,18 sekundy, czyli dziaaa
okoo 50 razy szybciej! Jaki wniosek wynika z tego przykadu? Jeeli chcesz przenie do
arkusza du ilo danych, wszdzie, gdzie tylko jest to moliwe, unikaj stosowania ptli.
Osignite czasy w duej mierze zale od obecnoci w arkuszu innych formu. W praktyce
lepsze czasy otrzymasz w sytuacji, kiedy podczas testu nie bd otwarte inne skoroszyty
zawierajce makra lub kiedy przeczysz Excela w tryb rcznego przeliczania arkusza.
Na pycie CD-ROM doczonej do ksiki znajdziesz skoroszyt Wypenianie zakresu
przy uyciu ptli i tablicy.xlsm, zawierajcy procedury
WriteReadRange, LoopFillRange
oraz
ArrayFillRange.
Przenoszenie zawartoci tablic jednowymiarowych
W poprzednim przykadzie zastosowano tablic dwuwymiarow, dobrze sprawdzajc
si w arkuszach, w których dane przechowywane s w uporzdkowanej strukturze wierszy
i kolumn.
Aby przenie zawarto tablicy jednowymiarowej, zakres musi mie orientacj poziom —
czyli inaczej mówic, posiada jeden wiersz z wieloma kolumnami. Jeeli jednak musisz
uy zakresu pionowego, najpierw bdziesz musia dokona transponowania tablicy z pozio-
mej na pionow. Aby to zrobi, moesz uy funkcji arkuszowej
TRANSPOSE
(
TRANSPONUJ
)
Excela. Ponisze polecenie przenosi tablic liczc 100 elementów do pionowego zakresu
arkusza (
A1:A100
):
Range("A1:A100").Value = Application.WorksheetFunction.Transpose(MyArray)
Funkcja arkuszowa
TRANSPONUJ nie bdzie dziaaa z tablicami, w których
przechowywanych jest wicej ni 65 536 elementów.
Przenoszenie zawartoci zakresu do tablicy typu Variant
W tym podrozdziale omówimy kolejn metod przetwarzania zawartoci arkusza przy
uyciu jzyka VBA. W poniszym przykadzie zawarto zakresu komórek jest przeno-
szona do dwuwymiarowej tablicy typu
Variant
. Nastpnie w oknach komunikatów s
wywietlane górne granice kadego wymiaru tablicy.
Sub RangeToVariant()
Dim x As Variant
x = Range("A1:L600").Value
MsgBox UBound(x, 1)
MsgBox UBound(x, 2)
End Sub
W pierwszym oknie komunikatu jest wywietlana warto 600 (liczba wierszy oryginal-
nego zakresu), natomiast w drugim — 12 (liczba kolumn). Jak sam si przekonasz, prze-
niesienie zawartoci zakresu do tablicy typu
Variant
odbywa si prawie natychmiast.
364
Cz III
Jzyk Visual Basic for Applications
Ponisza procedura wczytuje zawarto zakresu o nazwie
data
do tablicy typu
Variant
,
wykonuje na poszczególnych elementach tablicy prost operacj mnoenia, a nastpnie
ponownie przenosi dane zapisane w tablicy do zakresu.
Sub RangeToVariant2()
Dim x As Variant
Dim r As Long, c As Integer
' Wczytanie danych do tablicy typu Variant
x = Range("data").Value
' Wykonanie ptli dla tablicy typu Variant
For r = 1 To UBound(x, 1)
For c = 1 To UBound(x, 2)
' Mnoenie kolejnych elementów tablicy przez 2
x(r, c) = x(r, c) * 2
Next c
Next r
' Ponowne przeniesienie zawartoci tablicy typu Variant do arkusza
Range("data") = x
End Sub
Jak sam si moesz przekona, caa procedura dziaa naprawd szybko. Przetwarzanie
30 000 komórek na moim komputerze trwao poniej jednej sekundy.
Skoroszyt z tym przykadem (Przenoszenie tablicy typu Variant.xlsm) znajdziesz na pycie
CD-ROM doczonej do ksiki.
Zaznaczanie komórek na podstawie wartoci
Nasz kolejny przykad ilustruje, w jaki sposób mona zaznacza wybrane komórki w opar-
ciu o ich wartoci. Co ciekawe, Excel nie posiada swojego wasnego mechanizmu, który
umoliwiaby bezporednie wykonanie takiej operacji. Poniej przedstawiamy kod pro-
cedury
SelectByValue
, której zadaniem jest zaznaczenie komórek zakresu zawierajcych
wartoci ujemne, aczkolwiek mona to w atwy sposób zmodyfikowa i dopasowa do
wasnych potrzeb.
Sub SelectByValue()
Dim Cell As Object
Dim FoundCells As Range
Dim WorkRange As Range
If TypeName(Selection) <> "Range" Then Exit Sub
' Sprawdzamy wszystko czy tylko zaznaczenie
If Selection.CountLarge = 1 Then
Set WorkRange = ActiveSheet.UsedRange
Else
Set WorkRange = Application.Intersect(Selection, ActiveSheet.UsedRange)
End If
' Zredukuj liczb przetwarzanych komórek do komórek zawierajcych wartoci numeryczne
On Error Resume Next
Set WorkRange = WorkRange.SpecialCells(xlConstants, xlNumbers)
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
365
If WorkRange Is Nothing Then Exit Sub
On Error GoTo 0
' Sprawdzaj w ptli kolejne komórki i dodawaj do zakresu FoundCells, jeeli speniaj kryterium
For Each Cell In WorkRange
If Cell.Value < 0 Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Else
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell
' Poka komunikat lub zaznacz komórki
If FoundCells Is Nothing Then
MsgBox "Nie znaleziono komórek speniajcych kryterium."
Else
FoundCells.Select
End If
End Sub
Procedura rozpoczyna dziaanie od sprawdzenia zaznaczonego zakresu. Jeeli jest to
pojedyncza komórka, przeszukiwany jest cay arkusz. Jeeli zaznaczone zostay co naj-
mniej 2 komórki, wtedy przeszukiwany jest tylko zaznaczony zakres. Zakres, który bdzie
przeszukiwany, jest nastpnie redefiniowany poprzez uycie metody
SpecialCells
do
utworzenia obiektu klasy
Range
, który skada si tylko z komórek zawierajcych wartoci
numeryczne.
Kod w ptli
For ... Next
sprawdza wartoci kolejnych komórek. Jeeli komórka spe-
nia zadane kryterium (czyli jej zawarto jest mniejsza od 0), komórka jest dodawana za
pomoc metody
Union
do obiektu
FoundCells
klasy
Range
. Zwró uwag na fakt, e nie
moesz uy metody
Union
dla pierwszej komórki — jeeli zakres
FoundCells
nie zawiera
adnych komórek, próba uycia metody
Union
spowoduje wygenerowanie bdu. Wanie
dlatego w naszym programie zamiecilimy kod sprawdzajcy, czy zawarto zakresu
FoundCells
to
Nothing
.
Kiedy ptla koczy swoje dziaanie, obiekt
FoundCells
skada si z komórek, które spe-
niaj kryterium wyszukiwania (jeeli nie znaleziono adnych komórek, jego zawartoci
bdzie
Nothing
). Jeeli adna komórka nie speni kryteriów, na ekranie zostanie wywie-
tlone okno dialogowe z odpowiednim komunikatem. W przeciwnym razie komórki spe-
niajce kryteria zostan zaznaczone.
Skoroszyt z tym przykadem (Zaznaczanie wedug wartoci.xlsm) znajdziesz na pycie
CD-ROM doczonej do ksiki.
Kopiowanie niecigego zakresu komórek
Jeeli kiedykolwiek próbowae kopiowa niecigy zakres komórek, z pewnoci prze-
konae si, e Excel nie obsuguje takiej operacji. Próba jej wykonania koczy si wywie-
tleniem komunikatu Wykonanie tego polecenia dla kilku zakresów nie jest moliwe.
366
Cz III
Jzyk Visual Basic for Applications
Jedynym wyjtkiem jest tutaj sytuacja, w której próbujesz kopiowa zaznaczenie wielo-
krotne, skadajce si z caych wierszy lub kolumn. Excel pozwala na wykonanie takiej
operacji.
Kiedy napotykasz takie ograniczenia w programie Excel, zazwyczaj moesz je obej za
pomoc odpowiedniego makra. Procedura, któr omówimy poniej, jest przykadem takiego
makra, które pozwala na kopiowanie wielu zaznaczonych zakresów komórek w inne
miejsce arkusza.
Sub CopyMultipleSelection()
Dim SelAreas() As Range
Dim PasteRange As Range
Dim UpperLeft As Range
Dim NumAreas As Long, i As Long
Dim TopRow As Long, LeftCol As Long
Dim RowOffset As Long, ColOffset As Long
If TypeName(Selection) <> "Range" Then Exit Sub
' Zapisuje poszczególne zakresy jako osobne obiekty klasy Range
NumAreas = Selection.Areas.Count
ReDim SelAreas(1 To NumAreas)
For i = 1 To NumAreas
Set SelAreas(i) = Selection.Areas(i)
Next
' Okrela górn, lew komórk zaznaczonych obszarów
TopRow = ActiveSheet.Rows.Count
LeftCol = ActiveSheet.Columns.Count
For i = 1 To NumAreas
If SelAreas(i).Row < TopRow Then TopRow = SelAreas(i).Row
If SelAreas(i).Column < LeftCol Then LeftCol = SelAreas(i).Column
Next
Set UpperLeft = Cells(TopRow, LeftCol)
' Pobiera adres obszaru docelowego
On Error Resume Next
Set PasteRange = Application.InputBox _
(Prompt:="Podaj adres lewej, górnej komórki obszaru docelowego: ", _
Title:="Kopiowanie wielu zakresów", _
Type:=8)
On Error GoTo 0
' Jeeli operacja zostaa anulowana, zakocz dziaanie
If TypeName(PasteRange) <> "Range" Then Exit Sub
' Upewnij si, e uywamy tylko lewej, górnej komórki
Set PasteRange = PasteRange.Range("A1")
' Kopiowanie i wklejanie poszczególnych zakresów
For i = 1 To NumAreas
RowOffset = SelAreas(i).Row - TopRow
ColOffset = SelAreas(i).Column - LeftCol
SelAreas(i).Copy PasteRange.Offset(RowOffset, ColOffset)
Next i
End Sub
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
367
Na rysunku 11.11 przedstawiono okno dialogowe, w którym uytkownik powinien zde-
finiowa adres obszaru docelowego.
Rysunek 11.11. Zastosowanie metody InputBox programu Excel do pobierania lokalizacji komórki
Skoroszyt z tym przykadem (Kopiowanie wielu zakresów.xlsm) znajdziesz na pycie
CD-ROM doczonej do ksiki. Dodatkowo w skoroszycie tym znajduje si inna wersja
tej procedury, która ostrzega uytkownika, jeeli w wyniku kopiowania zostan nadpisane
dane istniejce w obszarze docelowym.
Przetwarzanie skoroszytów i arkuszy
Kolejne przykady omawiane w tym podrozdziale demonstruj metody przetwarzania sko-
roszytów i arkuszy przy uyciu jzyka VBA.
Zapisywanie wszystkich skoroszytów
Ponisza procedura przy uyciu ptli przetwarza wszystkie skoroszyty w kolekcji
Workbooks
i ponownie zapisuje kady plik, który by ju wczeniej zapisany:
Public Sub SaveAllWorkbooks()
Dim Book As Workbook
For Each Book In Workbooks
If Book.Path <> "" Then Book.Save
Next Book
End Sub
368
Cz III
Jzyk Visual Basic for Applications
Zwró uwag na waciwo
Path
. Jeeli waciwo
Path
danego skoroszytu jest pusta,
oznacza to, e jego plik nigdy nie zosta zapisany (jest to nowy skoroszyt). Procedura
ignoruje tego typu skoroszyty i zapisuje tylko te, których waciwo
Path
ma ustawion
dowoln warto.
Zapisywanie i zamykanie wszystkich skoroszytów
Ponisza procedura przy uyciu ptli przetwarza kolekcj
Workbooks
, zapisujc i zamy-
kajc wszystkie skoroszyty:
Sub CloseAllWorkbooks()
Dim Book As Workbook
For Each Book In Workbooks
If Book.Name <> ThisWorkbook.Name Then
Book.Close savechanges:=True
End If
Next Book
ThisWorkbook.Close savechanges:=True
End Sub
Aby okreli, czy dany skoroszyt to skoroszyt zawierajcy kod aktualnie wykonywanej
procedury, nasza procedura uywa instrukcji
If
umieszczonej w ptli
For-Next
. Jest to
konieczne, poniewa zamknicie takiego skoroszytu spowoduje przerwanie wykonywania
kodu, na skutek czego inne skoroszyty nie zostan zapisane. Po zamkniciu wszystkich
innych skoroszytów procedura zamyka równie swój macierzysty skoroszyt.
Ukrywanie wszystkich komórek arkusza
poza zaznaczonym zakresem
Procedura, któr omówimy w tym podrozdziale, ukrywa wszystkie komórki arkusza poza
tymi, które znajduj si w aktualnie zaznaczonym zakresie. Przykad takiej sytuacji przed-
stawiono na rysunku 11.12.
Sub HideRowsAndColumns()
Dim row1 As Long, row2 As Long
Dim col1 As Long, col2 As Long
If TypeName(Selection) <> "Range" Then Exit Sub
' Jeeli ostatni wiersz lub kolumna s ukryte, odkryj wszystko i zakocz dziaanie
If Rows(Rows.Count).EntireRow.Hidden Or _
Columns(Columns.Count).EntireColumn.Hidden Then
Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
Exit Sub
End If
row1 = Selection.Rows(1).Row
row2 = row1 + Selection.Rows.Count - 1
col1 = Selection.Columns(1).Column
col2 = col1 + Selection.Columns.Count - 1
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
369
Rysunek 11.12. Wszystkie komórki arkusza poza zaznaczonym zakresem (G8:K17) zostay ukryte
Application.ScreenUpdating = False
On Error Resume Next
' Ukryj wiersze
Range(Cells(1, 1), Cells(row1 - 1, 1)).EntireRow.Hidden = True
Range(Cells(row2 + 1, 1), Cells(Rows.Count, 1)).EntireRow.Hidden = True
' Ukryj kolumny
Range(Cells(1, 1), Cells(1, col1 - 1)).EntireColumn.Hidden = True
Range(Cells(1, col2 + 1), Cells(1, Columns.Count)).EntireColumn.Hidden = True
End Sub
Jeeli zaznaczony obszar arkusza skada si z kilku niecigych zakresów komórek, baz
do ukrywania wierszy i kolumn jest pierwszy zakres.
Skoroszyt z tym przykadem (Ukrywanie wierszy i kolumn.xlsm) znajdziesz na pycie
CD-ROM doczonej do ksiki.
Synchronizowanie arkuszy
Jeeli korzystae kiedykolwiek ze skoroszytów wieloarkuszowych, to wiesz zapewne,
e Excel nie potrafi synchronizowa danych z poszczególnych arkuszy skoroszytu. Innymi
sowy, nie ma moliwoci automatycznego zaznaczenia tego samego zakresu i ustawienia
górnej, lewej komórki takiego multizakresu. Ponisze makro jzyka VBA jako bazy uywa
aktywnego arkusza, a na pozostaych arkuszach skoroszytu wykonuje nastpujce operacje:
370
Cz III
Jzyk Visual Basic for Applications
zaznacza taki sam zakres, jak w przypadku aktywnego arkusza;
ustawia tak sam górn lew komórk okna, jak w aktywnym arkuszu.
Oto kod ródowy naszej procedury:
Sub SynchSheets()
' Ustawia we wszystkich arkuszach t sam aktywn komórk i lew, górn komórk zakresu
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
Dim UserSheet As Worksheet, sht As Worksheet
Dim TopRow As Long, LeftCol As Integer
Dim UserSel As String
Application.ScreenUpdating = False
' Zapamituje aktualny arkusz
Set UserSheet = ActiveSheet
' Zapisuje informacje z aktywnego arkusza
TopRow = ActiveWindow.ScrollRow
LeftCol = ActiveWindow.ScrollColumn
UserSel = ActiveWindow.RangeSelection.Address
' Przechodzi w ptli przez poszczególne arkusze
For Each sht In ActiveWorkbook.Worksheets
If sht.Visible Then ' pomija ukryte arkusze
sht.Activate
Range(UserSel).Select
ActiveWindow.ScrollRow = TopRow
ActiveWindow.ScrollColumn = LeftCol
End If
Next sht
' Przywraca oryginalne ustawienie aktywnego arkusza
UserSheet.Activate
Application.ScreenUpdating = True
End Sub
Skoroszyt z tym przykadem (Synchronizacja arkuszy.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki.
Techniki programowania w jzyku VBA
Nastpne przykady ilustruj czsto uywane techniki programowania w jzyku VBA,
które moesz wykorzysta we wasnych projektach.
Przeczanie wartoci waciwoci typu logicznego
Waciwo typu logicznego posiada warto
True
lub
False
. Najprostsza metoda prze-
czania wartoci takiej waciwoci polega na zastosowaniu operatora
Not
. Zostao to
pokazane w poniszym przykadzie, w którym przeczana jest warto waciwoci
WrapText
obiektu
Selection
:
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
371
Sub ToggleWrapText()
' Wcza lub wycza zawijanie tekstu dla zaznaczonych komórek
If TypeName(Selection) = "Range" Then
Selection.WrapText = Not ActiveCell.WrapText
End If
End Sub
Oczywicie w razie potrzeby moesz dowolnie zmodyfikowa t procedur, tak aby prze-
czaa inne waciwoci typu logicznego.
Zauwa, e przeczanie jest wykonywane w oparciu o aktywn komórk. Jeeli po zazna-
czeniu zakresu waciwoci komórek maj róne wartoci (np. zawarto niektórych
komórek jest pogrubiona, a innych nie), taki zakres ma mieszane waciwoci. W tym
przypadku Excel w celu okrelenia sposobu przeczania wartoci posuguje si aktywn
komórk. Jeeli na przykad zawarto aktywnej komórki jest pogrubiona, po naciniciu
przycisku na Wstce pogrubienie zostanie usunite z wszystkich komórek zaznaczenia.
Ta prosta procedura naladuje zachowanie Excela, co zazwyczaj jest najlepszym rozwi-
zaniem.
Zauwa równie, e do sprawdzenia, czy zaznaczono zakres, procedura wykorzystuje
funkcj
TypeName
. Jeeli zaznaczony obszar nie jest zakresem, nie zostanie wykonana adna
operacja.
Operator
Not
umoliwia przeczanie wartoci wielu innych waciwoci. Aby na przy-
kad w arkuszu wywietli lub ukry nagówki wierszy i kolumn, naley uy nastpu-
jcej instrukcji:
ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings
Aby w aktywnym arkuszu wywietli lub ukry linie siatki, naley uy nastpujcej
instrukcji:
ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
Okrelanie liczby drukowanych stron
W celu okrelenia liczby drukowanych stron arkusza mona uy polecenia Podgld
wydruku Excela i w dolnej czci ekranu sprawdzi liczb stron. Procedura jzyka VBA
przedstawiona poniej zlicza poziome i pionowe linie podziau stron i na tej podstawie
okrela liczb drukowanych stron aktywnego arkusza:
Sub PageCount()
MsgBox (ActiveSheet.HPageBreaks.Count + 1) * _
(ActiveSheet.VPageBreaks.Count + 1)
End Sub
Kolejna procedura jzyka VBA przechodzi w ptli przez wszystkie arkusze aktywnego
skoroszytu i wywietla cakowit liczb drukowanych stron, tak jak to zostao zilustrowane
na rysunku 11.13.
Sub ShowPageCount()
Dim PageCount As Integer
Dim sht As Worksheet
372
Cz III
Jzyk Visual Basic for Applications
Rysunek 11.13.
Wykorzystanie
procedury VBA
do wyznaczenia liczby
stron do wydruku
w skoroszycie
PageCount = 0
For Each sht In Worksheets
PageCount = PageCount + (sht.HPageBreaks.Count + 1) * _
(sht.VPageBreaks.Count + 1)
Next sht
MsgBox "Liczba stron do wydruku = " & PageCount
End Sub
Skoroszyt z tym przykadem (Zliczanie stron wydruku.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki.
Wywietlanie daty i czasu
Jeeli znasz ju system liczb seryjnych uywany przez Excel do przechowywania dat
i godzin, nie bdziesz mia adnych problemów z zastosowaniem dat i czasu w procedu-
rach jzyka VBA.
Procedura
DateAndTime
wywietla okno komunikatu z aktualn dat i czasem, tak jak to
zostao przedstawione na rysunku 11.14. W pasku tytuowym okna wywietlany jest
komunikat odpowiedni dla danej pory dnia.
Rysunek 11.14.
Okno komunikatu
wywietlajce
dat i czas
Procedura jako argument funkcji
Format
wykorzystuje funkcj
Date
. Wynikiem dziaania
procedury jest acuch zawierajcy sformatowan dat. Aby uzyska tak samo sformato-
wany czas, uylimy podobnego sposobu.
Sub DateAndTime()
' Wywietla aktualn dat i czas
Dim TheDate As String, TheTime As String
Dim Greeting As String
Dim FullName As String, FirstName As String
Dim SpaceInName As Integer
TheDate = Format(Date, "Long date")
TheTime = Format(Time, "Long time")
' Okrelenie powitania w oparciu o czas
Select Case Time
Case Is < TimeValue("12:00"): Greeting = "Dzie dobry "
Case Is >= TimeValue("17:00"): Greeting = "Dobry wieczór "
Case Else: Greeting = "Dzie dobry "
End Select
' Dodanie do powitania imienia uytkownika
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
373
FullName = Application.UserName
SpaceInName = InStr(1, FullName, " ", 1)
' Obsuga przypadku, gdy nazwa nie zawiera spacji
If SpaceInName = 0 Then SpaceInName = Len(FullName)
FirstName = Left(FullName, SpaceInName)
Greeting = Greeting & FirstName
' Wywietlenie komunikatu
MsgBox TheDate & vbCrLf & TheTime, vbOKOnly, Greeting
End Sub
W celu zagwarantowania, e makro bdzie poprawnie dziaao niezalenie od ustawie
regionalnych systemu uytkownika, w powyszym przykadzie uylimy nazw formatów
daty i czasu (
Long Date
i
Long Time
). Oczywicie w razie potrzeby moesz posuy si
innymi formatami. Aby na przykad wywietli dat w formacie mm/dd/rr, moesz uy
nastpujcego polecenia:
TheDate = Format(Date, "mm/dd/yy")
Aby uzaleni tre komunikatu wywietlanego na pasku tytuowym okna od pory dnia,
zastosowalimy konstrukcj
Select Case
. Wartoci zwizane z czasem stosowane w jzyku
VBA funkcjonuj podobnie jak w Excelu. Jeeli warto czasu jest mniejsza od liczby
0,5
(poudnie), oznacza to, e jest przedpoudnie. Jeeli z kolei warto ta jest wiksza od
liczby
0,7083
(godzina 17), oznacza to, e jest wieczór. W innych przypadkach jest popo-
udnie. Wybralimy proste rozwizanie polegajce na zastosowaniu funkcji
TimeValue
jzyka VBA, która pobierajc acuch, zwraca warto czasu.
Kolejna grupa instrukcji identyfikuje imi uytkownika znajdujce si w zakadce Ogólne
okna dialogowego Opcje. W celu zlokalizowania pierwszej spacji zawartej w personaliach
uytkownika uyem funkcji
InStr
jzyka VBA. Po napisaniu procedury stwierdziem,
e nie uwzgldniem identyfikatora uytkownika, w którym nie wystpuje spacja. Gdy
wic uruchomiem j w systemie uywanym przez uytkownika Nobody, nie zadziaaa
prawidowo. Jest to potwierdzeniem tezy, e nie mona przewidzie wszystkiego, i nawet
najprostsze procedury mog nie zadziaa. Nawiasem mówic, jeeli nie zostan podane
personalia uytkownika, Excel zawsze uyje nazwy aktualnie zalogowanego uytkownika.
W procedurze problem ten zosta rozwizany poprzez przypisanie zmiennej
SpaceInName
dugoci penej nazwy uytkownika, tak aby funkcja
Left
zwrócia odpowiedni nazw.
Funkcja
MsgBox
czy dat i czas, a ponadto w celu wstawienia pomidzy nimi znaku
podziau stosuje wbudowan sta
vbCrLf
. Staa
vbOKOnly
jest predefiniowan sta zwra-
cajc zero i powodujc, e w oknie komunikatu zostanie wywietlony jedynie przy-
cisk OK. Ostatni argument o nazwie
Greeting
zosta wczeniej zdefiniowany w procedurze.
Skoroszyt z tym przykadem (Data i czas.xlsm) znajdziesz na pycie CD-ROM doczonej
do ksiki.
Pobieranie listy czcionek
Jeeli bdziesz chcia pobra list wszystkich zainstalowanych czcionek, przekonasz si,
e Excel nie oferuje bezporedniej metody uzyskania takiej listy. Technika opisywana
tutaj wykorzystuje fakt, e ze wzgldu na konieczno zachowania kompatybilnoci
374
Cz III
Jzyk Visual Basic for Applications
z poprzednimi wersjami, Excel 2010 nadal obsuguje stare metody i waciwoci obiektów
CommandBar
, które w wersjach wczeniejszych ni 2007 byy wykorzystywane do pracy
z paskami narzdzi i menu.
Procedura
ShowInstalledFonts
wywietla w kolumnie
A
aktywnego arkusza list zainsta-
lowanych czcionek. Procedura tworzy tymczasowy pasek narzdzi (obiekt klasy
Command
´
Bar
), dodaje do niego formant
Font
i odczytuje list czcionek z waciwoci tego for-
mantu. Po zakoczeniu tymczasowy pasek narzdzi jest usuwany.
Sub ShowInstalledFonts()
Dim FontList As CommandBarControl
Dim TempBar As CommandBar
Dim i As Long
' Tworzy tymczasowy pasek narzdzi (obiekt klasy CommandBar)
Set TempBar = Application.CommandBars.Add
Set FontList = TempBar.Controls.Add(ID:=1728)
' Umieszcza list czcionek w kolumnie A
Range("A:A").ClearContents
For i = 0 To FontList.ListCount - 1
Cells(i + 1, 1) = FontList.List(i + 1)
Next i
' Usuwa tymczasowy pasek narzdzi (obiekt klasy CommandBar)
TempBar.Delete
End Sub
Opcjonalnie mona te wywietli nazw czcionki przy uyciu tej czcionki, tak jak to zostao
zaprezentowane na rysunku 11.15. Aby to zrobi, wewntrz ptli
For ... Next naley
umieci nastpujc instrukcj:
Cells(i+1, 1).Font.Name = FontList.List(i + 1)
Trzeba jednak mie wiadomo, e zastosowanie w skoroszycie wielu czcionek
spowoduje zuycie znacznej iloci zasobów systemowych, a nawet moe doprowadzi
do zawieszenia komputera.
Skoroszyt z tym przykadem (Lista czcionek.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki.
Sortowanie tablicy
Co prawda Excel posiada wbudowane polecenie sortujce zakresy arkusza, ale jzyk VBA
nie dysponuje metod sortowania tablic. Skuteczne, ale niewygodne rozwizanie tego
problemu polega na przeniesieniu zawartoci tablicy do zakresu arkusza, posortowaniu
jej przy uyciu polecenia Excela, a nastpnie wczytaniu wyniku do tablicy. Jeeli jednak
szybko odgrywa du rol, lepiej stworzy w jzyku VBA procedur sortujc.
W tym punkcie omówimy cztery róne metody sortowania:
Sortowanie arkuszowe polega na przeniesieniu zawartoci tablicy do zakresu arkusza,
posortowaniu jej, a nastpnie ponownym umieszczeniu w tablicy. Jedynym
argumentem procedury opartej na tej metodzie jest tablica.
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
375
Rysunek 11.15.
Wywietlanie listy
czcionek przy uyciu
tych samych czcionek
Sortowanie bbelkowe jest prost metod sortowania (zastosowano j te
w przykadzie demonstrujcym sortowanie arkusza w rozdziale 9.). Co prawda
metoda sortowania bbelkowego jest atwa w kodowaniu, ale ma raczej powolny
algorytm, zwaszcza gdy przetwarzaniu podlega dua liczba elementów.
Sortowanie metod quick-sort (sortowanie szybkie) w porównaniu z bbelkowym
jest o wiele szybsz metod sortowania, ale te znacznie trudniejsz do zrozumienia.
Metoda moe zosta wykorzystana tylko w przypadku takich typów danych,
jak
Integer
lub
Long
.
Sortowanie zliczajce jest bardzo szybkie, ale równie trudne do zrozumienia.
Technika ta, podobnie jak sortowanie szybkie, dziaa tylko w przypadku takich typów
danych, jak
Integer
lub
Long
.
Na doczonym dysku CD-ROM znajduje si skoroszyt o nazwie Sortowanie.xlsm, który
porównuje wyej wymienione metody sortowania. Skoroszyt przydaje si w przypadku
porównywania metod sortowania tablic o rónych rozmiarach. Oczywicie w razie potrzeby
moesz skopiowa z niego odpowiednie procedury i uy ich w swoich programach.
Na rysunku 11.16 pokazano okno dialogowe naszego programu. Procedury sortujce zostay
przetestowane przy uyciu tablic o siedmiu rónych rozmiarach liczcych od 100 do
100 000 elementów. W tablicach zawarte byy wartoci losowe typu
Long
.
376
Cz III
Jzyk Visual Basic for Applications
Rysunek 11.16.
Porównanie czasu
potrzebnego
do wykonania operacji
sortowania tablic
o rónych rozmiarach
W tabeli 11.1 zawarem wyniki testów. Warto
0,00
oznacza, e sortowanie zostao
zakoczone prawie natychmiast w czasie krótszym ni 0,01 sekundy.
Tabela 11.1. Czas trwania (wyraony w sekundach) operacji sortowania tablic wypenionych losowymi
wartociami przy uyciu czterech algorytmów sortujcych
Liczba
elementów tablicy
Sortowanie
arkuszowe Excela
Sortowanie
bbelkowe
przy uyciu
jzyka VBA
Sortowanie
szybkie
przy uyciu
jzyka VBA
Sortowanie
zliczajce
przy uyciu
jzyka VBA
100
0,04
0,00
0,00
0,02
500
0,02
0,01
0,00
0,01
1000
0,03
0,03
0,00
0,00
5000
0,07
0,84
0,01
0,01
10 000
0,09
3,41
0,01
0,01
50 000
0,43
79,95
0,07
0,02
100 000
0,78
301,90
0,14
0,04
Algorytm sortowania arkuszowego jest wyjtkowo szybki, zwaszcza e operacja uwzgld-
nia przeniesienie zawartoci tablicy do arkusza, sortowanie jej i ponowne wczytanie danych
do tablicy. Jeeli tablica jest ju prawie posortowana, sortowanie arkuszowe bdzie jeszcze
szybsze.
Algorytm sortowania bbelkowego jest do szybki w przypadku niewielkich tablic, ale
przy wikszych (liczcych ponad 5000 elementów) powiniene po prostu o nim zapo-
mnie. Sortowanie szybkie oraz sortowanie zliczajce s bardzo szybkie, ale ich funkcjo-
nalno jest ograniczona jedynie do danych typu
Interger
oraz
Long
.
Przetwarzanie grupy plików
Jednym z czstych zastosowa makr jest oczywicie kilkakrotne powtarzanie okrelonej
operacji. Przykad przedstawiony poniej ilustruje, jak przy uyciu makra przetworzy
kilka rónych plików zapisanych na dysku. Procedura, która moe pomóc w napisaniu
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
377
wasnego makra realizujcego tego typu zadanie, prosi uytkownika o podanie wzorca
nazw plików, a nastpnie przetwarza wszystkie pliki, których nazwy s z nim zgodne.
W tym przypadku operacja przetwarzania polega na zaimportowaniu pliku i wprowadzeniu
grupy formu sumujcych, które opisuj zawarte w nim dane.
Sub BatchProcess()
Dim FileSpec As String
Dim i As Integer
Dim FileName As String
Dim FileList() As String
Dim FoundFiles As Integer
' Okrelenie cieki i wzorca nazwy
FileSpec = ThisWorkbook.Path & "\" & "text??.txt"
FileName = Dir(FileSpec)
' Czy plik zosta znaleziony?
If FileName <> "" Then
FoundFiles = 1
ReDim Preserve FileList(1 To FoundFiles)
FileList(FoundFiles) = FileName
Else
MsgBox "Nie znaleziono adnych plików pasujcych do wzorca " & FileSpec
Exit Sub
End If
' Pobierz nazwy pozostaych plików
Do
FileName = Dir
If FileName = "" Then Exit Do
FoundFiles = FoundFiles + 1
ReDim Preserve FileList(1 To FoundFiles)
FileList(FoundFiles) = FileName & "*"
Loop
' Przetwarzanie kolejnych plików w ptli
For i = 1 To FoundFiles
Call ProcessFiles(FileList(i))
Next i
End Sub
Skoroszyt z tym przykadem (Przetwarzanie wsadowe.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki. Przykad korzysta z trzech dodatkowych plików, równie
znajdujcych si na dysku CD-ROM. S to: text01.txt, text02.txt i text03.txt. Aby
zaimportowa inne pliki tekstowe, bdziesz musia odpowiednio zmodyfikowa kod
procedury.
Nazwy plików pasujce do wzorca s przechowywane w tablicy o nazwie
FoundFiles
.
Pliki s przetwarzane przy uyciu ptli
For ... Next
. W trakcie przetwarzania wewntrz
ptli jest wywoywana prosta procedura
ProcessFiles
. W celu zaimportowania pliku
korzysta ona z metody
OpenText
, a nastpnie wstawia pi formu. Oczywicie zamiast
poniszej mona zastosowa wasn procedur.
Sub ProcessFiles(FileName As String)
' Importowanie pliku
Workbooks.OpenText FileName:=FileName, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
378
Cz III
Jzyk Visual Basic for Applications
FieldInfo:= _
Array(Array(0, 1), Array(3, 1), Array(12, 1))
' Wprowadzanie formu podsumowujcych
Range("D1").Value = "A"
Range("D2").Value = "B"
Range("D3").Value = "C"
Range("E1:E3").Formula = "=COUNTIF(B:B,D1)"
Range("F1:F3").Formula = "=SUMIF(B:B,D1,C:C)"
End Sub
Wicej szczegóowych informacji na temat pracy z plikami z poziomu jzyka VBA
znajdziesz w rozdziale 27.
Ciekawe funkcje,
których moesz uy w swoich projektach
W tym podrozdziale zaprezentujemy kilka niestandardowych funkcji, które mona albo
bezporednio stosowa w aplikacjach uytkowych, albo modyfikowa, traktujc je jako
twórcz inspiracj. Najprzydatniejsze s wtedy, gdy wywouje si je z innej procedury
jzyka VBA. Procedury zostay zadeklarowane przy uyciu sowa kluczowego
Private
,
dziki czemu nie bd widoczne w oknie dialogowym Wstawianie funkcji Excela.
Skoroszyt z tym przykadem (Funkcje uytkowe VBA.xlsm) znajdziesz na pycie CD-
ROM doczonej do ksiki.
Funkcja FileExists
Funkcja pobiera jeden argument (cieka pliku wraz z jego nazw) i zwraca warto
True
,
jeeli plik istnieje:
Private Function FileExists(fname) As Boolean
' Zwraca warto True, jeeli istnieje plik
FileExists = (Dir(fname) <> "")
End Function
Funkcja FileNameOnly
Funkcja pobiera jeden argument (cieka pliku wraz z jego nazw) i zwraca tylko nazw
pliku (innymi sowy — usuwa ciek pliku):
Private Function FileNameOnly(pname) As String
' Zwraca nazw pliku pobran z acucha zoonego ze cieki i nazwy pliku
Dim temp As Variant
length = Len(pname)
temp = Split(pname, Application.PathSeparator)
FileNameOnly = temp(UBound(temp))
End Function
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
379
Funkcja
FileNameOnly
wykorzystuje funkcj
Split
VBA, która pobiera acuch tekstu
(oraz separator) i zwraca tabel typu
Variant
, zawierajc elementy acucha znajdujce
si pomidzy znakami separatora. W tym przypadku zmienna
temp
zawiera tablic z acu-
chami tekstu znajdujcymi si pomidzy separatorami definiowanymi przez
Application.
´
PathSeparator
(zaznaczaj s to znaki lewego ukonika). Inny przykad zastosowania
funkcji
Split
znajdziesz w podrozdziale „Wyznaczanie n-tego elementu acucha”
w dalszej czci tego rozdziau.
Jeeli argumentem wywoania funkcji jest cieka c:\excel files\2010\backup\budget.xlsm,
funkcja zwróci acuch budget.xlsm.
Funkcja
FileNameOnly
przetwarza dowoln ciek i nazw pliku (nawet jeeli plik nie
istnieje). Jeeli plik istnieje, ponisza funkcja oferuje prostsz metod usuwania cieki
i zwracania tylko nazwy pliku:
Private Function FileNameOnly2(pname) As String
FileNameOnly2 = Dir(pname)
End Function
Funkcja PathExists
Funkcja pobiera jeden argument (cieka pliku) i zwraca warto
True
, jeeli cieka
istnieje:
Private Function PathExists(pname) As Boolean
' Zwraca warto True, jeeli istnieje cieka
If Dir(pname, vbDirectory) = "" Then
PathExists = False
Else
PathExists = (GetAttr(pname) And vbDirectory) = vbDirectory
End If
End Function
Funkcja RangeNameExists
Funkcja pobiera jeden argument (nazwa zakresu) i zwraca warto
True
, jeeli w aktyw-
nym skoroszycie istnieje nazwa zakresu:
Private Function RangeNameExists(nname) As Boolean
' Zwraca warto True, jeeli istnieje nazwa zakresu
Dim n As Name
RangeNameExists = False
For Each n In ActiveWorkbook.Names
If UCase(n.Name) = UCase(nname) Then
RangeNameExists = True
Exit Function
End If
Next n
End Function
Inny sposób napisania takiej funkcji przedstawiono poniej. Funkcja w tej wersji pró-
buje utworzy zmienn obiektow przy uyciu nazwy zakresu. Jeeli taka próba zako-
czy si wygenerowaniem bdu, oznacza to, e dana nazwa zakresu nie istnieje.
380
Cz III
Jzyk Visual Basic for Applications
Private Function RangeNameExists2(nname) As Boolean
' Zwraca warto True, jeeli istnieje nazwa zakresu
Dim n As Range
On Error Resume Next
Set n = Range(nname)
If Err.Number = 0 Then RangeNameExists2 = True _
Else RangeNameExists2 = False
End Function
Funkcja SheetExists
Funkcja pobiera jeden argument (nazwa arkusza) i zwraca warto
True
, jeeli w aktyw-
nym skoroszycie istnieje arkusz o takiej nazwie:
Private Function SheetExists(sname) As Boolean
' Zwraca warto True, jeeli w aktywnym skoroszycie istnieje arkusz o takiej nazwie
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function
Sprawdzanie, czy obiekt naley do kolekcji
Ponisza procedura
Function jest prost funkcj sprawdzajc, czy dany obiekt naley do kolekcji:
Private Function IsInCollection(Coln As Object, _
Item As String) As Boolean
Dim Obj As Object
On Error Resume Next
Set Obj = Coln(Item)
IsInCollection = Not Obj Is Nothing
End Function
Funkcja pobiera dwa argumenty — kolekcj (obiekt) i element (acuch), który moe, ale nie
musi nalee do kolekcji. Funkcja próbuje utworzy zmienn obiektow reprezentujc element
kolekcji. Jeeli próba si powiedzie, funkcja zwraca warto
True. W przeciwnym razie funkcja zwraca
warto
False.
Funkcji
IsInCollection mona uy zamiast trzech innych funkcji wymienionych w rozdziale
(
RangeNameExists, SheetExists i WorkbookIsOpen). Aby na przykad stwierdzi, czy w aktywnym
skoroszycie istnieje zakres o nazwie
Data, naley wywoa funkcj IsInCollection przy uyciu
poniszej instrukcji:
MsgBox IsInCollection(ActiveWorkbook.Names, "Data")
Aby stwierdzi, czy otwarto skoroszyt o nazwie budet.xlsx, naley uy nastpujcej instrukcji:
MsgBox IsInCollection(Workbooks, "budet.xlsx")
Aby stwierdzi, czy aktywny skoroszyt zawiera arkusz o nazwie
Arkusz1, naley uy nastpujcej
instrukcji:
MsgBox IsInCollection(ActiveWorkbook.Worksheets, "Arkusz1")
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
381
Funkcja WorkbookIsOpen
Funkcja pobiera jeden argument (nazwa skoroszytu) i zwraca warto
True
, jeeli skoro-
szyt jest otwarty:
Private Function WorkbookIsOpen(wbname) As Boolean
' Zwraca warto True, jeeli skoroszyt jest otwarty
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function
Pobieranie wartoci z zamknitego skoroszytu
Jzyk VBA nie posiada metody umoliwiajcej pobranie wartoci z zamknitego skoro-
szytu. W razie potrzeby moemy jednak skorzysta z faktu, e Excel obsuguje cza do
plików. Zamieszczona poniej funkcja
GetValue
jzyka VBA pobiera warto z zamkni-
tego skoroszytu. Zadanie to jest realizowane poprzez wywoanie starszego typu makra XLM,
które byo stosowane w wersjach Excela sprzed wersji 5. Na szczcie, jak wida, Excel
nadal obsuguje makra tego starego typu.
Private Function GetValue(path, file, sheet, ref)
' Pobiera warto z zamknitego skoroszytu
Dim arg As String
' Sprawdza, czy istnieje plik
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "Plik nie zosta znaleziony."
Exit Function
End If
' Tworzenie argumentu
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Wykonanie makra XLM
GetValue = ExecuteExcel4Macro(arg)
End Function
Funkcja
GetValue
pobiera cztery argumenty:
path
— cieka zamknitego pliku (np.
"d:\pliki"
);
file
— nazwa pliku skoroszytu (np.
"budet.xlsx"
);
sheet
— nazwa arkusza (np.
"Arkusz1"
);
ref
— odwoanie do komórki (np.
"C4"
).
Ponisza procedura
Sub
demonstruje, w jaki sposób uy funkcji
GetValue
. Procedura
wywietla po prostu warto w komórce
A1
arkusza
Arkusz1
pliku o nazwie
2010Budet.xlsx
znajdujcego si w katalogu
XLPliki\Budet
na dysku
C
.
Sub TestGetValue()
Dim p As String, f As String
Dim s As String, a As String
382
Cz III
Jzyk Visual Basic for Applications
p = "c:\XLPliki\Budet"
f = "2010Budet.xlsx"
s = "Arkusz1"
a = "A1"
MsgBox GetValue(p, f, s, a)
End Sub
Kolejna procedura odczytuje z zamknitego pliku 1200 wartoci (zajmujcych obszar
100
wierszy
u
12
kolumn), a nastpnie umieszcza je w aktywnym arkuszu:
Sub TestGetValue2()
Dim p As String, f As String
Dim s As String, a As String
Dim r As Long, c As Long
p = "c:\XLPliki\Budet"
f = "2010Budet.xlsx"
s = "Arkusz1"
Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 12
a = Cells(r, c).Address
Cells(r, c) = GetValue(p, f, s, a)
Next c
Next r
End Sub
Funkcja
GetValue nie zadziaa po zastosowaniu jej w formule arkuszowej. W praktyce
nie ma jednak adnej potrzeby umieszczania tej funkcji w jakiejkolwiek formule. W celu
pobrania wartoci z zamknitego pliku wystarczy stworzy cze do komórki znajdujcej
si w takim pliku.
Skoroszyt z tym przykadem (Pobieranie wartoci z zamknitego pliku.xlsm) znajdziesz
na pycie CD-ROM doczonej do ksiki. Procedury zawarte w tym skoroszycie pobieraj
dane ze skoroszytu o nazwie Mój skoroszyt.xlsx.
Uyteczne, niestandardowe funkcje arkuszowe
W tym podrozdziale zamiecilimy przykady niestandardowych funkcji, które mona
zastosowa w formuach arkusza. Pamitaj, e takie funkcje (procedury typu
Function
),
musz zosta zdefiniowane w module VBA, a nie w moduach powizanych z obiektami
takimi jak ThisWorkbook, Arkusz1 lub UserForm1.
Skoroszyt z przykadami omawianymi w tym podrozdziale (Funkcje arkuszowe.xlsm)
znajdziesz na pycie CD-ROM doczonej do ksiki.
Funkcje zwracajce informacje o formatowaniu komórki
W tym podrozdziale znajdziesz szereg niestandardowych funkcji zwracajcych róno-
rodne informacje o sposobie formatowania komórki. Funkcje takie s przydatne na przy-
kad w sytuacji, kiedy trzeba posortowa dane w oparciu o formatowanie (np. gdy szukasz
wszystkich komórek, wobec których uyto pogrubienia).
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
383
Takie funkcje nie zawsze s automatycznie przeliczane, poniewa zmiana formatowania
nie uaktywnia mechanizmu Excela wykonujcego ponowne obliczenia. Aby wymusi
ponowne obliczenie wszystkich skoroszytów wraz z aktualizacj wartoci funkcji
niestandardowych, naley nacisn kombinacj klawiszy Ctrl+Alt+F9.
Innym rozwizaniem jest umieszczenie w kodzie ródowym funkcji nastpujcego
polecenia:
Application.Volatile
Po zastosowaniu tego polecenia nacinicie klawisza F9 spowoduje ponowne przeliczenie
arkusza wraz z funkcjami niestandardowymi.
Ponisza funkcja zwraca warto
True
, jeeli w jednokomórkowym zakresie bdcym
jej argumentem zastosowano pogrubienie. Jeeli jako argument wywoania funkcji zosta-
nie przekazany zakres, funkcja uyje lewej, górnej komórki tego zakresu:
Function IsBold(cell) As Boolean
' Zwraca warto True, jeeli zawarto komórki zostaa pogrubiona
IsBold = cell.Range("A1").Font.Bold
End Function
Pamitaj, e takie funkcje dziaaj poprawnie tylko z komórkami, którym formatowanie
zostao nadane bezporednio za pomoc polece i przycisków formatujcych i nie bd
dziaay w przypadku formatowania nadanego za pomoc mechanizmu formatowania
warunkowego. W Excelu 2010 wprowadzony zosta nowy obiekt,
DisplayFormat
, który
potrafi obsugiwa elementy formatowania warunkowego. Poniej zamieszczamy now
wersj funkcji
IsBold
— zwraca ona poprawne wyniki równie dla komórek, których
zawarto zostaa pogrubiona za porednictwem formatowania warunkowego:
Function IsBold(cell) As Boolean
' Zwraca warto True, jeeli zawarto komórki zostaa pogrubiona (z uwzgldnieniem formatowania
warunkowego)
IsBold = cell.Range("A1").DisplayFormat.Font.Bold
End Function
Ponisza funkcja zwraca warto
True
, jeeli w komórce bdcej jej argumentem zasto-
sowano kursyw:
Function IsItalic(cell) As Boolean
' Zwraca warto True, jeeli w komórce uyto kursywy
IsItalic = cell.Range("A1").Font.Italic
End Function
Jeeli w komórce zostanie zastosowane mieszane formatowanie (np. tylko wybrane znaki
zostan pogrubione), obie powysze funkcje zwróc bd. Kolejna funkcja zwróci warto
True
tylko wtedy, gdy wszystkie znaki z komórki bd pogrubione:
Function AllBold(cell) As Boolean
' Zwraca warto True, jeeli wszystkie znaki z komórki s pogrubione
If IsNull(cell.Font.Bold) Then
AllBold = False
Else
AllBold = cell.Font.Bold
End If
End Function
384
Cz III
Jzyk Visual Basic for Applications
Funkcja
AllBold
moe zosta uproszczona do nastpujcej postaci:
Function AllBold (cell) As Boolean
' Zwraca warto True, jeeli wszystkie znaki z komórki s pogrubione
AllBold = Not IsNull(cell.Font.Bold)
End Function
Funkcja
FillColor
zwraca liczb cakowit odpowiadajc indeksowi koloru ta komórki
(czyli inaczej mówic, indeksowi koloru jej wypenienia). Kolor ta komórki zwykle
zaley od wybranego stylu formatowania arkusza. Jeeli to komórki nie zostanie wype-
nione, funkcja zwraca warto
–4142
.
Ta funkcja nie dziaa poprawnie kolorami ta tabel (definiowanych za pomoc polecenia
Tabela, znajdujcego si na karcie Wstawianie, w grupie polece Tabele). Aby uwzgld-
ni kolory ta tabel, powiniene uy obiektu
DisplayFormat
, o którym wspominalimy
wczeniej.
Function FillColor(cell) As Integer
' Zwraca liczb cakowit odpowiadajc kolorowi ta komórki
FillColor = cell.Range("A1").Interior.ColorIndex
End Function
Gadajcy arkusz?
Funkcja
SayIt
wykorzystuje wbudowany w Excela generator mowy to odczytywania „na
gos” przekazanego jej argumentu (którym moe by acuch tekstu lub odwoanie do
wybranej komórki).
Function SayIt(txt)
Application.Speech.Speak (txt)
SayIt = txt
End Function
Opisywana funkcja daje cakiem interesujce efekty i moe by naprawd uyteczna.
Spróbuj uy tej funkcji na przykad w nastpujcej formule:
=IF(SUM(A:A)>25000,SayIt("Cel zosta osignity!"))
Jeeli suma wartoci w kolumnie
A
przekroczy warto
25000
, usyszysz zsyntetyzowany
gos radonie oznajmiajcy, e cel zosta osignity. Metody
Speak
moesz równie
uy do powiadomienia o zakoczeniu dugo dziaajcej procedury. W ten sposób po uru-
chomieniu procedury bdziesz móg zaj si czym innym, a Excel sam powiadomi Ci,
kiedy procedura wreszcie zakoczy dziaanie.
Wywietlanie daty zapisania lub wydrukowania pliku
Skoroszyt Excela posiada szereg wbudowanych waciwoci dokumentu, które s dostpne
dla programów VBA za porednictwem waciwoci
BuiltinDocumentProperties
obiektu
Workbook
. Ponisza funkcja zwraca dat i czas wykonania ostatniej operacji zapisu
skoroszytu:
Function LastSaved()
Application.Volatile
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
385
LastSaved = ThisWorkbook. _
BuiltinDocumentProperties("Last Save Time")
End Function
Data i czas ostatniego zapisu, zwracane przez t funkcj, to dokadnie ta sama informacja,
jak znajdziesz w sekcji Powizane daty widoku Backstage, dostpnego po przejciu na
kart Plik i wybraniu polecenia Informacje. Pamitaj, e mechanizm automatycznego zapisu
równie modyfikuje te dane, std znacznik czasu ostatniego zapisu dokumentu niekoniecz-
nie musi by tosamy z tym, kiedy dany dokument zosta zapisany przez uytkownika.
Ponisza funkcja jest podobna do poprzedniej, z tym e zwraca dat i czas wykonania
ostatniej operacji drukowania lub podgldu wydruku dla skoroszytu. Jeeli skoroszyt nie
by jeszcze nigdy drukowany ani uytkownik nigdy nie korzysta z opcji podgldu wydruku,
funkcja zwraca bd
#ARG!
:
Function LastPrinted()
Application.Volatile
LastPrinted = ThisWorkbook. _
BuiltinDocumentProperties("Last Print Date")
End Function
Jeeli uyjesz tych funkcji w formule, to w celu uzyskania aktualnych wartoci waciwoci
skoroszytu moe by konieczne wymuszenie wykonania ponownych oblicze (poprzez
wcinicie klawisza F9).
Istnieje cakiem sporo dodatkowych wbudowanych waciwoci, ale Excel nie korzysta
ze wszystkich. Na przykad próba uycia waciwoci
Number of Bytes spowoduje
wygenerowanie bdu. Pen list wbudowanych waciwoci skoroszytów znajdziesz
w pomocy systemowej programu Excel.
Funkcje
LastSaved
i
LastPrinted
zostay zaprojektowane z myl o przechowywaniu ich
w skoroszycie, w którym s uywane. W niektórych przypadkach funkcja moe zosta
umieszczona w innym skoroszycie (np. personal.xlsb) lub dodatku. Poniewa obie powy-
sze funkcje odwouj si do waciwoci
ThisWorkbook
, po zapisaniu ich w innym sko-
roszycie nie bd dziaay poprawnie. Poniej zawarto wersje tych funkcji o bardziej
uniwersalnym przeznaczeniu. Funkcje uywaj waciwoci
Application.Caller
, która
zwraca obiekt klasy
Range
reprezentujcy komórk wywoujc funkcj. Waciwo
Parent.Parent
zwraca skoroszyt (obiekt
Workbook
), czyli obiekt nadrzdny przodka obiektu
Range
. Zagadnienie to zostanie omówione dokadniej w nastpnym podrozdziale.
Function LastSaved2()
Application.Volatile
LastSaved2 = Application.Caller.Parent.Parent. _
BuiltinDocumentProperties("Last Save Time")
End Function
Obiekty nadrzdne
Jak pamitasz, model obiektowy Excela ma posta hierarchiczn — poszczególne obiekty
s zawarte w innych obiektach. Na szczycie hierarchii znajduje si obiekt klasy
Appli
´
cation
. Excel zawiera inne obiekty, które s kontenerami dla kolejnych obiektów, itd.
Ponisza hierarchia ilustruje miejsce, jakie w tym schemacie zajmuje obiekt
Range
:
386
Cz III
Jzyk Visual Basic for Applications
obiekt
Application
obiekt
Workbook
obiekt
Worksheet
obiekt
Range
W jzyku programowania obiektowego mówimy, e przodkiem (obiektem nadrzdnym)
obiektu
Range
jest obiekt
Worksheet
, bdcy jego kontenerem. Przodkiem obiektu
Work
´
sheet
jest obiekt
Workbook
przechowujcy arkusz. Z kolei obiektem nadrzdnym obiektu
Workbook
jest obiekt
Application
.
W jaki sposób wykorzysta t informacj w praktyce? Przeanalizujmy ponisz funkcj
SheetName
jzyka VBA. Funkcja pobiera jeden argument (zakres) i zwraca nazw arkusza
zawierajcego zakres. Funkcja uywa waciwoci
Parent
obiektu
Range
. Waciwo
Parent
zwraca obiekt przechowujcy obiekt
Range
.
Function SheetName(ref) As String
SheetName = ref.Parent.Name
End Function
Kolejna funkcja
WorkbookName
zwraca nazw skoroszytu zawierajcego okrelon komórk.
Zauwa, e funkcja dwukrotnie uywa waciwoci
Parent
. Pierwsza waciwo
Parent
zwraca obiekt
Worksheet
, a druga — obiekt
Workbook
.
Function WorkbookName(ref) As String
WorkbookName = ref.Parent.Parent.Name
End Function
Ponisza funkcja
AppName
przenosi nas na kolejny poziom w hierarchii, trzykrotnie korzy-
stajc z waciwoci
Parent
. Funkcja zwraca nazw obiektu
Application
powizanego
z okrelon komórk. Oczywicie w naszym przypadku funkcja zawsze bdzie zwracaa
warto
Microsoft Excel
.
Function AppName(ref) As String
AppName = ref.Parent.Parent.Parent.Name
End Function
Zliczanie komórek, których wartoci zawieraj si
pomidzy dwoma wartociami
Ponisza funkcja o nazwie
CountBetween
zwraca liczb wartoci w zakresie (pierwszy
argument), mieszczcych si pomidzy dwoma wartociami reprezentowanymi przez
drugi i trzeci argument:
Function CountBetween(InRange, num1, num2) As Long
' Zlicza wartoci z przedziau od num1 do num2
With Application.WorksheetFunction
If num1 <= num2 Then
CountBetween = .CountIfs(InRange, ">=" & num1, _
InRange, "<=" & num2)
Else
CountBetween = .CountIfs(InRange, ">=" & num2, _
InRange, "<=" & num1)
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
387
End If
End With
End Function
Funkcja korzysta z funkcji arkuszowej
COUNTIFS
(
LICZ.WARUNKI
) Excela i w praktyce spe-
nia rol funkcji osonowej upraszczajcej tworzone formuy.
Funkcja
LICZ.WARUNKI zostaa wprowadzona w Excelu 2007, std taka funkcja nie bdzie
dziaaa z wczeniejszymi wersjami Excela.
Poniej zamieszczono przykad formuy korzystajcej z funkcji
CountBetween
, zwracaj-
cej liczb komórek zakresu
A1:A100
, których wartoci s wiksze lub równe
10
i mniejsze
lub równe
20
:
=CountBetween(A1:A100, 10, 20)
Funkcja pobiera dwa argumenty numeryczne w dowolnej kolejnoci, std formua przed-
stawiona poniej dziaa dokadnie tak samo, jak jej poprzedniczka:
=CountBetween(A1:A100, 20, 10)
Zastosowanie tej funkcji jzyka VBA jest zdecydowanie prostsze ni wprowadzenie nast-
pujcej dugiej (i jak wida — dosy zoonej) formuy:
=LICZ.WARUNKI(A1:A100,">=10",A1:A100"<=20")
Wyznaczanie ostatniej niepustej komórki kolumny lub wiersza
W tym podrozdziale zaprezentowano dwie bardzo przydatne funkcje. Pierwsza z nich,
o nazwie
LastInColumn
, zwraca zawarto ostatniej niepustej komórki danej kolumny.
Druga funkcja,
LastInRow
, zwraca zawarto ostatniej niepustej komórki danego wiersza.
Kada z funkcji pobiera pojedynczy argument, którym jest zakres. Zakresem moe by
caa kolumna (funkcja
LastInColumn
) lub cay wiersz (funkcja
LastInRow
). Jeeli przeka-
zany argument nie jest ca kolumn lub wierszem, funkcja uyje wiersza lub kolumny
okrelonej przez górn lew komórk zakresu. Ponisza przykadowa formua zwraca
warto ostatniej, niepustej komórki kolumny
B
:
=LastInColumn(B5)
Kolejna formua zwraca warto ostatniej, niepustej komórki z wiersza
7
:
=LastInRow(C7:D9)
Oto kod ródowy funkcji
LastInColumn
:
Function LastInColumn(rng As Range)
' Zwraca zawarto ostatniej niepustej komórki kolumny
Dim LastCell As Range
Application.Volatile
With rng.Parent
With .Cells(.Rows.Count, rng.Column)
If Not IsEmpty(.Value) Then
LastInColumn = .Value
ElseIf IsEmpty(.End(xlUp)) Then
388
Cz III
Jzyk Visual Basic for Applications
LastInColumn = ""
Else
LastInColumn = .End(xlUp).Value
End If
End With
End With
End Function
Funkcja jest dosy zoona, dlatego poniej przedstawiamy kilka punktów, które mog
pomóc Ci zrozumie jej sposób dziaania:
Metoda
Application.Volatile
powoduje, e funkcja zostanie wykonana
kadorazowo przy obliczaniu arkusza.
Waciwo
Rows.Count
zwraca liczb wierszy arkusza. Zamiast na sztywno
wprowadza w kodzie ródowym liczb wierszy arkusza, uyem waciwoci
Count
, poniewa kolejna wersja Excela moe obsugiwa jeszcze wiksz ni
dotychczas liczb wierszy.
Waciwo
rng.Column
zwraca numer kolumny górnej lewej komórki zakresu
bdcego wartoci argumentu
rng
.
Zastosowanie waciwoci
rng.Parent
powoduje, e funkcja bdzie dziaa
poprawnie nawet wtedy, gdy argument
rng
odwouje si do innego arkusza lub
skoroszytu.
Uycie metody
End
z argumentem
xlUp
jest równoznaczne z uaktywnieniem ostatniej
komórki kolumny, wciniciem klawisza End, a nastpnie klawisza
n.
Funkcja
IsEmpty
sprawdza, czy komórka jest pusta. Jeeli tak jest, zwraca pusty
acuch. Gdyby funkcja
IsEmpty
nie zostaa zastosowana, po napotkaniu pustej
komórki nasza funkcja zwróciaby warto
0
.
Poniej przedstawiamy kod ródowy funkcji
LastInRow
, która jest bardzo podobna do
funkcji
LastInColumn
:
Function LastInRow(rng As Range)
' Zwraca zawarto ostatniej niepustej komórki wiersza
Application.Volatile
With rng.Parent
With .Cells(rng.Row, .Columns.Count)
If Not IsEmpty(.Value) Then
LastInRow = .Value
ElseIf IsEmpty(.End(xlToLeft)) Then
LastInRow = ""
Else
LastInRow = .End(xlToLeft).Value
End If
End With
End With
End Function
Czy dany acuch tekstu jest zgodny z wzorcem?
Funkcja
IsLike
jest bardzo prosta i jednoczenie bardzo uyteczna. Zwraca warto
True
,
jeeli acuch tekstowy jest zgodny ze zdefiniowanym wzorcem.
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
389
Jak wida poniej, kod funkcji jest bardzo prosty. Funkcja waciwie odgrywa rol funkcji
osonowej, pozwalajcej na wygodne uycie w tworzonych formuach wszechstronnego
operatora
Like
jzyka VBA:
Function IsLike(text As String, pattern As String) As Boolean
' Zwraca warto True, jeeli pierwszy argument jest podobny do drugiego
IsLike = text Like pattern
End Function
Funkcja
IsLike
pobiera dwa argumenty:
text
— acuch tekstowy lub odwoanie do komórki, która go zawiera;
pattern
— acuch zawierajcy znaki wieloznaczne, które wymieniono w poniszej
tabeli.
Znaki zawarte we wzorcu
Zawarto acucha text zgodna ze wzorcem
?
Dowolny pojedynczy znak
*
0 lub wicej dowolnych znaków
#
Dowolna pojedyncza cyfra (0 – 9)
[lista_znaków]
Dowolny pojedynczy znak znajdujcy si na
licie_znaków
[!lista_znaków]
Dowolny pojedynczy znak, który nie znajduje si
na
licie_znaków
Ponisza formua zwraca warto
TRUE
, poniewa wzorzec
*
jest zgodny z dowoln
liczb znaków. Formua zwraca warto
TRUE
, jeeli pierwszy argument jest dowolnym
acuchem tekstowym rozpoczynajcym si od litery
g
:
=IsLike("gitara", "g*")
Kolejna formua zwraca warto
TRUE
, poniewa wzorzec
?
jest zgodny z dowolnym
pojedynczym znakiem. Formua zwróci warto
FALSE
, jeeli wartoci pierwszego argu-
mentu bdzie acuch
Jednostka12
:
= IsLike("Jednostka1", "Jednostka?")
Nastpna formua zwraca warto
TRUE
, poniewa jej pierwszy argument jest pojedynczym
znakiem zawartym w drugim argumencie:
= IsLike("a", "[aeiou]")
Ponisza formua zwraca warto
TRUE
, jeeli komórka
A1
zawiera liter a, e, i, o, u, A,
E, I, O lub U. Przetworzenie argumentów przy uyciu funkcji
UPPER
spowoduje, e formua
nie bdzie rozróniaa wielkoci znaków:
= IsLike(UPPER(A1), UPPER("[aeiou]"))
Ponisza formua zwraca warto
TRUE
, jeeli komórka
A1
zawiera warto rozpoczyna-
jc si cyfr
1
i skadajc si dokadnie z trzech cyfr (czyli dowoln liczb cakowit
z przedziau od 100 do 199):
= IsLike(A1, "1##")
390
Cz III
Jzyk Visual Basic for Applications
Wyznaczanie n-tego elementu acucha
Funkcja
ExtractElement
jest niestandardow funkcj arkusza (moe by te wywoy-
wana z procedury jzyka VBA) wyznaczajc n-ty element acucha tekstowego. Jeeli
na przykad komórka zawiera poniszy tekst, w celu wydzielenia dowolnego podacucha
zawartego pomidzy cznikami mona uy funkcji
ExtractElement
:
123-456-789-0133-8844
Kolejna formua zwraca podacuch
0133
bdcy czwartym elementem acucha (w roli
separatora w acuchu jest uywany cznik):
=ExtractElement("123-456-789-0133-8844", 4, "-")
Funkcja
ExtractElement
pobiera trzy argumenty:
Txt
— acuch tekstowy, z którego s wydzielane podacuchy (moe to by litera
lub odwoanie do komórki);
n
— liczba cakowita reprezentujca wydzielany element;
Separator
— pojedynczy znak speniajcy funkcj separatora.
Jeeli wartoci argumentu bdzie spacja, cigi kilku spacji zostan potraktowanych
jak jedna spacja, co prawie zawsze bdzie zgodne z Twoimi zamierzeniami. Jeeli warto
argumentu
n przekroczy liczb elementów acucha, funkcja zwróci pusty acuch.
Oto kod ródowy funkcji
ExtractElement
jzyka VBA:
Function ExtractElement(Txt, n, Separator) As String
' Zwraca n-ty element acucha tekstowego, w którym poszczególne elementy oddziela okrelony znak
separatora
Dim AllElements As Variant
AllElements = Split(Txt, Separator)
ExtractElement = AllElements(n - 1)
End Function
Funkcja korzysta z funkcji
Split
jzyka VBA zwracajcej tablic typu
Variant
, która
zawiera poszczególne elementy acucha tekstowego. Indeks tablicy rozpoczyna si od
wartoci
0
, a nie
1
, dlatego odwoania do kolejnych elementów tablicy s realizowane
poprzez wyraenie
n-1
.
Zamiana wartoci na sowa
1
Funkcja
SPELLDOLLARS
zwraca „sown” wersj wartoci numerycznych podanych jako
argument wywoania funkcji (tak jak w dobrze kademu znanej formuce SOWNIE: spo-
tykanej na blankietach przelewów i wpat czy fakturach). Na przykad formua przed-
stawiona poniej zwraca nastpujcy acuch tekstu: One hundred twenty-three and
45/100 dollars (sto dwadziecia trzy dolary i 45 centów):
=SPELLDOLLARS(123.45)
1
Uwaga: funkcja opisana w tym podrozdziale zwraca wartoci w jzyku angielskim — przyp. tum.
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
391
Na rysunku 11.17 przedstawiono kilka przykadów zastosowania funkcji
SPELLDOLLARS
.
Formuy zostay umieszczone w kolumnie
C
. Przykadowo formua umieszczona w komórce
C1
ma nastpujc posta:
=SPELLDOLLARS(A1)
Zwró uwag na fakt, e wartoci ujemne s podawane w nawiasach.
Rysunek 11.17. Przykady zastosowania funkcji SPELLDOLLARS
Funkcja
SPELLDOLLARS jest zbyt zoona, aby j tutaj zaprezentowa w caoci,
ale skoroszyt zawierajcy peny kod tej funkcji (Funkcje arkuszowe.xlsm) znajdziesz
na pycie CD-ROM doczonej do ksiki.
Funkcja wielofunkcyjna
Nastpny przykad prezentuje technik, która moe okaza si przydatna w niektórych
sytuacjach. Sprawia ona, e pojedyncza funkcja arkusza zachowuje si jak wiele funkcji.
Poniej zamieszczamy kod ródowy niestandardowej funkcji o nazwie
StatFunction
,
która pobiera dwa argumenty — zakres (
rng
) i operacj (
op
). W zalenoci od wartoci
argumentu
op
funkcja zwraca warto obliczon przy uyciu dowolnej z nastpujcych
funkcji arkuszowych:
AVERAGE
(
REDNIA
),
COUNT
(
ILE.LICZB
),
MAX
,
MEDIAN
(
MEDIANA
),
MIN
,
MODE
(
WYST.NAJCZCIEJ
),
STDEV
(
ODCH.STANDARDOWE
),
SUM
(
SUMA
) lub
VAR
(
WARIANCJA
).
Funkcji
StatFunction
moesz uy w arkuszu w nastpujcy sposób:
=StatFunction(B1:B24, A24)
Wynik formuy zaley od zawartoci komórki
A24
, która powinna by takim acuchem,
jak
Average
,
Count
,
Max
itd. Podobn technik kodowania moesz zastosowa w przy-
padku innych funkcji.
Function StatFunction(rng, op)
Select Case UCase(op)
Case "SUM"
StatFunction = WorksheetFunction.Sum(rng)
Case "AVERAGE"
StatFunction = WorksheetFunction.Average(rng)
Case "MEDIAN"
StatFunction = WorksheetFunction.Median(rng)
392
Cz III
Jzyk Visual Basic for Applications
Case "MODE"
StatFunction = WorksheetFunction.Mode(rng)
Case "COUNT"
StatFunction = WorksheetFunction.Count(rng)
Case "MAX"
StatFunction = WorksheetFunction.Max(rng)
Case "MIN"
StatFunction = WorksheetFunction.Min(rng)
Case "VAR"
StatFunction = WorksheetFunction.Var(rng)
Case "STDEV"
StatFunction = WorksheetFunction.StDev(rng)
Case Else
StatFunction = CVErr(xlErrNA)
End Select
End Function
Funkcja SheetOffset
Excel oferuje ograniczon obsug trójwymiarowych skoroszytów. Jeeli na przykad
konieczne jest odwoanie do innego arkusza skoroszytu, w formule trzeba uwzgldni
nazw arkusza. Nie stanowi to jednak duego problemu… do momentu próby skopiowa-
nia formuy do innych arkuszy. Skopiowane formuy w dalszym cigu odwouj si do
nazwy oryginalnego arkusza, a odwoania do arkuszy nie s modyfikowane tak, jak mia-
oby to miejsce w prawdziwym trójwymiarowym arkuszu.
W tym podrozdziale zosta omówiony przykad funkcji jzyka VBA o nazwie
SheetOffset
,
umoliwiajcej stosowanie wzgldnych odwoa do arkuszy. Na przykad w celu odwoa-
nia si do komórki
A1
poprzedniego arkusza naley uy formuy:
=SheetOffset(-1, A1)
Pierwszy argument funkcji, który moe by wartoci dodatni, ujemn lub zerem, identy-
fikuje wzgldne odwoanie do arkusza. Drugi argument musi by odwoaniem do poje-
dynczej komórki. Po skopiowaniu formuy do innych arkuszy odwoanie wzgldne bdzie
obowizywao we wszystkich jej kopiach.
Oto kod ródowy funkcji
SheetOffset
jzyka VBA:
Function SheetOffset(Offset As Long, Optional Cell As Variant)
' Zwraca zawarto komórki wzgldnie adresowanego arkusza, do której zdefiniowano odwoanie
Dim WksIndex As Long, WksNum As Long
Dim wks As Worksheet
Application.Volatile
If IsMissing(Cell) Then Set Cell = Application.Caller
WksNum = 1
For Each wks In Application.Caller.Parent.Parent.Worksheets
If Application.Caller.Parent.Name = wks.Name Then
SheetOffset = Worksheets(WksNum + Offset).Range(Cell(1).Address)
Exit Function
Else
WksNum = WksNum + 1
End If
Next wks
End Function
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
393
Zwracanie maksymalnej wartoci ze wszystkich arkuszy
Aby okreli maksymaln warto komórki
B1
z kilku arkuszy, mona zastosowa formu
podobn do poniszej:
=MAX(Arkusz1:Arkusz4!B1)
Formua zwraca maksymaln warto komórki
B1
z arkuszy
Arkusz1
,
Arkusz4
i wszystkich,
które znajduj si pomidzy nimi.
Co si jednak stanie, gdy za arkuszem
Arkusz4
zostanie wstawiony nowy arkusz
Arkusz5
?
Formua nie uwzgldni tego automatycznie, dlatego konieczne bdzie jej zmodyfikowanie
w celu dodania nowego odwoania do arkusza:
=MAX(Arkusz1:Arkusz5!B1)
Funkcja
MaxAllSheets
pobiera jeden argument i zwraca maksymaln warto okrelonej
komórki z wszystkich arkuszy skoroszytu. Przykadowo ponisza formua zwraca mak-
symaln warto komórki
B1
z uwzgldnieniem wszystkich arkuszy skoroszytu:
=MaxAllSheets(B1)
Po dodaniu nowego arkusza nie bdzie ju potrzeby edytowania formuy.
Function MaxAllSheets(cell)
Dim MaxVal As Double
Dim Addr As String
Dim Wksht As Object
Application.Volatile
Addr = cell.Range("A1").Address
MaxVal = -9.9E+307
For Each Wksht In cell.Parent.Parent.Worksheets
If Wksht.Name = cell.Parent.Name And _
Addr = Application.Caller.Address Then
' Uniknicie odwoania cyklicznego
Else
If WorksheetFunction.IsNumber(Wksht.Range(Addr)) Then
If Wksht.Range(Addr) > MaxVal Then _
MaxVal = Wksht.Range(Addr).Value
End If
End If
Next Wksht
If MaxVal = -9.9E+307 Then MaxVal = 0
MaxAllSheets = MaxVal
End Function
W celu uzyskania dostpu do skoroszytu ptla
For Each
uywa nastpujcego wyraenia:
cell.Parent.Parent.Worksheets
Obiektem nadrzdnym komórki jest arkusz, natomiast przodkiem arkusza jest skoroszyt.
Wynika z tego, e ptla
For Each ... Next
przetwarza wszystkie arkusze skoroszytu.
Pierwsza instrukcja
If
z ptli sprawdza, czy przetwarzana komórka zawiera funkcj. Jeeli
tak jest, to w celu uniknicia bdu odwoania cyklicznego komórka zostanie zignorowana.
394
Cz III
Jzyk Visual Basic for Applications
Opisana funkcja z atwoci moe zosta zmodyfikowana tak, aby wykonywaa inne
obliczenia midzyarkuszowe oparte na takich funkcjach, jak
MIN, REDNIA, SUMA itd.
Zwracanie tablicy zawierajcej unikatowe,
losowo uporzdkowane liczby cakowite
Funkcja
RandomIntegers
zamieszczona w tym punkcie zwraca tablic unikatowych liczb
cakowitych. Stosowana jest w wielokomórkowych formuach tablicowych.
{=RandomIntegers()}
Zaznacz zakres, a nastpnie wprowad formu (bez nawiasów klamrowych) i zatwierd
j poprzez nacinicie kombinacji klawiszy Ctrl+Shift+Enter. Formua zwraca tablic
zawierajc unikatowe, losowo uporzdkowane liczby cakowite. Jeeli na przykad for-
mua zostanie wprowadzona do zakresu zoonego z 50 komórek, jej kopie zwróc uni-
katowe liczby cakowite z przedziau od 1 do 50.
Oto kod ródowy funkcji
RandomIntegers
:
Function RandomIntegers()
Dim FuncRange As Range
Dim V() As Variant, ValArray() As Variant
Dim CellCount As Double
Dim i As Integer, j As Integer
Dim r As Integer, c As Integer
Dim Temp1 As Variant, Temp2 As Variant
Dim RCount As Integer, CCount As Integer
' Tworzy obiekt klasy Range
Set FuncRange = Application.Caller
' Zwraca bd, jeeli warto obiektu FuncRange jest zbyt dua
CellCount = FuncRange.Count
If CellCount > 1000 Then
RandomIntegers = CVErr(xlErrNA)
Exit Function
End If
' Przypisanie zmiennych
RCount = FuncRange.Rows.Count
CCount = FuncRange.Columns.Count
ReDim V(1 To RCount, 1 To CCount)
ReDim ValArray(1 To 2, 1 To CellCount)
' Wypenienie tablicy losowymi wartociami i liczbami cakowitymi zakresu rng
For i = 1 To CellCount
ValArray(1, i) = Rnd
ValArray(2, i) = i
Next i
' Sortowanie tablicy ValArray wedug wymiaru o losowej wartoci
For i = 1 To CellCount
For j = i + 1 To CellCount
If ValArray(1, i) > ValArray(1, j) Then
Temp1 = ValArray(1, j)
Temp2 = ValArray(2, j)
ValArray(1, j) = ValArray(1, i)
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
395
ValArray(2, j) = ValArray(2, i)
ValArray(1, i) = Temp1
ValArray(2, i) = Temp2
End If
Next j
Next i
' Wstawienie losowo uporzdkowanych wartoci do tablicy V
i = 0
For r = 1 To RCount
For c = 1 To CCount
i = i + 1
V(r, c) = ValArray(2, i)
Next c
Next r
RandomIntegers = V
End Function
Porzdkowanie zakresu w losowy sposób
Funkcja
RangeRandomize
pobiera jeden argument bdcy zakresem i zwraca tablic zoon
z losowo uporzdkowanych wartoci tego zakresu.
Function RangeRandomize(rng)
Dim V() As Variant, ValArray() As Variant
Dim CellCount As Double
Dim i As Integer, j As Integer
Dim r As Integer, c As Integer
Dim Temp1 As Variant, Temp2 As Variant
Dim RCount As Integer, CCount As Integer
' Zwraca bd, jeeli warto obiektu rng jest zbyt dua
CellCount = rng.Count
If CellCount > 1000 Then
RangeRandomize = CVErr(xlErrNA)
Exit Function
End If
' Przypisanie zmiennych
RCount = rng.Rows.Count
CCount = rng.Columns.Count
ReDim V(1 To RCount, 1 To CCount)
ReDim ValArray(1 To 2, 1 To CellCount)
' Wypenienie tablicy ValArray losowymi wartociami i wartociami obiektu rng
For i = 1 To CellCount
ValArray(1, i) = Rnd
ValArray(2, i) = rng(i)
Next i
' Sortowanie tablicy ValArray wedug wymiaru o losowej wartoci
For i = 1 To CellCount
For j = i + 1 To CellCount
If ValArray(1, i) > ValArray(1, j) Then
Temp1 = ValArray(1, j)
Temp2 = ValArray(2, j)
ValArray(1, j) = ValArray(1, i)
ValArray(2, j) = ValArray(2, i)
396
Cz III
Jzyk Visual Basic for Applications
ValArray(1, i) = Temp1
ValArray(2, i) = Temp2
End If
Next j
Next i
' Wstawienie losowo uporzdkowanych wartoci do tablicy V
i = 0
For r = 1 To RCount
For c = 1 To CCount
i = i + 1
V(r, c) = ValArray(2, i)
Next c
Next r
RangeRandomize = V
End Function
Jak atwo zauway, kod ródowy tej funkcji jest bardzo podobny do kodu funkcji
Random
´
Integers
.
Na rysunku 11.18 pokazano wynik dziaania funkcji. Formua tablicowa zawarta w zakresie
B2:B11
ma nastpujc posta:
{=RangeRandomize(A2:A11)}
Formua zwraca zawarto komórek zakresu
A2:A11
, ale uporzdkowanego w losowy
sposób.
Rysunek 11.18.
Funkcja
RangeRandomize
zwraca zawarto
komórek zakresu
w przypadkowej
kolejnoci
Wywoania funkcji interfejsu Windows API
Jedn z najwaniejszych cech jzyka VBA jest moliwo wywoywania funkcji prze-
chowywanych w bibliotekach DLL (ang. Dynamic Link Library). W przykadach zapre-
zentowanych w tym podrozdziale bdziemy korzystali z czsto uywanych funkcji inter-
fejsu API systemu Windows.
Dla uproszczenia deklaracje funkcji API przedstawiane w tym podrozdziale dziaaj tylko
w Excelu 2010 (zarówno w wersji 32-bitowej, jak i 64-bitowej), natomiast przykady
zamieszczone na dysku CD-ROM doczonym do ksiki zawieraj odpowiednie dyrektywy
kompilatora, dziki czemu bd poprawnie dziaa równie we wczeniejszych wersjach
Excela.
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
397
Okrelanie skojarze plików
W systemie Windows wiele typów plików jest kojarzonych z okrelon aplikacj. Po
wykonaniu takiego skojarzenia plik mona otworzy w powizanej z nim aplikacji poprzez
dwukrotne kliknicie pliku.
Funkcja
GetExecutable
w celu uzyskania penej cieki aplikacji skojarzonej z okrelonym
plikiem korzysta z funkcji interfejsu API systemu Windows. Przyjmijmy, e w Twoim sys-
temie znajduje si wiele plików o rozszerzeniu .txt. Jeden z nich, o nazwie Readme.txt, praw-
dopodobnie znajduje si w katalogu systemu Windows. Aby okreli pen ciek aplikacji
otwierajcej plik po jego dwukrotnym klikniciu, mona uy funkcji
GetExecutable
.
Deklaracje funkcji interfejsu API systemu Windows musz zosta umieszczone
na pocztku moduu kodu VBA.
Private Declare Function FindExecutableA Lib "shell32.dll" _
(ByVal lpFile As String, ByVal lpDirectory As String, _
ByVal lpResult As String) As Long
Function GetExecutable(strFile As String) As String
Dim strPath As String
Dim intLen As Integer
strPath = Space(255)
intLen = FindExecutableA(strFile, "\", strPath)
GetExecutable = Trim(strPath)
End Function
Na rysunku 11.19 pokazano wynik wywoania funkcji
GetExecutable
, która jako argu-
ment pobraa nazw pliku muzycznego w formacie MP3. Funkcja zwraca pen ciek
aplikacji powizanej z plikiem.
Rysunek 11.19.
Okrelanie cieki
aplikacji powizanej
z okrelonym plikiem
Skoroszyt z tym przykadem (Skojarzenia plików.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki.
Pobieranie informacji o napdach dyskowych
VBA nie posiada metody pozwalajcej na bezporednie pobieranie informacji o zain-
stalowanych w systemie napdach dyskowych. Jednak dziki zastosowaniu trzech funkcji
API moesz uzyska niezbdne informacje.
Na rysunku 11.20 przedstawiono wynik dziaania procedury VBA, która identyfikuje
wszystkie podczone do systemu dyski, okrela ich typ, sprawdza cakowit pojemno,
rozmiar uytego miejsca oraz rozmiar wolnego miejsca.
398
Cz III
Jzyk Visual Basic for Applications
Rysunek 11.20. Zastosowanie funkcji Windows API do pobierania informacji o dyskach
Kod procedury jest dosy dugi i zoony, dlatego nie umieszczono go tutaj, ale jeeli jeste
ciekawy, jak to dziaa, moesz zajrze do odpowiedniego skoroszytu na pycie CD-ROM
doczonej do ksiki.
Skoroszyt z tym przykadem (Informacja o dyskach.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki.
Pobieranie informacji dotyczcych drukarki domylnej
W kolejnym przykadzie uyjemy funkcji interfejsu Windows API zwracajcej informacje
na temat domylnej drukarki. Dane znajduj si w pojedynczym acuchu tekstowym.
Ponisza procedura analizuje acuch i wywietla informacje przy uyciu bardziej czytel-
nego dla uytkownika formatu:
Private Declare Function GetProfileStringA Lib "kernel32" _
(ByVal lpAppName As String, ByVal lpKeyName As String, _
ByVal lpDefault As String, ByVal lpReturnedString As String, _
ByVal nSize As Long) As Long
Sub DefaultPrinterInfo()
Dim strLPT As String * 255
Dim Result As String
Call GetProfileStringA _
("Windows", "Device", "", strLPT, 254)
Result = Application.Trim(strLPT)
ResultLength = Len(Result)
Comma1 = InStr(1, Result, ",", 1)
Comma2 = InStr(Comma1 + 1, Result, ",", 1)
' Pobiera nazw drukarki
Printer = Left(Result, Comma1 - 1)
' Pobiera informacje na temat sterownika
Driver = Mid(Result, Comma1 + 1, Comma2 - Comma1 - 1)
' Pobiera ostatni cz informacji na temat urzdzenia
Port = Right(Result, ResultLength - Comma2)
' Tworzy komunikat
Msg = "Drukarka:" & Chr(9) & Printer & Chr(13)
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
399
Msg = Msg & "Sterownik:" & Chr(9) & Driver & Chr(13)
Msg = Msg & "Port:" & Chr(9) & Port
' Wywietla komunikat
MsgBox Msg, vbInformation, "Informacje o drukarce domylnej"
End Sub
Co prawda waciwo
ActivePrinter obiektu Application zwraca nazw domylnej
drukarki i umoliwia jej zmian, ale nie istnieje bezporednia metoda okrelenia, jaki
sterownik i port urzdzenia jest uywany. Z tego wanie powodu czasami przydatna
moe by nasza funkcja
GetProfileStringA.
Na rysunku 11.21 pokazano przykadowe okno komunikatu wywietlone przez t procedur.
Rysunek 11.21.
Informacja o drukarce
domylnej wywietlona
przy uyciu funkcji
interfejsu API systemu
Windows
Skoroszyt z tym przykadem (Informacja o drukarce.xlsm) znajdziesz na pycie CD-ROM
doczonej do ksiki.
Pobieranie informacji o aktualnej rozdzielczoci karty graficznej
Zamieszczony w tym punkcie kod korzysta z funkcji interfejsu API w celu okrelenia
aktualnej rozdzielczoci karty graficznej uywanej w systemie. Jeeli uywana aplikacja
musi wywietli okrelon ilo informacji na jednym ekranie, znajomo jego rozdziel-
czoci moe pomóc we waciwym przeskalowaniu tekstu. Oprócz tego kod procedury
sprawdza liczb monitorów podczonych do komputera. Jeeli podczonych jest wicej
monitorów ni jeden, procedura wywietla rozmiary pulpitu wirtualnego.
Declare PtrSafe Function GetSystemMetrics Lib "user32" _
(ByVal nIndex As Long) As Long
Public Const SM_CMONITORS = 80
Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1
Public Const SM_CXVIRTUALSCREEN = 78
Public Const SM_CYVIRTUALSCREEN = 79
Sub DisplayVideoInfo()
Dim numMonitors As Long
Dim vidWidth As Long, vidHeight As Long
Dim virtWidth As Long, virtHeight As Long
Dim Msg As String
numMonitors = GetSystemMetrics(SM_CMONITORS)
vidWidth = GetSystemMetrics(SM_CXSCREEN)
vidHeight = GetSystemMetrics(SM_CYSCREEN)
virtWidth = GetSystemMetrics(SM_CXVIRTUALSCREEN)
virtHeight = GetSystemMetrics(SM_CYVIRTUALSCREEN)
If numMonitors > 1 Then
Msg = numMonitors & " monitory podczone" & vbCrLf
400
Cz III
Jzyk Visual Basic for Applications
Msg = Msg & "Pulpit wirtualny: " & virtWidth & " × "
Msg = Msg & virtHeight & vbCrLf & vbCrLf
Msg = Msg & "Rozdzielczo gównego monitora to: "
Msg = Msg & vidWidth & " × " & vidHeight
Else
Msg = Msg & "Aktualny tryb graficzny: "
Msg = Msg & vidWidth & " × " & vidHeight
End If
MsgBox Msg
End Sub
Na rysunku 11.22 pokazano okno komunikatu zwrócone przez powysz procedur uru-
chomion w systemie uywajcym dwóch monitorów.
Rysunek 11.22.
Zastosowanie funkcji
interfejsu Windows API
do okrelenia
rozdzielczoci karty
graficznej
Skoroszyt z tym przykadem (Informacja o rozdzielczoci karty graficznej.xlsm) znajdziesz
na pycie CD-ROM doczonej do ksiki.
Dodanie dwiku do aplikacji
Przykad, który omówiono w tym podrozdziale, pozwala na dodanie efektów dwikowych
do aplikacji Excela, a w szczególnoci pozwala na odtwarzanie dwików zapisanych
w formacie MIDI lub WAV. Moesz na przykad odtwarza wybrany dwik w momencie
otwierania danego okna dialogowego (lub nie…). Jeeli bdziesz chcia, aby Excel odtwa-
rza pliki w formacie MIDI lub WAV, w tej sekcji znajdziesz dokadnie to, czego bdziesz
potrzebowa.
Skoroszyt z tym przykadem (Dwik.xlsm) znajdziesz na pycie CD-ROM doczonej
do ksiki.
Odtwarzanie plików typu WAV
Poniszy przykad zawiera deklaracj funkcji interfejsu API wraz z prost procedur
odtwarzajc plik dwikowy o nazwie
sound.wav
, który znajduje si w tym samym kata-
logu, co plik skoroszytu:
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Sub PlayWAV()
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
401
WAVFile = "sound.wav"
WAVFile = ThisWorkbook.Path & "\" & WAVFile
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub
W przykadzie plik formatu WAV jest odtwarzany asynchronicznie. Oznacza to, e w trak-
cie odtwarzania kontynuowane jest wykonywanie procedury. Aby zatrzyma wykonywa-
nie kodu ródowego podczas odtwarzania dwiku, naley uy nastpujcej instrukcji:
Call PlaySound(WAVFile, 0&, SND_SYNC Or SND_FILENAME)
Odtwarzanie pliku formatu MIDI
W przypadku pliku formatu MIDI konieczne jest zastosowanie innej funkcji interfejsu
API. Procedura
PlayMIDI
rozpoczyna odtwarzanie pliku formatu MIDI. Wykonanie pro-
cedury
StopMIDI
spowoduje zakoczenie odtwarzania pliku. W przykadzie uyto pliku
o nazwie
helion.mid
.
Private Declare Function mciExecute Lib "winmm.dll" _
(ByVal lpstrCommand As String) As Long
Sub PlayMIDI()
MIDIFile = " helion.mid"
MIDIFile = ThisWorkbook.Path & "\" & MIDIFile
MciExecute ("play " & MIDIFile)
End Sub
Sub StopMIDI()
MIDIFile = "helion.mid"
MIDIFile = ThisWorkbook.Path & "\" & MIDIFile
MciExecute ("stop " & MIDIFile)
End Sub
Odtwarzanie dwiku przy uyciu funkcji arkuszowej
Funkcja
Alarm
zostaa stworzona z myl o zastosowaniu w formule arkusza. Jeeli komórka
spenia okrelone kryterium, funkcja, uywajc interfejsu API systemu Windows, odtwarza
plik dwikowy.
Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long
Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\sound.wav"
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Else
Alarm = False
End If
End Function
402
Cz III
Jzyk Visual Basic for Applications
Funkcja
Alarm
pobiera dwa argumenty — odwoanie do komórki i warunek (majcy posta
acucha). Ponisza formua uywa funkcji
Alarm
do odtworzenia pliku formatu WAV, gdy
warto komórki
B13
bdzie wiksza lub równa
1000
:
=ALARM(B13; ">=1000")
W celu stwierdzenia, czy warto komórki spenia okrelone kryterium, funkcja korzysta
z funkcji
Evaluate
jzyka VBA. Po spenieniu kryterium i wygenerowaniu dwiku funkcja
zwróci warto
True
. W przeciwnym razie zwróci warto
False
.
Funkcja
SayIt, omawiana wczeniej w tym rozdziale, jest znacznie prostszym sposobem
na wzbogacenie aplikacji w efekty dwikowe.
Skoroszyt z tymi przykadami (dwik.xlsm) znajdziesz na pycie CD-ROM doczonej
do ksiki.
Odczytywanie zawartoci rejestru systemu Windows
i zapisywanie w nim danych
Wikszo aplikacji Windows potrzebne informacje przechowuje w rejestrze systemu
bdcym baz danych. Aby uzyska dodatkowe informacje o rejestrze, naley zajrze do
rozdziau 4. Procedury jzyka VBA s w stanie odczytywa dane z rejestru i zapisywa
w nim nowe wartoci. Aby to byo moliwe, konieczne jest zastosowanie nastpujcych
deklaracji funkcji interfejsu API systemu Windows:
Private Declare PtrSafe Function RegOpenKeyA Lib "ADVAPI32.DLL" _
(ByVal hKey As Long, ByVal sSubKey As String, _
ByRef hkeyResult As Long) As Long
Private Declare PtrSafe Function RegCloseKey Lib "ADVAPI32.DLL" _
(ByVal hKey As Long) As Long
Private Declare PtrSafe Function RegSetValueExA Lib "ADVAPI32.DLL" _
(ByVal hKey As Long, ByVal sValueName As String, _
ByVal dwReserved As Long, ByVal dwType As Long, _
ByVal sValue As String, ByVal dwSize As Long) As Long
Private Declare PtrSafe Function RegCreateKeyA Lib "ADVAPI32.DLL" _
(ByVal hKey As Long, ByVal sSubKey As String, _
ByRef hkeyResult As Long) As Long
Private Declare PtrSafe Function RegQueryValueExA Lib "ADVAPI32.DLL" _
(ByVal hKey As Long, ByVal sValueName As String, _
ByVal dwReserved As Long, ByRef lValueType As Long, _
ByVal sValue As String, ByRef lResultLen As Long) As Long
Utworzyem dwie funkcje osonowe uatwiajce korzystanie z rejestru. S to:
GetRegistry
i
WriteRegistry. Obie znajduj si na doczonym dysku CD-ROM, w skoroszycie
o nazwie Rejestr systemu Windows.xlsm. Przykadowy skoroszyt zawiera procedur
demonstrujc odczyt i zapis danych w rejestrze.
Odczyt danych z rejestru
Funkcja
GetRegistry
zwraca ustawienia znajdujce si w okrelonej lokalizacji rejestru.
Funkcja pobiera trzy argumenty:
Rozdzia 11.
Przykady i techniki programowania w jzyku VBA
403
RootKey
— acuch reprezentujcy gówny klucz rejestru, który zostanie uyty.
Oto moliwe acuchy:
HKEY_CLASSES_ROOT
HKEY_CURRENT_USER
HKEY_LOCAL_MACHINE
HKEY_USERS
HKEY_CURRENT_CONFIG
Path
— pena cieka kategorii rejestru, która zostanie uyta.
RegEntry
— nazwa ustawienia, które zostanie odczytane.
Aby na przykad odnale w rejestrze aktualne ustawienie powizane z aktywnym paskiem
tytuu okna, naley w sposób pokazany poniej wywoa funkcj
GetRegistry
(wielko
znaków nazw argumentów nie jest rozróniana):
RootKey = "hkey_current_user"
Path = "Control Panel\Desktop"
RegEntry = "WallPaper"
MsgBox GetRegistry(RootKey, Path, RegEntry), _
vbInformation, Path & "\RegEntry"
Okno komunikatu wywietli ciek i nazw pliku graficznego uytego w roli tapety pulpitu
(jeeli tapeta nie jest uywana, funkcja zwróci pusty acuch).
Zapis danych w rejestrze
Funkcja
WriteRegistry
zapisuje warto w okrelonej lokalizacji rejestru. Jeeli operacja
zakoczy si powodzeniem, funkcja zwróci warto
True
. W przeciwnym razie zwróci
warto
False
. Funkcja
WriteRegistry
pobiera nastpujce argumenty (wszystkie s a-
cuchami tekstu):
RootKey
— acuch reprezentujcy klucz rejestru, który zostanie uyty.
Oto moliwe acuchy:
HKEY_CLASSES_ROOT
HKEY_CURRENT_USER
HKEY_LOCAL_MACHINE
HKEY_USERS
HKEY_CURRENT_CONFIG
Path
— pena cieka kategorii rejestru (jeeli cieka nie istnieje, zostanie
utworzona).
RegEntry
— nazwa kategorii rejestru, w której zostanie zapisana warto (jeeli
kategoria nie istnieje, zostanie dodana).
RegVal
— zapisywana warto.
404
Cz III
Jzyk Visual Basic for Applications
Poniej zamieszczono przykad procedury zapisujcej w rejestrze warto reprezentujc
dat i czas uruchomienia Excela. Informacja jest zapisywana w miejscu, w którym s prze-
chowywane ustawienia dotyczce Excela.
Sub Workbook_Open()
RootKey = "hkey_current_user"
Path = "software\microsoft\office\14.0\Excel\LastStarted"
RegEntry = "DateTime"
RegVal = Now()
If WriteRegistry(RootKey, Path, RegEntry, RegVal) Then
msg = RegVal & " zostaa zapisana w rejestrze."
Else
msg = "Wystpi bd."
End If
MsgBox msg
End Sub
Jeeli zapiszesz t procedur w module
ThisWorkbook
skoroszytu makr osobistych, usta-
wienia bd automatycznie aktualizowane przy kadym uruchomieniu programu Excel.
atwiejszy sposób uzyskania dostpu do rejestru
Jeeli w celu zapisania i odczytania danych dostp do rejestru systemu Windows chcesz uzyska
z poziomu aplikacji Excela, nie musisz stosowa funkcji interfejsu API. Zamiast nich mona uy
funkcji
GetSetting i SaveSetting jzyka VBA.
Obie funkcje zostay objanione w systemie pomocy, dlatego nie bd ich tutaj szczegóowo oma-
wia. Jednak naley wiedzie, e funkcje te dziaaj tylko z kluczem o nastpujcej nazwie:
HKEY_CURRENT_USER\Software\VB and VBA Program Settings
Innymi sowy, funkcje nie mog zosta zastosowane w celu uzyskania dostpu do dowolnego
klucza rejestru. Funkcje te s najbardziej przydatne do zapisywania informacji o wasnych aplika-
cjach Excela, które chcesz przechowa pomidzy kolejnymi sesjami.
