Über unsMediaKontaktImpressum
Thomas Theis 28. April 2020

Diagramme mit VBA für Excel 2019

Diagramme können in MS Excel bereits per Knopfdruck schnell erzeugt werden. In diesem Artikel lernen Sie, wie Sie Diagramme außerdem mithilfe von VBA-Programmcode erstellen und verändern. Damit haben Sie die Möglichkeit, Diagramme aus unterschiedlichen Quellen, abhängig von aktuellen Bedingungen, automatisiert zu erstellen.

Diagramm erstellen

Bekanntlich können Diagramme sowohl auf eigenen Blättern in einer Arbeitsmappe als auch eingebettet in einem Tabellenblatt erstellt werden. Beide Methoden werden in diesem Artikel vorgestellt. Ausgangspunkt soll in beiden Fällen eine Tabelle mit Temperaturwerten sein, die Sie in Abb. 1 sehen.

Diagrammblatt erstellen

Diagramme auf eigenen Blättern (Diagrammblätter) stehen in der Objekthierarchie auf der gleichen Ebene wie Tabellenblätter. Es gibt verschiedene Auflistungen innerhalb einer Arbeitsmappe:

  • Sheets – enthält alle Tabellenblätter und Diagrammblätter.
  • Worksheets – enthält nur die Tabellenblätter.
  • Charts – enthält nur die Diagrammblätter.

Mit der folgenden Prozedur wird ein einfaches Liniendiagramm als Diagrammblatt in der Arbeitsmappe erstellt:

Listing 1: Sub DiagrammNeuesBlattErstellen()

Sub DiagrammNeuesBlattErstellen()    
    ThisWorkbook.Charts.Add After:=Worksheets("Tabelle1")    
    With ActiveChart
        .ChartType = xlLine
        .SetSourceData Worksheets("Tabelle1").Range("A1:C8")
        .Name = "Diagramm1"
    End With
End Sub

Das Ergebnis zeigt Abb. 2.

Zur Erläuterung:

  • Die Methode Add() des Objekts Charts erstellt ein Diagrammblatt und fügt es der Charts-Auflistung der aktiven Arbeitsmappe hinzu. Ähnlich wie beim Kopieren oder Verschieben von Tabellenblättern können Sie mithilfe von Before und After den Ort des neuen Blattes festlegen. Gibt es keine Angabe, wird das neue Blatt vor dem aktuell aktiven Blatt eingefügt.
  • Das neue Diagrammblatt hat den Typ Chart. Es wird zum aktiven Diagrammblatt. Daher kann es mit ActiveChart angesprochen werden.
  • Die Eigenschaft ChartType legt den Typ des Diagramms fest, hier xlLine für ein Liniendiagramm. Einige wichtige Diagrammtypen werden weiter unten aufgelistet.
  • Die Methode SetSourceData() legt die Datenquelle für das Diagramm fest. In diesem Fall ist das der Bereich A1 bis C8 des Tabellenblattes Tabelle1, mit den Temperaturdaten.
  • Mithilfe der Eigenschaft Name können Sie dem Diagramm einen Namen geben.

Hinweis:

Die Methode SetSourceData() hat noch einen zweiten, optionalen Parameter. Damit legen Sie fest, ob das Diagramm die Tabelle nach Spalten (xlColumns) oder Zeilen (xlRows) darstellt. Der erste Wert ist der Standard.

Tabelle 1: Diagrammtypen

Diagrammtyp Eigenschaft »ChartType«
Gruppiertes Säulendiagramm xlColumnClustered
Gruppiertes Balkendiagramm xlBarClustered
Liniendiagramm xlLine
Kreisdiagramm xlPie

Eingebettetes Diagramm erstellen

Eingebettete Diagramme stehen in der Objekthierarchie unterhalb der Tabellenblätter. Die Auflistung Worksheets enthält die Unterauflistung ChartObjects, also alle Diagrammrahmen des Tabellenblattes. Ein ChartObject ist Element dieser Auflistung ChartObjects, also ein einzelner Diagrammrahmen. Ein einzelnes Diagramm ist ein Objekt des Typs Chart und stellt den Wert der Eigenschaft Chart eines Diagrammrahmens dar. Bis auf diese Einordnung gleichen sich beide Diagrammformen.

