Webinaria: Biurowe narzedzia informatyczne | Ms excel91 | Vbarpa91

MS Excel – Pętle w kodzie – jak tworzyć i prawidłowo wykorzystać (VBA)

Udostępnij!

TRANSKRYPCJA

 

Witam serdecznie, nazywam się Paweł Niemyt, jestem trenerem Altkom Akademii, na co dzień zajmuję się szkoleniami z Excela, z VBA, z SQL-a, i chciałbym dzisiaj powiedzieć parę słów na temat pętli VBA w Excelu.

 

Może przejdźmy od razu do Excela no i teraz tak, jeżeli chodzi o VBA, wejdziemy sobie tylko do edytora, możemy wejść tu za pomocą skrótu alt+F11, to co wcześniej już dodałem moduł do naszego pliku, to mamy kilka rodzajów pętli.

 

Do czego służą w ogóle pętle? Zdarza się czasami, że musimy pewną czynność wykonać wiele razy. I właśnie w językach programowania, np. takich jak właśnie Visual Basic for Applications, czyli VBA, możemy zastosować pętlę. Pętli mamy kilka rodzajów; zaczniemy od takiej podstawowej, można powiedzieć, pętli klasycznej, która się nazywa pętlą For no i za pomocą tej pętli pokażemy jak one działają.

 

Czyli zrobimy sobie sub TestFor, czyli zrobimy sobie testowanie pętli For. No i teraz tak, jeżeli byśmy chcieli dodać jeden arkusz no to jest bardzo proste polecenie, możemy zrobić worksheets, kropka et, worksheet to jest kolekcja arkuszy, w naszym pliku mamy aż jeden arkusz w kolekcji, czyli tylko jeden arkusz, natomiast polecenie worksheet co powoduje? Doda nam kolejny arkusz, tu się pojawiają dwa arkusze, a jakbyśmy popatrzyli na Excela, to mamy dwa arkusze.

 

Wracamy tutaj no i co jeżeli tych arkuszy chcielibyśmy zrobić 10. Jeżeli to jest takie polecenie, które dodaje pojedynczy arkusz, no to żeby dodać 10 arkuszy, albo uruchomić 10 razy to makro albo skopiować worksheet et 10 razy, ale możemy też to zastosować w pętli. W jaki sposób?

 

No, za pomocą pętli. Przyda nam się oczywiście zmienna ponieważ tu u góry mamy wymuszenie deklaracji zmiennych, to polecenie, które mamy tutaj u góry Option Explicite powoduje, że musimy deklarować zmienne – więc zadeklaruję sobie zmienną licznik, as integer, typu całkowitego,  no i teraz tak, jak wygląda pętla For?

 

Zaczynamy od wartości początkowej licznika i dokońcowe, 10, czyli tu, 10, czyli będzie wykonywać się 10 razy i zamykamy pętlę nie żadnym tam nawiasem, klamrą, tylko poleceniem Next. Można też optymalnie przy Nextcie dopisać nazwę zmiennej, czyli tego licznika, który będzie się zwiększał.

 

No i teraz tak, pętla For działa tak, że trwa dopóki dopóty wartość licznika jest mniejsza lub równa 10. Czyli jeżeli zaczynamy od jedynki to ona będzie iterować tak długo póki nie osiągnie dziesiątki i domyślnie zwiększa nam o 1. Co to oznacza? Oznacza to, że jak mamy teraz dwa arkusze i to odpalę to mam arkuszy 12. One się tutaj alfabetycznie przekładają więc może to wyglądać nie do końca po kolei, natomiast jest 12.

 

Radzę nie przesadzać z ilością arkuszy bo możemy bardzo łatwo zawiesić Excela. Pętle są o tyle właśnie też ryzykowne, że możemy za dużo coś zrobić no i nasz Excel niestety się zawiesi. Co to oznacza? Oznacza to, że bardzo często przed uruchomieniem, szczególnie jeżeli pętlę stosujemy bądź jakieś warunki, powinniśmy plik zapisywać; bo jeżeli nie będziemy zapisywać, no to może nam się  Excel zawiesić, a czasami, jak już dobrze wiemy, jak Excel się zawiesi, to się nie odwiesi. Więc warto te pliki dość często zapisywać, przed każdym jakimś uruchomieniem plik zapisać.

 

