Excel VBA Loops-pro každý, pro další, Do While, vnořené & více
Chcete-li efektivně pracovat ve VBA, musíte pochopit smyčky.
smyčky umožňují opakovat blok kódu nastavený počet opakování nebo opakovat blok kódu na každém objektu v sadě objektů.
nejprve vám ukážeme několik příkladů, které vám ukážou, jaké smyčky jsou schopny. Pak vás naučíme vše o smyčkách.
VBA smyčka rychlé příklady
pro každou smyčku
pro každou smyčku smyčky přes každý objekt v kolekci, například každý list v sešitu nebo každá buňka v rozsahu.
Smyčka Přes všechny Listy v Sešitu
Tento kód bude procházet všechny listy v sešitu, odkrytí každého listu:
|
1
2
3
4
5
6
7
8
|
Sub LoopThroughSheets()
Dim ws As Worksheet
Pro Každou ws V Listech
ws.Visible = True
End Sub
|
procházet Všechny Buňky v Rozsahu
Tento kód se smyčka přes oblast buněk, testování, pokud hodnota buňky je negativní, pozitivní, nebo nula.
Pro Další Smyčky
Další typ Smyčky „Pro“ je Pro Další Smyčka. Smyčka pro další umožňuje smyčku přes celá čísla.
Tento kód bude procházet celá čísla 1 až 10, zobrazování každý se zprávou:
|
1
2
3
4
5
6
|
Sub ForLoop()
Dim i As Integer
Pro i = 1 Až 10
MsgBox jsem
End Sub
|
Dělat, Zatímco Smyčky
Dělat, Zatímco Smyčky, smyčky a zároveň je splněna podmínka. Tento kód bude také smyčka přes celá čísla 1 až 10, zobrazující každý s pole zprávy.
|
1
2
3
4
5
6
7
8
|
Sub DoWhileLoop()
Dim n As Integer
, n = 1
Při n < 11
MsgBox n
n = n + 1
Opakování
End Sub
|
until
Naopak, Proveďte, dokud smyčky nebudou smyčky, dokud nebude splněna podmínka. Tento kód dělá totéž jako předchozí dva příklady.
|
1
2
3
4
5
6
7
8
|
Sub DoUntilLoop()
Dim n As Integer
, n = 1
Zpět, Dokud n >= 10
MsgBox n
n = n + 1
Opakování
End Sub
|
Budeme diskutovat o tom níže, ale při vytváření Do While nebo Do do smyček musíte být velmi opatrní, abyste nevytvořili nikdy nekončící smyčku.
VBA Loop Builder
Toto je snímek obrazovky „Loop Builder“z našeho prémiového doplňku VBA: AutoMacro. Loop Builder umožňuje rychle a snadno vytvářet smyčky pro smyčku přes různé objekty nebo čísla. Můžete provádět akce na každém objektu a / nebo vybrat pouze objekty, které splňují určitá kritéria.
doplněk obsahuje také mnoho dalších programátorů kódu, rozsáhlou knihovnu kódů VBA a sortiment kódovacích nástrojů. Je to nutné pro každého vývojáře VBA.
nyní pokryjeme různé typy smyček do hloubky.
VBA Pro Další Smyčky
cyklus For Syntaxe
Pro Další Smyčka umožňuje opakování bloku kódu zadaný počet krát. Syntaxe je:
|
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-celočíselná proměnná použitá k počítání
- Start-počáteční hodnota (např. 1)
- End-koncová hodnota (např. 10)
- – umožňuje počítat každé n celé číslo namísto každého 1 Celé číslo. Můžete také jít obráceně se zápornou hodnotou (např. Krok -1)
- – kód, který bude opakovat
- další-závěrečné prohlášení pro další smyčku. Počítadlo můžete zahrnout nebo ne. Důrazně však doporučuji zahrnout počítadlo, protože usnadňuje čtení vašeho kódu.
Pokud je to matoucí, nebojte se. Přezkoumáme některé příklady:
počítat do 10
Tento kód bude počítat do 10 pomocí smyčky pro-Next:
|
1
2
3
4
5
6
7
8
|
Sub ForEach_CountTo10()
Dim n As Integer
Pro n = 1 Až 10
MsgBox n
n
End Sub
|
Krok Smyčky
Počítat do 10 – Pouze sudá Čísla
kód bude počítat do 10 pouze počítání sudých čísel:
|
1
2
3
4
5
6
7
8
|
Sub ForEach_CountTo10_Even()
Dim n As Integer
Pro n = 2 Až 10 Krok 2
MsgBox n
n
End Sub
|
Všimněte si, že jsme přidali „Krok 2“. To říká smyčce For, aby „prošla“ počítadlem o 2. Můžeme také použít zápornou hodnotu kroku ke kroku obráceně:
pro krok smyčky inverzní
odpočítávání od 10
Tento kód bude odpočítávat od 10:
|
1
2
3
4
5
6
7
8
9
|
Sub ForEach_Countdown_Inverse()
Dim n As Integer
Pro n = 10: 1 Step -1
MsgBox n
n
MsgBox „Lift Off“
End Sub
|
Odstranit Řádky pokud je buňka prázdná
nejčastěji jsem použil záporný krok Pro-Loop pro smyčku přes rozsahy buněk, mazání řádků, které splňují určitá kritéria. Pokud smyčku z horních řádků do dolních řádků, jak budete mazat řádky budete zkazit čítač.
Tento příklad bude odstranit řádky s prázdné buňky (počínaje spodní řádek):
Vnořené For Smyčky
můžete „hnízdo“ jeden Pro Smyčky do druhé Smyčky For. Pro vytvoření násobící tabulky použijeme vnořené smyčky:
|
1
2
3
4
5
6
7
8
9
10
11
|
Sub Nested_ForEach_MultiplicationTable()
Dim řádku Jako celé Číslo, kol Jako celé Číslo
Pro řádek = 1 Do 9
Pro col = 1 Do 9
Buňky(řádek + 1, col + 1).Hodnota = řádek * sloupec
Druhý sloupec
Další řádek
End Sub
|
Konec
Výstup Pro prohlášení umožňuje opustit a Pro Další smyčku ihned.
Obvykle byste použili Exit For spolu s příkazem If a ukončili smyčku For Next, pokud je splněna určitá podmínka.
můžete například použít smyčku For k nalezení buňky. Jakmile je tato buňka nalezena, můžete ukončit smyčku, abyste urychlili kód.
Tento kód bude procházet řádky 1 do 1000, hledal „chyba“ ve sloupci a. Pokud je nalezen, bude kód vyberte buňku, vás upozorní na nalezené chyby, a výstupní smyčky:
Důležité: V případě Vnořených Smyček, Výstup Pro pouze ukončí aktuální cyklus For, ne všechny aktivní Smyčky.
Pokračovat pro
VBA nemá příkaz „pokračovat“, který se nachází ve Visual Basic. Místo toho budete muset použít „Exit“.
VBA pro každou smyčku
VBA pro každou smyčku bude procházet všemi objekty v kolekci:
- Všechny buňky v rozsahu
- Všechny listy v sešitu
- Všechny tvary v listu
- Všechny otevřené sešity
můžete také použít Vnořené Pro Každé Smyčky:
- Všechny buňky v řadě na všechny listy
- Všechny tvary na všechny listy
- Všech listů ve všech otevřených sešitech
- a tak dále…
syntaxe je:
|
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.Odemknout „heslo“
Další ws
End Sub
|
Pro Každý Otevřený Sešit
Tento kód uložte a zavřete všechny otevřené sešity:
|
1
2
3
4
5
6
7
8
9
|
Sub ForEachWB_inWorkbooks()
Dim wb As Workbook
Pro Každou wb V Sešitech
wb.Zavřít SaveChanges:=True
Další wb
End Sub
|
Pro Každý Tvar v Listu
Tento kód bude odstranit všechny tvary v aktivní list.
|
1
2
3
4
5
6
7
8
9
|
Sub ForEachShape()
Dim shp Jako je Tvar
Pro Každý shp V ActiveSheet.Tvary
shp.Odstranit
Další shp
End Sub
|
Pro Každý Tvar v Každém Listu v Sešitu
můžete také hnízdo Pro Každé Smyčky. Zde budeme procházet všemi tvary ve všech pracovních listech v aktivním sešitu:
|
1
2
3
4
5
6
7
8
9
10
11
|
Sub ForEachShape_inAllWorksheets()
Dim shp, Jako je Tvar, ws As Worksheet
Pro Každou ws V Listech
Pro Každý shp V ws.Tvary
shp.Odstranit
Další shp
Další ws
End Sub
|
Pro Každého – je-LI Smyčka
Jak jsme se již zmínil dříve, můžete použít příkaz if uvnitř smyčky, provádění akcí pouze tehdy, pokud určitá kritéria splněna.
tento kód skryje všechny prázdné řádky v rozsahu:
VBA Do While Loop
VBA Do While and Do Until (viz další část) jsou velmi podobné. Budou opakovat smyčku, zatímco (nebo dokud) je splněna podmínka.
smyčka Do While opakuje smyčku, zatímco je splněna podmínka.
Tady je to Dělat, Zatímco Syntaxi:
|
1
2
3
|
, Zatímco Stav
Opakování
|
Kde:
- Stav – stav k testování
- – kód bloku opakovat
můžete také nastavit Dělat, Zatímco smyčka s Podmínkou na konci cyklu:
|
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()
Dim n As Integer
, n = 1
Při n < 11
MsgBox n
n = n + 1
Opakování
End Sub
|
smyčka zatímco
nyní spustíme stejný postup, kromě toho, že podmínku přesuneme na konec smyčky:
|
1
2
3
4
5
6
7
8
|
Sub DoLoopWhile()
Dim n As Integer
, n = 1
MsgBox n
n = n + 1
Loop While n < 11
End Sub
|
VBA Udělat, Dokud Smyčky
Zpět, Dokud Smyčky budou opakovat smyčku, dokud nebude splněna určitá podmínka. Syntaxe je v podstatě stejné jako Dělat, Zatímco smyčky:
|
1
2
3
|
Do Stavu
Opakování
|
a podobně stav může jít na začátku nebo na konci smyčky:
|
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()
Dim n As Integer
, n = 1
Zpět, Dokud n > 10
MsgBox n
n = n + 1
Opakování
End Sub
|
Smyčka, Dokud
Tato Smyčka Do smyčky bude počítat do 10:
|
1
2
3
4
5
6
7
8
|
Sub DoLoopUntil()
Dim n As Integer
, n = 1
MsgBox n
n = n + 1
Smyčka, Dokud n > 10
End Sub
|
Exit Dělat Smyčky
Podobně pomocí Exit Pro ukončení Smyčky For, můžete použít Exit Udělat příkaz exit do Loop okamžitě
|
1
|
Konec
|
Zde je příklad Výstupu:
Ukončit nebo přerušit Smyčku
Jak jsme se zmínili výše, můžete použít Exit nebo Exit Udělat pro ukončení smyčky:
|
1
|
Exit For
|
|
1
|
Exit Do
|
However, these commands must be added to your code before you run tvoje smyčka.
Pokud se pokoušíte „přerušit“ smyčku, která je právě spuštěna, můžete zkusit stisknout klávesu ESC nebo CTRL + Pause Break na klávesnici. To však nemusí fungovat. Pokud to nefunguje, budete muset počkat na ukončení smyčky nebo v případě nekonečné smyčky pomocí kláves CTRL + ALT + Delete vynutit uzavření aplikace Excel.
to je důvod, proč se snažím vyhnout dělat smyčky, je snazší náhodně vytvořit nekonečnou smyčku, která vás nutí restartovat Excel a potenciálně ztratit práci.
další příklady smyček
smyčka přes řádky
tím se projdou všechny řádky ve sloupci:
Smyčka Přes Sloupce
Tento bude smyčka přes všechny sloupce v řádku:
procházet Soubory ve Složce
Tento kód bude procházet všechny soubory ve složce, vytvářet seznam:
Smyčku Přes Pole
Tento kód bude smyčku přes pole ‚arrList‘:
|
1
2
3
|
Pro i = LBound(arrList) UBound(arrList)
MsgBox arrList(i)
|
funkce LBound dostane „dolní mez“ pole a UBound dostane „horní mez“.
smyčky v Access VBA
většina výše uvedených příkladů bude fungovat také v Access VBA. V aplikaci Access však procházíme spíše objektem sady záznamů než objektem rozsahu.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
Sub LoopThroughRecords()
On Error Resume Next
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
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
|