Lavorare con i fogli di lavoro utilizzando Excel VBA (spiegato con esempi)

Oltre alle celle e agli intervalli, lavorare con i fogli di lavoro è un'altra area che dovresti conoscere per utilizzare VBA in modo efficiente in Excel.

Proprio come qualsiasi oggetto in VBA, i fogli di lavoro hanno proprietà e metodi diversi ad esso associati che è possibile utilizzare durante l'automazione del lavoro con VBA in Excel.

In questo tutorial, tratterò in dettaglio i "Fogli di lavoro" e ti mostrerò anche alcuni esempi pratici.

Quindi iniziamo.

Tutti i codici che menziono in questo tutorial devono essere inseriti nell'editor VB. Vai alla sezione "Dove inserire il codice VBA" per sapere come funziona.

Se sei interessato a imparare VBA nel modo più semplice, dai un'occhiata a my Formazione online su VBA di Excel.

Differenza tra fogli di lavoro e fogli in VBA

In VBA, hai due raccolte che a volte possono essere un po' confuse.

In una cartella di lavoro, puoi avere fogli di lavoro e fogli grafici. L'esempio seguente ha tre fogli di lavoro e un foglio grafico.

In Excel VBA:

• La raccolta "Fogli di lavoro" farebbe riferimento alla raccolta di tutti gli oggetti del foglio di lavoro in una cartella di lavoro. Nell'esempio sopra, la raccolta Fogli di lavoro sarebbe composta da tre fogli di lavoro.
• La raccolta "Fogli" farebbe riferimento a tutti i fogli di lavoro e ai fogli grafici nella cartella di lavoro. Nell'esempio sopra, avrebbe quattro elementi: 3 fogli di lavoro + 1 foglio grafico.

Se disponi di una cartella di lavoro che contiene solo fogli di lavoro e nessun foglio grafico, la raccolta "Fogli di lavoro" e "Fogli" è la stessa.

Ma quando hai uno o più fogli grafici, la raccolta "Fogli" sarebbe più grande della raccolta "Fogli di lavoro"

Fogli = Fogli di lavoro + Fogli grafici

Ora, con questa distinzione, consiglio di essere il più specifico possibile quando si scrive un codice VBA.

Quindi, se devi fare riferimento solo ai fogli di lavoro, usa la raccolta "Fogli di lavoro" e se devi fare riferimento a tutti i fogli (compresi i fogli grafici), usa la raccolta "Fogli".

In questo tutorial, utilizzerò solo la raccolta "Fogli di lavoro".

Fare riferimento a un foglio di lavoro in VBA

Esistono molti modi diversi per fare riferimento a un foglio di lavoro in VBA.

Capire come fare riferimento ai fogli di lavoro ti aiuterebbe a scrivere un codice migliore, specialmente quando usi i loop nel tuo codice VBA.

Utilizzo del nome del foglio di lavoro

Il modo più semplice per fare riferimento a un foglio di lavoro è utilizzare il suo nome.

Ad esempio, supponi di avere una cartella di lavoro con tre fogli di lavoro: Foglio 1, Foglio 2, Foglio 3.

E vuoi attivare il Foglio 2.

Puoi farlo usando il seguente codice: Sub ActivateSheet() Worksheets("Sheet2").Activate End Sub

Il codice sopra chiede a VBA di fare riferimento a Sheet2 nella raccolta Fogli di lavoro e attivarlo.

Poiché stiamo utilizzando il nome esatto del foglio, puoi anche utilizzare la raccolta Fogli qui. Quindi anche il codice seguente farebbe la stessa cosa.

Sub ActivateSheet() Sheets("Sheet2").Activate End Sub

Utilizzo del numero di indice

Sebbene l'utilizzo del nome del foglio sia un modo semplice per fare riferimento a un foglio di lavoro, a volte potresti non conoscere il nome esatto del foglio di lavoro.

Ad esempio, se stai utilizzando un codice VBA per aggiungere un nuovo foglio di lavoro alla cartella di lavoro e non sai quanti fogli di lavoro sono già presenti, non sapresti il ​​nome del nuovo foglio di lavoro.

In questo caso, puoi utilizzare il numero di indice dei fogli di lavoro.

Supponiamo di avere i seguenti fogli in una cartella di lavoro:

Il codice seguente attiverebbe Sheet2:

