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.