Lavorare con fogli diversi

Ultima modifica: 30-05-2016

 

In linea di massima le operazioni che si possono compiere sono le stesse di quelle che siamo abituati a compiere sul foglio attivo: selezioni ed individuazione di intervalli, lettura e scrittura di valori, ecc.

C'è solo bisogno di allargare le nostre vedute. Basta indicare al codice che, invece di lavorare sulle celle del foglio attivo, abbiamo intenzione di lavorare su celle di altri fogli.

 

Lavorando su oggetti diversi quali possono essere due differenti fogli per trattare (leggere o scrivere) valori che si trovano in un foglio o in un altro in linea di massima occorre specificare il foglio sul quale il valore è residente o sul quale vogliamo scrivere il valore. Non è sufficiente un generico:

Range("D5") = Range("B5") ma a volte è necessario specificare sia il foglio di destinazione che il foglio di origine come nell'esempio:

Sheets("Foglio1").Range("A9") = Sheets("Foglio3").Range("A1").

Per chiarirci le idee io penso che nell'uso del VBA dobbiamo acquisire un po' una mentalità militare per comprendere come in questo ambiente regna la gerarchia.

Questa gerarchia di oggetti viene a formare come una piramide alla cui cima sta l'oggetto da cui hanno origine tutti gli altri oggetti intermedi che a loro volta contengono altri oggetti via via più piccoli e più numerosi.

E' così che, partendo dall'oggetto Application, che rappresenta il padre dell'intera applicazione Excel, arriviamo, passando attraverso oggetti intermedi (Workbooks, Sheets, ecc), verso gli oggetti di ordine inferiore (Range, Cells per scendere fino a Font, Interior, ecc).

Nelle nostre istruzioni occorre solo porre attenzione a fare i giusti riferimenti ai giusti oggetti. Tanto per rimanere nel tema diciamo subito che per lavorare con celle di fogli diversi, le nostre azioni possono essere eseguite

Queste possono essere le situazioni in cui possiamo venire a trovarci:

Fatte queste premesse possiamo passare ad esaminare le istruzioni che di volta in volta occorre utilizzare.

Per copiare i valori da una cella di un foglio ad un'altra cella di un altro foglio:

Per selezionare una cella di un altro foglio:

Per compiere questa azione saremmo tentati a scrivere una cosa del genere:

Sheets("Foglio2").Range("A1").Select

Purtroppo l'istruzione genera un errore se il Foglio2 non è già attivo (ma in questo caso il riferimento al foglio sarebbe inutile). Il motivo dell'errore sollevato da questa istruzione è che non si può selezionare una cella in un foglio che non sia quello attivo e che l'istruzione non riesce ad eseguire contemporaneamente le due azioni: attivare il foglio e selezionare la cella indicata.

Vediamo subito un esempio destinato a fallire:

Sub seleziona1()
Dim N As Integer
For N = 1 To Worksheets.Count
Sheets(N).Range("C10").Select ' questa istruzione creerà un errore se il foglio non è quello attivo
If ActiveCell = "" Then
MsgBox "la cella è vuota"
End If
Next
End Sub

A questa istruzione possiamo (dobbiamo) sostituire una delle seguenti istruzioni:

Vediamo gli esempi corretti per ciclare tra i fogli e selezionare le celle usando le due sintassi:

Sub seleziona_con_Goto()
Dim N As Integer
For N = 1 To Worksheets.Count
'Sheets(N).Range("C10").Select ' questa è stata sostituita con la seguente riga
Application.Goto Sheets(N).Range("C10")
If ActiveCell = "" Then
MsgBox "la cella è vuota"
End If
Next
End Sub

oppure l'altra:

Sub seleziona_con_doppia_azione()
Dim N As Integer
For N = 1 To Worksheets.Count
'Sheets(N).Range("C10").Select ' questa è stata sostituita con la seguente riga
Sheets(N).Activate
Sheets(N).Range("C10").Select
If ActiveCell = "" Then
MsgBox "la cella è vuota"
End If
Next
End Sub

 