Sub ActivateSheet() Fogli di lavoro(2).Activate End Sub

Nota che abbiamo usato il numero di indice 2 in Fogli di lavoro(2). Questo farebbe riferimento al secondo oggetto nella raccolta dei fogli di lavoro.

Ora, cosa succede quando usi 3 come numero di indice?

Selezionerà Foglio3.

Se ti stai chiedendo perché ha selezionato Sheet3, poiché è chiaramente il quarto oggetto.

Ciò accade perché un foglio grafico non fa parte della raccolta di fogli di lavoro.

Quindi, quando usiamo i numeri di indice nella raccolta Fogli di lavoro, farà riferimento solo ai fogli di lavoro nella cartella di lavoro (e ignorerà i fogli del grafico).

Al contrario, se stai usando Fogli, Fogli(1) farebbe riferimento a Fogli1, Fogli(2) farebbe riferimento a Foglio2, Fogli(3) farebbe riferimento a Grafico1 e Fogli(4) farebbe riferimento a Foglio3.

Questa tecnica di utilizzo del numero di indice è utile quando si desidera scorrere tutti i fogli di lavoro in una cartella di lavoro. Puoi contare il numero di fogli di lavoro e poi scorrerli usando questo conteggio (vedremo come farlo più avanti in questo tutorial).

Nota: il numero di indice va da sinistra a destra. Quindi, se sposti Foglio2 a sinistra di Foglio1, allora Fogli di lavoro (1) farebbe riferimento a Foglio2.

Utilizzo del nome in codice del foglio di lavoro

Uno degli svantaggi dell'utilizzo del nome del foglio (come abbiamo visto nella sezione precedente) è che un utente può modificarlo.

E se il nome del foglio è stato modificato, il tuo codice non funzionerebbe finché non cambi anche il nome del foglio di lavoro nel codice VBA.

Per affrontare questo problema, puoi usare il nome in codice del foglio di lavoro (invece del nome normale che abbiamo usato finora). Un nome in codice può essere assegnato nell'editor VB e non cambia quando si cambia il nome del foglio dall'area del foglio di lavoro.

Per dare al tuo foglio di lavoro un nome in codice, segui i passaggi seguenti:

1. Fare clic sulla scheda Sviluppatore.
2. Fare clic sul pulsante Visual Basic. Questo aprirà l'editor VB.
3. Fare clic sull'opzione Visualizza nel menu e fare clic su Finestra progetto. Ciò renderà visibile il riquadro Proprietà. Se il riquadro Proprietà è già visibile, saltare questo passaggio.
4. Fare clic sul nome del foglio in Esplora progetti che si desidera rinominare.
5. Nel riquadro Proprietà, modifica il nome nel campo davanti a (Nome). Nota che non puoi avere spazi nel nome.

I passaggi precedenti cambierebbero il nome del tuo foglio di lavoro nel backend VBA. Nella visualizzazione del foglio di lavoro di Excel, puoi denominare il foglio di lavoro come preferisci, ma nel back-end risponderà a entrambi i nomi: il nome del foglio e il nome del codice.

Nell'immagine sopra, il nome del foglio è "SheetName" e il nome in codice è "CodeName". Anche se cambi il nome del foglio nel foglio di lavoro, il nome in codice rimane lo stesso.

Ora puoi utilizzare la raccolta Fogli di lavoro per fare riferimento al foglio di lavoro o utilizzare il nome in codice.

Ad esempio, entrambe le righe attiveranno il foglio di lavoro.

Fogli di lavoro ("Nome foglio"). Attiva NomeCodice. Attiva

La differenza tra questi due è che se cambi il nome del foglio di lavoro, il primo non funzionerebbe. Ma la seconda riga continuerebbe a funzionare anche con il nome modificato. Anche la seconda riga (usando CodeName) è più corta e più facile da usare.

Fare riferimento a un foglio di lavoro in una cartella di lavoro diversa

Se vuoi fare riferimento a un foglio di lavoro in una cartella di lavoro diversa, quella cartella di lavoro deve essere aperta durante l'esecuzione del codice e devi specificare il nome della cartella di lavoro e il foglio di lavoro a cui vuoi fare riferimento.

Ad esempio, se si dispone di una cartella di lavoro con il nome Esempi e si desidera attivare Foglio1 nella cartella di lavoro di esempio, è necessario utilizzare il codice seguente:

Sub SheetActivate() Workbooks("Examples.xlsx").Worksheets("Sheet1").Activate End Sub

Si noti che se la cartella di lavoro è stata salvata, è necessario utilizzare il nome del file insieme all'estensione. Se non sei sicuro del nome da utilizzare, chiedi aiuto a Project Explorer.

Nel caso in cui la cartella di lavoro non sia stata salvata, non è necessario utilizzare l'estensione del file.

Aggiungere un foglio di lavoro

Il codice seguente aggiungerà un foglio di lavoro (come primo foglio di lavoro, ovvero come foglio più a sinistra nella scheda del foglio).

Sub AddSheet() Worksheets.Add End Sub

Prende il nome predefinito Foglio2 (o qualsiasi altro numero in base a quanti fogli sono già presenti).

Se desideri aggiungere un foglio di lavoro prima di un foglio di lavoro specifico (ad esempio Foglio2), puoi utilizzare il codice seguente.

Sub AddSheet() Worksheets.Add Before:=Worksheets("Sheet2") End Sub

Il codice sopra indica a VBA di aggiungere un foglio e quindi utilizza l'istruzione "Prima" per specificare il foglio di lavoro prima del quale deve essere inserito il nuovo foglio di lavoro.

Allo stesso modo, puoi anche aggiungere un foglio dopo un foglio di lavoro (diciamo Foglio2), utilizzando il codice seguente:

Sub AddSheet() Worksheets.Add After:=Worksheets("Sheet2") End Sub

Se vuoi che il nuovo foglio venga aggiunto alla fine dei fogli, devi prima sapere quanti fogli ci sono. Il codice seguente conta prima il numero di fogli, e poi aggiunge il nuovo foglio dopo l'ultimo foglio (a cui ci riferiamo utilizzando il numero indice).

Sub AddSheet() Dim SheetCount As Integer SheetCount = Worksheets.Count Worksheets.Add After:=Worksheets(SheetCount) End Sub

Eliminare un foglio di lavoro

Il codice seguente eliminerebbe il foglio attivo dalla cartella di lavoro.

Sub DeleteSheet() ActiveSheet.Delete End Sub

Il codice sopra mostrerebbe un messaggio di avviso prima di eliminare il foglio di lavoro.

Se non vuoi vedere il messaggio di avviso, usa il codice seguente:

Sub DeleteSheet() Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Quando Application.DisplayAlerts è impostato su False, non verrà visualizzato il messaggio di avviso. Se lo usi, ricorda di reimpostarlo su True alla fine del codice.

Ricorda che non puoi annullare questa eliminazione, quindi usa il codice sopra quando sei assolutamente sicuro.

Se desideri eliminare un foglio specifico, puoi farlo utilizzando il seguente codice:

Sub DeleteSheet() Fogli di lavoro ("Sheet2"). Elimina End Sub

Puoi anche usare il nome in codice del foglio per eliminarlo.

Sub DeleteSheet() Sheet5.Delete End Sub

Rinominare i fogli di lavoro

È possibile modificare la proprietà del nome del foglio di lavoro per cambiarne il nome.

Il codice seguente cambierà il nome di Foglio1 in "Riepilogo".

Sub RenameSheet() Worksheets("Sheet1").Name = "Summary" End Sub

Puoi combinare questo con il metodo di aggiunta dei fogli per avere un set di fogli con nomi specifici.

Ad esempio, se si desidera inserire quattro fogli con il nome 2021-2022 Q1,2021-2022 Q2,2021-2022 Q3 e 2021-2022 Q4, è possibile utilizzare il codice seguente.

Sub RenameSheet() Dim Countsheets As Integer Countsheets = Worksheets.Count For i = 1 To 4 Worksheets.Aggiungi dopo:=Worksheets(Countsheets + i - 1) Worksheets(Countsheets + i).Name = "2018 Q" & i Next i Fine sottotitolo

Nel codice sopra, prima contiamo il numero di fogli e poi usiamo un ciclo For Next per inserire nuovi fogli alla fine. Quando il foglio viene aggiunto, anche il codice lo rinomina.

Assegnazione di un oggetto del foglio di lavoro a una variabile

Quando si lavora con i fogli di lavoro, è possibile assegnare un foglio di lavoro a una variabile oggetto e quindi utilizzare la variabile invece dei riferimenti del foglio di lavoro.

