Creazione di elenchi univoci con Collection

Ultima modifica: 30-05-2016

Il problema

Il problema che andremo ad affrontare questa volta è la raccolta di dati univoci da una tabella dove i dati sono ripetuti più volte. Per far questo ci avvaliamo dell'aiuto dell'oggetto Collection che il buon VBA, ma anche il VB standard, ci mettono a disposizione anche se avessimo potuto usare altre metodologie.

Per un approfondimento sull'oggetto Collection, almeno per il momento, possiamo affidarci alla guida in linea. In questa sede ci teniamo a sottolineare che, come molte altre collezioni già predefinite in ambiente VBA (Worksheets, Cells, Shapes, Windows, Charts, ecc), anche la nostra Collection ha due tipi di indice:

I due tipi di indice hanno la peculiarità di essere univoci e, se tentassimo di assegnargliene uno già esistente, provocheremmo senz'altro un errore a run time, cioè un errore in fase di esecuzione. Usando a modo tale peculiarità, aiutati da un normale gestore di errori, vedremo tra poco come è facile estrarre in modo univoco, non ripetuto, tutti i dati da una tabella o da un elenco.

Soluzione

Prima di usare un oggetto Collection è necessario innanzitutto crearlo. Per crearlo si può usare questa:

Dim MiaVariabile As New Collection

Negli esempi che seguono vedremo come:

riempire la nostra Collection personale con tutti gli elementi di un elenco (unici o no): in questo caso non dobbiamo preoccuparci di definire un indice alfanumerico e verrà assunto un indice numerico progressivo

riempire la nostra Collection personale coi soli elementi unici. Per questa seconda possibilità è necessario definire un indice alfanumerico ed affidarci alla consueta gestione degli errori: On Error Resume Next, On Error GoTo 0 o altri per evitare il blocco per eventuali errori dovuti ad indici ripetuti.

 

Nell'esempio che segue useremo, per chiarezza, entrambi i metodi, ma è solo il secondo quello che in questa sede ci interessa.

Qui di seguito vediamo un elenco sul quale intendiamo lavorare e, anche se esiguo, si possono notare molte ripetizioni.

scatole
bottiglie
bottiglie
vasetti
scatole
vasetti
confezioni
vasetti
confezioni
vasetti
confezione
confezioni
scatola
confezioni
bottiglie
scatole
latte
....
....

Primo esempio: raccolta di tutti i dati nella ComboBoxSub CreaElencoIntegrale()

Dim CL As Range, Intervallo As Range, Elenco As New Collection
Dim Valori As Variant
Dim Riga
Worksheets("Foglio2").Select
Set Intervallo = Range("A1", Range("A1").End(xlDown))
Set Intervallo = Intervallo.Offset(1, 0).Resize(Intervallo.Rows.Count - 1, Intervallo.Columns.Count)
For Each CL In Intervallo
'ci limitiamo a scrivere nella Collection tutti i dati della tabella
Elenco.Add CL.Value
Next
With Worksheets("Foglio2")
.ComboBox1.Clear
For Each Valori In Elenco
.ComboBox1.AddItem Valori
Next
End With
End Sub
collection con chiave numerica I dati raccolti nella ComboBox sono ripetuti. In questo caso non abbiamo fatto uso di alcuna chiave ed alla Collection sono stati attribuiti dei normali indici numerici progressivi
 

Secondo esempio: i dati vengono raccolti nella ComboBox in modo univoco

Sub CreaElencoUnivoco()
Dim CL As Range, Intervallo As Range, Elenco As New Collection
Dim Valori As Variant
Dim Riga
Worksheets("Foglio2").Select
Set Intervallo = Range("A1", Range("A1").End(xlDown))
Set Intervallo = Intervallo.Offset(1, 0).Resize(Intervallo.Rows.Count - 1, Intervallo.Columns.Count)
On Error Resume Next
For Each CL In Intervallo
'questa volta scriviamo nella collection anche un indice
Elenco.Add CL.Value, CStr(CL.Value)
Next
On Error GoTo 0
With Worksheets("Foglio2")
.ComboBox1.Clear
For Each Valori In Elenco
.ComboBox1.AddItem Valori
Next
End With
End Sub
collection con chiave alfanumerica

Come si può agevolmente verificare i dati raccolti nella ComboBox ora non sono più ripetuti, perchè questa volta abbiamo impostato, aiutati da una semplice gestione di errori, delle chiavi che nella Collection sono uniche.

Durante il lavoro di raccolta dei dati, se tentiamo di ripetere un indice verrà generato un errore che tuttavia, essendo controllato, non bloccherà l'esecuzione della procedura, ma ci farà semplicemente perdere il dato (ma è proprio quel che vogliamo)

Come si può vedere dai due esempi appena mostrati notiamo che:

Vediamo ora un esempio più complesso.

Dopo questo primo assaggio, molto semplice, su come utilizzare una Collection per reperire i dati univoci vediamo come agire in una situazione un po' più complessa.

Prendiamo in esame questa tabella:

 

  A B C D E
1 quantità descrizione colore art cod art pos
2 2 tavolo verde TV A1
3 6 tavolo verde TV A1
4 3 sedia rosso SR A2
5 8 panca rossa PR A7
6 5 sedia verde SV A2
7 2 tavolo rosso TR B
8 5 panca verde PV A6
9 5 tavolo rosso TR B
10 2 tavolo nero TN A4
11 9 sedia rosso SR A2
12 1 tavolo nero TN A4

