Excel_2010_PL_Programowanie_w_VBA_Vademecum_Walkenbacha_e21pvb

Idź do • Spis treści • Przykładowy rozdział Katalog książek • Katalog online • Zamów drukowany katalog Twój koszyk • Dodaj do koszyka Cennik i informacje • Zamów informacje o nowościach • Zamów cennik Czytelnia • Fragmenty książek online

Kontakt Helion SA ul. Kościuszki 1c 44-100 Gliwice tel. 32 230 98 63 e-mail: helion@helion.pl © Helion 1991–2010

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!

Spis tre ci O autorze ............................................................................................... 19 Przedmowa ............................................................................................ 21

Cz I Rozdzia 1.

Podstawowe informacje ....................................................29 Sk d si wzi Excel 2010? ................................................................... 31 Krótka historia arkuszy kalkulacyjnych ..............................................................................31 Wszystko zacz o si od programu VisiCalc .................................................................31 Lotus 1-2-3 ....................................................................................................................32 Quattro Pro ....................................................................................................................35 Microsoft Excel .............................................................................................................36 Excel jako dobre narz dzie dla projektantów aplikacji ........................................................41 Rola Excela w strategii Microsoftu .....................................................................................43

Rozdzia 2.

Program Excel w zarysie ...................................................................... 45 My lenie 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 u ytkownika programu Excel ................................................................................50 Wprowadzenie do Wst ki ............................................................................................51 Menu podr czne i minipasek narz dzi ..........................................................................57 Okna dialogowe ............................................................................................................58 Skróty klawiszowe ........................................................................................................59 Tagi inteligentne ............................................................................................................59 Panel zada ...................................................................................................................60 Dostosowywanie wy wietlania do w asnych potrzeb ..........................................................61 Wprowadzanie danych ........................................................................................................61 Formu y, 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 u yciu has a ..........................................................................66 Ochrona kodu VBA przy u yciu has a ..........................................................................67 Wykresy ..............................................................................................................................68 Kszta ty i obiekty typu SmartArt .........................................................................................68

4

Spis tre ci

Dost p do baz danych ..........................................................................................................69 Arkuszowe bazy danych ................................................................................................69 Zewn trzne bazy danych ...............................................................................................70 Funkcje internetowe ............................................................................................................71 Narz dzia 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 Formu y ...............................................................................................................................77 Obliczanie formu ................................................................................................................78 Odwo ania do komórki lub zakresu .....................................................................................79 Dlaczego warto u ywa odwo a , które nie s wzgl dne? ............................................79 Notacja W1K1 ...............................................................................................................80 Odwo ania do innych arkuszy lub skoroszytów ............................................................81 Zastosowanie nazw ..............................................................................................................83 Nadawanie nazw komórkom i zakresom .......................................................................83 Nadawanie nazw istniej cym odwo aniom ...................................................................83 Stosowanie nazw z operatorem przeci cia ....................................................................84 Nadawanie nazw kolumnom i wierszom .......................................................................85 Obszar obowi zywania nazw ........................................................................................85 Nadawanie nazw sta ym ................................................................................................86 Nadawanie nazw formu om ...........................................................................................87 Nadawanie nazw obiektom ...........................................................................................88 B dy wyst puj ce w formu ach ..........................................................................................89 Formu y tablicowe ...............................................................................................................89 Przyk adowa formu a tablicowa ....................................................................................90 Kalendarz oparty na formule tablicowej .......................................................................91 Zalety i wady formu tablicowych .................................................................................92 Metody zliczania i sumowania ............................................................................................93 Przyk ady formu zliczaj cych ......................................................................................94 Przyk ady formu sumuj cych .......................................................................................95 Inne narz dzia zliczaj ce ...............................................................................................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 obs ugiwane 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 Przegl danie dost pnych szablonów ...........................................................................108 Tworzenie szablonów ..................................................................................................109 Tworzenie szablonów skoroszytu ................................................................................110 Budowa plików programu Excel .......................................................................................111 Zagl damy do wn trza pliku .......................................................................................112 Dlaczego format pliku jest taki wa ny? ......................................................................115

Spis tre ci

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 Rozdzia 5.

Projektowanie aplikacji w Excelu ...................................123 Czym jest aplikacja arkusza kalkulacyjnego? ................................ 125 Aplikacje arkuszy kalkulacyjnych .....................................................................................125 Projektant i u ytkownik ko cowy .....................................................................................126 Kim s projektanci i czym si zajmuj ? ......................................................................127 Klasyfikacja u ytkowników arkuszy kalkulacyjnych ..................................................128 Odbiorcy aplikacji arkusza kalkulacyjnego .................................................................129 Rozwi zywanie problemów przy u yciu Excela ...............................................................129 Podstawowe kategorie arkuszy kalkulacyjnych .................................................................130 Arkusze robocze ..........................................................................................................131 Arkusze przeznaczone wy cznie do u ytku prywatnego ............................................131 Aplikacje jednego u ytkownika ..................................................................................132 Aplikacje typu „spaghetti” ..........................................................................................132 Aplikacje narz dziowe ................................................................................................133 Dodatki zawieraj ce funkcje arkusza ..........................................................................133 Arkusze jednoblokowe ................................................................................................134 Modele warunkowe .....................................................................................................134 Aplikacje bazodanowe (przechowuj ce i udost pniaj ce dane) ..................................134 Aplikacje komunikuj ce si z bazami danych .............................................................135 Aplikacje „pod klucz” .................................................................................................135

Rozdzia 6.

Podstawy projektowania aplikacji arkusza kalkulacyjnego .......... 137 Podstawowe etapy projektowania ......................................................................................137 Okre lanie wymaga u ytkownika ....................................................................................138 Planowanie aplikacji spe niaj cej wymagania u ytkownika .............................................139 Wybieranie odpowiedniego interfejsu u ytkownika ..........................................................141 Dostosowywanie Wst ki do potrzeb u ytkownika ....................................................144 Dostosowywanie menu podr cznego do potrzeb u ytkownika ...................................144 Tworzenie klawiszy skrótu ..........................................................................................145 Tworzenie niestandardowych okien dialogowych .......................................................146 Zastosowanie formantów ActiveX w arkuszu .............................................................146 Rozpocz cie prac projektowych ..................................................................................148 Zadania realizowane z my l o ko cowym u ytkowniku ..................................................149 Testowanie aplikacji ....................................................................................................149 Uodpornianie aplikacji na b dy pope niane przez u ytkownika ................................150 Nadawanie aplikacji przyjaznego, intuicyjnego i estetycznego wygl du ....................152 Tworzenie systemu pomocy i dokumentacji przeznaczonej dla u ytkownika .............154 Dokumentowanie prac projektowych ..........................................................................155 Przekazanie aplikacji u ytkownikom ..........................................................................155 Aktualizacja aplikacji (kiedy to konieczne) ................................................................156 Pozosta e kwestie dotycz ce projektowania ......................................................................156 Wersja Excela zainstalowana przez u ytkownika .......................................................157 Wersje j zykowe .........................................................................................................157 Wydajno systemu .....................................................................................................157 Tryby karty graficznej .................................................................................................158

6

Spis tre ci

Cz III

J zyk Visual Basic for Applications ................................159

Rozdzia 7.

Wprowadzenie do j zyka VBA .......................................................... 161 Podstawowe informacje o j zyku BASIC .........................................................................161 J zyk VBA ........................................................................................................................162 Modele obiektowe .......................................................................................................162 Porównanie j zyka VBA z j zykiem XLM .................................................................162 Wprowadzenie do j zyka VBA .........................................................................................163 Edytor VBE .......................................................................................................................165 Wy wietlanie karty Deweloper ...................................................................................166 Uruchamianie edytora VBE ........................................................................................167 Okna edytora VBE ......................................................................................................167 Tajemnice okna Project Explorer ......................................................................................169 Dodawanie nowego modu u VBA ...............................................................................170 Usuwanie modu u VBA ..............................................................................................171 Eksportowanie i importowanie obiektów ....................................................................171 Tajemnice okna Code ........................................................................................................171 Minimalizacja i maksymalizacja okien .......................................................................172 Przechowywanie kodu ród owego j zyka VBA ........................................................172 Wprowadzanie kodu ród owego j zyka 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 w a ciwie zapisuje rejestrator makr? .....................................................................186 Odwo ania wzgl dne czy bezwzgl dne? .....................................................................187 Opcje zwi zane z rejestrowaniem makr ......................................................................191 Modyfikowanie zarejestrowanych makr .....................................................................191 Obiekty i kolekcje .............................................................................................................192 Hierarchia obiektów ....................................................................................................193 Kolekcje ......................................................................................................................194 Odwo ywanie si do obiektów ....................................................................................195 W a ciwo ci i metody ........................................................................................................196 W a ciwo ci obiektów .................................................................................................196 Metody obiektowe .......................................................................................................197 Tajemnice obiektu Comment .............................................................................................198 Pomoc dla obiektu Comment ......................................................................................199 W a ciwo ci obiektu Comment ...................................................................................199 Metody obiektu Comment ...........................................................................................199 Kolekcja Comments ....................................................................................................201 W a ciwo Comment .................................................................................................202 Obiekty zawarte w obiekcie Comment ........................................................................202 Sprawdzanie, czy komórka posiada komentarz ...........................................................203 Dodawanie nowego obiektu Comment ........................................................................204 Kilka przydatnych w a ciwo ci obiektu Application .........................................................205 Tajemnice obiektów Range ...............................................................................................206 W a ciwo Range ......................................................................................................207 W a ciwo Cells ........................................................................................................209 W a ciwo Offset ......................................................................................................210 Co nale y wiedzie o obiektach? .......................................................................................212 Podstawowe zagadnienia, które nale y zapami ta ....................................................212 Dodatkowe informacje na temat obiektów i w a ciwo ci ............................................213

Spis tre ci

Rozdzia 8.

7

Podstawy programowania w j zyku VBA ......................................... 217 Przegl d elementów j zyka VBA ......................................................................................217 Komentarze .......................................................................................................................219 Zmienne, typy danych i sta e .............................................................................................220 Definiowanie typów danych ........................................................................................222 Deklarowanie zmiennych ............................................................................................222 Zasi g zmiennych ........................................................................................................226 Zastosowanie sta ych ...................................................................................................229 Praca z a cuchami 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 u ytkownika ..................................................................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 p tli ...........................................................252

Rozdzia 9.

Tworzenie procedur w j zyku VBA .................................................... 261 Kilka s ów o procedurach ..................................................................................................261 Deklarowanie procedury Sub ......................................................................................262 Zasi g procedury .........................................................................................................263 Wykonywanie procedur Sub .............................................................................................264 Uruchamianie procedury przy u yciu polecenia Run Sub/UserForm ..........................265 Uruchamianie procedury z poziomu okna dialogowego Makro ..................................265 Uruchamianie procedury przy u yciu skrótu z klawiszem Ctrl ...................................266 Uruchamianie procedury za pomoc Wst ki .............................................................267 Uruchamianie procedur za po rednictwem niestandardowego menu podr cznego .....267 Wywo ywanie procedury z poziomu innej procedury .................................................267 Uruchamianie procedury poprzez klikni cie obiektu ..................................................271 Wykonywanie procedury po wyst pieniu okre lonego zdarzenia ...............................273 Uruchamianie procedury z poziomu okna Immediate .................................................274 Przekazywanie argumentów procedurom ..........................................................................275 Metody obs ugi b dów .....................................................................................................278 Przechwytywanie b dów ............................................................................................278 Przyk ady kodu ród owego obs uguj cego b dy ......................................................279 Praktyczny przyk ad wykorzystuj cy procedury Sub ........................................................282 Cel ...............................................................................................................................283 Wymagania projektowe ...............................................................................................283 Co ju wiesz ................................................................................................................283 Podej cie do zagadnienia ............................................................................................284 Co musimy wiedzie ? .................................................................................................285 Wst pne rejestrowanie makr .......................................................................................285 Wst pne przygotowania ..............................................................................................286 Tworzenie kodu ród owego .......................................................................................288

8

Spis tre ci

Tworzenie procedury sortuj cej ..................................................................................289 Dodatkowe testy ..........................................................................................................292 Usuwanie problemów ..................................................................................................293 Dost pno narz dzia ..................................................................................................296 Ocena projektu ............................................................................................................296

Rozdzia 10. Tworzenie funkcji w j zyku 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 j zyka VBA ........................................................302 Analiza funkcji niestandardowej .................................................................................302 Procedury Function ...........................................................................................................304 Zasi g funkcji ..............................................................................................................306 Wywo ywanie procedur Function ...............................................................................306 Argumenty funkcji ............................................................................................................310 Przyk ady funkcji ..............................................................................................................311 Funkcja bezargumentowa ............................................................................................311 Funkcja jednoargumentowa ........................................................................................313 Funkcje z dwoma argumentami ...................................................................................316 Funkcja pobieraj ca tablic jako argument .................................................................317 Funkcje z argumentami opcjonalnymi ........................................................................318 Funkcje zwracaj ce tablic VBA ................................................................................319 Funkcje zwracaj ce warto b du ..............................................................................322 Funkcje o nieokre lonej liczbie argumentów ..............................................................323 Emulacja funkcji arkuszowej SUMA ................................................................................324 Rozszerzone funkcje daty ..................................................................................................327 Wykrywanie i usuwanie b dó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 Przyk ady zastosowania funkcji interfejsu API systemu Windows .............................336 Identyfikacja katalogu domowego systemu Windows ................................................337 Wykrywanie wci ni cia klawisza Shift .......................................................................338 Dodatkowe informacje na temat funkcji interfejsu API ..............................................339

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA ........................ 341 Nauka poprzez praktyk ....................................................................................................341 Przetwarzanie zakresów ....................................................................................................342 Kopiowanie zakresów .................................................................................................342 Przenoszenie zakresów ................................................................................................344 Kopiowanie zakresu o zmiennej wielko ci ..................................................................344 Zaznaczanie oraz identyfikacja ró nego typu zakresów ..............................................345 Wprowadzanie warto ci do komórki ...........................................................................346 Wprowadzanie warto ci do nast pnej pustej komórki .................................................348 Wstrzymywanie dzia ania makra w celu umo liwienia pobrania zakresu wyznaczonego przez u ytkownika ...........................................................................350 Zliczanie zaznaczonych komórek ................................................................................351 Okre lanie typu zaznaczonego zakresu .......................................................................352 Wydajne przetwarzanie komórek zaznaczonego zakresu przy u yciu p tli ................353

Spis tre ci

9

Usuwanie wszystkich pustych wierszy ........................................................................356 Powielanie wierszy ......................................................................................................357 Okre lanie, czy zakres zawiera si w innym zakresie .................................................358 Okre lanie typu danych zawartych w komórce ...........................................................359 Odczytywanie i zapisywanie zakresów .......................................................................360 Lepsza metoda zapisywania zakresu ...........................................................................361 Przenoszenie zawarto ci tablic jednowymiarowych ....................................................363 Przenoszenie zawarto ci zakresu do tablicy typu Variant ...........................................363 Zaznaczanie komórek na podstawie warto ci ..............................................................364 Kopiowanie nieci g ego 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 j zyku VBA ..........................................................................370 Prze czanie warto ci w a ciwo ci typu logicznego ....................................................370 Okre lanie liczby drukowanych stron .........................................................................371 Wy wietlanie daty i czasu ...........................................................................................372 Pobieranie listy czcionek .............................................................................................373 Sortowanie tablicy .......................................................................................................374 Przetwarzanie grupy plików ........................................................................................376 Ciekawe funkcje, których mo esz u y w swoich projektach ...........................................378 Funkcja FileExists .......................................................................................................378 Funkcja FileNameOnly ...............................................................................................378 Funkcja PathExists ......................................................................................................379 Funkcja RangeNameExists ..........................................................................................379 Funkcja SheetExists ....................................................................................................380 Funkcja WorkbookIsOpen ..........................................................................................381 Pobieranie warto ci z zamkni tego skoroszytu ...........................................................381 U yteczne, niestandardowe funkcje arkuszowe .................................................................382 Funkcje zwracaj ce informacje o formatowaniu komórki ...........................................382 Gadaj cy arkusz? ........................................................................................................384 Wy wietlanie daty zapisania lub wydrukowania pliku ................................................384 Obiekty nadrz dne ......................................................................................................385 Zliczanie komórek, których warto ci zawieraj si pomi dzy dwoma warto ciami ...386 Wyznaczanie ostatniej niepustej komórki kolumny lub wiersza .................................387 Czy dany a cuch tekstu jest zgodny z wzorcem? .......................................................388 Wyznaczanie n-tego elementu a cucha ......................................................................390 Zamiana warto ci na s owa .........................................................................................390 Funkcja wielofunkcyjna ..............................................................................................391 Funkcja SheetOffset ....................................................................................................392 Zwracanie maksymalnej warto ci ze wszystkich arkuszy ...........................................393 Zwracanie tablicy zawieraj cej unikatowe, losowo uporz dkowane liczby ca kowite .......................................................................................................394 Porz dkowanie zakresu w losowy sposób ...................................................................395 Wywo ania funkcji interfejsu Windows API .....................................................................396 Okre lanie skojarze plików .......................................................................................397 Pobieranie informacji o nap dach dyskowych ............................................................397 Pobieranie informacji dotycz cych drukarki domy lnej ..............................................398 Pobieranie informacji o aktualnej rozdzielczo ci karty graficznej ..............................399 Dodanie d wi ku do aplikacji .....................................................................................400 Odczytywanie zawarto ci rejestru systemu Windows i zapisywanie w nim danych .....402

10

Spis tre ci

Cz IV

Praca z formularzami UserForm ......................................405

Rozdzia 12. Tworzenie w asnych okien dialogowych .......................................... 407 Zanim rozpoczniesz tworzenie formularza UserForm .......................................................407 Okno wprowadzania danych .............................................................................................407 Funkcja InputBox j zyka VBA ...................................................................................408 Metoda InputBox Excela .............................................................................................409 Funkcja MsgBox j zyka VBA ...........................................................................................412 Metoda GetOpenFilename programu Excel ......................................................................415 Metoda GetSaveAsFilename programu Excel ...................................................................419 Okno wybierania katalogu .................................................................................................419 Wy wietlanie wbudowanych okien dialogowych Excela ..................................................420 Wy wietlanie formularza danych ......................................................................................421 Wy wietlanie formularza wprowadzania danych ........................................................423 Wy wietlanie formularza wprowadzania danych za pomoc VBA .............................424

Rozdzia 13. Wprowadzenie do formularzy UserForm ........................................... 425 Jak Excel obs uguje 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 w a ciwo ci formantรณw ...........................................................................432 Zastosowanie okna Properties .....................................................................................432 Wspรณlne w a ciwo ci ..................................................................................................435 Uwzgl dnienie wymaga u ytkownikรณw preferuj cych korzystanie z klawiatury .....435 Wy wietlanie formularza UserForm ..................................................................................438 Wy wietlanie niemodalnych okien formularzy UserForm ..........................................438 Wy wietlanie formularza UserForm na podstawie zmiennej ......................................439

adowanie formularza UserForm ................................................................................439 Procedury obs ugi zdarze ..........................................................................................439 Zamykanie formularza UserForm ......................................................................................439 Przyk ad tworzenia formularza UserForm .........................................................................441 Tworzenie formularza UserForm ................................................................................441 Tworzenie kodu procedury wy wietlaj cej okno dialogowe .......................................444 Testowanie okna dialogowego ....................................................................................444 Dodawanie procedur obs ugi zdarze ..........................................................................445 Sprawdzanie poprawno ci danych ..............................................................................447 Zako czenie tworzenia okna dialogowego ..................................................................447

