Excel VBA hurkok - Mindegyikhez, a Következő, a Tedd meg, beágyazott és egyebekhez

A VBA hatékony működéséhez meg kell értenie a hurkokat.

A hurkok lehetővé teszik, hogy egy kódblokkot meghatározott számú alkalommal megismételjenek, vagy egy kódblokkot ismételjenek meg egy objektumhalmaz minden objektumán.

Először néhány példát mutatunk be, hogy megmutassuk, mire képesek a hurkok. Akkor mindent megtanítunk a hurkokról.

VBA hurok gyors példák

Minden hurokhoz

Minden hurok esetén a gyűjtemény minden objektumán keresztül cikluson keresztül, például a munkafüzet minden munkalapján vagy a tartomány minden celláján.

Tekintse át a munkafüzet összes munkalapját

Ez a kód végigmegy a munkafüzet összes munkalapján, és feltárja az egyes lapokat:

12345678

Sub LoopThroughSheets ()Dim ws munkalapkéntMinden egyes munkalaphozws.Visible = IgazKövetkezőEnd Sub

Loop keresztül a tartomány összes celláján

Ez a kód cikluson keresztül cikluson keresztül ellenőrzi, hogy a cella értéke negatív, pozitív vagy nulla:

1234567891011121314

If_Loop () alDim cella mint tartományA tartomány minden cellájához ("A2: A6")Ha Cell.Value> 0 AkkorCell.Offset (0, 1) .Value = "Pozitív"Egyébként Cell.Érték <0 AkkorCell.Offset (0, 1) .Value = "Negative"MásCell.Offset (0, 1) .Value = "Zero"Vége HaKövetkező cellaEnd Sub

Következő hurkokhoz

A „For” hurok másik típusa a For Next Loop. A For Next Loop lehetővé teszi, hogy egész számokat ciklusozzon át.

Ez a kód az 1 -től 10 -ig terjedő egész számokat fogja látni, és mindegyik üzenetmezővel jelenik meg:

123456

Sub ForLoop ()Dim i As IntegerI = 1 és 10 közöttMsgBox iKövetkező iEnd Sub

Csinálj hurkokat

A Do while hurkok ciklusban ciklusosak, amíg egy feltétel teljesül. Ez a kód az 1 és 10 közötti egész számokon is keresztülhalad, és mindegyik üzenetmezővel jelenik meg.

12345678

Sub DoWhileLoop ()Dim n Mint egészn = 1Tedd, amíg n <11MsgBox nn = n + 1HurokEnd Sub

Csináld a hurkokig

Ezzel szemben a Do Do Loops ciklusok addig ciklusoznak, amíg egy feltétel teljesül. Ez a kód ugyanazt teszi, mint az előző két példa.

12345678

Sub DoUntilLoop ()Dim n Mint egészn = 1Végezze el, amíg n> = 10MsgBox nn = n + 1HurokEnd Sub

Ezt az alábbiakban tárgyaljuk, de rendkívül óvatosnak kell lennie a Do While vagy Do Do ciklusok létrehozásakor, hogy ne hozzon létre soha véget nem érő ciklust.

VBA huroképítő

Ez egy képernyőkép a „Loop Builder” -ről a prémium VBA bővítményünkből: AutoMacro. A Loop Builder lehetővé teszi a hurkok gyors és egyszerű felépítését különböző objektumok vagy számok közötti ciklushoz. Műveleteket hajthat végre minden objektumon, és/vagy csak olyan objektumokat választhat ki, amelyek megfelelnek bizonyos feltételeknek.

A bővítmény számos más kódkészítőt, kiterjedt VBA kódkönyvtárat és kódolóeszközöket is tartalmaz. Ez minden VBA fejlesztő számára kötelező.

Most részletesen bemutatjuk a különböző típusú hurkokat.

VBA a következő hurokhoz

Loop szintaxishoz

A For Next Loop lehetővé teszi, hogy meghatározott számú alkalommal ismételje meg a kódblokkot. A szintaxis a következő:

12345

[Dim Counter as Integer]Számlálóhoz = Kezdet -vég [Lépésérték][Csinálj valamit]Következő [Számláló]

Ahol a zárójelben lévő elemek nem kötelezőek.

