|
Formattazione condizionale in vba. - pagina vista: volte Tra gli argomenti più digitati nei motori di ricerca (Google etc) per questioni che riguardano Excel, figurano le ricerche per le funzioni condizionali, Conta.Se, Somma.Se ecc., compreso molte ricerche sulla Formattazione Condizionale (che chiamerò FC d'ora in avanti), il che mi ha suggerito di preparare un altro articolo sull'impiego della FC ma usando appunto il vba, (esiste già su questo sito l'articolo "Formattazione Condizionale" della sezione "Formule" (vedi) che propone soluzioni per usare la FC sul foglio di lavoro sfruttando formule o funzioni tipiche del foglio di lavoro). Con il vba è molto più facile ottenere formattazioni personalizzate, ed in numero maggiore di quante non sia possibile con la FC del foglio di lavoro (dove non si possono usare più di 3 condizioni) : è possibile articolare un ventaglio più ampio di opzioni che verifichino le condizioni di applicazione di FC., e inoltre è possibile "mirare" a celle molto distanti tra loro, o celle poste su altri fogli, per applicarci la FC, e infine è possibile aggiungere alle condizioni da far eseguire (in genere, cambio di colore di celle o di fonts, grassettatura, evidensazione bordi cella, ecc. ecc.) anche avvisi o messaggi visivi o sonori o brevi pezzi musicali. Al di là dei motivi che ci spingono ad usare una FC, possiamo distinguere due metodi per l'esecuzione di istruzioni vba di formattazione: A) il metodo manuale: compiliamo una macro con le istruzioni di formattazione, la associamo ad un pulsante che attiveremo dal foglio di lavoro quando riterremo opportuno. B) in automatico: sfruttando uno degli "eventi" del Foglio di lavoro, ne suggerisco 2:
Potremmo considerare anche l'evento Worksheet_SelectionChange(ByVal Target As Range), ma suggerisco caldamente di evitare questo evento per attivare la FC perchè l'evento scatta ad ogni cambio di selezione cella e quindi richiamerebbe le istruzioni anche se non si è modificato niente sul foglio di lavoro.
Come "legare" l'attivazione della FC alla modifica dati di una cella (o intervallo di celle) ? Dei due eventi descritti sopra quello che ci consente di "rilevare" quale cella ha subito un "cambiamento" è l'evento Worksheet_Change, che tramite l'argomento "Target" (della funzione stessa) ottiene il riferimento alla cella che ha subito il cambiamento; se utilizziamo questo riferimento alla cella come argomento del metodo Intersect (vedi articolo sul sito) possiamo far reagire le istruzioni di FC poste nell'evento Change, SOLO se il cambiamento si verifica nella cella "Target". il secondo evento, il Worksheet_Calculate, necessario per rilevare le modifiche effettuate per il ricalcolo di formule o funzioni, lo useremo con una sola semplice istruzione, questa sotto:
Questa istruzione consente ad Excel di "rilevare" i cambiamenti che si verifichino in celle dei fogli di lavoro per effetto di ricalcoli di formule o funzioni, e se Excel sente l'avvenuto cambiamento in una o più celle, si verifica anche l'evento Change e quindi scatteranno le istruzioni inserite nell'evento "Worksheet_Change", cioè le nostre istruzioni di FC. Come al solito, qualche esempio "spicciolo" servirà meglio di mille parole. Questo sotto sfrutta il metodo Intersect per agire soltanto se una determinata cella (la cella G5, ma ovviamente potrà essere qualsiasi altra cella) conterrà un valore uguale o maggiore ad un determinato valore (nell'esempio la cifra 50); se si verificherà la condizione, rileviamo il numero di riga della cella Target (cioè della G5) e coloriamo di rosso tutta la riga; questo tipo di formattazione condizionale (FC) risulta particolarmente efficace per evidenziare il verificarsi della condizione prevista e non è ottenibile con la FC del foglio di lavoro; con l'istruzione "Else" (cioè se la cella G5 non corrisponderà più ad un valore uguale o maggiore della cifra prevista, ripristinamo il colore naturale nella riga)
questo esempio sotto, simile al precedente nel comportamento della FC, differisce in quanto con il metodo Intersect comprendiamo un intero intervallo di celle nel quale se in qualsiasi cella dell'intervallo apparirà la cifra prevista o superiore (286 nell'esempio) , si attiverà la FC che colorerà la riga dove la cella corrisponde alla condizione. Da notare che se nell'intervallo previsto, ci saranno celle rispondenti alla condizione, verranno colorate di rosso tutte le righe le cui celle sono interessate alla condizione (e solo quelle).
Se anzichè comprendere un intervallo di celle "conseguenti" (cioè tutte su una colonna o su una stessa riga) dobbiamo "mirare" a celle sparse potremo modificare i riferimenti alle celle da assegnare al primo argomento del metodo Intersect, così: si citano tra doppi apici i riferimenti alle celle, separati da una virgola. in questo esempio inoltre NON coloriamo l'intera riga, ma solo le celle interessate (cioè le celle Target).
Nei casi in cui non ci interessi evidenziare la cella stessa che ha subito un cambiamento ma una qualsiasi altra cella che funzioni da "allarme", basta inserire il riferimento alla cella usata come allarme nelle istruzioni per colorare anzichè colorare la cella Target; questa soluzione ci può tornare utile quando lavoriamo con tabelle molto estese: una FC di una cella non immediatamente visibile potrebbe passare inosservata; nell'esempio sotto, se in ognuna delle 4 celle usate come target ottempera alla condizione prevista, facciamo colorare di rosso un'altra cella, la G1 ad esempio, e facciamo scrivere la parola "Alarm";
ma avremmo potuto, anzichè colorare altre celle, lanciare un messaggio: in questo esempio sotto avvisiamo con una frase e il riferimento della cella che ha subito il cambiamento:
Nel caso volessimo essere avvisati se una cella di un altro foglio avrà un valore non ammesso per effetto di un ricalcolo di formule che interessano modifiche a celle del foglio attivo NON potremo usare il metodo Intersect per rilevare il cambiamento di valore di una cella perchè Intersect agisce soltanto sul foglio attivo (si genera un errore di run-time se gli chiediamo di controllare l'intersezione tra due celle di un foglio non attivo). Seguiremo allora un'altra strada: utilizzeremo il metodo CalculateFull però inserito nell'evento Workbook_SheetCalculate, cioè l'evento del Workbook che agisce sul ricalcolo di ogni foglio presente nella cartella (oppure avremmo potuto usare il Worksheet_Calculate ma del Foglio contenente la cella da controllare), e quindi usare l'evento Worksheet_Change del foglio attivo per essere comunque avvisati, quindi:
In questo esempio simulo di lavorare sul Foglio 3 (foglio attivo) e di voler essere avvisato se la cella A1 del Foglio 1 conterrà valori compresi tra 40 e 80, valori che possono cambiare per effetto di formula residente che può subire modifiche per effetto di variazioni in celle del Foglio 3: consideriamo questo esempio: in A1 del Foglio 1 la seguente formula =D1*Foglio3!D1 che moltiplica il contenuto della cella D1 del foglio 1 per il contenuto della cella D1 del foglio 3
quest'altro esempio invece colorerà di rosso tutto il foglio di lavoro, creando un potente effetto di allarme; da notare che l'istruzione agisce se in una qualsiasi cella del foglio di lavoro, si scriverà un determinato valore (il valore 50 come esempio) ; potrebbe essere usato per impedire , tramite allerta visivo, l'inserimento di valori non accettabili; non scordiamoci che si agisce sulla cella Target, quindi l'ultima cella che subisce il cambiamento, e basterà che in una altra cella di nuovo si scriva un valore differente per eliminare il colore impostato, anche se nella cella precedentemente modificata permane il valore 50 (o quello che sarà).
Insomma, impostare istruzioni condizionali è abbastanza facile conoscendo che cosa dobbiamo controllare o verificare, ed altrettanto facile risulta scegliere il tipo di evidenzazione (visiva o sonora) per far risaltare il verificarsi di una o più condizioni. Credo di aver offerto un aiuto in più a chi cerca suggerimenti per problemi quotidiani. Buon lavoro. prelevato sul sito www.ennius.altervista.org |