Per lavorare in modo efficace in VBA, devi comprendere i loop.
I cicli consentono di ripetere un blocco di codice un determinato numero di volte o di ripetere un blocco di codice su ciascun oggetto in un insieme di oggetti.
Per prima cosa ti mostreremo alcuni esempi per mostrarti di cosa sono capaci i loop. Quindi ti insegneremo tutto sui loop.
Esempi rapidi di loop VBA
Per ogni loop
For Each Loops scorre ogni oggetto in una raccolta, ad esempio ogni foglio di lavoro nella cartella di lavoro o ogni cella in un intervallo.
Passa attraverso tutti i fogli di lavoro nella cartella di lavoro
Questo codice scorrerà tutti i fogli di lavoro nella cartella di lavoro, scoprendo ogni foglio:
| 12345678 | Sub LoopThroughsheets()Dim ws come foglio di lavoroPer ogni ws nei fogli di lavorows.Visible = TrueProssimoFine sottotitolo |
Passa attraverso tutte le celle nell'intervallo
Questo codice scorrerà un intervallo di celle, verificando se il valore della cella è negativo, positivo o zero:
| 1234567891011121314 | Sub If_Loop()Dim Cell come intervalloPer ogni cella nell'intervallo ("A2: A6")Se Cell.Value > 0 AlloraCell.Offset(0, 1).Value = "Positivo"ElseIf Cell.Value < 0 AlloraCell.Offset(0, 1).Value = "Negativo"AltroCell.Offset(0, 1).Value = "Zero"Finisci seCella successivaFine sottotitolo |
Per i prossimi loop
Un altro tipo di ciclo "For" è il ciclo For Next. Il For Next Loop ti permette di scorrere i numeri interi.
Questo codice scorrerà i numeri interi da 1 a 10, visualizzando ciascuno con una finestra di messaggio:
| 123456 | Sub ForLoop()Dim i As IntegerPer i = da 1 a 10MsgBox ioAvanti ioFine sottotitolo |
Fai mentre loop
Do While Loops andrà in loop quando una condizione è soddisfatta. Questo codice eseguirà anche un ciclo tra i numeri interi da 1 a 10, visualizzandoli ciascuno con una finestra di messaggio.
| 12345678 | Sub DoWhileLoop()Dim n As Integern = 1Fai mentre n < 11MsgBox nn = n + 1Ciclo continuoFine sottotitolo |
Fai fino ai loop
Al contrario, Do Until Loops eseguirà un ciclo fino a quando non viene soddisfatta una condizione. Questo codice fa la stessa cosa dei due esempi precedenti.
| 12345678 | Sub DoUntilLoop()Dim n As Integern = 1Fai fino a n >= 10MsgBox nn = n + 1Ciclo continuoFine sottotitolo |
Ne discuteremo di seguito, ma devi essere estremamente attento quando crei i cicli Do While o Do Until in modo da non creare un ciclo infinito.
Generatore di loop VBA
Questo è uno screenshot del "Loop Builder" dal nostro componente aggiuntivo VBA Premium: AutoMacro. Il Loop Builder ti consente di creare rapidamente e facilmente loop per scorrere diversi oggetti o numeri. È possibile eseguire azioni su ciascun oggetto e/o selezionare solo oggetti che soddisfano determinati criteri.
Il componente aggiuntivo contiene anche molti altri costruttori di codice, un'ampia libreria di codici VBA e un assortimento di strumenti di codifica. È un must per qualsiasi sviluppatore VBA.
Ora tratteremo in modo approfondito i diversi tipi di loop.
VBA per il prossimo ciclo
Per la sintassi del ciclo
Il ciclo For Next consente di ripetere un blocco di codice un numero specificato di volte. La sintassi è:
| 12345 | [Contatore attenuato come numero intero]Per contatore = dall'inizio alla fine [Valore passo][Fare qualcosa]Avanti [Contatore] |
Dove le voci tra parentesi sono facoltative.
- [Contatore attenuato finché lungo] - Dichiara la variabile contatore. Richiesto se Option Explicit è dichiarato nella parte superiore del modulo.
- Contatore - Una variabile intera usata per contare
- Cominciare - Il valore iniziale (Es. 1)
- Fine - Il valore finale (Es. 10)
- [Valore passo] - Ti permette di contare ogni n interi invece di ogni 1 intero. Puoi anche andare al contrario con un valore negativo (es. Step -1)
- [Fare qualcosa] - Il codice che si ripeterà
- Avanti [Contatore] - Chiusura dell'istruzione per il ciclo For Next. Puoi includere o meno il contatore. Tuttavia, consiglio vivamente di includere il contatore in quanto rende il codice più facile da leggere.
Se questo è fonte di confusione, non preoccuparti. Esamineremo alcuni esempi:
Conta fino a 10
Questo codice conterà fino a 10 utilizzando un ciclo For-Next:
| 12345678 | Sub ForEach_CountTo10()Dim n As IntegerPer n = da 1 a 10MsgBox nSuccessivo nFine sottotitolo |
Per il passaggio del ciclo
Conta fino a 10 - Solo numeri pari
Questo codice conterà fino a 10 contando solo i numeri pari:
| 12345678 | Sub ForEach_CountTo10_Even()Dim n As IntegerPer n = da 2 a 10 Passaggio 2MsgBox nSuccessivo nFine sottotitolo |
Notare che abbiamo aggiunto "Passaggio 2". Questo dice al ciclo For di "passare" attraverso il contatore di 2. Possiamo anche usare un valore di passo negativo per fare un passo indietro:
Per passo loop - Inverso
Conto alla rovescia da 10
Questo codice farà il conto alla rovescia da 10:
| 123456789 | Sub ForEach_Countdown_Inverse()Dim n As IntegerPer n = 10 a 1 passo -1MsgBox nSuccessivo nMsgBox "Sollevamento"Fine sottotitolo |
Elimina righe se la cella è vuota
Ho usato più frequentemente un passaggio For-Loop negativo per scorrere gli intervalli di celle, eliminando le righe che soddisfano determinati criteri. Se esegui un ciclo dalle righe superiori a quelle inferiori, mentre elimini le righe rovinerai il contatore.
Questo esempio eliminerà le righe con celle vuote (a partire dalla riga in basso):
| 12345678910 | Sub ForEach_DeleteRows_BlankCells()Dim n As IntegerPer n = 10 a 1 passo -1If Range("a" & n).Value = "" ThenIntervallo("a" & n).InteraRiga.EliminaFinisci seSuccessivo nFine sottotitolo |
Annidato per loop
È possibile "annidare" un For Loop all'interno di un altro For Loop. Useremo Nested For Loops per creare una tabella di moltiplicazione:
| 1234567891011 | Sub Nested_ForEach_MultiplicationTable()Dim row As Integer, col As IntegerPer la riga = da 1 a 9Per col = da 1 a 9Celle (riga + 1, col + 1). Valore = riga * colColpo successivoRiga successivaFine sottotitolo |
Esci per
L'istruzione Exit For consente di uscire immediatamente da un ciclo For Next.
Di solito si usa Exit For insieme a un'istruzione If, uscendo dal ciclo For Next se viene soddisfatta una determinata condizione.
Ad esempio, potresti usare un ciclo For per trovare una cella. Una volta trovata quella cella, puoi uscire dal ciclo per velocizzare il tuo codice.
Questo codice scorrerà le righe da 1 a 1000, cercando "errore" nella colonna A. Se viene trovato, il codice selezionerà la cella, ti avviserà dell'errore trovato e uscirà dal ciclo:
| 12345678910111213 | Sub ExitFor_Loop()Dim i As IntegerPer i = 1 a 1000If Range("A" & i).Value = "error" ThenIntervallo("A" & i).SelezionaMsgBox "Errore trovato"Esci perFinisci seAvanti ioFine sottotitolo |
Importante: nel caso di Nested For Loop, Exit For esce solo dal For Loop corrente, non da tutti i Loop attivi.
Continua per
VBA non ha il comando "Continua" che si trova in Visual Basic. Invece, dovrai usare "Esci".
VBA per ogni ciclo
Il VBA For Each Loop scorrerà tutti gli oggetti in una raccolta:
- Tutte le celle in un intervallo
- Tutti i fogli di lavoro in una cartella di lavoro
- Tutte le forme in un foglio di lavoro
- Tutte le cartelle di lavoro aperte
Puoi anche utilizzare Nested For Each Loop per:
- Tutte le celle in un intervallo su tutti i fogli di lavoro
- Tutte le forme su tutti i fogli di lavoro
- Tutti i fogli in tutte le cartelle di lavoro aperte
- e così via…
La sintassi è:
| 123 | Per ogni oggetto della collezione[Fare qualcosa]Avanti [Oggetto] |
In cui si:
- Oggetto - Variabile che rappresenta un intervallo, un foglio di lavoro, una cartella di lavoro, una forma, ecc. (es. rng)
- Collezione - Raccolta di oggetti (es. Range(“a1:a10”)
- [Fare qualcosa] - Blocco di codice da eseguire su ogni oggetto
- Avanti [Oggetto] - Dichiarazione di chiusura. [Oggetto] è facoltativo, tuttavia fortemente consigliato.
Per ogni cella nell'intervallo
Questo codice scorrerà ogni cella in un intervallo:
| 123456789 | Sub ForEachCell_inRange()Cella debole come intervalloPer ogni cella nell'intervallo ("a1: a10")cella.Valore = cella.Offset(0,1).ValoreCella successivaFine sottotitolo |
Per ogni foglio di lavoro nella cartella di lavoro
Questo codice scorrerà tutti i fogli di lavoro in una cartella di lavoro, rimuovendo la protezione di ogni foglio:
| 123456789 | Sub ForEachSheet_inWorkbook()Dim ws come foglio di lavoroPer ogni ws nei fogli di lavorows.Unprotect "password"Avanti wFine sottotitolo |
Per ogni cartella di lavoro aperta
Questo codice salverà e chiuderà tutte le cartelle di lavoro aperte:
| 123456789 | Sub ForEachWB_inWorkbooks()Dim wb come cartella di lavoroPer ogni wb nelle cartelle di lavorowb.Chiudi SaveChanges:=Truewb successivoFine sottotitolo |
Per ogni forma nel foglio di lavoro
Questo codice eliminerà tutte le forme nel foglio attivo.
| 123456789 | Sub ForEachShape()Dim shp come formaPer ogni shp in ActiveSheet.Shapesshp.Eliminaprossimo shpFine sottotitolo |
Per ogni forma in ogni foglio di lavoro nella cartella di lavoro
Puoi anche nidificare For Each Loop. Qui esamineremo tutte le forme in tutti i fogli di lavoro nella cartella di lavoro attiva:
| 1234567891011 | Sub ForEachShape_inAllWorksheets()Dim shp As Shape, ws As WorksheetPer ogni ws nei fogli di lavoroPer ogni shp In ws.Shapesshp.Eliminaprossimo shpAvanti wFine sottotitolo |
Per Ciascuno - IF Loop
Come accennato in precedenza, è possibile utilizzare un'istruzione If all'interno di un ciclo, eseguendo azioni solo se vengono soddisfatti determinati criteri.
Questo codice nasconderà tutte le righe vuote in un intervallo:
| 12345678910 | Sub ForEachCell_inRange()Cella debole come intervalloPer ogni cella nell'intervallo ("a1: a10")Se cell.Value = "" Then _cell.EntireRow.Hidden = TrueCella successivaFine sottotitolo |
VBA Do While Loop
Il VBA Do While e Do Until (vedere la sezione successiva) sono molto simili. Ripeteranno un ciclo mentre (o finché) una condizione non viene soddisfatta.
Il ciclo Do While ripeterà un ciclo finché una condizione è soddisfatta.
Ecco la sintassi Do While:
| 123 | Fai mentre condizione[Fare qualcosa]Ciclo continuo |
In cui si:
- Condizione - La condizione da testare
- [Fare qualcosa] - Il blocco di codice da ripetere
Puoi anche impostare un ciclo Do While con la Condizione alla fine del ciclo:
| 123 | Fare[Fare qualcosa]Ciclo mentre condizione |
Dimostreremo ciascuno di essi e mostreremo in che modo differiscono:
Fare mentre
Ecco l'esempio del ciclo Do While che abbiamo dimostrato in precedenza:
| 12345678 | Sub DoWhileLoop()Dim n As Integern = 1Fai mentre n < 11MsgBox nn = n + 1Ciclo continuoFine sottotitolo |
Ciclo mentre
Ora eseguiamo la stessa procedura, tranne per il fatto che sposteremo la condizione alla fine del ciclo:
| 12345678 | Sub DoLoopWhile()Dim n As Integern = 1FareMsgBox nn = n + 1Ciclo mentre n < 11Fine sottotitolo |
VBA Esegui fino al ciclo
Do Until Loops ripeterà un ciclo finché non viene soddisfatta una determinata condizione. La sintassi è essenzialmente la stessa dei cicli Do While:
| 123 | Fai fino alla condizione[Fare qualcosa]Ciclo continuo |
e allo stesso modo la condizione può andare all'inizio o alla fine del ciclo:
| 123 | Fare[Fare qualcosa]Ciclo fino alla condizione |
Fai fino a
Questo ciclo do Until conterà fino a 10, come i nostri esempi precedenti
| 12345678 | Sub DoUntilLoop()Dim n As Integern = 1Fai fino a n > 10MsgBox nn = n + 1Ciclo continuoFine sottotitolo |
Ciclo fino a
Questo ciclo Loop Until conterà fino a 10:
| 12345678 | Sub DoLoopUntil()Dim n As Integern = 1FareMsgBox nn = n + 1Ciclo fino a n > 10Fine sottotitolo |
Esci dal ciclo
Simile all'utilizzo di Exit For per uscire da un ciclo For, si utilizza il comando Exit Do per uscire immediatamente da un ciclo Do
| 1 | Esci Fai |
Ecco un esempio di Exit Do:
| 123456789101112131415 | Sub ExitDo_Loop()Dim i As Integerio = 1Fai fino a quando i > 1000If Range("A" & i).Value = "error" ThenIntervallo("A" & i).SelezionaMsgBox "Errore trovato"Esci FaiFinisci seio = io + 1Ciclo continuoFine sottotitolo |
Termina o interrompi il ciclo
Come accennato in precedenza, puoi utilizzare Exit For o Exit Do per uscire dai loop:
| 1 | Esci per |
| 1 | Esci Fai |
Tuttavia, questi comandi devono essere aggiunti al codice prima di eseguire il ciclo.
Se stai cercando di "interrompere" un ciclo attualmente in esecuzione, puoi provare a premere ESC o CTRL + Pausa Pausa sulla tastiera. Tuttavia, questo potrebbe non funzionare. Se non funziona, dovrai aspettare che il tuo ciclo finisca o, nel caso di un ciclo infinito, usare CTRL + ALT + Elimina per forzare la chiusura di Excel.
Questo è il motivo per cui cerco di evitare i loop Do, è più facile creare accidentalmente un loop infinito costringendoti a riavviare Excel, potenzialmente perdendo il tuo lavoro.
Altri esempi di loop
Passa attraverso le righe
Questo eseguirà il ciclo di tutte le righe di una colonna:
| 123456789 | Pubblico Sub LoopThroughRows()Cella debole come intervalloPer ogni cella nell'intervallo ("A: A")Ff cell.value "" poi MsgBox cell.address &": " & cell.valueCella successivaFine sottotitolo |
Passa attraverso le colonne
Questo scorrerà tutte le colonne di fila:
| 123456789 | Public Sub LoopThroughColumns()Cella debole come intervalloPer ogni cella nell'intervallo ("1:1")If cell.Value "" Then MsgBox cell.Address & ": " & cell.ValueCella successivaFine sottotitolo |
Loop attraverso i file in una cartella
Questo codice scorrerà tutti i file in una cartella, creando un elenco:
| 12345678910111213141516171819 | Sub LoopThroughFiles ()Dim oFSO come oggettoDim oFolder As ObjectDim oFile As ObjectDim i As IntegerImposta oFSO = CreateObject("Scripting.FileSystemObject")Imposta oFolder = oFSO.GetFolder("C:\Demo)io = 2Per ogni oFile in oFolder.FilesRange("A" & i).value = oFile.Nameio = io + 1File successivoFine sottotitolo |
Loop Through Array
Questo codice scorrerà l'array 'arrList':
| 123 | Per i = LBound(arrList) To UBound(arrList)MsgBox arrList(i)Avanti io |
La funzione LBound ottiene il "limite inferiore" dell'array e UBound ottiene il "limite superiore".
Cicli in Access VBA
La maggior parte degli esempi sopra funzionerà anche in Access VBA. Tuttavia, in Access, eseguiamo un ciclo attraverso l'oggetto Recordset anziché l'oggetto Range.
| 123456789101112131415161718 | Sub LoopThroughRecords()In caso di errore Riprendi AvantiDim dbs come databaseDim prima come RecordsetImposta dbs = CurrentDbSet rst = dbs.OpenRecordset("tblClients", dbOpenDynaset)con prima.SpostaUltimo.Sposta primaEsegui fino a .EOF = TrueMsgBox (rst.Fields("ClientName")).Sposta AvantiCiclo continuoTermina conprimo.ChiudiImposta primo = NienteImposta dbs = NienteFine sottotitolo |
