Usare RefEdit (oggetto ActiveX). - dal 04/09/04 pagina vista: volte In questo esercizio ci occuperemo dell'"oggetto" RefEdit, uno strumento utile in programmazione, che potrà interessare, credo, diversi utilizzatori di vba. Quante volte si impazzisce per creare riferimenti variabili (cioè definibili di volta in volta, a piacere), riferimenti che debbano servire per determinare un'origine dati da utilizzare nelle nostre procedure? Quante volte vogliamo identificare celle, con i cui valori vogliamo realizzare altre tabelle, una sorta di "seleziona e copia"? una specie di Filtro che ci consenta di riunire valori (dati) in modo da comporre una nuova tabella? Faremo un esempio in cui, utilizzando RefEdit, selezioneremo aree dati in un nostro ipotetico database, per caricare con i dati di quest'intervallo una ListBox. Ipotizzeremo anche, che per ogni valore così caricato nella ListBox, si possa ottenere la copia del valore che selezioneremo, in un'altra zona, magari di un altro foglio di lavoro. Intanto vediamo come ottenere il RefEdit : è un componente ActiveX, che possiamo chiamare nell'editor di visual basic, inserendo prima una UserForm, e dalla sua casella degli strumenti, selezionare cliccandoci destro, la voce "Controlli aggiuntivi". Nella finestra che appare, e che mostra tutti gli "oggetti ActiveX" diponibili sul proprio computer, bisogna mettere un segno di spunta alla voce : RefEdit.Crtl corrispondente alla libreria Refedit.dll normalmente disponibile all'interno della cartella Office in C:\Programmi\Microsoft Office\OfficeX\Refedit.dll (dove X sarà la vostra versione di Office). Nella immagine sotto vediamo come appare il controllo RefEdit nella casella degli strumenti della UserForm (freccia rossa), e come poi come si vedrà sulla UserForm (freccia blu) dopo che avremo selezionato il controllo e lo avremo trascinato, per dimensionarlo. Assomiglia ad una ComboBox, ma svolge funzioni molto diverse: Il RefEdit è lo strumento che consente di inserire un riferimento ad una o più celle, mediante il mouse, in modo simile a quanto accade con molte funzionalità di Excel, come in Ricerca obiettivo, Formattazione condizionale, Inserisci funzione per reperire i riferimenti alle celle (gli argomenti), ecc.ecc. insomma quella semplice finestrina lunga, che appare quando per fornire un riferimento clicchiamo sull'icona a destra dell'editor di riferimenti (o RefEdit che dir si voglia), quella specie di textbox che vediamo indicato nell'immagine sotto, dalla freccia rossa: e che fa scomparire la finestra, lasciando al suo posto questa finestrina che vediamto sotto: Quando ci troviamo in questa fase, se noi selezioniamo col mouse una cella o un intervallo, nella finestra appare l'intervallo selezionato, così: La freccia rossa indica un'intervallo selezionato, e memorizzato, e poi agendo sull'icona a destra (freccia blu), ricomparirà la nostra finestra per la prosecuzione delle operazioni. Credo che tutti avranno almeno una volta provato ad adoperare questo pratico sistema che Excel ci mette a disposizione. Bene, riprendendo l'esempio di questo esercizio, potremo quindi usare la nostra UserForm con alloggiato un RefEdit ed una ListBox, sfruttare il RefEdit per "caricare" di volta in volta intervalli di celle che formeranno il RowSource della ListBox; questo ci consente di ottenere liste di dati variabili, che potremo usare per copiare, con un click sul dato che interessa nella ListBox, in altre zone dello stesso foglio, o "mirare" a celle o intervalli di altri fogli. Potremo creare quindi tabelle di riepiloghi di nomi, numeri, totali, date, orari, dipenderà ovviamente dal tipo di dato di origine. Le modalità operative, dipenderanno da ciò che vogliamo ottenere : sarà possibile aggiornare dei valori, oppure inserendo un ciclo While, scegliere la prima riga libera di una colonna per accodare i dati ottenuti dalla selezione nella ListBox; oppure sarà possibile impostare routine che ci facciano scegliere di volta in volta la colonna (o la cella) di destinazione. Vediamo da vicino come poter procedere; per prima cosa una precisazione: è necessario che la proprietà ShowModal della UserForm che useremo, sia lasciata con l'impostazione di default, cioè = True. Modificando questa impostazione avremo solo il blocco totale di Excel. Questo in contrapposizione a quanto contenuto nella guida il linea, che a proposito del Controllo RefEdit, recita:
Evidentemente si tratta di un errore, visto che tutte le prove da me eseguite confermano quanto da me precisato. Ognuno comunque è libero di fare le proprie verifiche. Il RefEdit quindi ci restituisce un riferimento a cella/celle selezionate, sul tipo di Foglio1!A1:A7, e ce lo restituisce come stringa; quello stesso tipo di stringa che serve ad assegnare l'intervallo di origine dati ad una ListBox (ma anche per ComboBox) assegnato alla proprietà RowSource (se su userform, altrimenti ListFillRange se su foglio di lavoro, ma attenti: il RefEdit non è disponibile come "oggetto" inseribile sul foglio, solo su userform). Se inseriamo l'istruzione ListBox1.RowSource = RefEdit1.Text nell'evento Change del RefEdit, otterremo che ad ogni nuova selezione di celle tramite il RefEdit, assegnamo una nuova origine dati alla ListBox, aggiornando i dati nella lista. Vediamo una sequenza di immagini:
Abbiamo infatti generato l'evento Change del RefEdit attivando le istruzioni viste sopra che caricano i dati presenti nell'intervallo selezionato. Quello che poi faremo con questi dati si presta a numerose variazioni. Ho preparato una routine che sfruttando un ciclo While, copierà ogni dato che selezioneremo nella Listbox, sul foglio2, a partire dalla riga 5, nella colonna C, cercando per ogni dato, la prima riga libera per inserire il dato stesso. Chiaramente le destinazioni sono liberamente assegnabili, dipenderà dalle esigenze di ognuno. Precisazione: i dati caricati nella ListBox, anche se noi li vediamo col formato di origine, sono "visti" dal codice senza riconoscimento del "tipo di dato" di provenienza (ma, per le date o gli orari, solo come numeri; ricordo che Excel, per esempio, una data la vede come un numero che identifica quanti giorni sono trascorsi dalla data di inizio del suo calendario; altrettanto farà con gli orari (vedi articoli su date e orari sull'altro sito). Questo comporta che se cliccassimo sulla data 31/12/03 presente nella ListBox dell'esempio, il valore che sarebbe trasposto è 37986 (che sono i giorni trascorsi dall'inizio calendario di excel) se il formato cella destinazione è impostato a "Generale", mentre verrà copiata come data se avremo predisposto, per le celle destinazione, il giusto formato cella come "Data"; lo stesso faremo se le celle destinazione dovranno ospitare "tipi di dato" specifici (orari, numeri con decimali, ecc). Ricordiamoci sempre di questa necessità di predisporre il formato celle, sia in quelle di origine, sia in quelle di destinazione che ospiteranno lo stesso "tipo di dato" d'origne. Vediamo quindi le due routine: quella per caricare la ListBox:
e quella inserita nell'evento Click della ListBox, che serve appunto a copiare il dato ivi selezionato:
Questa routine serve ad accodare dati nella colonna di destinazione, se volessimo copiare un dato in una cella fissa, magari per aggiornare la destinazione con valori che vengano da celle diverse, basterà un'istruzione più semplice:
Potremo anche predisporre istruzioni che rendano variabile sia il foglio di destinazione, sia la colonna, sia la riga da cui iniziare a incollare. Potremo optare per InputBox con le quali richiedere i dati necessari, oppure predisporre delle TextBox nella UserForm nelle quali scrivere di volta in volta il numero o il nome del foglio destinazione, il numero di colonna, o/e il numero di riga. Una opportunità per tutti coloro che disponendo di un foglio di registrazione dati generale, vogliano per esempio, imputare valori a colonne di fogli diversi, ma il cui nome figura in uno dei campi del database generale. Oppure eseguire copie di dati multipli, caricando la ListBox su più colonne. Faremo quindi un'esempio un pò più articolato, ma più veritiero, su una situazione del genere:
Cosa vorremo ottenere?
L'esercizio è stato impostato su questo esempio e queste necessità, ma ovviamente è applicabile a situazioni similari dove vorremo creare selezioni variabili giorno dopo giorno. Vediamo un immagine della tabella usata come campione: Come anticipato, disponiamo di quattro campi ; per poter agire, avremo bisogno di visualizzare nella ListBox della form che apriremo per lavorare, tutti e quattro i campi per ogni riga, dovremo infatti identificare la città del campo "Deposito" perchè i dati al nome correlati (stessa riga nella ListBox) vengano copiati nel foglio che porterà lo stesso nome. Per ottenere una visione a quattro colonne nella ListBox, dovremo agire sulla sua proprietà "ColumnCount" impostando il valore "4". Poi, con le istruzioni, sfrutteremo la proprietà "List" della ListBox per determinare il valore di quale colonna, della riga che sarà selezionata, assegnare alla cella, stesso campo, del foglio destinazione. Useremo un pulsante che chiamerà la UserForm, e come spiegato negli esempi precedenti, sfrutteremo il RefEdit per reperire l'intervallo selezionato e caricare così la ListBox. Vediamo un immagine di come apparirà la situazione: Come si vede, l'area selezionata sul foglio (bordata di rosso) corrisponde a tutti i movimenti relativi alla stessa data (criterio di selezione), e con la routine già vista sopra, carichiamo con l'evento Change del Refedit, il RowSource della ListBox1, che con ColumnCount impostato a 4, ci consente di visualizzare tutte le quattro colonne selezionate sul foglio. In questo modo selezionando il nome del deposito nella ListBox, copieremo nel foglio con lo stesso nome, i dati della prima, terza e quarta colonna della ListBox (stessa riga). La seconda colonna è inutile copiarla, visto che ci serve solo a identificare il nome del deposito e quindi del foglio che porterà lo stesso nome, anzi la usiamo con una itruzione che copierà nella textbox1 il nome della città; abbiamo infatti aggiunto tre textbox che servono a rendere variabili sia il nome del foglio nel quale andare a copiare (textbox1), sia da quale riga iniziare a copiare (textbox2), sia da quale colonna iniziare a copiare (textbox3). L'aggiunta di tre textbox l'ho fatta solo a titolo dimostrativo, per far vedere come reperire tre variabili da assegnare aleìle istruzioni che vedremo, in realtà in questo esempio dove intendiamo accodare i dati a dati già esistenti, non era necessario rendere variabile il numero riga e il numero di colonna, ma inserirli come costanti direttamente nel codice, e il nome del foglio l'avremmo preso di volta in volta dal nome del deposito/foglio presente nella listbox, che avremmo selezionato. L'azione di copia avviene "a distanza", cioè non facciamo selezionare il foglio in cui copieremo; questo comporta che con un semplice click su ogni riga nella listbox, poichè attiviamo il suo evento Click, eseguirà le istruzioni senza che a video si notino cambiamenti; per questo ho inserito a fine istruzione il comando per un messaggio che avvisi l'esecuzione della copia. Ho inserito anche una verifica che controlla se esiste un foglio col nome deposito che verrà selezionato, nel caso non esista, si viene invitati a chiudere la form e controllarecome il nome è scritto nelle celle oppure a creare un nuovo foglio con quel nome. Non ho invece inserito un controllo che verifichi se la zona copiata è composta da 4 colonne, ma ce ne accorgeremmo subito guardando la ListBox e vedendo che mancano dati; sarà sufficiente agire di nuovo sul RefEdit. Vediamo le istruzioni: la prima, già vista, serve per assegnare al RowSource della ListBox l'intervallo reperito con RefEdit:
La seconda, inserita nell'evento Click della ListBox, si occupa di tutto il resto (in verde i commenti):
un accorgimento: nella sezione Generale - Dichiarazioni della userform, metteremo questa istruzione che servirà a non creare equivoci se un nome di deposito e lo stesso di un foglio, fossero scritti con diversità maiuscole/minuscole:
Mi sembra un buon esercizio, utile ed ampliabile, e le spiegazioni vi dovrebbero mettere in grado di modificarlo a volontà. Leggete anche gli altri articoli che parlano di ListBox, per familiarizzarvi con questi "oggetti ActiveX". Agggiungo una variante alla routine appena vista, per dimostrare l'ampliabilità delle istruzioni : inseriamo le istruzioni che se un foglio col nome uguale al deposito selezionato non esiste, ci venga posta la domanda se desideriamo aggiungerlo; in caso positivo, faremo aggiungere il nuovo foglio alla fine dei fogli già presenti, ne imposteremo la colonna A (quella che dovrà contenere le date) come "tipo di dato" (con NumberFormat) a formato Data, e ci copieremo i dati relativi alla voce selezionata, tutto in sequenza; dovremo aggiungere istruzioni per ritornare al foglio di origine, visto che il metodo Add porta il focus sul nuovo foglio. Se invece alla domanda risponderemo "No", si uscirà dalla routine. Vediamo le modifiche, che evidenzio in grassetto:
Buon lavoro. prelevato sul sito www.ennius.altervista.org |