Ad esempio, se si desidera aggiungere un prefisso dell'anno a tutti i fogli di lavoro, invece di contare i fogli ed eseguire il ciclo tante volte, è possibile utilizzare la variabile oggetto.

Ecco il codice che aggiungerà2021-2022 come prefisso a tutti i nomi del foglio di lavoro.

Sub RenameSheet() Dim Ws As Worksheet For Each Ws In Worksheets Ws.Name = "2018 - " & Ws.Name Next Ws End Sub

Il codice sopra dichiara una variabile Ws come tipo di foglio di lavoro (usando la riga "Dim Ws As Worksheet").

Ora, non abbiamo bisogno di contare il numero di fogli per scorrerli. Invece, possiamo usare il ciclo "Per ogni Ws in Worksheets". Questo ci consentirà di esaminare tutti i fogli nella raccolta dei fogli di lavoro. Non importa se ci sono 2 fogli o 20 fogli.

Mentre il codice sopra ci consente di scorrere tutti i fogli, puoi anche assegnare un foglio specifico a una variabile.

Nel codice seguente, assegniamo la variabile Ws a Sheet2 e la usiamo per accedere a tutte le proprietà di Sheet2.

Sub RenameSheet() Dim Ws As Worksheet Set Ws = Worksheets("Sheet2") Ws.Name = "Riepilogo" Ws.Protect End Sub

