Cercare una determinata parola sul foglio di lavoro. Uso delle Funzioni InStr , Len, delle Proprietà SelLenght e SelStart e del Metodo SetFocus. - dal 04/09/04 pagina vista: volte

Per mie necessità nell'ottenere un programma che mi consentisse di realizzarmi un "Indice di voci" trovando una parola in una pagina di testo, mi è venuto in mente che le procedure seguite potevano interessare altre necessità: ho pensato per esempio ad un database di un magazzino, dove si voglia trovare il Codice di un'Articolo, di cui sappiamo o ci ricordiamo una parola contenuta nella Descrizione Articolo, oppure un database di una Libreria, dove si voglia rintracciare un libro attraverso la ricerca di una parola contenuta in un campo Recensione, o ancora un'Agenzia immobiliare che per rintracciare un'immobile svolga una ricerca su una parola contenuta in un ipotetico campo Notazioni, (ascensore, 2 bagni, giardino, ecc. ecc.). Insomma, i casi per poter usare questi suggerimenti non mancano certamente, oltre al fatto di cercare di completare alcuni articoli presenti sul sito circa la possibilità di crearsi degli elenchi di file presenti sul nostro hard-disk, e perchè sempre più usiamo Excel come database, inserendo nelle celle frasi a volte molto lunghe, presento una nuova procedura (per questi siti) di ricerca: rintracciare una determinata parola in tutte le celle del foglio di lavoro, con evidenzazione  DELLA PAROLA CERCATA. Utilità questa da poter abbinare ad alcune procedure come quelle trattate negli articoli (in questa sezione) "Usare Shell + Run", "Usare l'HyperLinks" e anche "DriveList,DirList, FileList", oppure in elenchi nei quali, per necessità gestionali, si usino celle contenenti frasi lunghe, come potrebbe essere un campo "Descrizione" oppure "Argomento trattato", oppure "Recensioni", campi insomma dove per necessità visive, limitiamo la larghezza del campo e dove risulta impossibile leggere tutto il testo inserito. Sappiamo infatti che se in una cella scriviamo una frase dal testo lungo, Excel ce lo mostra nella sua lunghezza, ma se nella cella a destra inseriamo un valore, la frase "rientra" nella cella di residenza (al di là delle opzioni di Controllo testo). Un esempio, nella cella A2 abbiamo un testo lungo che fuoriesce dalla cella, come scriviamo un qualsiasi valore nella cella a lato (B2), il testo rientra:

Questo comportamento ci impedirebbe di leggere in una ricerca manuale la parola America. Ma potrebbe essere anche il caso di colonne impostate con larghezza sufficiente a contenere il testo, ma su fogli con database molto ampi, dove per trovare tutte le parole cercate, dovremmo scorrere centinaia o migliaia di righe di dati.