Lavorare a distanza e senza alcuna selezione

In genere è inutile effettuare selezioni. Sarà sufficiente lavorare a distanza, cioè lavorare su ogni foglio senza andarci fisicamente, e potremo cambiare la routine come mostrato qui di seguito:

Es. 1a Modificare una cella per ogni foglio senza effettuare alcuna selezione

Sub senza_Selezione()
Dim N As Integer
For N = 1 To Worksheets.Count
If Sheets(N).Range("C10") = "" Then
MsgBox "la cella è vuota"
End If
Next
End Sub

L'uso dell'istruzione With

In tutti i casi in cui occorre compiere molte azioni su un singolo oggetto, per semplificare le istruzioni, è possibile usare l'istruzione With. L'istruzione With può anche essere nidificata, ma l'istruzione With più interna, pur riferendosi ad un oggetto diverso da quello più esterno, dovrà comunque riferirsi ad un oggetto gerarchicamente inferiore a quello a cui il primo With si riferisce.

With Sheets("Foglio1")
With .Range("K1")
With .Font

In questa serie di With ci riferiamo all'oggetto Font dell'oggetto Range dell'oggetto Sheets rispettandone rigorosamente la scala gerarchica.

In una serie di With nidificati non ci si potrà riferire ad un altro oggetto gerarchicamente uguale o superiore a quello puntato dal With più esterno. Se col primo With ci riferiamo ad un determinato Foglio, col secondo With dovremo riferirci ad una cella di quel foglio e non ad un altro foglio o una cella di un altro foglio. Se col secondo With vogliamo riferirci ad un'altra cella dello stesso foglio, dobbiamo prima chiudere il precedente riferimento alla cella e riaprirne un altro che si riferisca ad un'altra cella.

With Sheets("Foglio1")
With .Range("K1")
...........
End With
With .Range("K5")
...........
End With
End With

Gli oggetti dipendenti dall'oggetto puntato da With saranno contraddistinti da un punto (.) usato come prefisso:

Se il nostro lavoro dovrà svolgersi su due differenti fogli magari diversi da quello attivo, anche se è lecito pensare di usare l'istruzione With per semplificare le nostre azioni, è tuttavia necessario decidere quale dei fogli e relative celle deve essere indicato da With e quale trattare senza With.

Se scriviamo: With Sheets("Foglio1") con un successivo With nidificato nel primo dovremo riferirci ad una cella dello stesso Foglio1: With .Range("K1") oppure: With .Range("K1").Font ma non possiamo riferirci a celle di un altro foglio. Vediamo un esempio pratico:

Di seguito propongo alcuni esempi per chiarire i concetti sin qui esposti.

 

Questo esempio mostra una ulteriore espansione dell'Esempio visto sopra

 

Sub ControllaSeVuota
For N = 1 To Worksheets.Count
With Sheets(N)
If .Range("C10") = "" Then
MsgBox "la cella è vuota"
End If
End With
Next
End Sub

Questo che segue è simile al precedente

With Sheets("Foglio1")
If .Range("C10") = "" Then
.Range("C10") = Sheets("Vendite").Range("K1")
End If
End With

Con questo esempio spazzoliamo tutti i fogli di una cartella per controllare se la cella C10 è vuota

Sub ControllaSeVuota()
Dim N
For N = 1 To Worksheets.Count
With Sheets(N)
If .Range("C10") = "" Then
MsgBox "la cella è vuota"
End If
End With
Next
End Sub

Nel seguente esempio il foglio attivo è uno diverso dal Foglio1 e senza spostarci da questo eseguiamo alcune azioni sul Foglio1. Uso tre With nidificati ognuno dei quali si riferisce a vari oggetti gerarchicamente via via più bassi:

Sheets ---> Range ---> Font
Sheets ---> Range ---> Interior

Per entrare nell'Interior dell'oggetto Range, dobbiamo prima uscire da Font dello stesso oggetto Range.