Mit der folgenden Prozedur wird ein einfaches Liniendiagramm in das Tabellenblatt Tabelle1 eingebettet:

Listing 2: Sub DiagrammEingebettetErstellen()

Sub DiagrammEingebettetErstellen()
     Dim CO As ChartObject
     Dim CH As Chart

     Set CO = ThisWorkbook.Worksheets("Tabelle1"). _ 
        ChartObjects.Add(200, 10, 300, 150)

     Set CH = CO.Chart
     CH.ChartType = xlLine
     CH.SetSourceData Worksheets("Tabelle1").Range("A1:C8")
End Sub

Das Ergebnis sehen Sie in Abb. 3.

Zur Erläuterung:

  • Zunächst wird eine Variable des Typs ChartObject deklariert. Sie verweist später auf den neu erstellten Diagrammrahmen.
  • Anschließend wird eine Variable des Typs Chart deklariert. Sie verweist später auf das Diagramm im neu erstellten Diagrammrahmen.
  • Die Auflistung ChartObjects enthält alle Rahmen der eingebetteten Diagramme auf einem Tabellenblatt. Die Methode Add() erstellt einen neuen Diagrammrahmen an einem bestimmten Ort und in einer bestimmten Größe. Sie liefert einen Verweis auf das neu erstellte Objekt zurück. Die vier Parameter von Add() stehen für:
    • den Abstand des Rahmens zum linken Rand des Tabellenblattes
    • den Abstand des Rahmens zum oberen Rand des Tabellenblattes
    • die Breite des Rahmens
    • die Höhe des Rahmens
  • Die Eigenschaft Chart der Auflistung ChartObjects stellt das eigentliche Diagramm innerhalb des Rahmens dar.
  • Die Eigenschaft ChartType ist bereits bekannt: Sie legt den Typ des Diagramms fest.
  • Die Methode SetSourceData() ist ebenfalls bekannt: Sie legt die Datenquelle für das Diagramm fest. In diesem Fall ist das der Bereich A1 bis C8 des aktiven Tabellenblattes.

Diagramm ändern

Diagramme haben viele Elemente mit zahlreichen Eigenschaften, die ebenso im Nachhinein geändert werden können. Die Elemente stehen in der Objekthierarchie unterhalb der Diagramme. Sie unterscheiden sich je nach Diagrammtyp. Am Beispiel des soeben erstellten Liniendiagramms sollen stellvertretend einige wichtige Eigenschaften (und Methoden) erläutert werden.

Seit MS Excel 2010 können Sie nicht nur die Erstellung eines Diagramms, sondern auch die Änderungen an einem Diagramm mithilfe eines Makros aufzeichnen. Dies kann eine Hilfe zur Nutzung der Objekthierarchie sein, deren Vielfalt hier nicht komplett beschrieben werden kann.

Diagrammblatt ändern

Zunächst das Programm:

Listing 3: Sub DiagrammNeuesBlattAendern()

Sub DiagrammNeuesBlattAendern()
     Dim CH As Chart
     Set CH = ThisWorkbook.Charts(1)
     DiagrammAendern CH
End Sub

Das Programm ist zweigeteilt. Zunächst der Programmteil, der nur auf Diagrammblätter zutrifft:

  • Es wird eine Variable des Typs Chart deklariert. Dieser Variablen wird das erste Diagrammblatt dieser Arbeitsmappe zugewiesen.
  • Es wird die Prozedur DiagrammAendern() aufgerufen. Als Parameter wird das erste Diagrammblatt Charts(1) übergeben.

Es folgt der Programmteil, der sowohl auf Diagrammblätter als auch auf eingebettete Diagramme zutrifft:

Listing 4: Sub DiagrammAendern()

