Zintegrowany kurs języka VBA – cz. 1 · PDF fileDariusz Pietrzyk Zintegrowany kurs...

Dariusz Pietrzyk

Zintegrowany kurs języka VBA – cz. 1

Podstawy programowania – operacje na danych w MS Excel

ISBN 978-83-912106-8-0

Copyright © by Dariusz Pietrzyk & Wydawnictwo Studio eM (www.sem.net.pl)

Żadna część tego podręcznika nie może być bez pisemnej zgody Wydawcy powielana lub rozpowszechniana w jakiejkolwiek formie czy też jakimikolwiek technikami elektronicznymi bądź mechanicznymi. Dotyczy to wykonania kopii dowolną metodą poligraficzną, kserograficzną czy fotograficzną oraz utrwalania na filmowym, magnetycznym, optycznym (m.in. CD-ROM i DVD-ROM) lub jakimkolwiek innym nośniku.

Autor oraz Wydawnictwo Studio eM dołożyli wszelkich starań, by opracowanie niniejsze było rzetelne i kompletne, ale nie biorą żadnej odpowiedzialności za wykorzystanie zawartych w nim informacji czy też za związane z tym ewentualne naruszenie praw patentowych bądź autorskich. Autor oraz Wydawnictwo nie ponoszą również żadnej odpowiedzialności za ewentualne szkody wynikłe z wy-korzystania informacji zawartych w tym podręczniku.

Wymienione w podręczniku nazwy produktów oraz marek są znakami handlo-wymi bądź towarowymi, przynależnymi ich prawowitym właścicielom.

Dariusz Pietrzyk, Zintegrowany kurs języka VBA – cz. 1

Spis treści

Wprowadzenie ............................................................................. 11Do kogo podręcznik jest adresowany? .................................... 11Czego się nauczysz z tego podręcznika? ................................. 12Jak możesz używać tego podręcznika? ................................... 13Cel kursu ................................................................................. 15Kurs jako wprowadzenie do programowania ......................... 16Porządek omawianego materiału ........................................... 17Język VBA a różne wersje MS Excel ...................................... 20O autorze ................................................................................ 21

Makra – automatyzacja zadań w arkuszach ............................... 23Co to jest makro (procedura/program)? ............................... 23Język VBA .............................................................................. 23Zastosowanie makr ................................................................ 25Sposoby tworzenia makr ........................................................ 26Przygotowanie do tworzenia makr ......................................... 27Rejestracja (nagrywanie) i uruchamianie makr .................... 28

Aby włączyć wstęgę z obsługą makr (wer. 2007) .............. 29Aby włączyć wstęgę z obsługą makr (wer. 2010) .............. 29Aby ustawić poziom zabezpieczeń (wer. XP/2003) .......... 30Aby ustawić poziom zabezpieczeń (wer. 2007/2010) ....... 31Aby zarejestrować makro (wer. XP/2003) ....................... 32Aby zarejestrować makro (wer. 2007/2010) .................... 33Aby uruchomić makro (sposób uniwersalny) ................... 35Aby zmodyfikować kod makra .......................................... 35Aby usunąć makro ............................................................. 37

Przyciski uruchamiające makra z poziomu arkusza .............. 37Aby wstawić do arkusza przycisk uruchamiający makro . . 39Aby przypisać do makra skrót klawiszowy ....................... 40

Copyright © Dariusz Pietrzyk 3

Paski narzędziowe .................................................................. 41Aby uaktywnić skoroszyt makr osobistych (personal.xls) .............................................................. 44Aby szybko przełączyć się do okna edytora VBA .............. 44Aby utworzyć własny pasek narzędziowy (wer. XP/2003) .......................................................... 45Aby utworzyć własną wstęgę/pasek narzędziowy (wer. 2010) ........................................................................ 45Aby podpiąć makro do przycisku na pasku narzędziowym (wer. XP/2003) ......................................... 46Aby dodać przycisk uruchamiający makro do paska „Szybki dostęp” (wer. 2007/2010) .................................... 47

Edycja i zarządzanie kodem VBA ............................................... 49Okno edytora VBA ................................................................. 49Moduły VBA (ang. modules) .................................................. 52

Aby utworzyć nowy moduł ................................................ 53Aby zmienić nazwę modułu ............................................... 54Aby przejść do edycji kodu w wybranym module ............. 55Aby szybko przełączyć się między otwartymi modułami . . 55Aby zamknąć podgląd aktywnego modułu ........................ 55Aby usunąć moduł ............................................................. 55

Archiwizacja i przenoszenie kodu VBA .................................. 56Aby wyeksportować moduł ................................................ 57Aby zaimportować moduł z pliku zewnętrznego .............. 58

Edycja kodu VBA .................................................................... 59Aby szybko odnaleźć procedurę w module ....................... 62Aby przeskoczyć w kodzie do początku następnej procedury ................................................................... 62Aby szybko zwiększyć lub zmniejszyć wcięcie ................... 63Aby ustalić podstawowe parametry działania edytora VBA ............................................................... 63

4 Copyright © Dariusz Pietrzyk


Znaczenie kolorów w edytorze VBA ....................................... 67