Nello scrivere la routine dobbiamo porre la massima attenzione al punto ( . ) da scrivere prima delle proprietà dell'oggetto a cui ci riferiamo:

Sub With_Nidificati()
With Sheets("Foglio1") ' qui puntiamo al foglio1
MsgBox .Name ' ne leggiamo il nome
.Range("K2") = .Name ' e lo scriviamo nella cella K2
.Tab.ColorIndex = 3 ' quindi coloriamo la relativa scheda (Tab)
With .Range("K2") ' ci spostiamo ora sulla cella K2 per formattarla
With .Font
.Name = "Arial"
.FontStyle = "Grassetto"
.Size = 11
.Strikethrough = True
.ColorIndex = 3
End With
With .Interior
.ColorIndex = 40
.PatternColorIndex = xlAutomatic
End With
End With
.Tab.ColorIndex = xlColorIndexNone ' qui togliamo il colore alla scheda del foglio 2
.Range("A2") = .Range("K2").Value ' e copiamo in A2 quel che avevamo scritto in K2
End With
End Sub

Con questo codice siamo in grado di lavorare sul Foglio1 anche se ci troviamo su un altro foglio.

Volendo fare a meno delle istruzioni With il nostro codice si leggerebbe pressappoco così:

Sheets("Foglio1").Range("K2").Font.Name = "Arial"
Sheets("Foglio1").Range("K2").Font.FontStyle = "Grassetto"
Sheets("Foglio1").Range("K2").Font.Size = 11
Sheets("Foglio1").Range("K2").Interior.ColorIndex = 40 ecc

 

 

Estrarre dati univoci da una tabella

In questo esempio, da una tabella globale, vogliamo estrarre, di volta in volta, i vari elementi e deporli, senza ripetizioni, in un altro foglio e segnalarne tutte le occorrenze.

Cod Art Operatore Prodotto Confez. Pezzi venduti
35031 Andrea Tonno scatole 181
84040 Massimo Coca Cola bottiglie 53
67030 Maurizio Maccheroni confezione 115
48012 Silvano Maionese vasetti 20
10060 Alessio Tonno scatole 107
20070 Giovanni Maionese vasetti 127
53040 Giuseppe Maccheroni confezione 438
61040 Oreste Mostarda vasetti 425
22050 Massimo Alici scatole 402
53021 Alessio Maionese vasetti 182
09071 Giovanni Pane confezione 130
65020 Oreste Maccheroni confezione 220
15024 Andrea Tonno scatole 355
24021 Silvano Pane confezione 170
04010 Silvano Coca Cola bottiglie 128
07020 Maurizio Alici scatole 119
21049 Giuseppe Mostarda vasetti 95
20081 Silvano Maccheroni confezione 225
23030 Massimo Alici scatole 15

ottenendo, a seconda della scelta effettuata, degli elenchi così composti:

 

Operatore Occorrenze        Prodotto. Occorrenze        Confez Occorrenze
Andrea
2
  Tonno
3
  scatole
6
Massimo
3
  Coca Cola
2
  bottiglie
2
Maurizio
2
  Maccheroni
4
  confezione
6
Silvano
4
  Maionese
3
  vasetti
5
Alessio
2
  Mostarda
2
   
Giovanni
2
  Alici
3
   
Giuseppe
2
  Pane
2
   
Oreste
2
   
   

Con la routine che verrà presentata tra poco si potrà agire su due fogli da un qualsiasi foglio della cartella di lavoro. Ossia:

a prescindere da quale sia il foglio attivo preleveremo i dati dal foglio denominato "Vendite" e depositati, dopo l'elaborazione, nel foglio denominato "Riepilogo".

Unico vincolo è quello di scrivere nella cella "I1" la colonna della tabella da esaminare:

es: 1 per la colonna 1, 2 per la colonna 2, ecc.

Le azioni che compieremo nel codice saranno queste:

determiniamo l'intervallo di origine con le ormai note istruzioni:

With Sheets("Vendite").Range("A1").CurrentRegion
Righe = .Rows.Count - 1
Col = .Columns.Count
Set Intervallo = .Offset(1, 0).Resize(Righe, Col)
End With

