Lavorare con i Fogli di lavoro. - dal 04/09/04 pagina vista: volte

Ovvero : come eseguire una serie di operazioni che facciamo su un foglio, anche su tutti gli altri fogli della cartella.

Mi sembrava che in più articoli e/o risposte avessi spiegato come lavorare con tutti i fogli di lavoro di una cartella di excel (WorkBook), ma continuano richieste su questo argomento, e quindi propongo un'articolo più specifico.

Premesso che la guida in linea del vba è piuttosto chiara al riguardo, proviamo a puntualizzare tre situazioni in cui ci potremo trovare:

  • Ripetere una istruzione oppure un  ciclo di istruzioni su ogni foglio della cartella

  • Ripetere una istruzione oppure un ciclo di istruzioni su alcuni fogli della cartella

Cominciamo col ricordare che ogni foglio di un WorkBook fa parte di un "Insieme", l' "Insieme Fogli" (WorkSheets), e come su tutti gli "insiemi" in Excel, possiamo impostare dei Cicli For per agire su ogni singolo componente dell'"insieme". Proprio come facciamo (e il sito è pieno di esempi) quando si devono scorrere celle in una determinata zona o sull'intero foglio. Comunque, tanto per chiarire: Worksheet (senza la "s" del plurale inglese) è il Foglio di lavoro, mentre Worksheets rappresenta tutti i fogli di lavoro della cartella.  L'istruzione base che serve a scorrere tutti i fogli, sarà molto semplice, questa:

  • For Each ws In Worksheets
    ...istruzioni di cosa fare
    Next ws

dove ws è la variabile che identifica un singolo foglio, e l'istruzione dice: Per Ogni foglio nell'insieme Fogli, esegui le istruzioni di cosa fare, e passa al foglio successivo con Next ws. E' chiaro che verrano "scorsi" tutti i fogli fino all'ultimo, e in ogni foglio verranno eseguite le istruzioni. E' altresì evidente che questo sarà per forza di cose, il ciclo esterno, il primo asd essere eseguito, rispetto ad altri cicli, che saranno interni a questo, e che potranno servire per eseguire determinate istruzioni all'interno di ogni foglio. Facciamo un esempio, utilizzando una routine già presente in questa sezione, e che serve a trovare una parola, cercandola in ogni cella di un foglio. Modifichiamo questa routine in modo da eseguire la ricerca della parola anche sugli altri fogli della cartella. Commento solo le modifiche: Troviamo infatti l'istruzione vista sopra (For Each ws....) e in aggiunta l'istruzione num = Worksheets.Count che serve a contare quanti fogli sono presenti nella cartella. Con questo numero, faremo un controllo, tramite un contatore: la variabile att, che si incrementa di una unità ad ogni foglio scorso, per sapere se saremo all'ultimo foglio, ed avviseremo a fine ciclo con il messaggio che la ricerca è terminata. In grassetto le modifiche:

Sub cercaparolasufogli()
Dim CL As Object
num = Worksheets.Count
Dimmi = InputBox("Cosa Diavolo cerchi?")
If Dimmi = "" Then Exit Sub
For Each ws In Worksheets
ws.Activate

'------------------------blocco delle istruzioni che verranno eseguite su ogni foglio
Set Zona = ws.UsedRange
For Each CL In Zona
If CL.Value Like "*" & Dimmi & "*" Then
CL.Select
Dove = CL.Address(rowabsolute:=False, columnabsolute:=False)
Dim idomanda As Integer
idomanda = MsgBox("Trovato " & Dimmi & " in " & Dove & ". Vuoi Cercare ancora?", vbYesNo)
If idomanda = vbNo Then
Exit Sub
End If
End If
Next

'--------------------fine del blocco delle istruzioni
att = att + 1
 'si incrementa un numeratore di una unità ad ogni foglio scorso
Next ws 
      'e si passa al foglio successivo
If att = num Then 
'se il contatore (att) sarà uguale al numero dei fogli (num) , si avvisa con il messaggio
MsgBox "Ricerca Terminata"
End If

End Sub

Potremo quindi compiere qualsiasi operazione all'interno di un ciclo che scorra tutti i fogli, come impostare valori in determinate celle, oppure impostare colori all'interno di celle, o ancora assegnare formule, colorare i fonts, impostare aree di stampa e mandarle in stampa, ecc. ecc.. E' evidente che qualsiasi istruzione faremo eseguire, sarà relativa al foglio in quel momento scorso, e reso attivo tramite l'istruzione ws.Activate.

Se invece vogliamo escludere uno o più fogli dall'esecuzione di una serie di istruzioni, dovremo comunque istruire un ciclo che scorra tutti i fogli, tranne quelli non interessati. Imposteremo quindi delle condizioni che idendificheranno i fogli da escludere. Avremo due possibilità:

  • identificare un foglio (da escludere) dal suo nome (Foglio1, Foglio5, ecc, oppure Fatture, Archivio, ecc. dipenderà ovviamente dal nome che avremmo assegnato ai fogli). In questo esempio eseguiremo le istruzioni su tutti i fogli tranne che sul Foglio1 (infatti diciamo: Se il nome del Foglio è diverso da foglio1, allora esegui le istruzioni:

  • For Each ws In WorkSheets
    If ws.Name <> "Foglio1" Then
    ...istruzioni di cosa fare
    End If
    Next

     

  • identificare un foglio (da escludere) dal numero che il foglio rappresenta nell'insieme Fogli (cioè dal suo numero indice). In questo caso istruiremo un ciclo For Next basato sul numero dei fogli della cartella, reperito con num=Sheets.Count. Poi istruiremo una condizione che valuti se il numero del foglio "scorso" corrisponde al numero del foglio che vogliamo escludere, nel caso corrisponda, salteremo il foglio (e le istruzioni) passando con Next al foglio successivo.In questo esempio vogliamo escludere il secondo foglio (il n. 2)

  • Dim F As Long
    num = Sheets.Count
    For F = 1 To num
    If F = 2 Then
     'si controlla se il contatore F è uguale a 2, se uguale si salta alla riga 10: passando al successivo
    GoTo 10
    Else
     'altrimenti si attiva il foglio(F) e si eseguono le istruzioni
    Sheets(F).Activate
    ...istruzioni di cosa fare
    End If
    10:
    Next

Se poi si volessero saltare più fogli si potrà impostare con l'operatore Or altre condizioni, ad esempio per il foglio 2 e il 5:

  • If F = 2 Or F = 5 Then  ecc. ecc.

Vediamo ora alcune routine veloci:

  • Reperire il nome del Foglio attivo ed assegnarlo ad una cella:

  • [A1].Value = Activesheet.Name

  • Rinominare il Foglio attivo assegnandogli un nome contenuto in una cella:

  • Activesheet.Name = [B5].Value

  • Scrivere nelle celle del Foglio attivo, i nomi dei fogli della cartella di lavoro attiva:

  • For i = 1 To Sheets.Count
    ActiveSheet.Cells(i, 1).Value = Sheets(i).Name
    Next i

  • Aggiungere un foglio di lavoro ed assegnarli un nome:

  • Set NuovoFoglio = Worksheets.Add
    NuovoFoglio.Name = "Quel che ti pare"

  • Oppure aggiungere un foglio e assegnarli come nome il contenuto di una cella di un determinato foglio:

  • Set NuovoFoglio = Worksheets.Add
    NuovoFoglio.Name = Worksheets("pippo").Range("B5").Value

Buon lavoro.

prelevato sul sito www.ennius.altervista.org