Spis tre ci

11

Zdarzenia powi zane z formularzem UserForm ................................................................447 Zdobywanie informacji na temat zdarze ....................................................................448 Zdarzenia formularza UserForm .................................................................................449 Zdarzenia zwi zane z formantem SpinButton .............................................................449 Wspó praca formantu SpinButton z formantem TextBox ...........................................451 Odwo ywanie si do formantów formularza UserForm .....................................................453 Dostosowywanie okna Toolbox do w asnych 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. Przyk ady formularzy UserForm .......................................................... 459 Tworzenie formularza UserForm pe ni cego funkcj menu ..............................................459 Zastosowanie w formularzu UserForm formantów CommandButton .........................460 Zastosowanie w formularzu UserForm formantu ListBox ..........................................460 Zaznaczanie zakresów przy u yciu formularza UserForm ................................................461 Tworzenie okna powitalnego .............................................................................................463 Wy czanie przycisku Zamknij formularza UserForm ......................................................465 Zmiana wielko ci formularza UserForm ...........................................................................466 Powi kszanie i przewijanie arkusza przy u yciu 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 kolejno ci 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 zewn trznych ............................................................................488 Animowanie etykiet ..........................................................................................................490

Rozdzia 15. Zaawansowane techniki korzystania z formularzy UserForm ......... 493 Niemodalne okna dialogowe .............................................................................................493 Wy wietlanie wska nika post pu zadania .........................................................................497 Tworzenie samodzielnego wska nika post pu zadania ...............................................498 Wy wietlanie wska nika post pu zadania za pomoc formantu MultiPage ................502 Wy wietlanie wska nika post pu 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 Zale no ci programowe w kreatorach .........................................................................509 Wykonywanie zada za pomoc kreatorów ................................................................511 Emulacja funkcji MsgBox .................................................................................................511 Emulacja funkcji MsgBox: kod funkcji MyMsgBox ..................................................512 Jak dzia a funkcja MyMsgBox ....................................................................................513 Wykorzystanie funkcji MyMsgBox do emulacji funkcji MsgBox ..............................515 Formularz UserForm z formantami, których po o enie mo na zmienia ..........................515 Formularz UserForm bez paska tytu owego ......................................................................516 Symulacja paska narz dzi za pomoc formularza UserForm ............................................518

12

Spis tre ci

Formularze UserForm z mo liwo ci zmiany rozmiaru ....................................................520 Obs uga wielu przycisków formularza UserForm za pomoc jednej procedury obs ugi zdarze ...............................................................................................................524 Wybór koloru za pomoc formularza UserForm ...............................................................527 Wy wietlanie wykresów na formularzach UserForm ........................................................528 Zapisywanie wykresu w postaci pliku GIF .................................................................529 Modyfikacja w a ciwo ci 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 narz dzi dla Excela w j zyku VBA .................................. 539 Kilka s ów o narz dziach dla programu Excel ..................................................................539 Zastosowanie j zyka VBA do tworzenia narz dzi ............................................................540 Co decyduje o przydatno ci narz dzia? .............................................................................541 Operacje tekstowe: anatomia narz dzia .............................................................................541 Kilka s ów o programie Operacje tekstowe .................................................................542 Okre lenie wymaga dla narz dzia Operacje tekstowe ...............................................543 Skoroszyt narz dzia Operacje tekstowe ......................................................................543 Jak dzia a narz dzie Operacje tekstowe? .....................................................................544 Formularz UserForm dla narz dzia Operacje tekstowe ...............................................545 Modu VBA Module1 .................................................................................................546 Modu formularza UserForm1 .....................................................................................548 Poprawa wydajno ci narz dzia Operacje tekstowe .....................................................550 Zapisywanie ustawie narz dzia Operacje tekstowe ...................................................551 Implementacja procedury Cofnij .................................................................................553 Wy wietlanie pliku pomocy ........................................................................................554 Umieszczanie polece na Wst ce ..............................................................................556 Ocena realizacji projektu .............................................................................................556 Dzia anie narz dzia Operacje tekstowe .......................................................................558 Dodatkowe informacje na temat narz dzi Excela ..............................................................558

Rozdzia 17. Tabele przestawne .............................................................................. 559 Przyk ad prostej tabeli przestawnej ...................................................................................559 Tworzenie tabel przestawnych ....................................................................................560 Analiza zarejestrowanego kodu tworzenia tabeli przestawnej ....................................561 Optymalizacja wygenerowanego kodu tworz cego tabel przestawn .......................562 Tworzenie z o onych tabel przestawnych .........................................................................565 Kod tworz cy tabel przestawn .................................................................................567 Jak dzia a z o ona tabela przestawna? .........................................................................568 Jednoczesne tworzenie wielu tabel przestawnych .............................................................569 Tworzenie odwróconych tabel przestawnych ....................................................................572

Rozdzia 18. Wykresy ............................................................................................... 575 Podstawowe wiadomo ci o wykresach ..............................................................................575 Lokalizacja wykresu ....................................................................................................576 Rejestrator makr a wykresy .........................................................................................576 Model obiektu Chart ....................................................................................................577 Tworzenie wykresów osadzonych na arkuszu danych .......................................................578

Spis tre ci

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 p tli .....................................................................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 j zyka VBA do identyfikacji zakresu danych prezentowanych na wykresie ..............................................................................................................593 Wykorzystanie VBA do wy wietlania dowolnych etykiet danych na wykresie ................596 Wy wietlanie wykresu w oknie formularza UserForm ......................................................598 Zdarzenia zwi zane z wykresami ......................................................................................601 Przyk ad wykorzystania zdarze zwi zanych z wykresami ........................................601 Obs uga zdarze dla wykresów osadzonych ...............................................................604 Przyk ad: zastosowanie zdarze dla wykresów osadzonych .......................................606 Jak u atwi sobie prac z wykresami przy u yciu VBA? ..................................................608 Drukowanie wykresów osadzonych na arkuszu ..........................................................608 Ukrywanie serii danych poprzez ukrywanie kolumn ...................................................608 Tworzenie wykresów, które nie s po czone z danymi ..............................................610 Wykorzystanie zdarzenia MouseOver do wy wietlania tekstu ....................................611 Wykresy animowane .........................................................................................................614 Przewijanie wykresów .................................................................................................615 Tworzenie wykresu krzywych hipocykloidalnych ......................................................617 Tworzenie wykresu-zegara ..........................................................................................618 Tworzenie wykresu interaktywnego bez u ycia 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. Obs uga zdarze ................................................................................. 627 Co powiniene wiedzie o zdarzeniach .............................................................................627 Sekwencje zdarze ......................................................................................................628 Gdzie nale y umie ci procedury obs ugi zdarze ? ....................................................628 Wy czanie obs ugi zdarze ........................................................................................630 Wprowadzanie kodu procedury obs ugi zdarze .........................................................631 Procedury obs ugi zdarze z argumentami ..................................................................632 Zdarzenia poziomu skoroszytu ..........................................................................................634 Zdarzenie Open ...........................................................................................................634 Zdarzenie Activate ......................................................................................................636 Zdarzenie SheetActivate .............................................................................................636 Zdarzenie NewSheet ...................................................................................................636 Zdarzenie BeforeSave .................................................................................................637 Zdarzenie Deactivate ...................................................................................................637 Zdarzenie BeforePrint .................................................................................................638 Zdarzenie BeforeClose ................................................................................................639

14

Spis tre ci

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 dotycz ce wykresów .........................................................................................649 Zdarzenia dotycz ce aplikacji ...........................................................................................649 W czenie obs ugi zdarze poziomu aplikacji ............................................................651 Sprawdzanie, czy skoroszyt jest otwarty .....................................................................653 Monitorowanie zdarze poziomu aplikacji .................................................................654 Zdarzenia dotycz ce formularzy UserForm .......................................................................655 Zdarzenia niezwi zane 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 j zyka 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 Dzia ania z obiektami innych aplikacji z wykorzystaniem automatyzacji ...................670 Wczesne i pó ne wi zanie ...........................................................................................670 Funkcja GetObject a CreateObject ..............................................................................673 Prosty przyk ad pó nego wi zania ..............................................................................673 Sterowanie Wordem z poziomu Excela .......................................................................674 Zarz dzanie Excelem z poziomu innej aplikacji .........................................................677 Wysy anie spersonalizowanych wiadomo ci e-mail z wykorzystaniem Outlooka ............678 Wysy anie wiadomo ci e-mail z za cznikami 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 Mened er dodatków Excela ...............................................................................................688 Tworzenie dodatków .........................................................................................................689 Przyk ad 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 — przynale no do kolekcji z poziomu VBA ......................................696 Widoczno plików XLSM i XLAM ..........................................................................697 Arkusze i wykresy w plikach XLSM i XLAM ............................................................697 Dost p do procedur VBA w dodatku ..........................................................................698

Spis tre ci

15

Przetwarzanie dodatków za pomoc kodu VBA ................................................................701 W a ciwo ci obiektu AddIn ........................................................................................703 Korzystanie z dodatku jak ze skoroszytu ....................................................................706 Zdarzenia zwi zane z obiektami Addin .......................................................................706 Optymalizacja wydajno ci dodatków ................................................................................707 Problemy z dodatkami .......................................................................................................708 Zapewnienie, e dodatek zosta zainstalowany ...........................................................708 Odwo ywanie si do innych plików z poziomu dodatku .............................................710 Wykrywanie w a ciwej wersji Excela dla dodatku .....................................................710

Cz VI

Tworzenie aplikacji ..........................................................711

Rozdzia 22. Tworzenie pasków narz dzi ............................................................... 713 Wprowadzenie do pracy ze Wst k .................................................................................713 VBA i Wst ka ..................................................................................................................715 Dost p do polece Wst ki .........................................................................................718 Praca ze Wst k .........................................................................................................719 Aktywowanie karty .....................................................................................................721 Dostosowywanie Wst ki do w asnych potrzeb ................................................................721 Prosty przyk ad kodu RibbonX ...................................................................................722 Prosty przyk ad kodu RibbonX — podej cie 2 ...........................................................725 Kolejny przyk ad kodu RibbonX .................................................................................730 Demo formantów Wst ki ...........................................................................................732 Przyk ad u ycia formantu DynamicMenu ...................................................................738 Wi cej wskazówek dotycz cych modyfikacji Wst ki ...............................................741 Tworzenie pasków narz dzi w starym stylu ......................................................................742 Ograniczenia funkcjonalno ci tradycyjnych pasków narz dzi w Excelu 2010 ...........742 Kod tworz cy pasek narz dzi ......................................................................................743

Rozdzia 23. Praca z menu podr cznym ................................................................ 747 Obiekt CommandBar .........................................................................................................747 Rodzaje obiektów CommandBar .................................................................................748 Wy wietlanie menu podr cznych ................................................................................748 Odwo ania do elementów kolekcji CommandBars .....................................................749 Odwo ania do formantów w obiekcie CommandBar ...................................................750 W a ciwo ci formantów obiektu CommandBar ..........................................................751 Wy wietlanie wszystkich elementów menu podr cznego ...........................................752 Wykorzystanie VBA do dostosowywania menu podr cznego ..........................................754 Resetowanie menu podr cznego .................................................................................755 Wy czanie menu podr cznego ...................................................................................755 Wy czanie wybranych elementów menu podr cznego ..............................................756 Dodawanie nowego elementu do menu podr cznego Cell ..........................................756 Dodawanie nowego podmenu do menu podr cznego .................................................758 Menu podr czne i zdarzenia ..............................................................................................761 Automatyczne tworzenie i usuwanie menu podr cznego ............................................761 Wy czanie lub ukrywanie elementów menu podr cznego .........................................762 Tworzenie kontekstowych menu podr cznych ............................................................762

Rozdzia 24. Tworzenie systemów pomocy w aplikacjach .................................. 765 Systemy pomocy w aplikacjach Excela .............................................................................765 Pomoc online ...............................................................................................................766 Systemy pomocy wykorzystuj ce komponenty Excela .....................................................766 Wykorzystanie komentarzy do tworzenia systemów pomocy .....................................768 Wykorzystanie pól tekstowych do wy wietlania pomocy ...........................................769

16

Spis tre ci

Wykorzystanie arkusza do wy wietlania tekstu pomocy .............................................770 Wy wietlanie pomocy w oknie formularza UserForm ................................................771 Wy wietlanie pomocy w oknie przegl darki 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 wy wietlania 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 u ytkownika ........................... 783 Czym jest aplikacja przyjazna dla u ytkownika? ..............................................................783 Kreator amortyzacji po yczek ...........................................................................................783 Obs uga Kreatora amortyzacji po yczek .....................................................................784 Struktura skoroszytu Kreatora amortyzacji po yczek .................................................785 Jak dzia a Kreator amortyzacji po yczek? ..................................................................786 Potencjalne usprawnienia Kreatora amortyzacji po yczek ..........................................793 Wskazówki dotycz ce projektowania aplikacji .................................................................793

Cz VII Inne zagadnienia .............................................................795 Rozdzia 26. Problem kompatybilno ci aplikacji .................................................. 797 Co to jest kompatybilno ? ................................................................................................797 Rodzaje problemów ze zgodno ci ...................................................................................798 Unikaj u ywania nowych funkcji i mechanizmów ............................................................799 Czy aplikacja b dzie dzia a na komputerach Macintosh? ................................................801 Praca z 64-bitow wersj Excela .......................................................................................802 Tworzenie aplikacji dla wielu wersji narodowych ............................................................803 Aplikacje obs uguj ce wiele j zyków .........................................................................805 Obs uga j zyka w kodzie VBA ...................................................................................805 Wykorzystanie w a ciwo ci lokalnych ........................................................................806 Identyfikacja ustawie systemu ...................................................................................807 Ustawienia daty i godziny ...........................................................................................809

Rozdzia 27. Operacje na plikach wykonywane za pomoc kodu VBA ........... 811 Najcz ciej wykonywane operacje na plikach ...................................................................811 Zastosowanie polece j zyka VBA do wykonywania operacji na plikach ..................812 Zastosowanie obiektu FileSystemObject .....................................................................816 Wy wietlanie 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 Okre lanie lub ustawianie pozycji w pliku ..................................................................824 Instrukcje pozwalaj ce na odczytywanie i zapisywanie plików ..................................824 Przyk ady 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 zawarto ci pliku tekstowego ....................................................................830 Eksportowanie zakresu komórek do pliku HTML ......................................................830 Eksportowanie zakresu komórek do pliku XLM .........................................................832

Spis tre ci

17

Pakowanie i rozpakowywanie plików ...............................................................................835 Pakowanie plików do formatu ZIP ..............................................................................836 Rozpakowywanie plików ZIP .....................................................................................838 Dzia ania z obiektami danych ActiveX (ADO) .................................................................838

Rozdzia 28. Operacje na sk adnikach j zyka VBA ............................................. 841 Podstawowe informacje o rodowisku IDE .......................................................................841 Model obiektowy rodowiska IDE ....................................................................................843 Kolekcja VBProjects ...................................................................................................844 Wy wietlanie wszystkich sk adników projektu VBA ........................................................846 Wy wietlanie wszystkich procedur VBA w arkuszu .........................................................847 Zast powanie modu u uaktualnion wersj .......................................................................848 Zastosowanie j zyka 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 przyk ad formularza UserForm ........................................................................855 U yteczny (ale ju nie tak prosty) przyk ad dynamicznego formularza UserForm .....857

Rozdzia 29. Modu y klas ......................................................................................... 863 Czym jest modu klasy? .....................................................................................................863 Przyk ad: utworzenie klasy NumLock ...............................................................................864 Wstawianie modu u klasy ...........................................................................................865 Dodawanie kodu VBA do modu u klasy .....................................................................865 Wykorzystanie klasy NumLock ..................................................................................867 Dodatkowe informacje na temat modu ów klas .................................................................868 Programowanie w a ciwo ci obiektów .......................................................................868 Programowanie metod obiektów .................................................................................870 Zdarzenia definiowane w module klasy ......................................................................871 Przyk ad: klasa CSVFileClass ...........................................................................................871 Zmienne poziomu modu u dla klasy CSVFileClass ....................................................872 Definicje w a ciwo ci 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 szaro ci ....................................................................................................................880 Zamiana kolorów na skal szaro ci .............................................................................883 Wy wietlanie wykresów w skali szaro ci ....................................................................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 Wy wietlanie wszystkich kolorów motywu ................................................................890 Praca z obiektami Shape ....................................................................................................893 Kolor t a kszta tu .........................................................................................................893 Kszta ty i kolory motywów .........................................................................................895 Przyk ady kszta tów ....................................................................................................897 Modyfikacja kolorów wykresów .......................................................................................897

18

Spis tre ci

Rozdzia 31. Cz sto zadawane pytania na temat programowania w Excelu .... 901 FAQ — czyli cz sto zadawane pytania .............................................................................901 Ogólne pytania dotycz ce programu Excel .......................................................................902 Pytania dotycz ce edytora Visual Basic ............................................................................908 Pytania dotycz ce procedur ...............................................................................................911 Pytania dotycz ce funkcji ..................................................................................................916 Pytania dotycz ce obiektów, w a ciwo ci, metod i zdarze ..............................................919 Pytania dotycz ce formularzy UserForm ...........................................................................928 Pytania dotycz ce dodatków .............................................................................................932 Pytania dotycz ce pasków polece ....................................................................................934

Cz VIII Dodatki ..............................................................................937 Dodatek A

Zasoby online dotycz ce 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 Dost p do grup dyskusyjnych za pomoc czytników grup dyskusyjnych ................941 Dost p do grup dyskusyjnych za pomoc przegl darki 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 Wywo ywanie funkcji Excela w instrukcjach VBA ..........................................................950

Dodatek C

Kody b dów VBA ............................................................................... 957

Dodatek D

Zawarto p yty CD-ROM ................................................................... 961 Skorowidz ............................................................................................. 977

Rozdzia 11.

Przyk ady i techniki programowania w j zyku VBA W tym rozdziale: Zastosowanie VBA do pracy z zakresami Zastosowanie VBA do pracy ze skoroszytami i arkuszami Tworzenie w asnych funkcji i u ywanie ich w formu ach arkusza i procedurach VBA Przyk ady technik programowania w j zyku VBA Przyk ady zastosowania funkcji interfejsu API

Nauka poprzez praktyk Wierz , e nauka programowania odbywa si znacznie szybciej, kiedy pracujemy na konkretnych przyk adach omawianych zagadnie , a Czytelnicy poprzednich wyda ksi ki zdecydowanie utwierdzaj mnie w tym przekonaniu. Takie podej cie sprawdza si zw aszcza dla programistów pracuj cych z j zykiem VBA. Dobrze opracowany przyk ad o wiele lepiej obja nia zagadnienie ni teoretyczny opis. W zwi zku z tym zrezygnowa em z materia u referencyjnego, w którym dok adnie opisywano by wszystkie, nawet najdrobniejsze aspekty j zyka VBA, a zamiast tego przygotowa em przyk ady demonstruj ce u yteczne, praktyczne techniki programowania przy u yciu Excela.

Poprzednie rozdzia y tej cz ci ksi ki odpowiednio przygotowa y Czytelników do poznawania zagadnie omawianych w tym rozdziale, natomiast w systemie pomocy programu Excel znajdziesz wszystkie informacje, które tutaj zosta y pomini te. W tym rozdziale zwi kszy si nieco tempo i zaprezentowanych zostanie sporo przyk adów rozwi zuj cych problemy spotykane w praktyce i pozwalaj cych pog bi wiedz na temat j zyka VBA.

