Macro Excel Formattazione Celle

Come utilizzare il registratore macro di excel con impostazione dei riferimenti relativi per costruire una macro che applichi qualunque tipo di formattazione automaticamente a qualunque intervallo celle indipendentemente dal numero di righe e colonne e dalla posizione della cella iniziale.

Nel nostro intervallo celle possono essere presenti testo, valori numerici o formule. Nell'esempio applichiamo uno stile differente per le 3 tipologie di celle e il formato valuta con 2 decimali per valori numerici e per le formule, ma la tecnica esposta in questo tutorial è valida anche per qualsiasi altra combinazione di formati: basta usare il registratore delle macro e sostituire le righe di codice generate nella posizione opportuna (figura1).

Iniziamo con il registrare una semplice macro a riferimenti assoluti, selezionando dapprima con il mouse o con i tasti direzionali l'intervallo celle interessato. In seguito perfezioneremo la macro in modo da poter funzionare con qualsiasi intervallo. Selezioniamo la linguetta Sviluppo nella barra multifunzione di Excel, quindi nel riquadro Codice (il primo a sinistra) facciamo clic sull'icona registra nuova macro (la prima in alto accanto all'icona Macro). La macro che stiamo creando si chiamerà per ora Macro1 e verrà salvata nella cartella di lavoro corrente (figura2).

La registrazione è in corso. Apriamo la linguetta Home della barra multifunzione, quindi facciamo clic sull'icona Trova e Seleziona (icona con il binocolo), quindi, nel menù a discesa selezioniamo la voce: Vai a formato speciale (figura3).

Nella finestra di dialogo Vai a formato speciale selezioniamo il pulsante di opzione Costanti e de-selezioniamo al contempo tutte le caselle di opzione sottostanti tranne Testo. Facciamo clic su OK per chiudere la finestra di dialogo (figura4).

A questo punto, mentre il registratore macro è sempre attivo, facciamo clic sul pulsantino Stili cella, riquadro Stili, sezione Home della barra multifunzione di Excel. Notate che nel frattempo risultano selezionate tutte le celle del nostro intervallo che contengono testo (etichette di colonna e le celle della prima colonna: Categoria). Nel riquadro a colori che si apre sulla destra della finestra selezioniamo 60% - Colore 1 (azzurro) (figura5).

Le celle del nostro intervallo che contengono testo ora hanno lo sfondo azzurro e il colore del font è bianco. Proseguiamo sempre con il registratore macro attivo e ritorniamo a Trova e selezona, vai a formato speciale. Nella finestra di dialogo Vai a formato speciale selezioniamo ora Costanti / Numeri (figura6).

Torniamo a Stili cella e, mentre notiamo che ora sono state selezionate le celle che contengono valori numerici (escluse le formule) lasciamo Normale come colore di sfondo e selezioniamo invece il formato Valuta nella parte inferiore del riquadro a colori (figura7).

Ripetiamo la stessa sequenza per la terza e ultima volta. Questa volta selezioniamo il pulsante di opzione Formule nella finestra di dialogo Vai a formato speciale, lasciando selezionate tutte e 4 le caselle di opzione sottostanti (Numeri, Testo, Valori logici ed Errori) (figura8).

Nel riquadro a colori che si apre dopo aver fatto clic sull'icona Stili cella, questa volta selezioniamo Valore valido (rettangolo il cui colore sfono è verde prato) mentre sono selezionate le celle del nostro intervallo che contengono formule (i totali di colonna mensili e i totali di riga trimestrali) (figura9).

A questo punto possiamo interrompere la registrazione di Macro1, facendo clic sullo stesso pulsantino di prima (sezione Sviluppo, riquadro codice) che questa volta espone il tooltip: Interrompi macro ed è a forma di quadratino rosso.

Nella figura seguente è mostrato il codice della Macro1 alla quale sono stati aggiunte alcune righe di commento per evidenziare meglio le parti della macro che si riferiscono alla formattazione delle 3 tipologie di celle (testo, numeri e formule). Ricordiamo che abbiamo utilizzato i riferimenti assoluti, come per esempio: Range("A1:E4").Select (figura10).

E' il momento di testare la nostra prima macro. Facciamo clic su Sviluppo, Macro, quindi selezioniamo Macro1 nel riquadro Nome macro della finestra di dialogo Macro e quindi facciamo clic sul pulsante di comando Esegui (figura11).

Il nostro intervallo celle viene formattato come previsto. Le celle contenenti testo hanno lo sfondo azzurro, quelle che cotengono formule sono evidenziate in verde, mentre le celle che contengono valori numerici hanno lo sfondo bianco. Numeri e Formule sono formattati come valuta Euro con 2 decimali (figura12).

Se vogliamo togliere tutte le formattazioni automaticamente e ritornare quindi alla situazione iniziale magari per fare altre prove di formattazione, ci conviene registrare un'altra macro, che chiameremo Macro3 per azzerare i formati. Il Nome Macro2 lo conserviamo per la versione a riferimenti relativi di Macro1 che vedremo tra poco. Ricordatevi di selezionare sempre manualmente con mouse o con i tasti direzionali l'intervallo celle prima di registrare qualsiasi Macro (figura13).

Mentre è attivo il registratore Macro non facciamo altro che andare su Stili cella e fare clic sul primo rettangono: Normale, nel riquadro a colori che si apre sulla destra della finestra di Excel (figura14).

Il codice di Macro3 è molto semplice: contiene una sola istruzione, che ha l'effetto di riportare a Normal gli stili di tutte le celle dell'intervallo selezionato. Un metodo alternativo sarebbe scrivere: Selection.ClearFormats (figura15).

Proviamo ora spostare il nostro intervallo celle, inserendo 2 colonne vuote a sinistra e 2 righe vuote sopra il nostro intervallo. Facciamo questo per dimostrare l'utilità dei riferimenti relativi anziché di quelli assoluti quando si registra una macro che lavora con celle di un foglio di Excel. Proviamo ora ad eseguire di nuovo Macro1 (quella con i riferimenti assoluti) dopo avere selezionato il nostro intervallo celle che si trova ora in una nuova posizione del foglio (C3:G6 anziché A1:E4) (figura16).

Dopo avere fatto clic su Esegui nella finestra di dialogo Macro, ci accorgiamo che la Macro non funziona più ed inoltre viene generato anche un errore. Questo perché la Macro utilizza ancora il riferimento assoluto a A1:E4 mentre ora l'intervallo celle è stato spostato in nuova posizione. Utilizzando i riferimenti relativi questo problema non si porrebbe. Vediamo come si fa (figura17).

Registriamo ora Macro2 con i riferimenti relativi abilitati, come mostrato dalla figura seguente, nella quale è visibile, evidenziato con una freccia e un cerchietto blu il pulsantino che si trova immediatamente sotto a quello che attiva la registrazione macro. Fate attenzione all'aspetto grafico del pulsantino: si tratta di un togglebutton, quindi se cliccato 1 sola volta viene abilitato se prima era disabilitato (icona non in rilievo) e invece viene disabiltato se prima era abilitato (icona in rilievo) Ora ci assicuriamo che l'icona sia in rilievo e che quindi i riferimenti relativi sono correttamente impostati (figura18).

Registriamo Macro2 ripetendo esattamente tutti i passaggi che avevamo seguito in precedenza per registrare Macro1. Al Termine andiamo ad ispezionare il codice della nuova macro: Macro2 (riferimenti relativi). Notate che le uniche differenze sono l'utilizzo del metodo Offset di ActiveCell (che è un oggetto Range) al posto di Range("A1:E4") (Riferimento assoluto)

Ma il riferimento a Range("A1:E4") compare ancora nel codice, come è possibile? Eppure se provate a spostare l'intervallo con un taglia e incolla brutale per esempio in cella G20, la Macro2 funziona regolarmente! (figura19).

La spiegazione è data dal fatto che il riferimento Range("A1:E4") serve ora soltanto per ricordare alla macro quante colonne e quante righe deve considerare a partire dall'ultima cella selezionata (che è H20 e non più B1!). Infatti se impostate un punto di interruzione in corrispondenza dell'istruzione:

ActiveCell.Offset(0, -1).Range("A1:E4").Select

ActiveCell è l'ultima cella selezionata dopo l'applicazione delle istruzioni precedenti (ricordiamo che Selection all'inizio contiene l'indirizzo delle celle selezionate, qualunque sia ora la nuova posizione)

Se infatti digitate nella finestra immediata dell'editor VBA il comando seguente, quando il codice è sulla riga sopra:

?activecell.Offset(0,-1).Range("A1:E4").Address

$G$20:$K$23

Ora l'indirizzo è G20:K23 (perché Offset(0,-1) ha avuto l'effetto di spostare la selezione corrente di una colonna indietro, e cioè da H20 a G20, che è la nuova cella iniziale del nostro intervallo. (figura20).

Ora possiamo eseguire la Macro2 senza più preoccuparci di dove sia posizionato effettivamente il nostro intervallo celle: basta ricordarsi di selezionare l'intervallo prima di eseguire la Macro. Tutto questo grazie all'impostazione dei riferimenti relativi e non di quelli assoluti (figura21).

Ma questo è soltanto l'inizio! Possiamo miglioare ancora la nostra macro, per esempio modificando il nome da Macro2 a ApplicaFormattazione e inserire in testata una variabile stringa: SelezionaIniziale = Selection.Address. In questo modo non occorrerà più nemmeno utilizzare il metodo Offset. Ad ogni ricerca della tipologia (testo, numeri, formule) nell'intervallo selezionato, la selezione corrente verrà riportata su Range(SelezioneIniziale).Select come si vede dall'esame del listato mostrato in figura22.

Tra l'altro otteniamo un secondo vantaggio non da poco. Se infatti proviamo ad inserire una ulteriore colonna al nostro intervallo, ad esempio I dati relativi al mese di Aprile ed eseguiamo questa volta la macro ApplicaFormattazione invece di Macro2, i formati vengono applicati magicamente anche alla nuova colonna (che contiene ancora sia testo, numeri e formule) (figura23).

Ora la nostra Macro è indipendente dal numero di colonne e di righe provate anche ad inserire più righe (per esempio una riga Gomme prima del totale) e vedrete che la macro funziona lo stesso.

Ma non siamo ancora contenti, vogliamo che la macro sia in grado anche di cercarsi da sola la cella iniziale dell'intervallo e di conseguenza selezionare da sola anche l'intero intervallo, dovunque si trovi la cella iniziale e qualunque sia il numero di righe o il numero di colonne interessate, senza alcun intervanto dell'utente, che quindi non dovrà più preoccuparsi di selezionare l'intervallo manualmente prima di eseguire la macro: come ottenere tutto questo?

Basta costruire una funzione VBA che chiameremo FindFirstNonEmpyCell() che significa: 'Trova la prima cella (del foglio) non vuota' . La funzione recupera l'indirizzo della prima cella non vuota del foglio corrente e lo assegna alla variabile stringa Target. La riga successiva imposta correttamente l'intervallo grazie al metodo CurrentRegione dell'oggetto Range:

SelezioneIniziale = Range(Target).CurrentRegion.Address (figura24).

Nella figura sotto è visibile il codice della funzione FindFirstNonEmptyCell, la quale ha 2 argomenti: Righe e Colonne, che abbiamo impostato a 100 per non impegnare troppo la CPU. Difficilmente il nostro intervallo si trova fuori dall'area visibile del foglio di lavoro quindi abbiamo pensato che limitare la ricerca alle prime 100 righe e alle prime 100 colonne sia sufficiente.

In pratica si tratta di un doppio ciclo for next. Quando la funzione VBA IsEmpty ritorna un valore non nullo per la prima volta si esce dalla funzione con l'indirizzo della cella ricerca (figura25).

Ulteriore osservazione: il metodo CurrentRegion dell'oggetto Range potrebbe non funzionare correttamente se ci sono righe o colonne vuote in mezzo al nostro intervallo, ma questo non lo consideriamo come un problema nel nostro caso in quanto ci saranno almeno le intestazioni di colonna e i nomi delle categorie in tutte le righe della prima colonna.

Ecco l'ultima versione della nostra macro in azione: ora aabbiamo inserito 2 pulsanti di comando, il primo (Formatta Celle) è associato alla macro ApplicaFormattazione, mentre il secondo (Azzera formattazione) è associato alla macro AzzeraFormattazione (ex Macro3). Inoltre abbiamo inserito anche i dati completi del secondo trimestre con relativi subtotali (figura26).

Grazie alla tecnica descritta in questo tutorial siete ora in grado di personalizzare la vostra macro per applicare automaticamente le vostre formattazioni preferite a qualsiasi intervallo celle per qualunque posizione iniziale e per qualunque numero di righe e di colonne.



Avvertenza !

Il file zip dell'esempio Macro Excel Formattazione Celle contenente il progetto completo incluso il codice vba in chiaro di cui si è discusso in questo tutorial, è disponibile nell'area 'Videotutorial Download' riservata agli iscritti ai nostri corsi on line.

Nella colonna di destra puoi vedere una anteprima della 'Videotutorial Download area' e quanti e quali altri progetti Access, Excel, VBA sono disponibili.

Acquista anche uno solo dei nostri corsi per avere immediatamente accesso alla 'Videotutorial Download Area': è facile, basta un clic:

http://www.mc2.it






I Tutorial di Microsoft Excel


Home page MC2 E-Learning



Dr.Maurizio Cucchiara

Amministratore unico
MC2 E-Learning S.a r.l.
Formazione per il Web
autore del corso:
"Excel Corso Base"
disponibile on line su questo sito
http://www.mc2.it/html/excel.html

Condividi sui social network:

Social buttons



Iscriviti al nostro
nuovissimo
canale Youtube!


Iscrivi al nostro canale Youtube



Potrai restare aggiornato
sui nostri più recenti
video-tutorial su:
Microsoft Access ed Excel
e molto altro...



Cerca sul web con Google:




Guarda il video-tutorial
sul nostro canale Youtube:


Macro Excel Formattazione Celle

NOVITA'!

'VIDEO TUTORIAL
DOWNLOAD AREA'

(Fai clic sull'immagine
qui sotto per vedere
una anteprima della
pagina di accesso)



L'accesso è riservato agli iscritti ai corsi on line www.mc2.it

Acquista anche uno solo
dei nostri corsi
per avere immediatamente
accesso alla
'Videotutorial Download Area': è facile, basta un clic:

http://www.mc2.it



Iscriviti alla newsletter: E' GRATIS! (?)

 Nome: (?)

 (?)

e-mail: (?)

 (?)

Newsletter:

formato preferito:

Microsoft Access (?)

Impariamo Excel e il VBA(?)

Internet "da zero"! (?)

Html (?)

Consiglio

(?) = Aiuto

Leggi qui la Informativa completa sulla privacy.