excel-vba
Grafici e grafici

Creazione di un grafico con intervalli e un nome fisso

I grafici possono essere creati lavorando direttamente con l'oggetto Series che definisce i dati del grafico. Per arrivare alla Series senza un grafico esistente, si crea un oggetto ChartObject su un determinato Worksheet e si ottiene l'oggetto Chart da esso. Il vantaggio di lavorare con l'oggetto Series è che puoi impostare Values e XValues facendo riferimento agli oggetti Range . Queste proprietà dei dati definiranno correttamente la Series con riferimenti a tali intervalli. Lo svantaggio di questo approccio è che la stessa conversione non viene gestita durante l'impostazione del Name ; è un valore fisso. Non si regola con i dati sottostanti l'originale Range . Controllo della formula SERIES ed è ovvio che il nome è fisso. Questo deve essere gestito creando direttamente la formula SERIES .

Codice utilizzato per creare un grafico

Si noti che questo codice contiene dichiarazioni di variabili aggiuntive per il Chart e il Worksheet . Questi possono essere omessi se non vengono utilizzati. Possono essere utili tuttavia se si modifica lo stile o altre proprietà del grafico.

Sub CreateChartWithRangesAndFixedName()

    Dim xData As Range
    Dim yData As Range
    Dim serName As Range
    
    'set the ranges to get the data and y value label
    Set xData = Range("B3:B12")
    Set yData = Range("C3:C12")
    Set serName = Range("C2")
    
    'get reference to ActiveSheet
    Dim sht As Worksheet
    Set sht = ActiveSheet
    
    'create a new ChartObject at position (48, 195) with width 400 and height 300
    Dim chtObj As ChartObject
    Set chtObj = sht.ChartObjects.Add(48, 195, 400, 300)
    
    'get reference to chart object
    Dim cht As Chart
    Set cht = chtObj.Chart
    
    'create the new series
    Dim ser As Series
    Set ser = cht.SeriesCollection.NewSeries
    
    ser.Values = yData
    ser.XValues = xData
    ser.Name = serName
    
    ser.ChartType = xlXYScatterLines

End Sub

Dati / intervalli originali e Chart risultante dopo l'esecuzione del codice

Si noti che la SERIES formula include una "B" per il nome della serie, invece di un riferimento alla Range che lo ha creato.

Creare un grafico vuoto

Il punto di partenza per la maggior parte del codice dei grafici è creare un Chart vuoto. Si noti che questo Chart è soggetto al modello di grafico predefinito che è attivo e potrebbe non essere effettivamente vuoto (se il modello è stato modificato).

La chiave di ChartObject sta determinando la sua posizione. La sintassi per la chiamata è ChartObjects.Add(Left, Top, Width, Height) . Una volta creato ChartObject , è possibile utilizzare il relativo oggetto Chart per modificare effettivamente il grafico. ChartObject si comporta più come una Shape per posizionare il grafico sul foglio.

Codice per creare un grafico vuoto

Sub CreateEmptyChart()
    
    'get reference to ActiveSheet
    Dim sht As Worksheet
    Set sht = ActiveSheet
    
    'create a new ChartObject at position (0, 0) with width 400 and height 300
    Dim chtObj As ChartObject
    Set chtObj = sht.ChartObjects.Add(0, 0, 400, 300)
    
    'get refernce to chart object
    Dim cht As Chart
    Set cht = chtObj.Chart
    
    'additional code to modify the empty chart
    '...

End Sub

Grafico risultante

Creare un grafico modificando la formula SERIE

Per il controllo completo su un nuovo oggetto Chart e Series (in particolare per un nome Series dinamico), è necessario ricorrere alla modifica della formula SERIES direttamente. Il processo per impostare gli oggetti Range è semplice e l'ostacolo principale è semplicemente la costruzione di stringhe per la formula SERIES .

La formula SERIES prende la seguente sintassi:

=SERIES(Name,XValues,Values,Order)

