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 |