dopo di questo si legge il contenuto della cella "I1" e se ne verifica il valore:

Colonna = Worksheets("Vendite").Range("I1")
If Colonna = "" Then
MsgBox "scrivere il numero di colonne nelle cella I1 del foglio Vendite"
Exit Sub
End If
If Colonna < 1 Or Colonna > Col Then
MsgBox "Nella cella I1 del foglio Vendite scrivere solo valori numerici tra 1 e " & Col
Exit Sub
End If

dopo di questo si passa a lavorare nell'intervallo precedentemente determinato per creare un elenco univoco di tutti gli elementi della colonna in esame usando una Collection:

On Error Resume Next
For R = 1 To Righe
Unici.Add Intervallo(R, Colonna).Value, CStr(Intervallo(R, Colonna).Value)
Next
On Error GoTo 0

quindi si conclude trasferendo i dati trovati nel foglio di destinazione.

Con With Sheets("Riepilogo") si punterà al foglio denominato "Riepilogo"

dopo aver contato, nel foglio di origine le occorrenze di ciascun elemento contenuto nella collection si aggiorna la nuova tabella fel foglio di destinazione denominato "Riepilogo" (notate come ci si riferisce alle celle del foglio "Riepilogo" anteponendo un punto ( . ) a queste celle.

Tot = Unici.Count: R2 = 1
With Sheets("Riepilogo")
.Range("A1").CurrentRegion.ClearContents
.Range("A1") = Sheets("Vendite").Cells(1, Colonna)
For R = 1 To Tot
Conta = 0
Valore = Unici(R)
For R1 = 1 To Righe
If Intervallo(R1, Colonna) = Valore Then
Conta = Conta + 1
End If
Next
R2 = R2 + 1
.Cells(R2, 1) = Valore
.Cells(R2, 2) = Conta
Next
End With

Questo che segue è il codice completo:

Sub CopiaInAltroFoglio()
Dim Intervallo As Range
Dim Unici As New Collection
Dim Righe, R, R1, R2, Col, Colonna
Dim Tot, Conta, Valore
' qui viene creato l'intervallo che contiene la tabella di origine
With Sheets("Vendite").Range("A1").CurrentRegion
Righe = .Rows.Count - 1
Col = .Columns.Count
Set Intervallo = .Offset(1, 0).Resize(Righe, Col)
End With
' ora viene controllato il numero di colonna che dovrebbe essere nella cella "I1" del foglio "Vendite"
Colonna = Worksheets("Vendite").Range("I1")
If Colonna = "" Then
MsgBox "scrivere il numero di colonne nelle cella I1 del foglio Vendite"
Exit Sub
End If
If Colonna < 1 Or Colonna > Col Then
MsgBox "Nella cella I1 del foglio Vendite scrivere solo valori numerici tra 1 e " & Col
Exit Sub
End If
' ora viene creata una nuova Collection
On Error Resume Next
For R = 1 To Righe
Unici.Add Intervallo(R, Colonna).Value, CStr(Intervallo(R, Colonna).Value)
Next
On Error GoTo 0
If Unici Is Nothing Then
MsgBox "collezione non creata"
Exit Sub
End If
' finalmente si passa a processare la colonna dell'intervallo di origine
' ed infine gli elaborati vengono copiati nel foglio di destinazione

Tot = Unici.Count: R2 = 1
With Sheets("Riepilogo")
.Range("A1").CurrentRegion.ClearContents
.Range("A1") = Sheets("Vendite").Cells(1, Colonna)
For R = 1 To Tot
Conta = 0
Valore = Unici(R)
For R1 = 1 To Righe
If Intervallo(R1, Colonna) = Valore Then
Conta = Conta + 1
End If
Next
R2 = R2 + 1
.Cells(R2, 1) = Valore
.Cells(R2, 2) = Conta
Next
End With
End Sub

Se qualcosa ancora non è chiaro, penso che potremmo tornare sull'argomento.