Fare un Grafico col vba

Difficilmente nei miei lavori ho fatto e faccio uso di Grafici (in inglese: Charts), ed ho sempre evitato di scrivere articoli sull'argomento, vista l'estrema complessità delle istruzioni da usare non tanto per creare ma soprattutto gestire Grafici col vba.

Complessità motivata dalla quantità di istruzioni necessarie a gestire tutti i componenti di un grafico (e sono molti), non certamente dalla comprensibilità delle istruzioni stesse. Se poi consideriamo la moltitudine di "Tipo di Grafico" disponibili in Excel, non se ne uscirebbe più.

Ma credo di avere trovato un modo per parlare di Grafici, riducendo all'indispensabile due articoli che presento: questo basato su Chart.Add, ed un secondo di prossima uscita basato su ChartObjects.Add. Qui parleremo delle istruzioni per creare un grafico base (il più semplice è il tipo a Linee), che non sono molte, e di istruzioni "ristrette" per quanto riguarda la gestione dell'origine dati, e di alcune caratteristiche del grafico.

Voglio comunque ricordare che la maniera più semplice per ottenere un grafico sarebbe quella di seguire la "Creazione guidata Grafico", un pratico ed efficace wizard (mago) che Excel mette a disposizione (dal menù Inserisci/Grafico, o cliccando sull'apposita icona nella barra menù "Standard"). Se poi, prima di iniziare la "Creazione guidata", si attivasse il "Registratore di macro", spengendolo a fine creazione, otterremmo tutto il codice vba creato nell'occasione dal bravo Excel.

Suggerisco quindi a chi si accingerà a provare a realizzare un grafico dopo aver letto queste note, di usare il wizard per ottenere il formato di grafico che più gli interessa, registrandolo, e comparando poi i dati ottenuti con le informazioni qui contenute: risulterà più facile capire dove intervenire per eventuali modifiche.

La prima cosa da fare è di disporre di un'area dati (una tabella) in cui i dati sono raggruppati; nell'esempio che seguiremo vedremo la realizzazione di un grafico a Linee che mostri l'andamento settimanale (rappresentato dalle date di rilevamento) delle visite su i due miei siti: i numeri rappresenteranno i "valori" mostrati dalle linee; questa una vista parziale dell'area dati: nella colonna A le date di rilevamento visite, nella colonna B le visite per settimana al sito interfree e nella colonna C quelle al sito altervista (che è stato "aperto" il 21/07/03); come si nota i dati sono in colonne contigue ("raggruppati") :

Nota: nella "Creazione guidata" (wizard) è opportuno selezionare, prima di iniziare, tutta l'area dati, ma se i dati sono raggruppati come nella tabella sopra, si può selezionare una qualsiasi cella della tabella e ci penserà Excel a rilevare tutta l'area, mentre tramite vba dovremo fornire noi gli indirizzi dell'intervallo.

Prima di vedere le istruzioni per la creazione del grafico, mi pare opportuna una precisazione: dove collochiamo il grafico? Le possibilità sono due:

Se proviamo questa semplice routine (dobbiamo prima avere selezionato l'area dati da graficizzare), vedremo aggiungere un nuovo foglio "Grafico1" con il grafico creato (anche se non sarà proprio quello che vorremo)

E' l'istruzione Charts.Add (Grafico.Aggiungi) che aggiunge un nuovo foglio con il grafico che conterrà i valori rilevati dall'area precedentemente selezionata. Questa semplice routine provocherà un risultato del genere:

dove i valori sono riportati in colonne (tipo grafico : istogramma) e diversificati dal diverso spessore. Contemporaneamente, dall'editor di visual basic, nella finestra "Progetto-VBA Project", vedremo l'aggiunta del nuovo foglio:

dove il nuovo foglio viene identificato dal tipo di oggetto inserito (Grafico) dal numero progressivo 4 oltre al nome assegnatogli in automatico (Grafico1).

A questo proposito (dei numeri progressivi) ricordo che Excel registra (nella cartella attiva) tutto ciò che aggiungiamo: siano essi fogli, grafici(fogli), grafici incorporati, shapes, oggetti ActiveX, ecc. ecc, e per ogni "classe" di oggetti assegna e memorizza il numero indice che identifica l'oggetto; se noi eliminiamo uno di questi oggetti, e ne inseriamo al suo posto uno nuovo, il nuovo non prende lo stesso numero di quello cancellato, ma al nuovo oggetto (stessa "classe") verrà assegnato il numero indice successivo a quello eliminato. Per cui se noi eliminiamo il foglio "Grafico4(Grafico1)" e ripetiamo la macro vista sopra, verrà generato il "Grafico5(Grafico2)" ; viene incrementato il numero indice sia del foglio Grafico (5) sia del suo nome (Grafico2).

Chiariti alcuni aspetti, vediamo invece una routine che ci consentirà di ottenere un grafico incorporato (quindi su un foglio esistente, in questo esempio lo stesso foglio dove risiedono i dati) :

come si legge viene ancora usato il metodo Add dell'oggetto Chart (Chart.Add) , poi al grafico ora attivo (ActiveChart) viene applicato il metodo SetSourceData che serve ad impostare l'intervallo dei dati di origine per il grafico (in questo esempio i dati sono sul Foglio1, nell'intervallo A3:C22), (quindi non è più necessario selezionare l'area dati da graficizzare).

Ed è il metodo Location applicato all' ActiveChart che tramite l'argomento Where ci consente di definire la posizione del grafico, indicando il nome del foglio destinatario del grafico. Vediamo che Where possiede l'argomento xlLocationAsObject : tre sono i possibili argomenti (costanti XlChartLocation) di Where:

dove: Se Where ha valore xlLocationAsObject , indica il nome del foglio dove il grafico sarà incorporato; se Where ha valore xlLocationAsNewSheet, indica il nome del nuovo foglio da creare. Se avessimo voluto inserire il grafico su un nuovo foglio chiamato "Visite Siti", dovremmo cambiare l'istruzione

Come potete vedere è abbastanza facile seguire e capire le istruzioni di base per ottenere un grafico. Il grafico poi potremo spostarlo in una parte del foglio a nostro piacere, e ,vedremo in seguito, come richiamare una "finestra" che ci mostri il grafico, anche se il grafico è stato spostato fuori vista.

Ma ho detto che vogliamo intervenire per gestire l'aspetto del nostro grafico, che con le istruzioni finora usate si presenta di default un pò spartano, anche se comunque leggibile, vediamo quindi una serie di istruzioni per "modellare" il grafico.

Impostare il grafico a Linee (di default è a Istogramma, come nella foto sopra):

e produrrebbe questo tipo di grafico, che è quello che vogliamo; da notare che avendo inserito in tabella le intestazioni di campo nelle tre colonne "date, interfree, altervista", Excel provvede a inserire automaticamente nella etichetta di Legenda, i nomi relativi alle linee dell'asse valori:

A questo punto, potremmo prendere in esame un'infinità di parametri su cui voler intervenire: dai colori, alla dimensione, dalla posizione della "Legenda", ai bordi del grafico, alla posizione del grafico sul foglio, alla dimensione ed al tipo dei font usati, all'intervallo da tenere nelle scale dei valori, al formato e ai colori delle linee, ecc. ecc.

Come anticipato, mi limiterò alle cose secondo me necessarie per una più corretta lettura del grafico.

In un grafico 2d come questo a linee, la rappresentazione grafica viene svolta su un piano formato da due assi: uno verticale che prende nome "Asse di valori" (xlValue), ed uno orizzontale che prende nome "Asse delle categorie" (xlCategory) :

I numeri e le date che vediamo lungo gli assi, sono "unità" di scala, e vengono rilevati dall'origine dei dati, e visualizzati da Excel in automatico, e sono influenzati dalla dimensione del grafico (maggiore sarà la dimensione, maggiore sarà la quantità di "unità" visualizzata). La proprietà che determina la quantità di "unità" da visualizzare è la proprietà MajorUnit del metodo Axes applicato all'oggetto Chart (ActiveChart) (Grafico attivo).

L'asse dei valori che vediamo (da 0 a 1500) viene indicizzato da Excel che, rilevando il valore massimo presente tra i numeri delle visite presenti nelle due colonne B e C, imposta la scala dei valori su un valore superiore al valore massimo rilevato; le unità di scala (in questo caso di 500 in 500) vengono determinate in automatico da Excel in funzione della dimensione del grafico stesso. E' un intervallo un pò troppo ampio che non consente una visione precisa delle Linee per quanto riguarda lo scartamento dei valori. Possiamo intervenire per modificare l'intervallo in modo che risulti più leggibile. Dovremo quindi aumentare l'altezza del grafico (e allungarlo), ma vedremo dopo come; ora restiamo a parlare dell'Asse dei valori e del suo "Formato Asse", ricco di settaggi, che si articola in:

Noi agiremo sulla scala dei valori e delle categorie e nella dimensione e orientamento del carattere (solo categorie). Sarà necessario ricorrere a due blocchi di istruzioni; il primo ci consentirà, modificando la proprietà che determina il valore di intervallo scala (MajorUnit), di ottenere un maggior numero di ordinate e quindi un maggior numero di "unità", il secondo, riducendo la dimensione del carattere, di avere bisogno di un'altezza e lunghezza grafico inferiore. Vediamo i due gruppi di istruzioni (che poi vedremo nella macro completa) :

e ora le istruzioni per modificare l'asse delle categorie: come si nota, le istruzioni impegnano quasi tutte le proprietà dell'istruzione vista per la scala dei valori, solo che mentre sopra abbiamo assegnato un numero all'intervallo di scala (MajorUnit) , cioè abbiamo detto di impostare la cadenza di 100 in 100 visto che i valori sono "numeri", per le categorie si indicherà una cadenza di 7 giorni (una settimana) visto che la cadenza delle date è settimanale. Vedremo così "muoversi" le linee sull'asse dei valori raggiunti settimanalmente:

Attenzione: intervenire sulle proprietà MajorUnit con valori impostati da noi, verrà eseguito se daremo al grafico dimensioni sufficienti a mostrare le cadenze scelte, altrimenti Excel mostrerà sì la cadenza scelta, ma vedremo solo ciò che la dimensione del grafico permetterà di mostrare, oppure una sovrapposizione quasi illeggibile. Insomma, bisogna anche capire che non potremo far entrare in un "fazzoletto" di 6x10 cm una scala date dell'arco di molti mesi, se non allunghiamo opportunamente il grafico, oppure se scegliamo un valore di "unità" dei valori basso, ed avremo un elevato valore di massimo di scala, e non avremo opportunamente alzato (l'altezza) del grafico.

Ora vediamo i blocchi di istruzioni per modificare la dimensione dei font per l'asse valori, e per la dimensione dei font e loro orientamento in verticale dell'asse categorie (questi ultimi verrebbero mostrati obliqui, a 45°, occupando una lunghezza maggiore). Le istruzioni sono facili da capire è commento solo la proprietà Size, che determina la dimensione del font, e che impostiamo a 6; questa sotto è per l'asse dei valori :

come si nota si usa l'istruzione With...End With per intervenire sulle proprietà dell'oggetto Font applicato a ChartArea senza ripetere per ogni proprietà l'oggetto Font. Nell'istruzione per dimensionare e orientare il testo dell'asse categorie, per ridurre la quantità di istruzioni (altrimenti necessarie) abbiamo bisogno di lavorare su due proprietà dell'oggetto TickLabels (etichette di graduazione) applicata all'oggetto Axes, il suo orientamento testo (Orientation) e il suo Font, e faremo in questo modo:

vediamo alla fine le istruzioni per il dimensionamento del grafico; i valori che leggete sono relativi all'esempio, e andranno modificati secondo le vostre eventuali esigenze, intervenendo sui valori delle proprietà.

E' necessario precisare alcune cose: per tutte le operazioni da eseguire su un grafico, è necessario identificare il grafico, o riferendosi a lui con ActiveChart (quando il grafico viene creato, in quel momento è attivo), oppure indicandone il nome nelle azioni di attivazione o selezione ( Activate o Select ) : a seconda dell'azione che intendiamo compiere sul grafico, cambierà il sistema di riferimento; normalmente un grafico incorporato in un foglio è un ChartObject e per riferirsi a lui per modifiche o impostazioni, una volta attivato, si usa il metodo ChartWizard ; in alcuni casi possiamo usare le istruzioni seguenti: esse si basano sul fatto che un grafico viene anche visto da Excel come una Forma (Shapes("nome del grafico")) , e come Shape possiede dei metodi non disponibili per ChartObject (che vedremo nel secondo articolo).Quando ci riferiamo ad uno Shapes è necessario indicare prima su quale foglio risiede, quindi non più ad ActiveChart, ma in questo caso ad ActiveSheet seguito dal nome del grafico, in questo caso "Grafico 1" :

Sub GraficoSulFoglioFinale()
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("foglio1").Range("A3:C22")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Foglio1"
'impostiamo dimensione font all'asse valori
With ActiveChart.ChartArea.Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 6
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
'impostiamo dimensione font all'asse categorie e orientiamo il testo in verticale
With ActiveChart.Axes(xlCategory).TickLabels
.Orientation = xlUpward 'orientamento del testo in verticale (nelle TicksLabels)
.Font.Name = "Arial"
.Font.FontStyle = "Normale"
.Font.Size = 6
.Font.Strikethrough = False
.Font.Superscript = False
.Font.Subscript = False
.Font.OutlineFont = False
.Font.Shadow = False
.Font.Underline = xlUnderlineStyleNone
.Font.ColorIndex = xlAutomatic
.Font.Background = xlAutomatic
End With

'impostiamo il valore di "unità" all'asse valori
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnit = 100
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
'impostiamo il valore di "unità" all'asse categorie
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = 7
.MajorUnitScale = xlDays
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
'ridimensioniamo il grafico
ActiveSheet.Shapes("Grafico 18").ScaleHeight 1.4, msoFalse, _
msoScaleFromBottomRight
ActiveSheet.Shapes("Grafico 18").IncrementLeft 9#
ActiveSheet.Shapes("Grafico 18").IncrementTop 52.5
ActiveSheet.Shapes("Grafico 18").ScaleWidth 1.39, msoFalse, msoScaleFromTopLeft

End Sub

Direi decisamente più leggibile e completo rispetto al primo visto sopra. Al di là di possibili migliorie da fare (ingrossare le linee, scegliere dei colori a nostro piacere, spostare la Legenda, ecc.ecc) abbiamo visto una serie di istruzioni che ci consentono di realizzare un grafico gradevole e funzionale. Me queste istruzioni servono solo una volta, la prima volta quando vogliamo realizzare il grafico, non potremo usarle per esempio per aggiornare il grafico, perchè il grafico esiste già, e non prevede istruzioni per l'aggiornamento dei dati.

Se rileggiamo l'articolo, abbiamo visto che assegniamo via codice i riferimenti dell'intervallo dati con SetSourceData , ma cosa avviene quando aggiungeremo righe di nuovi dati alla nostra tabella? che i nuovi dati non saranno compresi nell'intervallo precedentemente impostato. Abbiamo quindi bisogno di creare una macro che chiameremo "AggiornaGrafico" dove, facendo leggere l'ultima riga occupata (Con End(xlDown)) ci consenta di riassegnare un nuovo intervallo a SetSourceData.

Tutte le istruzioni viste sopra sono istruzioni che agiscono sul grafico appena creato e quindi attivo (ActiveChart), ma ora il grafico non è più attivo e per poterci intervenire abbiamo bisogno di Attivarlo; in questo caso ci riferiremo al grafico identificandolo con ChartObject seguito dal nome del grafico, che non scordiamo, è ancora "Grafico 1":

Questa sotto invece è la macro che ci consentirà, nel caso di avere spostato il grafico in una zona del foglio fuori vista, di richiamarlo in una nuova finestra che posizioneremo opportunamente in vista: