5 października, 2021

Excel VBA Loops-for Each, for Next, Do While, Nested & More

aby działać skutecznie w VBA, musisz zrozumieć 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 Ci kilka przykładów, aby pokazać, do czego są zdolne pętle. Wtedy nauczymy Cię wszystkiego o pętlach.

pętla VBA szybkie przykłady

dla każdej pętli

dla każdej pętli pętla przez każdy obiekt w kolekcji, taki jak każdy arkusz roboczy w skoroszycie lub każda komórka w zakresie.

zapętl wszystkie arkusze robocze w skoroszycie

ten kod będzie zapętlać wszystkie arkusze robocze w skoroszycie, odkrywając każdy arkusz:

1
2
3
4
5
6
7
8
sub loopthroughsheets()
Dim ws jako arkusz roboczy
dla każdego ws w arkuszach roboczych
ws.Visible = True
Next
End Sub

Pętla przez wszystkie komórki w zakresie

ten kod zostanie zapętlony przez zakres komórek, testując, czy wartość komórki jest ujemna, dodatnia lub zerowa: ętla for next jest pętlą for next, która może być używana do tworzenia kolejnych pętli. Pętla for Next umożliwia zapętlanie przez liczby całkowite.

Ten kod będzie zapętlał się przez liczby całkowite od 1 do 10, wyświetlając każdą z nich pole z Komunikatem:

1
2
3
4
5
6
Sub forloop()
Dim i Jak liczba całkowita
Dla i = Od 1 Do 10
Pole wprowadzania i
Dalej
Kończę podtytuł

Wykonuję cykle

Wykonuję cykle, dopóki jest spełniony warunek. Kod ten będzie również przechodził przez liczby całkowite od 1 do 10, wyświetlając każdą z nich wraz z Komunikatem.

1
2
3
4
5
6
7
8
Sub dowhileloop()
n Jak Zmniejszyć liczbę całkowitą
n = 1
Zrobić, gdy n < 11
Pole do wiadomości n
n = n + 1
Cykl
Koniec nagłówka

Rób Cykle, aż

Odwrotnie, Do Until pętle będą zapętlać się, dopóki warunek nie zostanie spełniony. Ten kod robi to samo, co dwa poprzednie przykłady.

1
2
3
4
5
6
7
8
Sub dountilloop()
n Jak Zmniejszyć liczbę całkowitą
n = 1
Wróć do n >= 10
Pole do wiadomości n
n = n + 1
Cykl
Koniec nagłówka

– o tym poniżej, ale należy zachować szczególną ostrożność podczas tworzenia pętli do While lub Do Until, aby nie tworzyć nigdy kończącej się pętli.

VBA Loop Builder

To jest zrzut ekranu „Loop Builder” z naszego dodatku Premium VBA: AutoMacro. Konstruktor pętli pozwala szybko i łatwo tworzyć pętle do pętli między różnymi obiektami lub liczbami. Można wykonywać operacje na każdym obiekcie i / lub zaznaczać tylko obiekty spełniające określone kryteria.

dodatek zawiera również wiele innych kreatorów kodu, obszerną bibliotekę kodu VBA i zestaw narzędzi do kodowania. To must have dla każdego programisty VBA.

teraz omówimy różne rodzaje pętli w głębi.

VBA dla następnej pętli

dla składni pętli

pętla for Next pozwala na powtórzenie bloku kodu określoną liczbę razy. Składnia jest:

1
2
3
4
5
For Counter = Start to End
Next

Where the items in brackets are optional.

  • – Declares the counter variable. Required if Option Explicit is declared at the top of your module.
  • Counter – zmienna całkowita służąca do zliczania
  • Start – wartość początkowa (np. 1)
  • End-wartość końcowa (np. 10)
  • – pozwala policzyć każde n liczb całkowitych zamiast każdej 1 liczby całkowitej. Można również przejść w odwrotnej kolejności z ujemną wartością (np. Krok -1)
  • – kod, który powtórzy
  • Next-polecenie zamykające pętlę For Next. Możesz dołączyć licznik lub nie. Jednak zdecydowanie polecam włączenie licznika, ponieważ ułatwia to czytanie kodu.

Jeśli to jest mylące, nie martw się. Omówimy kilka przykładów:

policz do 10

Ten kod będzie liczył do 10 przy użyciu pętli For-Next:

1
2
3
4
5
6
7
8
Sub ForEach_CountTo10()
n Jak Zmniejszyć liczbę całkowitą
Dla n = Od 1 do 10
MsgBox n
Następny n
Koniec nagłówka

Dla etapu cyklu

Liczyć do 10 – Tylko liczby parzyste

To kod będzie liczył do 10 licząc tylko liczby parzyste:

1
2
3
4
5
6
7
8
Sub ForEach_CountTo10_Even()
n Jak Zmniejszyć liczbę całkowitą
Dla n = 2-10 Krok 2
Pole do wiadomości n
Następny n
Koniec nagłówka

Należy pamiętać, że dodaliśmy „Krok 2”. To mówi pętli For, aby „krok” przez licznik o 2. Możemy również użyć ujemnej wartości kroku do kroku odwrotnego:

dla kroku pętli – odwrócenie

odliczanie od 10

ten kod będzie odliczał od 10:

Usuń Wiersze jeśli komórka jest pusta

najczęściej używałem ujemnego kroku For-Loop, aby zapętlić zakresy komórek, usuwając wiersze spełniające określone kryteria. Jeśli zapętlisz od górnych wierszy do dolnych wierszy, podczas usuwania wierszy zepsujesz swój licznik.

Ten przykład usunie wiersze z pustymi komórkami (zaczynając od dolnego wiersza):

zagnieżdżona pętla For

Możesz „zagnieżdżać” jedną pętlę For wewnątrz drugiej pętli For. Użyjemy zagnieżdżonych pętli For do utworzenia tabliczki mnożenia:

1
2
3
4
5
6
7
8
9
Подзаголовок_countdown_inverse()
Zmniejsz n Jako liczba całkowita
Dla n = Od 10 Do Kroku 1 -1
Pole wprowadzania n
Następujący n
Pole wprowadzania „Zdjąć”
Koniec nagłówka

1
2
3
4
5
6
7
8
9
10
11
Sub Nested_ForEach_MultiplicationTable()
Przyciemnić linię Jako Liczba Całkowita, kolumny Jako Liczba Całkowita
Do wierszy = 1-9
Do kolumn = 1-9
Komórki (wiersz + 1, kolumna + 1).Value = row * col
Next col
Next row
End Sub

exit for

Instrukcja exit for umożliwia natychmiastowe wyjście z pętli for next.

zwykle używasz Exit For wraz z instrukcją If, wychodząc z pętli For Next, jeśli spełniony jest 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 będzie przechodził przez wiersze od 1 do 1000, szukając „error” w kolumnie A. Jeśli zostanie znaleziony, kod wybierze komórkę, powiadomi Cię o znalezionym błędzie i zakończy pętlę:

ważne: w przypadku zagnieżdżonych pętli For, Exit For kończy tylko bieżącą pętlę For, a nie wszystkie aktywne pętle.

Kontynuuj dla

VBA nie ma polecenia „Kontynuuj”, które znajduje się w Visual Basic. Zamiast tego musisz użyć „Exit”.

VBA dla każdej pętli

VBA dla każdej pętli zapętli się przez wszystkie obiekty w kolekcji:

  • wszystkie komórki w zakresie
  • wszystkie arkusze w skoroszycie
  • wszystkie kształty w arkuszu
  • wszystkie otwarte skoroszyty

Możesz również użyć zagnieżdżonych dla każdej pętli do:

  • wszystkie komórki w zakresie we wszystkich arkuszach
  • wszystkie kształty we wszystkich arkuszach
  • wszystkie arkusze we wszystkich otwartych skoroszytach
  • i tak dalej…

składnia jest:

1
2
3
For Each Object in Collection
Next

Where:

For Each Cell in Range

This code will loop through each cell in a range:

For Each Worksheet in Workbook

This code will loop through all worksheets in a workbook, unprotecting each sheet:

1
2
3
4
5
6
7
8
9
Sub ForEachSheet_inWorkbook()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Usuń „hasło”
następny ws
End Sub

dla każdego otwartego skoroszytu

ten kod zapisze i zamknie wszystkie otwarte skoroszyty:

1
2
3
4
5
6
7
8
9
подзаголовокwb_inworkbooks()
Przyciemnić wb Jak Skoroszyt
Dla Każdego wb W Pracy Książkach
wb.Zamknij zmiany zapisu:=True
następny WB
End Sub

dla każdego kształtu w arkuszu roboczym

ten kod usunie wszystkie kształty w aktywnym arkuszu.

1
2
3
4
5
6
7
8
9
Podtytuł()
Dim shp Jako Forma
Dla Każdego shp W Tabeli Aktywności.Figury
shp.100000000000000000000000000000000000000000 kształt w każdym arkuszu w skoroszycie

Możesz również zagnieżdżać dla każdej pętli. Tutaj będziemy przeglądać wszystkie kształty we wszystkich arkuszach w aktywnym skoroszycie:

1
2
3
4
5
6
7
8
9
10
11
Sub ForEachShape_inAllWorksheets()
Przyciemnić shp Jak Kształt, ws Jak Arkusz
Dla Każdego ws W Arkuszach Roboczych
Dla Każdego shp W ws.Formularze
shp.Delete
Next shp
Next ws
End Sub

dla każdej pętli IF

jak już wspomnieliśmy, można użyć instrukcji If wewnątrz pętla, wykonująca akcje tylko wtedy, gdy spełnione są określone kryteria.

