Guinguette Marais Poitevin

Per ottenere il massimo da Excel e VBA, devi sapere come usare i loop in modo efficiente.

In VBA, i loop ti permettono di passare attraverso un insieme di oggetti/valori e analizzarli uno per uno. Puoi anche eseguire compiti specifici per ogni ciclo.

Ecco un semplice esempio di utilizzo dei cicli VBA in Excel.

Supponiamo che tu abbia un set di dati e che tu voglia evidenziare tutte le celle nelle righe pari. Potete usare un ciclo VBA per passare attraverso l’intervallo e analizzare ogni numero di riga della cella. Se risulta essere pari, gli date un colore, altrimenti lo lasciate così com’è.

Ora questo, naturalmente, è molto semplice di looping in Excel VBA (e potete anche farlo usando la formattazione condizionale).

Nella vita reale, potete fare molto di più con i loop VBA in Excel che vi possono aiutare ad automatizzare i compiti.

Ecco alcuni esempi pratici in cui i cicli VBA possono essere utili:

• Circa un intervallo di celle e analizza ogni cella (evidenzia le celle con un testo specifico).
• Correre tutti i fogli di lavoro e fare qualcosa con ognuno di essi (come proteggerli/sbloccarli).
• Correre tutte le cartelle di lavoro aperte (e salvare ogni cartella di lavoro o chiuderle tutte tranne quella attiva).
• Capire tutti i caratteri di una cella (ed estrarre la parte numerica da una stringa).
• Capire tutti i valori di un array.
• Capire tutti i grafici/oggetti (e dare un bordo o cambiare il colore di sfondo).

Ora per usare al meglio i loop in Excel VBA, è necessario conoscere i diversi tipi che esistono e la sintassi corretta di ciascuno.

In questo tutorial, mostrerò diversi tipi di loop di Excel VBA e coprirò alcuni esempi per ogni loop

Se sei interessato a imparare VBA nel modo più semplice, dai un’occhiata alla mia formazione online su Excel VBA.

For Next Loop

Il ciclo ‘For Next’ ti permette di passare attraverso un blocco di codice per il numero di volte specificato.

Per esempio, se vi chiedo di sommare manualmente gli interi da 1 a 10, dovrete aggiungere i primi due numeri, poi aggiungere il terzo numero al risultato, poi aggiungere il quarto numero al risultato, e così via.

Non è così?

La stessa logica è usata nel ciclo For Next in VBA.

Si specifica quante volte si vuole che il ciclo venga eseguito e si specifica anche cosa si vuole che il codice faccia ogni volta che il ciclo viene eseguito.

Di seguito la sintassi del ciclo For Next:

For Counter = Start To End Next 

Nel ciclo For Next, potete usare un contatore (o qualsiasi variabile) che verrà usato per eseguire il ciclo. Questo contatore vi permette di eseguire questo ciclo per un certo numero di volte.

Per esempio, se voglio aggiungere i primi 10 numeri interi positivi, allora il valore del mio contatore sarà da 1 a 10.

Diamo un’occhiata ad alcuni esempi per capire meglio come funziona il ciclo For Next.

Esempio 1 – Aggiungere i primi 10 numeri interi positivi

Di seguito il codice che aggiungerà i primi 10 numeri interi positivi usando un ciclo For Next.

Viene poi visualizzata una casella di messaggio che mostra la somma di questi numeri.

Sub AddNumbers()Dim Total As IntegerDim Count As IntegerTotal = 0For Count = 1 To 10Total = Total + CountNext CountMsgBox TotalEnd Sub

In questo codice, il valore di Total è impostato a 0 prima di entrare nel ciclo For Next.

Una volta entrato nel ciclo, mantiene il valore totale dopo ogni ciclo. Così, dopo il primo ciclo, quando Counter è 1, il valore di ‘Total’ diventa 1, e dopo il secondo ciclo diventa 3 (1+2), e così via.