Sub DiagrammAendern(CH As Chart)
     ' Diagrammfläche
     CH.ChartArea.Interior.Color = vbCyan

     ' Zeichnungsfläche
     CH.PlotArea.Interior.Color = vbYellow

     ' Titel
     CH.HasTitle = True
     CH.ChartTitle.Text = "Temperatur"

     ' Legende
     CH.HasLegend = True
     With CH.Legend
         .Interior.Color = vbYellow
         .Border.Color = vbBlue
         .Border.Weight = xlThick
     End With

     ' Kategorienachse
     With CH.Axes(xlCategory)
         .HasTitle = True
         .AxisTitle.Text = "Datum"
         .TickLabels.NumberFormatLocal = "TT.MM."
     End With
 
    ' Werteachse
     With CH.Axes(xlValue)
         .HasTitle = True
         .AxisTitle.Text = "Grad"
         .MinimumScale = 5
         .MaximumScale = 35
     End With
 
    ' Datenreihe
     With CH.SeriesCollection(1)
         .Border.Color = vbRed
         .MarkerStyle = xlMarkerStyleCircle
         .MarkerForegroundColor = vbRed
         .MarkerBackgroundColor = vbRed
     End With
 
    ' Datenpunkt
     With CH.SeriesCollection(1).Points(3)
         .Border.Color = vbBlue
         .ApplyDataLabels xlShowValue
         .MarkerStyle = xlMarkerStyleSquare
         .MarkerForegroundColor = vbBlue
         .MarkerBackgroundColor = vbBlue
     End With
 End Sub

Abb. 4 und Abb. 5 zeigen einige Bildausschnitte dazu.

Zur Erläuterung:

  • Die Eigenschaft ChartArea steht für die Diagrammfläche des Diagramms. Diese umfasst z. B. die Zeichnungsfläche, den Titel und die Legende. Sie hat u. a. die Eigenschaft Interior, die (wie bei einer Zelle) für das Innere des Objekts steht. Dieses Innere hat wiederum eine Farbe, die in diesem Beispiel auf Cyan gesetzt wird.
  • Die Eigenschaft PlotArea steht für die Zeichnungsfläche des Diagramms. Sie hat ebenfalls u. a. die Eigenschaft Interior, deren Farbe in diesem Beispiel auf Gelb gesetzt wird.
  • Diagramme können einen Titel haben. Darüber bestimmt die Eigenschaft HasTitle. Wird sie auf True gesetzt, hat das Diagramm einen Titel. Bei False hat es keinen Titel.
  • Das Aussehen des Titels wird über die Eigenschaft ChartTitle festgelegt. Die Eigenschaft Text enthält den Text des Titels.
  • Diagramme können eine Legende haben. Darüber bestimmt die Eigenschaft HasLegend. Sie kann ebenso auf True oder False gesetzt werden.
  • Das Aussehen der Legende wird über die Eigenschaft Legend festgelegt. Ähnlich wie eine Zelle hat eine Legende die Eigenschaften Interior und Border. Damit können Sie über die Farbe des Inneren bzw. über die Rahmenfarbe und -stärke bestimmen.
  • Alle Achsen eines Diagramms stehen in der Auflistung Axes. Eine einzelne Achse ist ein Objekt des Typs Axis. Zunächst muss mithilfe der Methode Axes() des Objekts Chart entschieden werden, welche Achse gemeint ist. Es gibt die Parameter:
    • xlCategory für die horizontale Achse, die Kategorienachse.
    • xlValue für die vertikale Achse, die Wertachse.
  • Für jede der beiden Achsen wird im Beispiel festgelegt, dass sie einen Achsentitel haben (HasTitle = True). Anschließend wird der Text des Achsentitels zugewiesen (AxisTitle.Text = ...).
  • Bei der Kategorienachse wird die Formatierung der Achsenbeschriftung (TickLabels) bestimmt. Hier stehen im Beispiel Datumsangaben. Sie werden mithilfe von NumberFormatLocal (wie Zellen) formatiert.
  • Bei der Wertachse wird die Skalierung bestimmt. Dabei können der unterste Wert (MinimumScale) und der oberste Wert (MaximumScale) gesetzt werden.
  • Alle Datenreihen eines Diagramms stehen in der Auflistung SeriesCollection. Eine einzelne Datenreihe ist ein Objekt des Typs Series. Zunächst muss mithilfe der Methode SeriesCollection() des Objekts Chart entschieden werden, welche Datenreihe gemeint ist. Diese Methode erhält als Parameter die Nummer der Datenreihe, beginnend bei 1, und liefert als Rückgabewert die entsprechende Datenreihe.
  • Mit Border können die Eigenschaften des Rahmens der Datenreihe (bei Liniendiagrammen die Eigenschaften der eigentlichen Linie) bestimmt werden. In diesem Beispiel wird die Farbe auf Rot gesetzt.
  • Mit MarkerStyle, MarkerForegroundColor und MarkerBackgroundColor entscheiden Sie über das Aussehen der Markierungspunkte auf der Linie.
    • Für MarkerStyle ist eine Reihe von Konstanten erlaubt, z. B. xlMarkerStyleCircle (Kreis), xlMarkerStyleSquare (Quadrat) und xlMarkerStyleNone (keine Markierung).
    • Die Eigenschaften MarkerForegroundColor und MarkerBackgroundColor stehen für die Farbe der Markierungsfüllung und des Markierungsrandes.
  • Alle Datenpunkte einer Datenreihe stehen in der Auflistung Points. Ein einzelner Datenpunkt ist ein Objekt des Typs Point. Zunächst muss mithilfe der Methode Points() des Objekts Series entschieden werden, welcher Datenpunkt gemeint ist. Diese Methode erhält als Parameter die Nummer des Datenpunktes, beginnend bei 1, und liefert als Rückgabewert den entsprechenden Datenpunkt.
  • Mit Border bestimmen Sie die Eigenschaften des Rahmens des Datenpunktes (bei Liniendiagrammen die Eigenschaften der eigentlichen Linie). In diesem Beispiel wird die Farbe auf Blau gesetzt.
  • Die Methode ApplyDataLabels() bestimmt über die Beschriftung von Datenreihen oder Datenpunkten. Der wichtigste Parameter ist der Typ der Beschriftung. Erlaubte Werte sind u. a. xlShowValue (Wert), xlShowLabel (Kategorie) und xlShowNone (keine Beschriftung).
  • Das Aussehen der Markierung eines Datenpunktes lässt sich wie bei einer Datenreihe einstellen, siehe oben.

