Add-In Excel 2003: realizziamo una macro che colora le righe pari e dispari delle nostre tabelle con colori diversi
Nota:
C’è anche una versione alternativa della macro mostrata in questo articolo: Excel : Colorare le righe pari e dispari in maniera diversa. Nuovo codice e Macro da scaricare
Forse non tutti sanno cosa sia una macro per Excel, ma forse in parecchi utilizzano Excel per lavoro o per studio. Una macro altri non è che un insieme di comandi, che possiamo registrare in maniera semplice utilizzando l’apposita funzione (si avvia il tasto di registrazione delle macro, si eseguono una serie di operazioni, si preme quindi il tasto stop e alla fine, ogni volta che andremo ad eseguire la macro, excel ripeterà le operazioni che abbiamo registrato). E’ chiaro che una macro ci può semplificare di molto la vita quando bisogna svolgere operazioni routinarie. In questo articolo voglio osare molto di più: insegnarvi a creare una macro scritta in visual basic for applications, una sorta di visual basic adattato alle applicazioni. Le macro realizzate in questo modo permettono di compiere operazioni molto più potenti che altrimenti non sarebbero possibili con la semplice funzione di registrazione macro in excel.
Illustro in questo articolo una macro da me realizzata che serve a colorare le righe delle tabelle con colori alterni, una ricercatezza grafica molto nota ai più che realizzano report da database o comunque tabelle molto lunghe, in cui il colore diverso delle righe pari da quelle dispari, aiuta proprio chi visualizza la tabella a non perdere il “filo” lungo la riga.
Innanzitutto come creare una macro in VBA? Attenzione, premetto che questo articolo fa riferimento a Excel 2003: Si apre un nuovo file di Excel e dal menù “Strumenti” selezionare “Macro” -> “Visual Basic Editor” (ALT+F11):
Appare l’editor di Visual Basic, in cui si nota chiaramente un nuovo progetto Visual basic (VBAProject), che ha al suo interno una cartella oggetti, all’interno del quale c’è un “oggetto” chiamato ThisWorkbook e 3 riferimenti alle schede del foglio di lavoro:
Per creare una Macro, clicchiamo con il tasto destro sul VBAProject e selezioniamo: Inserisci -> Modulo
Appare quindi una nuova cartella (Moduli) con un nuovo oggetto (Modulo1):
Un Modulo è un elemento che contiene le macro. Difatti è qui che andremo a scrivere il codice per far funzionare la nostra macro. Copiamo il codice illustrato di seguito e incolliamolo nello spazio bianco dell’editor di visual basic (avendo cura che sia selezionato Modulo1), in maniera da avere una situazione come quella illustrata:
Il codice da copiare è il seguente:
Sub ColoraRighe() ' Ottengo l'indirizzo della selezione nel formato C1R1:C2R2 Selezione = Selection.Address ' Divido il range in base ai due punti per conoscere ' la cella di inizio e la cella di fine sp = Split(Selezione, ":") '0->cella di partenza, 1->cella di fine ' ottengo quindi un array dove: ' sp(0) = cella di inizio ' sp(1) = cella di fine ' Ora divido la cella di inizio per conoscere la riga di partenza spi = Split(sp(0), "$") '0->vuoto,1->colonna start (lettera),2->riga start (numero) ' Divido quindi la cella di fine per conoscere la riga di fine spu = Split(sp(1), "$") '0->vuoto,1->colonna end (lettera),2->riga end (numero) ' Conto il numero di colonne comprese nella selezione NumeroColonne = Selection.Columns.Count ' Mi posiziono alla prima cella della selezione Range(sp(0)).Select ' ... Cosi mi ricavo il numero di colonna da dove partire colonnastart = ActiveCell.Column ' Quindi il numero di colonna dove finire è dato da: colonnaend = colonnastart + NumeroColonne - 1 ' I numeri di riga li ho trovati prima, li converto in intero rigastart = CInt(spi(2)) rigaend = CInt(spu(2)) ' Faccio quindi un ciclo da riga di inizio a riga di fine For r = rigastart To rigaend ' controllo se la riga attuale è pari o è dispari If (r Mod 2) = 0 Then BG = bg1 Else BG = bg2 End If Range(Cells(r, colonnastart), Cells(r, colonnaend)).Interior.ColorIndex = BG Next r End Sub |
Analizziamo la struttura di questa macro. Il codice che Excel eseguirà è racchiusto tra “Sub” ed “End Sub” (ad indicare appunto una SUBroutine), scrivere SUB ColoraRighe() vuol dire che stiamo dando alla nostra macro il nome “ColoraRighe” in maniera tale che sia facilmente riconoscibile sia da noi sia da Excel, le parentesi tonde () indicano che a questa routine non passiamo nessun argomento. Le frasi scritte dopo l’apice ‘ sono commenti e vengono ignorati durante l’esecuzione, servono soltanto al programmatore per inserire delle note.
Incontriamo la prima istruzione:
Selezione = Selection.Address |
Il VBA è orientato agli oggetti. In questo punto, definiamo la variabile “Selezione” che conterrà all’interno l’indirizzo (Address) dell’oggetto selezione (Selection). Ovviamente questo funzionerà soltanto se all’interno del nuovo foglio Excel avremo selezionato un intervallo di celle, soltanto in questo modo l’oggetto “Selection” avrà modo di esistere e quindi possiamo leggerne la proprietà “Address” che restituisce l’indirizzo Excel della selezione nel formato ColonnaInizio RigaInizio: ColonnaFine RigaFine. Memorizziamo dunque questo indirizzo appunto nella variabile a cui abbiamo dato il nome di “Selezione”. Excel giustamente restituirà un errore se eseguiamo la macro e non abbiamo selezionato nulla.
Nota:
In questo modo la riga è espressa in formato numerico, la colonna in formato letterale, per cui la colonna ricavata in questo modo non possiamo utilizzarla con cicli numerici, come vedremo in seguito.
Segue quindi:
sp = Split(Selezione, ":") |
Eseguo la funzione SPLIT che serve a suddividere una stringa in un array, spezzettandola in base ad un delimitatore da me definito, in pratica passo due parametri: il primo è la variabile su cui eseguire la funzione e il secondo è il mio delimitatore (i due punti in questo caso), in maniera tale da ottenere i due pezzi separati: ColonnaInizio RigaInizio e ColonnaFine RigaFine, questi due pezzi andranno a finire nell’ array che io ho chiamato sp e che quindi avrà due indici : 0 che contiene il primo pezzo della mia stringa di partenza (la cella di inizio in questo caso) e 1 che contiene il secondo pezzo (la cella di fine). Abbiamo quindi eliminato i due punti dalla stringa.
Eseguo ancora un’altra funzione split sulle celle di inizio e fine per sapere separatamente solo la riga (che mi interessa) e solo la colonna (che ho in formato letterale, e non mi interessa):
spi = Split(sp(0), "$") spu = Split(sp(1), "$") |
Ho quindi a portata di mano tutti i dati sulle celle che mi interessano. Conto quindi da quante colonne è composta la mia selezione:
NumeroColonne = Selection.Columns.Count |
In questo caso eseguo il metodo Count sull’oggetto Columns (che identifica l’insieme di colonne) che a sua volta appartiene a Selection, so cosi il numero di colonne selezionate, che sarà memorizzato nella variabile che io ho chiamato NumeroColonne.
Posiziono quindi il cursore nella prima cella della mia selezione con l’istruzione:
Range(sp(0)).Select |
sp(0) come abbiamo visto prima contiene l’indirizzo della nostra cella di partenza, il metodo Select per funzionare ha bisogno dell’oggetto Range che indica un insieme di celle, in genere range viene utilizzato con una cella di inizio e una cella di fine in maniera tale da specificare un intervallo, in questo caso definisco l’oggetto range dando una sola cella come insieme: Range(sp(0)), e quindi la seleziono con il metodo Select
Mi ricavo quindi l’indirizzo numerico della colonna (quello letterale trovato prima non mi serve) da cui partire:
colonnastart = ActiveCell.Column |
ActiveCell è un oggetto che fa riferimento alla cella attualmente selezionata, la proprietà Column mi restituisce appunto il numero di colonna di cui fa parte questa cella.
L’indirizzo numerico della colonna di fine selezione è quindi dato da:
colonnaend = colonnastart + NumeroColonne - 1 |
I numeri di riga per poterli utilizzare devo convertirli in intero, altrimenti Excel mi darà errore, lo faccio utilizzando la funzione CINT (conversione ad intero) :
rigastart = CInt(spi(2)) rigaend = CInt(spu(2)) |
eseguo quindi un ciclo dalla riga di inizio alla riga di fine con l’istruzione:
For r = rigastart To rigaend ... Next r |
all’interno di questo ciclo eseguo una serie di istruzioni, la prima (con un costrutto If..then..else) per determinare se la riga che sto attualmente “ciclando” (mi si passi il termine…) è pari o dispari:
If (r Mod 2) = 0 Then BG = 43 Else BG = 44 End If |
Lo faccio utilizzando la funzione MOD che mi restituisce il resto di una divisione, se dividendo un numero per 2, ho zero come resto, vuol dire che il numero è pari, altrimenti (else) vuol dire che è dispari (come vedete in programmazione si ricorre a piccoli espedienti molto semplici quanto efficaci), quindi a seconda che è pari o dispari, assegno a una variabile che ho chiamato BG un numero, ogni numero è associato ad un colore (vedremo dopo).
Arriva quindi la parte che mi colora la riga:
Range(Cells(r, colonnastart), Cells(r, colonnaend)).Interior.ColorIndex = BG |
Abbiamo di nuovo l’oggetto range, qui seleziono un range che va dalla cella di inizio della mia riga (oggetto Cells al quale vanno fornite le coordinate riga, colonna) alla cella di fine ( e quindi: Range (cella inizio , cella fine) ). Faccio quindi riferimento alla proprietà Interior della riga selezionata (che indica la parte interna delle celle) e quindi ancora alla proprietà ColorIndex che fa riferimento a un indice colore (excel ha 56 indici colore ovvero ogni numero da 0 a 56 è associato a un colore, spiego dopo dove trovarli), imposto quindi il colore (colorindex) dell’interno (interior) di tutta la mia riga (cella inizio – cella fine) al colore che ho scelto prima con la divisione e che ho memorizzato nella variabile BG.
La macro così impostata già funziona bene. Per vederla in azione, dopo averla copiata e incollata come detto in precedenza, chiudiamo l’editor di visual basic (premendo la X in alto a destra alla finestra principale), e portiamoci sul nostro foglio excel, selezioniamo delle celle tenendo premuto il tasto sinistro del mouse e trascinando:
Eseguiamo quindi la macro cliccando su Strumenti -> Macro -> Macro:
Appare quindi la finestra con l’elenco delle macro che ha caricato Excel, deve apparire la nostra macro:
La selezioniamo e clicchiamo sul tasto “Esegui”. Come “per magia” in un solo colpo otteniamo un risultato che se avremmo voluto farlo manualmente, ci sarebbe voluto un bel po di tempo (questo si chiama appunto Office Automation: perchè fare a mano ciò che il computer è stato progettato per fare da solo?)
Se la nostra macro ci piace, possiamo salvarla per tenerla sempre a portata di mano. In questo caso ci conviene salvarla come “Componente aggiuntivo di Excel” (selezionare la scelta apposita in Tipo File come iilustrato nella schermata successiva) in maniera tale che dopo possiamo impostare Excel perchè carichi sempre questa macro ogni volta che si avvia e averla quindi sempre a portata di mano, diamogli quindi un nome significativo (io l’ho chiamata ColoraRighe) e premiamo Salva:
Di default Excel salverà questo Componente aggiuntivo nella sua cartella con tutti gli altri componenti aggiuntivi. Per renderlo sempre disponibile ad ogni avvio di Excel dobbiamo eseguire ancora un ultimo passaggio. Dal menù strumenti selezioniamo “Componenti aggiuntivi”:
Appare quindi la finestra con l’elenco dei componenti aggiuntivi, diamo segno di spunta sul nostro e premiamo Ok:
Il gioco è fatto. Avremo la nostra macro sempre disponibile dal menù Strumenti -> macro -> macro.
Possiamo personalizzare questa macro cambiando il colore delle righe, a questo punto può essere utile questa pagina http://www.mvps.org/dmcritchie/excel/colors.htm dove sono elencati gli indici colore di Excel che possiamo utilizzare nella nostra funzione per cambiare i colori delle righe pari e dispari.