Wstęp do programowania .......................................................... 69Jakość kodu ............................................................................ 69Procedury i funkcje ................................................................ 72

Aby utworzyć procedurę .................................................... 73Zmienne i stałe ....................................................................... 74

Aby wymusić jawną deklarację zmiennych ....................... 79Aby zadeklarować zmienną ............................................... 81Aby zadeklarować stałą ..................................................... 82Aby wyświetlić wyniki działania procedury w oknie dialogowym (przykład programu) .................................... 83

Typy danych VBA ................................................................... 85Typy liczbowe .................................................................... 87Pozostałe typy ................................................................... 88

Operatory ................................................................................ 91Operator przypisania ......................................................... 95Operatory arytmetyczne .................................................... 96Operator sklejania tekstów ............................................... 98Operatory porównania ...................................................... 99Operatory logiczne ........................................................... 101

Testowanie programów ............................................................ 103Uruchamianie krokowe ........................................................ 103

Aby przetestować działanie procedury krok po kroku .... 104Szybki podgląd wyników przetwarzania (instrukcja „Debug.Print” i panel „Immediate”) .................................... 105

Aby wyniki przetwarzania skierować do panelu „Immediate” ..................................................................... 107

Czujki – śledzenie wartości zmiennych ............................... 108Aby szybko dodać czujkę dla zmiennej ........................... 108Aby włączyć/wyłączyć okno z czujkami ........................... 110Aby dodać czujkę .............................................................. 111


Aby usunąć czujkę ............................................................ 112Aby śledzić wszystkie zmienne lokalne ............................ 112

Przetwarzanie tekstów ............................................................... 115Sklejanie tekstów (konkatenacja) ......................................... 116

Aby połączyć ciąg znaków ................................................ 118Aby przełamać wiersz tekstu w kodzie ............................. 119Aby przełamać wynikowy wiersz tekstu (np. w oknie komunikatu) .................................................................... 120Aby dodać do tekstu wynikowego znaki specjalne (np. cudzysłów) ................................................................ 122

Funkcje przetwarzające ciągi znaków .................................. 123Aby sprawdzić ilość znaków w tekście (funkcja „Len”) ... 126Aby pobrać początek tekstu (funkcja „Left”) ................... 128Aby pobrać koniec tekstu (funkcja „Right”) .................... 129Aby pobrać środkowy fragment tekstu (funkcja „Mid”) . 130Aby usunąć zbędne spacje z początku i końca tekstu (funkcja „Trim”) ............................................................... 132Aby zamienić tekst na wielkie litery (funkcja „UCase”) . . 135Aby zamienić tekst na małe litery (funkcja „LCase”) ....... 137Aby znaleźć tekst w ciągu znaków (funkcja „InStr”) ....... 138Aby sprawdzić, czy znak jest cyfrą (funkcja „IsNumeric”) ............................................................. 141Aby ustalić sposób porównywania tekstów („Option Compare Binary/Text”) ................................................... 144

Sterowanie przetwarzaniem danych – instrukcje warunkowe i pętle ..................................................................... 145

Instrukcja warunkowa (If … Then) ...................................... 146Aby obsłużyć tylko warunek prawdziwy tylko jedną instrukcją ......................................................................... 147Aby obsłużyć tylko warunek prawdziwy więcej niż jedną instrukcją ......................................................................... 150



Aby użyć warunku złożonego ........................................... 151Aby obsłużyć warunek prawdziwy lub fałszywy .............. 155Aby obsłużyć tylko jeden ze sprawdzanych po kolei warunków ........................................................................ 158

Instrukcja wyboru („Select Case”) ....................................... 160Aby użyć instrukcji „Select Case” ..................................... 161

Pętla z licznikiem („For … Next”) ......................................... 166Aby użyć pętli „For … Next” ............................................. 169Aby zmieniać licznik pętli o wartość inną niż 1 ................ 171Aby użyć w pętli osobnego licznika wewnętrznego ......... 174

Pętla przetwarzająca kolekcje obiektów(„For Each … In … Next”) ..................................................... 177

Aby użyć pętli „For Each … In … Next” ........................... 180Pętla z warunkiem zakończenia („Do … Loop”) ................... 182

Aby użyć pętli „Do … Loop” ............................................. 186Aby użyć pętli „Do … Loop” z warunkiem na początku . . 188Aby użyć pętli „Do … Loop” z warunkiem „While” ......... 189

Pętla „While … Wend” .......................................................... 190Aby użyć pętli „While … Wend” ....................................... 191

Instrukcja przerwania/wyjścia („Exit”) ............................... 192Aby przerwać działanie pętli, procedury lub funkcji ....... 195

VBA w arkuszu kalkulacyjnym .................................................. 199Własności i metody obiektów ............................................... 199Hierarchia obiektów w MS Excel ......................................... 201Składnia obiektowa .............................................................. 204

Aby użyć metody lub własności obiektu ......................... 206Klasy i kolekcje obiektów ..................................................... 207

Operacje na aktywnej komórce ................................................. 210Wartość komórki (właściwość „Value”) ................................ 211

Aby pobrać wartość komórki ........................................... 212Aby wpisać wartość do komórki ...................................... 214