Eingebettetes Diagramm ändern

Das Programm zum Ändern eines eingebetteten Diagramms ist ebenso aufgebaut wie das Programm zum Ändern eines Diagrammblattes, also zweiteilig. Es folgt der Programmteil, der nur auf eingebettete Diagramme zutrifft:

Listing 5: Sub DiagrammEingebettetAendern()

Sub DiagrammEingebettetAendern()
     Dim CO As ChartObject
     Dim CH As Chart
     Set CO = ThisWorkbook.Worksheets("Tabelle1").ChartObjects(1)
     CO.Left = 220
     CO.Top = 30
     CO.Width = 400
     CO.Height = 300
     Set CH = CO.Chart
     DiagrammAendern CH
End Sub

Zur Erläuterung:

  • Es wird eine Variable des Typs ChartObject deklariert. Dieser Variablen wird der erste Diagrammrahmen des Tabellenblattes Tabelle1 dieser Arbeitsmappe zugewiesen.
  • Anschließend wird eine Variable des Typs Chart deklariert. Ihr weisen wir das Diagramm dieses Diagrammrahmens zu.
  • Bei einem eingebetteten Diagramm kann es sinnvoll sein, Position und Größe des Diagrammrahmens zu ändern. Hierzu können Sie die Eigenschaften Left, Top, Width und Height nutzen.
  • Es wird die (bereits bekannte) Prozedur DiagrammAendern() aufgerufen. Als Parameter wird der Chart übergeben.

Diagramme verwalten

Diagramme können unter Anderem kopiert, gelöscht oder als Bilddatei exportiert werden. Dazu folgen insgesamt sechs Programme, zunächst drei Programme zu einem Diagrammblatt, anschließend drei Programme zu einem eingebetteten Diagramm.