• [Hosszú homályos számláló] - Deklarálja a számláló változót. Kötelező, ha a Option Explicit ki van jelölve a modul tetején.
• Számláló - A számláláshoz használt egész változó
• Rajt - A kezdőérték (pl. 1)
• Vége - A végérték (Péld. 10)
• [Lépés értéke] - Lehetővé teszi minden n egész szám megszámlálását minden 1 egész szám helyett. Fordított irányba is léphet negatív értékkel (pl. -1. Lépés)
• [Csinálj valamit] - A kód, amely megismétlődik
• Következő [Számláló] - Záró nyilatkozat a For Next Loop számára. Felveheti a számlálót vagy sem. Erősen javaslom azonban a számláló szerepeltetését, mivel ez megkönnyíti a kód olvasását.

Ha ez zavaró, ne aggódjon. Néhány példát áttekintünk:

Számolj 10 -ig

Ez a kód 10-ig számít a For-Next Loop használatával:

12345678

Sub ForEach_CountTo10 ()Dim n Mint egészN = 1 és 10 közöttMsgBox nKövetkező nEnd Sub

A Loop Step számára

Számoljon 10 -ig - csak páros számokat

Ez a kód 10 -ig számít, csak páros számokat számolva:

12345678

Sub ForEach_CountTo10_Even ()Dim n Mint egészN = 2-10 esetén 2. lépésMsgBox nKövetkező nEnd Sub

Figyeljük meg, hogy hozzáadtuk a „2. lépést”. Ez azt mondja a For Loop -nak, hogy 2 -gyel lépjen át a számlálón. Negatív lépésértéket is használhatunk a fordított lépéshez:

Loop Step - Fordított

Visszaszámlálás 10 -től

Ez a kód visszaszámlál 10 -től:

123456789

Sub ForEach_Countdown_Inverse ()Dim n Mint egészN = 10 esetén 1 lépés -1MsgBox nKövetkező nMsgBox "Lift Off"End Sub

Törölje a sorokat, ha a cella üres

Leggyakrabban negatív lépést használtam a For-Loop számára a cellák tartományainak végigkíséréséhez, bizonyos feltételeknek megfelelő sorok törléséhez. Ha a felső soroktól az alsó sorok felé halad, miközben törli a sorokat, összezavarja a számlálót.

Ez a példa törli az üres cellákkal rendelkező sorokat (az alsó sorból kiindulva):

12345678910

Sub ForEach_DeleteRows_BlankCells ()Dim n Mint egészN = 10 esetén 1 lépés -1Ha Tartomány ("a" & n) .Value = "" AkkorTartomány ("a" & n) .EntireRow.DeleteVége HaKövetkező nEnd Sub

Hurokba ágyazva

Az egyik hurkot „beágyazhatja” egy másikba a hurokba. A Nested For Loops segítségével szorzótáblát készítünk:

1234567891011

Sub Nested_ForEach_MultiplicationTable ()Halvány sor egész számként, oszlop: egész számSor = 1 -től 9 -igSzín = 1 és 9 közöttSejtek (sor + 1, oszlop + 1). Érték = sor * oszlopKövetkező oszlopKövetkező sorEnd Sub

Kilépés

Az Exit For utasítás lehetővé teszi, hogy azonnal kilépjen a For Next ciklusból.

Általában az Exit For parancsot használja az If utasítással együtt, és kilép a For Next Loopból, ha egy bizonyos feltétel teljesül.

Például használhat For Loop -ot egy cella megkereséséhez. Miután megtalálta a cellát, kiléphet a ciklusból, hogy felgyorsítsa a kódot.

Ez a kód az 1 -től 1000 -ig terjedő sorokban cikáz, és az „A” oszlopban „hibát” keres. Ha megtalálta, a kód kiválasztja a cellát, figyelmeztet a talált hibára, és kilép a ciklusból:

12345678910111213

Sub ExitFor_Loop ()Dim i As IntegerI = 1 és 1000 közöttHa Tartomány ("A" & i) .Érték = "hiba" AkkorTartomány ("A" & i). Válassza kiMsgBox "Hiba található"KilépésVége HaKövetkező iEnd Sub

Fontos: A Nested For Loops esetében az Exit For csak az aktuális For Loop kilép, nem minden aktív Loop.

Folytatás

A VBA nem rendelkezik a Visual Basicben található „Folytatás” paranccsal. Ehelyett az „Exit” -t kell használnia.

VBA minden hurokhoz

A VBA minden egyes hurokhoz a gyűjtemény összes objektumán áthalad:

• Minden sejt egy tartományban
• Minden munkalap egy munkafüzetben
• Minden alakzat egy munkalapon
• Minden nyitott munkafüzet