ten kod ukryje wszystkie puste wiersze w zakresie:

VBA do While Loop

VBA do While I do Until (patrz następna sekcja) są bardzo podobne. Będą powtarzać pętlę, gdy (lub dopóki) warunek zostanie spełniony.

pętla do While powtarza pętlę, gdy spełniony jest warunek.

Oto składnia Do While:

1
2
3
do while condition
pętla

gdzie:

  • condition – warunek do przetestowania
  • – blok kodu do powtórzenia

można również ustawić pętlę do while z warunkiem na końcu pętli:

1
2
3
Do
Loop While Condition

We will demo each one and show how they differ:

Do While

Here is the Do While loop example we demonstrated previously:

1
2
3
4
5
6
7
8
Sub dowhileloop()
n Jak Zmniejszyć liczbę całkowitą
n = 1
Zrobić, gdy n < 11
Pole do wiadomości n
n = n + 1
Cykl
Koniec nagłówka

pętla podczas gdy

teraz wykonajmy tę samą procedurę, z tym że przeniesiemy warunek na koniec pętli:

1
2
3
4
5
6
7
8

Sub doloopwhile()
n Jak Zmniejszyć liczbę całkowitą
n = 1
Wstecz
MsgBox n
n = n + 1
Cykl, w czasie, gdy n < 11
Koniec nagłówka

VBA Wykonuje cykl, dopóki

Nie wróci do Pętle będą powtarzać pętlę aż do spełnienia określonego warunku. Składnia jest zasadniczo taka sama jak pętle do While:

1
2
3
wykonaj do stanu
pętla

i podobnie warunek może przejść na początku lub na końcu pętli:

1
2
3
Do
Loop Until Condition

Do Until

This do Until loop will count to 10, like our previous examples

1
2
3
4
5
6
7
8
Sub dountilloop()
n Jak Zmniejszyć liczbę całkowitą
n = 1
Wróć do n > 10
Pole do wiadomości n
n = n + 1
Cykl
Koniec nagłówka

Cykl Do

Ten cykl, dopóki cykl nie będzie się liczyć do 10:

wyjście z pętli do

podobnie jak w przypadku użycia Exit For do wyjścia z pętli For, należy użyć polecenia Exit Do natychmiastowego wyjścia z pętli do

1
2
3
4
5
6
7
8
Sub doloopuntil()
n Jak Zmniejszyć liczbę całkowitą
n = 1
Wstecz
MsgBox n
n = n + 1
Cykl do n > 10
Koniec nagłówka

wyjście do
1

oto przykład wyjścia do:

koniec lub przerwa pętli

jak wspomnieliśmy powyżej, możesz użyć wyjścia dla lub wyjścia do, aby zakończyć pętle:

1
Exit For
1
Exit Do

However, these commands must be added to your code before you run twoja pętla.

Jeśli próbujesz „złamać” aktualnie uruchomioną pętlę, możesz spróbować nacisnąć ESC lub CTRL + Pause Break na klawiaturze. Może to jednak nie zadziałać. Jeśli to nie zadziała, musisz poczekać na zakończenie pętli lub, w przypadku pętli bez końca, użyj CTRL + ALT + Delete, aby wymusić zamknięcie programu Excel.

dlatego staram się unikać pętli Do, łatwiej jest przypadkowo utworzyć nieskończoną pętlę zmuszającą do ponownego uruchomienia Excela, potencjalnie tracąc pracę.

więcej przykładów pętli

Pętla przez wiersze

To będzie pętla przez wszystkie wiersze w kolumnie:

Pętla przez kolumny

To będzie pętla przez wszystkie kolumny z rzędu:

Pętla przez pliki w folderze

ten kod będzie pętla przez wszystkie pliki w folderze, tworząc listę:

Pętla przez tablicę

ten kod będzie pętla przez tablica 'arrlist’:

1
2 związku z tym, że w tym przypadku nie jest to konieczne, należy pamiętać, że nie jest to konieczne, ponieważ w tym przypadku nie jest to konieczne, ponieważ w tym przypadku nie jest to konieczne.

funkcja lbound otrzymuje”dolną granicę”tablicy, a ubound otrzymuje”górną granicę”.

pętle w Access VBA

większość powyższych przykładów będzie również działać w Access VBA. Jednak w programie Access przepuszczamy obiekt Recordset, a nie Obiekt Range.

Dodatkowe cykliczne nagrywanie()
Przy błędzie Wznowić Następny
Przyciemnić bazy danych Jako bazy danych
Najpierw Przyciemnić Jak Zestaw rekordów
Ustaw dbs = Aktualna baza danych
Set rst = dbs.OpenRecordset(„tblClients”, dbOpenDynaset)
With rst
.MoveLast
.MoveFirst
Do Until .EOF = True
MsgBox (rst.Fields(„ClientName”))
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18