E infine, quando il ciclo finisce, la variabile ‘Total’ ha la somma dei primi 10 interi positivi.

A MsgBox poi visualizza semplicemente il risultato in una casella di messaggio.

Esempio 2 – Sommare i primi 5 interi positivi pari

Per sommare i primi cinque interi positivi pari (cioè 2,4,6,8, e 10), avete bisogno di 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 IntegerDim Count As IntegerTotal = 0For Count = 2 To 10 Step 2Total = Total + CountNext CountMsgBox TotalEnd Sub

Nota che abbiamo iniziato il valore del conteggio da 2 e abbiamo anche usato lo ‘Step 2’.

Quando usi lo ‘Step 2’, dice al codice di incrementare il valore di ‘Count’ di 2 ogni volta che il ciclo viene eseguito.

Così il valore del conteggio parte da 2 e poi diventa 4, 6, 8 e 10 man mano che il ciclo viene eseguito.

NOTA: Un altro modo di fare questo potrebbe essere quello di eseguire il ciclo da 1 a 10 e all’interno del ciclo controllare se il numero è pari o dispari. Tuttavia, usare 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 parte da un valore più alto e continua a diminuire del valore di Step specificato.

Esempio 3 – Inserire il numero di serie nelle celle selezionate

Puoi anche usare il ciclo For Next per passare attraverso una collezione di oggetti (come celle, 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 RangeDim Counter As IntegerDim RowCount As IntegerSet Rng = SelectionRowCount = Rng.Rows.CountFor Counter = 1 To RowCountActiveCell.Offset(Counter - 1, 0).Value = CounterNext CounterEnd Sub

Il codice qui sopra conta prima il numero di righe selezionate e poi assegna questo valore alla variabile RowCount. Poi eseguiamo il ciclo da ‘1 a RowCount’.

Nota anche che, poiché la selezione può essere un numero qualsiasi di righe, abbiamo impostato la variabile Rng a Selection (con la linea ‘Set Rng = Selection’). Ora possiamo usare la variabile ‘Rng’ per riferirci alla selezione nel nostro codice.

Esempio 4 – Proteggere tutti i fogli di lavoro nella cartella di lavoro attiva

Puoi usare il ciclo ‘For Next’ per passare attraverso tutti i fogli di lavoro nella cartella di lavoro attiva, e proteggere (o non proteggere) ogni foglio di lavoro.

Di seguito è riportato il codice che farà questo:

Sub ProtectWorksheets()Dim i As IntegerFor i = 1 To ActiveWorkbook.Worksheets.CountWorksheets(i).ProtectNext iEnd Sub

Il codice precedente conta il numero di fogli usando ActiveWorkbook.Worksheets.Count. Questo dice a VBA quante volte il ciclo deve essere eseguito.

In ogni istanza, si riferisce all’iesima cartella di lavoro (usando Worksheets(i)) e la protegge.

Puoi usare questo stesso codice anche per Unprotect worksheets. Basta cambiare la linea Worksheets(i).Protect in Worksheets(i).UnProtect.

Loop ‘For Next’ annidati

Puoi usare loop ‘For Next’ annidati per ottenere un’automazione più complessa in Excel. Un ciclo ‘For Next’ annidato significa che c’è un ciclo ‘For Next’ all’interno di un ciclo ‘For Next’.

Lasciate che vi mostri come usarlo usando un esempio.

Supponiamo che io abbia 5 cartelle di lavoro aperte nel mio sistema e voglio proteggere tutti i fogli di lavoro in tutte queste cartelle.

Di seguito c’è il codice che farà questo:

Sub ProtectWorksheets()Dim i As IntegerDim j As IntegerFor i = 1 To Workbooks.CountFor j = 1 To Workbooks(i).Worksheets.CountWorkbooks(i).Worksheets(j).ProtectNext jNext iEnd Sub

Questo è un ciclo For Next annidato perché abbiamo usato un ciclo For Next dentro un altro.

Dichiarazioni ‘EXIT For’ nei cicli For Next

La dichiarazione ‘Exit For’ vi permette di uscire completamente dal ciclo ‘For Next’.

Potete usarla nei casi in cui volete che il ciclo For Next finisca quando una certa condizione è soddisfatta.

Prendiamo un esempio in cui avete una serie di numeri nella colonna A e volete evidenziare tutti i numeri negativi in carattere rosso. In questo caso, dobbiamo analizzare ogni cella per il suo valore e poi cambiare il colore del carattere di conseguenza.

Ma per rendere il codice più efficiente, possiamo prima controllare se ci sono valori negativi nella lista o no. Se non ci sono valori negativi, possiamo usare l’istruzione Exit For per uscire semplicemente dal codice.

Di seguito il codice che fa questo:

Sub HghlightNegative()Dim Rng As RangeSet Rng = Range("A1", Range("A1").End(xlDown))Counter = Rng.CountFor i = 1 To CounterIf WorksheetFunction.Min(Rng) >= 0 Then Exit ForIf Rng(i).Value < 0 Then Rng(i).Font.Color = vbRedNext iEnd Sub

Quando usate l’istruzione ‘Exit For’ all’interno di un ciclo ‘For Next’ annidato, uscirà dal ciclo in cui è stata eseguita e andrà ad eseguire la prossima linea nel codice dopo il ciclo For Next.

Per esempio, nel codice seguente, l’istruzione ‘Exit For’ vi farà uscire dal ciclo interno, ma il ciclo esterno continuerà a funzionare.

Sub SampleCode()For i = 1 To 10For j = 1 to 10Exit ForNext JNext iEnd Sub

Do While Loop

Un ciclo ‘Do While’ vi permette di controllare una condizione ed eseguire il ciclo mentre questa condizione è soddisfatta (o è VERA).

Ci sono due tipi di sintassi nel Do While Loop.

Do Loop

e

DoLoop 

La differenza tra questi due è che nel primo, la condizione While viene controllata prima che qualsiasi blocco di codice venga eseguito, e nel secondo caso, il blocco di codice viene eseguito prima e poi viene controllata la condizione While.

Questo significa che se la condizione While è falsa in entrambi i casi, il codice verrà comunque eseguito almeno una volta nel secondo caso (poiché la condizione ‘While’ viene controllata dopo che il codice è stato eseguito una volta).

Ora vediamo alcuni esempi di utilizzo dei cicli Do While in VBA.

Esempio 1 – Aggiungere i primi 10 interi positivi usando VBA

Supponiamo che tu voglia aggiungere i primi dieci interi positivi usando il ciclo Do While in VBA.

Per fare questo, puoi usare il ciclo Do While finché il prossimo numero è minore o uguale a 10. Non appena il numero è maggiore di 1o, il vostro ciclo si fermerà.

Qui c’è il codice VBA che eseguirà questo ciclo Do While e mostrerà il risultato in una casella di messaggio.

Sub AddFirst10PositiveIntegers()Dim i As Integeri = 1Do While i <= 10Result = Result + ii = i + 1LoopMsgBox ResultEnd Sub

Il suddetto ciclo continua a lavorare fino a quando il valore di ‘i’ diventa 11. Non appena diventa 11, il ciclo termina (poiché la condizione While diventa False).

Nel ciclo, abbiamo usato una variabile Result che contiene il valore finale Una volta che il ciclo è completato, una casella di messaggio mostra il valore della variabile ‘Result’.

Esempio 2 – Inserire le date del mese corrente

Diciamo che volete inserire tutte le date del mese corrente in una colonna del foglio di lavoro.

Lo potete fare usando il seguente codice Do While:

Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do While Month(CMDate) = Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1CMDate = CMDate + 1LoopEnd Sub

Il codice precedente inserirebbe tutte le date nella prima colonna del foglio di lavoro (partendo da A1). Il ciclo continua fino a quando il valore del mese della variabile ‘CMDate’ corrisponde a quello del mese corrente.

Exit Do Statement

Potete usare l’istruzione Exit Do per uscire dal ciclo. Non appena il codice esegue la linea ‘Exit Do’, esce dal ciclo Do While e passa il controllo alla linea successiva subito dopo il ciclo.

Per esempio, se volete inserire solo le prime 10 date, allora potete uscire dal ciclo non appena le prime 10 date sono inserite.

Il codice qui sotto farà questo:

Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do While Month(CMDate) = Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1If i >= 10 Then Exit DoCMDate = CMDate + 1LoopEnd Sub

Nel codice qui sopra, l’istruzione IF è usata per controllare se il valore di i è maggiore di 10 o no. Non appena il valore di ‘i’ diventa 10, viene eseguita l’istruzione Exit Do e il ciclo termina.

Do Until Loop

I cicli ‘Do Until’ sono molto simili ai cicli ‘Do While’.

Nel ‘Do While’, il ciclo viene eseguito fino a quando la condizione data è soddisfatta, mentre nel ‘Do Until’, si esegue il ciclo fino a quando la condizione specificata è soddisfatta.

Ci sono due tipi di sintassi nel Do Until Loop.

Do Loop

e

DoLoop 

La differenza tra questi due è che nel primo, la condizione Until viene controllata prima che qualsiasi blocco di codice venga eseguito, e nel secondo caso, il blocco di codice viene eseguito prima e poi viene controllata la condizione Until.

Questo 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 controllata dopo che il codice è stato eseguito una volta).

