Če želite kar najbolje izkoristiti Excel in VBA, morate vedeti, kako učinkovito uporabljati zanke.
V VBA vam zanke omogočajo, da greste skozi niz objektov/vrednosti in jih analizirate enega za drugim. Za vsako zanko lahko izvedete tudi posebna opravila.
Tukaj je preprost primer uporabe zank VBA v Excelu.
Recimo, da imate nabor podatkov in želite označiti vse celice v enakomernih vrsticah. Z zanko VBA lahko prečkate obseg in analizirate številko vrstice vsake celice. Če se izkaže za enakomerno, mu daš barvo, drugače pa pustiš takšnega, kot je.
Seveda je to zelo preprosto zankanje v Excelu VBA (to lahko storite tudi s pogojnim oblikovanjem).
V resničnem življenju lahko z zankami VBA v Excelu naredite veliko več, kar vam lahko pomaga pri avtomatizaciji opravil.
Tu je še nekaj praktičnih primerov, kjer so lahko zanke VBA uporabne:
- Ponavljanje po vrsti celic in analiziranje vsake celice (označite celice z določenim besedilom).
- Prelistajte vse delovne liste in z vsakim naredite nekaj (na primer zaščitite/odstranite zaščito).
- Prelistajte vse odprte delovne zvezke (in shranite vsak delovni zvezek ali zaprite vse razen aktivnega delovnega zvezka).
- Prelistajte vse znake v celici (in izvlecite številski del iz niza).
- Preglejte vse vrednosti v nizu.
- Prelistajte vse grafikone/predmete (in podajte obrobo ali spremenite barvo ozadja).
Zdaj, če želite najbolje uporabiti zanke v Excelu VBA, morate poznati različne vrste, ki obstajajo, in pravilno sintakso vsake od njih.
V tej vadnici bom predstavil različne vrste zank Excel VBA in obravnaval nekaj primerov za vsako zanko
Opomba: To bo velika vadnica, kjer bom poskušal podrobno opisati vsako zanko VBA. Priporočam, da to stran dodate med zaznamke za prihodnjo uporabo.Če vas zanima učenje VBA na enostaven način, si oglejte moj Spletno usposabljanje Excel VBA.
Za naslednjo zanko
Zanka 'Za naprej' vam omogoča, da določeno število krat preidete skozi blok kode.
Na primer, če vas prosim, da ročno dodate cela števila od 1 do 10, bi dodali prvi dve številki, nato k rezultatu dodali še tretjo številko, nato pa k rezultatu dodali četrto številko, itd.
Kajne?
Ista logika se uporablja v zanki For Next v VBA.
Določite, kolikokrat želite, da se zanka izvaja, in tudi, kaj želite, da koda izvede ob vsakem zagonu zanke.
Spodaj je sintaksa zanke For Next:
Za števec = Začni do konca [Vrednost koraka] [Blokiranje kode za izvedbo] Naprej [števec]
V zanki For Next lahko uporabite števec (ali katero koli spremenljivko), ki bo uporabljena za izvajanje zanke. Ta števec vam omogoča, da to zanko izvedete zahtevano število krat.
Na primer, če želim dodati prvih 10 pozitivnih celih števil, bi bila moja vrednost števca od 1 do 10.
Oglejmo si nekaj primerov, da bi bolje razumeli, kako deluje zanka For Next.
Primer 1 - Dodajanje prvih 10 pozitivnih celih števil
Spodaj je koda, ki bo dodala prvih 10 pozitivnih celih števil z zanko For Next.
Nato se prikaže okno s sporočilom, ki prikazuje vsoto teh številk.
Podštevilke podštevilk () Skupaj zatemni kot celo število Zatemni štetje kot celo število = 0 Za štetje = 1 do 10 Skupaj = Skupaj + Število Naslednje število MsgBox Skupaj konec pod
V tej kodi je vrednost Total nastavljena na 0, preden vstopi v zanko For Next.
Ko pride v zanko, zadrži skupno vrednost po vsaki zanki. Torej po prvi zanki, ko je števec 1, vrednost 'Skupaj' postane 1, po drugi zanki pa 3 (1+2) itd.
In končno, ko se zanka konča, ima spremenljivka "Skupaj" vsoto prvih 10 pozitivnih celih števil.
MsgBox nato preprosto prikaže rezultat v polju za sporočila.
Primer 2 - Dodajanje prvih 5 celo pozitivnih celih števil
Če seštejete prvih pet parnih celih števil (tj. 2,4,6,8 in 10), potrebujete podobno kodo s pogojem, da upoštevate le parna števila in prezrete liha števila.
Tukaj je koda, ki bo to naredila:
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
Upoštevajte, da smo vrednost Count začeli z 2 in uporabili tudi '2. korak‘.
Ko uporabljate "2. korak", pove kodi, da vsakič, ko se zažene zanka, poveča vrednost 'Count' za 2.
Tako se vrednost Count začne od 2 in nato postane 4, 6, 8 in 10, ko pride do zanke.
OPOMBA: Drug način za to je lahko zagon zanke od 1 do 10 in znotraj zanke preverite, ali je število sodo ali liho. Vendar je uporaba Step v tem primeru učinkovitejši način, saj zanka ne zahteva, da se zanka izvaja 10 -krat, ampak le 5 -krat.
Vrednost koraka je lahko tudi negativna. V takem primeru se števec začne z višjo vrednostjo in se za določeno vrednost koraka še naprej zmanjšuje.
Primer 3 - Vnos serijske številke v izbrane celice
Z zanko For Next lahko pregledate tudi zbirko predmetov (na primer celice ali delovne liste ali delovne zvezke),
Tu je primer, ki hitro vnese zaporedne številke v vse izbrane celice.
Sub EnterSerialNumber () Dim Rng kot obseg Dim Counter As Integer Dim RowCount As Integer Set Rng = Selection RowCount = Rng.Rows.Count For Counter = 1 Za RowCount ActiveCell.Offset (Števec - 1, 0). Vrednost = Števec Naslednji konec števca Pod
Zgornja koda najprej prešteje število izbranih vrstic in nato to vrednost dodeli spremenljivki RowCount. Nato izvedemo zanko od '1 do RowCount'.
Upoštevajte tudi, da je izbira lahko poljubno število vrstic, zato smo spremenljivko Rng nastavili na izbor (s vrstico »Nastavi Rng = izbor«). Zdaj se lahko s spremenljivko 'Rng' sklicujemo na izbiro v naši kodi.
Primer 4 - Zaščitite vse delovne liste v aktivnem delovnem zvezku
Z zanko »Za naprej« lahko prelistate vse delovne liste v aktivnem delovnem zvezku in zaščitite (ali odstranite zaščito) vsakega delovnega lista.
Spodaj je koda, ki bo to naredila:
Sub ProtectWorksheets () Dim i As Integer For i = 1 Za ActiveWorkbook.Worksheets.Count Worksheets (i). Protect Next i End Sub
Zgornja koda šteje število listov z uporabo ActiveWorkbook.Worksheets.Count. To pove VBA, kolikokrat je treba zanko zagnati.
V vsakem primeru se sklicuje na I -ti delovni zvezek (z uporabo delovnih listov (i)) in ga varuje.
To isto kodo lahko uporabite tudi za odstranjevanje zaščite delovnih listov. Samo spremenite črto Delovni listi (i). Zaščitite do Delovni listi (i). UnProtect.
Vgnezdene zanke »Za naslednjo«
Za bolj zapleteno avtomatizacijo v Excelu lahko uporabite ugnezdene zanke »Za naslednje«. Vgnezdena zanka "Za naslednjo" bi pomenila, da je v zanki "Za naslednjo" zanka "Za naslednjo".
Naj vam na primeru pokažem, kako to uporabiti.
Recimo, da imam v sistemu odprtih 5 delovnih zvezkov in želim zaščititi vse delovne liste v vseh teh delovnih zvezkih.
Spodaj je koda, ki bo to naredila:
Podzaščitni delovni listi () Dim i kot celo število Dim j kot celo število za i = 1 za delovne zvezke. Štetje za j = 1 za delovne zvezke (i). Delovne liste. Štetje delovnih zvezkov (i). Delovne liste (j).
Zgornje je ugnezdena zanka For Next, saj smo eno zanko For Next uporabili v drugi.
Izjave 'EXIT For' v Za naslednje zanke
Izjava 'Exit For' vam omogoča, da popolnoma zapustite zanko 'For Next'.
Uporabite ga lahko v primerih, ko želite, da se zanka For Next konča, ko je izpolnjen določen pogoj.
Vzemimo primer, ko imate nabor številk v stolpcu A in želite označiti vsa negativna števila z rdečo pisavo. V tem primeru moramo vsako celico analizirati glede na njeno vrednost in nato ustrezno spremeniti barvo pisave.
Da pa bi bila koda učinkovitejša, lahko najprej preverimo, ali so na seznamu negativne vrednosti ali ne. Če ni negativnih vrednosti, lahko uporabimo stavek Exit For, da preprosto pridemo iz kode.
Spodaj je koda, ki to počne:
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 Potem zapustite For If Rng (i) .Vrednost <0 Potem Rng (i). Font.Color = vbRed Naprej i Končaj Sub
Ko uporabite stavek "Exit For" v ugnezdeni zanki "For Next", bo prišel iz zanke, v kateri se izvaja, in nadaljeval z izvajanjem naslednje vrstice v kodi za zanko For Next.
Na primer, v spodnji kodi vas bo stavek 'Exit For' izvlekel iz notranje zanke, vendar bo zunanja zanka še naprej delovala.
Sub SampleCode () Za i = 1 do 10 Za j = 1 do 10 Izhod Za Naprej J Naprej i Konec Sub
Naredi While Loop
Zanka "Do While" vam omogoča, da preverite stanje in zaženete zanko, medtem ko je ta pogoj izpolnjen (ali je TRUE).
V zanki Do While obstajata dve vrsti sintakse.
Naredite zanko [Med pogojem] [Kodo bloka za izvedbo]
in
Naredi [Blokiranje kode za izvedbo] Zanka [Med pogojem]
Razlika med tema dvema je v tem, da se v prvem, preden se izvede kateri koli kodni blok, najprej preveri pogoj While, v drugem primeru pa se najprej izvede kodni blok in nato še pogoj While.
To pomeni, da če je pogoj While False v obeh primerih, se bo koda v drugem primeru še vedno izvajala vsaj enkrat (saj se pogoj 'While' preveri po enkratni izvedbi kode).
Zdaj pa poglejmo nekaj primerov uporabe zank Do While v VBA.
Primer 1 - Dodajte prvih 10 pozitivnih celih števil z uporabo VBA
Recimo, da želite dodati prvih deset pozitivnih celih števil z uporabo zanke Do While v VBA.
Če želite to narediti, lahko uporabite zanko Do While, dokler naslednja številka ni manjša ali enaka 10. Takoj, ko je število večje od 1o, se bo vaša zanka ustavila.
Tukaj je koda VBA, ki bo zagnala to zanko Do While in prikaz rezultata v polju s sporočilom.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i <= 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
Zgornja zanka še naprej deluje, dokler vrednost 'i' ne postane 11. Takoj, ko postane 11, se zanka konča (ko pogoj While postane False).
Znotraj zanke smo uporabili spremenljivko Result, ki ima končno vrednost. Ko je zanka končana, se v oknu s sporočilom prikaže vrednost spremenljivke "Result".
Primer 2 - Vnesite datume za trenutni mesec
Recimo, da želite v stolpec delovnega lista vnesti vse datume tekočega meseca.
To lahko storite z naslednjo kodo zanke Do While:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Leto (Datum), Mesec (Datum), 1) Do While Mesec (CMDate) = Mesec (Datum) Območje ("A1"). Odmik (i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Zgornja koda bi vnesla vse datume v prvi stolpec delovnega lista (od A1). Zanke se nadaljujejo, dokler se vrednost meseca spremenljivke "CMDate" ne ujema z vrednostjo za trenutni mesec.
Izhod iz izjave Do
Iz stavka Exit Do lahko pridete iz zanke. Takoj, ko koda izvede vrstico »Exit Do«, pride iz zanke Do While in nadzor prenese v naslednjo vrstico takoj za zanko.
Na primer, če želite vnesti samo prvih 10 datumov, lahko zanko zapustite takoj, ko vnesete prvih 10 datumov.
Spodnja koda bo to naredila:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Leto (Datum), Mesec (Datum), 1) Do While Mesec (CMDate) = Mesec (Datum) Območje ("A1"). Odmik (i, 0) = CMDate i = i + 1 Če je i> = 10 Potem zapustite Do CMDate = CMDate + 1 Loop End Sub
V zgornji kodi se stavek IF uporablja za preverjanje, ali je vrednost i večja od 10 ali ne. Takoj, ko vrednost 'i' postane 10, se izvede stavek Exit Do in zanka se konča.
Naredi do zanke
Zank »Do Until« je zelo podoben zankam »Do While«.
V »Do While« zanka teče, dokler ni izpolnjen dani pogoj, v »Do While« pa v zanki, dokler ni izpolnjen določen pogoj.
V zanki Do do konca obstajata dve vrsti sintakse.
Naredi [Do stanja] [Kodo bloka za izvedbo] Zanka
in
Naredite [Blokiranje kode za izvajanje] Zanka [Do stanja]
Razlika med tema dvema je v tem, da se v prvem, preden se izvede kateri koli kodni blok, najprej preveri pogoj Until, v drugem primeru pa se najprej izvede kodni blok in nato še pogoj Dokler.
To pomeni, da če je pogoj Dokler je TRUE v obeh primerih, se bo koda v drugem primeru še vedno izvajala vsaj enkrat (saj je pogoj 'Do' preverjen po enkratni izvedbi kode).
Zdaj pa poglejmo nekaj primerov uporabe zank Do Do v VBA.
Opomba: Vsi primeri za Do While so enaki kot za Do While. Te so bile spremenjene, da bi vam pokazale, kako deluje zanka Do Do.
Primer 1 - Dodajte prvih 10 pozitivnih celih števil z uporabo VBA
Recimo, da želite dodati prvih deset pozitivnih celih števil z uporabo zanke Do Do v VBA.
Če želite to narediti, morate izvajati zanko, dokler naslednja številka ne bo manjša ali enaka 10. Takoj, ko je število večje od 1o, se bo vaša zanka ustavila.
Tukaj je koda VBA, ki bo zagnala to zanko in prikazala rezultat v polju za sporočila.
Sub AddFirst10PositiveIntegers () Dim i As Integer i = 1 Do While i> 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
Zgornja zanka deluje, dokler vrednost 'i' ne postane 11. Takoj, ko postane 11, se zanka konča (ko pogoj 'Dokler' ne postane True).
Primer 2 - Vnesite datume za trenutni mesec
Recimo, da želite v stolpec delovnega lista vnesti vse datume tekočega meseca.
To lahko storite z naslednjo kodo zanke Do Do:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Leto (Datum), Mesec (Datum), 1) Naredite Do meseca (CMDate) Mesec (Datum) Območje ("A1"). Odmik ( i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Zgornja koda bi vnesla vse datume v prvi stolpec delovnega lista (od A1). Zanka se nadaljuje, dokler mesec spremenljivke CMDate ni enak mesecu v tekočem mesecu.
Izhod iz izjave Do
Za izhod iz zanke lahko uporabite stavek 'Exit Do'.
Takoj, ko koda izvede vrstico "Exit Do", pride iz zanke Do Until in prenese nadzor v naslednjo vrstico takoj za zanko.
Na primer, če želite vnesti samo prvih 10 datumov, lahko zanko zapustite takoj, ko vnesete prvih 10 datumov.
Spodnja koda bo to naredila:
Sub EnterCurrentMonthDates () Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial (Leto (Datum), Mesec (Datum), 1) Naredi Do meseca (CMDate) Mesec (Datum) Območje ("A1"). Odmik ( i, 0) = CMDate i = i + 1 Če je i> = 10 Potem zapustite Do CMDate = CMDate + 1 Loop End Sub
V zgornji kodi, takoj ko vrednost 'i' postane 10, se izvede izhod Do statment in zanka se konča.
Za vsakogar
V VBA lahko krožite po nizu zbirk z zanko »Za vsakega«.
Tu je nekaj primerov zbirk v Excelu VBA:
- Zbirka vseh odprtih delovnih zvezkov.
- Zbirka vseh delovnih listov v delovnem zvezku.
- Zbirka vseh celic v nizu izbranih celic.
- Zbirka vseh grafikonov ali oblik v delovnem zvezku.
Z zanko »Za vsakega« lahko prečkate vsak predmet v zbirki in nad njim izvedete nekaj dejanj.
Na primer, lahko pregledate vse delovne liste v delovnem zvezku in jih zaščitite, ali pa poiščete vse celice v izboru in spremenite oblikovanje.
Z zanko »Za vsakega« (imenovano tudi zanka »Za vsak naslednji«) vam ni treba vedeti, koliko predmetov je v zbirki.
Zanka »Za vsak« bi samodejno prešla skozi vsak predmet in izvedla podano dejanje. Če želite na primer zaščititi vse delovne liste v delovnem zvezku, bi bila koda enaka, če imate delovni zvezek s 3 delovnimi listi ali 30 delovnimi listi.
Tukaj je sintaksa zanke Za vsak naslednji v Excelu VBA.
Za vsak element v zbirki [Blokiranje kode za izvedbo] Naprej [element]
Zdaj pa poglejmo nekaj primerov uporabe za vsako zanko v Excelu.
Primer 1 - Preglejte vse delovne liste v delovnem zvezku (in ga zaščitite)
Recimo, da imate delovni zvezek, v katerem želite zaščititi vse delovne liste.
Spodaj za zanko Every-Next lahko to preprosto storite:
Sub ProtectSheets () Zatemni ws kot delovni list za vsak ws v ActiveWorkbook.
V zgornji kodi smo spremenljivko 'ws' opredelili kot predmet delovnega lista. To pove VBA, da je treba "ws" razlagati kot predmet delovnega lista v kodi.
Zdaj uporabljamo stavek 'Za vsakega', da preidemo skozi vsak 'ws' (ki je predmet delovnega lista) v zbirki vseh delovnih listov v aktivnem delovnem zvezku (podano s strani ActiveWorkbook.Worksheets).
Upoštevajte, da nam za razliko od drugih zank, kjer smo poskušali zaščititi vse delovne liste v delovnem zvezku, ni treba skrbeti, koliko delovnih listov je v delovnem zvezku.
Za zagon zanke nam jih ni treba šteti. Za vsako zanko zagotavlja, da se vsi predmeti analizirajo eden za drugim.
Primer 2 - Preglejte vse odprte delovne zvezke (in shranite vse)
Če hkrati delate z več delovnimi zvezki, vam lahko pride prav, če lahko vse te delovne zvezke shranite hkrati.
Spodaj koda VBA lahko to naredi za nas:
Sub SaveAllWorkbooks () Dim wb kot delovni zvezek za vsako wb v delovnih zvezkih wb.Save Next wb End Sub
Upoštevajte, da v tej kodi ne dobite poziva, ki vas prosi, da delovni zvezek shranite na določeno mesto (če ga shranite prvič).
Shrani ga v privzeto mapo (v mojem primeru je bila to mapa »Dokumenti«). Ta koda najbolje deluje, če so te datoteke že shranjene in spreminjate ter želite hitro shraniti vse delovne zvezke.
Primer 3 - Pojdite skozi vse celice v izboru (označite negativne vrednosti)
Z zanko 'Za vsakega' lahko prečkate vse celice v določenem obsegu ali v izbranem obsegu.
To je lahko v pomoč, če želite analizirati vsako celico in na njej izvesti dejanje.
Spodaj je na primer koda, ki bo šla skozi vse celice pri izbiri in spremenila barvo celic celic z negativnimi vrednostmi v rdečo.
Sub HighlightNegativeCells () Dim Cll As Range Za vsako Cll v izboru Če je Cll.Value <0 Potem Cll.Interior.Color = vbRed End Če je naslednji Cll End Sub
(Upoštevajte, da sem Cll uporabil kot kratko ime spremenljivke za celico. Priporočljivo je, da kot imena spremenljivk ne uporabljate imen predmetov, kot so listi ali obseg)
V zgornji kodi zanka For Every-Next gre skozi zbirko celic v izboru. Stavek IF se uporablja za ugotavljanje, ali je vrednost celice negativna ali ne. Če je tako, celica dobi rdečo notranjo barvo, sicer gre v naslednjo celico.
Če nimate izbire in namesto tega želite, da VBA izbere vse napolnjene celice v stolpcu, začenši z določeno celico (tako kot za izbiro vseh zapolnjenih celic uporabljamo tipko Control + Shift + puščica navzdol), lahko uporabite spodnjo kodo:
Sub HighlightNegativeCells () Dim Cll As Range Dim Rng As Range Set Rng = Range ("A1", Range ("A1"). End (xlDown)) Za vsako celico v Rng Če je Cll.Value <0 Potem Cll.Interior.Color = vbRdeči konec Če je naslednji Cll End Sub
V zgornjem primeru ni pomembno, koliko napolnjenih celic je. Začel se bo iz celice A1 in analiziral vse sosednje napolnjene celice v stolpcu.
Prav tako vam ni treba izbrati celice A1. Izberete lahko poljubno oddaljeno celico in ko se koda zažene, bo še vedno upoštevala vse celice v stolpcu A (začenši z A1) in obarvala negativne celice.
Izpis "Izhod za"
Za izhod iz zanke lahko uporabite stavek 'Exit For' v zanki For Every-Next. To se običajno naredi, če je izpolnjen določen pogoj.
Na primer, v primeru 3, ko gremo skozi niz celic, je lahko učinkoviteje preveriti, ali obstajajo negativne vrednosti ali ne. V primeru, da ni negativnih vrednosti, lahko preprosto zapustimo zanko in prihranimo nekaj časa obdelave VBA.
Spodaj je koda VBA, ki bo to naredila:
Sub HighlightNegativeCells () Dim Cll As Range Za vsako celico v izboru Če je WorksheetFunction.Min (Selection)> = 0 Potem zapustite For If Cll.Value <0 Potem Cll.Interior.Color = vbRed Konec Naslednji Cll End Sub
Kam vstaviti kodo VBA
Se sprašujete, kje je koda VBA v vašem Excelovem delovnem zvezku?
Excel ima zaledje VBA, imenovano urejevalnik VBA. Kodo morate kopirati in prilepiti v okno kode modula VB Editor.
Tu so naslednji koraki:
- Pojdite na zavihek Razvijalec.
- Kliknite možnost Visual Basic. To bo odprlo urejevalnik VB v zaledju.
- V podoknu Raziskovalec projektov v urejevalniku VB z desno tipko miške kliknite kateri koli predmet delovnega zvezka, v katerega želite vstaviti kodo. Če ne vidite Raziskovalca, pojdite na zavihek Pogled in kliknite Raziskovalec projektov.
- Pojdite na Vstavi in kliknite Modul. S tem boste v svoj delovni zvezek vstavili objekt modula.
- Kodo kopirajte in prilepite v okno modula.