I w taki oto sposób wygląda pętla For. Ja bym chciał pokazać kilka wersji jeszcze pętli For, żebyśmy zobaczyli jej cały przekrój, tylko już nie będę dodawał arkuszy bo to trochę będzie męczące jak będzie ich teraz coraz więcej, wcale nie tak łatwo się je już usuwa bo żeby usunąć arkusz to już trzeba wybrać, który chcemy usunąć, natomiast, jak widzimy, dodawać to możemy ich tam tyle ile tam chcemy.

 

No dobra, to będzie TestFor1 no i teraz tu zrobimy coś innego. Wyświetlimy sobie wyniki tu na dole; jest polecenie Debug Print, Debug Print to jest polecenie, które będzie wyświetlać mi zawartość tej zmiennej w okienku Immediate, jak sobie to odpalimy, no to zobaczymy, że mamy od 1 do 10. To spokojnie mogę sobie zrobić nawet do tysiąca, jak sobie odpalę no to widzimy, że tysiąc wyników się pojawia.

 

Natomiast jeżeli chodzi o pętlę For, to czy musimy zawsze zwiększać o jeden? Nie, możemy zwiększać też np. o 2, o 3, o 4, o ile tam chcemy, wystarczy, że podamy wartość kroku, jak zrobimy sobie tak dwa, ja oczywiście nie będę przesadzał z tym tysiącem, zrobię teraz do 20, to będzie nam się zwiększać o dwa.

 

Odpalamy i mamy 1,3,5 do 19. Jeżeli chcemy o 3, to wiadomo, będzie się zwiększać o 3. No dobra, czy można w takim razie zrobić pętlę, która zadziałała od tyłu, czyli będzie zmniejszać, czyli zadziała od 20 np. do 1. Oczywiście, że możemy to zrobić tylko też jest ważna rzecz jeszcze, trzeba podać wartość kroku.

 

Jak podamy tu wartość kroku, tu sobie zmienimy tylko nazwę, odpalimy, proszę bardzo, mamy pętlę, która ma 10,9,8,7, aż do 1, czyli dokładnie mamy pętlę, która wykonuje się do tyłu. Po co takie pętle? No bo czasem niektóre rzeczy łatwiej zrobić do przodu niż do tyłu. Bardzo dobry przykład w przypadku domyślnego dodawania arkuszy.

 

Zobaczmy, że domyślnie arkusze dodają się odwrotnie. Ponieważ zawsze nam się wstawia na lewo od aktywnego, jak się wstawia je po kolei i nazywa, no to jakbyśmy na przykład nazywali je miesiącami, to tu mamy styczeń i teraz jak wstawię luty to będzie przed styczniem, a potem jak wstawię marzec to będzie przed marcem.

 

Więc co można zrobić? Jeden ze sposobów to jest coś takiego żeby na przykład zacząć od marca; i mamy marzec, potem ustawiamy luty, a potem przed lutym styczeń. Czyli wystarczy zrobić pętlę, która zadziała odwrotnie; w tym momencie rozwiązuje nasz problem nie po kolei poukładanych arkuszy przy wstawianiu i nazywaniu więc możemy pętlę robić, ikrementować, dekrementować, jak wolimy, lub te wartości mogą być po przecinku.

 

Oczywiście, mogą być, tylko w naszym przypadku tu ograniczeniem jest ta zmienna, czyli intiger, bo to jest intiger, to jest intiger wszystko. Więc jeżeli chcielibyśmy zrobić na przykład żeby on nam zwiększał co pół, no to jak to jest intiger, to zobaczmy, że nie zwiększa co półtora ponieważ ma co dwa. No bo tam nam idzie w górę.

 

Natomiast jeżeli zmienimy tam A single’a, w pojedynczej precyzji, proszę bardzo, mamy co półtora. No i tak wygląda pętla. Oczywiście,  można te przykłady też mnożyć tam na komórkach, możemy wstawiać różne rzeczy, ale chciałbym jeszcze poruszyć tu temat dwóch pętli, a mianowicie pętli warunkowych i pętli, która działa nam na kolekcjach.

 

Więc tak, ja sobie dodam kolejny moduł i chciałbym powiedzieć teraz dwa słowa o tak zwanej pętli warunkowej. Co to jest pętla warunkowa? Pętla warunkowa to jest To, która działa tak, że nie działa tak jak pętla For, że określamy ile dokładnie ile razy ma działać, czy tam od 1 do 20, do 1000, do ilu tam chcemy, tylko to jest pętla, która działa dopóki dopóty albo działa póki warunek jest spełniony albo działa kiedy warunek nie jest spełniony.

 

