Pętle Wielokrotne

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela VBA.xlsm, tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji.

Przykłady dla wszystkich lekcji szkolenia Excel 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

Przykład 1.

(Arkusz: ‘Petle Wielokrotne 1’)

W wielu makrach wykorzystywane są numeryczne oznaczenia kolorów.

W arkuszu ‘Petle Wielokrotne 1’ komórki B3:K11 zostały pokolorowane tak samo jak wygląda podstawowa paleta kolorów dostępnych w Excelu 2013.

Poniższe makro wpisuje w komórki będące w zakresie od drugiej kolumny (B) do jedenastej (K) i w wierszach od 3 do 11 numer koloru jaki ma tło tej komórki.

Wykorzystano tu podwójną pętle For Next. Zewnętrzna pętla zmienia kolumny, wewnętrzna zmienia wiersze.

Komenda Cells(wiersz, kolumna) = Selection.Interior.ColorIndex wprowadza numer koloru do komórki.

Komenda ActiveCell.Offset(1, 0).Range("A1").Select przechodzi o linijkę niżej, a komenda:

ActiveCell.Offset(-5, 1).Range("A1").Select przechodzi o 1 kolumnę w prawo i 5 komórek w górę.

Przed uruchomieniem makra należy ustawić B3 jako aktywną komórkę.

Sub Podwojna_Petla_For_Next()

For kolumna = 2 To 11

For wiersz = 3 To 11

Cells(wiersz, kolumna) = Selection.Interior.ColorIndex

ActiveCell.Offset(1, 0).Range("A1").Select

Next wiersz

ActiveCell.Offset(-9, 1).Range("A1").Select

Next kolumna

End Sub

Przechodzenie komórkę w dół i w prawo pozwala łatwo zrozumieć działanie tego makra, bo zmiany dokonywane są w komórce która jest akurat aktywna.

Ale uaktywnianie komórek nie jest konieczne, główną jego zaletą jest łatwiejsze zrozumienia makr dla początkujących programistów.

W poniższej krótszej wersji tego makra uaktywnianie komórek zostało pominięte.

Wersja ta nie wymaga też uaktywnienia komórki B3 przed jego uruchomieniem.

Sub Podwojna_Petla_For_Next_v2()

For kolumna = 2 To 11

For wiersz = 3 To 11

Cells(wiersz, kolumna) = Cells(wiersz, kolumna).Interior.ColorIndex

Next wiersz

Next kolumna

End Sub

Oba makra dają identyczny wynik:

Jak widać niektóre odcienie tych samych kolorów mają ten sam numer, np. w komórkach B6 i B7.

Visual Basic for Applications rozpoznaje mniej kolorów niż Excel 2013.

Przykład 2.

(Arkusze: ‘Petle Wielokrotne 1’, ‘Petle Wielokrotne 2’)

W tej lekcji zapoznamy się z makrem, które zmienia wartości w arkuszu danych.

Chcielibyśmy ustalić targety dla dwudziestu przedstawicieli handlowych w Woj. Mazowieckim, na skutek dyskusji i uzgodnień udało się nam wypracować poniższą tabelę danych. Jednak targety dla wszystkich przedstawicieli w roku i każdym kwartale muszą się sumować dokładnie do targetu dla całego województwa. Ponieważ różnice są niewielkie kierownicy zespołów A, B i C zgodzili się aby rozdzielić je po równo pomiędzy ich podwładnych.

Wykorzystamy do tego makro, które różnicę z komórki M34 doda do targetu każdego z przedstawicieli handlowych tych zespołów.

Poniżej przedstawiam najprostszą wersję takiego makra, które w dalszej części tej lekcji będzie rozbudowywane.

Sub Target()

tablica_linii_Q = Array("zerowy", 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 20, 21, 22, 23, "koniec")

j = 1 'zmienia kolejne wiersze

b = -Range("m34").Value

Do Until tablica_linii_Q(j) = "koniec"

Range("m" & tablica_linii_Q(j)).Select

ActiveCell.Value = ActiveCell.Value + b

j = j + 1

Loop

End Sub

Po tytule makra zadeklarowano tablicę (Array) wraz ze wszystkimi jej elementami.

Elementy tej tablicy to numery wierszy w których są dane jakie planujemy zmieniać.

Tablica taka ma element zerowy któremu w tym przypadku przyznano wartość tekstową „zerowy” i który nie jest wykorzystywany w makrze.

Zmienna „j” służy do wskazywania kolejnych wierszy w kolumnie m, które mają być zmienione, dla pierwszej z komórek wynosi 1 co oznacza pierwszy element tablicy czyli wiersz 7.

Zmienna „b” przyjmuje wartość różnicy znajdującej się komórce M34 ale z odwrotnym znakiem. b = -Range("m34").Value

Składnia wykorzystanej tu pętli jest następująca:

Do Until WARUNEK

ZADANIE

Loop

Pętla ta wykonuje określone zadanie znajdujące się pomiędzy Do Until i Loop tak długo jak długo warunek podany po ‘Do Until’ będzie spełniony. W naszym przypadku tak długo aż zmienna j nie osiągnie wartości „koniec”.

W tym przykładzie Zadanie składa się z 3 linii:

Range("m" & tablica_linii_Q(j)).Select

Zadanie jakie jest wykonywane to przejście do komórki której adres składa się z komny M i wartości j z tablica_linii, np. dla j wynoszącego 3 będzie to adres M9.

ActiveCell.Value = ActiveCell.Value + b

W kolejnej linii zadania makro wprowadza do aktywnej komórki jej wartość zsumowaną ze zmienną b.

j = j + 1

W ostatniej linii zadania makro powiększa zmienną j o 1 tak aby przy kolejnym powtórzeniu makro zmieniło następną z wartości i docelowo mogło osiągnąć warrość zmiennej „koniec”

Ta najprostsza wersja tego makra zmieniać będzie tylko wartości dla kolumny M i będzie mało dokładna ponieważ wartości dla Zespołu D nie są zmieniane. Kilkukrotne uruchomienie makra znacznie poprawi dokładność, ale po co mielibyśmy samodzielnie uruchamiać makro kilkakrotnie jeżeli możemy to ująć w makrze?

Sub Target1()

tablica_linii_Q = Array("zerowy", 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 20, 21, 22, 23, "koniec")

i = 1 'powtórzenia w kolumnie

j = 1

Do Until i = 5

b = -Range("m34").Value

Do Until tablica_linii_Q(j) = "koniec"

Range("m" & tablica_linii_Q(j)).Select

ActiveCell.Value = ActiveCell.Value + b

j = j + 1

Loop

j = 1

i = i + 1

Loop

End Sub

W drugiej wersji tego samego makra dodano jedynie pętle, która pozwoli nam powtórzyć całą operację kilkukrotnie. Zmienna „i” to właśnie ilość powtórzeń całej operacji.

W naszym przykładzie pętla zmieniająca dane zostanie powtórzona 4 razy.

Proponuję uruchomienie obu makr za pomocą przycisku F8 i przyjrzenie się jak aktywna komórka krąży w pętlach.

W pliku VBA.xls znajdują się następne wersje tego makra które do 2 pętli przedstawionych powyżej dodają kolejne. Makro to w swojej ostatecznej wersji może w ciągu minuty wykonać pracę, którą człowiek wykonywałby przez cały dzień.

Kolejne wersje pokazane pokazuje także powyżej zamieszczony film.