Ora vediamo alcuni esempi di utilizzo dei cicli Do Until in VBA.

Nota: Tutti gli esempi per Do Until sono uguali a quelli di Do While. Questi sono stati modificati per mostrarvi come funziona il ciclo Do Until.

Esempio 1 – Aggiungere i primi 10 interi positivi usando VBA

Supponiamo che vogliate aggiungere i primi dieci interi positivi usando il ciclo Do Until in VBA.

Per fare questo, dovete eseguire il ciclo finché il prossimo numero è minore o uguale a 10. Non appena il numero è maggiore di 1o, il vostro ciclo si fermerà.

Ecco il codice VBA che eseguirà questo ciclo e mostrerà il risultato in una casella di messaggio.

Sub AddFirst10PositiveIntegers()Dim i As Integeri = 1Do Until i > 10Result = Result + ii = i + 1LoopMsgBox ResultEnd Sub

Il ciclo di cui sopra continua a funzionare fino a quando il valore di ‘i’ diventa 11. Non appena diventa 11, il ciclo termina (poiché la condizione ‘Until’ diventa vera).

Esempio 2 – Inserire le date del mese corrente

Diciamo che volete inserire tutte le date del mese corrente in una colonna del foglio di lavoro.

Lo si può fare usando il seguente codice Do Until loop:

Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do Until Month(CMDate) <> Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1CMDate = CMDate + 1LoopEnd Sub