342

Cz III J zyk Visual Basic for Applications

Przyk ady omawiane w tym rozdziale zosta y podzielone na sze kategorii: Praca z zakresami Praca ze skoroszytami i arkuszami Techniki programowania w j zyku VBA U yteczne funkcje, których warto u ywa w procedurach VBA U yteczne funkcje, których mo esz u ywa w formu ach arkuszowych Wywo ania funkcji i procedur Windows API W kolejnych rozdzia ach naszej ksi ki znajdziesz szereg przyk adów procedur dotycz cych m.in. takich zagadnie , jak wykresy, tabele przestawne, zdarzenia, formularze UserForm i inne.

Przetwarzanie zakresów Przyk ady zamieszczone w tym podrozdziale demonstruj , w jaki sposób za pomoc j zyka VBA mo na manipulowa zakresami arkusza.

W szczególno ci znajdziesz tutaj przyk ady 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 warto ci do komórek przez u ytkownika, wyszukiwanie pierwszej pustej komórki w kolumnie, zatrzymywanie makra w celu umo liwienia u ytkownikowi zaznaczenia zakresu, zliczanie komórek w zakresie, przechodzenie w p tli i przetwarzanie kolejnych komórek zakresu oraz kilka innych operacji, cz sto wykonywanych na zakresach komórek arkusza.

Kopiowanie zakresów Rejestrator makr Excela jest bardzo przydatny nie tyle do generowania wydajnego, u ytecznego kodu ród owego, co do „odkrywania” nazw odpowiednich obiektów, metod i w a ciwo ci. Kod ród owy generowany przez rejestrator makr nie zawsze jest optymalny i efektywny, ale zwykle pozwala uzyska sporo przydatnych informacji.

Przyk adowo po zarejestrowaniu prostej operacji kopiowania i wklejania, generowanych jest pi wierszy kodu ród owego j zyka 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 nast pnie, po zaznaczeniu komórki B1, procedura wykonuje operacj wklejania. Jednak w j zyku VBA nie jest konieczne zaznaczanie obiektu, który b dzie przetwarzany. O tej istotnej sprawie

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

343

Jak korzysta z przyk adów zamieszczonych w tym rozdziale?

Nie wszystkie przyk ady zamieszczone w tym rozdziale mog spe nia rol samodzielnych programów, ale zawsze maj posta wykonywalnych procedur, które mo esz dostosowa do w asnych potrzeb i u y w swoich aplikacjach.

W trakcie lektury powiniene na bie co pracowa z komputerem i samodzielnie testowa opisywane w tym rozdziale przyk ady (i nie tylko). Jeszcze lepiej b dzie, je eli b dziesz próbowa samodzielnie modyfikowa przyk ady i sprawdza , jaki b dzie efekt tych modyfikacji. Mog Ci zagwarantowa , e takie praktyczne do wiadczenia b d o wiele bardziej pomocne ni przeczytanie od deski do deski ksi ki zawieraj cej tylko teoretyczn stron programowania w j zyku VBA.

nie dowiedzia by si nigdy, gdyby wzorowa si tylko na kodzie ród owym zarejestrowanego makra, w którym w dwóch instrukcjach zosta a u yta metoda Select. Zamiast tego, mo esz pos u y si znacznie prostsz procedur , która nie zaznacza adnych komórek i korzysta z tego, e metoda Copy mo e u y argumentu reprezentuj cego miejsce docelowe kopiowanego zakresu.

Sub CopyRange() Range("A1").Copy Range("B1") End Sub

W obu powy szych makrach przyj to za o enie, e arkusz, w którym wykonywana jest operacja, jest aktywny. Aby skopiowa zakres do innego arkusza lub skoroszytu, wystarczy odpowiednio zdefiniowa odwo anie do zakresu docelowego. W poni szym przyk adzie zakres jest kopiowany z arkusza Arkusz1 skoroszytu Plik1.xlsm do arkusza Arkusz2 skoroszytu Plik2.xlsm. Poniewa odwo ania s w pe ni kwalifikowane, procedura zadzia a niezale nie od tego, który skoroszyt b dzie 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 reprezentuj cych zakresy, tak jak to zosta o zilustrowane w kodzie poni szego przyk adu: 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 domy lasz, kopiowanie nie jest ograniczone tylko do jednej komórki na raz. Przyk adowo, procedura przedstawiona poni ej kopiuje du y 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. U ycie jednej komórki dzia a dok adnie tak, jak podczas r cznego kopiowania i wklejania komórek arkusza.

Sub CopyRange4() Range("A1:C800").Copy Range("D1") End Sub

344

Cz III J zyk Visual Basic for Applications

Przenoszenie zakresów Instrukcje j zyka VBA s u ce do przenoszenia zakresu s bardzo podobne do instrukcji u ywanych podczas kopiowania zakresów, tak jak to zosta o zaprezentowane na poni szym przyk adzie. Ró nica polega na tym, e zamiast metody Copy u yta zosta a metoda Cut. Pami taj, e musisz poda tylko lokalizacj górnej, lewej komórki zakresu docelowego.

W przyk adzie przedstawionym poni ej 18 komórek (z zakresu A1:C6) przenosimy w nowy obszar, rozpoczynaj cy si od adresu H1.

Sub MoveRange1() Range("A1:C6").Cut Range("H1") End Sub

Kopiowanie zakresu o zmiennej wielko ci W wielu przypadkach konieczne jest skopiowanie zakresu komórek, dla którego dok adna liczba wierszy i kolumn okre laj cych jego wielko nie jest z góry znana. Przyk adowo mo esz dysponowa skoroszytem ledz cym tygodniow sprzeda , w którym liczba wierszy zmienia si ka dego tygodnia po wprowadzeniu nowych danych.

Na rysunku 11.1 pokazano bardzo cz sto spotykany typ arkusza. Zawarty w nim zakres sk ada si z kilku wierszy, których liczba zmienia si ka dego tygodnia. Poniewa nie wiesz, jaki jest adres zakresu w danej chwili, podczas pisania makra kopiuj cego zakres b dziesz uwzgl dni nieco dodatkowego kodu ród owego.

Rysunek 11.1.

Liczba wierszy zakresu danych zmienia si ka dego tygodnia

Poni sze makro ilustruje sposób kopiowania zakresu komórek z arkusza Arkusz1 do arkusza Arkusz2 (pocz wszy od komórki A1). Makro wykorzystuje w a ciwo CurrentRegion, która zwraca obiekt Range odpowiadaj cy blokowi komórek otaczaj cych okre lon komórk (w tym przypadku o adresie A1).

Sub CopyCurrentRegion2() Range("A1").CurrentRegion.Copy Sheets("Arkusz2").Range("A1") End Sub

Zastosowanie w a ciwo ci CurrentRegion jest równowa ne przej ciu na kart Narz dzia g ówne i wybraniu polecenia Znajd i zaznacz/Przejd do — specjalnie, znajduj cego si w grupie opcji Edycja i nast pnie zaznaczeniu opcji Bie cy obszar (zamiast tego mo esz równie nacisn kombinacj klawiszy Ctrl+Shift+*). Aby przekona si , jak to dzia a, podczas wykonywania tych polece powiniene zarejestrowa makro. Zazwyczaj warto w a ciwo ci CurrentRegion reprezentuje prostok tny blok komórek otoczony przez puste wiersze i kolumny.

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

345

Wskazówki dotycz ce przetwarzania zakresów W trakcie przetwarzania zakresów powiniene pami ta o kilku wa nych kwestiach.

W j zyku VBA, do przetwarzania zakresu nie jest konieczne jego uprzednie zanaczenie.

Nie mo esz zaznaczy zakresu, który znajduje si na nieaktywnym arkuszu, zatem, je eli Twoja procedura zaznacza zakres, powi zany z nim arkusz musi by aktywny. W celu uaktywnienia okre lonego arkusza mo na u y metody Activate kolekcji Worksheets.

Pami taj, e rejestrator makr nie generuje zbyt wydajnego kodu ród owego. Najlepiej utworzy makro przy u yciu rejestratora, a nast pnie jego kod ród owy zmodyfikowa w celu zwi kszenia efektywno ci.

W kodzie ród owym j zyka VBA warto stosowa nazwane zakresy. Przyk adowo odwo anie Range("Total") jest znacznie bardziej czytelne ni odwo anie Range("D45"). W tym drugim przypadku dodanie wiersza powy ej wiersza 45. spowoduje zmian adresu komórki i w konsekwencji konieczne b dzie zmodyfikowanie makra tak, aby u ywa o zakresu o poprawnym adresie (D46).

Je eli w trakcie zaznaczania zakresów korzystasz z rejestratora makr, upewnij si , e makro rejestrowane jest przy u yciu odwo a wzgl dnych. Aby to zrobi , przejd na kart Deweloper i naci nij przycisk U yj odwo a wzgl dnych, znajduj cy si w grupie opcji Kod. Po uruchomieniu makra przetwarzaj cego kolejne komórki aktualnie zaznaczonego zakresu, u ytkownik mo e zaznacza ca e wiersze lub kolumny. W wi kszo ci przypadków nie ma potrzeby przetwarzania wszystkich komórek zaznaczonego zakresu. Tworzone makro powinno definiowa podzbiór zaznaczenia zawieraj cy wy cznie niepuste komórki. Wi cej szczegó owych informacji na ten temat znajdziesz w podrozdziale „Wydajne przetwarzanie komórek zaznaczonego zakresu przy u yciu p tli” w dalszej cz ci rozdzia u.

Excel pozwala zaznacza wiele obszarów jednocze nie. Na przyk ad mo esz zaznaczy pierwszy zakres, wcisn klawisz Ctrl i zaznaczy kolejny zakres. Makro powinno dokona sprawdzenia zakresu i podj odpowiedni decyzj . Zapoznaj si z zawarto ci punktu „Okre lanie typu zaznaczonego zakresu” w dalszej cz ci rozdzia u.