Questi contenuti possono essere forniti come riferimenti o come valori di matrice per gli elementi di dati. Order rappresenta la posizione della serie all'interno del grafico. Si noti che i riferimenti ai dati non funzioneranno a meno che non siano pienamente qualificati con il nome del foglio. Per un esempio di una formula di lavoro, fai clic su qualsiasi serie esistente e controlla la barra della formula.

Codice per creare un grafico e impostare i dati utilizzando la formula SERIES

Si noti che la creazione di stringhe per creare la formula SERIES utilizza .Address(,,,True) . Ciò garantisce che venga utilizzato il riferimento Range esterno in modo da includere un indirizzo completo con il nome del foglio. Si verificherà un errore se il nome del foglio è escluso .

Sub CreateChartUsingSeriesFormula()

    Dim xData As Range
    Dim yData As Range
    Dim serName As Range
    
    'set the ranges to get the data and y value label
    Set xData = Range("B3:B12")
    Set yData = Range("C3:C12")
    Set serName = Range("C2")
    
    'get reference to ActiveSheet
    Dim sht As Worksheet
    Set sht = ActiveSheet
    
    'create a new ChartObject at position (48, 195) with width 400 and height 300
    Dim chtObj As ChartObject
    Set chtObj = sht.ChartObjects.Add(48, 195, 400, 300)
    
    'get refernce to chart object
    Dim cht As Chart
    Set cht = chtObj.Chart
    
    'create the new series
    Dim ser As Series
    Set ser = cht.SeriesCollection.NewSeries
    
    'set the SERIES formula
    '=SERIES(name, xData, yData, plotOrder)
    
    Dim formulaValue As String
    formulaValue = "=SERIES(" & _
        serName.Address(, , , True) & "," & _
        xData.Address(, , , True) & "," & _
        yData.Address(, , , True) & ",1)"
    
    ser.Formula = formulaValue
    ser.ChartType = xlXYScatterLines

End Sub

Dati originali e grafico risultante

Si noti che per questo grafico, il nome della serie è impostato correttamente con un intervallo per la cella desiderata. Ciò significa che gli aggiornamenti si propagheranno al Chart .

Organizzazione di grafici in una griglia

Una routine comune con i grafici in Excel è la standardizzazione delle dimensioni e del layout di più grafici su un singolo foglio. Se fatto manualmente, puoi tenere premuto ALT mentre ridimensiona o spostando il grafico per "attaccare" ai contorni delle celle. Questo funziona per un paio di grafici, ma un approccio VBA è molto più semplice.

Codice per creare una griglia

Questo codice creerà una griglia di grafici a partire da una determinata posizione (in alto a sinistra), con un numero definito di colonne e una dimensione di grafico comune definita. I grafici verranno posizionati nell'ordine in cui sono stati creati e avvolti attorno al bordo per formare una nuova riga.

Sub CreateGridOfCharts()

    Dim int_cols As Integer
    int_cols = 3
    
    Dim cht_width As Double
    cht_width = 250
        
    Dim cht_height As Double
    cht_height = 200
    
    Dim offset_vertical As Double
    offset_vertical = 195
    
    Dim offset_horz As Double
    offset_horz = 40

    Dim sht As Worksheet
    Set sht = ActiveSheet

    Dim count As Integer
    count = 0
    
    'iterate through ChartObjects on current sheet
    Dim cht_obj As ChartObject
    For Each cht_obj In sht.ChartObjects
        
        'use integer division and Mod to get position in grid
        cht_obj.Top = (count \ int_cols) * cht_height + offset_vertical
        cht_obj.Left = (count Mod int_cols) * cht_width + offset_horz
        cht_obj.Width = cht_width
        cht_obj.Height = cht_height

        count = count + 1

    Next cht_obj
End Sub

Risultato con diversi grafici

Queste immagini mostrano il layout casuale originale dei grafici e la griglia risultante dall'esecuzione del codice sopra.

Prima

Dopo