Aby efektywnie pracować w VBA, musisz rozumieć pętle.
Pętle umożliwiają powtórzenie bloku kodu określoną liczbę razy lub powtórzenie bloku kodu na każdym obiekcie w zestawie obiektów.
Najpierw pokażemy kilka przykładów, aby pokazać, do czego zdolne są pętle. Wtedy nauczymy Cię wszystkiego o pętlach.
Szybkie przykłady pętli VBA
Dla każdej pętli
Pętla For Each Loops przechodzi przez każdy obiekt w kolekcji, na przykład każdy arkusz w skoroszycie lub każdą komórkę w zakresie.
Przeprowadź pętlę przez wszystkie arkusze w skoroszycie
Ten kod przejdzie przez wszystkie arkusze w skoroszycie, odkrywając każdy arkusz:
| 12345678 | Podrzędne arkusze pętli()Dim ws As WorksheetDla każdego ws w arkuszach roboczychws.Widoczny = PrawdaNastępnyNapis końcowy |
Przeprowadź pętlę przez wszystkie komórki w zakresie
Ten kod przejdzie przez zakres komórek, sprawdzając, czy wartość komórki jest ujemna, dodatnia czy zerowa:
| 1234567891011121314 | Sub If_Loop()Dim Cell jako zakresDla każdej komórki w zakresie ("A2:A6")Jeśli Komórka.Wartość > 0 WtedyCell.Offset(0, 1).Value = "Dodatni"ElseIf Cell.Value < 0 WtedyCell.Offset(0, 1).Value = "Ujemna"W przeciwnym razieCell.Offset(0, 1).Value = "Zero"Zakończ, jeśliNastępna komórkaNapis końcowy |
Dla następnych pętli
Innym rodzajem pętli „For” jest pętla For Next. Pętla For Next pozwala na przechodzenie przez liczby całkowite.
Ten kod przejdzie przez liczby całkowite od 1 do 10, wyświetlając każdą z okienkiem komunikatu:
| 123456 | Sub ForLoop()Dim i jako liczba całkowitaDla i = 1 do 10MsgBox iDalej jaNapis końcowy |
Rób pętle
Do While Loops zapętli się, gdy spełniony zostanie warunek. Ten kod będzie również przechodzić w pętli przez liczby całkowite od 1 do 10, wyświetlając każdą z okienkiem komunikatu.
| 12345678 | Sub DoWhileLoop()Dim n jako liczba całkowitan = 1Rób, gdy n < 11MsgBox nn = n + 1PętlaNapis końcowy |
Rób do pętli
I odwrotnie, Do Until Loops będzie zapętlać się, dopóki warunek nie zostanie spełniony. Ten kod robi to samo, co poprzednie dwa przykłady.
| 12345678 | Sub DoUntilLoop()Dim n jako liczba całkowitan = 1Czy Do n >= 10MsgBox nn = n + 1PętlaNapis końcowy |
Omówimy to poniżej, ale musisz być bardzo ostrożny podczas tworzenia pętli Do While lub Do Until, aby nie utworzyć nigdy niekończącej się pętli.
Konstruktor pętli VBA
To jest zrzut ekranu „Konstruktora pętli” z naszego dodatku Premium VBA: AutoMacro. Loop Builder pozwala szybko i łatwo budować pętle, aby przechodzić przez różne obiekty lub liczby. Możesz wykonać akcje na każdym obiekcie i/lub wybrać tylko te obiekty, które spełniają określone kryteria.
Dodatek zawiera również wiele innych konstruktorów kodu, obszerną bibliotekę kodów VBA oraz zestaw narzędzi do kodowania. To pozycja obowiązkowa dla każdego programisty VBA.
Teraz szczegółowo omówimy różne typy pętli.
VBA dla następnej pętli
Składnia pętli
For Next Loop umożliwia powtórzenie bloku kodu określoną liczbę razy. Składnia to:
| 12345 | [Licznik Dim jako liczba całkowita]Dla licznika = od początku do końca [wartość kroku][Zrób coś]Dalej [Licznik] |
Gdzie pozycje w nawiasach są opcjonalne.
- [Licznik przyciemnienia jako długi] - Deklaruje zmienną licznika. Wymagane, jeśli Option Explicit jest zadeklarowane w górnej części modułu.
- Licznik - Zmienna całkowita używana do liczenia
- Początek - wartość początkowa (przykład 1)
- Kończyć się - wartość końcowa (przykład 10)
- [Wartość kroku] - Pozwala policzyć co n liczb całkowitych zamiast co 1 liczbę całkowitą. Możesz również iść w odwrotnej kolejności z wartością ujemną (np. Krok -1)
- [Zrób coś] - Kod, który się powtórzy
- Dalej [Licznik] - Oświadczenie zamykające pętlę For Next. Możesz dołączyć licznik lub nie. Jednak zdecydowanie zalecam włączenie licznika, ponieważ ułatwia to odczytanie kodu.
Jeśli to mylące, nie martw się. Przyjrzymy się kilku przykładom:
Policz do 10
Ten kod będzie liczony do 10 przy użyciu pętli For-Next:
| 12345678 | Sub ForEach_CountTo10()Dim n jako liczba całkowitaDla n = 1 do 10MsgBox nNastępna nNapis końcowy |
Dla kroku pętli
Policz do 10 - Tylko liczby parzyste
Ten kod będzie liczyć do 10, licząc tylko liczby parzyste:
| 12345678 | Sub ForEach_CountTo10_Even()Dim n jako liczba całkowitaDla n = 2 do 10 Krok 2MsgBox nNastępna nNapis końcowy |
Zauważ, że dodaliśmy „Krok 2”. To każe pętli For „przekroczyć” licznik o 2. Możemy również użyć ujemnej wartości kroku, aby przejść w odwrotnym kierunku:
Krok pętli - odwrotny
Odliczanie od 10
Ten kod będzie odliczał od 10:
| 123456789 | Sub ForEach_Countdown_Inverse()Dim n jako liczba całkowitaDla n = 10 do 1 Krok -1MsgBox nNastępna nMsgBox "Podnieś"Napis końcowy |
Usuń wiersze, jeśli komórka jest pusta
Najczęściej używałem ujemnego kroku For-Loop, aby przechodzić przez zakresy komórek, usuwając wiersze spełniające określone kryteria. Jeśli zapętlisz się od górnych rzędów do dolnych rzędów, podczas usuwania rzędów zepsujesz swój licznik.
Ten przykład usunie wiersze z pustymi komórkami (zaczynając od dolnego wiersza):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells()Dim n jako liczba całkowitaDla n = 10 do 1 Krok -1If Range("a" & n).Value = "" ThenZakres("a" & n).EntireRow.DeleteZakończ, jeśliNastępna nNapis końcowy |
Zagnieżdżone dla pętli
Możesz „zagnieździć” jedną pętlę For w innej pętli For. Użyjemy Nested For Loops do stworzenia tabliczki mnożenia:
| 1234567891011 | Sub Nested_ForEach_MultiplicationTable()Dim wiersz As Integer, col As IntegerDla rzędu = 1 do 9Dla kol = 1 do 9Komórki(wiersz + 1, kolumna + 1). Wartość = wiersz * kolumnaNastępny kolorNastępny rządNapis końcowy |
Wyjdź dla
Instrukcja Exit For umożliwia natychmiastowe wyjście z pętli For Next.
Zwykle używasz Exit For razem z instrukcją If, opuszczając pętlę For Next, jeśli zostanie spełniony określony warunek.
Na przykład możesz użyć pętli For, aby znaleźć komórkę. Po znalezieniu tej komórki możesz wyjść z pętli, aby przyspieszyć kod.
Ten kod przejdzie w pętli przez wiersze od 1 do 1000, szukając „błędu” w kolumnie A. Jeśli zostanie znaleziony, kod wybierze komórkę, ostrzeże Cię o znalezionym błędzie i zakończy pętlę:
| 12345678910111213 | Sub ExitFor_Loop()Dim i jako liczba całkowitaDla i = 1 do 1000If Range("A" & i).Value = "błąd" ThenZakres ("A" i i).WybierzMsgBox "Znaleziono błąd"Wyjdź dlaZakończ, jeśliDalej jaNapis końcowy |
Ważne: W przypadku pętli zagnieżdżonych polecenie Exit For powoduje wyjście tylko z bieżącej pętli For, a nie ze wszystkich aktywnych pętli.
Kontynuuj dla
VBA nie ma polecenia „Kontynuuj”, które można znaleźć w Visual Basic. Zamiast tego będziesz musiał użyć „Wyjdź”.
VBA dla każdej pętli
VBA For Each Loop przejdzie przez wszystkie obiekty w kolekcji:
- Wszystkie komórki w zakresie
- Wszystkie arkusze w skoroszycie
- Wszystkie kształty w arkuszu
- Wszystkie otwarte skoroszyty
Możesz także użyć opcji Zagnieżdżone dla każdej pętli, aby:
- Wszystkie komórki w zakresie we wszystkich arkuszach roboczych
- Wszystkie kształty we wszystkich arkuszach roboczych
- Wszystkie arkusze we wszystkich otwartych skoroszytach
- i tak dalej…
Składnia to:
| 123 | Dla każdego obiektu w kolekcji[Zrób coś]Dalej [Obiekt] |
Gdzie:
- Obiekt - Zmienna reprezentująca zakres, arkusz, skoroszyt, kształt itp. (np. rng)
- Kolekcja - Zbiór obiektów (np. Zakres("a1:a10")
- [Zrób coś] - Blok kodu do uruchomienia na każdym obiekcie
- Dalej [Obiekt] - Oświadczenie końcowe. [Object] jest opcjonalne, jednak zdecydowanie zalecane.
Dla każdej komórki w zakresie
Ten kod przejdzie przez każdą komórkę w zakresie:
| 123456789 | Sub ForEachCell_inRange()Przyciemnij komórkę jako zakresDla każdej komórki w zakresie("a1:a10")cell.Value = cell.Offset(0,1).ValueNastępna komórkaNapis końcowy |
Dla każdego arkusza w skoroszycie
Ten kod przejdzie przez wszystkie arkusze w skoroszycie, nie chroniąc każdego arkusza:
| 123456789 | Sub ForEachSheet_inWorkbook()Dim ws As WorksheetDla każdego ws w arkuszach roboczychws.Odbezpiecz „hasło”NastępnyNapis końcowy |
Dla każdego otwartego skoroszytu
Ten kod zapisze i zamknie wszystkie otwarte skoroszyty:
| 123456789 | Sub ForEachWB_inWorkbooks()Dim wb jako skoroszytDla każdego wb w skoroszytachwb.Close SaveChanges:=TrueNastępny wbNapis końcowy |
Dla każdego kształtu w arkuszu
Ten kod usunie wszystkie kształty z aktywnego arkusza.
| 123456789 | Sub dla każdego kształtu()Przyciemnij shp jako kształtDla każdego shp w ActiveSheet.Shapesshp.UsuńNastępny shpNapis końcowy |
Dla każdego kształtu w każdym arkuszu w skoroszycie
Możesz także zagnieżdżać pętle For Each. Tutaj przejdziemy przez wszystkie kształty we wszystkich arkuszach roboczych w aktywnym skoroszycie:
| 1234567891011 | Sub ForEachShape_inAllWorksheets()Dim shp jako kształt, ws jako arkusz roboczyDla każdego ws w arkuszach roboczychDla każdego shp w ws.Kształtyshp.UsuńNastępny shpNastępnyNapis końcowy |
Dla każdego - JEŚLI Pętla
Jak wspomnieliśmy wcześniej, możesz użyć instrukcji If w pętli, wykonując akcje tylko wtedy, gdy spełnione są określone kryteria.
Ten kod ukryje wszystkie puste wiersze w zakresie:
| 12345678910 | Sub ForEachCell_inRange()Przyciemnij komórkę jako zakresDla każdej komórki w zakresie("a1:a10")Jeśli komórka.Wartość = „” Wtedy _cell.EntireRow.Hidden = PrawdaNastępna komórkaNapis końcowy |
VBA Rób pętlę
VBA Do While i Do Until (patrz następna sekcja) są bardzo podobne. Będą powtarzać pętlę, dopóki (lub dopóki) warunek nie zostanie spełniony.
Pętla Do While powtórzy pętlę, gdy warunek zostanie spełniony.
Oto składnia Do While:
| 123 | Dopóki warunek[Zrób coś]Pętla |
Gdzie:
- Stan: schorzenie - Warunek do przetestowania
- [Zrób coś] - Blok kodu do powtórzenia
Możesz także skonfigurować pętlę Do While z warunkiem na końcu pętli:
| 123 | Robić[Zrób coś]Pętla podczas warunku |
Zademonstrujemy każdy z nich i pokażemy, czym się różnią:
Czy podczas
Oto przykład pętli Do While, którą zademonstrowaliśmy wcześniej:
| 12345678 | Sub DoWhileLoop()Dim n jako liczba całkowitan = 1Rób, gdy n < 11MsgBox nn = n + 1PętlaNapis końcowy |
Pętla podczas
Teraz uruchommy tę samą procedurę, z tą różnicą, że przeniesiemy warunek na koniec pętli:
| 12345678 | Sub DoLoopWhile()Dim n jako liczba całkowitan = 1RobićMsgBox nn = n + 1Pętla, gdy n < 11Napis końcowy |
VBA Do pętli
Do Until Loops będzie powtarzać pętlę, dopóki nie zostanie spełniony określony warunek. Składnia jest zasadniczo taka sama jak w przypadku pętli Do While:
| 123 | Czy dopóki warunek[Zrób coś]Pętla |
i podobnie warunek może pojawić się na początku lub na końcu pętli:
| 123 | Robić[Zrób coś]Pętla do warunku |
Rób dopóki
To robi Dopóki pętla będzie liczyć do 10, tak jak nasze poprzednie przykłady
| 12345678 | Sub DoUntilLoop()Dim n jako liczba całkowitan = 1Rób Do n > 10MsgBox nn = n + 1PętlaNapis końcowy |
Powtarzaj dopóki
Ta pętla do pętli będzie liczyć do 10:
| 12345678 | Sub DoLoopUntil()Dim n jako liczba całkowitan = 1RobićMsgBox nn = n + 1Pętla Do n > 10Napis końcowy |
Wyjdź z pętli
Podobnie jak w przypadku użycia Exit For do wyjścia z pętli For, użyj polecenia Exit Do, aby natychmiast wyjść z pętli Do
| 1 | Wyjście Wykonaj |
Oto przykład Exit Do:
| 123456789101112131415 | Sub ExitDo_Loop()Dim i jako liczba całkowitaja = 1Rób Dopóki > 1000If Range("A" & i).Value = "błąd" ThenZakres ("A" i i).WybierzMsgBox "Znaleziono błąd"Wyjście WykonajZakończ, jeślija = ja + 1PętlaNapis końcowy |
Zakończ lub przerwij pętlę
Jak wspomnieliśmy powyżej, możesz użyć Exit For lub Exit Do, aby wyjść z pętli:
| 1 | Wyjdź dla |
| 1 | Wyjście Wykonaj |
Jednak te polecenia muszą zostać dodane do kodu przed uruchomieniem pętli.
Jeśli próbujesz „przerwać” aktualnie uruchomioną pętlę, możesz spróbować nacisnąć WYJŚCIE lub KLAWISZ KONTROLNY + Przerwa na klawiaturze. Może to jednak nie zadziałać. Jeśli to nie zadziała, będziesz musiał poczekać na zakończenie pętli lub, w przypadku pętli nieskończonej, użyć KLAWISZ KONTROLNY + ALT + Kasować aby wymusić zamknięcie programu Excel.
Dlatego staram się unikać pętli Do, łatwiej jest przypadkowo utworzyć niekończącą się pętlę, zmuszającą do ponownego uruchomienia programu Excel, potencjalnie tracąc pracę.
Więcej przykładów pętli
Zapętlaj wiersze
Spowoduje to przejście przez wszystkie wiersze w kolumnie:
| 123456789 | Publiczna pętla podrzędna przez wiersze()Przyciemnij komórkę jako zakresDla każdej komórki w zakresie ("A:A")Ff cell.value "" następnie MsgBox cell.address &": " & cell.valueNastępna komórkaNapis końcowy |
Zapętlaj kolumny
Spowoduje to przejście przez wszystkie kolumny w rzędzie:
| 123456789 | Publiczna podrzędna pętla przez kolumny()Przyciemnij komórkę jako zakresDla każdej komórki w zakresie ("1:1")If cell.Value "" Then MsgBox cell.Address & ": " & cell.ValueNastępna komórkaNapis końcowy |
Zapętlaj pliki w folderze
Ten kod przejdzie przez wszystkie pliki w folderze, tworząc listę:
| 12345678910111213141516171819 | Podrzędne pliki pętli ()Dim FSO jako obiektDim ofFolder As ObjectPrzyciemnij plik jako obiektDim i jako liczba całkowitaSet ofFSO = CreateObject("Scripting.FileSystemObject")Ustaw oFolder = oFSO.GetFolder("C:\Demo)ja = 2Dla każdego pliku w folderze oFolder.FilesZakres("A" & i).value = oFile.Nameja = ja + 1Następny plikNapis końcowy |
Zapętlaj tablicę
Ten kod przejdzie przez tablicę „arrList”:
| 123 | For i = LBound(arrList) To UBound(arrList)MsgBox arrList(i)Dalej ja |
Funkcja LBound pobiera „dolną granicę” tablicy, a UBound pobiera „górną granicę”.
Pętle w programie Access VBA
Większość powyższych przykładów będzie działać również w programie Access VBA. Jednak w programie Access w pętli przechodzimy przez obiekt zestawu rekordów, a nie obiekt zakresu.
| 123456789101112131415161718 | Sub LoopThroughRecords()Przy błędzie Wznów DalejDim dbs jako baza danychPrzyciemnij najpierw jako zestaw rekordówUstaw dbs = Bieżąca baza danychUstaw rst = dbs.OpenRecordset("tblClients", dbOpenDynaset)Z pierwszym.PrzenieśOstatni.Najpierw przesuńCzy Do .EOF = PrawdaMsgBox (rst.Fields("NazwaKlienta")).PrzesuńDalejPętlaKończyć zpierwsze.ZamknijUstaw pierwsze = NicUstaw dbs = NicNapis końcowy |