Dopo aver impostato un riferimento del foglio di lavoro su una variabile oggetto (utilizzando l'istruzione SET), è possibile utilizzare quell'oggetto al posto del riferimento del foglio di lavoro. Questo può essere utile quando hai un codice lungo e complicato e vuoi cambiare il riferimento. Invece di apportare la modifica ovunque, puoi semplicemente apportare la modifica nell'istruzione SET.

Si noti che il codice dichiara l'oggetto Ws come variabile di tipo Worksheet (usando la riga Dim Ws come Worksheet).

Nascondi i fogli di lavoro usando VBA (nascosto + molto nascosto)

Nascondere e scoprire i fogli di lavoro in Excel è un'attività semplice.

Puoi nascondere un foglio di lavoro e l'utente non lo vedrebbe quando aprirà la cartella di lavoro. Tuttavia, possono facilmente mostrare il foglio di lavoro facendo clic con il pulsante destro del mouse su qualsiasi scheda del foglio.

Ma cosa succede se non vuoi che siano in grado di mostrare i fogli di lavoro.

Puoi farlo usando VBA.

Il codice seguente nasconderebbe tutti i fogli di lavoro nella cartella di lavoro (tranne il foglio attivo), in modo tale che non sia possibile visualizzarlo facendo clic con il pulsante destro del mouse sul nome del foglio.

Sub HideAllExcetActiveSheet() Dim Ws come foglio di lavoro per ogni Ws in ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden Next Ws End Sub

Nel codice sopra, la proprietà Ws.Visible è cambiata in xlSheetVeryHidden.

• Quando la proprietà Visible è impostata su xlSheetVisible, il foglio è visibile nell'area del foglio di lavoro (come schede del foglio di lavoro).
• Quando la proprietà Visible è impostata su xlSheetHidden, il foglio è nascosto ma l'utente può scoprirlo facendo clic con il pulsante destro del mouse su qualsiasi scheda del foglio.
• Quando la proprietà Visible è impostata su xlSheetVeryHidden, il foglio è nascosto e non può essere visualizzato dall'area del foglio di lavoro. È necessario utilizzare un codice VBA o la finestra delle proprietà per visualizzarlo.

Se vuoi semplicemente nascondere i fogli, che possono essere visualizzati facilmente, usa il codice seguente:

Sub HideAllExceptActiveSheet() Dim Ws come foglio di lavoro per ogni Ws in ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetHidden Next Ws End Sub

Il codice seguente mostrerebbe tutti i fogli di lavoro (sia nascosti che molto nascosti).

Sub UnhideAllWoksheets() Dim Ws As Worksheet For Each Ws In ThisWorkbooks.Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

Articolo correlato: Scopri tutti i fogli in Excel (in una volta sola)

Nascondi i fogli in base al testo in essi contenuto

Supponiamo di avere più fogli con il nome di diversi reparti o anni e di voler nascondere tutti i fogli tranne quelli che contengono l'anno 2021-2022.

Puoi farlo usando una funzione VBA INSTR.

Il codice seguente nasconderebbe tutti i fogli tranne quelli con il testo2021-2022 al suo interno.

Sub HideWithMatchingText() Dim Ws come foglio di lavoro per ogni W nei fogli di lavoro If InStr(1, Ws.Name, "2018", vbBinaryCompare) = 0 Then Ws.Visible = xlSheetHidden End If Next Ws End Sub

Nel codice sopra, la funzione INSTR restituisce la posizione del carattere in cui trova la stringa corrispondente. Se non trova la stringa corrispondente, restituisce 0.

Il codice sopra controlla se il nome contiene il testo 2021-2022. Se lo fa, non succede nulla, altrimenti il ​​foglio di lavoro è nascosto.

Puoi fare un ulteriore passo avanti inserendo il testo in una cella e utilizzando quella cella nel codice. Ciò ti consentirà di avere un valore nella cella e quindi quando esegui la macro, tutti i fogli, tranne quello con il testo corrispondente, rimarranno visibili (insieme ai fogli in cui stai inserendo il valore nel cellula).

Ordinamento dei fogli di lavoro in ordine alfabetico

Usando VBA, puoi ordinare rapidamente i fogli di lavoro in base ai loro nomi.

Ad esempio, se si dispone di una cartella di lavoro con fogli per reparti o anni diversi, è possibile utilizzare il codice seguente per ordinare rapidamente questi fogli in ordine crescente.

Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Sposta prima:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

Nota che questo codice funziona bene con i nomi di testo e nella maggior parte dei casi anche con anni e numeri. Ma può darti risultati sbagliati nel caso tu abbia i nomi dei fogli come 1,2,11. Ordina e ti dà la sequenza 1, 11, 2. Questo perché esegue il confronto come testo e considera 2 più grande di 11.

Proteggi/sproteggi tutti i fogli in una volta sola

Se hai molti fogli di lavoro in una cartella di lavoro e desideri proteggere tutti i fogli, puoi utilizzare il codice VBA di seguito.

Consente di specificare la password all'interno del codice. Avrai bisogno di questa password per rimuovere la protezione del foglio di lavoro.

Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'sostituisci Test123 con la password che desideri Per ogni ws In Worksheets ws.Protect password:=password Next ws End Sub

Il seguente codice rimuoverà la protezione di tutti i fogli in una volta sola.

Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'sostituisci Test123 con la password che hai usato durante la protezione For Each ws In Worksheets ws.Unprotect password:=password Next ws End Sub

Creazione di un sommario di tutti i fogli di lavoro (con collegamenti ipertestuali)

Se hai una serie di fogli di lavoro nella cartella di lavoro e desideri inserire rapidamente un foglio di riepilogo che abbia i collegamenti a tutti i fogli, puoi utilizzare il codice seguente.

Sub AddIndexSheet() Worksheets.Add ActiveSheet.Name = "Index" For i = 2 To Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor:=Cells(i - 1, 1), _ Address:="", SubAddress:=Worksheets (i).Nome & "!A1", _ TextToDisplay:=Fogli di lavoro(i).Nome Avanti i End Sub

Il codice sopra inserisce un nuovo foglio di lavoro e lo nomina Index.

Quindi scorre tutti i fogli di lavoro e crea un collegamento ipertestuale per tutti i fogli di lavoro nel foglio Indice.

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:

1. Vai alla scheda Sviluppatore.
2. Fare clic sull'opzione Visual Basic. Questo aprirà l'editor VB nel backend.
3. 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.
4. Vai su Inserisci e fai clic su Modulo. Questo inserirà un oggetto modulo per la tua cartella di lavoro.
5. Copia e incolla il codice nella finestra del modulo.

Potrebbero piacerti anche i seguenti tutorial VBA di Excel:

• Lavorare con le cartelle di lavoro utilizzando VBA.
• Utilizzo delle istruzioni IF Then Else in VBA.
• Per il ciclo successivo in VBA.
• Creazione di una funzione definita dall'utente in Excel.
• Come registrare una macro in Excel.
• Come eseguire una macro in Excel.
• Eventi VBA di Excel: una guida facile (e completa).
• Come creare un componente aggiuntivo in Excel.
• Come salvare e riutilizzare la macro utilizzando la cartella di lavoro macro personale di Excel.