Ora, per trovare una parola all'interno di un testo, meglio se cominciamo a chiamarlo "stringa di testo", e abbreviato "stringa", possiamo ricorrere all'Operatore LIKE (vedi articolo sull'altro sito, sezione vba), che con una routine veloce veloce, ci consentirebbe di trovare la cella con la stringa contenente la parola cercata, MA NON di evidenziare la parola stessa all'interno della stringa, che è lo scopo di questo articolo. Vediamo comunque una routine con Like, dal sapore un pò goliardico per il tipo di domanda posta, che può comunque servire egregiamente dato che il contenuto di ogni cella occupata da una stringa in cui compaia la parola che cerchiamo, viene evidenziato nella barra della formula, e tanto basterebbe per riconoscere se la stringa è quella che cerchiamo:

Ed ora passiamo a vedere il come evidenziare una parola all'interno di una stringa. Useremo ora le Funzioni InStr , Len, le Proprietà SelLenght e SelStart e il Metodo SetFocus per una TextBox (ActiveX) posta in una UserForm. Abbiamo bisogno di sfruttare caratteristiche di una TextBox disponibili SOLO se la TextBox ActiveX è nel suo ambiente naturale. In particolare il metodo SetFocus NON si applica ad una TextBox ActiveX posta ("prestata") sul foglio di lavoro. Ma vediamo prima funzioni e proprietà:

• InStr - Funzione. Restituisce un valore Variant (Long) che specifica la posizione della prima occorrenza di una stringa (o parola) all'interno di un'altra. La sua sintassi è questa (tralascio gli argomenti opzionali, tra parentesi quadre):  InStr([inizio, ]stringa1, stringa2[, confronto]) - dove: stringa1 è l'oggetto che contiene la stringa (tutto il testo) DOVE cercare, stringa2 è l'oggetto DA cercare, cioè la parola da cercare all'interno del testo.

• Len - Funzione. Restituisce un valore (Long) che contiene il numero di caratteri presenti in una stringa o il numero di byte necessari per memorizzare una variabile. La sua sintassi è questa : Len(stringa | nomevariabile). E possibile usare uno solo dei due argomenti nella funzione, e noi useremo stringa. Con Len misuriamo da quanti caratteri è formata la parola da cercare.

• SelStart - Proprietà, si applica solo ad una TextBox e/o ComboBox ActiveX. Indica il punto di inizio del testo selezionato oppure il punto di inserimento se non è selezionato alcun testo. La sua sintassi è : oggetto.SelStart [= Long]. Oggetto è sempre necessario (sarà la nostra textbox), [Long] è facoltativo. e dovrà essere una espressione numerica che indica il punto di inizio del testo selezionato. Nel nostro esempio sarà rappresentato dal valore restituito dalla funzione InStr.

• SelLenght - Proprietà, si applica solo ad una TextBox e/o ComboBox ActiveX. Indica il numero di caratteri selezionati in una casella di testo o nell'area di immissione del testo di una casella combinata. La sua sintassi è : oggetto.SelLength [= Long] . Oggetto è sempre necessario (sarà la nostra textbox), [Long] è facoltativo, e dovrà essere una espressione numerica che indica il numero di caratteri selezionati. Nel nostro caso sarà la funzione Len.

• SetFocus - Metodo, si applica a tutti gli "Oggetti" ActiveX inseriti su UserForm (non disponibile se un oggetto ActiveX viene inserito sul foglio di lavoro). Serve per spostare lo stato attivo sull'istanza corrente dell'oggetto. La sua sintassi è semplice: oggetto.SetFocus . Oggetto in questo caso sarà la nostra TextBox.

Bene, se avete seguito fin qui, non mollate che ora viene il bello. La routine che stiamo impostando, ha come compito principale quello di evidenziare una parola in una stringa di testo, e tanto per intenderci, cercando la parola "America", vogliamo un risultato del genere:

e perchè ciò avvenga, è necessario che l'oggetto in cui si trova la stringa (una TextBox) e la parola trovata, mantenga lo stato attivo (cioè il Focus). Questa necessità non è gestibile in due casi :

• Se si inserisce una TextBox (ActiveX) direttamente sul foglio di lavoro. In questo caso la TextBox non possiede la proprietà TabIndex nè la proprietà TabStop e quindi rifiuta il SetFocus necessario per selezionare la parola cercata, generando un errore. Non è possibile usare il Select perchè servirebbe solo a selezionare la TextBox ma non a porvi il Focus.

• Useremo quindi una UserForm per inserirci la TextBox  dove visualizzare il contenuto della cella contenente la stringa con la parola cercata, ottenendo il risultato voluto. Abbiamo anche bisogno però di una istruzione che preveda la continuazione della ricerca in tutte le celle occupate del foglio. Per fare questo ci dovremo servire di una finestra di dialogo che ci ponga una domanda: se vogliamo continuare o fermare la ricerca. E qui troviamo il secondo ostacolo al nostro progetto: infatti la comparsa di una finestra di dialogo immediatamente seguente al testo trovato, TOGLIE il Focus alla parola appena selezionata, prendendo lo stato attivo, e noi col piffero che identificheremo subito detta parola, per stabilire se la stringa è quella che ci interessa, specie se il testo è molto lungo, e la parola magari è alla fine della stringa.

Chiariti questi due punti, iniziamo a vedere come sono composte le istruzioni: per chiamare la routine useremo un pulsante posto sul foglio di lavoro. La macro viene posta in un modulo standard, useremo delle variabili che dovranno essere "visibili" anche fuori dal modulo (anche dalla UserForm) e quindi nella sezione Dichiarazioni  -  Generale del modulo, inseriremo queste due istruzioni:

• Option Compare Text - che ha il compito di rendere Non CaseSensitive  la ricerca della parola ( America sarà così trovata anche se come ricerca useremo america (con la minuscola) o AMERICA).

• Option Explicit - obbliga a "dichiarare" una variabile. Dovremo in questo caso obbligatoriamente Dimensionare (Dim) il "tipo" (As) di variabile. Noi useremo tutte variabili di "tipo" Variant, che possono essere non dichiarate, e se non dichiarate, vengono di default assunte come tali.

• poi imposteremo la proprietà ShowModal della UserForm a False. Questo ci consente di poter utilizzare il foglio senza essere bloccati dalla presenza della UserForm.

Useremo una InputBox per reperire la parola da cercare, per definire l'area in cui eseguire la ricerca useremo UsedRange che ci consente di reperire tutte le celle del foglio con dati, anche se non contigue o distanti, e lo "spazzolamento" delle celle lo faremo con un ciclo For Each..Next. Useremo poi la funzione Timer : impostando il tempo in secondi, regoleremo la durata tra la visualizzazione della stringa con la parola cercata, e la comparsa del messaggio che ci chiederà se continuare la ricerca o meno. Io per consentire di modificare il tempo ho inserito una seconda TextBox nella UserForm, con la sua proprietà Text impostata a 5 (secondi). Questo valore è il valore assegnato alla pausa del Timer; potrete modificarlo inserendo un valore fisso diverso anche direttamente nella riga dell'istruzione, oppure richiedere con un'altra inputbox il tempo che giudicherete a voi necessario. (ricordo che questa pausa deve servire per riuscire a leggere tutto il testo visualizzato; con testi molto lunghi dove l'individuazione della parola può richiedere più tempo, sarà necessario impostare tempi di pausa più lunghi.). Cominciamo a vedere questa benedetta routine, con le spiegazioni (in verde):

