excel-vba
Grafieken en grafieken

Een grafiek met bereiken en een vaste naam maken

Grafieken kunnen worden gemaakt door rechtstreeks te werken met het Series object dat de grafiekgegevens definieert. Om naar de Series zonder een bestaand diagram, maakt u een ChartObject op een bepaald Worksheet en haalt u er vervolgens het Chart object uit. Het voordeel van werken met het object Series is dat u de Values en XValues kunt instellen door te verwijzen naar objecten Range . Deze gegevenseigenschappen zullen de Series correct definiëren met verwijzingen naar die bereiken. Het nadeel van deze benadering is dat dezelfde conversie niet wordt verwerkt bij het instellen van de Name ; het is een vaste waarde. Het wordt niet aangepast met de onderliggende gegevens in het oorspronkelijke Range . Het controleren van de SERIES formule en het is duidelijk dat de naam vast is. Dit moet worden afgehandeld door de SERIES formule rechtstreeks te maken.

Code gebruikt om grafiek te maken

Merk op dat deze code extra variabele declaraties bevat voor de Chart en het Worksheet . Deze kunnen worden weggelaten als ze niet worden gebruikt. Ze kunnen echter handig zijn als u de stijl of andere grafiekeigenschappen wijzigt.

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

Oorspronkelijke gegevens / bereiken en resulterende Chart na uitvoering van code

Merk op dat de SERIES formule een "B" voor de serienaam in plaats van een verwijzing naar het Range dat deze heeft gemaakt.

Een lege grafiek maken

Het startpunt voor het overgrote deel van de grafiekcode is om een lege Chart . Merk op dat deze Chart onderhevig is aan de standaard grafieksjabloon die actief is en mogelijk niet leeg is (als de sjabloon is gewijzigd).

De sleutel tot het ChartObject is het bepalen van de locatie. De syntaxis voor de aanroep is ChartObjects.Add(Left, Top, Width, Height) . Nadat het ChartObject is gemaakt, kunt u het Chart object gebruiken om de grafiek daadwerkelijk te wijzigen. Het ChartObject gedraagt zich meer als een Shape om de grafiek op het blad te plaatsen.

Code om een lege grafiek te maken

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

Resulterende grafiek

Maak een grafiek door de SERIE-formule te wijzigen

Voor volledige controle over een nieuw Chart en Series (vooral voor een dynamische Series ), moet u de formule SERIES rechtstreeks wijzigen. Het proces om de Range objecten in te stellen is eenvoudig en de belangrijkste hindernis is eenvoudig het bouwen van een string voor de SERIES formule.

De formule SERIES heeft de volgende syntaxis:

=SERIES(Name,XValues,Values,Order)

Deze inhoud kan worden geleverd als referentie of als matrixwaarden voor de gegevensitems. Order vertegenwoordigt de reekspositie in de grafiek. Merk op dat de verwijzingen naar de gegevens alleen werken als ze volledig gekwalificeerd zijn met de bladnaam. Klik voor een voorbeeld van een werkende formule op een bestaande reeks en controleer de formulebalk.

Code om een grafiek te maken en gegevens in te stellen met behulp van de SERIES formule

Merk op dat bij het maken van de tekenreeks om de SERIES formule te maken .Address(,,,True) . Dit zorgt ervoor dat de externe bereikreferentie wordt gebruikt, zodat een volledig gekwalificeerd adres met de bladnaam wordt opgenomen. U krijgt een foutmelding als de bladnaam is uitgesloten .

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

Oorspronkelijke gegevens en resulterende grafiek

Merk op dat voor deze grafiek de serienaam correct is ingesteld met een bereik tot de gewenste cel. Dit betekent dat updates worden doorgegeven aan de Chart .

Grafieken rangschikken in een raster

Een veel voorkomend karwei met grafieken in Excel is het standaardiseren van de grootte en lay-out van meerdere grafieken op één blad. Als u dit handmatig doet, kunt u ALT ingedrukt houden terwijl u het formaat van de grafiek wijzigt of de grafiek verplaatst om zich aan de celgrenzen te houden. Dit werkt voor een paar grafieken, maar een VBA-aanpak is veel eenvoudiger.

Code om een raster te maken

Deze code maakt een raster van diagrammen beginnend op een bepaalde positie (boven, links), met een gedefinieerd aantal kolommen en een gedefinieerde gemeenschappelijke kaartgrootte. De grafieken worden geplaatst in de volgorde waarin ze zijn gemaakt en worden om de rand gewikkeld om een nieuwe rij te vormen.

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

Resultaat met verschillende grafieken

Deze afbeeldingen tonen de oorspronkelijke willekeurige lay-out van grafieken en het resulterende raster van het uitvoeren van de bovenstaande code.

Voordat

Na