Excel VBA Loops – Per ciascuno, per il prossimo, Fai While, Annidato e altro
Per lavorare efficacemente in VBA, devi capire i Loop.
Loop consentono di ripetere un blocco di codice un determinato numero di volte o ripetere un blocco di codice su un ogni 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.
VBA Loop Esempi rapidi
Per ogni Loop
Per ogni Loop loop attraverso ogni oggetto in una raccolta, come ad esempio ogni foglio di lavoro in cartella di lavoro o ogni cella in un intervallo.
Ciclo Attraverso tutti i Fogli di lavoro nella Cartella di lavoro
Questo codice ciclo attraverso tutti i fogli di lavoro nella cartella di lavoro, il recupero di ciascun foglio:
|
1
2
3
4
5
6
7
8
|
Sub LoopThroughSheets()
Dim ws Come Foglio di lavoro
Per Ogni ws In Fogli di lavoro
ws.Visible = True
Avanti
End Sub
|
Loop Attraverso Tutte le Celle nell’Intervallo
Questo codice loop attraverso un intervallo di celle, verifica se il valore è negativo, positivo, o zero:
Per il Prossimo Loop
un Altro tipo di Ciclo “For” è il Ciclo For Next. Il ciclo For Next ti consente di scorrere interi.
Questo codice eseguirà un ciclo di interi da 1 a 10, visualizzandoli ciascuno con una finestra di messaggio:
|
1
2
3
4
5
6
|
Sub ForLoop()
Dim i As Integer
For i = 1 To 10
MsgBox i
voglio
End Sub
|
Do While Loop
Do While Loop in loop, mentre la condizione è soddisfatta. Questo codice eseguirà anche il ciclo degli interi da 1 a 10, visualizzandoli ciascuno con una finestra di messaggio.
|
1
2
3
4
5
6
7
8
|
Sub DoWhileLoop()
Dim n As Integer
n = 1
Do While n < 11
MsgBox n
n = n + 1
Anello
End Sub
|
Fino a quando i Passanti
al contrario, Fai fino a quando i loop non si interromperanno fino a quando non viene soddisfatta una condizione. Questo codice fa la stessa cosa dei due esempi precedenti.
|
1
2
3
4
5
6
7
8
|
Sub DoUntilLoop()
Dim n As Integer
n = 1
Indietro Fino a n >= 10
MsgBox n
n = n + 1
Anello
End Sub
|
di questo parleremo in seguito, ma si devi essere estremamente attento quando crei Do While o Do Until loop in modo da non creare un ciclo senza fine.
VBA Loop Builder
Questo è uno screenshot del “Loop Builder” dal nostro componente aggiuntivo VBA Premium: AutoMacro. Il Generatore di loop consente di creare rapidamente e facilmente loop per scorrere diversi oggetti o numeri. È possibile eseguire azioni su ciascun oggetto e / o selezionare solo gli oggetti che soddisfano determinati criteri.
Il componente aggiuntivo contiene anche molti altri costruttori di codice, una vasta libreria di codici VBA, e un assortimento di strumenti di codifica. È un must per qualsiasi sviluppatore VBA.
Ora copriremo i diversi tipi di loop in profondità.
VBA per il ciclo successivo
Per la sintassi del ciclo
Il ciclo successivo consente di ripetere un blocco di codice un numero specificato di volte. La sintassi è:
|
1
2
3
4
5
|
For Counter = Start to End
Next
|
Where the items in brackets are optional.
- – Declares the counter variable. Required if Option Explicit is declared at the top of your module.
- Counter-Una variabile intera utilizzata per contare
- Start-Il valore iniziale (Es. 1)
- End-Il valore finale (Es. 10)
- – Consente di contare ogni n interi invece di ogni 1 intero. Puoi anche andare al contrario con un valore negativo (es. Step -1)
- – Il codice che si ripeterà
- Next – Chiusura istruzione per il ciclo successivo. È possibile includere il Contatore o meno. Tuttavia, consiglio vivamente di includere il contatore in quanto rende il codice più facile da leggere.
Se questo è confuso, non preoccuparti. Esamineremo alcuni esempi:
Conta fino a 10
Questo codice conterà fino a 10 usando un ciclo For-Next:
|
1
2
3
4
5
6
7
8
|
Sub ForEach_CountTo10()
Dim n As Integer
Per n = 1 To 10
MsgBox n
il Prossimo n
End Sub
|
Per il Ciclo di Passaggio
Conteggio di 10 Numeri pari
Questo il codice conterà fino a 10 contando solo i numeri pari:
|
1
2
3
4
5
6
7
8
|
Sub ForEach_CountTo10_Even()
Dim n As Integer
Per n = 2 A 10 Passo 2
MsgBox n
il Prossimo n
End Sub
|
da Notare, abbiamo aggiunto la “Fase 2”. Questo dice al ciclo For di “passare” attraverso il contatore di 2. Possiamo anche usare un valore passo negativo per fare un passo in senso inverso:
Per Loop Step – Inverse
Conto alla rovescia da 10
Questo codice sarà conto alla rovescia da 10:
|
1
2
3
4
5
6
7
8
9
|
Sub ForEach_Countdown_Inverse()
Dim n As Integer
Per n = 10 To 1 Step -1
MsgBox n
il Prossimo n
MsgBox “Lift Off”
End Sub
|
Elimina Righe se la cella è vuota
Ho usato più frequentemente un passaggio negativo Per-Loop per scorrere intervalli di celle, eliminando righe che soddisfano determinati criteri. Se si esegue il loop dalle righe superiori alle righe inferiori, mentre si eliminano le righe si rovinerà il contatore.
Questo esempio eliminerà righe con celle vuote (a partire dalla riga inferiore):
Annidato per Loop
Puoi “annidare” uno per Loop all’interno di un altro Per Loop. Useremo i loop nidificati per creare una tabella di moltiplicazione:
|
1
2
3
4
5
6
7
8
9
10
11
|
Sub Nested_ForEach_MultiplicationTable()
Dim riga As Integer, col As Integer
Per riga = 1 To 9
Per il col = 1 A 9
Cells(riga + 1, col + 1).Valore = riga * colonna
Avanti col
riga Successiva
End Sub
|
Uscita
L’istruzione Exit For permette di uscire da un ciclo For Next immediatamente.
Di solito si usa Exit For insieme a un’istruzione If, uscendo dal ciclo For Next se viene soddisfatta una determinata condizione.
Ad esempio, è possibile utilizzare un ciclo For per trovare una cella. Una volta trovata quella cella, puoi uscire dal ciclo per accelerare il tuo codice.
Questo codice scorrerà tra le righe da 1 a 1000, cercando “errore” nella colonna A. Se viene trovato, il codice selezionerà la cella, avviserà l’errore trovato e uscirà dal ciclo:
Importante: nel caso di cicli For annidati, Exit For esce solo dal ciclo For corrente, non da tutti i cicli attivi.
Continua Per
VBA non ha il comando “Continua” che si trova in Visual Basic. Invece, dovrai usare “Exit”.
VBA Per ogni ciclo
Il VBA Per ogni ciclo scorrerà attraverso tutti gli oggetti in una raccolta:
- Tutte le celle in un intervallo
- Tutti i fogli di una cartella di lavoro
- Tutte le forme in un foglio di lavoro
- Tutte le cartelle di lavoro aperte
È anche possibile utilizzare Nidificati Per Ogni Loop:
- Tutte le celle in un intervallo su tutti i fogli di lavoro
- Tutte le forme in tutti i fogli di lavoro
- Tutti i fogli in tutte le cartelle di lavoro aperte
- e così via…
La sintassi è:
|
1
2
3
|
For Each Object in Collection
Next
|
Where:
For Each Cell in Range
This code will loop through each cell in a range:
For Each Worksheet in Workbook
This code will loop through all worksheets in a workbook, unprotecting each sheet:
|
1
2
3
4
5
6
7
8
9
|
Sub ForEachSheet_inWorkbook()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect ” password “
Next ws
End Sub
|
Per ogni cartella di lavoro aperta
Questo codice salverà e chiuderà tutte le cartelle di lavoro aperte:
|
1
2
3
4
5
6
7
8
9
|
Sub ForEachWB_inWorkbooks()
Dim wb Come Cartella di lavoro
Per Ogni wb In Cartelle di lavoro
wb.Chiudi SaveChanges:= True
Next wb
End Sub
|
Per ogni Forma nel foglio di lavoro
Questo codice cancellerà tutte le forme nel foglio attivo.
|
1
2
3
4
5
6
7
8
9
|
Sub ForEachShape()
Dim shp Come Forma
Per Ogni shp In ActiveSheet.Forme
shp.Elimina
Avanti shp
End Sub
|
Per Ogni Forma, in Ogni Foglio di lavoro nella Cartella di lavoro
È possibile anche il nido Per Ogni Loop. Qui analizzeremo tutte le forme in tutti i fogli di lavoro nella cartella di lavoro attiva:
|
1
2
3
4
5
6
7
8
9
10
11
|
Sub ForEachShape_inAllWorksheets()
Dim shp Come Forma, ws Come Foglio di lavoro
Per Ogni ws In Fogli di lavoro
Per Ogni shp In ws.Forme
shp.Delete
Next shp
Next ws
End Sub
|
Per ogni ciclo – IF
Come abbiamo accennato in precedenza, è possibile utilizzare un’istruzione If all’interno di loop, eseguendo azioni solo se determinati criteri sono soddisfatti.
Questo codice nasconderà tutte le righe vuote in un intervallo:
VBA Do While Loop
I VBA Do While e Do Until (vedi sezione successiva) sono molto simili. Ripeteranno un ciclo mentre (o fino a quando) una condizione è soddisfatta.
Il ciclo Do While ripeterà un ciclo mentre viene soddisfatta una condizione.
Ecco il Do While Sintassi:
|
1
2
3
|
Do While Condizione
Anello
|
in Cui:
- la Condizione La condizione di test
- Il codice di blocco a ripetere
È anche possibile impostare un ciclo Do While con la Condizione alla fine del ciclo:
|
1
2
3
|
Do
Loop While Condition
|
We will demo each one and show how they differ:
Do While
Here is the Do While loop example we demonstrated previously:
|
1
2
3
4
5
6
7
8
|
Sub DoWhileLoop()
Dim n As Integer
n = 1
Do While n < 11
MsgBox n
n = n + 1
Anello
End Sub
|
Loop Mentre
Ora eseguiamo la stessa procedura, tranne che sposteremo la condizione alla fine del ciclo:
|
1
2
3
4
5
6
7
8
|
Sub DoLoopWhile()
Dim n As Integer
n = 1
Indietro
MsgBox n
n = n + 1
Loop While n < 11
End Sub
|
VBA Ciclo Do Until
all’Indietro Fino a I loop ripeteranno un ciclo fino a quando non viene soddisfatta una determinata condizione. La sintassi è essenzialmente lo stesso come il Do While:
|
1
2
3
|
Non Fino a Condizione
Anello
|
e, analogamente, la condizione può andare all’inizio o alla fine del ciclo:
|
1
2
3
|
Do
Loop Until Condition
|
Do Until
This do Until loop will count to 10, like our previous examples
|
1
2
3
4
5
6
7
8
|
Sub DoUntilLoop()
Dim n As Integer
n = 1
Indietro Fino a n > 10
MsgBox n
n = n + 1
Anello
End Sub
|
Loop Until
il Ciclo Fino a quando loop, contare fino a 10:
|
1
2
3
4
5
6
7
8
|
Sub DoLoopUntil()
Dim n As Integer
n = 1
Indietro
MsgBox n
n = n + 1
Loop Fino a quando n > 10
End Sub
|
Exit Do Loop
Simile all’uso di Uscita per uscire da un Ciclo For, si utilizza l’Uscita di comando per uscire da un Ciclo Do subito
|
1
|
Exit Do
|
Ecco un esempio di Uscita:
End o Rompere il Loop
Come abbiamo accennato in precedenza, è possibile utilizzare l’Uscita o Uscita Fare per uscire dal loop:
|
1
|
Exit For
|
|
1
|
Exit Do
|
However, these commands must be added to your code before you run il tuo 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, è necessario attendere la fine del ciclo o, nel caso di un ciclo infinito, utilizzare CTRL + ALT + Canc per forzare la chiusura di Excel.
Questo è il motivo per cui cerco di evitare i loop, è più facile creare accidentalmente un ciclo infinito che ti costringe a riavviare Excel, potenzialmente perdendo il tuo lavoro.
Altri esempi di loop
Loop Through Rows
Questo eseguirà il loop di tutte le righe di una colonna:
Loop Attraverso le Colonne
Questo ciclo attraverso tutte le colonne di una riga:
Loop Attraverso i File in una Cartella
Questo codice ciclo attraverso tutti i file in una cartella, la creazione di un elenco:
Loop Attraverso Array
Questo codice scorrere la matrice ‘arrList’:
|
1
2
3
|
For i = LBound(arrList) To UBound(arrList)
MsgBox arrList(mi)
voglio
|
La funzione LBound ottiene il “limite inferiore” dell’array e UBound ottiene il “limite superiore”.
Loop in Access VBA
La maggior parte degli esempi precedenti funzionerà anche in Access VBA. Tuttavia, in Access, eseguiamo il loop attraverso l’oggetto Recordset piuttosto che l’oggetto Range.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
Sub LoopThroughRecords()
On Error Resume Next
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = dbs.OpenRecordset(“tblClients”, dbOpenDynaset)
With rst
.MoveLast
.MoveFirst
Do Until .EOF = True
MsgBox (rst.Fields(“ClientName”))
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
|