Je eli zakres komórek, który chcesz skopiowa jest tabel (zdefiniowan przy u ycia polecenia Tabela, znajduj cego si nakrcie Narz dzia g ówne, w grupie polece Tabele), mo esz u y kodu przedstawionego poni ej (który zak ada, 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 Wi kszo operacji wykonywanych przez instrukcje j zyka VBA opiera si na zakresach — poprzez definiowanie zakresów lub identyfikowanie zakresów w celu wykonywania operacji na komórkach do nich nale cych.

Oprócz w a ciwo ci CurrentRegion (o której mówili my ju wcze niej) powiniene równie pozna metod End obiektu Range. Metoda ta pobiera jeden argument okre laj cy kierunek, w którym zostanie wykonane zaznaczenie. Poni sze polecenie zaznacza zakres rozpoczynaj cy si od aktywnej komórki i ko cz cy na ostatniej niepustej dolnej komórce: Range(ActiveCell, ActiveCell.End(xlDown)).Select

346

Cz III ย J zyk Visual Basic for Applications

Poni ej zamieszczamy kolejny przyk ad, w ktรณrym zosta a zdefiniowana komรณrka b d ca pocz tkiem zakresu: Range(Range("A2"), Range("A2").End(xlDown)).Select

Jak mo na si domy li , trzy pozosta e sta e (xlUp, xlToLeft, xlToRight) symuluj kombinacje klawiszy zaznaczaj ce komรณrki w innych kierunkach.

Korzystaj c z w a ciwo ci ActiveCell w powi zaniu z metod End, powiniene zachowa szczegรณln ostro no . Je eli aktywna komรณrka znajduje si na ko cu zakresu lub je eli w sk ad zakresu wchodzi jedna lub wi cej pustych komรณrek, wyniki dzia ania metody End mog by zupe nie inne od oczekiwanych.

Na do czonym dysku CD-ROM znajduje si skoroszyt (Zaznaczanie zakresรณw.xlsm) ilustruj cy najcz ciej spotykane rodzaje zaznacze zakresรณw. Po jego otwarciu, w menu podr cznym pojawi si nowe podmenu o nazwie Przyk ady zaznacze . Poszczegรณlne polecenia menu umo liwiaj u ytkownikowi zapoznanie si z przyk adami rรณ nych rodzajรณw zaznacze (patrz rysunek 11.2).

Poni sze makro, o nazwie SelectCurrentRegion znajduje si w przyk adowym skoroszycie i symuluje naci ni cie kombinacji klawiszy Ctrl+Shift+*: Sub SelectCurrentRegion() ActiveCell.CurrentRegion.Select End Sub

Bardzo cz sto zaznaczanie komรณrek jest tylko wst pem do innych operacji, na przyk ad formatowania. Procedur zaznaczaj c komรณrki mo na atwo przystosowa do tego celu.

Procedura przedstawiona poni ej jest prost modyfikacj makra SelectCurrentRegion, ktรณra nie zaznacza komรณrek, a jedynie formatuje zakres zdefiniowany jako bie cy obszar otaczaj cy aktywn komรณrk . Inne procedury znajduj ce si w przyk adowym skoroszycie te mog zosta przystosowane w ten sposรณb.

Sub FormatCurrentRegion() ActiveCell.CurrentRegion.Font.Bold = True End Sub

Wprowadzanie warto ci do komรณrki Kolejna procedura przedstawiona poni ej demonstruje, jak poprosi u ytkownika o podanie warto ci i wstawi j do komรณrki A1 aktywnego arkusza: Sub GetValue1() Range("A1").Value = InputBox("Wprowad warto :") End Sub

Na rysunku 11.3 pokazano wygl d okna umo liwiaj cego wprowadzenie warto ci.

Przedstawiona procedura mo e sprawia jednak pewien problem. Je eli u ytkownik naci nie w oknie dialogowym przycisk Cancel, procedura usunie wszelkie dane ju znajduj ce si w komรณrce. Poni sza zmodyfikowana wersja procedury sprawdza, czy zosta naci ni ty przycisk Cancel i je eli tak, nie dokonuje zmiany zawarto ci komรณrki:

Rozdzia 11. ย Przyk ady i techniki programowania w j zyku VBA

347

Rysunek 11.2. Niestandardowe menu podr czne w tym skoroszycie ilustruje zaznaczanie zakresรณw o rรณ nej wielko ci Rysunek 11.3.

Funkcja InputBox pobiera warto , ktรณra zostanie umieszczona w komรณrce

Sub GetValue2() Dim UserEntry As String UserEntry = InputBox("Wprowad warto :") If UserEntry <> "" Then Range("A1").Value = UserEntry End Sub

348

Cz III ย J zyk Visual Basic for Applications

W wielu przypadkach konieczne b dzie sprawdzenie poprawno ci danych wprowadzonych przez u ytkownika. Na przyk ad chcesz, aby u ytkownik wprowadzi liczb z zakresu od 1 do 12. W poni szym przyk adzie zademonstrowano jedn z metod sprawdzenia poprawno ci danych. Niepoprawna warto jest ignorowana, a okno wy wietlane ponownie. Operacja jest powtarzana do momentu wprowadzenia prawid owej warto ci lub naci ni cia 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

Je eli u ytkownik wprowadzi niepoprawn warto , program odpowiednio zmieni tre wy wietlanego komunikatu (patrz rysunek 11.4).

Rysunek 11.4.

Sprawdzenie poprawno ci danych wprowadzonych przez u ytkownika przy u yciu funkcji InputBox j zyka VBA Skoroszyt zawieraj cy wszystkie trzy przyk ady (Funkcja InputBox.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Wprowadzanie warto ci do nast pnej pustej komรณrki Cz sto wymagan operacj jest wprowadzenie warto ci do nast pnej pustej komรณrki kolumny lub wiersza. Poni sza procedura prosi u ytkownika o podanie imienia i warto ci, a nast pnie wprowadza dane do nast pnego pustego wiersza (patrz rysunek 11.5).

Sub GetData() Dim NextRow As Long Dim Entry1 As String, Entry2 As String Do 'Odszukaj nast pny pusty wiersz NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

349

Rysunek 11.5.

Makro wstawiaj ce dane do nast pnego 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 poprawno ci wprowadzanych danych. Zwró uwag , e w procedurze nie zosta okre lony warunek zako czenia p tli. W celu jej opuszczenia u yto instrukcji Exit Sub, która jest wykonywana po naci ni ciu przycisku Cancel w oknie wprowadzania danych.

Skoroszyt zawieraj cy oba przyk ady (Nast pnaPustaKomórka.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Zwró uwag na instrukcj okre laj c warto zmiennej NextRow. Je eli nie rozumiesz, w jaki sposób procedura dzia a, spróbuj r cznie wykona realizowan przez ni operacj : uaktywnij ostatni komórk w kolumnie A (co w przypadku Excela 2010 oznacza komórk o adresie A1048576), naci nij klawisz End i nast pnie naci nij klawisz (strza ka w gór ).

W efekcie zostanie zaznaczona ostatnia niepusta komórka kolumny A. W a ciwo Row zwraca numer wiersza tej komórki. W celu uzyskania numeru kolejnego pustego wiersza (poni ej) warto ta jest zwi kszana o jeden. Zamiast umieszcza na „sztywno” adres ostatniej komórki wiersza A, u yta zosta a metoda Rows.Count, dzi ki czemu nasza procedura b dzie poprawnie dzia a a równie z poprzednimi wersjami programu Excel (w których maksymalna liczba wierszy w arkuszu jest du o mniejsza).

Z tak metod zaznaczania nast pnej pustej komórki zwi zany jest drobny problem. Je eli kolumna jest zupe nie pusta, jako nast pny pusty wiersz metoda wyznaczy wiersz 2. Na szcz cie dodanie odpowiedniego kodu, który b dzie zapobiega takiemu zachowaniu, nie jest trudnym zadaniem.

350

Cz III J zyk Visual Basic for Applications

Wstrzymywanie dzia ania makra w celu umo liwienia pobrania zakresu wyznaczonego przez u ytkownika Zdarzaj si sytuacje, w których makro musi by w pewien sposób interaktywne. Na przyk ad mo esz utworzy makro, które wstrzymuje dzia anie, pozwalaj c u ytkownikowi na zaznaczenie wybranego zakresu komórek. Procedura opisana w tym punkcie demonstruje sposób wykonania zadania przy u yciu metody InputBox Excela.

Nie nale y myli metody InputBox Excela z funkcj j zyka VBA o takiej samej nazwie.

Co prawda obie funkcje maj tak sam nazw , ale nie s takie same.

Poni sza procedura Sub demonstruje sposób zatrzymania pracy makra i umo liwienia u ytkownikowi zaznaczenia zakresu komórek. Po wznowieniu dzia ania 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" '

Wy wietlanie 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 zosta o anulowane?

If UserRange Is Nothing Then MsgBox "Operacja anulowana."

Else UserRange.Formula = "=RAND()" End If End Sub

Okno dialogowe zosta o przedstawione na rysunku 11.6.

Skoroszyt z tym przyk adem (Zaznacz zakres.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Podanie argumentu Type o warto ci 8 ma kluczowe znaczenie dla powy szej procedury.

Oprócz tego powiniene równie zwróci uwag na zastosowanie instrukcji On Error Resume Next. To polecenie powoduje, e b d, który wyst pi gdy u ytkownik naci nie przycisk Anuluj, b dzie ignorowany. Je eli tak si stanie, nie zostanie zdefiniowana zmienna obiektowa UserRange. W powy szym przyk adzie jest wy wietlane okno zawieraj ce komunikat o tre ci Operacja anulowana. Gdy u ytkownik naci nie przycisk OK, wykonywanie makra b dzie kontynuowane. Instrukcja On Error GoTo 0 przywraca standardow obs ug b dów.

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

351

Rysunek 11.6. Okno dialogowe u yte do wstrzymania dzia ania makra

Nawiasem mówi c, sprawdzanie poprawno ci zaznaczonego zakresu nie jest konieczne, poniewa zajmie si tym Excel.

Pami taj, podczas u ywania metody InputBox do zaznaczania zakresu komórek od wie anie ekranu powinno by zawsze w czone. W przeciwnym wypadku nie b dziesz w stanie zaznaczy zakresu komórek. Do sterowania od wie aniem ekranu w trakcie wykonywania makra powiniene u y w a ciwo ci ScreenUpdating obiektu Application.

Zliczanie zaznaczonych komórek Mo na stworzy makro przetwarzaj ce zaznaczone komórki zakresu. Aby okre li liczb komórek w zaznaczonym zakresie (lub dowolnym innym), nale y u y w a ciwo ci Count obiektu Range. Przyk adowo poni sza instrukcja wy wietla w oknie komunikatu liczb aktualnie zaznaczonych komórek: MsgBox Selection.Count

Je eli aktywny arkusz zawiera zakres o nazwie dane, polecenie przedstawione poni ej przypisze liczb jego komórek zmiennej CellCount: CellCount = Range("dane").Count

Mo esz równie okre li liczb wierszy lub kolumn w zakresie. Poni sze wyra enie wyznacza liczb kolumn znajduj cych si w aktualnie zaznaczonym zakresie: Selection.Columns.Count

352

Cz III J zyk Visual Basic for Applications

Ze wzgl du na fakt, e w najnowszych wersjach Excela (2007 i 2010) maksymalne rozmiary arkusza zosta y znacz co powi kszone, w a ciwo Count mo e w pewnych sytuacjach generowa b d. W a ciwo Count wykorzystuje dane typu Long, zatem najwi ksza warto , jak ta w a ciwo mo e przechowywa , to 2 147 483 647, st d je eli u ytkownik zaznaczy na przyk ad 2048 pe nych kolumn (czyli 2 147 483 648 komórek), w a ciwo Count wygeneruje b d. Na szcz cie firma Microsoft doda a w Excelu, pocz wszy od wersji 2007, now w a ciwo : CountLarge. W a ciwo ta u ywa danych typu Double, które pozwalaj na przechowywanie liczb z zakresu do 1,79+E308.

Wnioski? W przyt aczaj cej wi kszo ci przypadków w a ciwo Count b dzie dzia a a najzupe niej poprawnie. Je eli jednak zak adasz, e b dziesz zlicza arkusze zawieraj ce naprawd du e ilo ci komórek (na przyk ad wszystkie komórki arkusza), to zamiast w a ciwo ci Count powiniene u y w a ciwo ci CountLarge.

Oczywi cie liczb wierszy zakresu mo na równie okre li przy u yciu w a ciwo ci Rows. Poni sza instrukcja okre la liczb wierszy zakresu o nazwie dane i przypisuje warto zmiennej RowCount: RowCount = Range("dane").Rows.Count

Okre lanie typu zaznaczonego zakresu Excel obs uguje kilka typów zaznacze zakresów. Oto one: pojedyncza komórka, ci g y zakres komórek, jedna lub wi cej kolumn, jeden lub wi cej wierszy, ca y arkusz, dowolna kombinacja wy ej wymienionych typów, czyli zaznaczenie wielokrotne.

Poniewa istnieje kilka typów zaznacze , w trakcie przetwarzania zakresu procedura j zyka VBA nie mo e przewidzie , jaki jest typ zaznaczenia. Na przyk ad zaznaczony obszar mo e sk ada si z dwóch zakresów komórek, A1:A10 i C1:C10 (aby utworzy zaznaczenie wielokrotne, podczas zaznaczania kolejnych zakresów trzymaj wci ni ty klawisz Ctrl).

Je eli zakres zosta zdefiniowany przez wiele zaznacze , obiekt Range b dzie si sk ada z oddzielnych obszarów. Aby stwierdzi , czy zaznaczenie jest zaznaczeniem wielokrotnym, nale y u y metody Areas zwracaj cej kolekcj Areas. Kolekcja reprezentuje wszystkie obszary wchodz ce w sk ad zakresu stworzonego poprzez zaznaczenie wielokrotne.

W celu stwierdzenia, czy wybrany zakres posiada wiele obszarów, nale y zastosowa wyra enie podobne do poni szego: NumAreas = Selection.Areas.Count

Je eli zmienna NumAreas zawiera warto wi ksz od 1, zaznaczenie jest zaznaczeniem wielokrotnym.

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

353

Poni ej zamieszczono kod funkcji o nazwie AreaType, która zwraca a cuch tekstu opisuj cy rodzaj zaznaczenia.

Function AreaType(RangeArea As Range) As String ' Funkcja okre la 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 pi ciu a cuchów opisuj cych obszar — Komórka, Arkusz, Kolumna, Wiersz lub Blok. W celu okre lenia, które z pi ciu wyra e porównuj cych ma warto True, funkcja korzysta z konstrukcji Select Case.

Na przyk ad: je eli zakres sk ada si z jednej komórki, funkcja zwróci a cuch Komórka.

Je eli liczba komórek zakresu jest równa liczbie komórek arkusza, funkcja zwróci a cuch Arkusz. Je eli liczba wierszy zakresu jest równa liczbie wierszy arkusza, funkcja zwróci a cuch Kolumna. Je eli liczba kolumn zakresu jest równa liczbie kolumn arkusza, funkcja zwróci a cuch Wiersz. Je eli adne wyra enie instrukcji Case nie b dzie mia o warto ci True, funkcja zwróci a cuch Blok.

Zauwa , e do liczenia komórek u yta zosta a w a ciwo CountLarge, poniewa — jak ju wspominali my wcze niej — ca kowita liczba zaznaczonych komórek w arkuszu mo e teoretycznie przekroczy limit typu danych w a ciwo ci Count.

Skoroszyt Zaznaczanie.xlsm znajduj cy si na do czonym dysku CD-ROM zawiera procedur RangeDescription, która pos uguje si funkcj AreaType w celu wy wietlenia okna komunikatu opisuj cego typ zaznaczenia aktualnego zakresu. Na rysunku 11.7 pokazano przyk ad jej dzia ania. Zrozumienie zasad dzia ania funkcji b dzie stanowi o dobre przygotowanie do wykonywania kolejnych operacji na obiektach klasy Range.

Excel pozwala na wykonanie wielu identycznych zaznacze . Na przyk ad, je eli trzymaj c wci ni ty klawisz Ctrl, pi ciokrotnie klikniesz komórk A1, zaznaczenie b dzie z o one z pi ciu identycznych obszarów. Procedura RangeDescription uwzgl dnia taka sytuacj i nie zlicza wielokrotnie tych samych komórek.

Wydajne przetwarzanie komórek zaznaczonego zakresu przy u yciu p tli Jednym z cz ciej wykonywanych przez makra zada jest sprawdzanie poszczególnych komórek zakresu i wykonywanie okre lonych operacji, je eli komórka spe nia zadane kryterium. Kolejna procedura, której kod przedstawiamy poni ej, jest w a nie przyk adem takiego makra. Procedura ColorNegaitve ustawia czerwony kolor t a dla wszystkich komórek zaznaczenia, które przechowuj warto ujemn . Kolor t a pozosta ych komórek jest zerowany.

354

Cz III J zyk Visual Basic for Applications

Rysunek 11.7. Procedura AboutRangeSelection analizuje aktualnie zaznaczony zakres Poni szy przyk ad zosta opracowany tylko i wy cznie w celach edukacyjnych.

W zastosowaniach praktycznych o wiele lepszym rozwi zaniem b dzie po prostu u ycie mechanizmu formatowania warunkowego.

Sub ColorNegative() ' Je eli warto jest ujemna, zmienia kolor t a 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 pewno ci procedura ColorNegative zadzia a, ale zawiera powa ny b d. Dla przyk adu: co si stanie, je eli obszar danych na arkuszu jest bardzo ma y, a u ytkownik zaznaczy na przyk ad ca kolumn ? Albo 10 kolumn? Albo mo e nawet ca y arkusz? Jak si zapewne sam domy lasz, nie ma adnej potrzeby sprawdzania wszystkich pustych, nieu ywanych komórek arkusza, nie mówi c ju o tym, e przy du ych zaznaczonych obszarach u ytkownik z pewno ci podda by si , zanim ca a procedura dobieg aby do ko ca.

Lepszym rozwi zaniem jest procedura ColorNegative2, której kod przedstawiamy poni ej.

W tej poprawionej wersji utworzyli my zmienn obiektow WorkRange, której zawarto odpowiada cz ci wspólnej zaznaczonego obszaru i u ytego obszaru arkusza. Przyk adow sytuacj ilustruje rysunek 11.8. Jak wida , zaznaczona jest ca a kolumna D (czyli 1 048 576 komórek), ale u yty zakres komórek sprowadza si ju tylko do obszaru B2:I18.

Zatem przeci ciem tych dwóch obszarów jest zakres D2:D18, co jest zdecydowanie mniejszym zakresem ni pocz tkowe zaznaczenie. Ró nica pomi dzy czasem przetwarzania 15 komórek a 1 048 576 komórek b dzie naprawd znacz ca.

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

355

Rysunek 11.8.

Zastosowanie przeci cia zaznaczonego zakresu i u ytego obszaru arkusza skutkuje znacz cym zmniejszeniem liczby komórek, które b dziemy musieli przetwarza

Sub ColorNegative2() ' Je eli warto jest ujemna, zmienia kolor t a 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 d u sza, ale jednocze nie o wiele bardziej wydajna. W celu wygenerowania dwóch podzbiorów zaznaczenia u y em metody SpecialCells. Pierwszy podzbiór obejmuje jedynie komórki zawieraj ce sta e numeryczne (ConstantCells), natomiast drugi — komórki przechowuj ce formu y numeryczne (FormulaCells). Komórki obu podzbiorów s nast pnie przetwarzane za pomoc dwóch konstrukcji For Each ... Next. W efekcie przetwarzane s tylko niepuste komórki zawieraj ce warto ci numeryczne, dzi ki czemu uzyskujemy znacz ce zwi kszenie szybko ci dzia ania makra.

Sub ColorNegative3() ' Je eli warto jest ujemna, zmienia kolor t a komórki na czerwony Dim FormulaCells As Range, ConstantCells As Range Dim cell As Range If TypeName(Selection) <> "Range" Then Exit Sub

356

Cz III ย J zyk 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 zawieraj cych formu y 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 zawieraj cych sta e warto ci 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 b d, gdy adna komรณrka nie spe nia kryterium.

Skoroszyt z tymi przyk adami (Tworzenie wydajnych p tli.xlsm), zawieraj cy trzy wersje procedury ColorNegative, znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Usuwanie wszystkich pustych wierszy Poni sza procedura usuwa puste wiersze aktywnego arkusza. Procedura jest szybka i wydajna, poniewa nie sprawdza wszystkich wierszy, a jedynie wiersze u ywane przez zakres, ktรณry jest identyfikowany za pomoc w a ciwo ci UsedRange obiektu Worksheet.

Sub DeleteEmptyRows() Dim LastRow As Long Dim r As Long Dim Counter As Long Application.ScreenUpdating = False LastRow = ActiveSheet.UsedRange.Rows.Count + _ ActiveSheet.UsedRange.Rows(1).Row - 1 For r = LastRow To 1 Step -1 If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then Rows(r).Delete Counter = Counter + 1 End If Next r

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

357

Application.ScreenUpdating = True MsgBox Counter & " pustych wierszy zosta o usuni tych."

End Sub

Pierwszym krokiem jest okre lenie ostatnio u ywanego wiersza, a nast pnie przypisanie jego numeru zmiennej LastRow. Nie jest to takie proste, jak mog oby si wydawa , poniewa u ywany zakres mo e, ale nie musi rozpoczyna si od wiersza 1. A zatem warto zmiennej LastRow jest obliczana poprzez okre lenie liczby wierszy u ywanego zakresu, dodanie numeru pierwszego wiersza zakresu i odj cie jedynki.

W celu stwierdzenia, czy wiersz jest pusty, procedura korzysta z funkcji arkuszowej COUNTA (ILE.NIEPUSTYCH) Excela. Je eli dla okre lonego wiersza funkcja zwróci warto 0, oznacza to, e jest pusty. Procedura przetwarza wiersze od do u do góry, a ponadto w p tli For ...

Next u ywa ujemnej warto ci skoku (Step). Jest to niezb dne, poniewa operacja usuwania wierszy powoduje, e wszystkie kolejne wiersze s przesuwane w gór arkusza.

Je eli p tla przetwarza aby wiersze od góry do do u, jej licznik po usuni ciu wiersza nie mia by w a ciwej warto ci.

Makro wykorzystuje równie inn zmienn , Counter, do ledzenia liczby usuni tych wierszy. Liczba ta jest wy wietlana w oknie dialogowym, kiedy procedura ko czy dzia anie.

Skoroszyt z tym przyk adem (Usu puste wiersze.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Powielanie wierszy Przyk ad, który omówimy w tym podrozdziale, ilustruje sposób wykorzystania VBA do tworzenia duplikatów istniej cych wierszy. Na rysunku 11.9 przedstawiono wygl d skoroszytu zawieraj cego informacje o loterii biurowej. Kolumna A przechowuje imiona graczy, kolumna B liczb losów zakupionych przez poszczególne osoby, a w kolumnie C znajduje si liczba losowa (wygenerowana przy u yciu funkcji RAND). Zwyci zca zostanie wy oniony przez sortowanie danych w kolumnie C (wygrywa osoba, do której zosta a przypisana najwi ksza liczba losowa).

Rysunek 11.9.

Zadanie polega na powieleniu istniej cych wierszy w oparciu o warto ci z kolumny B

358

Cz III J zyk Visual Basic for Applications

Nasze zadanie polega na powieleniu istniej cych wierszy dla poszczególnych osób, tak aby ka da z nich mia a tyle osobnych wierszy, ile zakupi a losów. Na przyk ad Barbara kupi a 2 losy, a zatem powinni my dla niej utworzy 2 osobne wiersze. Kod procedury realizuj cej takie zadanie zosta zamieszczony poni ej: 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 reprezentuj ca komórk B2, czyli pierwsz komórk , w której przechowywana jest liczba zakupionych losów. P tla wstawia nowy wiersz i nast pnie kopiuje go odpowiedni ilo razy przy u yciu metody FillDown. Zmienna cell jest inkrementowana tak, aby wskazywa a na liczb losów zakupionych przez kolejn osob i procedura kontynuuje dzia anie a do momentu napotkania pierwszej pustej komórki. Na rysunku 11.10 przedstawiono wygl d arkusza po zako czeniu dzia ania procedury.

Skoroszyt z tym przyk adem (Powielanie wierszy.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Okre lanie, czy zakres zawiera si w innym zakresie Poni sza funkcja InRange pobiera dwa argumenty (obiekty klasy Range) i zwraca warto True, je eli pierwszy zakres zawiera si w drugim: Function InRange(rng1, rng2) As Boolean ' Zwraca warto True, je eli 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 mo e si wydawa do z o ony, poniewa program musi sprawdzi , czy dwa zakresy znajduj si w tym samym arkuszu i skoroszycie. Procedura pos uguje si w a ciwo ci Parent zwracaj c kontener obiektu. Przyk adowo poni sze wyra enie zwraca nazw arkusza b d cego kontenerem obiektu rng1, do którego jest wykonywane odwo anie: rng1.Parent.Name

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

359

Rysunek 11.10.

Procedura doda a do arkusza nowe wiersze w oparciu o warto ci w kolumnie B

Kolejne wyra enie zwraca nazw skoroszytu obiektu rng1: rng1.Parent.Parent.Name

Funkcja Union j zyka VBA zwraca obiekt klasy Range reprezentuj cy sum dwóch obiektów Range. Zakres sumy obejmuje wspólne komórki dwóch zakresów. Je eli 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 przyk adem (Funkcja InRange.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Okre lanie typu danych zawartych w komórce Excel oferuje kilka wbudowanych funkcji arkuszowych, które pomagaj w okre laniu typu danych zawartych w komórce. Nale y do nich zaliczy funkcje CZY.TEKST, CZY.LOGICZNA i CZY.B D. Dodatkowo j zyk VBA zawiera funkcje IsEmpty, IsDate i IsNumeric.

360

Cz III J zyk Visual Basic for Applications

Zamieszczona poni ej funkcja CellType akceptuje argument Range i zwraca a cuch (Pusta, Tekst, Logiczny, B d, Data, Czas lub Liczba) opisuj cy typ danych zawartych w górnej lewej komórce zakresu. Funkcja mo e zosta u yta w formule arkusza lub wywo ana z innej procedury j zyka 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 = "B d" 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 u ycie polecenia Set TheCell. Funkcja CellType akceptuje argument Range dowolnej wielko ci, ale ta instrukcja powoduje, e funkcja przetwarza tylko górn lew komórk zakresu reprezentowanego przez zmienn TheCell.

Skoroszyt z tym przyk adem (Funkcja CellType.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

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 szybciej odczytuje dane z zakresu, ni je w nim zapisuje. Procedura WriteReadRange, której kod przedstawiamy poni ej, demonstruje porównanie wzgl dnych szybko ci wykonywania operacji zapisu i odczytu zakresu.

Procedura tworzy tablic , a nast pnie za pomoc p tli For ... Next zapisuje jej zawarto w zakresie i ponownie wczytuje j do tablicy. Przy u yciu funkcji Timer j zyka VBA oblicza czas wymagany do wykonania ka dej operacji.

Sub WriteReadRange() Dim MyArray() Dim Time1 As Double Dim NumElements As Long, i As Long Dim WriteTime As String, ReadTime As String Dim Msg As String

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

361

NumElements = 60000 ReDim MyArray(1 To NumElements) '

Wype nienie 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 za adowanie do tablicy Time1 = Timer For i = 1 To NumElements MyArray(i) = Cells(i, 1) Next i ReadTime = Format(Timer - Time1, "00:00")

'

Wy wietlenie 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 licz cej 60 000 elementów do zakresu komórek zaj o 58 sekund, natomiast wczytanie zakresu komórek do tablicy tylko 1 sekund .

Lepsza metoda zapisywania zakresu W poprzednim przyk adzie, aby przenie zawarto tablicy do zakresu arkusza, u yto p tli For ... Next. W tym podrozdziale zademonstrujemy wydajniejsz metod osi gni cia tego samego celu.

Kod procedury przedstawiony poni ej ilustruje najbardziej oczywisty (ale niestety nie najwydajniejszy) sposób wype niania zakresu danymi. Do umieszczenia danych w zakresie ponownie zosta a u yta p tla For ... Next.

Sub LoopFillRange() ' Wype nia zakres przy u yciu p tli przetwarzaj cej komórki Dim CellsDown As Long, CellsAcross As Integer Dim CurrRow As Long, CurrCol As Integer Dim StartTime As Double Dim CurrVal As Long '

Pobranie wymiarów CellsDown = Val(InputBox("Ile komórek w pionie?"))

If CellsDown = 0 Then Exit Sub CellsAcross = Val(InputBox("Ile komórek w poziomie?"))

If CellsAcross = 0 Then Exit Sub

362

Cz III J zyk Visual Basic for Applications

'

Zarejestrowanie czasu rozpocz cia StartTime = Timer

'

Przy u yciu p tli przetwarza komórki i wstawia warto ci 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

'

Wy wietla czas trwania operacji Application.ScreenUpdating = True MsgBox Format(Timer - StartTime, "00.00") & " sekund" End Sub

Kolejny przyk ad demonstruje o wiele szybsz metod pozwalaj c na uzyskanie tego samego efektu. Procedura wstawia do tablicy poszczególne warto ci, a nast pnie za pomoc jednej instrukcji przenosi zawarto tablicy do zakresu.

Sub ArrayFillRange() ' Wype nienie 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 rozpocz cia 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)) ' Wype nienie 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 ' Wy wietlanie czas trwania operacji Application.ScreenUpdating = True MsgBox Format(Timer - StartTime, "00.00") & " sekund" End Sub

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

363

W moim systemie wype nienie zakresu o wymiarach 1000u250 komórek (250 000 komórek) przy u yciu p tli zaj o 10,05 sekundy. Metoda oparta na transferze zawarto ci tablicy do uzyskania identycznego efektu potrzebowa a zaledwie 0,18 sekundy, czyli dzia a a oko o 50 razy szybciej! Jaki wniosek wynika z tego przyk adu? Je eli chcesz przenie do arkusza du ilo danych, wsz dzie, gdzie tylko jest to mo liwe, unikaj stosowania p tli.

Osi gni te czasy w du ej mierze zale od obecno ci w arkuszu innych formu . W praktyce lepsze czasy otrzymasz w sytuacji, kiedy podczas testu nie b d otwarte inne skoroszyty zawieraj ce makra lub kiedy prze czysz Excela w tryb r cznego przeliczania arkusza.

Na p ycie CD-ROM do czonej do ksi ki znajdziesz skoroszyt Wype nianie zakresu przy u yciu p tli i tablicy.xlsm, zawieraj cy procedury WriteReadRange, LoopFillRange oraz ArrayFillRange.

Przenoszenie zawarto ci tablic jednowymiarowych W poprzednim przyk adzie zastosowano tablic dwuwymiarow , dobrze sprawdzaj c si w arkuszach, w których dane przechowywane s w uporz dkowanej strukturze wierszy i kolumn.

Aby przenie zawarto tablicy jednowymiarowej, zakres musi mie orientacj poziom — czyli inaczej mówi c, posiada jeden wiersz z wieloma kolumnami. Je eli jednak musisz u y zakresu pionowego, najpierw b dziesz musia dokona transponowania tablicy z poziomej na pionow . Aby to zrobi , mo esz u y funkcji arkuszowej TRANSPOSE (TRANSPONUJ) Excela. Poni sze polecenie przenosi tablic licz c 100 elementów do pionowego zakresu arkusza (A1:A100): Range("A1:A100").Value = Application.WorksheetFunction.Transpose(MyArray)

Funkcja arkuszowa TRANSPONUJ nie b dzie dzia a a z tablicami, w których przechowywanych jest wi cej ni 65 536 elementów.

Przenoszenie zawarto ci zakresu do tablicy typu Variant W tym podrozdziale omówimy kolejn metod przetwarzania zawarto ci arkusza przy u yciu j zyka VBA. W poni szym przyk adzie zawarto zakresu komórek jest przenoszona do dwuwymiarowej tablicy typu Variant. Nast pnie w oknach komunikatów s wy wietlane górne granice ka dego 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 wy wietlana warto 600 (liczba wierszy oryginalnego zakresu), natomiast w drugim — 12 (liczba kolumn). Jak sam si przekonasz, przeniesienie zawarto ci zakresu do tablicy typu Variant odbywa si prawie natychmiast.

364

Cz III J zyk Visual Basic for Applications

Poni sza procedura wczytuje zawarto zakresu o nazwie data do tablicy typu Variant, wykonuje na poszczególnych elementach tablicy prost operacj mno enia, a nast pnie 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 p tli dla tablicy typu Variant For r = 1 To UBound(x, 1) For c = 1 To UBound(x, 2) ' Mno enie kolejnych elementów tablicy przez 2 x(r, c) = x(r, c) * 2 Next c Next r ' Ponowne przeniesienie zawarto ci tablicy typu Variant do arkusza Range("data") = x End Sub

Jak sam si mo esz przekona , ca a procedura dzia a naprawd szybko. Przetwarzanie 30 000 komórek na moim komputerze trwa o poni ej jednej sekundy.

Skoroszyt z tym przyk adem (Przenoszenie tablicy typu Variant.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Zaznaczanie komórek na podstawie warto ci Nasz kolejny przyk ad ilustruje, w jaki sposób mo na zaznacza wybrane komórki w oparciu o ich warto ci. Co ciekawe, Excel nie posiada swojego w asnego mechanizmu, który umo liwia by bezpo rednie wykonanie takiej operacji. Poni ej przedstawiamy kod procedury SelectByValue, której zadaniem jest zaznaczenie komórek zakresu zawieraj cych warto ci ujemne, aczkolwiek mo na to w atwy sposób zmodyfikowa i dopasowa do w asnych 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 zawieraj cych warto ci numeryczne On Error Resume Next Set WorkRange = WorkRange.SpecialCells(xlConstants, xlNumbers)

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

365

If WorkRange Is Nothing Then Exit Sub On Error GoTo 0 '

Sprawdzaj w p tli kolejne komórki i dodawaj do zakresu FoundCells, je eli spe niaj 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 spe niaj cych kryterium."

Else FoundCells.Select End If End Sub

Procedura rozpoczyna dzia anie od sprawdzenia zaznaczonego zakresu. Je eli jest to pojedyncza komórka, przeszukiwany jest ca y arkusz. Je eli zaznaczone zosta y co najmniej 2 komórki, wtedy przeszukiwany jest tylko zaznaczony zakres. Zakres, który b dzie przeszukiwany, jest nast pnie redefiniowany poprzez u ycie metody SpecialCells do utworzenia obiektu klasy Range, który sk ada si tylko z komórek zawieraj cych warto ci numeryczne.

Kod w p tli For ... Next sprawdza warto ci kolejnych komórek. Je eli 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 mo esz u y metody Union dla pierwszej komórki — je eli zakres FoundCells nie zawiera adnych komórek, próba u ycia metody Union spowoduje wygenerowanie b du. W a nie dlatego w naszym programie zamie cili my kod sprawdzaj cy, czy zawarto zakresu FoundCells to Nothing.

Kiedy p tla ko czy swoje dzia anie, obiekt FoundCells sk ada si z komórek, które spe niaj kryterium wyszukiwania (je eli nie znaleziono adnych komórek, jego zawarto ci b dzie Nothing). Je eli adna komórka nie spe ni kryteriów, na ekranie zostanie wy wietlone okno dialogowe z odpowiednim komunikatem. W przeciwnym razie komórki spe niaj ce kryteria zostan zaznaczone.

Skoroszyt z tym przyk adem (Zaznaczanie wed ug warto ci.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Kopiowanie nieci g ego zakresu komórek Je eli kiedykolwiek próbowa e kopiowa nieci g y zakres komórek, z pewno ci przekona e si , e Excel nie obs uguje takiej operacji. Próba jej wykonania ko czy si wy wietleniem komunikatu Wykonanie tego polecenia dla kilku zakresów nie jest mo liwe.

366

Cz III J zyk Visual Basic for Applications

Jedynym wyj tkiem jest tutaj sytuacja, w której próbujesz kopiowa zaznaczenie wielokrotne, sk adaj ce si z ca ych wierszy lub kolumn. Excel pozwala na wykonanie takiej operacji.

Kiedy napotykasz takie ograniczenia w programie Excel, zazwyczaj mo esz je obej za pomoc odpowiedniego makra. Procedura, któr omówimy poni ej, jest przyk adem 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

'

Okre la 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 Je eli operacja zosta a anulowana, zako cz dzia anie If TypeName(PasteRange) <> "Range" Then Exit Sub

'

'

'

Upewnij si , e u ywamy tylko lewej, górnej komórki Set PasteRange = PasteRange.Range("A1")

Kopiowanie i wklejanie poszczególnych zakresów For i = 1 To NumAreas RowOffset = SelAreas(i).Row - TopRow ColOffset = SelAreas(i).Column - LeftCol SelAreas(i).Copy PasteRange.Offset(RowOffset, ColOffset) Next i End Sub

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

367

Na rysunku 11.11 przedstawiono okno dialogowe, w którym u ytkownik powinien zdefiniowa adres obszaru docelowego.

Rysunek 11.11. Zastosowanie metody InputBox programu Excel do pobierania lokalizacji komórki Skoroszyt z tym przyk adem (Kopiowanie wielu zakresów.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki. Dodatkowo w skoroszycie tym znajduje si inna wersja tej procedury, która ostrzega u ytkownika, je eli w wyniku kopiowania zostan nadpisane dane istniej ce w obszarze docelowym.

Przetwarzanie skoroszytów i arkuszy Kolejne przyk ady omawiane w tym podrozdziale demonstruj metody przetwarzania skoroszytów i arkuszy przy u yciu j zyka VBA.

Zapisywanie wszystkich skoroszytów Poni sza procedura przy u yciu p tli przetwarza wszystkie skoroszyty w kolekcji Workbooks i ponownie zapisuje ka dy plik, który by ju wcze niej zapisany: Public Sub SaveAllWorkbooks() Dim Book As Workbook For Each Book In Workbooks If Book.Path <> "" Then Book.Save Next Book End Sub

368

Cz III J zyk Visual Basic for Applications

Zwró uwag na w a ciwo Path. Je eli w a ciwo 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 w a ciwo Path ma ustawion dowoln warto .

Zapisywanie i zamykanie wszystkich skoroszytów Poni sza procedura przy u yciu p tli przetwarza kolekcj Workbooks, zapisuj c i zamykaj c 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 okre li , czy dany skoroszyt to skoroszyt zawieraj cy kod aktualnie wykonywanej procedury, nasza procedura u ywa instrukcji If umieszczonej w p tli For-Next. Jest to konieczne, poniewa zamkni cie takiego skoroszytu spowoduje przerwanie wykonywania kodu, na skutek czego inne skoroszyty nie zostan zapisane. Po zamkni ciu 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. Przyk ad takiej sytuacji przedstawiono 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 '

Je eli ostatni wiersz lub kolumna s ukryte, odkryj wszystko i zako cz dzia anie 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 row2 col1 col2

= = = =

Selection.Rows(1).Row row1 + Selection.Rows.Count - 1 Selection.Columns(1).Column col1 + Selection.Columns.Count - 1

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

369

Rysunek 11.12. Wszystkie komórki arkusza poza zaznaczonym zakresem (G8:K17) zosta y 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

Je eli zaznaczony obszar arkusza sk ada si z kilku nieci g ych zakresów komórek, baz do ukrywania wierszy i kolumn jest pierwszy zakres.

Skoroszyt z tym przyk adem (Ukrywanie wierszy i kolumn.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Synchronizowanie arkuszy Je eli korzysta e kiedykolwiek ze skoroszytów wieloarkuszowych, to wiesz zapewne, e Excel nie potrafi synchronizowa danych z poszczególnych arkuszy skoroszytu. Innymi s owy, nie ma mo liwo ci automatycznego zaznaczenia tego samego zakresu i ustawienia górnej, lewej komórki takiego multizakresu. Poni sze makro j zyka VBA jako bazy u ywa aktywnego arkusza, a na pozosta ych arkuszach skoroszytu wykonuje nast puj ce operacje:

370

Cz III J zyk 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ód owy 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 Zapami tuje aktualny arkusz Set UserSheet = ActiveSheet

'

Zapisuje informacje z aktywnego arkusza TopRow = ActiveWindow.ScrollRow LeftCol = ActiveWindow.ScrollColumn UserSel = ActiveWindow.RangeSelection.Address

'

Przechodzi w p tli 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 przyk adem (Synchronizacja arkuszy.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Techniki programowania w j zyku VBA Nast pne przyk ady ilustruj cz sto u ywane techniki programowania w j zyku VBA, które mo esz wykorzysta we w asnych projektach.

Prze czanie warto ci w a ciwo ci typu logicznego W a ciwo typu logicznego posiada warto True lub False. Najprostsza metoda prze czania warto ci takiej w a ciwo ci polega na zastosowaniu operatora Not. Zosta o to pokazane w poni szym przyk adzie, w którym prze czana jest warto w a ciwo ci WrapText obiektu Selection:

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

371

Sub ToggleWrapText() ' W cza lub wy cza zawijanie tekstu dla zaznaczonych komórek If TypeName(Selection) = "Range" Then Selection.WrapText = Not ActiveCell.WrapText End If End Sub

Oczywi cie w razie potrzeby mo esz dowolnie zmodyfikowa t procedur , tak aby prze cza a inne w a ciwo ci typu logicznego.

Zauwa , e prze czanie jest wykonywane w oparciu o aktywn komórk . Je eli po zaznaczeniu zakresu w a ciwo ci komórek maj ró ne warto ci (np. zawarto niektórych komórek jest pogrubiona, a innych nie), taki zakres ma mieszane w a ciwo ci. W tym przypadku Excel w celu okre lenia sposobu prze czania warto ci pos uguje si aktywn komórk . Je eli na przyk ad zawarto aktywnej komórki jest pogrubiona, po naci ni ciu przycisku na Wst ce pogrubienie zostanie usuni te z wszystkich komórek zaznaczenia.

Ta prosta procedura na laduje zachowanie Excela, co zazwyczaj jest najlepszym rozwi zaniem.

Zauwa równie , e do sprawdzenia, czy zaznaczono zakres, procedura wykorzystuje funkcj TypeName. Je eli zaznaczony obszar nie jest zakresem, nie zostanie wykonana adna operacja.

Operator Not umo liwia prze czanie warto ci wielu innych w a ciwo ci. Aby na przyk ad w arkuszu wy wietli lub ukry nag ówki wierszy i kolumn, nale y u y nast puj cej instrukcji: ActiveWindow.DisplayHeadings = Not ActiveWindow.DisplayHeadings

Aby w aktywnym arkuszu wy wietli lub ukry linie siatki, nale y u y nast puj cej instrukcji: ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines

Okre lanie liczby drukowanych stron W celu okre lenia liczby drukowanych stron arkusza mo na u y polecenia Podgl d wydruku Excela i w dolnej cz ci ekranu sprawdzi liczb stron. Procedura j zyka VBA przedstawiona poni ej zlicza poziome i pionowe linie podzia u stron i na tej podstawie okre la liczb drukowanych stron aktywnego arkusza: Sub PageCount() MsgBox (ActiveSheet.HPageBreaks.Count + 1) * _ (ActiveSheet.VPageBreaks.Count + 1) End Sub

Kolejna procedura j zyka VBA przechodzi w p tli przez wszystkie arkusze aktywnego skoroszytu i wy wietla ca kowit liczb drukowanych stron, tak jak to zosta o zilustrowane na rysunku 11.13.

Sub ShowPageCount() Dim PageCount As Integer Dim sht As Worksheet

372

Cz III J zyk 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 przyk adem (Zliczanie stron wydruku.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Wy wietlanie daty i czasu Je eli znasz ju system liczb seryjnych u ywany przez Excel do przechowywania dat i godzin, nie b dziesz mia adnych problemów z zastosowaniem dat i czasu w procedurach j zyka VBA. Procedura DateAndTime wy wietla okno komunikatu z aktualn dat i czasem, tak jak to zosta o przedstawione na rysunku 11.14. W pasku tytu owym okna wy wietlany jest komunikat odpowiedni dla danej pory dnia.

Rysunek 11.14.

Okno komunikatu wy wietlaj ce dat i czas

Procedura jako argument funkcji Format wykorzystuje funkcj Date. Wynikiem dzia ania procedury jest a cuch zawieraj cy sformatowan dat . Aby uzyska tak samo sformatowany czas, u yli my podobnego sposobu.

Sub DateAndTime() ' Wy wietla 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") ' Okre lenie 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 u ytkownika

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

373

FullName = Application.UserName SpaceInName = InStr(1, FullName, " ", 1) '

Obs uga przypadku, gdy nazwa nie zawiera spacji If SpaceInName = 0 Then SpaceInName = Len(FullName) FirstName = Left(FullName, SpaceInName) Greeting = Greeting & FirstName ' Wy wietlenie komunikatu MsgBox TheDate & vbCrLf & TheTime, vbOKOnly, Greeting End Sub

W celu zagwarantowania, e makro b dzie poprawnie dzia a o niezale nie od ustawie regionalnych systemu u ytkownika, w powy szym przyk adzie u yli my nazw formatów daty i czasu (Long Date i Long Time). Oczywi cie w razie potrzeby mo esz pos u y si innymi formatami. Aby na przyk ad wy wietli dat w formacie mm/dd/rr, mo esz u y nast puj cego polecenia: TheDate = Format(Date, "mm/dd/yy")

Aby uzale ni tre komunikatu wy wietlanego na pasku tytu owym okna od pory dnia, zastosowali my konstrukcj Select Case. Warto ci zwi zane z czasem stosowane w j zyku VBA funkcjonuj podobnie jak w Excelu. Je eli warto czasu jest mniejsza od liczby 0,5 (po udnie), oznacza to, e jest przedpo udnie. Je eli z kolei warto ta jest wi ksza od liczby 0,7083 (godzina 17), oznacza to, e jest wieczór. W innych przypadkach jest popo udnie. Wybrali my proste rozwi zanie polegaj ce na zastosowaniu funkcji TimeValue j zyka VBA, która pobieraj c a cuch, zwraca warto czasu.

Kolejna grupa instrukcji identyfikuje imi u ytkownika znajduj ce si w zak adce Ogólne okna dialogowego Opcje. W celu zlokalizowania pierwszej spacji zawartej w personaliach u ytkownika u y em funkcji InStr j zyka VBA. Po napisaniu procedury stwierdzi em, e nie uwzgl dni em identyfikatora u ytkownika, w którym nie wyst puje spacja. Gdy wi c uruchomi em j w systemie u ywanym przez u ytkownika Nobody, nie zadzia a a prawid owo. Jest to potwierdzeniem tezy, e nie mo na przewidzie wszystkiego, i nawet najprostsze procedury mog nie zadzia a . Nawiasem mówi c, je eli nie zostan podane personalia u ytkownika, Excel zawsze u yje nazwy aktualnie zalogowanego u ytkownika.

W procedurze problem ten zosta rozwi zany poprzez przypisanie zmiennej SpaceInName d ugo ci pe nej nazwy u ytkownika, tak aby funkcja Left zwróci a odpowiedni nazw .

Funkcja MsgBox czy dat i czas, a ponadto w celu wstawienia pomi dzy nimi znaku podzia u stosuje wbudowan sta vbCrLf. Sta a vbOKOnly jest predefiniowan sta zwracaj c zero i powoduj c , e w oknie komunikatu zostanie wy wietlony jedynie przycisk OK. Ostatni argument o nazwie Greeting zosta wcze niej zdefiniowany w procedurze.

Skoroszyt z tym przyk adem (Data i czas.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Pobieranie listy czcionek Je eli b dziesz chcia pobra list wszystkich zainstalowanych czcionek, przekonasz si , e Excel nie oferuje bezpo redniej metody uzyskania takiej listy. Technika opisywana tutaj wykorzystuje fakt, e ze wzgl du na konieczno zachowania kompatybilno ci

374

Cz III J zyk Visual Basic for Applications

z poprzednimi wersjami, Excel 2010 nadal obs uguje stare metody i w a ciwo ci obiektów CommandBar, które w wersjach wcze niejszych ni 2007 by y wykorzystywane do pracy z paskami narz dzi i menu.

Procedura ShowInstalledFonts wy wietla w kolumnie A aktywnego arkusza list zainstalowanych czcionek. Procedura tworzy tymczasowy pasek narz dzi (obiekt klasy Command ´Bar), dodaje do niego formant Font i odczytuje list czcionek z w a ciwo ci tego formantu. Po zako czeniu tymczasowy pasek narz dzi jest usuwany.

Sub ShowInstalledFonts() Dim FontList As CommandBarControl Dim TempBar As CommandBar Dim i As Long '

Tworzy tymczasowy pasek narz dzi (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 narz dzi (obiekt klasy CommandBar) TempBar.Delete End Sub

Opcjonalnie mo na te wy wietli nazw czcionki przy u yciu tej czcionki, tak jak to zosta o zaprezentowane na rysunku 11.15. Aby to zrobi , wewn trz p tli For ... Next nale y umie ci nast puj c instrukcj : Cells(i+1, 1).Font.Name = FontList.List(i + 1)

Trzeba jednak mie wiadomo , e zastosowanie w skoroszycie wielu czcionek spowoduje zu ycie znacznej ilo ci zasobów systemowych, a nawet mo e doprowadzi do zawieszenia komputera.

Skoroszyt z tym przyk adem (Lista czcionek.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Sortowanie tablicy Co prawda Excel posiada wbudowane polecenie sortuj ce zakresy arkusza, ale j zyk VBA nie dysponuje metod sortowania tablic. Skuteczne, ale niewygodne rozwi zanie tego problemu polega na przeniesieniu zawarto ci tablicy do zakresu arkusza, posortowaniu jej przy u yciu polecenia Excela, a nast pnie wczytaniu wyniku do tablicy. Je eli jednak szybko odgrywa du rol , lepiej stworzy w j zyku VBA procedur sortuj c .

W tym punkcie omówimy cztery ró ne metody sortowania: Sortowanie arkuszowe polega na przeniesieniu zawarto ci tablicy do zakresu arkusza,

posortowaniu jej, a nast pnie ponownym umieszczeniu w tablicy. Jedynym argumentem procedury opartej na tej metodzie jest tablica.

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

375

Rysunek 11.15.

Wy wietlanie listy czcionek przy u yciu tych samych czcionek

Sortowanie b belkowe jest prost metod sortowania (zastosowano j te

w przyk adzie demonstruj cym sortowanie arkusza w rozdziale 9.). Co prawda metoda sortowania b belkowego jest atwa w kodowaniu, ale ma raczej powolny algorytm, zw aszcza gdy przetwarzaniu podlega du a liczba elementów.

Sortowanie metod quick-sort (sortowanie szybkie) w porównaniu z b belkowym

jest o wiele szybsz metod sortowania, ale te znacznie trudniejsz do zrozumienia.

Metoda mo e zosta wykorzystana tylko w przypadku takich typów danych, jak Integer lub Long.

Sortowanie zliczaj ce jest bardzo szybkie, ale równie trudne do zrozumienia.

Technika ta, podobnie jak sortowanie szybkie, dzia a tylko w przypadku takich typów danych, jak Integer lub Long.

Na do czonym dysku CD-ROM znajduje si skoroszyt o nazwie Sortowanie.xlsm, który porównuje wy ej wymienione metody sortowania. Skoroszyt przydaje si w przypadku porównywania metod sortowania tablic o ró nych rozmiarach. Oczywi cie w razie potrzeby mo esz skopiowa z niego odpowiednie procedury i u y ich w swoich programach.

Na rysunku 11.16 pokazano okno dialogowe naszego programu. Procedury sortuj ce zosta y przetestowane przy u yciu tablic o siedmiu ró nych rozmiarach licz cych od 100 do 100 000 elementów. W tablicach zawarte by y warto ci losowe typu Long.

Cz III J zyk Visual Basic for Applications

376

Rysunek 11.16.

Porównanie czasu potrzebnego do wykonania operacji sortowania tablic o ró nych rozmiarach

W tabeli 11.1 zawar em wyniki testów. Warto 0,00 oznacza, e sortowanie zosta o zako czone prawie natychmiast w czasie krótszym ni 0,01 sekundy.

Tabela 11.1. Czas trwania (wyra ony w sekundach) operacji sortowania tablic wype nionych losowymi warto ciami przy u yciu czterech algorytmów sortuj cych Liczba elementów tablicy

Sortowanie arkuszowe Excela

Sortowanie b belkowe przy u yciu j zyka VBA

Sortowanie szybkie przy u yciu j zyka VBA

Sortowanie zliczaj ce przy u yciu j zyka 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 wyj tkowo szybki, zw aszcza e operacja uwzgl dnia przeniesienie zawarto ci tablicy do arkusza, sortowanie jej i ponowne wczytanie danych do tablicy. Je eli tablica jest ju prawie posortowana, sortowanie arkuszowe b dzie jeszcze szybsze.

Algorytm sortowania b belkowego jest do szybki w przypadku niewielkich tablic, ale przy wi kszych (licz cych ponad 5000 elementów) powiniene po prostu o nim zapomnie . Sortowanie szybkie oraz sortowanie zliczaj ce s bardzo szybkie, ale ich funkcjonalno jest ograniczona jedynie do danych typu Interger oraz Long.

Przetwarzanie grupy plików Jednym z cz stych zastosowa makr jest oczywi cie kilkakrotne powtarzanie okre lonej operacji. Przyk ad przedstawiony poni ej ilustruje, jak przy u yciu makra przetworzy kilka ró nych plików zapisanych na dysku. Procedura, która mo e pomóc w napisaniu

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

377

w asnego makra realizuj cego tego typu zadanie, prosi u ytkownika o podanie wzorca nazw plików, a nast pnie przetwarza wszystkie pliki, których nazwy s z nim zgodne.

W tym przypadku operacja przetwarzania polega na zaimportowaniu pliku i wprowadzeniu grupy formu sumuj cych, 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 ' Okre lenie cie ki 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 pasuj cych do wzorca " & FileSpec Exit Sub End If

'

Pobierz nazwy pozosta ych 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 p tli For i = 1 To FoundFiles Call ProcessFiles(FileList(i)) Next i End Sub

Skoroszyt z tym przyk adem (Przetwarzanie wsadowe.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki. Przyk ad korzysta z trzech dodatkowych plików, równie znajduj cych si na dysku CD-ROM. S to: text01.txt, text02.txt i text03.txt. Aby zaimportowa inne pliki tekstowe, b dziesz musia odpowiednio zmodyfikowa kod procedury.

Nazwy plików pasuj ce do wzorca s przechowywane w tablicy o nazwie FoundFiles.

Pliki s przetwarzane przy u yciu p tli For ... Next. W trakcie przetwarzania wewn trz p tli jest wywo ywana prosta procedura ProcessFiles. W celu zaimportowania pliku korzysta ona z metody OpenText, a nast pnie wstawia pi formu . Oczywi cie zamiast poni szej mo na zastosowa w asn procedur .

Sub ProcessFiles(FileName As String) ' Importowanie pliku Workbooks.OpenText FileName:=FileName, _ Origin:=xlWindows, _ StartRow:=1, _ DataType:=xlFixedWidth, _

378

Cz III J zyk Visual Basic for Applications

FieldInfo:= _ Array(Array(0, 1), Array(3, 1), Array(12, 1)) ' Wprowadzanie formu podsumowuj cych 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

Wi cej szczegó owych informacji na temat pracy z plikami z poziomu j zyka VBA znajdziesz w rozdziale 27.

Ciekawe funkcje, których mo esz u y w swoich projektach W tym podrozdziale zaprezentujemy kilka niestandardowych funkcji, które mo na albo bezpo rednio stosowa w aplikacjach u ytkowych, albo modyfikowa , traktuj c je jako twórcz inspiracj . Najprzydatniejsze s wtedy, gdy wywo uje si je z innej procedury j zyka VBA. Procedury zosta y zadeklarowane przy u yciu s owa kluczowego Private, dzi ki czemu nie b d widoczne w oknie dialogowym Wstawianie funkcji Excela.

Skoroszyt z tym przyk adem (Funkcje u ytkowe VBA.xlsm) znajdziesz na p ycie CDROM do czonej do ksi ki.

Funkcja FileExists Funkcja pobiera jeden argument ( cie ka pliku wraz z jego nazw ) i zwraca warto True, je eli plik istnieje: Private Function FileExists(fname) As Boolean ' Zwraca warto True, je eli istnieje plik FileExists = (Dir(fname) <> "") End Function

Funkcja FileNameOnly Funkcja pobiera jeden argument ( cie ka pliku wraz z jego nazw ) i zwraca tylko nazw pliku (innymi s owy — usuwa cie k pliku): Private Function FileNameOnly(pname) As String ' Zwraca nazw pliku pobran z a cucha z o onego ze cie ki i nazwy pliku Dim temp As Variant length = Len(pname) temp = Split(pname, Application.PathSeparator) FileNameOnly = temp(UBound(temp)) End Function

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

379

Funkcja FileNameOnly wykorzystuje funkcj Split VBA, która pobiera a cuch tekstu (oraz separator) i zwraca tabel typu Variant, zawieraj c elementy a cucha znajduj ce si pomi dzy znakami separatora. W tym przypadku zmienna temp zawiera tablic z a cuchami tekstu znajduj cymi si pomi dzy separatorami definiowanymi przez Application. ´PathSeparator (zaznaczaj s to znaki lewego uko nika). Inny przyk ad zastosowania funkcji Split znajdziesz w podrozdziale „Wyznaczanie n-tego elementu a cucha” w dalszej cz ci tego rozdzia u.

Je eli argumentem wywo ania funkcji jest cie ka c:\excel files\2010\backup\budget.xlsm, funkcja zwróci a cuch budget.xlsm.

Funkcja FileNameOnly przetwarza dowoln cie k i nazw pliku (nawet je eli plik nie istnieje). Je eli plik istnieje, poni sza funkcja oferuje prostsz metod usuwania cie ki i zwracania tylko nazwy pliku: Private Function FileNameOnly2(pname) As String FileNameOnly2 = Dir(pname) End Function

Funkcja PathExists Funkcja pobiera jeden argument ( cie ka pliku) i zwraca warto True, je eli cie ka istnieje: Private Function PathExists(pname) As Boolean ' Zwraca warto True, je eli istnieje cie ka 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, je eli w aktywnym skoroszycie istnieje nazwa zakresu: Private Function RangeNameExists(nname) As Boolean ' Zwraca warto True, je eli 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 poni ej. Funkcja w tej wersji próbuje utworzy zmienn obiektow przy u yciu nazwy zakresu. Je eli taka próba zako czy si wygenerowaniem b du, oznacza to, e dana nazwa zakresu nie istnieje.

380

Cz III J zyk Visual Basic for Applications

Private Function RangeNameExists2(nname) As Boolean ' Zwraca warto True, je eli 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, je eli w aktywnym skoroszycie istnieje arkusz o takiej nazwie: Private Function SheetExists(sname) As Boolean ' Zwraca warto True, je eli 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 nale y do kolekcji Poni sza procedura Function jest prost funkcj sprawdzaj c , czy dany obiekt nale y 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 ( a cuch), który mo e, ale nie musi nale e do kolekcji. Funkcja próbuje utworzy zmienn obiektow reprezentuj c element kolekcji. Je eli próba si powiedzie, funkcja zwraca warto True. W przeciwnym razie funkcja zwraca warto False.

Funkcji IsInCollection mo na u y zamiast trzech innych funkcji wymienionych w rozdziale (RangeNameExists, SheetExists i WorkbookIsOpen). Aby na przyk ad stwierdzi , czy w aktywnym skoroszycie istnieje zakres o nazwie Data, nale y wywo a funkcj IsInCollection przy u yciu poni szej instrukcji: MsgBox IsInCollection(ActiveWorkbook.Names, "Data")

Aby stwierdzi , czy otwarto skoroszyt o nazwie bud et.xlsx, nale y u y nast puj cej instrukcji: MsgBox IsInCollection(Workbooks, "bud et.xlsx")

Aby stwierdzi , czy aktywny skoroszyt zawiera arkusz o nazwie Arkusz1, nale y u y nast puj cej instrukcji: MsgBox IsInCollection(ActiveWorkbook.Worksheets, "Arkusz1")

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

381

Funkcja WorkbookIsOpen Funkcja pobiera jeden argument (nazwa skoroszytu) i zwraca warto True, je eli skoroszyt jest otwarty: Private Function WorkbookIsOpen(wbname) As Boolean ' Zwraca warto True, je eli 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 warto ci z zamkni tego skoroszytu J zyk VBA nie posiada metody umo liwiaj cej pobranie warto ci z zamkni tego skoroszytu. W razie potrzeby mo emy jednak skorzysta z faktu, e Excel obs uguje cza do plików. Zamieszczona poni ej funkcja GetValue j zyka VBA pobiera warto z zamkni tego skoroszytu. Zadanie to jest realizowane poprzez wywo anie starszego typu makra XLM, które by o stosowane w wersjach Excela sprzed wersji 5. Na szcz cie, jak wida , Excel nadal obs uguje makra tego starego typu.

Private Function GetValue(path, file, sheet, ref) ' Pobiera warto z zamkni tego 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 — cie ka zamkni tego pliku (np. "d:\pliki"); file — nazwa pliku skoroszytu (np. "bud et.xlsx"); sheet — nazwa arkusza (np. "Arkusz1"); ref — odwo anie do komórki (np. "C4").

Poni sza procedura Sub demonstruje, w jaki sposób u y funkcji GetValue. Procedura wy wietla po prostu warto w komórce A1 arkusza Arkusz1 pliku o nazwie 2010Bud et.xlsx znajduj cego si w katalogu XLPliki\Bud et na dysku C. Sub TestGetValue() Dim p As String, f As String Dim s As String, a As String

382

Cz III J zyk Visual Basic for Applications

p = "c:\XLPliki\Bud et" f = "2010Bud et.xlsx" s = "Arkusz1" a = "A1" MsgBox GetValue(p, f, s, a) End Sub

Kolejna procedura odczytuje z zamkni tego pliku 1200 warto ci (zajmuj cych obszar 100 wierszyu12 kolumn), a nast pnie 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\Bud et" f = "2010Bud et.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 zadzia a po zastosowaniu jej w formule arkuszowej. W praktyce nie ma jednak adnej potrzeby umieszczania tej funkcji w jakiejkolwiek formule. W celu pobrania warto ci z zamkni tego pliku wystarczy stworzy cze do komórki znajduj cej si w takim pliku.

Skoroszyt z tym przyk adem (Pobieranie warto ci z zamkni tego pliku.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki. Procedury zawarte w tym skoroszycie pobieraj dane ze skoroszytu o nazwie Mój skoroszyt.xlsx.

U yteczne, niestandardowe funkcje arkuszowe W tym podrozdziale zamie cili my przyk ady niestandardowych funkcji, które mo na zastosowa w formu ach arkusza. Pami taj, e takie funkcje (procedury typu Function), musz zosta zdefiniowane w module VBA, a nie w modu ach powi zanych z obiektami takimi jak ThisWorkbook, Arkusz1 lub UserForm1.

Skoroszyt z przyk adami omawianymi w tym podrozdziale (Funkcje arkuszowe.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Funkcje zwracaj ce informacje o formatowaniu komórki W tym podrozdziale znajdziesz szereg niestandardowych funkcji zwracaj cych ró norodne informacje o sposobie formatowania komórki. Funkcje takie s przydatne na przyk ad w sytuacji, kiedy trzeba posortowa dane w oparciu o formatowanie (np. gdy szukasz wszystkich komórek, wobec których u yto pogrubienia).

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

383

Takie funkcje nie zawsze s automatycznie przeliczane, poniewa zmiana formatowania nie uaktywnia mechanizmu Excela wykonuj cego ponowne obliczenia. Aby wymusi ponowne obliczenie wszystkich skoroszytów wraz z aktualizacj warto ci funkcji niestandardowych, nale y nacisn kombinacj klawiszy Ctrl+Alt+F9.

Innym rozwi zaniem jest umieszczenie w kodzie ród owym funkcji nast puj cego polecenia: Application.Volatile

Po zastosowaniu tego polecenia naci ni cie klawisza F9 spowoduje ponowne przeliczenie arkusza wraz z funkcjami niestandardowymi.

Poni sza funkcja zwraca warto True, je eli w jednokomórkowym zakresie b d cym jej argumentem zastosowano pogrubienie. Je eli jako argument wywo ania funkcji zostanie przekazany zakres, funkcja u yje lewej, górnej komórki tego zakresu: Function IsBold(cell) As Boolean ' Zwraca warto True, je eli zawarto komórki zosta a pogrubiona IsBold = cell.Range("A1").Font.Bold End Function

Pami taj, e takie funkcje dzia aj poprawnie tylko z komórkami, którym formatowanie zosta o nadane bezpo rednio za pomoc polece i przycisków formatuj cych i nie b d dzia a y w przypadku formatowania nadanego za pomoc mechanizmu formatowania warunkowego. W Excelu 2010 wprowadzony zosta nowy obiekt, DisplayFormat, który potrafi obs ugiwa elementy formatowania warunkowego. Poni ej zamieszczamy now wersj funkcji IsBold — zwraca ona poprawne wyniki równie dla komórek, których zawarto zosta a pogrubiona za po rednictwem formatowania warunkowego: Function IsBold(cell) As Boolean ' Zwraca warto True, je eli zawarto komórki zosta a pogrubiona (z uwzgl dnieniem formatowania warunkowego) IsBold = cell.Range("A1").DisplayFormat.Font.Bold End Function

Poni sza funkcja zwraca warto True, je eli w komórce b d cej jej argumentem zastosowano kursyw : Function IsItalic(cell) As Boolean ' Zwraca warto True, je eli w komórce u yto kursywy IsItalic = cell.Range("A1").Font.Italic End Function

Je eli w komórce zostanie zastosowane mieszane formatowanie (np. tylko wybrane znaki zostan pogrubione), obie powy sze funkcje zwróc b d. Kolejna funkcja zwróci warto True tylko wtedy, gdy wszystkie znaki z komórki b d pogrubione: Function AllBold(cell) As Boolean ' Zwraca warto True, je eli wszystkie znaki z komórki s pogrubione If IsNull(cell.Font.Bold) Then AllBold = False Else AllBold = cell.Font.Bold End If End Function

384

Cz III J zyk Visual Basic for Applications

Funkcja AllBold mo e zosta uproszczona do nast puj cej postaci: Function AllBold (cell) As Boolean ' Zwraca warto True, je eli wszystkie znaki z komórki s pogrubione AllBold = Not IsNull(cell.Font.Bold) End Function

Funkcja FillColor zwraca liczb ca kowit odpowiadaj c indeksowi koloru t a komórki (czyli inaczej mówi c, indeksowi koloru jej wype nienia). Kolor t a komórki zwykle zale y od wybranego stylu formatowania arkusza. Je eli t o komórki nie zostanie wype nione, funkcja zwraca warto –4142.

Ta funkcja nie dzia a poprawnie kolorami t a tabel (definiowanych za pomoc polecenia Tabela, znajduj cego si na karcie Wstawianie, w grupie polece Tabele). Aby uwzgl dni kolory t a tabel, powiniene u y obiektu DisplayFormat, o którym wspominali my wcze niej.

Function FillColor(cell) As Integer ' Zwraca liczb ca kowit odpowiadaj c kolorowi t a komórki FillColor = cell.Range("A1").Interior.ColorIndex End Function

Gadaj cy arkusz?

Funkcja SayIt wykorzystuje wbudowany w Excela generator mowy to odczytywania „na g os” przekazanego jej argumentu (którym mo e by a cuch tekstu lub odwo anie do wybranej komórki).

Function SayIt(txt) Application.Speech.Speak (txt) SayIt = txt End Function

Opisywana funkcja daje ca kiem interesuj ce efekty i mo e by naprawd u yteczna.

Spróbuj u y tej funkcji na przyk ad w nast puj cej formule: =IF(SUM(A:A)>25000,SayIt("Cel zosta osi gni ty!"))

Je eli suma warto ci w kolumnie A przekroczy warto 25000, us yszysz zsyntetyzowany g os rado nie oznajmiaj cy, e cel zosta osi gni ty. Metody Speak mo esz równie u y do powiadomienia o zako czeniu d ugo dzia aj cej procedury. W ten sposób po uruchomieniu procedury b dziesz móg zaj si czym innym, a Excel sam powiadomi Ci , kiedy procedura wreszcie zako czy dzia anie.

Wy wietlanie daty zapisania lub wydrukowania pliku Skoroszyt Excela posiada szereg wbudowanych w a ciwo ci dokumentu, które s dost pne dla programów VBA za po rednictwem w a ciwo ci BuiltinDocumentProperties obiektu Workbook. Poni sza funkcja zwraca dat i czas wykonania ostatniej operacji zapisu skoroszytu: Function LastSaved() Application.Volatile

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

385

LastSaved = ThisWorkbook. _ BuiltinDocumentProperties("Last Save Time") End Function

Data i czas ostatniego zapisu, zwracane przez t funkcj , to dok adnie ta sama informacja, jak znajdziesz w sekcji Powi zane daty widoku Backstage, dost pnego po przej ciu na kart Plik i wybraniu polecenia Informacje. Pami taj, e mechanizm automatycznego zapisu równie modyfikuje te dane, st d znacznik czasu ostatniego zapisu dokumentu niekoniecznie musi by to samy z tym, kiedy dany dokument zosta zapisany przez u ytkownika.

Poni sza funkcja jest podobna do poprzedniej, z tym e zwraca dat i czas wykonania ostatniej operacji drukowania lub podgl du wydruku dla skoroszytu. Je eli skoroszyt nie by jeszcze nigdy drukowany ani u ytkownik nigdy nie korzysta z opcji podgl du wydruku, funkcja zwraca b d #ARG!: Function LastPrinted() Application.Volatile LastPrinted = ThisWorkbook. _ BuiltinDocumentProperties("Last Print Date") End Function

Je eli u yjesz tych funkcji w formule, to w celu uzyskania aktualnych warto ci w a ciwo ci skoroszytu mo e by konieczne wymuszenie wykonania ponownych oblicze (poprzez wci ni cie klawisza F9).

Istnieje ca kiem sporo dodatkowych wbudowanych w a ciwo ci, ale Excel nie korzysta ze wszystkich. Na przyk ad próba u ycia w a ciwo ci Number of Bytes spowoduje wygenerowanie b du. Pe n list wbudowanych w a ciwo ci skoroszytów znajdziesz w pomocy systemowej programu Excel.

Funkcje LastSaved i LastPrinted zosta y zaprojektowane z my l o przechowywaniu ich w skoroszycie, w którym s u ywane. W niektórych przypadkach funkcja mo e zosta umieszczona w innym skoroszycie (np. personal.xlsb) lub dodatku. Poniewa obie powy sze funkcje odwo uj si do w a ciwo ci ThisWorkbook, po zapisaniu ich w innym skoroszycie nie b d dzia a y poprawnie. Poni ej zawarto wersje tych funkcji o bardziej uniwersalnym przeznaczeniu. Funkcje u ywaj w a ciwo ci Application.Caller, która zwraca obiekt klasy Range reprezentuj cy komórk wywo uj c funkcj . W a ciwo Parent.Parent zwraca skoroszyt (obiekt Workbook), czyli obiekt nadrz dny przodka obiektu Range. Zagadnienie to zostanie omówione dok adniej w nast pnym podrozdziale.

Function LastSaved2() Application.Volatile LastSaved2 = Application.Caller.Parent.Parent. _ BuiltinDocumentProperties("Last Save Time") End Function

Obiekty nadrz dne Jak pami tasz, 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.

Poni sza hierarchia ilustruje miejsce, jakie w tym schemacie zajmuje obiekt Range:

386

Cz III J zyk Visual Basic for Applications

obiekt Application obiekt Workbook obiekt Worksheet obiekt Range W j zyku programowania obiektowego mówimy, e przodkiem (obiektem nadrz dnym) obiektu Range jest obiekt Worksheet, b d cy jego kontenerem. Przodkiem obiektu Work ´sheet jest obiekt Workbook przechowuj cy arkusz. Z kolei obiektem nadrz dnym obiektu Workbook jest obiekt Application.

W jaki sposób wykorzysta t informacj w praktyce? Przeanalizujmy poni sz funkcj SheetName j zyka VBA. Funkcja pobiera jeden argument (zakres) i zwraca nazw arkusza zawieraj cego zakres. Funkcja u ywa w a ciwo ci Parent obiektu Range. W a ciwo Parent zwraca obiekt przechowuj cy obiekt Range.

Function SheetName(ref) As String SheetName = ref.Parent.Name End Function

Kolejna funkcja WorkbookName zwraca nazw skoroszytu zawieraj cego okre lon komórk .

Zauwa , e funkcja dwukrotnie u ywa w a ciwo ci Parent. Pierwsza w a ciwo Parent zwraca obiekt Worksheet, a druga — obiekt Workbook.

Function WorkbookName(ref) As String WorkbookName = ref.Parent.Parent.Name End Function

Poni sza funkcja AppName przenosi nas na kolejny poziom w hierarchii, trzykrotnie korzystaj c z w a ciwo ci Parent. Funkcja zwraca nazw obiektu Application powi zanego z okre lon komórk . Oczywi cie w naszym przypadku funkcja zawsze b dzie zwraca a warto Microsoft Excel.

Function AppName(ref) As String AppName = ref.Parent.Parent.Parent.Name End Function

Zliczanie komórek, których warto ci zawieraj si pomi dzy dwoma warto ciami Poni sza funkcja o nazwie CountBetween zwraca liczb warto ci w zakresie (pierwszy argument), mieszcz cych si pomi dzy dwoma warto ciami reprezentowanymi przez drugi i trzeci argument: Function CountBetween(InRange, num1, num2) As Long ' Zlicza warto ci z przedzia u od num1 do num2 With Application.WorksheetFunction If num1 <= num2 Then CountBetween = .CountIfs(InRange, ">=" & num1, _ InRange, "<=" & num2) Else CountBetween = .CountIfs(InRange, ">=" & num2, _ InRange, "<=" & num1)

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

387

End If End With End Function

Funkcja korzysta z funkcji arkuszowej COUNTIFS (LICZ.WARUNKI) Excela i w praktyce spe nia rol funkcji os onowej upraszczaj cej tworzone formu y.

Funkcja LICZ.WARUNKI zosta a wprowadzona w Excelu 2007, st d taka funkcja nie b dzie dzia a a z wcze niejszymi wersjami Excela.

Poni ej zamieszczono przyk ad formu y korzystaj cej z funkcji CountBetween, zwracaj cej liczb komórek zakresu A1:A100, których warto ci s wi ksze lub równe 10 i mniejsze lub równe 20: =CountBetween(A1:A100, 10, 20)

Funkcja pobiera dwa argumenty numeryczne w dowolnej kolejno ci, st d formu a przedstawiona poni ej dzia a dok adnie tak samo, jak jej poprzedniczka: =CountBetween(A1:A100, 20, 10)

Zastosowanie tej funkcji j zyka VBA jest zdecydowanie prostsze ni wprowadzenie nast puj cej d ugiej (i jak wida — dosy z o onej) formu y: =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.

Ka da z funkcji pobiera pojedynczy argument, którym jest zakres. Zakresem mo e by ca a kolumna (funkcja LastInColumn) lub ca y wiersz (funkcja LastInRow). Je eli przekazany argument nie jest ca kolumn lub wierszem, funkcja u yje wiersza lub kolumny okre lonej przez górn lew komórk zakresu. Poni sza przyk adowa formu a zwraca warto ostatniej, niepustej komórki kolumny B: =LastInColumn(B5)

Kolejna formu a zwraca warto ostatniej, niepustej komórki z wiersza 7: =LastInRow(C7:D9)

Oto kod ród owy funkcji LastInColumn: Function LastInColumn(rng As Range) ' Zwraca zawarto ostatniej niepustej komórki kolumny Dim LastCell As Range Application.Volatile With rng.Parent With .Cells(.Rows.Count, rng.Column) If Not IsEmpty(.Value) Then LastInColumn = .Value ElseIf IsEmpty(.End(xlUp)) Then

388

Cz III J zyk Visual Basic for Applications

LastInColumn = "" Else LastInColumn = .End(xlUp).Value End If End With End With End Function

Funkcja jest dosy z o ona, dlatego poni ej przedstawiamy kilka punktów, które mog pomóc Ci zrozumie jej sposób dzia ania: Metoda Application.Volatile powoduje, e funkcja zostanie wykonana

ka dorazowo przy obliczaniu arkusza.

W a ciwo Rows.Count zwraca liczb wierszy arkusza. Zamiast na sztywno

wprowadza w kodzie ród owym liczb wierszy arkusza, u y em w a ciwo ci Count, poniewa kolejna wersja Excela mo e obs ugiwa jeszcze wi ksz ni

dotychczas liczb wierszy.

W a ciwo rng.Column zwraca numer kolumny górnej lewej komórki zakresu b d cego warto ci argumentu rng.

Zastosowanie w a ciwo ci rng.Parent powoduje, e funkcja b dzie dzia a poprawnie nawet wtedy, gdy argument rng odwo uje si do innego arkusza lub

skoroszytu.

U ycie metody End z argumentem xlUp jest równoznaczne z uaktywnieniem ostatniej

komórki kolumny, wci ni ciem klawisza End, a nast pnie klawisza n.

Funkcja IsEmpty sprawdza, czy komórka jest pusta. Je eli tak jest, zwraca pusty a cuch. Gdyby funkcja IsEmpty nie zosta a zastosowana, po napotkaniu pustej komórki nasza funkcja zwróci aby warto 0.

Poni ej przedstawiamy kod ród owy 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 a cuch tekstu jest zgodny z wzorcem?

Funkcja IsLike jest bardzo prosta i jednocze nie bardzo u yteczna. Zwraca warto True, je eli a cuch tekstowy jest zgodny ze zdefiniowanym wzorcem.

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

389

Jak wida poni ej, kod funkcji jest bardzo prosty. Funkcja w a ciwie odgrywa rol funkcji os onowej, pozwalaj cej na wygodne u ycie w tworzonych formu ach wszechstronnego operatora Like j zyka VBA: Function IsLike(text As String, pattern As String) As Boolean ' Zwraca warto True, je eli pierwszy argument jest podobny do drugiego IsLike = text Like pattern End Function

Funkcja IsLike pobiera dwa argumenty: text — a cuch tekstowy lub odwo anie do komórki, która go zawiera; pattern — a cuch zawieraj cy znaki wieloznaczne, które wymieniono w poni szej

tabeli.

Znaki zawarte we wzorcu

Zawarto a cucha text zgodna ze wzorcem

?

Dowolny pojedynczy znak

*

0 lub wi cej dowolnych znaków

#

Dowolna pojedyncza cyfra (0 – 9)

[lista_znaków]

Dowolny pojedynczy znak znajduj cy si na li cie_znaków

[!lista_znaków]

Dowolny pojedynczy znak, który nie znajduje si na li cie_znaków

Poni sza formu a zwraca warto TRUE, poniewa wzorzec * jest zgodny z dowoln liczb znaków. Formu a zwraca warto TRUE, je eli pierwszy argument jest dowolnym a cuchem tekstowym rozpoczynaj cym si od litery g: =IsLike("gitara", "g*")

Kolejna formu a zwraca warto TRUE, poniewa wzorzec ? jest zgodny z dowolnym pojedynczym znakiem. Formu a zwróci warto FALSE, je eli warto ci pierwszego argumentu b dzie a cuch Jednostka12: = IsLike("Jednostka1", "Jednostka?")

Nast pna formu a zwraca warto TRUE, poniewa jej pierwszy argument jest pojedynczym znakiem zawartym w drugim argumencie: = IsLike("a", "[aeiou]")

Poni sza formu a zwraca warto TRUE, je eli komórka A1 zawiera liter a, e, i, o, u, A, E, I, O lub U. Przetworzenie argumentów przy u yciu funkcji UPPER spowoduje, e formu a nie b dzie rozró nia a wielko ci znaków: = IsLike(UPPER(A1), UPPER("[aeiou]"))

Poni sza formu a zwraca warto TRUE, je eli komórka A1 zawiera warto rozpoczynaj c si cyfr 1 i sk adaj c si dok adnie z trzech cyfr (czyli dowoln liczb ca kowit z przedzia u od 100 do 199): = IsLike(A1, "1##")

390

Cz III J zyk Visual Basic for Applications

Wyznaczanie n-tego elementu a cucha Funkcja ExtractElement jest niestandardow funkcj arkusza (mo e by te wywo ywana z procedury j zyka VBA) wyznaczaj c n-ty element a cucha tekstowego. Je eli na przyk ad komórka zawiera poni szy tekst, w celu wydzielenia dowolnego pod a cucha zawartego pomi dzy cznikami mo na u y funkcji ExtractElement: 123-456-789-0133-8844

Kolejna formu a zwraca pod a cuch 0133 b d cy czwartym elementem a cucha (w roli separatora w a cuchu jest u ywany cznik): =ExtractElement("123-456-789-0133-8844", 4, "-")

Funkcja ExtractElement pobiera trzy argumenty: Txt — a cuch tekstowy, z którego s wydzielane pod a cuchy (mo e to by litera

lub odwo anie do komórki); n — liczba ca kowita reprezentuj ca wydzielany element; Separator — pojedynczy znak spe niaj cy funkcj separatora.

Je eli warto ci argumentu b dzie spacja, ci gi kilku spacji zostan potraktowanych jak jedna spacja, co prawie zawsze b dzie zgodne z Twoimi zamierzeniami. Je eli warto argumentu n przekroczy liczb elementów a cucha, funkcja zwróci pusty a cuch.

Oto kod ród owy funkcji ExtractElement j zyka VBA: Function ExtractElement(Txt, n, Separator) As String ' Zwraca n-ty element a cucha tekstowego, w którym poszczególne elementy oddziela okre lony znak separatora Dim AllElements As Variant AllElements = Split(Txt, Separator) ExtractElement = AllElements(n - 1) End Function

Funkcja korzysta z funkcji Split j zyka VBA zwracaj cej tablic typu Variant, która zawiera poszczególne elementy a cucha tekstowego. Indeks tablicy rozpoczyna si od warto ci 0, a nie 1, dlatego odwo ania do kolejnych elementów tablicy s realizowane poprzez wyra enie n-1.

Zamiana warto ci na s owa1 Funkcja SPELLDOLLARS zwraca „s own ” wersj warto ci numerycznych podanych jako argument wywo ania funkcji (tak jak w dobrze ka demu znanej formu ce S OWNIE: spotykanej na blankietach przelewów i wp at czy fakturach). Na przyk ad formu a przedstawiona poni ej zwraca nast puj cy a cuch tekstu: One hundred twenty-three and 45/100 dollars (sto dwadzie cia trzy dolary i 45 centów): =SPELLDOLLARS(123.45)

1

Uwaga: funkcja opisana w tym podrozdziale zwraca warto ci w j zyku angielskim — przyp. t um.

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

391

Na rysunku 11.17 przedstawiono kilka przyk adów zastosowania funkcji SPELLDOLLARS. Formu y zosta y umieszczone w kolumnie C. Przyk adowo formu a umieszczona w komórce C1 ma nast puj c posta : =SPELLDOLLARS(A1)

Zwró uwag na fakt, e warto ci ujemne s podawane w nawiasach.

Rysunek 11.17. Przyk ady zastosowania funkcji SPELLDOLLARS Funkcja SPELLDOLLARS jest zbyt z o ona, aby j tutaj zaprezentowa w ca o ci, ale skoroszyt zawieraj cy pe ny kod tej funkcji (Funkcje arkuszowe.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Funkcja wielofunkcyjna Nast pny przyk ad prezentuje technik , która mo e okaza si przydatna w niektórych sytuacjach. Sprawia ona, e pojedyncza funkcja arkusza zachowuje si jak wiele funkcji.

Poni ej zamieszczamy kod ród owy niestandardowej funkcji o nazwie StatFunction, która pobiera dwa argumenty — zakres (rng) i operacj (op). W zale no ci od warto ci argumentu op funkcja zwraca warto obliczon przy u yciu dowolnej z nast puj cych funkcji arkuszowych: AVERAGE ( REDNIA), COUNT (ILE.LICZB), MAX, MEDIAN (MEDIANA), MIN, MODE (WYST.NAJCZ CIEJ), STDEV (ODCH.STANDARDOWE), SUM (SUMA) lub VAR (WARIANCJA).

Funkcji StatFunction mo esz u y w arkuszu w nast puj cy sposób: =StatFunction(B1:B24, A24)

Wynik formu y zale y od zawarto ci komórki A24, która powinna by takim a cuchem, jak Average, Count, Max itd. Podobn technik kodowania mo esz zastosowa w przypadku innych funkcji.

Function StatFunction(rng, Select Case UCase(op) Case "SUM" StatFunction = Case "AVERAGE" StatFunction = Case "MEDIAN" StatFunction =

op)

WorksheetFunction.Sum(rng) WorksheetFunction.Average(rng) WorksheetFunction.Median(rng)

392

Cz III J zyk Visual Basic for Applications

Case "MODE" StatFunction Case "COUNT" StatFunction Case "MAX" StatFunction Case "MIN" StatFunction Case "VAR" StatFunction Case "STDEV" StatFunction Case Else StatFunction End Select End Function

= WorksheetFunction.Mode(rng) = WorksheetFunction.Count(rng) = WorksheetFunction.Max(rng) = WorksheetFunction.Min(rng) = WorksheetFunction.Var(rng) = WorksheetFunction.StDev(rng) = CVErr(xlErrNA)

Funkcja SheetOffset Excel oferuje ograniczon obs ug trójwymiarowych skoroszytów. Je eli na przyk ad konieczne jest odwo anie do innego arkusza skoroszytu, w formule trzeba uwzgl dni nazw arkusza. Nie stanowi to jednak du ego problemu… do momentu próby skopiowania formu y do innych arkuszy. Skopiowane formu y w dalszym ci gu odwo uj si do nazwy oryginalnego arkusza, a odwo ania do arkuszy nie s modyfikowane tak, jak mia oby to miejsce w prawdziwym trójwymiarowym arkuszu.

W tym podrozdziale zosta omówiony przyk ad funkcji j zyka VBA o nazwie SheetOffset, umo liwiaj cej stosowanie wzgl dnych odwo a do arkuszy. Na przyk ad w celu odwo ania si do komórki A1 poprzedniego arkusza nale y u y formu y: =SheetOffset(-1, A1)

Pierwszy argument funkcji, który mo e by warto ci dodatni , ujemn lub zerem, identyfikuje wzgl dne odwo anie do arkusza. Drugi argument musi by odwo aniem do pojedynczej komórki. Po skopiowaniu formu y do innych arkuszy odwo anie wzgl dne b dzie obowi zywa o we wszystkich jej kopiach.

Oto kod ród owy funkcji SheetOffset j zyka VBA: Function SheetOffset(Offset As Long, Optional Cell As Variant) ' Zwraca zawarto komórki wzgl dnie adresowanego arkusza, do której zdefiniowano odwo anie Dim WksIndex As Long, WksNum As Long Dim wks As Worksheet Application.Volatile If IsMissing(Cell) Then Set Cell = Application.Caller WksNum = 1 For Each wks In Application.Caller.Parent.Parent.Worksheets If Application.Caller.Parent.Name = wks.Name Then SheetOffset = Worksheets(WksNum + Offset).Range(Cell(1).Address) Exit Function Else WksNum = WksNum + 1 End If Next wks End Function

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

393

Zwracanie maksymalnej warto ci ze wszystkich arkuszy Aby okre li maksymaln warto komórki B1 z kilku arkuszy, mo na zastosowa formu podobn do poni szej: =MAX(Arkusz1:Arkusz4!B1)

Formu a zwraca maksymaln warto komórki B1 z arkuszy Arkusz1, Arkusz4 i wszystkich, które znajduj si pomi dzy nimi.

Co si jednak stanie, gdy za arkuszem Arkusz4 zostanie wstawiony nowy arkusz Arkusz5?

Formu a nie uwzgl dni tego automatycznie, dlatego konieczne b dzie jej zmodyfikowanie w celu dodania nowego odwo ania do arkusza: =MAX(Arkusz1:Arkusz5!B1)

Funkcja MaxAllSheets pobiera jeden argument i zwraca maksymaln warto okre lonej komórki z wszystkich arkuszy skoroszytu. Przyk adowo poni sza formu a zwraca maksymaln warto komórki B1 z uwzgl dnieniem wszystkich arkuszy skoroszytu: =MaxAllSheets(B1)

Po dodaniu nowego arkusza nie b dzie ju potrzeby edytowania formu y.

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 ' Unikni cie odwo ania 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 dost pu do skoroszytu p tla For Each u ywa nast puj cego wyra enia: cell.Parent.Parent.Worksheets

Obiektem nadrz dnym komórki jest arkusz, natomiast przodkiem arkusza jest skoroszyt.

Wynika z tego, e p tla For Each ... Next przetwarza wszystkie arkusze skoroszytu.

Pierwsza instrukcja If z p tli sprawdza, czy przetwarzana komórka zawiera funkcj . Je eli tak jest, to w celu unikni cia b du odwo ania cyklicznego komórka zostanie zignorowana.

394

Cz III J zyk Visual Basic for Applications

Opisana funkcja z atwo ci mo e zosta zmodyfikowana tak, aby wykonywa a inne obliczenia mi dzyarkuszowe oparte na takich funkcjach, jak MIN, REDNIA, SUMA itd.

Zwracanie tablicy zawieraj cej unikatowe, losowo uporz dkowane liczby ca kowite Funkcja RandomIntegers zamieszczona w tym punkcie zwraca tablic unikatowych liczb ca kowitych. Stosowana jest w wielokomórkowych formu ach tablicowych. {=RandomIntegers()}

Zaznacz zakres, a nast pnie wprowad formu (bez nawiasów klamrowych) i zatwierd j poprzez naci ni cie kombinacji klawiszy Ctrl+Shift+Enter. Formu a zwraca tablic zawieraj c unikatowe, losowo uporz dkowane liczby ca kowite. Je eli na przyk ad formu a zostanie wprowadzona do zakresu z o onego z 50 komórek, jej kopie zwróc unikatowe liczby ca kowite z przedzia u od 1 do 50.

Oto kod ród owy 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 b d, je eli warto obiektu FuncRange jest zbyt du a 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) Wype nienie tablicy losowymi warto ciami i liczbami ca kowitymi zakresu rng For i = 1 To CellCount ValArray(1, i) = Rnd ValArray(2, i) = i Next i Sortowanie tablicy ValArray wed ug wymiaru o losowej warto ci For i = 1 To CellCount For j = i + 1 To CellCount If ValArray(1, i) > ValArray(1, j) Then Temp1 = ValArray(1, j) Temp2 = ValArray(2, j) ValArray(1, j) = ValArray(1, i)

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

395

ValArray(2, j) = ValArray(2, i) ValArray(1, i) = Temp1 ValArray(2, i) = Temp2 End If Next j Next i '

Wstawienie losowo uporz dkowanych warto ci 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

Porz dkowanie zakresu w losowy sposób Funkcja RangeRandomize pobiera jeden argument b d cy zakresem i zwraca tablic z o on z losowo uporz dkowanych warto ci 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 b d, je eli warto obiektu rng jest zbyt du a 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) Wype nienie tablicy ValArray losowymi warto ciami i warto ciami obiektu rng For i = 1 To CellCount ValArray(1, i) = Rnd ValArray(2, i) = rng(i) Next i Sortowanie tablicy ValArray wed ug wymiaru o losowej warto ci For i = 1 To CellCount For j = i + 1 To CellCount If ValArray(1, i) > ValArray(1, j) Then Temp1 = ValArray(1, j) Temp2 = ValArray(2, j) ValArray(1, j) = ValArray(1, i) ValArray(2, j) = ValArray(2, i)

'

'

396

Cz III J zyk Visual Basic for Applications

ValArray(1, i) = Temp1 ValArray(2, i) = Temp2 End If Next j Next i '

Wstawienie losowo uporz dkowanych warto ci 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 zauwa y , kod ród owy tej funkcji jest bardzo podobny do kodu funkcji Random ´Integers.

Na rysunku 11.18 pokazano wynik dzia ania funkcji. Formu a tablicowa zawarta w zakresie B2:B11 ma nast puj c posta : {=RangeRandomize(A2:A11)}

Formu a zwraca zawarto komórek zakresu A2:A11, ale uporz dkowanego w losowy sposób.

Rysunek 11.18.

Funkcja RangeRandomize zwraca zawarto komórek zakresu w przypadkowej kolejno ci

Wywo ania funkcji interfejsu Windows API Jedn z najwa niejszych cech j zyka VBA jest mo liwo wywo ywania funkcji przechowywanych w bibliotekach DLL (ang. Dynamic Link Library). W przyk adach zaprezentowanych w tym podrozdziale b dziemy korzystali z cz sto u ywanych funkcji interfejsu API systemu Windows.

Dla uproszczenia deklaracje funkcji API przedstawiane w tym podrozdziale dzia aj tylko w Excelu 2010 (zarówno w wersji 32-bitowej, jak i 64-bitowej), natomiast przyk ady zamieszczone na dysku CD-ROM do czonym do ksi ki zawieraj odpowiednie dyrektywy kompilatora, dzi ki czemu b d poprawnie dzia a równie we wcze niejszych wersjach Excela.

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

397

Okre lanie skojarze plików W systemie Windows wiele typów plików jest kojarzonych z okre lon aplikacj . Po wykonaniu takiego skojarzenia plik mo na otworzy w powi zanej z nim aplikacji poprzez dwukrotne klikni cie pliku.

Funkcja GetExecutable w celu uzyskania pe nej cie ki aplikacji skojarzonej z okre lonym plikiem korzysta z funkcji interfejsu API systemu Windows. Przyjmijmy, e w Twoim systemie znajduje si wiele plików o rozszerzeniu .txt. Jeden z nich, o nazwie Readme.txt, prawdopodobnie znajduje si w katalogu systemu Windows. Aby okre li pe n cie k aplikacji otwieraj cej plik po jego dwukrotnym klikni ciu, mo na u y funkcji GetExecutable.

Deklaracje funkcji interfejsu API systemu Windows musz zosta umieszczone na pocz tku modu u 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 wywo ania funkcji GetExecutable, która jako argument pobra a nazw pliku muzycznego w formacie MP3. Funkcja zwraca pe n cie k aplikacji powi zanej z plikiem.

Rysunek 11.19.

Okre lanie cie ki aplikacji powi zanej z okre lonym plikiem

Skoroszyt z tym przyk adem (Skojarzenia plików.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Pobieranie informacji o nap dach dyskowych VBA nie posiada metody pozwalaj cej na bezpo rednie pobieranie informacji o zainstalowanych w systemie nap dach dyskowych. Jednak dzi ki zastosowaniu trzech funkcji API mo esz uzyska niezb dne informacje.

Na rysunku 11.20 przedstawiono wynik dzia ania procedury VBA, która identyfikuje wszystkie pod czone do systemu dyski, okre la ich typ, sprawdza ca kowit pojemno , rozmiar u ytego miejsca oraz rozmiar wolnego miejsca.

398

Cz III Â&#x2039; J zyk Visual Basic for Applications

Rysunek 11.20. Zastosowanie funkcji Windows API do pobierania informacji o dyskach

Kod procedury jest dosy d ugi i z o ony, dlatego nie umieszczono go tutaj, ale je eli jeste ciekawy, jak to dzia a, mo esz zajrze do odpowiedniego skoroszytu na p ycie CD-ROM do czonej do ksi ki.

Skoroszyt z tym przyk adem (Informacja o dyskach.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Pobieranie informacji dotycz cych drukarki domy lnej W kolejnym przyk adzie u yjemy funkcji interfejsu Windows API zwracaj cej informacje na temat domy lnej drukarki. Dane znajduj si w pojedynczym a cuchu tekstowym.

Poni sza procedura analizuje a cuch i wy wietla informacje przy u yciu bardziej czytelnego dla u ytkownika 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 urz dzenia Port = Right(Result, ResultLength - Comma2) Tworzy komunikat Msg = "Drukarka:" & Chr(9) & Printer & Chr(13)

Rozdzia 11. ย Przyk ady i techniki programowania w j zyku VBA

399

Msg = Msg & "Sterownik:" & Chr(9) & Driver & Chr(13) Msg = Msg & "Port:" & Chr(9) & Port ' Wy wietla komunikat MsgBox Msg, vbInformation, "Informacje o drukarce domy lnej" End Sub

Co prawda w a ciwo ActivePrinter obiektu Application zwraca nazw domy lnej drukarki i umo liwia jej zmian , ale nie istnieje bezpo rednia metoda okre lenia, jaki sterownik i port urz dzenia jest u ywany. Z tego w a nie powodu czasami przydatna mo e by nasza funkcja GetProfileStringA.

Na rysunku 11.21 pokazano przyk adowe okno komunikatu wy wietlone przez t procedur .

Rysunek 11.21.

Informacja o drukarce domy lnej wy wietlona przy u yciu funkcji interfejsu API systemu Windows Skoroszyt z tym przyk adem (Informacja o drukarce.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Pobieranie informacji o aktualnej rozdzielczo ci karty graficznej Zamieszczony w tym punkcie kod korzysta z funkcji interfejsu API w celu okre lenia aktualnej rozdzielczo ci karty graficznej u ywanej w systemie. Je eli u ywana aplikacja musi wy wietli okre lon ilo informacji na jednym ekranie, znajomo jego rozdzielczo ci mo e pomรณc we w a ciwym przeskalowaniu tekstu. Oprรณcz tego kod procedury sprawdza liczb monitorรณw pod czonych do komputera. Je eli pod czonych jest wi cej monitorรณw ni jeden, procedura wy wietla 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 pod czone" & vbCrLf

400

Cz III J zyk Visual Basic for Applications

Msg Msg Msg Msg

= = = =

Msg Msg Msg Msg

& & & &

"Pulpit wirtualny: " & virtWidth & " × " virtHeight & vbCrLf & vbCrLf "Rozdzielczo g ównego monitora to: " 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 powy sz procedur uruchomion w systemie u ywaj cym dwóch monitorów.

Rysunek 11.22.

Zastosowanie funkcji interfejsu Windows API do okre lenia rozdzielczo ci karty graficznej Skoroszyt z tym przyk adem (Informacja o rozdzielczo ci karty graficznej.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Dodanie d wi ku do aplikacji Przyk ad, który omówiono w tym podrozdziale, pozwala na dodanie efektów d wi kowych do aplikacji Excela, a w szczególno ci pozwala na odtwarzanie d wi ków zapisanych w formacie MIDI lub WAV. Mo esz na przyk ad odtwarza wybrany d wi k w momencie otwierania danego okna dialogowego (lub nie…). Je eli b dziesz chcia , aby Excel odtwarza pliki w formacie MIDI lub WAV, w tej sekcji znajdziesz dok adnie to, czego b dziesz potrzebowa .

Skoroszyt z tym przyk adem (D wi k.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Odtwarzanie plików typu WAV Poni szy przyk ad zawiera deklaracj funkcji interfejsu API wraz z prost procedur odtwarzaj c plik d wi kowy o nazwie sound.wav, który znajduje si w tym samym katalogu, co plik skoroszytu: Private Declare Function PlaySound Lib "winmm.dll" _ Alias "PlaySoundA" (ByVal lpszName As String, _ ByVal hModule As Long, ByVal dwFlags As Long) As Long Const SND_SYNC = &H0 Const SND_ASYNC = &H1 Const SND_FILENAME = &H20000 Sub PlayWAV()

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

401

WAVFile = "sound.wav" WAVFile = ThisWorkbook.Path & "\" & WAVFile Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME) End Sub

W przyk adzie plik formatu WAV jest odtwarzany asynchronicznie. Oznacza to, e w trakcie odtwarzania kontynuowane jest wykonywanie procedury. Aby zatrzyma wykonywanie kodu ród owego podczas odtwarzania d wi ku, nale y u y nast puj cej 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 procedury StopMIDI spowoduje zako czenie odtwarzania pliku. W przyk adzie u yto 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 d wi ku przy u yciu funkcji arkuszowej Funkcja Alarm zosta a stworzona z my l o zastosowaniu w formule arkusza. Je eli komórka spe nia okre lone kryterium, funkcja, u ywaj c interfejsu API systemu Windows, odtwarza plik d wi kowy.

Declare Function PlaySound Lib "winmm.dll" _ Alias "PlaySoundA" (ByVal lpszName As String, _ ByVal hModule As Long, ByVal dwFlags As Long) As Long Function Alarm(Cell, Condition) Dim WAVFile As String Const SND_ASYNC = &H1 Const SND_FILENAME = &H20000 If Evaluate(Cell.Value & Condition) Then WAVFile = ThisWorkbook.Path & "\sound.wav" Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME) Alarm = True Else Alarm = False End If End Function

402

Cz III J zyk Visual Basic for Applications

Funkcja Alarm pobiera dwa argumenty — odwo anie do komórki i warunek (maj cy posta a cucha). Poni sza formu a u ywa funkcji Alarm do odtworzenia pliku formatu WAV, gdy warto komórki B13 b dzie wi ksza lub równa 1000: =ALARM(B13; ">=1000")

W celu stwierdzenia, czy warto komórki spe nia okre lone kryterium, funkcja korzysta z funkcji Evaluate j zyka VBA. Po spe nieniu kryterium i wygenerowaniu d wi ku funkcja zwróci warto True. W przeciwnym razie zwróci warto False.

Funkcja SayIt, omawiana wcze niej w tym rozdziale, jest znacznie prostszym sposobem na wzbogacenie aplikacji w efekty d wi kowe.

Skoroszyt z tymi przyk adami (d wi k.xlsm) znajdziesz na p ycie CD-ROM do czonej do ksi ki.

Odczytywanie zawarto ci rejestru systemu Windows i zapisywanie w nim danych Wi kszo aplikacji Windows potrzebne informacje przechowuje w rejestrze systemu b d cym baz danych. Aby uzyska dodatkowe informacje o rejestrze, nale y zajrze do rozdzia u 4. Procedury j zyka VBA s w stanie odczytywa dane z rejestru i zapisywa w nim nowe warto ci. Aby to by o mo liwe, konieczne jest zastosowanie nast puj cych 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

Utworzy em dwie funkcje os onowe u atwiaj ce korzystanie z rejestru. S to: GetRegistry i WriteRegistry. Obie znajduj si na do czonym dysku CD-ROM, w skoroszycie o nazwie Rejestr systemu Windows.xlsm. Przyk adowy skoroszyt zawiera procedur demonstruj c odczyt i zapis danych w rejestrze.

Odczyt danych z rejestru Funkcja GetRegistry zwraca ustawienia znajduj ce si w okre lonej lokalizacji rejestru.

Funkcja pobiera trzy argumenty:

Rozdzia 11. Przyk ady i techniki programowania w j zyku VBA

403

RootKey — a cuch reprezentuj cy g ówny klucz rejestru, który zostanie u yty.

Oto mo liwe a cuchy: HKEY_CLASSES_ROOT HKEY_CURRENT_USER HKEY_LOCAL_MACHINE HKEY_USERS HKEY_CURRENT_CONFIG Path — pe na cie ka kategorii rejestru, która zostanie u yta.

RegEntry — nazwa ustawienia, które zostanie odczytane.

Aby na przyk ad odnale w rejestrze aktualne ustawienie powi zane z aktywnym paskiem tytu u okna, nale y w sposób pokazany poni ej wywo a 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 wy wietli cie k i nazw pliku graficznego u ytego w roli tapety pulpitu (je eli tapeta nie jest u ywana, funkcja zwróci pusty a cuch).

Zapis danych w rejestrze Funkcja WriteRegistry zapisuje warto w okre lonej lokalizacji rejestru. Je eli operacja zako czy si powodzeniem, funkcja zwróci warto True. W przeciwnym razie zwróci warto False. Funkcja WriteRegistry pobiera nast puj ce argumenty (wszystkie s a cuchami tekstu): RootKey — a cuch reprezentuj cy klucz rejestru, który zostanie u yty.

Oto mo liwe a cuchy: HKEY_CLASSES_ROOT HKEY_CURRENT_USER HKEY_LOCAL_MACHINE HKEY_USERS HKEY_CURRENT_CONFIG Path — pe na cie ka kategorii rejestru (je eli cie ka nie istnieje, zostanie

utworzona).

RegEntry — nazwa kategorii rejestru, w której zostanie zapisana warto (je eli

kategoria nie istnieje, zostanie dodana).

RegVal — zapisywana warto .

404

Cz III ย J zyk Visual Basic for Applications

Poni ej zamieszczono przyk ad procedury zapisuj cej w rejestrze warto reprezentuj c dat i czas uruchomienia Excela. Informacja jest zapisywana w miejscu, w ktรณrym s przechowywane ustawienia dotycz ce 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 & " zosta a zapisana w rejestrze."

Else msg = "Wyst pi b d."

End If MsgBox msg End Sub

Je eli zapiszesz t procedur w module ThisWorkbook skoroszytu makr osobistych, ustawienia b d automatycznie aktualizowane przy ka dym uruchomieniu programu Excel.

atwiejszy sposรณb uzyskania dost pu do rejestru Je eli w celu zapisania i odczytania danych dost p do rejestru systemu Windows chcesz uzyska z poziomu aplikacji Excela, nie musisz stosowa funkcji interfejsu API. Zamiast nich mo na u y funkcji GetSetting i SaveSetting j zyka VBA. Obie funkcje zosta y obja nione w systemie pomocy, dlatego nie b d ich tutaj szczegรณ owo omawia . Jednak nale y wiedzie , e funkcje te dzia aj tylko z kluczem o nast puj cej nazwie: HKEY_CURRENT_USER\Software\VB and VBA Program Settings

Innymi s owy, funkcje nie mog zosta zastosowane w celu uzyskania dost pu do dowolnego klucza rejestru. Funkcje te s najbardziej przydatne do zapisywania informacji o w asnych aplikacjach Excela, ktรณre chcesz przechowa pomi dzy kolejnymi sesjami.