Articolo wiki
  • Questo articolo wiki è bloccato
Applicabile a 8484 visualizzazioni

Excel - Estrarre dati univoci da una colonna - VBA

Livello tecnico : Base

Riepilogo

Livello tecnico: Conoscenza di base di Visual Basic for Applications Si applica a: Excel (2003/2007/2010/2013)

Dettagli

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:

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.


Risorse.


 Avvio Pulito di Windows
(courtesy of Microsoft MVP Franco Leuzzi)


Computer infettato da malware (courtesy of Microsoft MVP Vincenzo Di Russo)




Commenti (0)

avanzamento