Diagrammblatt kopieren

Listing 6: Sub DiagrammNeuesBlattKopieren()

Sub DiagrammNeuesBlattKopieren()
     ThisWorkbook.Charts("Diagramm1").Copy _
         After:=Worksheets("Tabelle3")
     ActiveChart.Name = "Diagramm1 Neu"
End Sub

 

Zur Erläuterung:

  • Ein Diagrammblatt wird genau so kopiert wie ein Tabellenblatt.
  • Die Methode Copy() sorgt für die Erstellung der Kopie, s. Abb.6. Mit Before bzw. After können Sie den Ort der Kopie angeben. Ohne diese Parameter würde eine neue Arbeitsmappe mit dem Diagrammblatt als einzigem Blatt angelegt werden.

Diagrammblatt löschen

Es folgt das Löschen eines Diagrammblatts:

Listing 7: Sub DiagrammNeuesBlattLoeschen()

Sub DiagrammNeuesBlattLoeschen()
     ThisWorkbook.Charts("Diagramm1 Neu").Delete
End Sub

Die Methode Delete() löscht das angegebene Diagrammblatt einschließlich Rückfrage vor dem Löschen.

Diagrammblatt exportieren

Zuletzt das Exportieren:

Listing 8: Sub DiagrammNeuesBlattExport()

Sub DiagrammNeuesBlattExport()
     ThisWorkbook.Charts("Diagramm1").Export "C:\Temp\April.png"
End Sub

 

Die Methode Export() dient dem Erzeugen einer Bilddatei. Es muss der Name der Bilddatei (gegebenenfalls mit dem Verzeichnisnamen) angegeben werden. Die Endung des Dateinamens bestimmt über die Art der Konvertierung. Es sind u. a. .jpg,.gif und .png erlaubt.

Das Ergebnis sehen Sie in Abb. 7.

Eingebettetes Diagramm kopieren

Es folgt das Kopieren eines eingebetteten Diagrammblatts:

Listing 9: Sub DiagrammEingebettetKopieren()

Sub DiagrammEingebettetKopieren()
     With ThisWorkbook.Worksheets("Tabelle1")
         .ChartObjects(1).Copy
         .Paste
         .ChartObjects(2).Top = 250
         .ChartObjects(2).Left = 200
     End With 
End Sub

Das Ergebnis sehen Sie in Abb. 8.

Zur Erläuterung:

  • Es wird davon ausgegangen, dass im Tabellenblatt Tabelle1 nur ein Diagrammrahmen existiert.
  • Dieser Rahmen hat die Nummer 1 in der ChartObjects-Auflistung. Die Methode Copy() kopiert den Diagrammrahmen (inklusive des Diagramms) in die Zwischenablage.
  • Die Methode Paste() fügt den Diagrammrahmen aus der Zwischenablage in das Tabellenblatt Tabelle1 ein. Damit wird er zu ChartObjects(2).
  • Die Position dieses neuen Diagrammrahmens wird über die Eigenschaften Top und Left festgelegt.

Eingebettetes Diagramm löschen

Mit folgender Prozedur wird ein eingebettetes Diagramm gelöscht:

Listing 10: Sub DiagrammEingebettetLoeschen()

Sub DiagrammEingebettetLoeschen()
     ThisWorkbook.Worksheets("Tabelle1").ChartObjects(2).Delete 
End Sub

Die Methode Delete() löscht den Diagrammrahmen ohne vorherige Rückfrage.

Eingebettetes Diagramm exportieren

Es folgt das Exportieren:

Listing 11: Sub DiagrammEingebettetExport()

Sub DiagrammEingebettetExport()
     ThisWorkbook.Worksheets("Tabelle1").ChartObjects(1). _ 
        Chart.Export "C:\Temp\April.jpg" 
End Sub

Die Methode Export() dient dem Erzeugen einer Bilddatei, diesmal aus dem Diagramm eines Diagrammrahmens.

Ein formatiertes Kreisdiagramm