Tekst wyświetlany w komórce (właściwość „Text”) ............. 215Aby pobrać tekst wyświetlany w komórce ....................... 216

Formatowanie komórki z użyciem stylu (właściwość „Style”) ........................................................................... 217

Aby sformatować komórkę z użyciem stylu .................... 219Czyszczenie komórki (grupa metod „Clear”) ....................... 221

Aby wyczyścić komórkę ................................................... 222Adres komórki (właściwość „Address”) ............................... 222

Aby sprawdzić adres komórki ......................................... 223Współrzędne komórki (właściwości „Row” i „Column”) ..... 223

Aby sprawdzić numer wiersza i numer kolumny dla komórki ........................................................................... 224

Podsumowanie głównych właściwości i metod dla komórki ......................................................................... 225

Nawigacja względem aktywnej komórki ................................... 227Właściwość „Offset” ............................................................. 227Metoda „Activate” ................................................................ 229

Aby przejść do innej komórki .......................................... 231Aby pobrać wartość z komórki poniżej ........................... 233

Odwołania do komórek i obszarów .......................................... 235Obszar arkusza (obiekt „Range”) ......................................... 235Zaznaczanie obszarów (metoda „Select” i obiekt „Selection”) ................................................................... 236

Aby zaznaczyć komórkę o znanym adresie ...................... 237Aby zaznaczyć obszar o znanym adresie ......................... 238Aby zaznaczyć obszar o znanym adresie początku i końca zakresu ................................................................ 239Aby zaznaczyć obszar nieciągły ....................................... 239

Zaznaczanie obszarów – podsumowanie ............................ 240Kolumny i wiersze (obiekty „Column” i „Row”) ................... 241

Aby zaznaczyć kolumnę o znanej nazwie ........................ 242



Aby zaznaczyć kolumnę o znanym numerze ................... 243Aby sprawdzić, czy kolumna jest ukryta (właściwość „Hidden”) ........................................................................ 243Aby dopasować szerokość zaznaczonych kolumn do ich zawartości (metoda „AutoFit”) ............................. 244Aby zaznaczyć wiersz o numerze ..................................... 245Aby zaznaczyć kilka wierszy leżących obok siebie .......... 246Aby dopasować wysokość zaznaczonych wierszy do zawartości (metoda „AutoFit”) ....................................... 246Aby ustalić wysokość zaznaczonych wierszy (właściwość „RowHeight”) .............................................. 247Aby policzyć wiersze w arkuszu (właściwość „Count”) ... 249Aby policzyć kolumny w arkuszu (właściwość „Count”) . 250

Kolumny i wiersze – podsumowanie ................................... 250Odwołanie do komórki za pomocą współrzędnych ............. 252

Aby odwołać się przez współrzędne do komórki w arkuszu .................................................................. 253Aby odwołać się przez współrzędne do komórki w zaznaczonym obszarze ................................................. 254Odwołanie do komórki o podanych współrzędnych – podsumowanie ................................................................ 258

Pobieranie danych od użytkownika .......................................... 259Wprowadzanie danych przez użytkownika (metoda „InputBox”) .......................................................................... 260

Aby pobrać dane od użytkownika ................................... 262Konwersja typów danych ..................................................... 264

Aby skonwertować tekst na typ „Date” (funkcja „CDate”) .................................................................... 271

Skorowidz ................................................................................. 274


Wprowadzenie

[…]

Niniejszy podręcznik obejmuje podstawowy zestaw wiedzy w za-

kresie automatyzacji pracy w aplikacji MS Excel od wersji XP aż

po 2007/2010.

Do kogo podręcznik jest adresowany?

Ten podręcznik pomoże Ci zdobyć obszerną, solidną i przydatną

wiedzę, jeśli dobrze znasz i intensywnie wykorzystujesz aplikację

MS Excel w codziennej pracy, a zarazem:

• Widzisz potrzebę dalszego zwiększenia wydajności pracy w tym

zakresie.

• Przetwarzasz duże ilości danych i potrzebujesz działania z tym

związane przyspieszyć oraz ułatwić.

• Wykonujesz wiele rozmaitych powtarzalnych, monotonnych

i pracochłonnych, a przez to męczących i nużących czynności,

które zarazem mogą obniżyć jakość wykonywanej pracy – czło-

wiek zmęczony łatwiej popełnia błędy. Maszyna natomiast się

nie męczy, a jeśli będzie dobrze zaprogramowana, nie będzie

błędów popełniać w ogóle.

• Czujesz ograniczenia w możliwościach samej aplikacji – jest

w niej wiele przydatnych narzędzi, ale nie rozwiązuje ona

wszystkich problemów. Każdy z nas pracuje przecież ze specy-

ficznymi zestawami i strukturami danych, zatem potrzebuje

wielu narzędzi wyspecjalizowanych do ich przetwarzania.

• Próbujesz nauczyć się samodzielnie języka VBA, ale napotykasz

na tej drodze przeszkody, które wydają Ci się zbyt trudne do

przezwyciężenia.

Wprowadzenie

• Używasz języka VBA od jakiegoś czasu (i może nawet masz na

swoim koncie kilka bardziej złożonych programów), ale chcesz

uporządkować, uzupełnić, poszerzyć i pogłębić swoją wiedzę

