Smyčky Excel VBA - pro každou, pro další, do chvíle, vnořené a další

Chcete -li efektivně pracovat ve VBA, musíte porozumět smyčkám.

Smyčky vám umožňují opakovat blok kódu nastavený početkrát nebo opakovat blok kódu na každém objektu v sadě objektů.

Nejprve vám ukážeme několik příkladů, abychom vám ukázali, čeho jsou smyčky schopné. Poté vás naučíme vše o smyčkách.

Rychlé příklady smyčky VBA

Pro každou smyčku

Pro každou smyčku projděte každý objekt v kolekci, například každý list v sešitu nebo každou buňku v rozsahu.

Projděte si všechny pracovní listy v sešitu

Tento kód prochází všemi listy v sešitu a odkryje každý list:

12345678

Sub LoopThroughSheets ()Dim ws As WorksheetZa každé ws v pracovních listechws.Visible = TruedalšíEnd Sub

Projděte všechny buňky v dosahu

Tento kód bude procházet řadou buněk a testovat, zda je hodnota buňky záporná, kladná nebo nulová:

1234567891011121314

Sub If_Loop ()Dim Cell jako rozsahPro každou buňku v dosahu („A2: A6“)Pokud Cell.Value> 0 ThenCell.Offset (0, 1) .Value = "Pozitivní"ElseIf Cell.Value <0 PotomCell.Offset (0, 1) .Value = "Negativní"JinýCell.Offset (0, 1) .Value = "Nula"Konec IfDalší buňkaEnd Sub

Pro další smyčky

Dalším typem smyčky „For“ je smyčka For Next. Smyčka For Next Loop vám umožňuje procházet celá čísla.

Tento kód bude procházet celými čísly 1 až 10 a každé zobrazí se zprávou:

123456

Sub ForLoop ()Dim i As IntegerPro i = 1 až 10MsgBox iPříště jáEnd Sub

Dělejte smyčky

Smyčky Do While se budou smyčkovat, pokud je splněna podmínka. Tento kód bude také procházet celými čísly 1 až 10 a každé bude zobrazovat se zprávou.

12345678

Sub DoWhileLoop ()Dim n jako celé číslon = 1Do While n <11MsgBox čn = n + 1SmyčkaEnd Sub

Do Do Loops

A naopak, Do Do Loops se bude smyčka opakovat, dokud nebude splněna podmínka. Tento kód dělá totéž jako předchozí dva příklady.

12345678

Sub DoUntilLoop ()Dim n jako celé číslon = 1Proveďte do n> = 10MsgBox čn = n + 1SmyčkaEnd Sub

Budeme o tom diskutovat níže, ale musíte být velmi opatrní při vytváření smyček Do While nebo Do Before, abyste nevytvořili nikdy nekončící smyčku.

VBA Loop Builder

Toto je snímek obrazovky „Loop Builder“ z našeho doplňku Premium VBA: AutoMacro. Loop Builder vám umožňuje rychle a snadno vytvářet smyčky pro procházení různými objekty nebo čísly. S každým objektem můžete provádět akce a/nebo vybrat pouze objekty, které splňují určitá kritéria.

Doplněk také obsahuje mnoho dalších tvůrců kódu, rozsáhlou knihovnu kódů VBA a řadu kódovacích nástrojů. To musí mít každý vývojář VBA.

Nyní pokryjeme různé typy smyček do hloubky.

VBA pro další smyčku

Pro syntaxi smyčky

Smyčka For Next Loop vám umožňuje opakovat blok kódu zadaný početkrát. Syntaxe je:

12345

[Dim Counter jako Integer]For Counter = od začátku do konce [hodnota kroku][Dělej něco]Další [počítadlo]

Kde položky v závorkách jsou volitelné.

