Excel - Estrarre dati univoci da una colonna - VBA

NOTA. Gli esempi ed il codice sono forniti *così come sono* e l’autore declina ogni responsabilità per un loro uso scorretto. Utilizzate gli esempi forniti o file di prova per i vostri test. La correzione delle eccezioni (errori) e il controllo del tipo di dati inserito è solo parziale. I file vogliono essere un semplice esempio che illustra come sia possibile automatizzare Excel e Word e sono forniti solo a scopo dimostrativo.

In Excel/VBA, possiamo estrarre dati univoci dalla colonna di un foglio in due modi differenti: sfruttando il filtro avanzato di Excel e tramite l’oggetto Collection. Pensiamo ad esempio ad un lungo elenco di nominativi o di prodotti presenti nella colonna del foglio che dobbiamo estrarre in forma univoca. Negli esempi utilizzerò questa struttura per la tabella di Foglio1 dove in colonna A ho i dati da rendere univoci:

Figura 1: Struttura tabella

Scaricando i file, potete notare come i dati della colonna A siano ripetuti più volte.

Nel primo esempio: valoriunivoci.xlsmutilizzo nel codice VBA il filtro avanzato di Excel. Se la procedura di filtro avanzato/valori univoci viene eseguita direttamente sul foglio, non consente di creare il filtro univoco in un foglio diverso da quello che contiene i dati d’origine, mentre con il codice è possibile avere i dati filtrati in un foglio differente. Nell’esempio, creo un nuovo foglio, vi porto i dati filtrati, li utilizzo per caricarli in una ComboBox nella UserForm e quindi elimino il foglio. Per caricare i dati nella ComboBox, utilizzo il metodo AddItem e non la proprietà RowSource,, in quanto eliminando il foglio con i dati filtrati, la proprietà non troverebbe più la fonte dati. Creo un nuovo foglio anche se potrei utilizzare una colonna *lontana dai dati* dello stesso foglio che contiene la tabella, ad esempio la colonna AV, e questo per impedire eventuali problemi *sporcando* il foglio che dovrebbe contenere unicamente la tabella.

Lanciando la macro mMostraUserForm (è possibile farlo tramite i tasti di scelta rapida CTRL+a), viene caricata la ComboBox1 nell’evento Initialize della UserForm e espandendo la ComboBox1 vedo la mia lista univoca:

Figura 2: ComboBox con dati univoci

Nel secondo esempio: collectionfoglio.xlsmutilizzo l’oggetto Collection per caricare i dati nelle due ComboBox della UserForm (è possibile lanciare la UserForm con i tasti di scelta rapida CTRL+a).

L’oggetto Collection accetta più parametri; i due che ci interessano qui sono Item e Key:

•  Item sarà ciò che inseriremo nella Collection e può essere una stringa, un numero, un oggetto, ecc.
• Key sarà la chiave univoca che contraddistinguerà l’Item dagli altri. Key deve essere una stringa (String). Se tentiamo di inserire due Key identiche, verrà sollevata una eccezione e non verrà inserito nessun valore nella Collection. Questo assieme all’istruzione On Error Resume Next ci consente di gestire l’eccezione (non si interrompe l’esecuzione del codice) e di inserire solo dati univoci nella Collection

Nel modulo di codice della UserForm vi sono due diversi metodi per caricare le ComboBox:

• la Sub m_1()carica i dati univoci nell’oggetto Collection e quindi cicla la Collection per inserire i dati nella ComboBox1
• la Sub m_2() che sfrutta l’eccezione restituita quando si cerca di inserire un valore con una chiave già presente nella Collection per caricare i dati nella ComboBox2, il tutto nello stesso ciclo che carica la Collection

Personalmente preferisco la soluzione m_2(). Questo il risultato:

Figura 3: ComboBox2 con i valori univoci

Nel Modulo1 è inoltre presente la macro m_3() (è possibile lanciarla con i tasti di scelta rapida CTRL+s) che mostra come venga sollevata una eccezione che blocca la soluzione se non gestita. L’eccezione è sollevata alla prima chiave doppia che si tenta di inserire nella Collection. Nella figura qui sotto la riga incriminata in fase di debug:

Figura 4: Evidenziata la riga con l'eccezione non gestita

Nel terzo esempio: collectionoggetti.xlsmutilizzo la Collection per caricare degli oggetti. Creo una classe (Classe1) che rispecchia il modello dati presente nella tabella del Foglio1:

• tre campi: sNome, sComune, sProvincia non visibili direttamente dall’esterno
• tre proprietà di scrittura (Let): Nome, Comune, Provincia
• quattro proprietà di lettura (Get): Nome, Comune, Provincia, TuttiDati

Nel Modulo1 sono presenti 5 Sub:

• mCreaCollection (che può essere lanciata con i pulsanti di scelta rapida CTRL+q) che crea una Collection che ha visibilità per tutto il progetto (la dichiarazione è Public e all’esterno di tutte le Sub) e nella quale istanzio tanti oggetti quanti sono i valori univoci in colonna A e li inserisco nella Collection
• mProva_1 (che può essere lanciata con i tasti di scelta rapida CTRL+w) che cicla gli oggetti della Collection e ne recupera i valore dei campi tramite le proprietà di lettura Get e via via li mostra in una serie di MsgBox

Figura 5: Esempio di MsgBox con i dati di uno degli oggetti ciclati nella Collection

• mProva_2 (che può essere lanciata con i tasti di scelta rapida CTRL+e) che mostra i valori dei campi in una MsgBox, tramite una proprietà di lettura Get di uno specifico oggetto; punto a quello specifico oggetto tramite la key univoca che lo identifica all’interno della Collection

Figura 6: MsgBox con i dati di uno specifico oggetto della Collection

• mEliminaCollection (che può essere lanciata con i tasti di scelta rapida CTRL+r) che elimina dalla memoria la Collection
• mMostraaUserForm (che può essere lanciata con i tasti di scelta rapida CTRL+a) che richiama la UserForm1. Se la Collection è stata istanziata, all’avvio la ListBox1 presente sulla UserForm verrà caricata con i campi dei vari oggetti della Collection tramite le proprietà di lettura Get degli oggetti stessi

Figura 7: La ListBox che mostra i dati dei vari oggetti della Collection:

In tutti i casi in cui la Collection non sia istanziata, l’errore viene gestito tramite una MsgBox che avverte l’utente.

Figura 8: Avviso di Collection non istanziata

Quest’ultima soluzione (Collection/Oggetti) ha un suo valore quando si ha la necessità di avere disponibile in memoria dati recuperati da un database; in questo caso il modello dati della classe seguirà quello della fonte dati.

Tutti i codici nei vari file sono ampiamente commentati e gli esempi possono essere scaricati dal sito maurogsc.eu:

• valoriunivoci.xlsm
• collectionfoglio.xlsm
• collectionoggetti.xlsm

Commenti.

Sono graditi i tuoi commenti a questo articolo Wiki e, se lo ritieni interessante, per favore spunta la voce : E' stato utile all'inizio della pagina.

Grazie.

NOTA. Excel non dispone nativamente di procedure assolutamente sicure per impedire la visualizzazione e la modifica del codice vb e/o per la protezione dei fogli. E’ una cosa da tenere sempre presente quando si distribuiscono file con contenuti ritenuti delicati. Ricordate anche che è possibile lanciare il file di Excel senza che vengano eseguite le macro.