w tym zakresie, a w efekcie skuteczniej zapobiegać rozmaitym –

dziwnym czasami i na razie niezrozumiałym – błędom działa-

nia Twoich programów.

• Chcesz, by Twoje programy napisane w VBA działały jeszcze

szybciej, sprawniej i bardziej niezawodnie.

Czego się nauczysz z tego podręcznika?

Wielu mechanizmów umożliwiających przede wszystkim pisanie

programów, które:

• ułatwią i znacząco przyspieszą wprowadzanie dużych ilości po-

wtarzalnych danych (np. liczb porządkowych),

• wspomogą weryfikację i usuwanie najbardziej uciążliwych

i najczęściej spotykanych błędów w danych czy nawet oblicze-

niach wykonywanych w arkuszach kalkulacyjnych,

• wspomogą analizę dużych ilości danych oraz tworzenie różno-

rodnych raportów i zestawień,

• przyspieszą precyzyjne formatowanie licznych i obszernych

arkuszy.

A przy okazji – jeśli nie znasz jeszcze żadnego języka programo-

wania – nauczysz się również podstaw programowania, czyli

umiejętności zmuszenia komputera do tego, by wspomagał Cię

w wykonywaniu powtarzalnych czynności, które męczą i zajmują

niepotrzebnie Twój cenny czas. I – co najważniejsze – by maszy-

na robiła to wszystko dokładnie zgodnie z Twoimi potrzebami.



Najlepiej wiesz, co w pracy z arkuszami kalkulacyjnymi zajmuje

najwięcej Twojego czasu, a wiedza zdobyta za pośrednictwem

niniejszego kursu pozwoli Ci też uniezależnić się od innych osób

z Twojego otoczenia (biura, firmy, korporacji), które taką wiedzę

już posiadają, ale zarazem nie zawsze mają czas i możliwości, by

pomóc w rozwiązywaniu również Twoich problemów.

Jak możesz używać tego podręcznika?

Przede wszystkim:

1. Do sukcesywnej nauki.

Czytając ze zrozumieniem kolejne rozdziały, a przy okazji prze-

pisując i testując podane w nich przykłady, stopniowo możesz

zdobyć, utrwalić i poszerzać bardzo przydatną wiedzę.

Aby nauczyć się programować, trzeba programować – pisać

programy, testować je i usuwać znalezione błędy. Im więcej ko-

du zatem napiszesz (nawet jeśli zaczniesz od przepisywania

i uruchamiania), tym szybciej zdobędziesz wystarczającą

sprawność, by skutecznie rozwiązywać coraz bardziej skompli-

kowane problemy związane z automatyzacją wykonywanej na

co dzień pracy. Jeśli do tego dołożysz jeszcze twórczą zabawę –

z wykorzystaniem stopniowo poznawanych na tym kursie

elementów języka VBA, by dostosować dostępne w tym pod-

ręczniku przykłady do własnych potrzeb, nauka będzie łatwiej-

sza i przyniesie lepsze rezultaty.

I choć być może na początku będziesz mieć uczucie powolnego

posuwania się naprzód w tym rozwoju, zaś pisane programy

będą najpierw stosunkowo proste, to jednak – przy odrobinie


Wprowadzenie

cierpliwości i konsekwencji – dotrzesz do takiego momentu, że

niespodziewanie zaczniesz „myśleć” w języku VBA, czyli stanie

się on naprawdę przedłużeniem Twojej ręki i narzędziem,

które pozwoli bardzo ułatwić i przyspieszyć wiele codziennych

czynności.

2. Jako podręczną pomoc w programowaniu.

Przy pisaniu kodu potrzebnych programów najpierw zawsze

musisz uświadomić sobie, jakie czynności program ma kolejno

wykonać. Następnie dla każdej kolejnej czynności możesz od-

szukać w spisie treści podręcznika mechanizm, który pozwala

ją wykonać, a następnie przepisać i dostosować do konkretnej

sytuacji i własnych potrzeb wybrany fragmentu kodu przykła-

dowego (pojedynczych instrukcji i funkcji czy też całych więk-

szych konstrukcji).

W ten sposób możesz tworzyć nawet bardziej skomplikowane

programy z minimalnym wysiłkiem – próba napisania od razu

całego dłuższego kodu, szczególnie bez takiej dodatkowej po-

mocy, może być zniechęcająca. Bowiem nawet programista

z dużym doświadczeniem i wiedzą w zakresie używania tego

czy innego języka programowania ma czasem problemy, by

napisać większy program od razu „z głowy na czysto”.

3. Jako podręczne wsparcie w tłumaczeniu zapisów języka VBA.

Jeśli czytając kod nagranego makra lub procedury napisanej

przez kogoś innego, napotkasz nieznaną lub mało zrozumiałą

instrukcję, możesz sięgnąć do skorowidza, gdzie znajdziesz

odsyłacz do dokładniejszego objaśnienia poszczególnych kon-

strukcji, obiektów, metod i właściwości.



4. Jako „przypominacza”.

Jeśli znasz już polecenie języka VBA lub nazwę funkcji, której

potrzebujesz, ale nie pamiętasz dokładnie jej składni lub