Una variante per gestire meglio il tempo della pausa necessaria alla lettura del testo, può essere questa:

• Si imposta un tempo di pausa lungo, per esempio 30 secondi (modificando la proprietà Text della TextBox2 che rappresenta il valore in secondi assegnato a TotalTime)

• si aggiunge un CommandButton sulla UserForm, e nel suo evento Click inseriamo l'istruzione per chiudere la Form: la potremo chiudere appena ci siamo resi conto se la stringa che appare nella textbox1 è quella che interessa o meno, anticipando (con la riga in grassetto inserita al punto successivo)  la continuazione della ricerca o la selezione della cella così trovata:

• Private Sub CommandButton1_Click()
  Unload Me
  End Sub

• Poi aggiungeremo una riga di istruzione nella procedura Timer che controlli se la UserForm è visibile oppure no; nel caso non sia visibile si esce dal ciclo Do While con Exit Do, e si prosegue (in grassetto la modifica):

• Do While Timer < Start + PauseTime
  DoEvents  'si passa con DoEvents il controllo ad altri processi

• If UserForm1.Visible = False Then Exit Do
  Loop 

Vediamo le immagini; la InputBox per l'introduzione della parola da cercare:

e la userform con il testo visualizzato e la parola che abbiamo cercato evidenziata, l'indicazione di quale cella è selezionata, la casella per modificare il  tempo di pausa (se siete veloci):

Per poter ospitare eventuali testi lunghi, la proprietà MultiLine della TextBox1 è impostata a True, e la proprietà ScrollBars impostata a 2 (barre di scorr. verticali). Le barre compariranno se il testo visualizzato occuperà più righe rispetto alla dimensione che avremo scelto per la TextBox .

Per riallacciarmi ad discorso iniziale, legato a possibili ricerche su un determinato campo e non a tutto il foglio (come nella routine presentata) sarà sufficiente reimpostare la "Zona", quindi un esempio che effettui la ricerca in tutte le celle della colonna D, sarà:

• Set Zona = Worksheets(1).Range("D : D") 

Se poi vogliamo identificare tutti i dati correlati relativi alla parola trovata, dopo aver risposto NO alla richiesta di continuare la ricerca, possiamo modificare la seconda istruzione CL.Select   in : CL.EntireRow.Select e verrà selezionata l'intera riga.

Buon lavoro.

prelevato sul sito www.ennius.altervista.org