Da questa tabella dobbiamo estrarre gli articoli in modo univoco e sommarne i rispettivi movimenti.

La complessità della tabella sta nel fatto che gli articoli, anche se duplicati, possono essere di tipologia diversa (colori diversi per articoli simili) per cui, volendo estrarre gli articoli in maniera univoca, non basta estrarre un tavolo, una sedia o altro, ma bisogna tener conto anche dei colori dei singoli articoli. Fortunatamente notiamo che nell'elenco sopra mostrato il codice dei singoli articoli provvede ad indicarci il giusto articolo: TV (tavolo verde), TN (tavolo nero), SR, SV, ecc.

Il nostro lavoro allora viene incentrato proprio sulla colonna relativa a questi codici.

Per la Collection uso i codici degli articoli come chiave e i numeri di riga relativi agli articoli come dato. Questo perchè rileggendo poi questi valori sappiamo a quale riga riferirci per ogni singolo articolo

On Error Resume Next
For R = 1 To Righe
Elenco.Add R, CStr(Intervallo(R, 4).Value)
'in Intervallo(R, 4).Value troviamo i codici degli articoli che usiamo come Chiave, mentre memorizziamo il solo valore della riga
Next
On Error GoTo 0

Alla fine avremo che la Collection contiene questi dati: 1, 3, 4, 5, 6, 7, 9 che rappresentano le righe relative al codice dell'articolo che ci interessa prendere in considerazione.

Al momento dell'utilizzo della Collection basta usare un ciclo per:

Ora occorre solo prestare la massima attenzione alle variabili usate nel frammento di codice di seguito illustrato:

Fatte queste premesse non penso servano altre spiegazioni per il seguente codice, tra l'altro già sufficientemente commentato:

 

Sub ElencoUnivoco()
' inizializzazione delle variabili di servizio
Dim Intervallo As Range, IntervDest As Range
Dim Elenco As New Collection
Dim Righe, R, Riga, A, R1
' l'elenco da trattare si trova nel Foglio1 a partire dalla cella A3,
' che è la zona delle intestazioni di colonna
Worksheets("Foglio1").Select
Set Intervallo = Range("A3").CurrentRegion
' per escludere dall'intervallo le intestazioni di colonna
' sposto l'intero intervallo di una riga sotto e tolgo l'ultima riga che
' a causa di questa manovra è una riga vuota
Set Intervallo = Intervallo.Offset(1, 0).Resize(Intervallo.Rows.Count - 1, Intervallo.Columns.Count)
' definisco anche l'intervallo di destinazione ponendolo 2 colonne più in là
' dell'intervallo di origine
Set IntervDest = Intervallo.Offset(0, Intervallo.Columns.Count + 2)
IntervDest.ClearContents' cancello eventuali dati residui da precedenti manovre
' conto le righe dell'intervallo di origine
Righe = Intervallo.Rows.Count
' popolo la Collection coi dati univoci
On Error Resume Next
For R = 1 To Righe
Elenco.Add R, CStr(Intervallo(R, 4).Value)
Next
On Error GoTo 0
' come ultima azione copio la tabella in modo univoco e ne sommo i movimenti
For R = 1 To Elenco.Count
Riga = Elenco(R)
For A = 1 To Intervallo.Columns.Count
IntervDest(R, A) = Intervallo(Riga, A)
Next
IntervDest(R, A) = Riga
For R1 = Riga + 1 To Righe
If Intervallo(Riga, 4) = Intervallo(R1, 4) Then
IntervDest(R, 1) = IntervDest(R, 1) + Intervallo(R1, 1)
End If
Next
Next
End Sub

 

Alla fine ottengo questo risultato:

  A B C D E F G H I J K L
1 ELENCO ORIGINALE     ELENCO UNIVOCO
2 quant descr colore cod pos                      quant descr colore cod pos
3 2 tavolo verde TV A1     8 tavolo verde TV A1
4 6 tavolo verde TV A1     12 sedia rosso SR A2
5 3 sedia rosso SR A2     8 panca rossa PR A7
6 8 panca rossa PR A7     5 sedia verde SV A2
7 5 sedia verde SV A2     7 tavolo rosso TR B
8 2 tavolo rosso TR B     5 panca verde PV A6
9 5 panca verde PV A6     3 tavolo nero TN A4
10 5 tavolo rosso TR B              
11 2 tavolo nero TN A4              
12 9 sedia rosso SR A2              
13 1 tavolo nero TN A4              

 

Conclusioni

Prima di concludere è bene fare una osservazione sulle istruzioni usate per creare l'intervallo e nella tabella sottostante evidenzio che cosa succede con:

Set Intervallo = Range("A3").CurrentRegion

e con:

Set Intervallo = Intervallo.Offset(1, 0).Resize(Intervallo.Rows.Count - 1, Intervallo.Columns.Count)

 

Ecco i passi che si fanno con queste due righe di codice:

Range("A3").CurrentRegion Offset(1, 0) Resize(Intervallo.Rows.Count - 1, Intervallo.Columns.Count)
determino l'intervallo su cui voglio lavorare sposto la selezione nella riga sottostante, stessa colonna ridimensiono di una riga l'intero intervallo
primo passo secondo passo terzo passo