I teraz tak, tu trzeba bardzo ostrożnie ponieważ wstępną warunkową tu jest pewne ryzyko, że możemy zrobić pętlę, która co zrobi, która na przykład zadziała zawsze, czyli będzie działać, jeśli warunek będzie zawsze spełniony, to będzie działać po prostu w nieskończoność. Tu jest ryzyko, że coś takiego możemy zrobić, warto na to zawsze zwrócić uwagę.

 

Dobra, zrobimy sobie pierwsze, proste makro. To jest Do, no i teraz tak, od razu posłużymy się jakimś przykładem żeby pokazać jak to działa. Zadeklaruję sobie zmienną wiersz, w której to będę przechowywać numer wiersza, as long, zaczniemy od wiersza równego 1, no i teraz Do while, zrobimy pętlę, która będzie działać dopóki dopóty wartość jest spełniona. Czyli robimy Do while.

 

Można też użyć Do until; wtedy działamy odwrotnie. Czyli tak, jeżeli chodzi o Sales, no to jest kolekcja wszystkich komórek w naszym arkuszu, możemy odwoływać się do tych elementów kolekcji podając numer komórki z kolekcji pojedynczy, albo, tak jak to zrobię, podam dwa parametry,  czyli podam numer wiersza, numer kolumny, w tym przypadku będzie się zaczynać od pierwszego wiersza od drugiej kolumny.

 

Nie trzeba podawać Value bo to jest domyślna właściwość dla obiektu ręcznej komórki, ale to zrobię i będzie działać dopóki dopóty wartość jest różna. Co tutaj dalej? Jeszcze dorzucimy sobie wiersz równa się wiersz + 1 i to sobie jeszcze skopiuję i tu wpiszę. O, możemy sobie jeszcze to dorzucić, na przykład będzie numer wiersza dorzucony dodatkowo.

 

No, i teraz jak to wygląda. Wygląda to tak; czym się różni ta pętla od pętli For? Nie mamy ile ma działać, czyli nie podajemy, że ma na przykład wypełnić 20 komórek. Będzie działać tak, że będzie nam wstawiać wartość pracownik, z numerem wiersza, kiedy? Wtedy kiedy wartość jest różna od puste. Jeżeli jest różna od puste, w tym momencie dodaje nam kolejny wiersz, sprawdza czy jest różny, jeżeli nie jest różny, no to dalej dodaje, natomiast jeżeli jest puste to wychodzi z pętli i wstawia nam pracownika.

 

No i teraz zobaczmy jak to będzie działać. Jeżeli ja bym sobie tu Excela wziął, tu sobie to usunę żeby było wszystko ładnie widać, to teraz odpalamy, ciach, wstawił nam Pracownik 1.  Co się teraz stanie? Jak odpalę jeszcze raz, będzie Pracownik 2,3,4. Za każdym razem ta pętla działa inną ilość razy. Jeżeli użyłem jej pierwszy raz to ona tak naprawdę ani razu się nie wykonała.

 

Zobaczmy jak ja to wykasuję; jeżeli bym chciał to zrobić na zasadzie trybu debugowania, czyli użyję skrótu f8, to wykonuję ją po kolei, wiersz jest równy 1, ani razu się nie wykonało. Dlaczego? Dlatego, że komórka jest pusta, jest pusta – nie spełnia warunków. Więc co od razu wyskakuje, dodaję pracownika. Koniec makro.

 

Jak odpalę drugi raz to makro, to w tym momencie znowu zaczyna od pierwszego i co, i tym razem jest coś. Czyli dodaję wiersz. Idzie jeszcze raz, już w drugim jest pusty, więc co, wychodzi. Czyli zobaczmy, że ta pętla za każdym razem zadziała inną ilość razy, w zależności od warunku, który tam ma. Czyli jeżeli jest spełniony to się wykonuje, jeżeli nie jest spełniony, to nie.

 

To jest bardzo fajny przykład, który nam pokazuje, który bardzo często można zastosować w różnych naszych aplikacjach, który nam służy do czego? Do sprawdzenia pierwszego wolnego wiersza. Czyli jeżeli byśmy chcieli zrobić na przykład jakiś import danych, na przykład z jakiejś bazy danych lub na przykład z kilku plików excelowych zrobić konsolidację, no to jak wrzucimy jedną tabelę to tą prostą pętlą możemy sprawdzić, która komórka jest pusta i wrzucić kolejną tabelę pod tą tabelą.

 