Als weiteres Beispiel für ein neu erstelltes, eingebettetes und formatiertes Diagramm soll nachfolgend ein Kreisdiagramm dienen. Sie können erkennen, dass dabei viele allgemeine Diagrammeigenschaften aus dem Liniendiagramm übernommen werden können. Andere Eigenschaften gibt es hingegen nur bei Kreisdiagrammen. Als Datenquelle dienen Wahlergebnisse aus dem Jahr 2019 in Spanien in Abb. 9.

Zunächst der Code:

Listing 12: Sub DiagrammKreis()

Sub DiagrammKreis()
     Dim CO As ChartObject
     Dim CH As Chart
     Dim i As Integer

     Set CO = ThisWorkbook.Worksheets("Tabelle2"). _
         ChartObjects.Add(200, 10, 400, 350)
     Set CH = CO.Chart

     ' Diagrammtyp und Datenquelle
     CH.ChartType = xlPie
     CH.SetSourceData Range("A1:B16")

     ' Diagramm- und Zeichnungsfläche
     CH.ChartArea.Interior.Color = vbCyan
     CH.PlotArea.Interior.Color = vbYellow

     ' Titel
     CH.HasTitle = True
     CH.ChartTitle.Text = "Spanien 2019"

     ' Legende
     CH.HasLegend = True
     With CH.Legend
         .Interior.Color = vbYellow
         .Border.Color = vbBlue
         .Border.Weight = xlThick
     End With

     'Datenpunkte
     CH.SeriesCollection(1).Points(1). _
         Interior.Color = vbWhite

     For i = 1 To CH.SeriesCollection(1).Points.Count
         If Cells(i + 1, 2) > 30 Then
             With CH.SeriesCollection(1).Points(i)
                 .ApplyDataLabels xlDataLabelsShowLabelAndPercent
                 .DataLabel.NumberFormatLocal = "0,00%"
             End With
         End If
     Next i
End Sub

In Abb. 10 sehen Sie einen Ausschnitt des Ergebnisses.

Zur Erläuterung:

  • Wie bekannt, wird mit ChartObjects.Add() der Diagrammrahmen neu erstellt.
  • Als Diagrammtyp dient xlPie für Kreis- oder Kuchendiagramme.
  • Die Diagramm- und Zeichnungsfläche, der Titel und die Legende können wie beim Liniendiagramm formatiert werden.
  • Bei den Datenreihen sieht es anders aus. Es gibt keine Markierungen, dafür aber z. B. Folgendes:
    • xlDataLabelsShowLabelAndPercent – ein spezieller Beschriftungstyp
    • eine innere Fläche
  • Im vorliegenden Fall werden mit dem Beschriftungstyp die Datenpunkte, deren Wert oberhalb einer Grenze (30 Sitze) liegt, mit Kategorie und Prozentzahl beschriftet. Außerdem wird die innere Fläche eines Datenpunktes in Weiß dargestellt.
  • Die Eigenschaft DataLabel bestimmt über das Aussehen der Beschriftung. Hier wurde festgelegt, dass die Prozentzahl mit zwei Nachkommastellen dargestellt wird.

Der Artikel ist ein Auszug aus dem Buch "Einstieg in VBA mit Excel"

Einstieg in VBA mit Excel. Makro-Programmierung für Microsoft Excel 2010 bis 2019 und Office 365.
5. Auflage. Rheinwerk Verlag. Mai 2020 - 455 Seiten

Makros programmieren und Aufgaben in Excel automatisieren: So geht’s! Sie möchten Excel an Ihre Bedürfnisse anpassen, Berechnungen automatisieren, eigene Dialogfelder erstellen und mehr? Dieses Buch zeigt Ihnen, wie Sie Ihr Ziel schnell und einfach mit VBA erreichen. Ganz ohne Vorkenntnisse, mit vielen Übungsaufgaben und Musterlösungen.

Autor

Thomas Theis

Thomas Theis ist Diplom-Ingenieur für Technische Informatik und Software-Entwickler. Er hat langjährige Erfahrung als IT-Dozent, unter anderem an der Fachhochschule Aachen.
>> Weiterlesen

Kommentare (0)

Neuen Kommentar schreiben