Per ottenere il massimo da Excel e VBA, devi sapere come utilizzare i loop in modo efficiente.
In VBA, i loop ti consentono di esaminare un insieme di oggetti/valori e analizzarli uno per uno. Puoi anche eseguire attività specifiche per ogni ciclo.
Ecco un semplice esempio di utilizzo dei loop VBA in Excel.
Supponiamo di avere un set di dati e di voler evidenziare tutte le celle in righe pari. È possibile utilizzare un ciclo VBA per passare attraverso l'intervallo e analizzare il numero di ogni riga di cella. Se risulta essere pari, gli dai un colore, altrimenti lo lasci così com'è.
Ora questo, ovviamente, è molto semplice da eseguire in loop in Excel VBA (e puoi anche farlo usando la formattazione condizionale).
Nella vita reale, puoi fare molto di più con i loop VBA in Excel che possono aiutarti ad automatizzare le attività.
Ecco alcuni esempi più pratici in cui i loop VBA possono essere utili:
- Passare attraverso un intervallo di celle e analizzare ogni cella (evidenziare le celle con un testo specifico).
- Passare attraverso tutti i fogli di lavoro e fare qualcosa con ciascuno (come proteggerlo/non proteggerlo).
- Scorri tutte le cartelle di lavoro aperte (e salva ogni cartella di lavoro o chiudi tutte tranne la cartella di lavoro attiva).
- Passa attraverso tutti i caratteri in una cella (ed estrai la parte numerica da una stringa).
- Passa attraverso tutti i valori di un array.
- Passa attraverso tutti i grafici/oggetti (e dai un bordo o cambia il colore di sfondo).
Ora per utilizzare al meglio i loop in Excel VBA, è necessario conoscere i diversi tipi esistenti e la sintassi corretta di ciascuno.
In questo tutorial, mostrerò diversi tipi di loop VBA di Excel e coprirò alcuni esempi per ogni loop
Nota: questo sarà un enorme tutorial, in cui proverò a coprire ogni ciclo VBA in dettaglio. Ti consiglio di aggiungere questa pagina ai segnalibri per riferimento futuro.Se sei interessato a imparare VBA nel modo più semplice, dai un'occhiata a my Formazione online su VBA di Excel.
Per il prossimo ciclo
Il ciclo "For Next" ti consente di passare attraverso un blocco di codice per il numero di volte specificato.
Ad esempio, se ti chiedessi di aggiungere manualmente i numeri interi da 1 a 10, aggiungeresti i primi due numeri, quindi aggiungeresti il terzo numero al risultato, quindi aggiungeresti il quarto numero al risultato, e così via…
Non è vero?
La stessa logica viene utilizzata nel ciclo For Next in VBA.
Specifica quante volte vuoi che il ciclo venga eseguito e specifichi anche cosa vuoi che il codice esegua ogni volta che viene eseguito il ciclo.
Di seguito è riportata la sintassi del ciclo For Next:
Per contatore = dall'inizio alla fine [Valore passo] [Blocco codice da eseguire] Avanti [contatore]
Nel ciclo For Next, puoi utilizzare un contatore (o qualsiasi variabile) che verrà utilizzato per eseguire il ciclo. Questo contatore consente di eseguire questo ciclo per un numero richiesto di volte.
Ad esempio, se voglio aggiungere i primi 10 numeri interi positivi, il valore del mio contatore sarà compreso tra 1 e 10.
Diamo un'occhiata ad alcuni esempi per capire meglio come funziona il ciclo For Next.
Esempio 1 - Somma dei primi 10 numeri interi positivi
Di seguito è riportato il codice che aggiungerà i primi 10 numeri interi positivi utilizzando un ciclo For Next.
Verrà quindi visualizzata una finestra di messaggio che mostra la somma di questi numeri.
Sub AddNumbers() Dim Total As Integer Dim Count As Integer Total = 0 For Count = da 1 a 10 Total = Total + Count Next Count MsgBox Total End Sub
In questo codice, il valore di Total è impostato su 0 prima di entrare nel ciclo For Next.
Una volta entrato nel ciclo, mantiene il valore totale dopo ogni ciclo. Quindi, dopo il primo ciclo, quando Contatore è 1, il valore "Totale" diventa 1 e dopo il secondo ciclo diventa 3 (1+2), e così via.
E infine, quando il ciclo termina, la variabile "Totale" ha la somma dei primi 10 numeri interi positivi.
Un MsgBox quindi visualizza semplicemente il risultato in una finestra di messaggio.
Esempio 2 - Somma dei primi 5 numeri interi pari positivi
Per sommare i primi cinque numeri interi positivi pari (cioè 2,4,6,8 e 10), è necessario un codice simile con una condizione per considerare solo i numeri pari e ignorare i numeri dispari.
Ecco un codice che lo farà:
Sub AddEvenNumbers() Dim Total As Integer Dim Count As Integer Total = 0 For Count = da 2 a 10 Step 2 Total = Total + Count Next Count MsgBox Total End Sub
Nota che abbiamo iniziato il valore Count da 2 e abbiamo anche usato 'Passo 2‘.
Quando usi 'Passo 2', dice al codice di incrementare il valore 'Count' di 2 ogni volta che viene eseguito il ciclo.
Quindi il valore Count inizia da 2 e poi diventa 4, 6, 8 e 10 quando si verifica il loop.
NOTA: un altro modo per farlo potrebbe essere eseguire il ciclo da 1 a 10 e all'interno del ciclo verificare se il numero è pari o dispari. Tuttavia, l'uso di Step, in questo caso, è un modo più efficiente in quanto non richiede che il ciclo venga eseguito 10 volte, ma solo 5 volte.
Il valore Step può anche essere negativo. In tal caso, il contatore inizia con un valore più alto e continua a essere decrementato del valore di Step specificato.
Esempio 3 - Inserimento del numero di serie nelle celle selezionate
Puoi anche usare il ciclo For Next per passare attraverso una raccolta di oggetti (come celle o fogli di lavoro o cartelle di lavoro),
Ecco un esempio che inserisce rapidamente i numeri di serie in tutte le celle selezionate.
Sub EnterSerialNumber() Dim Rng As Range Dim Counter As Integer Dim RowCount As Integer Imposta Rng = Selezione RowCount = Rng.Rows.Count For Counter = 1 To RowCount ActiveCell.Offset(Contatore - 1, 0).Valore = Contatore Fine contatore successivo Sub
Il codice precedente conta prima il numero di righe selezionate e quindi assegna questo valore alla variabile RowCount. Quindi eseguiamo il ciclo da "1 a RowCount".
Si noti inoltre che poiché la selezione può essere un numero qualsiasi di righe, abbiamo impostato la variabile Rng su Selezione (con la riga "Imposta Rng = Selezione"). Ora possiamo usare la variabile 'Rng' per fare riferimento alla selezione nel nostro codice.
Esempio 4 - Proteggi tutti i fogli di lavoro nella cartella di lavoro attiva
È possibile utilizzare il ciclo "For Next" per esaminare tutti i fogli di lavoro nella cartella di lavoro attiva e proteggere (o rimuovere la protezione) ciascuno dei fogli di lavoro.
Di seguito è riportato il codice che lo farà:
Sub ProtectWorksheets() Dim i As Integer For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets(i).Protect Next i End Sub
Il codice sopra conta il numero di fogli utilizzando ActiveWorkbook.Worksheets.Count. Questo dice a VBA quante volte il ciclo deve essere eseguito.
In ogni caso, fa riferimento alla cartella di lavoro Ith (usando Worksheets(i)) e la protegge.
Puoi utilizzare questo stesso codice anche per rimuovere la protezione dai fogli di lavoro. Cambia solo la linea Fogli di lavoro(i).Proteggi a Fogli di lavoro(i).UnProtect.
Cicli "For Next" annidati
È possibile utilizzare i cicli "For Next" annidati per ottenere un'automazione più complessa eseguita in Excel. Un ciclo "For Next" annidato significherebbe che c'è un ciclo "For Next" all'interno di un ciclo "For Next".
Lascia che ti mostri come usarlo usando un esempio.
Supponiamo di avere 5 cartelle di lavoro aperte nel mio sistema e di voler proteggere tutti i fogli di lavoro in tutte queste cartelle di lavoro.
Di seguito è riportato il codice che lo farà:
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
Quanto sopra è un ciclo For Next nidificato poiché abbiamo usato un ciclo For Next all'interno di un altro.
Dichiarazioni "EXIT For" nei cicli For Next
L'istruzione "Exit For" consente di uscire completamente dal ciclo "For Next".
Puoi usarlo nei casi in cui desideri che il ciclo For Next termini quando viene soddisfatta una determinata condizione.
Facciamo un esempio in cui hai una serie di numeri nella colonna A e vuoi evidenziare tutti i numeri negativi in caratteri rossi. In questo caso, dobbiamo analizzare ogni cella per il suo valore e quindi modificare il colore del carattere di conseguenza.
Ma per rendere il codice più efficiente, possiamo prima verificare se ci sono valori negativi nell'elenco o meno. Se non ci sono valori negativi, possiamo usare l'istruzione Exit For per uscire semplicemente dal codice.
Di seguito è riportato il codice che esegue questa operazione:
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 Then Exit For If Rng(i).Value < 0 Then Rng(i).Font.Color = vbRed Next i End Sub
Quando si utilizza l'istruzione "Exit For" all'interno di un ciclo "For Next" annidato, uscirà dal ciclo in cui viene eseguita e andrà avanti per eseguire la riga successiva nel codice dopo il ciclo For Next.
Ad esempio, nel codice seguente, l'istruzione "Exit For" ti farà uscire dal ciclo interno, ma il ciclo esterno continuerà a funzionare.
Sub SampleCode() For i = da 1 a 10 For j = da 1 a 10 Exit For Next J Next i End Sub
Fai mentre loop
Un ciclo "Do While" consente di verificare una condizione ed eseguire il ciclo mentre tale condizione è soddisfatta (o è TRUE).
Ci sono due tipi di sintassi nel ciclo Do While.
Esegui [Mentre condizione] [Blocco di codice da eseguire] Loop
e
Esegui [Blocco di codice da eseguire] Ciclo [Condizione durante]
La differenza tra questi due è che nel primo caso, la condizione While viene verificata prima dell'esecuzione di qualsiasi blocco di codice e, nel secondo caso, viene eseguito prima il blocco di codice e quindi viene verificata la condizione While.
Ciò significa che se la condizione While è False in entrambi i casi, il codice verrà comunque eseguito almeno una volta nel secondo caso (poiché la condizione "While" viene verificata dopo che il codice è stato eseguito una volta).
Ora vediamo alcuni esempi di utilizzo dei loop Do While in VBA.
Esempio 1 - Aggiungi i primi 10 numeri interi positivi usando VBA
Supponiamo di voler aggiungere i primi dieci numeri interi positivi usando il ciclo Do While in VBA.
Per fare ciò, puoi utilizzare il ciclo Do While finché il numero successivo non è inferiore o uguale a 10. Non appena il numero è maggiore di 1o, il ciclo si interrompe.
Ecco il codice VBA che eseguirà questo ciclo Do While e mostrerà il risultato in una finestra di messaggio.
Sub AddFirst10PositiveIntegers() Dim i As Integer i = 1 Do While i <= 10 Result = Result + i i = i + 1 Loop MsgBox Result End Sub
Il ciclo precedente continua a funzionare finché il valore di "i" diventa 11. Non appena diventa 11, il ciclo termina (quando la condizione While diventa False).
All'interno del ciclo, abbiamo utilizzato una variabile Risultato che contiene il valore finale Una volta completato il ciclo, una finestra di messaggio mostra il valore della variabile "Risultato".
Esempio 2 - Inserisci le date per il mese corrente
Supponiamo che tu voglia inserire tutte le date del mese corrente in una colonna del foglio di lavoro.
Puoi farlo usando il seguente codice del ciclo 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
Il codice sopra inserirebbe tutte le date nella prima colonna del foglio di lavoro (a partire da A1). I cicli continuano fino a quando il valore del mese della variabile "CMDate" corrisponde a quello del mese corrente.
Esci dall'istruzione
Puoi usare l'istruzione Exit Do per uscire dal ciclo. Non appena il codice esegue la riga "Exit Do", esce dal ciclo Do While e passa il controllo alla riga successiva subito dopo il ciclo.
Ad esempio, se desideri inserire solo le prime 10 date, puoi uscire dal ciclo non appena vengono immesse le prime 10 date.
Il codice seguente farà questo:
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
Nel codice sopra, l'istruzione IF viene utilizzata per verificare se il valore di i è maggiore di 10 o meno. Non appena il valore di "i" diventa 10, viene eseguita l'istruzione Exit Do e il ciclo termina.
Fai fino al ciclo
I loop "Do Until" sono molto simili ai loop "Do While".
In 'Do While', il ciclo viene eseguito fino a quando non viene soddisfatta la condizione data, mentre in 'Do Until', esegue il ciclo fino a quando non viene soddisfatta la condizione specificata.
Ci sono due tipi di sintassi nel ciclo Do Until.
Esegui [Fino a condizione] [Blocco di codice da eseguire] Loop
e
Esegui [Blocco di codice da eseguire] Ciclo [Fino alla condizione]
La differenza tra questi due è che nel primo, la condizione Until viene verificata prima dell'esecuzione di qualsiasi blocco di codice e, nel secondo caso, viene eseguito prima il blocco di codice e quindi viene verificata la condizione Until.
Ciò significa che se la condizione Until è TRUE è in entrambi i casi, il codice verrà comunque eseguito almeno una volta nel secondo caso (poiché la condizione "Until" viene verificata dopo che il codice è stato eseguito una volta).
Ora vediamo alcuni esempi di utilizzo dei loop Do Until in VBA.
Nota: tutti gli esempi per Do Until sono gli stessi di Do While. Questi sono stati modificati per mostrarti come funziona il ciclo Do Until.
Esempio 1 - Aggiungi i primi 10 numeri interi positivi usando VBA
Supponiamo di voler aggiungere i primi dieci numeri interi positivi utilizzando il ciclo Do Until in VBA.
Per fare ciò, è necessario eseguire il ciclo finché il numero successivo non è inferiore o uguale a 10. Non appena il numero è maggiore di 1o, il ciclo si interrompe.
Ecco il codice VBA che eseguirà questo ciclo e mostrerà il risultato in una finestra di messaggio.
Sub AddFirst10PositiveIntegers() Dim i As Integer i = 1 Do fino a i > 10 Risultato = Risultato + i = i + 1 Loop MsgBox Risultato End Sub
Il ciclo precedente continua a funzionare finché il valore di "i" diventa 11. Non appena diventa 11, il ciclo termina (poiché la condizione "Until" diventa True).
Esempio 2 - Inserisci le date per il mese corrente
Supponiamo che tu voglia inserire tutte le date del mese corrente in una colonna del foglio di lavoro.
Puoi farlo usando il seguente codice di ciclo Do Until:
Sub EnterCurrentMonthDates() Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial(Year(Date), Month(Date), 1) Do Until Month(CMDate) Month(Date) Range("A1").Offset( i, 0) = CMDate i = i + 1 CMDate = CMDate + 1 Loop End Sub
Il codice sopra inserirebbe tutte le date nella prima colonna del foglio di lavoro (a partire da A1). Il ciclo continua finché il mese della variabile CMDate non è uguale a quello del mese corrente.
Esci dall'istruzione
Puoi usare l'istruzione "Exit Do" per uscire dal ciclo.
Non appena il codice esegue la riga "Exit Do", esce dal ciclo Do Until e passa il controllo alla riga successiva subito dopo il ciclo.
Ad esempio, se desideri inserire solo le prime 10 date, puoi uscire dal ciclo non appena vengono immesse le prime 10 date.
Il codice seguente farà questo:
Sub EnterCurrentMonthDates() Dim CMDate As Date Dim i As Integer i = 0 CMDate = DateSerial(Year(Date), Month(Date), 1) Do fino a 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
Nel codice sopra, non appena il valore di "i" diventa 10, viene eseguita l'istruzione Exit Do e il ciclo termina.
Per ciascuno
In VBA, puoi scorrere una serie di raccolte utilizzando il ciclo "For Each".
Ecco alcuni esempi di raccolte in Excel VBA:
- Una raccolta di tutte le cartelle di lavoro aperte.
- Una raccolta di tutti i fogli di lavoro in una cartella di lavoro.
- Una raccolta di tutte le celle in un intervallo di celle selezionate.
- Una raccolta di tutti i grafici o le forme nella cartella di lavoro.
Utilizzando il ciclo "For Each", puoi esaminare ciascuno degli oggetti in una raccolta ed eseguire alcune azioni su di esso.
Ad esempio, puoi esaminare tutti i fogli di lavoro in una cartella di lavoro e proteggerli, oppure puoi esaminare tutte le celle della selezione e modificare la formattazione.
Con il ciclo "For Each" (noto anche come ciclo "For Each-Next"), non è necessario sapere quanti oggetti ci sono in una raccolta.
Il ciclo "For Each" passa automaticamente attraverso ogni oggetto ed esegue l'azione specificata. Ad esempio, se si desidera proteggere tutti i fogli di lavoro in una cartella di lavoro, il codice sarebbe lo stesso se si dispone di una cartella di lavoro con fogli di lavoro 3 o fogli di lavoro 30.
Ecco la sintassi del ciclo For Each-Next in Excel VBA.
Per ogni elemento nella raccolta [Blocco di codice da eseguire] Avanti [elemento]
Ora vediamo un paio di esempi di utilizzo di For Each Loop in Excel.
Esempio 1 - Scorri tutti i fogli di lavoro in una cartella di lavoro (e proteggila)
Supponiamo di avere una cartella di lavoro in cui desideri proteggere tutti i fogli di lavoro.
Sotto il ciclo For Each-Next puoi farlo facilmente:
Sub ProtectSheets() Dim ws come foglio di lavoro per ogni ws in ActiveWorkbook.Worksheets ws.Protect Next ws End Sub
Nel codice sopra, abbiamo definito la variabile "ws" come un oggetto Foglio di lavoro. Questo dice a VBA che "ws" dovrebbe essere interpretato come un oggetto foglio di lavoro nel codice.
Ora usiamo l'istruzione "For Each" per esaminare ogni "ws" (che è un oggetto foglio di lavoro) nella raccolta di tutti i fogli di lavoro nella cartella di lavoro attiva (data da ActiveWorkbook.Worksheets).
Nota che a differenza di altri loop in cui abbiamo cercato di proteggere tutti i fogli di lavoro in una cartella di lavoro, qui non dobbiamo preoccuparci di quanti fogli di lavoro ci sono nella cartella di lavoro.
Non abbiamo bisogno di contarli per eseguire il ciclo. Il ciclo For Each garantisce che tutti gli oggetti vengano analizzati uno per uno.
Esempio 2: esaminare tutte le cartelle di lavoro aperte (e salvare tutto)
Se lavori con più cartelle di lavoro contemporaneamente, può essere utile poter salvare tutte queste cartelle di lavoro contemporaneamente.
Sotto il codice VBA puoi farlo per noi:
Sub SaveAllWorkbooks() Dim wb as workbook for each wb in Workbooks wb.Save Next wb End Sub
Tieni presente che in questo codice non viene visualizzato un messaggio che ti chiede di salvare la cartella di lavoro in una posizione specifica (se la salvi per la prima volta).
Lo salva nella cartella predefinita (nel mio caso era la cartella "Documenti"). Questo codice funziona al meglio quando questi file sono già salvati e stai apportando modifiche e vuoi salvare rapidamente tutte le cartelle di lavoro.
Esempio 3 - Passa attraverso tutte le celle in una selezione (evidenzia i valori negativi)
Utilizzando il ciclo "For Each", puoi scorrere tutte le celle in un intervallo specifico o nell'intervallo selezionato.
Questo può essere utile quando si desidera analizzare ogni cella ed eseguire un'azione basata su di essa.
Ad esempio, di seguito è riportato il codice che attraverserà tutte le celle nella selezione e cambierà il colore delle celle delle celle con valori negativi in rosso.
Sub HighlightNegativeCells() Dim Cll As Range For Every Cll In Selection If Cll.Value < 0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
(Nota che ho usato Cll come nome di variabile breve per Cell. È consigliabile non utilizzare nomi di oggetti come Fogli o Intervallo come nomi di variabili)
Nel codice sopra, il ciclo For Each-Next passa attraverso la raccolta di celle nella selezione. L'istruzione IF viene utilizzata per identificare se il valore della cella è negativo o meno. In caso affermativo, alla cella viene assegnato un colore interno rosso, altrimenti passa alla cella successiva.
Nel caso in cui non disponi di una selezione e desideri invece che VBA selezioni tutte le celle piene in una colonna, a partire da una cella specifica (proprio come usiamo Ctrl + Maiusc + Freccia giù per selezionare tutte le celle piene), puoi usa il seguente codice:
Sub HighlightNegativeCells() Dim Cll As Range Dim Rng As Range Set Rng = Range("A1", Range("A1").End(xlDown)) Per ogni Cll In Rng If Cll.Value < 0 Then Cll.Interior.Color = vbRed End If Next Cll End Sub
Nell'esempio sopra, non importa quante celle piene ci sono. Inizierà dalla cella A1 e analizzerà tutte le celle riempite contigue nella colonna.
Inoltre, non è necessario che la cella A1 sia selezionata. Puoi avere qualsiasi cella lontana selezionata e quando il codice viene eseguito, considererà ancora tutte le celle nella colonna A (a partire da A1) e colorerà le celle negative.
Dichiarazione "Esci per"
Puoi usare l'istruzione "Exit For" nel ciclo For Each-Next per uscire dal ciclo. Questo di solito viene fatto nel caso in cui sia soddisfatta una condizione specifica.
Ad esempio, nell'Esempio 3, mentre stiamo esaminando un insieme di celle, può essere più efficiente verificare se ci sono valori negativi o meno. Nel caso in cui non ci siano valori negativi, possiamo semplicemente uscire dal ciclo e risparmiare un po' di tempo di elaborazione VBA.
Di seguito è riportato il codice VBA che farà questo:
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
Dove inserire il codice VBA
Ti chiedi dove va il codice VBA nella cartella di lavoro di Excel?
Excel ha un backend VBA chiamato editor VBA. Devi copiare e incollare il codice nella finestra del codice del modulo VB Editor.
Ecco i passaggi per farlo:
- Vai alla scheda Sviluppatore.
- Fare clic sull'opzione Visual Basic. Questo aprirà l'editor VB nel backend.
- Nel riquadro Project Explorer nell'editor VB, fare clic con il pulsante destro del mouse su qualsiasi oggetto per la cartella di lavoro in cui si desidera inserire il codice. Se non vedi Esplora progetti, vai alla scheda Visualizza e fai clic su Esplora progetti.
- Vai su Inserisci e fai clic su Modulo. Questo inserirà un oggetto modulo per la tua cartella di lavoro.
- Copia e incolla il codice nella finestra del modulo.