innych szczegółów użycia, wówczas możesz zajrzeć do skoro-

widza, znaleźć tam potrzebny element VBA, a następnie przy-

pomnieć sobie potrzebne szczegóły w oparciu o odpowiedni

fragment podręcznika.

[…]

Porządek omawianego materiału

Materiał podawany w tym i kolejnych częściach tego kursu są

ułożone warstwami – od podstaw do coraz bardziej zaawan-

sowanych zagadnień, ale zarazem od zagadnień najbardziej przy-

datnych na co dzień do takich, które przydają się w tworzeniu

coraz bardziej wyspecjalizowanych mechanizmów. Stąd w niniej-

szym podręczniku znajdziesz kolejno rozdziały:

1. Makra – automatyzacja zadań w arkuszach (s. 23) – nagry-

wanie makr i ich udostępnianie użytkownikom (przez przyciski

i paski narzędziowe), czyli tworzenie prostszych mechanizmów

przyspieszających najczęściej wykonywane czynności.

2. Edycja i zarządzanie kodem VBA (s. 49) – krótkie omówienie

środowiska ułatwiającego pisanie programów w tym języku,

zasad tworzenia poprawnego i stabilnego kodu oraz archiwi-

zacji (zabezpieczenia) i przenoszenia naszych programów z ze-

szytu do zeszytu czy też na inny komputer.

3. Wstęp do programowania (s. 69) – od czego zacząć pisanie

programu, jakie są podstawowe elementy składowe typowego


Wprowadzenie

programu (głównie zmienne i stałe, typy danych i operatory)

oraz jakie mają one znaczenie.

4. Testowanie programów (s. 103) – zapewnienie maksymalnej

poprawności działania naszych programów i podstawowe me-

chanizmy ułatwiające osiąganie tego celu.

5. Przetwarzanie tekstów (s. 115) – sklejanie tekstów, pisanie

czytelnego i przejrzystego kodu oraz funkcje ułatwiające wyko-

nywanie najczęstszych operacji na tekstach.

Jest to omówienie mechanizmów, które w programowaniu są

(wbrew pozorom) powszechnie i bardzo często wykorzysty-

wane w bardzo wielu sytuacjach, a zarazem są na tyle proste,

by od nich właśnie rozpocząć przestawianie się na sposób

myślenia właściwy do rozwiązywania różnych problemów pro-

gramistycznych (jeśli jeszcze nie masz takiego doświadczenia).

6. Sterowanie przetwarzaniem danych – instrukcje warunkowe

i pętle (s. 145) – objaśnienie konstrukcji, które są niezbędne,

by tworzyć efektywnie działające programy, w tym również te

przetwarzające większe ilości danych.

7. VBA w arkuszu kalkulacyjnym (s. 199) – objaśnienie podsta-

wowych zagadnień związanych z uzyskiwaniem dostępu do

danych, które chcemy przetwarzać w naszych programach

(w kontekście zeszytów i aplikacji MS Excel).

8. Operacje na aktywnej komórce (s. 210) – m.in. jak pobrać

i zmienić wartość, zmienić formatowanie czy też sprawdzić

adres komórki (nie tylko aktywnej zresztą).



9. Nawigacja względem aktywnej komórki (s. 227) – poruszanie

się po arkuszu oraz sięganie do komórek, wykorzystywane

przede wszystkim przy przetwarzaniu większych ilości danych

wg schematu „od komórki zaznaczonej do końca ciągu

danych”.

10. Odwołania do komórek i obszarów (s. 235) – głównie dostęp

do komórek/obszarów o znanych adresach oraz do kolumn

i wierszy arkusza.

11. Pobieranie danych od użytkownika (s. 259) – jak pobrać od

użytkownika pojedyncze parametry potrzebne do działania

programu (np. adres komórki czy też współczynnik wykorzy-

stywany do obliczeń) oraz jak dokonać konwersji typów da-

nych (dostosować dane uzyskane w formie nieodpowiedniej do

potrzeb przetwarzania w programu).

Autor kursu starał się też, by nie pojawiały się tu skróty myślowe

spotykane w tego rodzaju podręcznikach. Zdarza się bowiem, że

pewne zagadnienia lub pojęcia wydają się niektórym autorom

oczywiste – na tyle, że nie warto ich omawiać czy objaśniać. I jak-

kolwiek osoba, która np. w języku VBA już od jakiegoś czasu

programuje (lub programowała wcześniej w innym) i ma już

pewną ugruntowaną wiedzę w tym zakresie, czasem sobie z taki-

mi skrótami myślowymi poradzi, to jednak jest to bardzo duże

utrudnienie dla kogoś, kto dopiero uczy się programować lub

uczył się języka VBA samodzielnie (czyli tworząc własne progra-

my, ale zarazem nie mając żadnego kontaktu z niezbędną szerszą

wiedzą o programowaniu).


Wprowadzenie

Dzięki takiemu układowi materiału oraz kompletnym omówie-

niom przekazywanej wiedzy masz zatem okazję nauczyć się

programowania – umiejętności bardzo przydatnej w dzisiejszym

świecie wypełnionym przez komputery – lub zwiększyć swoją

sprawność w tym zakresie.

[…]

O autorze

