Aby ste efektívne pracovali vo VBA, musíte porozumieť slučkám.
Smyčky vám umožňujú opakovať blok kódu stanovený počet krát alebo opakovať blok kódu na každom objekte v sérii objektov.
Najprv vám ukážeme niekoľko príkladov, ktoré vám ukážu, čoho sú slučky schopné. Potom vás naučíme všetko o slučkách.
Rýchle príklady slučky VBA
Pre každú slučku
Pre každú slučku prechádza každým objektom v zbierke, napríklad každým pracovným listom v zošite alebo každou bunkou v rozsahu.
Prelistujte si všetky pracovné listy v zošite
Tento kód bude prechádzať všetkými pracovnými listami v zošite, pričom odkryje každý list:
| 12345678 | Sub LoopThroughSheets ()Dim ws As worksheetZa každé ws v pracovných listochws.Visible = TrueĎalšieKoniec pod |
Smyčka cez všetky bunky v dosahu
Tento kód bude prechádzať rozsahom buniek a testovať, či je hodnota bunky záporná, kladná alebo nulová:
| 1234567891011121314 | Sub If_Loop ()Dim Cell ako rozsahPre každú bunku v rozsahu („A2: A6“)Ak je Cell.Value> 0 PotomCell.Offset (0, 1) .Hodnota = "Pozitívne"ElseIf Cell.Value <0 PotomCell.Offset (0, 1) .Value = "Negative"InakCell.Offset (0, 1) .Hodnota = "Nula"Koniec AkĎalšia bunkaKoniec pod |
Pre ďalšie slučky
Ďalším typom slučky „For“ je slučka For Next. Slučka For Next Loop vám umožňuje opakovať celé čísla.
Tento kód bude prechádzať celými číslami 1 až 10 a každé zobrazí v poli so správou:
| 123456 | Sub ForLoop ()Dim i As IntegerPre i = 1 až 10MsgBox iĎalej iKoniec pod |
Vykonajte slučky
Slučky Do While sa budú slučkovať, pokiaľ je splnená podmienka. Tento kód bude tiež prechádzať celými číslami 1 až 10 a každé bude zobrazovať so správou.
| 12345678 | Sub DoWhileLoop ()Dim n ako celé číslon = 1Urobte, kým n <11MsgBox čn = n + 1SlučkaKoniec pod |
Vykonajte Do Loops
Naopak, Do Do Loops sa budú cyklovať, kým nie je splnená podmienka. Tento kód robí to isté ako predchádzajúce dva príklady.
| 12345678 | Sub DoUntilLoop ()Dim n ako celé číslon = 1Vykonajte do n> = 10MsgBox čn = n + 1SlučkaKoniec pod |
Budeme o tom diskutovať nižšie, ale pri vytváraní slučiek Do While alebo Do Before musíte byť veľmi opatrní, aby ste nevytvorili nikdy nekončiacu slučku.
VBA Loop Builder
Toto je snímka obrazovky „Loop Builder“ z nášho doplnku Premium VBA: AutoMacro. Loop Builder vám umožňuje rýchlo a ľahko vytvárať slučky na prechádzanie rôznymi objektmi alebo číslami. Na každom objekte môžete vykonávať akcie a/alebo vybrať iba objekty, ktoré spĺňajú určité kritériá.
Doplnok tiež obsahuje mnoho ďalších tvorcov kódu, rozsiahlu knižnicu kódov VBA a sortiment kódovacích nástrojov. Je to nevyhnutné pre každého vývojára VBA.
Teraz pokryjeme rôzne typy slučiek do hĺbky.
VBA pre ďalšiu slučku
Pre syntax slučky
Slučka For Next Loop vám umožňuje opakovať blok kódu zadaný počet krát. Syntax je:
| 12345 | [Dim Counter ako celé číslo]Počítadlo = od začiatku do konca [hodnota kroku][Urob niečo]Ďalej [počítadlo] |
Kde položky v zátvorkách sú voliteľné.
- [Dim Counter as Long] - Deklaruje premennú počítadla. Vyžaduje sa, ak je v hornej časti modulu deklarovaný explicitný parameter.
- Počítadlo - Celočíselná premenná používaná na počítanie
- Začnite - Počiatočná hodnota (Príklad 1)
- Koniec - Koncová hodnota (Príklad 10)
- [Hodnota kroku] - Umožňuje počítať každé n celé číslo namiesto každého 1 celého čísla. Môžete ísť aj opačne so zápornou hodnotou (napr. Krok -1)
- [Urob niečo] - Kód, ktorý sa bude opakovať
- Ďalej [počítadlo] - Záverečné vyhlásenie k cyklu For Next. Počítadlo môžete zahrnúť alebo nie. Dôrazne však odporúčam zahrnúť počítadlo, pretože uľahčuje čítanie kódu.
Ak je to mätúce, nebojte sa. Zrevidujeme niekoľko príkladov:
Počítať do 10
Tento kód sa bude počítať do 10 pomocou slučky For-Next:
| 12345678 | Sub ForEach_CountTo10 ()Dim n ako celé čísloPre n = 1 až 10MsgBox čĎalej nKoniec pod |
Pre krok slučky
Počítajte do 10 - iba párne čísla
Tento kód bude počítať do 10 iba s párnymi číslami:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n ako celé čísloPre n = 2 až 10 Krok 2MsgBox čĎalej nKoniec pod |
Všimnite si, že sme pridali „Krok 2“. To znamená, že slučka For Loop má „prejsť“ počítadlom o 2. Na spätný krok môžeme použiť aj zápornú hodnotu kroku:
Pre krok slučky - inverzný
Odpočítavanie od 10
Tento kód bude odpočítavať od 10:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n ako celé čísloPre n = 10 až 1 Krok -1MsgBox čĎalej nMsgBox „Zdvihnúť“Koniec pod |
Ak je bunka prázdna, odstráňte riadky
Najčastejšie som používal negatívny krok For-Loop na cyklovanie medzi rozsahmi buniek a odstraňovanie riadkov, ktoré spĺňajú určité kritériá. Ak sa budete cyklovať od horných riadkov k dolným riadkom, pri odstraňovaní riadkov pokazíte počítadlo.
Tento príklad odstráni riadky s prázdnymi bunkami (začínajúc od spodného riadka):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n ako celé čísloPre n = 10 až 1 Krok -1Ak je rozsah ("a" & n) .Hodnota = "" PotomRozsah ("a" & n) .EntireRow.DeleteKoniec AkĎalej nKoniec pod |
Vnorené pre slučku
Jeden pre slučku môžete „vnoriť“ do druhej pre slučku. Na vytvorenie multiplikačnej tabuľky použijeme vnorené slučky:
| 1234567891011 | Sub Nested_ForEach_MultiplicationTable ()Stmaviť riadok ako celé číslo, stĺpec ako celé čísloPre riadok = 1 až 9Pre stĺpec = 1 až 9Bunky (riadok + 1, stĺpček + 1). Hodnota = riadok * stĺpĎalej plkĎalší riadokKoniec pod |
Ukončiť pre
Príkaz Exit For vám umožňuje okamžite opustiť slučku For Next.
Obvykle by ste použili príkaz Exit For spolu s príkazom If a opustenie cyklu For Next Loop, ak je splnená určitá podmienka.
Bunku môžete napríklad nájsť pomocou slučky For Loop. Akonáhle je táto bunka nájdená, môžete opustiť slučku a urýchliť tak svoj kód.
Tento kód bude prechádzať riadkami 1 až 1000 a v stĺpci A bude hľadať „chybu“. Ak sa nájde, kód vyberie bunku, upozorní vás na nájdenú chybu a ukončí slučku:
| 12345678910111213 | Sub ExitFor_Loop ()Dim i As IntegerPre i = 1 až 1 000Ak je rozsah ("A" & i) .Hodnota = "chyba" PotomRozsah („A“ a i). VyberteMsgBox „Našla sa chyba“Ukončiť preKoniec AkĎalej iKoniec pod |
Dôležité: V prípade Vnorených pre slučky, Ukončiť pre opustí iba aktuálnu Pre slučku, nie všetky aktívne slučky.
Pokračovať pre
VBA nemá príkaz „Pokračovať“, ktorý sa nachádza v programe Visual Basic. Namiesto toho budete musieť použiť „Ukončiť“.
VBA pre každú slučku
VBA pre každú slučku bude prechádzať všetkými objektmi v zbierke:
- Všetky bunky v rozsahu
- Všetky pracovné listy v pracovnom zošite
- Všetky tvary v pracovnom liste
- Všetky otvorené pracovné zošity
Vnorené pre každú slučku môžete použiť aj na:
- Všetky bunky v rozsahu na všetkých pracovných hárkoch
- Všetky tvary na všetkých pracovných listoch
- Všetky listy vo všetkých otvorených pracovných zošitoch
- a tak ďalej…
Syntax je:
| 123 | Pre každý objekt v zbierke[Urob niečo]Ďalej [Objekt] |
Kde:
- Objekt - Premenná predstavujúca rozsah, pracovný hárok, zošit, tvar atď. (Napr. Rng)
- Zbierka - Zbierka predmetov (napr. Rozsah („a1: a10“)
- [Urob niečo] - Blok kódu, ktorý sa má spustiť na každom objekte
- Ďalej [Objekt] - Záverečné vyhlásenie. [Objekt] je voliteľný, ale dôrazne sa odporúča.
Pre každú bunku v dosahu
Tento kód bude prechádzať každou bunkou v rozsahu:
| 123456789 | Sub ForEachCell_inRange ()Dim bunka ako rozsahPre každú bunku v rozsahu („a1: a10“)cell.Value = cell.Offset (0,1) .HodnotaĎalšia bunkaKoniec pod |
Pre každý pracovný list v zošite
Tento kód bude prechádzať všetkými pracovnými listami v zošite, pričom každý list odomkne:
| 123456789 | Sub ForEachSheet_inWorkbook ()Dim ws As worksheetZa každé ws v pracovných listochws.Zrušte ochranu „hesla“Ďalšie wsKoniec pod |
Pre každý otvorený zošit
Tento kód uloží a zatvorí všetky otvorené zošity:
| 123456789 | Sub ForEachWB_inWorkbooks ()Dim wb ako zošitPre každý wb v zošitochwb.Zavrieť SaveChanges: = TrueDalsie wbKoniec pod |
Pre každý tvar v pracovnom liste
Tento kód odstráni všetky tvary v aktívnom hárku.
| 123456789 | Sub ForEachShape ()Dim shp As ShapePre každý SHP v ActiveSheet.Shapesshp. VymazaťĎalej shpKoniec pod |
Pre každý tvar v každom pracovnom hárku v zošite
Môžete tiež vnoriť pre každú slučku. Tu sa budeme prechádzať všetkými tvarmi vo všetkých pracovných hárkoch v aktívnom zošite:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Dim shp As Shape, ws As WorksheetZa každé ws v pracovných listochPre každý SHP vo Ws.Tvaryshp. VymazaťĎalej shpĎalšie wsKoniec pod |
Pre každú - IF slučku
Ako sme už uviedli, príkaz If môžete použiť v rámci cyklu, pričom akcie vykonávate iba vtedy, ak sú splnené určité kritériá.
Tento kód skryje všetky prázdne riadky v rozsahu:
| 12345678910 | Sub ForEachCell_inRange ()Dim bunka ako rozsahPre každú bunku v rozsahu („a1: a10“)Ak cell.Value = "" Potom _cell.EntireRow.Hidden = TrueĎalšia bunkaKoniec pod |
VBA Do While Loop
VBA Do While a Do Do (pozri nasledujúcu časť) sú veľmi podobné. Budú opakovať slučku, kým (alebo kým) nie je splnená podmienka.
Slučka Do While opakuje slučku, keď je splnená podmienka.
Tu je syntax Do Do While:
| 123 | Podmienka Do While[Urob niečo]Slučka |
Kde:
- Podmienka - Podmienka testovania
- [Urob niečo] - Blok kódu na zopakovanie
Môžete tiež nastaviť cyklus Do While s podmienkou na konci cyklu:
| 123 | Urob[Urob niečo]Slučka, zatiaľ čo podmienka |
Každý z nich predvedieme na ukážke a ukážeme, ako sa líšia:
Robiť kým
Tu je príklad cyklu Do While, ktorý sme predtým demonštrovali:
| 12345678 | Sub DoWhileLoop ()Dim n ako celé číslon = 1Urobte, kým n <11MsgBox čn = n + 1SlučkaKoniec pod |
Slučka Kým
Teraz spustíme rovnaký postup, ibaže podmienku presunieme na koniec cyklu:
| 12345678 | Sub DoLoopWhile ()Dim n ako celé číslon = 1UrobMsgBox čn = n + 1Slučka, kým n <11Koniec pod |
VBA Robiť do slučky
Do Do Loops budú cykly opakovať, kým nebude splnená určitá podmienka. Syntax je v zásade rovnaká ako slučky Do While:
| 123 | Vykonajte podmienku Do[Urob niečo]Slučka |
a podobne podmienka môže ísť na začiatku alebo na konci cyklu:
| 123 | Urob[Urob niečo]Slučka do podmienky |
Robiť do
Táto slučka Do until bude počítať do 10, ako naše predchádzajúce príklady
| 12345678 | Sub DoUntilLoop ()Dim n ako celé číslon = 1Vykonajte do n> 10MsgBox čn = n + 1SlučkaKoniec pod |
Slučka do
Táto slučka Do cyklu sa bude počítať do 10:
| 12345678 | Sub DoLoopUntil ()Dim n ako celé číslon = 1UrobMsgBox čn = n + 1Slučka do n> 10Koniec pod |
Exit Do Loop
Podobne ako pri použití príkazu Exit For na ukončenie cyklu For Loop, príkaz Exit Do použite na okamžité opustenie cyklu Do Loop
| 1 | Ukončiť Do |
Tu je príklad Exit Do:
| 123456789101112131415 | Sub ExitDo_Loop ()Dim i As Integeri = 1Robiť, kým nebudem> 1000Ak je rozsah ("A" & i) .Hodnota = "chyba" PotomRozsah („A“ a i). VyberteMsgBox „Našla sa chyba“Ukončiť DoKoniec Aki = i + 1SlučkaKoniec pod |
Ukončiť alebo prerušiť slučku
Ako sme už uviedli, na opustenie slučiek môžete použiť Exit For alebo Exit Do:
| 1 | Ukončiť pre |
| 1 | Ukončiť Do |
Tieto príkazy však musíte pridať do kódu pred spustením slučky.
Ak sa pokúšate „prerušiť“ aktuálne spustenú slučku, môžete skúsiť stlačiť ESC alebo CTRL + Pauza prestávka na klávesnici. To však nemusí fungovať. Ak to nefunguje, budete musieť počkať, kým sa slučka skončí alebo v prípade nekonečnej slučky použiť CTRL + ALT + Vymazať vynútiť zatvorenie programu Excel.
Preto sa snažím vyhýbať slučkám Do, je jednoduchšie omylom vytvoriť nekonečnú slučku, ktorá vás prinúti reštartovať Excel a potenciálne prísť o prácu.
Viac príkladov slučiek
Smyčka cez riadky
Tým sa bude prechádzať všetkými riadkami v stĺpci:
| 123456789 | Public Sub LoopThroughRows ()Dim bunka ako rozsahPre každú bunku v rozsahu („A: A“)Ff cell.value "" potom MsgBox cell.address & ":" & cell.valueĎalšia bunkaKoniec pod |
Smyčka cez stĺpce
Tým sa bude prechádzať všetkými stĺpcami v rade:
| 123456789 | Public Sub LoopThroughColumns ()Dim bunka ako rozsahPre každú bunku v rozsahu („1: 1“)Ak cell.Value "" Potom MsgBox cell.Address & ":" & cell.ValueĎalšia bunkaKoniec pod |
Prechádzajte súbormi v priečinku
Tento kód bude prechádzať všetkými súbormi v priečinku a vytvorí zoznam:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO ako objektDim oFolder ako objektDim oFile As ObjectDim i As IntegerNastaviť oFSO = CreateObject ("Scripting.FileSystemObject")Nastaviť oFolder = oFSO.GetFolder ("C: \ Demo)i = 2Pre každý oFile v oFolder.FilesRozsah ("A" & i) .hodnota = oSúbor.Názovi = i + 1Ďalší oFileKoniec pod |
Smyčka cez pole
Tento kód bude prechádzať poľom „arrList“:
| 123 | Pre i = LBound (arrList) Na UBound (arrList)MsgBox arrList (i)Ďalej i |
Funkcia LBound získa „dolnú hranicu“ poľa a UBound získa „hornú hranicu“.
Slučky v programe Access VBA
Väčšina vyššie uvedených príkladov bude fungovať aj v programe Access VBA. V Accesse však prechádzame skôr cez objekt sady záznamov, než ako objekt rozsahu.
| 123456789101112131415161718 | Sub LoopThroughRecords ()Pri chybe Pokračovať ďalejDim dbs ako databázaDim first as RecordsetNastaviť dbs = CurrentDbNastaviť rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)S prvým.Pohyb.Pohyb Po prvéRobiť do .EOF = PravdaMsgBox (rst.Fields („ClientName“)).MoveNextSlučkaUkončiť snajskôr. ZatvorteNastaviť prvý = ničNastaviť dbs = ničKoniec pod |