Il codice precedente inserirebbe tutte le date nella prima colonna del foglio di lavoro (a partire da A1). Il ciclo continua fino a quando il mese della variabile CMDate non è uguale a quello del mese corrente.

Exit Do Statement

Potete usare l’istruzione ‘Exit Do’ per uscire dal ciclo.

Non appena il codice esegue la linea ‘Exit Do’, esce dal ciclo Do Until e passa il controllo alla prossima linea subito dopo il ciclo.

Per esempio, se volete inserire solo le prime 10 date, allora potete uscire dal ciclo non appena le prime 10 date sono inserite.

Il codice qui sotto farà questo:

Sub EnterCurrentMonthDates()Dim CMDate As DateDim i As Integeri = 0CMDate = DateSerial(Year(Date), Month(Date), 1)Do Until Month(CMDate) <> Month(Date)Range("A1").Offset(i, 0) = CMDatei = i + 1If i >= 10 Then Exit DoCMDate = CMDate + 1LoopEnd Sub

Nel codice qui sopra, non appena il valore di ‘i’ diventa 10, viene eseguito Exit Do statment e il ciclo finisce.

For Each

In VBA, potete fare un ciclo su un insieme di collezioni usando il ciclo ‘For Each’.

Ecco alcuni esempi di collezioni in Excel VBA:

• Una collezione di tutte le cartelle di lavoro aperte.
• Una collezione di tutti i fogli di lavoro in una cartella di lavoro.
• Una collezione di tutte le celle in un intervallo di celle selezionate.
• Un insieme di tutti i grafici o forme nella cartella di lavoro.

Utilizzando il ciclo ‘For Each’, puoi passare attraverso ogni oggetto in un insieme ed eseguire qualche azione su di esso.

Per esempio, potete passare attraverso tutti i fogli di lavoro in una cartella di lavoro e proteggerli, o potete passare attraverso tutte le celle nella selezione e cambiare la formattazione.

Con il ciclo ‘For Each’ (anche chiamato ciclo ‘For Each-Next’), non avete bisogno di sapere quanti oggetti ci sono in una collezione.

Il ciclo ‘For Each’ passa automaticamente attraverso ogni oggetto ed esegue l’azione specificata. Per esempio, se volete proteggere tutti i fogli di lavoro in una cartella di lavoro, il codice sarà lo stesso sia che abbiate una cartella di lavoro con 3 fogli di lavoro o 30 fogli di lavoro.

Ecco la sintassi del ciclo For Each-Next in Excel VBA.

For Each element In collectionNext 

Ora vediamo un paio di esempi di utilizzo del ciclo For Each in Excel.

Esempio 1 – Passare attraverso tutti i fogli di lavoro in una cartella di lavoro (e proteggerla)

Supponiamo che abbiate una cartella di lavoro dove volete proteggere tutti i fogli di lavoro.

Il seguente ciclo For Each-Next può farlo facilmente:

Sub ProtectSheets()Dim ws As WorksheetFor Each ws In ActiveWorkbook.Worksheetsws.ProtectNext wsEnd Sub

Nel codice sopra, abbiamo definito la variabile ‘ws’ come un oggetto Worksheet. Questo dice a VBA che ‘ws’ deve essere interpretato come un oggetto foglio di lavoro nel codice.

Ora usiamo l’istruzione ‘For Each’ per passare attraverso ogni ‘ws’ (che è un oggetto foglio di lavoro) nella collezione di tutti i fogli di lavoro nella cartella di lavoro attiva (data da ActiveWorkbook.Worksheets).

Nota che a differenza di altri cicli 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 assicura che tutti gli oggetti siano analizzati uno per uno.

Esempio 2 – Passare attraverso tutte le cartelle di lavoro aperte (e salvarle tutte)

Se si lavora con più cartelle di lavoro allo stesso tempo, può essere utile poterle salvare tutte in una volta.

Il seguente codice VBA può fare questo per noi:

Sub SaveAllWorkbooks()Dim wb As WorkbookFor Each wb In Workbookswb.SaveNext wbEnd Sub

Nota che in questo codice, non ti viene chiesto di salvare la cartella di lavoro in una posizione specifica (se la salvi per la prima volta).

La salva nella cartella di default (nel mio caso era la cartella ‘Documenti’). Questo codice funziona meglio quando questi file sono già salvati e stai facendo delle modifiche e vuoi salvare tutte le cartelle di lavoro velocemente.

Esempio 3 – Passare attraverso tutte le celle in una selezione (evidenziare i valori negativi)

Utilizzando il ciclo ‘For Each’, puoi passare attraverso tutte le celle in un intervallo specifico o nell’intervallo selezionato.

Questo può essere utile quando volete analizzare ogni cella ed eseguire un’azione in base ad essa.