Tak, nie będziemy wiedzieć ile mają wierszy, to makro nam policzy i wstawi pierwszy wolny wiersz. No i do tego można właśnie użyć pętli Do. Tak wygląda pętla Do, oczywiście ona może mieć inne wariacje, może być Do while, może być Do until. Jeszcze jeden rodzaj pętli chciałbym pokazać.

 

Dodam kolejny moduł, jest to tak zwana pętla For Each. Ale pętla For Each, to jest pętla, która działa nam na kolekcję, to jest bardzo fajna pętla, możemy sobie użyć na przykład takiej bardzo prostej kolekcji, zrobimy sobie Sub Test Kolekcji, jest taka kolekcja Selection.

 

Kolekcja Selection jest to kolekcja, która nam określa wszystkie zaznaczone komórki. Mamy taką funkcję R&D, to jest funkcja Random, która losuje nam wartość z jakiegoś dowolnego przedziału, ja sobie zrobię Random razy 2000, czyli wylosuje mi liczbę w przedziale od zera do dwóch tysięcy, no, żeby jeszcze było ładnie to sobie ją przekonwertuję do Intigera żeby wszystkie były całkowite. To jest CIT, czyli Convert To Intiger, no i teraz zobaczmy co się stanie.

 

Ja sobie zaznaczę jakiś obszar, makro trzeba trochę przesunąć żeby było wszystko ładnie widać i odpalę, to wszędzie mam tę samę liczbę. Dlaczego? Dlatego, że ta cała kolekcja jest traktowana jako jeden element i to losowanie następuje dla całego elementu. To jest kolekcja iluś tam komórek, natomiast wrzucamy po prostu pojedyńczą wartość.

 

Natomiast co jeżeli byśmy chcieli wrzucić do tej kolekcji wartości, ale w każdej komórce inną, ale dla tych obszarów, które mamy określone? To mamy taką bardzo fajną pętlę, która działa w ten sposób, że jest w stanie działać po każdym elemencie kolekcji i to jest właśnie pętla For Each. No to teraz zrobimy ten sam przykład, ale z pętlą For Each.

 

Zobaczmy; sub, Test For Each, potrzebujemy zmienną, nowa komórka, as range, wszystkie komórki to są obiekty typu range, For Each, komórka, in, selection, next, to sobie wrzucamy do środka, nie będę pisał. Jeżeli czegoś nie trzeba pisać, to nie piszemy. Przedtem komórka, zrobimy wcięcie, tu wrzucamy, odpalamy to makro w tym samym miejscu i co? W każdej komórce mamy inne.

 

I teraz różnica od zwykłych pętli takich jak For, czy pętli Do jest taka, że nie musimy sprawdzać tych komórek, liczyć i tak dalej, tylko działa to na tej zasadzie, że dla każdej komórki w zaznaczonych komórkach co ma zrobić?  Wylosować Each. No i teraz ta komórka to co to jest za komórka? To jest pierwsza komórka kolekcji. Czyli tak, po tym jak ta pętla rusza dalej to przesunie się sama na drugą, bo to jest pętla For, ma zadziałać tak, że za każdym razem nam iteruje po każdym elemencie kolekcji.

 

Ile razy? Sama wie ile tych elementów kolekcji jest. Dlaczego? Dlatego, że kolekcja zna ilość elementów, które posiada, tak, czyli ona zadziała tak, że dla każdej komórki w zaznaczonych komórkach będzie nam losować wartość. Jak to można wykorzystać? Możemy na przykład odwołać się do kolekcji wykresów w arkuszu i możemy zrobić tak, że dla każdego wykresu zmienić tło; i ile jest tych wykresów nie musimy wcześniej wiedzieć.

 

Jeżeli będzie ich 2 czy 20 nie ma znaczenia, nie muszę tego sprawdzać, wystarczy, że zrobię For Each, wykres, in, kolekcja wykresów, jak chcemy to zrobić. Możemy dzięki temu odwołać się do każdego elementu z osobna nie wiedząc albo nawet nie sprawdzając ile tych elementów jest; więc pętla For Each jest bardzo wygodna, bardzo przydatna, szczególnie jeśli pracujemy na komórkach, na arkuszu, skoroszytach, no to często się jej używa.

 

Chciałem tylko pokazać takie proste przykłady, rodzaje pętli jakie mamy; to nie jest wszystko oczywiście, o tym możnaby mówić i mówić. Ja dziękuję za uwagę i zapraszam na szkolenia Visual Basica w Excelu, a dowiemy się tam o wiele, wiele więcej. Dziękuję i do zobaczenia.