A Nested For Every Loops funkciót a következőkre is használhatja:

• A tartomány összes cellája az összes munkalapon
• Minden alakzat minden munkalapon
• Minden lap minden nyitott munkafüzetben
• stb…

A szintaxis a következő:

123	A gyűjtemény minden tárgyához[Csinálj valamit]Következő [Objektum]

Ahol:

• Tárgy - Változó, amely egy tartományt, munkalapot, munkafüzetet, alakot stb. Képvisel (pl. Rng)
• Gyűjtemény - Tárgyak gyűjteménye (pl. Tartomány („a1: a10”)
• [Csinálj valamit] - Kódblokk futtatása minden objektumon
• Következő [Objektum] - Záró nyilatkozat. Az [Objektum] opcionális, de erősen ajánlott.

A tartomány minden cellájához

Ez a kód végigmegy a tartomány minden celláján:

123456789

Sub ForEachCell_inRange ()Homályos cella mint tartományA tartomány minden cellájához ("a1: a10")cell.Value = cell.Offset (0,1). ValueKövetkező cellaEnd Sub

Minden munkalaphoz a munkafüzetben

Ez a kód végigmegy a munkafüzet összes munkalapján, és eltávolítja az egyes lapok védelmét:

123456789

Sub ForEachSheet_inWorkbook ()Dim ws munkalapkéntMinden egyes munkalaphozws. A "jelszó" védelmének feloldásaKövetkező wsEnd Sub

Minden nyitott munkafüzethez

Ez a kód menti és bezárja az összes nyitott munkafüzetet:

123456789

Sub ForEachWB_inWorkbooks ()Dim wb Mint munkafüzetMinden wb -hez a munkafüzetekbenwb. Bezárás SaveChanges: = IgazKövetkező wbEnd Sub

A munkalap minden alakjához

Ez a kód törli az összes formát az aktív lapról.

123456789

Sub ForEachShape ()Dim shp As ShapeAz ActiveSheet.Shapes minden egyes shp -jéhezshp. TörlésKövetkező shpEnd Sub

Minden alakzathoz a munkafüzet minden munkalapján

Fészkelhet az egyes hurkokhoz is. Itt az aktív munkafüzet összes munkalapjának minden alakját végigjárjuk:

1234567891011

Sub ForEachShape_inAllWorksheets ()Dim shp mint alakzat, ws mint munkalapMinden egyes munkalaphozMinden shp In ws.Shapesshp. TörlésKövetkező shpKövetkező wsEnd Sub

Mindegyikhez - HA hurok

Amint azt már korábban említettük, használhat If utasításokat egy cikluson belül, és csak bizonyos feltételek teljesülése esetén hajthat végre műveleteket.

Ez a kód elrejti a tartomány összes üres sorát:

12345678910

Sub ForEachCell_inRange ()Homályos cella mint tartományA tartomány minden cellájához ("a1: a10")Ha cell.Value = "" Akkor _cell.EntireRow.Hidden = IgazKövetkező cellaEnd Sub

VBA Do while Loop

A VBA Do Do és a Do Before (lásd a következő részt) nagyon hasonlóak. Megismétlik a ciklust, amíg (vagy amíg) egy feltétel teljesül.

A Do Do ciklus megismétli a ciklust, amíg egy feltétel teljesül.

Íme a Do while szintaxis:

123	Csinálj állapotot[Csinálj valamit]Hurok

Ahol:

• Állapot - A tesztelés feltétele
• [Csinálj valamit] - Az ismétlődő kódblokk

Beállíthat egy Do Do ciklusot is a feltétellel a ciklus végén:

123	Tedd[Csinálj valamit]Hurok állapot

Mindegyiket bemutatjuk, és megmutatjuk, miben különböznek:

Csinálni, miközben

Íme a korábban bemutatott Do While cikluspélda:

12345678

Sub DoWhileLoop ()Dim n Mint egészn = 1Tedd, amíg n <11MsgBox nn = n + 1HurokEnd Sub

Hurok közben

Most futtassuk ugyanazt az eljárást, csakhogy a feltételt a ciklus végére helyezzük át:

12345678

Sub DoLoopWhile ()Dim n Mint egészn = 1TeddMsgBox nn = n + 1Hurok Míg n <11End Sub

VBA Do Before Loop

Do Do Loops megismétli a ciklust, amíg egy bizonyos feltétel teljesül. A szintaxis lényegében megegyezik a Do while ciklusokkal:

123	Tedd állapotig[Csinálj valamit]Hurok

és hasonlóan a feltétel a ciklus elején vagy végén is mehet:

123	Tedd[Csinálj valamit]Hurok állapotáig

Csináld addig

Ez a till ciklus 10 -ig számít, mint az előző példáink

12345678

Sub DoUntilLoop ()Dim n Mint egészn = 1Tegye n> 10 -igMsgBox nn = n + 1HurokEnd Sub

Hurok addig

Ez a ciklus ciklus 10 -ig számít:

12345678

Sub DoLoopUntil ()Dim n Mint egészn = 1TeddMsgBox nn = n + 1Hurok n> 10 -igEnd Sub

Kilépés a Do Loopból

A For Loop kilépéséhez hasonlóan az Exit For funkcióhoz használja az Exit Do parancsot a Do Loop azonnali kilépéséhez

1	Kilépés Do

Íme egy példa az Exit Do -ra:

123456789101112131415

Sub ExitDo_Loop ()Dim i As Integeri = 1Tedd, amíg i> 1000Ha Tartomány ("A" & i) .Érték = "hiba" AkkorTartomány ("A" & i). Válassza kiMsgBox "Hiba található"Kilépés DoVége Hai = i + 1HurokEnd Sub

End vagy Break Loop

Amint fentebb említettük, az Exit For vagy az Exit Do segítségével kiléphet a hurkokból:

1

Kilépés

1	Kilépés Do

Ezeket a parancsokat azonban hozzá kell adni a kódhoz a ciklus futtatása előtt.

Ha megpróbál „megszakítani” egy éppen futó hurkot, megpróbálhatja megnyomni KILÉPÉS vagy CTRL + Szünet a billentyűzeten. Ez azonban nem biztos, hogy működik. Ha nem működik, akkor várnia kell a ciklus végére, vagy végtelen ciklus esetén használni CTRL + ALT + Töröl kényszeríteni az Excel bezárását.

Ezért próbálom elkerülni a Do ciklusokat, könnyebb véletlenül létrehozni egy végtelen ciklust, amely arra kényszeríti, hogy indítsa újra az Excel -t, és potenciálisan elveszítheti munkáját.

További példák a hurokra

Loop keresztül sorok

Ez végigmegy az oszlop összes során:

123456789

Nyilvános alhurokThroughRows ()Homályos cella mint tartományA tartomány minden cellájához ("A: A")Ff cell.value "" majd MsgBox cell.address & ":" & cell.valueKövetkező cellaEnd Sub

Loop keresztül oszlopok

Ez végigmegy a sor összes oszlopán:

123456789

Nyilvános alhurok áthaladási oszlopok ()Homályos cella mint tartományA tartomány minden cellájához ("1: 1")Ha cell.Value "" Akkor MsgBox cell.Address & ":" & cell.ValueKövetkező cellaEnd Sub

Lépjen át a mappában lévő fájlokon

Ez a kód a mappában lévő összes fájlt végigjárja, és létrehoz egy listát:

12345678910111213141516171819

Sub LoopThroughFiles ()Dim oFSO mint objektumA mappát halványítsa objektumkéntDim oFile objektumkéntDim i As IntegerSet oFSO = CreateObject ("Scripting.FileSystemObject")Az oFolder = oFSO.GetFolder beállítása ("C: \ Demo)i = 2Minden oFile In oFolder.FilesTartomány ("A" & i) .value = oFile.Namei = i + 1Következő oFileEnd Sub

Loop Through Array

Ez a kód végigmegy az „arrList” tömbön:

123	I = LBound (arrList) - UBound (arrList)MsgBox arrList (i)Következő i

Az LBound függvény megkapja a tömb „alsó határát”, az UBound pedig a „felső határt”.

Loopok az Access VBA -ban

A fenti példák többsége az Access VBA -ban is működik. Az Accessben azonban a Recordset objektumon keresztül hurkolunk, nem pedig a Range objektumon.

123456789101112131415161718

Sub LoopThroughRecords ()Hiba esetén Folytassa a következőtDim dbs mint adatbázisDim rst RecordsetkéntÁllítsa be a dbs = CurrentDb értéketÁllítsa be az rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Az elsővel.MoveLast.MoveFirstTedd addig .EOF = IgazMsgBox (rst.Fields ("Ügyfélnév")).MoveNextHurokVége ezzelelső. Zárja beSet rst = SemmiSet dbs = SemmiEnd Sub