Per esempio, qui sotto c’è il codice che passerà attraverso tutte le celle della selezione e cambierà il colore delle celle con valori negativi in rosso.

Sub HighlightNegativeCells()Dim Cll As RangeFor Each Cll In SelectionIf Cll.Value < 0 ThenCll.Interior.Color = vbRedEnd IfNext CllEnd Sub

(Notate che ho usato Cll come nome breve della variabile Cell. È consigliabile non usare nomi di oggetti come Sheets o Range come nomi di variabili)

Nel codice sopra, il ciclo For Each-Next passa attraverso l’insieme di celle nella selezione. L’istruzione IF è usata per identificare se il valore della cella è negativo o no. Se lo è, alla cella viene dato un colore interno rosso, altrimenti si passa alla cella successiva.

Nel caso in cui non avete una selezione, e invece volete che VBA selezioni tutte le celle riempite in una colonna, partendo da una cella specifica (proprio come noi usiamo Control + Shift + tasto freccia giù per selezionare tutte le celle riempite), potete usare il codice seguente:

Sub HighlightNegativeCells()Dim Cll As RangeDim Rng As RangeSet Rng = Range("A1", Range("A1").End(xlDown))For Each Cll In RngIf Cll.Value < 0 ThenCll.Interior.Color = vbRedEnd IfNext CllEnd Sub

Nell’esempio precedente, non importa quante celle riempite ci sono. Partirà dalla cella A1 e analizzerà tutte le celle riempite contigue nella colonna.

Non è necessario che la cella A1 sia selezionata. Potete avere qualsiasi cella lontana selezionata e quando il codice viene eseguito, considererà ancora tutte le celle della colonna A (a partire da A1) e colorerà le celle negative.

Dichiarazione ‘Exit For’

Potete usare la dichiarazione ‘Exit For’ nel ciclo For Each-Next per uscire dal ciclo. Questo di solito viene fatto nel caso in cui una specifica condizione sia soddisfatta.

Per esempio, nell’esempio 3, dato che stiamo attraversando un insieme di celle, può essere più efficiente controllare 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 RangeFor Each Cll In SelectionIf WorksheetFunction.Min(Selection) >= 0 Then Exit ForIf Cll.Value < 0 ThenCll.Interior.Color = vbRedEnd IfNext CllEnd Sub

Dove mettere il codice VBA

Si sta chiedendo dove va il codice VBA nella sua cartella di lavoro Excel?

Excel ha un backend VBA chiamato editor VBA. È necessario copiare e incollare il codice nella finestra del modulo VB Editor.

Questi sono i passi per farlo:

1. Vai alla scheda Developer.
2. Clicca sull’opzione Visual Basic. Questo aprirà l’editor VB nel backend.
3. Nel riquadro Project Explorer nell’editor VB, clicca con il tasto destro del mouse su qualsiasi oggetto della cartella di lavoro in cui vuoi inserire il codice. Se non vedi l’Esploratore Progetto vai alla scheda Visualizza e clicca su Esploratore Progetto.
4. Vai su Inserisci e clicca su Modulo. Questo inserirà un oggetto modulo per la tua cartella di lavoro.
5. Copia e incolla il codice nella finestra del modulo.

Ti possono piacere anche i seguenti tutorial di Excel:

• Come registrare una macro in Excel.
• Creare funzioni definite dall’utente in Excel.
• Excel VBA Msgbox
• Come eseguire una macro in Excel.
• Come creare e usare i componenti aggiuntivi in Excel.
• Eventi Excel VBA – una guida facile (e completa).
• Come ordinare i dati in Excel usando VBA (una guida passo dopo passo).
• 24 utili esempi di macro Excel per principianti VBA (pronti all’uso).
• Come usare la funzione Excel VBA InStr (con ESEMPI pratici).
• Excel Personal Macro Workbook | Save & Usa le macro in tutte le cartelle di lavoro.
• Usare Select Case in Excel VBA.