Autor używa języka VBA od ok. roku 1998 – do automatyzacji

przetwarzania długich dokumentów MS Word, a w ostatnich kil-

ku latach również prac wykonywanych za pośrednictwem MS

Excel oraz MS Access. Posiada też m.in. wykształcenie w zakresie

inżynierii oprogramowania oraz bogate doświadczenie dydak-

tyczne w tym zakresie – prowadzi bowiem od wielu lat różnoro-

dne szkolenia w zakresie programowania oraz projektowania

systemów komputerowych.

Serdecznie życzymy sukcesów w tworzeniu mniejszych i więk-

szych, ale jak najbardziej przydatnych w codziennej pracy progra-

mów – Autor i Wydawnictwo.

[…]


Nawigacja względem aktywnej komórki

Bardzo często nasz program ma wykonywać operacje związane

z przechodzeniem po kolejnych komórkach arkusza – np. od

pierwszej komórki z danymi (zaznaczonej przez użytkownika

arkusza) i w dół aż do ostatniego wiersza z danymi (jak robiliśmy to przy omawianiu pętli Do … Loop) albo też sformatować

komórki z tytułami kolumn, przechodząc w prawo aż do ostatniej

komórki zawierającej tytuł. Może to dotyczyć też przechodzenia

o jedną komórkę w wybranym kierunku (np. w górę lub w lewo) –

zarówno wewnątrz pętli, jak też poza nią.

W takich i jeszcze innych sytuacjach możemy skorzystać przede

wszystkim z możliwości nawigacji względem aktywnej komórki.

Mechanizm ten zaś wykorzystuje przede wszystkim właściwość Offset (określającą wielkość i kierunek przesunięcia w arkuszu)

i metodę Activate (uaktywniającą komórkę zgodnie z podanymi

parametrami przesunięcia).

Właściwość „Offset”

Ang. offset = ‘pozycja, przesunięcie’. Jest to właściwość określa-

jąca wielkość przesunięcia (określaną za pomocą liczby wierszy

oraz kolumn) względem komórki, której właściwość ta dotyczy.

Ważna jest tutaj przede wszystkim:

• kolejność parametrów – zawsze najpierw podawana jest ilość

wierszy, a dopiero potem ilość kolumn1,

• kierunek przesunięcia – podawany za pomocą liczb całkowitych

naturalnych lub ujemnych.

1 Warto pamiętać, że jest to porządek dotyczący w języku VBA wszystkich obiektów o budowie tabelarycznej – nie tylko arkuszy kalkulacyjnych w MS Excel, ale też m.in. tabel w MS Word i tabel bazodanowych w MS Access.


Stąd schemat instrukcji dokonującej zmiany aktywnej komórki

będzie wyglądać następująco:ActiveCell.Offset(ileWierszy, ileKolumn).Activate

gdzie parametr:

• ileWierszy – jest liczbą naturalną (czyli całkowitą dodatnią,

jeśli przesunięcie ma nastąpić w dół1), całkowitą ujemną (jeśli

przesunięcie ma nastąpić w górę2) lub równą 0 (jeśli kolejna

aktywna komórka ma się znajdować w tym samym wierszu),

• ileKolumn – jest liczbą naturalną (czyli całkowitą dodatnią,

jeśli przesunięcie ma nastąpić w prawo3), całkowitą ujemną

(jeśli przesunięcie ma nastąpić w lewo4) lub równą 0 (jeśli

kolejna aktywna komórka ma się znajdować w tej samej

kolumnie).

Oba te parametry mogą też w razie potrzeby być zmiennymi

liczbowymi (nawet o takich samych nazwach, jak w przykładzie), zwykle typu Integer5.

Dla lepszego skojarzenia, jak poprawnie określać kierunki przesu-

nięcia, wystarczy zapamiętać następujący prosty schemat:

1 Czyli gdy kierunek przesunięcia ma być „naturalny” w kontekście arkusza, gdzie w wierszach zwykle poruszamy się z góry na dół.

2 Czyli gdy kierunek przesunięcia ma być „przeciwny do naturalnego” w kon-tekście wierszy arkusza.

3 Czyli gdy kierunek przesunięcia ma być „naturalny” w kontekście arkusza, gdzie w kolumnach zwykle poruszamy się od lewej do prawej.

4 Czyli gdy kierunek przesunięcia ma być „przeciwny do naturalnego” w kon-tekście kolumn arkusza.

5 Ponieważ ten typ obejmuje zarówno liczby całkowite dodatnie, jak i ujemne, zaś przesunięcia czasem mogą dotyczyć przejścia do określonej komórki i po-wrotu do komórki poprzedniej. Zależnie od sytuacji może to być oczywiście również typ Byte lub Long.



Metoda „Activate”

Ang. activate = ‘uaktywnij’. Jest to metoda uaktywniająca wskaza-

ny obiekt. Jeśli zatem aktywna jest komórka A1, zaś wykonamy

instrukcję uaktywnienia komórki leżącej o jeden wiersz w dół,

aktywna stanie się komórka A2 (i odwołanie za pomocą ActiveCell będzie się od tego momentu odnosić właśnie do

komórki A2).

Granice arkusza

