Gestione dei fogli

Ultima modifica: 27-05-2016

Questa volta vogliamo vedere come gestire i fogli di lavoro di Excel.

Ogni nuova cartella di Excel mostra per default tre fogli. Ogni foglio di lavoro è rappresentato nella sezione inferiore dell'interfaccia con una linguetta che ne mostra il nome. La prima linguetta è Foglio1. La seconda è Foglio2. L'ultima è Foglio3.

I vari fogli di lavoro di cartella sono raccolti in una collezione denominata Worksheets.

Nella maggior parte dei casi, è possibile usare questa collezione ed ogni foglio di lavoro può essere individuato tramite un indice sia numerico che letterario. L'indice numerico comincia a 1 per cui si ha che il primo foglio di lavoro ha un indice di 1, il secondo foglio di lavoro ha un indice di 2 e così via. L'indice letterario è invece distinto da un nome alfanumerico univoco che per default ha il valore "Foglio1", Foglio2", "Foglio3", ecc.

Ne consegue che in VBA possiamo riferirci ai singoli fogli chiamandoli Sheets(1), Sheets(2), ecc oppure Sheets("Foglio1"), Sheets("Foglio2"), ecc.

 

Conteggio dei fogli

La Collection che contiene i nomi di tutti i Fogli di lavoro di una cartella, come tutte le Collection espone la Proprietà Count. Così, in VBA, possiamo conoscere il numero dei fogli con cui stiamo lavorando:

Sub ContaFogli()
MsgBox Sheets.Count & " fogli in questa cartella"
End Sub

 

Escursione tra i Fogli, ossia lettura dei nomi e degli indici dei fogli

In questo piccolo test vogliamo visualizzare il nome dei fogli di una cartella ed il loro indice numerico.

Sub IndiceFogli()
Dim A
Range("A1").CurrentRegion.ClearContents
With Range("A1")
     For A = 1 To Sheets.Count
          .Item(A, 1) = "Nome del foglio: " & Sheets(A).Name & " - indice del foglio: " & Sheets(A).Index
     Next
End With
End Sub

Da questa routine ci aspetteremmo un risultato del genere:

Nome del foglio: Foglio1 - indice del foglio: 1

Nome del foglio: Foglio2 - indice del foglio: 2

Nome del foglio: Foglio3 - indice del foglio: 3

Questo è vero se i fogli sono disposti nell'ordine: Foglio1, Foglio2, Foglio3, ...., ma se i fogli fossero disposti diversamente: Foglio2, Foglio3, Foglio1, ...., avremmo questo altro output:

Nome del foglio: Foglio2 - indice del foglio: 1

Nome del foglio: Foglio3 - indice del foglio: 2

Nome del foglio: Foglio1 - indice del foglio: 3


Per quanto riguarda gli indici numerici dei fogli c'è da dire che questi indici seguono la disposizione che i fogli hanno nella cartella. Infatti il test eseguito ci mostra che se la sequenza dei fogli è Foglio1, Foglio2, Foglio3, ...., la loro numerazione é 1, 2, 3, ..... , ma se la sequenza dei fogli è diversa, gli indici numerici vengono stravolti.

Questo fatto dovrebbe indurci a trarre delle conclusioni: Non sempre quando chiamiamo un certo Sheets(2) ci risponde il secondo foglio. Se siamo consapevoli che i fogli sono stati spostati o se solo abbiamo un dubbio che possano essere spostati, ci conviene chiamarli col loro nome alfanumerico: Sheets("Foglio1") o Sheets("pippo"), ecc.

Perciò dobbiamo stare attenti quando eseguiamo delle operazioni che coinvolgono il primo, secondo o altro foglio in maniera sequenziale secondo la nostra logica. Potremmo avere delle spiaceloli sorprese!

 

Aggiungere nuovi fogli

Questa può essere una breve routine che aggiunge un foglio nella nostra cartella di lavoro:

Sub CreaFoglio()
Dim Attuale, NomeFoglio
Dim WS As Worksheet
Attuale = ActiveSheet.Name
NomeFoglio = [F1]     'il nome del foglio da aggiungere è indicato nella cella F1
For Each WS In Worksheets
     If WS.Name = NomeFoglio Then
          MsgBox "Il foglio " & NomeFoglio & " già esiste"
          Exit Sub
     End If
Next
Sheets.Add
ActiveSheet.Name = NomeFoglio

Sheets(NomeFoglio).Move After:=Sheets(Worksheets.Count)
Sheets(Attuale).Select     'si torna al foglio di partenza (se si vuole)
End Sub

Il nome del foglio da aggiungere viene letto dalla cella F1. Ma prima di aggiungere un nuovo foglio è necessario controllare se il foglio che vogliamo aggiungere esiste già nella cartella. Infatti se si tenta di aggiungere un foglio a cui vogliamo assegnare lo stesso nome di un altro foglio, viene generato un errore di run-time con l'avvertimento: "Impossibile rinominare un foglio con lo stesso nome di un altro foglio...."

Di seguito si esegue un piccolo controllo per evitare l'insorgere di questo errore:

For Each WS In Worksheets
If WS.Name = NomeFoglio Then
MsgBox "Il foglio " & NomeFoglio & " già esiste"
Exit Sub
End If
Next

Eliminare un foglio di lavoro

Altrettanto semplice è l'eliminazione di un foglio.

Sub EliminaUnFoglio()
Dim NomeFoglio, Risposta
Dim FL As Boolean
Dim WS As Worksheet
NomeFoglio = [F1]
FL = False
For Each WS In Worksheets
     If LCase(WS.Name) = LCase(NomeFoglio) Then
          FL = True
          Risposta = MsgBox("Sicuro di voler eliminare il foglio " & NomeFoglio & "?", vbYesNo, "ATTENZIONE")
          If Risposta = vbNo Then Exit Sub
          Application.DisplayAlerts = False
          Sheets(NomeFoglio).Delete
          Application.DisplayAlerts = True
          MsgBox "il foglio " & NomeFoglio & " è stao eliminato"
     End If
Next
If FL = False Then
     MsgBox "il foglio " & NomeFoglio & " non esiste" & vbCr _
          & "Impossibile compiere l'operazione"
End If
End Sub

Il nome del foglio da eliminare viene letto dalla cella F1.

Anche in questo caso occorre eseguire un controllo per verificare se il foglio esiste. In caso contrario viene sollevato un errore di run-time che ci ammonisce: "indice non incluso nell'intervallo". In simile azioni demolitive non è male nemmeno dare all'utente anche la possibilità di ripensarci.

Nel ciclo For Each WS In Worksheets .... Next si controlla l'esistenza del foglio e, se questo esiste, si procede con la richiesta di conferma ed alla sua distruzione.

Ma andiamo ad esaminare il codice il codice che è compreso nel ciclo For ... Next

For Each WS In Worksheets
If LCase(WS.Name) = LCase(NomeFoglio) Then
FL = True
Risposta = MsgBox("Sicuro di voler eliminare il foglio " & NomeFoglio & "?", vbYesNo, "ATTENZIONE")
If Risposta = vbNo Then Exit Sub
Application.DisplayAlerts = False
Sheets(NomeFoglio).Delete
Application.DisplayAlerts = True
MsgBox "il foglio " & NomeFoglio & " è stao eliminato"
End If
Next

Appena aperto il ciclo

La variabile FL serve a mandare un messaggio di foglio non trovato nel caso che il foglio da eliminare non esiste

 

Ordinare alfabeticamente i fogli

Può capitare che in una cartella abbiamo molti fogli di lavoro. In questo caso potremmo avere difficoltà nello spostarci tra un foglio ed un altro. In questo caso abbiamo la possibilità di disporre i fogli in ordine alfabetico.

Sub Ordina_Fogli()
Dim iFogli() As String
Dim Attuale
Dim N, A, B, C
Dim CL As Worksheet
N = Application.Worksheets.Count
ReDim iFogli(1 To N)
A = 1
'raccolta dei nomi dei fogli in una matrice
For Each CL In Worksheets
     iFogli(A) = CL.Name
     A = A + 1
Next
'ordinamento della matrice
For A = LBound(iFogli) To UBound(iFogli) - 1
     For B = A + 1 To UBound(iFogli)
          If iFogli(A) < iFogli(B) Then
               C = iFogli(A)
               iFogli(A) = iFogli(B)
               iFogli(B) = C
          End If
     Next
Next
Attuale = ActiveSheet.Name
Application.ScreenUpdating = False
'spostamento dei fogli ordinati
For A = LBound(iFogli) To UBound(iFogli)
     If iFogli(A) <> "" Then
          Worksheets(iFogli(A)).Move before:=Worksheets(1)
     End If
Next
Sheets(Attuale).Select
Application.ScreenUpdating = True
End Sub

La routine è semplice.

memorizziamo i nomi dei fogli in una matrice dinamica (dinamica perchè non se ne conosce il numero esatto)

ordiniamo i nomi dei fogli

ordinati i nomi dei fogli memorizzati in matrice si possono spostare i fogli seguendo l'ordine che hanno nella matrice

 

Salvare un foglio come file

Come ultima cosa potremmo desiderare di salvare un solo foglio della cartella attiva. Questa una semplice routine.

Sub SalvaFoglio()
Dim Percorso As String, NomeFoglio As String
Dim WS As Worksheet
Dim FL As Boolean
Percorso = ActiveWorkbook.Path & "\"
NomeFoglio = [F1]
FL = False
For Each WS In Worksheets
     If LCase(WS.Name) = LCase(NomeFoglio) Then
          FL = True
          Application.ScreenUpdating = False
          Application.DisplayAlerts = False
          Sheets(NomeFoglio).Copy
          Sheets(NomeFoglio).SaveAs Percorso & NomeFoglio & ".xls"
          ActiveWorkbook.Close
          Application.DisplayAlerts = True
          Application.ScreenUpdating = True
          Exit For
     End If
Next


If FL = True Then
MsgBox "Il foglio è stato salvato come " & NomeFoglio & ".xls" & vbCrLf _
& "nella cartella " & Percorso
Else
MsgBox "L'operazione non può essere portata a termime" & vbCrLf _
& "perchè il foglio da salvare non esiste"
End If
End Sub

Anche in questo caso è necessario impostare dei controlli per non provocare errori se, nel tentativo di salvare un foglio, questo non esiste. Le istruzioni veramente necessarie per questa operazione si trovano nel ciclo For Each .... Next.

Le ultime righe del codice non sono necessarie ma servono solo a far visualizzare i messaggi di avviso.