Abyste z Excelu a VBA získali maximum, potřebujete vědět, jak smyčky efektivně využívat.
Ve VBA vám smyčky umožňují procházet sadou objektů/hodnot a analyzovat ji jeden po druhém. Pro každou smyčku můžete také provádět konkrétní úkoly.
Zde je jednoduchý příklad použití smyček VBA v aplikaci Excel.
Předpokládejme, že máte datovou sadu a chcete zvýraznit všechny buňky v sudých řádcích. Pomocí smyčky VBA můžete procházet rozsah a analyzovat každé číslo řádku buňky. Pokud se ukáže, že je sudá, dáte jí barvu, jinak ji necháte tak, jak je.
Nyní je to samozřejmě velmi jednoduché ve smyčce v aplikaci Excel VBA (a můžete to také provést pomocí podmíněného formátování).
V reálném životě můžete udělat mnohem více se smyčkami VBA v aplikaci Excel, které vám pomohou automatizovat úkoly.
Zde je několik praktických příkladů, kde mohou být smyčky VBA užitečné:
- Smyčka v řadě buněk a analýza každé buňky (zvýrazněte buňky s konkrétním textem).
- Projděte si všechny pracovní listy a s každým něco proveďte (například jej ochraňte/odemkněte).
- Projděte všechny otevřené sešity (a uložte každý sešit nebo zavřete všechny kromě aktivního sešitu).
- Projděte všechny znaky v buňce (a extrahujte číselnou část z řetězce).
- Projděte všemi hodnotami pole.
- Projděte všechny grafy/objekty (a dejte ohraničení nebo změňte barvu pozadí).
Chcete -li nyní nejlépe používat smyčky v aplikaci Excel VBA, musíte vědět o různých typech, které existují, a o správné syntaxi každého z nich.
V tomto tutoriálu předvedu různé typy smyček Excel VBA a uvedu několik příkladů pro každou smyčku
Poznámka: Toto bude obrovský tutoriál, kde se pokusím podrobně pokrýt každou smyčku VBA. Doporučuji si tuto stránku uložit do záložek pro budoucí použití.Pokud máte zájem naučit se VBA jednoduchým způsobem, podívejte se na můj Online školení Excel VBA.
Pro další smyčku
Smyčka „Pro další“ vám umožňuje projít blokem kódu po zadaný počet opakování.
Pokud vás například požádám o manuální sečtení celých čísel od 1 do 10, přidali byste první dvě čísla, potom do výsledku přidali třetí číslo, pak do výsledku přidali čtvrté číslo atd.…
Není to tak?
Stejná logika se používá ve smyčce For Next ve VBA.
Můžete určit, kolikrát chcete smyčku spustit, a také určit, co má kód dělat při každém spuštění smyčky.
Níže je syntaxe smyčky For Next:
For Counter = začátek až konec [hodnota kroku] [blok kódu k provedení] další [čítač]
Ve smyčce For Next můžete použít Counter (nebo libovolnou proměnnou), která bude použita ke spuštění smyčky. Toto počítadlo vám umožňuje spustit tuto smyčku po požadovaný počet opakování.
Pokud například chci přidat prvních 10 kladných celých čísel, pak by moje hodnota čítače byla od 1 do 10.
Podívejme se na několik příkladů, abychom lépe porozuměli tomu, jak funguje smyčka For Next.
Příklad 1 - Přidání prvních 10 kladných celých čísel
Níže je uveden kód, který přidá prvních 10 kladných celých čísel pomocí smyčky For Next.
Poté se zobrazí okno se zprávou se součtem těchto čísel.
Sub AddNumbers () Dim Total as Integer Dim Count as Integer Total = 0 For Count = 1 To 10 Total = Total + Count Next Count MsgBox Total End Sub
V tomto kódu je hodnota Total nastavena na 0, než se dostanete do smyčky For Next.
Jakmile se dostane do smyčky, po každé smyčce udrží celkovou hodnotu. Takže po první smyčce, když je Counter 1, se hodnota „Total“ změní na 1 a po druhé smyčce na 3 (1+2) atd.
A nakonec, když smyčka skončí, proměnná „Celkem“ má součet prvních 10 kladných celých čísel.
MsgBox pak jednoduše zobrazí výsledek v okně se zprávou.
Příklad 2 - Přidání prvních 5 sudých pozitivních celých čísel
Abychom sečetli prvních pět sudých kladných celých čísel (tj. 2,4,6,8 a 10), potřebujete podobný kód s podmínkou, že budete brát v úvahu pouze sudá čísla a lichá čísla ignorovat.
Zde je kód, který to udělá:
Sub AddEvenNumbers () Dim Total as Integer Dim Count as Integer Total = 0 For Count = 2 to 10 Step 2 Total = Total + Count Next Count MsgBox Total End Sub
Všimněte si toho, že jsme spustili hodnotu Count od 2 a také jsme použili ‘Krok 2‘.
Když použijete 'Krok 2', řekne kódu, aby zvýšil hodnotu „Count“ o 2 při každém spuštění smyčky.
Hodnota Count tedy začíná od 2 a poté se stává 4, 6, 8 a 10, jak dochází k opakování.
POZNÁMKA: Dalším způsobem, jak toho dosáhnout, je spustit smyčku od 1 do 10 a ve smyčce zkontrolovat, zda je číslo sudé nebo liché. V tomto případě je však použití kroku efektivnější, protože nevyžaduje, aby se smyčka spustila 10krát, ale pouze 5krát.
Hodnota kroku může být také záporná. V takovém případě počitadlo začíná na vyšší hodnotě a stále se snižuje o zadanou hodnotu kroku.
Příklad 3 - Zadání sériového čísla do vybraných buněk
Smyčku For Next můžete také použít k procházení sbírky objektů (například buněk nebo pracovních listů nebo sešitů),
Zde je příklad, který rychle zadává sériová čísla do všech vybraných buněk.
Sub EnterSerialNumber () Dim Rng As Range Dim Counter As Integer Dim RowCount As Integer Set Rng = Selection RowCount = Rng.Rows.Count For Counter = 1 To RowCount ActiveCell.Offset (Counter - 1, 0) .Value = Counter Next Counter End Sub
Výše uvedený kód nejprve spočítá počet vybraných řádků a poté přiřadí tuto hodnotu proměnné RowCount. Poté spustíme smyčku od „1 do RowCount“.
Všimněte si také, že protože výběr může být libovolný počet řádků, nastavili jsme proměnnou Rng na výběr (s řádkem „Nastavit Rng = výběr“). Nyní můžeme použít proměnnou ‘Rng’ k označení výběru v našem kódu.
Příklad 4 - Chraňte všechny listy v aktivním sešitu
Pomocí smyčky „Pro další“ můžete procházet všechny listy v aktivním sešitu a chránit (nebo odemknout) každý z listů.
Níže je uveden kód, který to provede:
Sub ProtectWorksheets () Dim i As Integer For i = 1 To ActiveWorkbook.Worksheets.Count worksheets (i) .Protect Next i End Sub
Výše uvedený kód počítá počet listů pomocí ActiveWorkbook.Worksheets.Count. To říká VBA, kolikrát je třeba smyčku spustit.
V každém případě odkazuje na I -tý sešit (pomocí pracovních listů (i)) a chrání jej.
Stejný kód můžete použít také k odemknutí listů. Stačí změnit řádek Pracovní listy (i). Chraňte na Pracovní listy (i) .UnProtect.
Vnořené smyčky „Pro další“
Vnořené smyčky „Pro další“ můžete použít k dokončení složitější automatizace v aplikaci Excel. Vnořená smyčka „Pro další“ by znamenala, že ve smyčce „Pro další“ je smyčka „Pro další“.
Ukážu vám, jak to použít, na příkladu.
Předpokládejme, že mám v systému otevřeno 5 sešitů a chci chránit všechny listy ve všech těchto sešitech.
Níže je uveden kód, který to provede:
Sub ProtectWorksheets () Dim i As Integer Dim j As Integer For i = 1 to Workbooks.Count For j = 1 To Workbooks (i) .Worksheets.Count Workbooks (i) .Worksheets (j) .Protect Next j Next i End Sub
Výše uvedené je vnořená smyčka For Next, protože jsme použili jednu smyčku For Next v jiné.
Prohlášení „EXIT For“ v Pro další smyčky
Příkaz „Exit For“ vám umožňuje úplně opustit smyčku „For Next“.
Můžete jej použít v případech, kdy chcete, aby smyčka For Next skončila, když je splněna určitá podmínka.
Vezměme si příklad, kde máte ve sloupci A sadu čísel a chcete zvýraznit všechna záporná čísla červeným písmem. V tomto případě musíme každou buňku analyzovat na její hodnotu a poté podle toho změnit barvu písma.
Aby byl ale kód efektivnější, můžeme nejprve zkontrolovat, zda v seznamu nejsou nějaké záporné hodnoty nebo ne. Pokud neexistují žádné záporné hodnoty, můžeme příkaz Exit For jednoduše vyjít z kódu.
Níže je uveden kód, který to dělá:
Sub HghlightNegative () Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Counter = Rng.Count For i = 1 To Counter If WorksheetFunction.Min (Rng)> = 0 Pak Ukončete pro If Rng (i) .Hodnota <0 Then Rng (i) .Font.Color = vbRed Next i End Sub
Když použijete příkaz „Exit For“ ve vnořené smyčce „For Next“, vyjde ze smyčky, ve které je spuštěn, a provede další řádek v kódu za smyčkou For Next.
Například v níže uvedeném kódu vás příkaz „Exit For“ dostane z vnitřní smyčky, ale vnější smyčka bude nadále fungovat.
Sub SampleCode () For i = 1 to 10 For j = 1 to 10 Exit For Next J Next i End Sub
Do While Loop
Smyčka „Do While“ vám umožňuje zkontrolovat podmínku a spustit smyčku, dokud je tato podmínka splněna (nebo je PRAVDA).
Ve smyčce Do While Loop existují dva typy syntaxe.
Proveďte smyčku [While condition] [Code block to Execute]
a
Proveďte [Blok kódu k provedení] Smyčku [Zatímco podmínka]
Rozdíl mezi těmito dvěma je ten, že v prvním je podmínka While zkontrolována nejprve před provedením jakéhokoli bloku kódu a ve druhém případě je nejprve spuštěn blok kódu a poté je zkontrolována podmínka While.
To znamená, že pokud je podmínka While nepravdivá v obou případech, kód se přesto spustí alespoň jednou ve druhém případě (protože podmínka „Zatímco“ je zkontrolována poté, co byl kód proveden jednou).
Nyní se podívejme na několik příkladů použití smyček Do While ve VBA.
Příklad 1 - Přidejte prvních 10 pozitivních celých čísel pomocí VBA
Předpokládejme, že chcete přidat prvních deset kladných celých čísel pomocí smyčky Do While ve VBA.
Chcete -li to provést, můžete použít smyčku Do While, dokud není další číslo menší nebo rovné 10. Jakmile je číslo větší než 1o, vaše smyčka se zastaví.
Zde je kód VBA, který spustí tuto smyčku Do While a zobrazí výsledek v okně se zprávou.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i <= 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
Výše uvedená smyčka pokračuje v práci, dokud se hodnota „i“ nestane 11. Jakmile se stane 11, smyčka skončí (protože podmínka While se stane False).
Ve smyčce jsme použili proměnnou Result, která obsahuje konečnou hodnotu. Jakmile je smyčka dokončena, zobrazí se v okně se zprávou hodnota proměnné „Result“.
Příklad 2 - Zadejte data pro aktuální měsíc
Řekněme, že chcete do sloupce listu zadat všechna data aktuálního měsíce.
Můžete to provést pomocí následujícího kódu smyčky Do While:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Výše uvedený kód by zadal všechna data do prvního sloupce listu (od A1). Smyčky pokračují, dokud se hodnota měsíce proměnné „CMDate“ neshoduje s aktuálním měsícem.
Prohlášení o ukončení
K vyjití ze smyčky můžete použít příkaz Exit Do. Jakmile kód spustí řádek „Exit Do“, vyjde ze smyčky Do While a předá ovládací prvek na další řádek hned za smyčkou.
Pokud například chcete zadat pouze prvních 10 dat, můžete smyčku opustit, jakmile zadáte prvních 10 dat.
Následující kód to provede:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do While Month (CMDate) = Month (Date) Range ("A1"). Offset (i, 0) = CMDate i = i + 1 If i> = 10 Then Exit Do CMDate = CMDate + 1 Loop End Sub
Ve výše uvedeném kódu se příkaz IF používá ke kontrole, zda je hodnota i větší než 10 nebo ne. Jakmile se hodnota „i“ stane 10, provede se příkaz Exit Do a smyčka skončí.
Dělejte Do Loop
Smyčky „Do Do“ jsou velmi podobné smyčkám „Do While“.
V „Do While“ smyčka běží, dokud není splněna daná podmínka, zatímco v „Do While“ je ve smyčce, dokud není splněna zadaná podmínka.
V Do Do Loop existují dva typy syntaxe.
Proveďte smyčku [Do podmínky] [Blok kódu k provedení]
a
Proveďte [Blok kódu k provedení] Smyčku [Do podmínky]
Rozdíl mezi těmito dvěma je v tom, že v prvním je nejprve zkontrolována podmínka Before, než je proveden jakýkoli blok kódu, a ve druhém případě je nejprve spuštěn blok kódu a poté je zkontrolována podmínka Before.
To znamená, že pokud je podmínka Before PRAVDA v obou případech, kód se ve druhém případě stále spustí alespoň jednou (protože podmínka ‘Do‘ je zkontrolována poté, co byl kód proveden jednou).
Nyní se podívejme na několik příkladů použití smyček Do Before ve VBA.
Poznámka: Všechny příklady pro Do Do jsou stejné jako u Do While. Ty byly upraveny tak, aby vám ukázaly, jak funguje smyčka Do Before.
Příklad 1 - Přidejte prvních 10 pozitivních celých čísel pomocí VBA
Předpokládejme, že chcete přidat prvních deset kladných celých čísel pomocí smyčky Do Do ve VBA.
Chcete -li to provést, musíte spustit smyčku, dokud nebude další číslo menší nebo rovné 10. Jakmile je číslo větší než 1 °, vaše smyčka se zastaví.
Zde je kód VBA, který spustí tuto smyčku a zobrazí výsledek v okně se zprávou.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do Do i> 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
Výše uvedená smyčka pokračuje v práci, dokud se hodnota „i“ nestane 11. Jakmile se stane 11, smyčka skončí (protože podmínka „Do“ se stane skutečností).
Příklad 2 - Zadejte data pro aktuální měsíc
Řekněme, že chcete do sloupce listu zadat všechna data aktuálního měsíce.
Můžete to udělat pomocí následujícího cyklu smyčky Do Do:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do Before Month (CMDate) Month (Date) Range ("A1"). Offset ( i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Konec smyčky Sub
Výše uvedený kód by zadal všechna data do prvního sloupce listu (od A1). Smyčka pokračuje, dokud se Měsíc proměnné CMDate nerovná aktuálnímu měsíci.
Prohlášení o ukončení
K vyjití ze smyčky můžete použít příkaz „Exit Do“.
Jakmile kód spustí řádek „Exit Do“, vyjde ze smyčky Do Then a předá ovládací prvek na další řádek hned za smyčkou.
Pokud například chcete zadat pouze prvních 10 dat, můžete smyčku opustit, jakmile zadáte prvních 10 dat.
Následující kód to provede:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Year (Date), Month (Date), 1) Do Before Month (CMDate) Month (Date) Range ("A1"). Offset ( i, 0) = CMDate i = i + 1 If i> = 10 Then Exit Do CMDate = CMDate + 1 Loop End Sub
Ve výše uvedeném kódu, jakmile se hodnota „i“ stane 10, provede se příkaz Exit Do a smyčka skončí.
Pro každého
Ve VBA můžete procházet sadou kolekcí pomocí smyčky „Pro každého“.
Zde je několik příkladů sbírek v aplikaci Excel VBA:
- Sbírka všech otevřených sešitů.
- Sbírka všech pracovních listů v sešitu.
- Kolekce všech buněk v rozsahu vybraných buněk.
- Kolekce všech grafů nebo tvarů v sešitu.
Pomocí smyčky „Pro každý“ můžete projít každý z objektů ve sbírce a provést s ní nějakou akci.
Můžete například procházet všechny listy v sešitu a chránit je, nebo můžete procházet všechny buňky ve výběru a změnit formátování.
U smyčky „Pro každého“ (také označované jako smyčka „Pro každého dalšího“) nemusíte vědět, kolik předmětů se ve sbírce nachází.
Smyčka „Pro každého“ by automaticky prošla každým objektem a provedla zadanou akci. Pokud například chcete chránit všechny listy v sešitu, kód by byl stejný, ať už máte sešit se 3 listy nebo 30 listy.
Zde je syntaxe smyčky For Every-Next v aplikaci Excel VBA.
Pro každý prvek ve sbírce [Blok kódu k provedení] Další [prvek]
Nyní se podívejme na několik příkladů použití pro každou smyčku v aplikaci Excel.
Příklad 1 - Projděte si všechny pracovní listy v sešitu (a chraňte jej)
Předpokládejme, že máte sešit, kde chcete chránit všechny listy.
Níže pro smyčku Every-Next to lze snadno provést:
Sub ProtectSheets () Dim ws as worksheet for each ws in ActiveWorkbook.Worksheets ws.Protect Next ws End Sub
Ve výše uvedeném kódu jsme definovali proměnnou „ws“ jako objekt listu. To říká VBA, že „ws“ by mělo být v kódu interpretováno jako objekt listu.
Nyní pomocí příkazu „Pro každého“ projdeme každé „ws“ (což je objekt listu) ve sbírce všech listů v aktivním sešitu (dáno ActiveWorkbook.Worksheets).
Všimněte si toho, že na rozdíl od jiných smyček, kde jsme se pokusili chránit všechny listy v sešitu, se zde nemusíme starat o to, kolik listů je v sešitu.
Pro spuštění smyčky je nemusíme počítat. Pro každou smyčku zajišťuje, že jsou všechny objekty analyzovány jeden po druhém.
Příklad 2 - Projděte si všechny otevřené sešity (a uložte vše)
Pokud pracujete s více sešity současně, může se vám hodit uložit všechny tyto sešity najednou.
Níže uvedený kód VBA to může udělat za nás:
Sub SaveAllWorkbooks () Dim wb as Workbook for each wb in Workbooks wb.Save Next wb End Sub
Všimněte si, že v tomto kódu se nezobrazí výzva k uložení sešitu na konkrétní místo (pokud ho ukládáte poprvé).
Uloží jej do výchozí složky (v mém případě to byla složka „Dokumenty“). Tento kód funguje nejlépe, když jsou tyto soubory již uloženy a provádíte změny a chcete rychle uložit všechny sešity.
Příklad 3 - Projděte všechny buňky ve výběru (zvýrazněte záporné hodnoty)
Pomocí smyčky „Pro každého“ můžete procházet všemi buňkami v určitém rozsahu nebo ve vybraném rozsahu.
To může být užitečné, když chcete analyzovat každou buňku a na jejím základě provést akci.
Níže je například kód, který projde všemi buňkami ve výběru a změní barvu buněk buněk se zápornými hodnotami na červenou.
Sub HighlightNegativeCells () Dim Cll as Range for each Cll in Selection If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
(Všimněte si, že jsem použil Cll jako krátký název proměnné pro Cell. Jako názvy proměnných je vhodné nepoužívat názvy objektů, jako jsou například Sheets nebo Range)
Ve výše uvedeném kódu smyčka For Every-Next prochází kolekcí buněk ve výběru. Příkaz IF se používá k identifikaci, zda je hodnota buňky záporná nebo ne. V případě, že ano, buňka dostane červenou vnitřní barvu, jinak přejde do další buňky.
V případě, že nemáte výběr a místo toho chcete, aby VBA vybral všechny vyplněné buňky ve sloupci, počínaje konkrétní buňkou (stejně jako my používáme Control + Shift + šipka dolů pro výběr všech vyplněných buněk), můžete použijte níže uvedený kód:
Sub HighlightNegativeCells () Dim Cll As Range Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) For each Cll In Rng If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
Ve výše uvedeném příkladu nezáleží na tom, kolik naplněných buněk je. Začne od buňky A1 a analyzuje všechny sousedící vyplněné buňky ve sloupci.
Rovněž nemusíte mít vybranou buňku A1. Můžete vybrat libovolnou vzdálenou buňku a při spuštění kódu bude stále zvažovat všechny buňky ve sloupci A (od A1) a vybarvit negativní buňky.
Prohlášení „Exit For“
Pro vyjití ze smyčky můžete použít příkaz „Exit For“ ve smyčce For Every-Next. To se obvykle provádí v případě, že je splněna konkrétní podmínka.
Například v příkladu 3, když procházíme sadou buněk, může být efektivnější zkontrolovat, zda existují nějaké záporné hodnoty nebo ne. V případě, že neexistují žádné záporné hodnoty, můžeme smyčku jednoduše opustit a ušetřit čas zpracování VBA.
Níže je uveden kód VBA, který to provede:
Sub HighlightNegativeCells () Dim Cll as Range for each Cll in Selection If WorksheetFunction.Min (Selection)> = 0 then Exit For If Cll.Value <0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
Kam vložit kód VBA
Zajímá vás, kam jde kód VBA do vašeho sešitu v Excelu?
Excel má VBA backend nazvaný VBA editor. Musíte zkopírovat a vložit kód do okna kódu modulu VB Editor.
Zde jsou kroky, jak toho dosáhnout:
- Přejděte na kartu Vývojář.
- Klikněte na možnost Visual Basic. Tím se otevře editor VB v backendu.
- V podokně Průzkumník projektů v editoru VB klikněte pravým tlačítkem na libovolný objekt sešitu, do kterého chcete vložit kód. Pokud Průzkumníka nevidíte, přejděte na kartu Zobrazit a klikněte na Průzkumník projektů.
- Přejděte na Vložit a klikněte na Modul. Tím se vloží objekt modulu pro váš sešit.
- Zkopírujte a vložte kód do okna modulu.