Przy „chodzeniu” po komórkach arkusza trzeba też pamiętać, że

arkusz ma swoje granice, poza które nie wolno wychodzić. Próba

przejścia poza ostatni wiersz lub ostatnią kolumnę, ale też przed

pierwszy wiersz lub pierwszą kolumnę, zakończy się błędem –



z punktu widzenia naszego programu w VBA poza tymi granicami

nie ma komórek, do których można przejść.

Uaktywnianie komórek a użycie klawiszy kierunkowych

Wykonanie np. instrukcji ActiveCell.Offset(1, 0).

Activate na pierwszy rzut oka wygląda jak efekt naciśnięcia na

klawiaturze komputera strzałki kierunkowej „w dół”. Jednak

wykonując instrukcje zapisane w naszych programach w języku

VBA, korzystamy z dużo mocniejszych narzędzi, niż te, które ma

do dyspozycji użytkownik samej tylko aplikacji MS Excel.

W przypadku instrukcji uaktywniania komórek w VBA porusza-

my się przede wszystkim po arkuszu w pamięci operacyjnej

komputera. Zatem jeśli jakiś wiersz (lub wiersze) są ukryte i nie

widać ich na arkuszu pokazywanym na ekranie monitora po

naciśnięciu klawisza ze strzałką kierunkową „przeskakujemy”

przez nie (nad nimi) na ekranie. Jeśli jednak wykonamy

instrukcję uaktywnienia kolejnej komórki w dół z poziomu VBA,

zawsze przejdziemy do komórki w następnym wierszu (nawet

jeśli ten wiersz jest ukryty). Świadomość tego faktu pozwoli

uniknąć niemiłych niespodzianek w niektórych sytuacjach.

Aby przejść do innej komórki

• Wpisz np.:Sub zejdzOKomorkeWDol() If ActiveCell.Row < ActiveSheet.Rows.Count Then ActiveCell.Offset(1, 0).Activate Else MsgBox ("Ostatni wiersz arkusza.") End IfEnd Sub



Instrukcja Znaczenie

If ActiveCell.Row _

< ActiveSheet.Rows.Count Then

‘Jeśli aktywna komórka

znajduje się co najwyżej

w przedostatnim wierszu,

wtedy…’ (Jest to zabezpie-

czenie przed przekrocze-

niem dolnej krawędzi

arkusza.)

ActiveCell.Offset(1, 0).Activate ‘Uaktywnij komórkę leżącą

o 1 wiersz w dół, w tej samej

kolumnie.’

Else

MsgBox…

‘W przeciwnym wypadku

(czyli gdy aktywna jest ko-

mórka w ostatnim wierszu),

wyświetl tylko komunikat

z ostrzeżeniem.’

Stan arkusza przed i po uruchomieniu procedury

Wskazówki

• Właściwość Count oznacza liczbę elementów w kolekcji, której

dotyczy. W powyższym przykładzie oznacza zatem liczbę



wierszy (kolekcja Rows) w aktywnym arkuszu (obiekt

ActiveSheet), a zarazem numer ostatniego wiersza w arku-

szu (wiersze są numerowane od 1).

Przyspieszenie działania programów

Fakt, że z poziomu VBA mamy dostęp do arkusza w jego „praw-

dziwej” postaci w pamięci operacyjnej (a nie tylko do widoku

arkusza pokazywanego na ekranie), ma jeszcze jedną ważną kon-

sekwencję. Każda instrukcja uaktywnienia kolejnej komórki jest

wykonywana nie tylko w pamięci operacyjnej (gdzie wykonana

być musi), ale też jest pokazywana („rysowana”) na ekranie moni-

tora, który służy przede wszystkim do komunikacji z użytkowni-

kiem aplikacji. Po uruchomieniu zaś naszego programu (który ma

np. przejść przez setki tysięcy komórek, wykonując zadane przez

nas, a potrzebne użytkownikowi operacje), można by bez żadnej

szkody dla działania programu monitor wyłączyć – aż do momen-

tu zakończenia wykonywania programu (szczególnie jeśli ma on

pracować kilka czy nawet kilkanaście minut).

Wyłączać monitora oczywiście zwykle nie będziemy (bo najczęś-

ciej korzystamy w tym czasie z innych programów), ale będzie

wiele sytuacji, kiedy możemy tak napisać program, by zamiast

„chodzić” po komórkach arkusza (czyli zarazem owo przecho-

dzenie rysować na ekranie), „sięgać” do potrzebnych komórek

bezpośrednio w pamięci komputera (czyli z pominięciem działa-

nia karty graficznej i monitora). Takie rozwiązanie znacząco

przyspieszy działanie naszych programów – szczególnie jeśli

instrukcji uaktywniania komórek będzie bardzo dużo (tysiące lub

setki tysięcy). Przyspieszenie to może zaś zaoszczędzić nawet



połowę i więcej czasu działania programu, zatem warto o tym

pomyśleć (jakkolwiek w niektórych sytuacjach nieco skomplikuje

to kod naszego programu). Przykład takiego rozwiązania jest

przedstawiony w poniższej procedurze.

Aby pobrać wartość z komórki poniżej

• Wpisz wewnątrz procedury np. instrukcję:MsgBox (ActiveCell.Offset(1, 0).Value)