• [Dim Counter as Long] - Deklaruje proměnnou čítače. Je vyžadováno, pokud je v horní části modulu deklarována možnost Explicit.
• Čelit - Celočíselná proměnná používaná k počítání
• Start - Počáteční hodnota (příklad 1)
• Konec - Koncová hodnota (Př. 10)
• [Hodnota kroku] - Umožňuje počítat každé n celé číslo místo každé 1 celé číslo. Můžete také jít opačně se zápornou hodnotou (např. Krok -1)
• [Dělej něco] - Kód, který se bude opakovat
• Další [počítadlo] - Závěrečné prohlášení ke smyčce For Next. Počitadlo můžete zahrnout nebo ne. Důrazně však doporučuji zahrnout čítač, protože usnadňuje čtení kódu.

Pokud je to matoucí, nebojte se. Zkontrolujeme několik příkladů:

Počítejte do 10

Tento kód se bude počítat do 10 pomocí smyčky For-Next:

12345678

Dílčí ForEach_CountTo10 ()Dim n jako celé čísloPro n = 1 až 10MsgBox čDalší nEnd Sub

Pro krok smyčky

Počítejte do 10 - pouze sudá čísla

Tento kód bude počítat do 10 pouze se sudými čísly:

12345678

Dílčí ForEach_CountTo10_Even ()Dim n jako celé čísloPro n = 2 až 10 Krok 2MsgBox čDalší nEnd Sub

Všimli jsme si, že jsme přidali „Krok 2“. To říká, že For Loop má „projít“ počítadlem o 2. Můžeme také použít zápornou hodnotu kroku pro krok zpět:

Pro krok smyčky - inverzní

Odpočítávání od 10

Tento kód bude odpočítávat od 10:

123456789

Sub ForEach_Countdown_Inverse ()Dim n jako celé čísloPro n = 10 až 1 Krok -1MsgBox čDalší nMsgBox „Lift Off“End Sub

Pokud je buňka prázdná, odstraňte řádky

Nejčastěji jsem používal negativní krok For-Loop k procházení řadami buněk a odstraňování řádků, které splňují určitá kritéria. Pokud smyčkujete z horních řádků do spodních řádků, při odstraňování řádků zpackáte svůj pult.

Tento příklad odstraní řádky s prázdnými buňkami (počínaje od spodního řádku):

12345678910

Sub ForEach_DeleteRows_BlankCells ()Dim n jako celé čísloPro n = 10 až 1 Krok -1Pokud Rozsah ("a" & n) .Hodnota = "" PotomRozsah („a“ & n). EnireRow.DeleteKonec IfDalší nEnd Sub

Vnořené pro smyčku

Jeden pro smyčku můžete „vnořit“ do jiného pro smyčku. K vytvoření multiplikační tabulky použijeme vnořené smyčky:

1234567891011

Sub Nested_ForEach_MultiplicationTable ()Dim řádek jako celé číslo, col jako celé čísloPro řádek = 1 až 9Pro col = 1 až 9Buňky (řádek + 1, sloupec + 1). Hodnota = řádek * sloupecDalší plkDalší řadaEnd Sub

Ukončit pro

Příkaz Exit For vám umožňuje okamžitě opustit smyčku For Next.

Obvykle použijete Exit For spolu s příkazem If, opuštění For Next Loop, pokud je splněna určitá podmínka.

Například můžete použít For Loop k nalezení buňky. Jakmile je tato buňka nalezena, můžete smyčku ukončit a zrychlit kód.

Tento kód prochází řádky 1 až 1000 a ve sloupci A hledá „chybu“. Pokud je nalezen, kód vybere buňku, upozorní vás na nalezenou chybu a smyčku opustí:

12345678910111213

Sub ExitFor_Loop ()Dim i As IntegerPro i = 1 až 1000Pokud Rozsah ("A" & i) .Hodnota = "chyba" PakRozsah („A“ a i). VyberteMsgBox "Chyba nalezena"Ukončit proKonec IfPříště jáEnd Sub

Důležité: V případě Nested For Loops, Exit For opustí pouze aktuální For Loop, ne všechny aktivní Loops.

Pokračovat pro

VBA nemá příkaz „Pokračovat“, který se nachází v jazyce Visual Basic. Místo toho budete muset použít „Konec“.

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 pracovní listy v sešitu
• Všechny tvary v listu
• Všechny otevřené sešity

Vnořené pro každou smyčku můžete také použít k:

• Všechny buňky v rozsahu na všech listech
• Všechny tvary na všech pracovních listech
• Všechny listy ve všech otevřených sešitech
• a tak dále…

Syntaxe je:

123	Pro každý objekt ve sbírce[Dělej něco]Další [Objekt]

Kde:

• Objekt - Proměnná představující rozsah, pracovní list, sešit, tvar atd. (Např. Rng)
• Sbírka - Sbírka předmětů (např. Rozsah („a1: a10“)
• [Dělej něco] - Blok kódu pro spuštění na každém objektu
• Další [Objekt] - Závěrečné prohlášení. [Objekt] je volitelný, ale důrazně se doporučuje.

Pro každou buňku v dosahu

Tento kód bude procházet každou buňkou v rozsahu:

123456789

Sub ForEachCell_inRange ()Dim buňka jako rozsahPro každou buňku v dosahu („a1: a10“)cell.Value = cell.Offset (0,1) .ValueDalší buňkaEnd Sub

Pro každý pracovní list v sešitu

Tento kód bude procházet všemi listy v sešitu a každý list odemkne:

123456789

Sub ForEachSheet_inWorkbook ()Dim ws As WorksheetZa každé ws v pracovních listechws.Onprotect "heslo"Další wsEnd Sub

Pro každý otevřený sešit

Tento kód uloží a zavře všechny otevřené sešity:

123456789

Sub ForEachWB_inWorkbooks ()Dim wb jako sešitPro každý wb v sešitechwb.Zavřít SaveChanges: = TrueDalší wbEnd Sub

Pro každý tvar v listu

Tento kód odstraní všechny tvary v aktivním listu.

123456789

Sub ForEachShape ()Dim shp As ShapePro každý SHP v ActiveSheet.Shapesshp. SmazatDalší shpEnd Sub

Pro každý tvar v každém listu v sešitu

Můžete také vnořit pro každou smyčku. Zde procházíme všemi tvary ve všech listech v aktivním sešitu:

1234567891011

Sub ForEachShape_inAllWorksheets ()Dim shp As Shape, ws As WorksheetZa každé ws v pracovních listechPro každý SHP ve ws.Shapesshp. SmazatDalší shpDalší wsEnd Sub

Pro každou - IF smyčku

Jak jsme již zmínili, můžete použít příkaz If ve smyčce a provádět akce pouze v případě, že jsou splněna určitá kritéria.

Tento kód skryje všechny prázdné řádky v rozsahu:

12345678910

Sub ForEachCell_inRange ()Dim buňka jako rozsahPro každou buňku v dosahu („a1: a10“)Pokud cell.Value = "" Pak _cell.EntireRow.Hidden = TrueDalší buňkaEnd Sub

VBA Do While Loop

VBA Do While a Do Before (viz další část) jsou velmi podobné. Budou opakovat smyčku, dokud (nebo dokud) není splněna podmínka.

Smyčka Do While opakuje smyčku, pokud je splněna podmínka.

Zde je syntaxe Do While:

123	Proveďte podmínku[Dělej něco]Smyčka

Kde:

• Stav - Podmínka k testování
• [Dělej něco] - Blok kódu k opakování

Můžete také nastavit smyčku Do While s podmínkou na konci smyčky:

123	Dělat[Dělej něco]Smyčka za podmínky

Každý z nich předvedeme a ukážeme, jak se liší:

Dělat, zatímco

Zde je příklad cyklu Do While, který jsme si předtím ukázali:

12345678

Sub DoWhileLoop ()Dim n jako celé číslon = 1Do While n <11MsgBox čn = n + 1SmyčkaEnd Sub

Smyčka Zatímco

Nyní spustíme stejný postup, kromě toho, že podmínku přesuneme na konec smyčky:

12345678

Sub DoLoopWhile ()Dim n jako celé číslon = 1DělatMsgBox čn = n + 1Smyčka zatímco n <11End Sub

VBA dělat do smyčky

Opakujte smyčku, dokud nebude splněna určitá podmínka. Syntaxe je v podstatě stejná jako smyčky Do While:

123	Do Do podmínky[Dělej něco]Smyčka

a podobně podmínka může jít na začátku nebo na konci smyčky:

123	Dělat[Dělej něco]Smyčka do podmínky

Dělat Do

Tato smyčka do do bude počítat do 10, jako naše předchozí příklady

12345678

Sub DoUntilLoop ()Dim n jako celé číslon = 1Proveďte do n> 10MsgBox čn = n + 1SmyčkaEnd Sub

Smyčka do

Tato smyčka Do smyčky se bude počítat do 10:

12345678

Sub DoLoopUntil ()Dim n jako celé číslon = 1DělatMsgBox čn = n + 1Smyčka do n> 10End Sub

Exit Do Loop

Podobně jako použití Exit For k ukončení smyčky For, použijete příkaz Exit Do k okamžitému opuštění Do Loop

1	Ukončit Do

Zde je příklad Exit Do:

123456789101112131415

Sub ExitDo_Loop ()Dim i As Integeri = 1Do I> 1000Pokud Rozsah ("A" & i) .Hodnota = "chyba" PakRozsah („A“ a i). VyberteMsgBox "Chyba nalezena"Ukončit DoKonec Ifi = i + 1SmyčkaEnd Sub

Konec nebo přerušení smyčky

Jak jsme zmínili výše, můžete pro ukončení smyček použít Exit For nebo Exit Do:

1	Ukončit pro

1	Ukončit Do

Tyto příkazy však musíte přidat do kódu před spuštěním smyčky.

Pokud se pokoušíte „přerušit“ aktuálně spuštěnou smyčku, můžete zkusit stisknout ESC nebo CTRL + Přestávka na klávesnici. To však nemusí fungovat. Pokud to nefunguje, budete muset počkat, až vaše smyčka skončí, nebo v případě nekonečné smyčky použít CTRL + ALT + Vymazat vynutit zavření Excelu.

Proto se snažím vyhýbat smyčkám Do, je snazší omylem vytvořit nekonečnou smyčku, která vás donutí restartovat Excel a potenciálně přijít o práci.

Více příkladů smyček

Projděte řádky

To bude procházet všemi řádky ve sloupci:

123456789

Public Sub LoopThroughRows ()Dim buňka jako rozsahPro každou buňku v dosahu („A: A“)Ff cell.value "" potom MsgBox cell.address & ":" & cell.valueDalší buňkaEnd Sub

Smyčka skrz sloupce

To bude procházet všemi sloupci v řadě:

123456789

Public Sub LoopThroughColumns ()Dim buňka jako rozsahPro každou buňku v rozsahu („1: 1“)Pokud cell.Value "" Pak MsgBox cell.Address & ":" & cell.ValueDalší buňkaEnd Sub

Procházejte soubory ve složce

Tento kód bude procházet všemi soubory ve složce a vytvoří seznam:

12345678910111213141516171819

Sub LoopThroughFiles ()Dim oFSO jako objektDim oFolder jako objektDim oFile As ObjectDim i As IntegerNastavit oFSO = CreateObject ("Scripting.FileSystemObject")Nastavit oFolder = oFSO.GetFolder ("C: \ Demo)i = 2Pro každý oFile v oFolder.FilesRozsah ("A" & i) .value = oFile.Namei = i + 1Další oFileEnd Sub

Smyčka skrz pole

Tento kód bude procházet polem ‘arrList’:

123	Pro i = LBound (arrList) Do UBound (arrList)MsgBox arrList (i)Příště já

Funkce LBound získá „dolní mez“ pole a UBound získá „horní hranici“.

Smyčky v Access VBA

Většina výše uvedených příkladů bude fungovat také v Access VBA. V Accessu však procházíme spíše objektem sady záznamů než objektem rozsahu.

123456789101112131415161718

Sub LoopThroughRecords ()Při chybě Pokračovat DalšíDim dbs jako databázeDim první jako sada záznamůNastavit dbs = CurrentDbNastavit rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)S první.MoveLast.MoveFirstDo .EOF = TrueMsgBox (rst.Fields ("ClientName")).MoveNextSmyčkaKonec sprvní ZavřítNastavit první = nicNastavit dbs = nicEnd Sub