VBA mit Excel: Diagramme mit bedingter Zellformatierung
Eine Excel-Tabelle und ein zugehöriges Diagramm im selben Format darzustellen, kann aufwendig werden. Mit diesem VBA-Code geht es automatisch.
Sie wollen, dass die Farben eines Diagramms identisch sind mit jenen in der Datenbasis bzw. gleich wie jene der Zellformate? Das ist mit manueller Zuweisung grundsätzlich möglich, kann jedoch sehr aufwändig werden.
Eine ganz andere Geschichte ist es, die Datenpunkte im Diagramm gemäss «bedingte Formatierung» in denselben Farben wie in der Tabelle darzustellen. Dafür gibt es in den Diagramm-Tools standardmässig keine Einstellungsmöglichkeiten.
Dank der Programmiersprache VBA (Visual Basic for Applications) gibt es aber eine tolle Möglichkeit, Diagramme bzw. ihre Datenpunkte (Punkte, Säulen oder Balken) automatisch genau in den gleichen Farben anzuzeigen, wie es die Datenbasis, die aufgrund einer «bedingten Zellformatierung» vorgibt.
Ein Beispiel
Die folgende Abbildung zeigt beispielhaft eine Tabelle mit Diagramm. Die Werte sind gemäss der bedingten Formatierung farblich hervorgehoben. Mit einem Klick auf die Schaltfläche «Werte ändern …» werden die Werte neu berechnet und damit auch das Diagramm aktualisiert.
Die Vergleichswerte (<= und >=) sollen variabel angepasst werden können, um die Anwendung effektiv zu testen.
Der Zellbereich beinhaltet 20 Werte bzw. Datenpunkte.
Die Formel für die Werte lautet: =ZUFALLSZAHL()*2500+1.
Bedingung: alle Werte >=2000 sind grün, alle Werte <=1000 sind rot und alle übrigen Werte haben keine spezielle Farbgebung (weiss).
Der Schaltfläche «Werte ändern …» ist folgendes VBA-Programm hinterlegt:
VBA-Code
Option Explicit
Das Werte-Array soll bei 1 – anstatt bei 0 beginnen … (0 = standard)
Option Base 1
Variablendeklaration:
Dim arrWertereihe()
Dim pntWertePunkt As Point, serWertereihe As Series
Dim intWert As Integer, intWertKleinerGleich As Integer, intWertGrösserGleich As Integer
Diagramm soll die gleiche “Bedingte Formatierung” wie die Werte aus dem Zellbereich erhalten:
Sub DiagrammFormat_aktualisieren()
With Tabelle1
'variable Vergleichswerte (<=, >=) auslesen ...
intWertKleinerGleich = .Range("rngWertKleinerGleich").Value
intWertGrösserGleich = .Range("rngWertGrösserGleich").Value
Diagramm 1″ aktivieren:
.ChartObjects("Diagramm 1").Activate
Diagramm (Wertereihe) der Diagramm-Objektvariable zuweisen:
Set serWertereihe = ActiveChart.FullSeriesCollection(1)
'Werte-Array erhält die Wertereihe aus der Diagramm-Objektvariable ...
arrWertereihe = serWertereihe.Values
Zählschleife, um die einzelnen Werte aus dem Werte-Array auszulesen:
For intWert = 1 To UBound(arrWertereihe)
With serWertereihe.Points(intWert)
und die entsprechende Formatierung zuweisen:
Select Case arrWertereihe(intWert)
Case Is >= intWertGrösserGleich
.Format.Fill.ForeColor.RGB = RGB(146, 208, 8) '= grün
Case Is <= intWertKleinerGleich
.Format.Fill.ForeColor.RGB = RGB(255, 0, 0) '= rot
Case Else
.Format.Fill.ForeColor.RGB = RGB(250, 250, 250) '= hellgrau (~farblos)
End Select
End With
Next
am Schluss die aktive Zelle auf A4 setzen:
.Range("A4").Select
End With
End Sub
Den obenstehende VBA-Code können Sie einfach in ein Modul kopieren. Sie müssen
allenfalls Zellbezüge und/oder Namen auf die eigene Umgebung anpassen.
Und das ist im Prinzip schon alles. Sie erhalten ein «bedingt formatiertes» Diagramm, dass identisch mit der Zellformatierung ist.
Viel Spass beim Ausprobieren!
Ready for VBA?Nutzen Sie die Möglichkeiten von Visual Basic for Applications (VBA), um Ihre Arbeit mit Office produktiver und effizienter zu machen. Unser breites VBA-Angebot ermöglicht Ihnen genau den Kurs zu besuchen, der Sie auf Ihrer kontinuierlichen Lernreise den entscheidenden Schritt weiter bringt.
|
Nutzen Sie die Möglichkeiten von Visual Basic for Applications (VBA), um Ihre Arbeit mit Office produktiver und effizienter zu machen. Unser breites VBA-Angebot ermöglicht Ihnen genau den Kurs zu besuchen, der Sie auf Ihrer kontinuierlichen Lernreise den entscheidenden Schritt weiter bringt.