Dane w arkuszu i efekt działania

Wskazówki

• Zadziała to szybciej niż konstrukcja ActiveCell.Offset

(1,0).Activate, ponieważ nie wymaga zmiany zaznaczenia

komórki na ekranie.

• W analogiczny sposób można skorzystać z innych właściwości

lub metod wskazanej komórki.

• Próba sięgnięcia poza granice arkusza (przed pierwszy wiersz

lub kolumnę albo za ostatni wiersz lub kolumnę) wywoła błąd –

podobnie jak przy próbie przechodzenia do kolejnej komórki

z przykładów wcześniejszych.

[…]


Skorowidz

Skorowidz

Numer strony zapisany pogrubionym pismem oznacza obszer-

niejszy (czasem kilkustronicowy) opis, ważniejsze objaśnienie lub

typowy przykład użycia.

funkcja CBool .............................268CByte .............................268CCur ...............................268CDate .....................271, 272CDbl ...............................268Chr .........................122, 123CInt ................................268CLng ..............................268CStr ........................268, 273InputBox .........................273InStr .......................138, 139IsDate .....................270, 272IsNumeric .......141-143, 270LCase ......................137, 138Left .................................128Len .................126, 127, 168LTrim .............................134Mid .........................130-132Right ......................129, 130RTrim .............................134Trim ........................132-134UCase .....................135, 136

instrukcja Case ........................162, 163

Case Else .......162, 164, 165Case Is ...........162-164, 166Else ..................155-157, 160ElseIf ......................158-160End If .....................150, 151End Select ..............162, 164Exit .......146, 192, 193, 195,

196Exit Do ...........................194Exit For ..........................194Exit Function .................194Exit Sub ..........................194If … Then ......145, 146, 148,

150-152, 155, 156, 158Loop ................184, 186, 187Next .........169-171, 180, 182Select Case .....145, 160-162,

166Step ..........................171-173To ...........................169, 170Wend ......................190, 191

kolekcja Cells .......208, 238, 252-256,

258



Columns …....209, 241-243, 250-252

Rows .......231, 232, 241, 245, 246, 251, 252

Sheets .............................206Workbooks ......................257

metoda Activate . 186, 187, 228, 229-

231, 234AutoFit .244-247, 250, 251Clear ......................221, 225ClearComments .....221, 225ClearContents …...201, 205,

221, 222, 225ClearFormats ..................221ClearNotes ......................221ClearOutline . 222, 226, 237InputBox .....259, 260, 262,

263, 264MsgBox .....................83, 84Select ...236-240, 241, 242,

243, 245, 246, 250, 251

obiekt ActiveCell ........….....186-188,

203, 204, 205, 210, 228, 229, 231, 233, 237

ActiveSheet . 203, 204, 205, 209, 231, 232, 241, 253, 254, 258

ActiveWorkbook . 203, 204, 205

Application ............203, 257Column ...................209, 241Range ....235, 236, 237-240Row ................................241Selection .......208, 236, 238,

242, 245, 253-255, 256, 258

ThisWorkbook ...............204

operator And ...........101, 153-155, 184Is ...................................100Like ................................100Mod ..................................97Not ..................................101Or .............101, 153-155, 184XOR ................................102^ ......................................97- ......................................97* ......................................97/ ......................................97\ ......................................97& ...............98, 99, 117, 118+ ......................................97< ...................................100<= ..................................100<> ..................................100= ...................................100> ...................................100>= ..................................100

panel Immediate ..............105-107


Skorowidz

Locals ......................112, 113Watches .........................109

pętla Do .........146, 182, 183, 186,

188-190, 191, 194, 210, 237

For . 146, 166-172, 174, 175, 177, 194, 210

For Each ..146, 177-181, 194, 210

While ......146, 190, 191, 194

słowo kluczowe Binary .............................144Compare .........................144Const ................................82Database .........................144Debug.Assert .................108Debug.Print ...........105-108Dim ............................81, 82End Function ....................73End Sub ............................73Explicit ......................79, 80Function ...........................73Option ...............79, 80, 144Sub ....................................73Text ................................144Until ........183-185, 188, 189vbCr ................................123vbCrLf ....................120, 121vbLf ................................123

While .. . .183-185, 189, 190

typ danych Boolean ............................88Byte ..................................87Currency ..........................89Date ..................................89Double .............................88Integer ..............................87Long .................................87Object ...............................88Single ...............................88String ...............................88Variant .............................89

właściwość Address …..222, 223, 226,

237, 252-255, 258Column ........223, 224, 226Count . .231, 232, 249, 250,

252Hidden .......243, 244, 250,

251Offset ..186, 187, 227, 228,

230, 231, 233Row ..............223, 224, 226RowHeight . . .247, 248, 251Style 205, 217, 219, 225,

238Text ...............215, 216, 225Value …..186-188, 211-214,

216, 225, 233


Zintegrowany kurs języka VBA – cz. 1 · PDF fileDariusz Pietrzyk Zintegrowany kurs...

Documents

Transcript of Zintegrowany kurs języka VBA – cz. 1 · PDF fileDariusz Pietrzyk Zintegrowany kurs...