Excel 2010 PL Programowanie w VBA Vademecum Walkenbacha

background image
background image

Idź do

• Spis treści
• Przykładowy rozdział

• Katalog online

• Dodaj do koszyka

• Zamów cennik

• Zamów informacje

o nowościach

• Fragmenty książek

online

Helion SA

ul. Kościuszki 1c

44-100 Gliwice

tel. 32 230 98 63

e-mail: helion@helion.pl

© Helion 1991–2010

Katalog książek

Twój koszyk

Cennik i informacje

Czytelnia

Kontakt

• Zamów drukowany

katalog

Excel 2010 PL.
Programowanie w VBA.
Vademecum Walkenbacha

Autor:

John Walkenbach

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!

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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.

background image

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

background image

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

background image

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.

background image

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

background image

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:

background image

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

background image

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

background image

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.

background image

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.

background image

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

background image

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.

background image

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.

background image

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.

background image

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

background image

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

background image

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

background image

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

background image

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

.

background image

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

background image

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

background image

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

background image

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.

background image

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)

background image

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.

background image

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

background image

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

background image

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

background image

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:

background image

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

:

background image

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

background image

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

background image

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

background image

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.

background image

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

.

background image

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

background image

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, _

background image

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

background image

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.

background image

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

background image

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

background image

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

background image

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

background image

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

background image

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

:

background image

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)

background image

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

background image

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.

background image

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##")

background image

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.

background image

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)

background image

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

background image

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.

background image

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)

background image

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)

background image

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.

background image

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.

background image

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)

background image

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

background image

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

background image

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

background image

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:

background image

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.

background image

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.

background image

Wyszukiwarka

Podobne podstrony:
Excel 2010 PL Programowanie w VBA Vademecum Walkenbacha 2
Excel 2010 PL Programowanie w VBA Vademecum Walkenbacha e21pvb
Excel 2010 PL Programowanie w VBA Vademecum Walkenbacha e21pvb
Excel 2010 PL Programowanie w VBA Vademecum Walkenbacha e21pvb(1)
Excel 2010 PL Programowanie w VBA Vademecum Walkenbacha e21pvb
Excel 2010 PL Programowanie w VBA Vademecum Walkenbacha e21pvb
Excel 2010 PL Programowanie w VBA Vademecum Walkenbacha e21pvb
Excel 2010 PL Programowanie w VBA Vademecum Walkenbacha e21pvb
Excel 2010 PL Programowanie w VBA Vademecum Walkenbacha e21pvb
Excel 2003 PL Programowanie w VBA Vademecum profesjonalisty
Excel 2003 PL Programowanie w VBA Vademecum profesjonalisty
Excel 2010 Programowanie w VBA Vademecum Walkenbacha
Excel 2010 Programowanie w VBA Vademecum Walkenbacha

więcej podobnych podstron