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):

macrovba_01

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:

macrovba_02

Per creare una Macro, clicchiamo con il tasto destro sul VBAProject e selezioniamo: Inserisci -> Modulo

macrovba_03

Appare quindi una nuova cartella (Moduli) con un nuovo oggetto (Modulo1):

macrovba_04

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:

macrovba_05

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:

macrovba_06

Eseguiamo quindi la macro cliccando su Strumenti -> Macro -> Macro:

macrovba_07

Appare quindi la finestra con l’elenco delle macro che ha caricato Excel, deve apparire la nostra macro:

macrovba_08

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?)

macrovba_09

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:

macrovba_10

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”:

macrovba_11

Appare quindi la finestra con l’elenco dei componenti aggiuntivi, diamo segno di spunta sul nostro e premiamo Ok:

macrovba_12

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.

Se questo articolo ti è piaciuto, condividilo su un social:
  •  
  •  
  •  
  •  
  •  
  •  
Se l'articolo ti è piaciuto o ti è stato utile, potresti dedicare un minuto a leggere questa pagina, dove ho elencato alcune cose che potrebbero farmi contento? Grazie :)