Lai efektīvi darbotos VBA, jums ir jāsaprot cilpas.
Cilpas ļauj atkārtot koda bloku noteiktu skaitu reižu vai atkārtot koda bloku katram objektam objektu kopā.
Vispirms mēs parādīsim dažus piemērus, lai parādītu, ko cilpas spēj. Tad mēs iemācīsim jums visu par cilpām.
VBA cilpas ātrie piemēri
Katrai cilpai
Katrai cilpai cilpa caur katru kolekcijas objektu, piemēram, katru darbgrāmatas darblapu vai katru diapazona šūnu.
Izlasiet visas darbgrāmatas darblapas
Šis kods parādīs visas darbgrāmatas darblapas, atverot katru lapu:
| 12345678 | Sub LoopThroughSheets ()Dim ws kā darblapaPar katru ws darblapāsws.Visible = TrueNākamaisBeigu apakš |
Izslēgt visas diapazona šūnas
Šis kods pārvietosies pa virkni šūnu, pārbaudot, vai šūnas vērtība ir negatīva, pozitīva vai nulle:
| 1234567891011121314 | Apakš If_Loop ()Dim šūna kā diapazonsKatrai diapazona šūnai ("A2: A6")Ja šūna.vērtība> 0 TadŠūna. Nobīde (0, 1). Vērtība = "Pozitīvs"ElseIf Cell.Value <0 TadŠūna.Pārvietojums (0, 1). Vērtība = "Negatīvs"CitādiŠūna. Nobīde (0, 1). Vērtība = "Nulle"Beigas JaNākamā šūnaBeigu apakš |
Nākamajām cilpām
Cits cilpas “For” veids ir For Next Loop. For Next Loop ļauj pārvietoties pa veseliem skaitļiem.
Šis kods tiks rādīts caur veseliem skaitļiem no 1 līdz 10, parādot katru ar ziņojumu lodziņu:
| 123456 | Sub ForLoop ()Dim i As IntegerJa i = 1 līdz 10MsgBox iTālāk iBeigu apakš |
Darīt, kamēr cilpas
Darīt, kamēr cilpas darbosies, kamēr būs izpildīts nosacījums. Šis kods arī cilpos caur veseliem skaitļiem no 1 līdz 10, parādot katru ar ziņojumu lodziņu.
| 12345678 | Sub DoWhileLoop ()Dim n Kā vesels skaitlisn = 1Darīt, kamēr n <11MsgBox nn = n + 1CilpaBeigu apakš |
Vai līdz cilpām
Savukārt Do Do Loops darbosies, līdz tiks izpildīts kāds nosacījums. Šis kods dara to pašu, ko divi iepriekšējie piemēri.
| 12345678 | Sub DoUntilLoop ()Dim n Kā vesels skaitlisn = 1Dariet līdz n> = 10MsgBox nn = n + 1CilpaBeigu apakš |
Mēs to apspriedīsim tālāk, taču jums ir jābūt ļoti uzmanīgam, veidojot cilnes “Do while” vai “Do Before”, lai neradītu nebeidzamu cilpu.
VBA cilpu veidotājs
Šis ir “Loop Builder” ekrānuzņēmums no mūsu Premium VBA pievienojumprogrammas: AutoMacro. Cilpu veidotājs ļauj ātri un viegli izveidot cilpas, lai cilpotu dažādus objektus vai ciparus. Jūs varat veikt darbības ar katru objektu un/vai atlasīt tikai objektus, kas atbilst noteiktiem kritērijiem.
Papildinājumā ir arī daudzi citi kodu veidotāji, plaša VBA kodu bibliotēka un kodēšanas rīku sortiments. Tas ir nepieciešams jebkuram VBA izstrādātājam.
Tagad mēs detalizēti aplūkosim dažāda veida cilpas.
VBA nākamajai cilpai
Cilpas sintaksei
Nākamā cilpa ļauj atkārtot koda bloku noteiktu skaitu reižu. Sintakse ir šāda:
| 12345 | [Dim skaitītājs kā vesels skaitlis]Skaitītājam = no sākuma līdz beigām [soļa vērtība][Dari kaut ko]Nākamais [skaitītājs] |
Ja iekavās esošie vienumi nav obligāti.
- [Dim skaitītājs tik ilgi] - deklarē skaitītāja mainīgo. Obligāti, ja moduļa augšpusē ir norādīta opcija Explicit.
- Skaitītājs - skaitīšanai izmantots vesels skaitlis
- Sākt - sākuma vērtība (piem. 1)
- Beigas - beigu vērtība (piem. 10)
- [Step Value] - Ļauj saskaitīt katru n veselu skaitli, nevis katru 1 veselu skaitli. Varat arī apgriezt ar negatīvu vērtību (piem., 1. darbība)
- [Dari kaut ko] - Kods, kas atkārtosies
- Nākamais [skaitītājs] - noslēguma paziņojums par nākamo cilpu. Jūs varat iekļaut skaitītāju vai nē. Tomēr es ļoti iesaku iekļaut skaitītāju, jo tas atvieglo jūsu koda lasīšanu.
Ja tas ir mulsinoši, neuztraucieties. Mēs pārskatīsim dažus piemērus:
Skaitiet līdz 10
Šis kods tiks skaitīts līdz 10, izmantojot nākamo cilpu:
| 12345678 | Sub ForEach_CountTo10 ()Dim n Kā vesels skaitlisJa n = 1 līdz 10MsgBox nNākamais nBeigu apakš |
Loop Step
Skaitīt līdz 10 - tikai pāra skaitļi
Šis kods tiks skaitīts līdz 10, skaitot tikai pāra skaitļus:
| 12345678 | Sub ForEach_CountTo10_Even ()Dim n Kā vesels skaitlisN = 2 līdz 10 2. darbībaMsgBox nNākamais nBeigu apakš |
Ievērojiet, ka mēs pievienojām “2. darbību”. Tas norāda, ka cilpai For Loop “jāiet cauri skaitītājam par 2. Mēs varam izmantot arī negatīvu soļa vērtību, lai solītu pretējā virzienā:
Cilpas solim - apgriezts
Atpakaļskaitīšana no 10
Šis kods tiks atskaitīts no 10:
| 123456789 | Sub ForEach_Countdown_Inverse ()Dim n Kā vesels skaitlisAttiecībā uz n = 10 līdz 1 Solis -1MsgBox nNākamais nMsgBox "pacelt"Beigu apakš |
Dzēst rindas, ja šūna ir tukša
Visbiežāk esmu izmantojis negatīvu soli For-Loop, lai pārvietotos pa šūnu diapazoniem, dzēšot rindas, kas atbilst noteiktiem kritērijiem. Ja jūs cilpa no augšējām rindām uz apakšējām rindām, dzēšot rindas, jūs sajauksit savu skaitītāju.
Šis piemērs izdzēsīs rindas ar tukšām šūnām (sākot no apakšējās rindas):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells ()Dim n Kā vesels skaitlisAttiecībā uz n = 10 līdz 1 Solis -1Ja diapazons ("a" & n) .Value = "" TadDiapazons ("a" & n) .Visa rinda.DeleteBeigas JaNākamais nBeigu apakš |
Ligzdota cilpai
Jūs varat “ligzdot” vienu cilpu citā cilpā cilpai. Mēs izmantosim Nested For Loops, lai izveidotu reizināšanas tabulu:
| 1234567891011 | Apakš ligzdota_ForEach_MultiplicationTable ()Rindot kā veselu skaitli, kolonu kā veselu skaitliRindai = 1 līdz 9Kolonai = 1 līdz 9Šūnas (rinda + 1, kolonna + 1). Vērtība = rinda * kolonnaNākamā kolNākamā rindaBeigu apakš |
Iziet uz
Paziņojums Exit For ļauj nekavējoties iziet no cilpas For Next.
Jūs parasti izmantotu Exit For kopā ar paziņojumu If, ja izietu no nākamās cilpas, ja ir izpildīts kāds nosacījums.
Piemēram, šūnas atrašanai varat izmantot cilpu For Loop. Kad šī šūna ir atrasta, varat iziet no cilpas, lai paātrinātu kodu.
Šis kods pārvietosies no 1. līdz 1000. rindai, meklējot “kļūdu” A slejā. Ja tas tiek atrasts, kods atlasīs šūnu, brīdinās par atrasto kļūdu un izies no cilpas:
| 12345678910111213 | Sub ExitFor_Loop ()Dim i As IntegerJa i = 1 līdz 1000Ja diapazons ("A" & i) .Vērtība = "kļūda" TadDiapazons ("A" un i). AtlasietMsgBox "Atrasta kļūda"Iziet uzBeigas JaTālāk iBeigu apakš |
Svarīgi: ligzdotu cilpu gadījumā Exit For iziet tikai no pašreizējās cilpas, nevis visas aktīvās cilpas.
Turpināt Par
VBA nav komandas “Turpināt”, kas atrodama Visual Basic. Tā vietā jums būs jāizmanto “Iziet”.
VBA katrai cilpai
Katras cilpas VBA cilpa cauri visiem kolekcijas objektiem:
- Visas šūnas diapazonā
- Visas darblapas darbgrāmatā
- Visas formas darblapā
- Visas atvērtās darbgrāmatas
Varat arī izmantot ligzdu katrai cilpai, lai:
- Visas šūnas diapazonā visās darblapās
- Visas formas uz visām darblapām
- Visas lapas visās atvērtās darbgrāmatās
- un tā tālāk…
Sintakse ir šāda:
| 123 | Katram kolekcijas objektam[Dari kaut ko]Nākamais [objekts] |
Kur:
- Objekts - Mainīgais lielums, kas pārstāv diapazonu, darblapu, darbgrāmatu, formu utt. (Piemēram, rng)
- Kolekcija - objektu kolekcija (piem. Diapazons (“a1: a10”)
- [Dari kaut ko] - Koda bloks, lai palaistu katru objektu
- Nākamais [objekts] - Noslēguma paziņojums. [Objekts] nav obligāts, tomēr stingri ieteicams.
Katrai diapazona šūnai
Šis kods pārvietosies pa katru šūnu diapazonā:
| 123456789 | Sub ForEachCell_inRange ()Dim šūna kā diapazonsKatrai diapazona šūnai ("a1: a10")šūna. Vērtība = šūna. Nobīde (0,1). VērtībaNākamā šūnaBeigu apakš |
Par katru darblapu darbgrāmatā
Šis kods parādīs visas darbgrāmatas darblapas, noņemot aizsardzību no katras lapas:
| 123456789 | Sub ForEachSheet_inWorkbook ()Dim ws kā darblapaPar katru ws darblapāsws.Aizsargāt "paroli"Nākamais wsBeigu apakš |
Par katru atvērto darbgrāmatu
Šis kods saglabās un aizvērs visas atvērtās darbgrāmatas:
| 123456789 | Sub ForEachWB_inWorkbooks ()Dim wb kā darbgrāmataPar katru wb darbgrāmatāswb. Aizvērt SaveChanges: = TrueNākamais wbBeigu apakš |
Katrai darblapas formai
Šis kods izdzēsīs visas formas aktīvajā lapā.
| 123456789 | Sub ForEachShape ()Dim shp kā formaPar katru shp ActiveSheet.Shapesshp. DzēstNākamais shpBeigu apakš |
Katrai formai katrā darbgrāmatas darblapā
Varat arī ligzdot katrai cilpai. Šeit mēs apskatīsim visas formas visās darblapas darblapās:
| 1234567891011 | Sub ForEachShape_inAllWorksheets ()Dim shp kā forma, ws kā darblapaPar katru ws darblapāsPar katru shp In ws.Shapesshp. DzēstNākamais shpNākamais wsBeigu apakš |
Katram - JA cilpa
Kā jau minējām iepriekš, cilnē varat izmantot paziņojumu If, veicot darbības tikai tad, ja ir izpildīti noteikti kritēriji.
Šis kods paslēps visas tukšās rindas diapazonā:
| 12345678910 | Sub ForEachCell_inRange ()Dim šūna kā diapazonsKatrai diapazona šūnai ("a1: a10")Ja šūna.Vērtība = "" Tad _cell.EntireRow.Hidden = PatiessNākamā šūnaBeigu apakš |
VBA Dariet ciklu
VBA Do Do un Do Do (skatīt nākamo sadaļu) ir ļoti līdzīgi. Viņi atkārtos cilpu, kamēr (vai līdz) nosacījums ir izpildīts.
Darba cilpa atkārto ciklu, kamēr tiek izpildīts nosacījums.
Šeit ir sintakse Darīt, kamēr:
| 123 | Darīt, kamēr stāvoklis[Dari kaut ko]Cilpa |
Kur:
- Stāvoklis - nosacījums, lai pārbaudītu
- [Dari kaut ko] - Atkārtojams koda bloks
Varat arī iestatīt ciklu “Do Do” ar nosacījumu cikla beigās:
| 123 | Vai[Dari kaut ko]Cilpa, kamēr stāvoklis |
Mēs demonstrēsim katru no tiem un parādīsim, kā tie atšķiras:
Darīt kamēr
Šeit ir cikls Do Do, ko mēs parādījām iepriekš:
| 12345678 | Sub DoWhileLoop ()Dim n Kā vesels skaitlisn = 1Darīt, kamēr n <11MsgBox nn = n + 1CilpaBeigu apakš |
Cilpa kamēr
Tagad veiksim to pašu procedūru, izņemot to, ka mēs pārvietosim nosacījumu uz cilpas beigām:
| 12345678 | Sub DoLoopWhile ()Dim n Kā vesels skaitlisn = 1VaiMsgBox nn = n + 1Cilpa Kamēr n <11Beigu apakš |
VBA Vai līdz cilpai
Darīt, līdz cilpas atkārto ciklu, līdz tiek izpildīts noteikts nosacījums. Sintakse būtībā ir tāda pati kā cilnēm Do Do:
| 123 | Dariet līdz nosacījumam[Dari kaut ko]Cilpa |
un līdzīgi stāvoklis var notikt cikla sākumā vai beigās:
| 123 | Vai[Dari kaut ko]Cikls līdz stāvoklim |
Dariet līdz
Atkārtojiet šo ciklu līdz 10, tāpat kā mūsu iepriekšējos piemēros
| 12345678 | Sub DoUntilLoop ()Dim n Kā vesels skaitlisn = 1Dariet līdz n> 10MsgBox nn = n + 1CilpaBeigu apakš |
Cilpa līdz
Šī cilpa līdz cilpai tiks skaitīta līdz 10:
| 12345678 | Sub DoLoopUntil ()Dim n Kā vesels skaitlisn = 1VaiMsgBox nn = n + 1Cikls līdz n> 10Beigu apakš |
Iziet Do Loop
Līdzīgi kā Exit For, lai izietu no For Loop, jūs izmantojat Exit Do komandu, lai nekavējoties izietu no Do Loop
| 1 | Iziet Do |
Šeit ir Exit Do piemērs:
| 123456789101112131415 | Sub ExitDo_Loop ()Dim i As Integeri = 1Dariet, līdz i> 1000Ja diapazons ("A" & i) .Vērtība = "kļūda" TadDiapazons ("A" un i). AtlasietMsgBox "Atrasta kļūda"Iziet DoBeigas Jai = i + 1CilpaBeigu apakš |
Beigt vai pārtraukt cilpu
Kā minēts iepriekš, lai izietu no cilpām, varat izmantot Exit For vai Exit Do:
| 1 | Iziet uz |
| 1 | Iziet Do |
Tomēr šīs komandas ir jāpievieno jūsu kodam pirms cilpas palaišanas.
Ja jūs mēģināt “pārtraukt” cilpu, kas pašlaik darbojas, varat mēģināt nospiest ESC vai CTRL + Pauzes pārtraukums uz tastatūras. Tomēr tas var nedarboties. Ja tas nedarbojas, jums jāgaida cikla beigas vai bezgalīgas cilpas gadījumā jāizmanto CTRL + ALT + Dzēst piespiest aizvērt Excel.
Tāpēc es cenšos izvairīties no Do cilpām, ir vieglāk nejauši izveidot bezgalīgu cilpu, liekot jums restartēt Excel, iespējams, zaudējot darbu.
Citi cilpu piemēri
Cilpa caur rindām
Tas parādīs visas kolonnas rindas:
| 123456789 | Publiskā apakšlīnijaThroughRows ()Dim šūna kā diapazonsKatrai diapazona šūnai ("A: A")Ff cell.value "", pēc tam MsgBox cell.address & ":" & cell.valueNākamā šūnaBeigu apakš |
Izslēgt kolonnas
Tas parādīs visas kolonnas pēc kārtas:
| 123456789 | Publiskās apakškopas kolonnas ()Dim šūna kā diapazonsKatrai diapazona šūnai ("1: 1")If cell.Value "" Tad MsgBox cell.Address & ":" & cell.ValueNākamā šūnaBeigu apakš |
Pārskatīt failus mapē
Šis kods aplūkos visus failus mapē, izveidojot sarakstu:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO kā objektsIzmainiet mapi kā objektuDim oFile kā objektsDim i As IntegerIestatīt oFSO = CreateObject ("Scripting.FileSystemObject")Iestatīt oFolder = oFSO.GetFolder ("C: \ Demo)i = 2Par katru oFile In oFolder.FilesDiapazons ("A" & i) .value = oFile.Namei = i + 1Nākamais failsBeigu apakš |
Cilpa caur masīvu
Šis kods iet caur masīvu “arrList”:
| 123 | I = LBound (arrList) līdz UBound (arrList)MsgBox arrList (i)Tālāk i |
Funkcija LBound iegūst masīva “apakšējo robežu”, un UBound iegūst “augšējo robežu”.
Cilpas Access VBA
Lielākā daļa iepriekš minēto piemēru darbosies arī Access VBA. Tomēr programmā Access mēs izmantojam ierakstu kopas objektu, nevis diapazona objektu.
| 123456789101112131415161718 | Sub LoopThroughRecords ()Par kļūdu Atsākt nākamoDim dbs kā datu bāzeIzmērs kā ierakstsIestatīt dbs = CurrentDbIestatiet rst = dbs.OpenRecordset ("tblClients", dbOpenDynaset)Ar pirmo.MoveLast.MoveFirstDarīt līdz .EOF = TrueMsgBox (rst.Fields ("Klienta nosaukums")).PārvietotNākamaisCilpaBeigt arpirmais. AizvērtIestatīt pirmo: nekasSet dbs = NekasBeigu apakš |
