Office: Diagram per Makro: Balken im Datumformat

Hallo,

meine bisherigen kleinen Markobehafteten Vorhaben führten Dank der Unterstützung hier rasch zum gewünschten Erfolg.
Das hat mich zu einer neuen aufgabe inspiriert, für die ich aber wieder um Hilfe bitten möchte.

Ich möchte per Makro wieder ein Diagramm erzeugen, dass für ein zeitlich begrenztes Projekt einzelne Aufgaben und ihren benötigten Zeitraum darstellt:
- Balkendiagramm (horizontal)
- Achsen im Datumsformat formatiert
- die eigentlichen Balken von "Projektbeginn" bis jeweiliger Endwert eher usichtbar oder dezent formatiert
- breit und deutlich sichtbar der "negative Fehler" des jeweiligen Balkens
- Endbereich der Datenquele wird vorher bestimmt und steht in der Variablen "hzeilee" (Datenbereich also von Zeile 3 bis Zeile "hzeilee")

Ich habe bisher Codefragmente:

Code:

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range(Cells(3, 10), Cells(hzeilee, 12))
    ActiveChart.ChartType = xlBarClustered
        With ActiveSheet.ChartObjects(1).Chart
        .SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlMinusValues, _
            Type:=xlCustom, Amount:=Range(Cells(3, 12), Cells(hzeilee, 12)), _
            MinusValues:=Range(Cells(3, 12), Cells(hzeilee, 12))
       End With

 

Hi,

ein paar Angaben, wo dein Problem liegt, wären sehr hilfreich gewesen. Außerdem wäre es gut, den Tabellenaufbau zu posten, oder eine Beispieldatei anzuhängen - was natürlich noch besser wäre.

Bis später,
Karin

 

Es gibt allgemein Zellen, wo z. B. der Anfang und das Ende eines Projektes festgelegt ist.

Und eine Tabele mit verschiedenen Angaben (eine Aufgabe = eine Zeile).
die wichtigsten Informationen sind in
Zeile 10: Name der Aufgabe
Zeile 11: Ende der Aufgabe als Datum (=Maximalwert des zugehörigen Balkens)
Zeile 12: Dauer der Aufgabe in Tagen (=negativer Fehlerbalken des Balkens ...du erinnerst dich an den anderen Thread wo ich diese Funktion schon für etwas anderes erfragt hatte)

Es soll nun per Knopfdruck ein Balkendiagramm erstellt werden,
welches alle bisher in der Tabelle eingetragenen Aufgaben (die Funktion zur Ermittlung der Zeilen habe ich schon) als Balken darstellt.

Beispiel siehe (links Diagramm, rechts Quelle)


Hauptproblem ist das korrekte Verarbeiten von Daten im Datumsformat und das Formatieren des Diagramms. Wobei sich das"hübsch machen" dann sicher sonoch herausfinden lässt.

 

Ich selbst bin inzwischen so weit gekommen (siehe Grafik).
Was ich noch nicht hinbekommen habe ist die Formatierung der ErrorBars.



Mit diesem Quelltext:

Code:

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range(Cells(3, 10), Cells(hzeilee, 11))
    ActiveChart.ChartType = xlBarClustered
         With ActiveSheet.ChartObjects(1).Chart
        .SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlMinusValues, _
            Type:=xlCustom, Amount:=Range(Cells(3, 12), Cells(hzeilee, 12)), _
            MinusValues:=Range(Cells(3, 12), Cells(hzeilee, 12)) ', ColorIndex:=5
       End With

    'Titel festlegen
      ActiveChart.SetElement (msoElementChartTitleAboveChart)
      ActiveChart.ChartTitle.Text = "Balkenplan"

    'Abmessungen festlegen anhand des Umfangs
      ActiveSheet.ChartObjects(1).Width = Sheets("Allgemeine_Angaben").Cells(2, 9) / 2
      ActiveSheet.ChartObjects(1).Height = hzeilee * 20

    'Balken einfärben
ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Interior.ColorIndex = 2

    'Legende entfernen
      ActiveChart.Legend.Select
      Selection.Delete

    ' Ausrichtung
     Dim eDia As ChartObject
     Set eDia = ActiveSheet.ChartObjects(1)
     With eDia
       .Left = Range("B17").Left
       .Top = Range("B17").Top
      End With

 

Hi,

sorry, ich erkenne immer noch nicht, wo dein Problem liegt.

Bis später,
Karin

 

Das erste Bild zeigt wie ich es von Hand erstellt habe (dicke, rote ErrorBars).
Das zweite Bild erzeugt mein Makro bis jetzt, leider finde ich keine Hinweise darauf, wie ich ErrorBars per Makro so formatieren kann.
Das ist das Hauptproblem jetzt.

Das Problem mit den Daten im Datumsformat konnte ich soweit durch passende Zellformatierungen umgehen.

 

Hi,

in deinem 1. Diagramm sind meines Erachtens keine Fehlerindikatoren dargestellt sondern du verwendest ein gestapeltes Balkendiagramm. Andernfalls musst du schon mal beschreiben, WIE du sie so formatiert hast, damit man das nachvollziehen kann.

Bis später,
Karin

 

In dem ersten Diagramm habe ich
- ein Balkendiagramm aus den Endwerten der jeweiligen Aufgaben erstellt
- benutzerdefinierte Fehlerbalken (negativ, basierend auf der Dauer der Aufgabe) eingefügt
- die Haupbalken hellgrau eingefärbt
- die Fehlerbalken per Rechtsklick und "Fehlerbalken formatieren" eingefärbt und die Linienstärke auf 5 gesetzt

Mit einem gestapelten Balkendiagramm müsste rein optisch das Selbe einfacher erreichbar sein, oder?

EDIT:
Ja natürlich geht das auch mit einem gestapelten Balkendiagramm..warum umständlich wenn es auch einfach geht...
Wobei, so viel einfacher ist es nicht. Excel erkennt nicht automatisch die beiden Dateireihen für die Säulen, da eine Spalte im Datumsformat vorliegt und die zweite als Zahl (Tage).

EDIT 2:
Ich habs jetzt soweit hinbekommen, nur noch hübsch machen und Achsen formatieren fehlt. Ich füge die zweite Datenreihe getrent hinzu und dann klappt es. Hier der Code (es geht sicher eleganter, aber funktioniert):

Code:

    Range(Cells(3, 10), Cells(hzeilee, 12)).Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range(Cells(3, 10), Cells(hzeilee, 11))
    ActiveChart.ChartType = xlBarStacked
        'Titel festlegen
      ActiveChart.SetElement (msoElementChartTitleAboveChart)
      ActiveChart.ChartTitle.Text = "Balkenplan"
    'Abmessungen festlegen anhand des Umfangs
      ActiveSheet.ChartObjects(1).Width = Sheets("Allgemeine_Angaben").Cells(2, 9) / 2
      ActiveSheet.ChartObjects(1).Height = hzeilee * 20
    'Balken einfärben
      ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Interior.ColorIndex = 2
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = ""
    ActiveChart.SeriesCollection(2).Values = Range(Cells(3, 12), Cells(hzeilee, 12))
        'Legende entfernen
      ActiveChart.Legend.Select
      Selection.Delete
    ' Ausrichtung
     Dim eDia As ChartObject
     Set eDia = ActiveSheet.ChartObjects(1)
     With eDia
       .Left = Range("B17").Left
       .Top = Range("B17").Top
      End With

Vielen Dank!

 

Hi,

Code:

    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).ErrorBars
        .Border.LineStyle = xlContinuous
        .Format.Line.Weight = 5
        .Border.Color = 192
    End With

Bis später,
Karin

 

Beide Varianten funktionieren, allerdings mus sman erwähnen, das die Variante mit den Fehlerbalken etwas ungenau ist bei kleinen Zeitabschnitten. Zumindest muss man dort noch irgendwie die Kennzeichnung des minimalen Wertepunktes entfernen. Dies führt dazu, das die Linie etwas eher und schmaler beginnt und dann der eigentliche Fehlerbalken mit der gewählten Linienstärke dort ansetzt.
Anbei die beiden Ergebnisse. Vielen Dank für die Hilfe und Hinweise! ..wieder was gelernt.

Mit gestapelten Balkendiagramm:


Mit Errorbars

 

Hi,

das Erstellen und Formatieren des Diagramm kann man auch ohne Select ausführen - dein geposteter Code würde dann so aussehen:

Code:

With Worksheets("Allgemeine_Angaben")
    With .ChartObjects.Add(.Range("B17").Left, _
        .Range("B17").Top, .Cells(2, 9) / 2, hzeilee * 20).Chart
        .SetSourceData Source:=Range(Cells(3, 10), Cells(hzeilee, 11)), _
            PlotBy:=xlColumns
        .SeriesCollection.NewSeries
        .SeriesCollection(2).Name = ""
        .SeriesCollection(2).Values = Range(Cells(3, 12), Cells(hzeilee, 12))
        .ChartType = xlBarStacked
        .SetElement (msoElementChartTitleAboveChart)
        .ChartTitle.Text = "Balkenplan"
        .Legend.Delete
        .SeriesCollection(1).Interior.ColorIndex = xlNone
    End With
End With

Bis später,
Karin

 

Ich danke dir, wobei es dann das Worksheet "Aufgabenliste" wäre, im anderen finden sich nur Daten auf die Zugegriffen wird.

Eine Fragehabe ich nun doch noch:
Wie kann ich die Achse so formatieren, das mir an Stelle von "beliebigen" Datumsangaben die Monate (Jan., Feb., ..) von Anfang bis Ende angezeigt werden?
Oder Monat + Jahr, Beispiel "Feb. 10".

 

Hi,

ergänze den Code um diese Zeile:

Code:

        .Axes(xlValue).TickLabels.NumberFormat = "MMM. DD"

Das lässt sich übrigens mit dem Makrorekorder aufzeichnen, da es die selbe Aktion wie bei einer Zellformatierung ist.

Bis später,
Karin

 

Hallo Karin,

vielen Dank für deine Hilfe!
In deinem letzten Post muss mann noch "DD" durch "YY" ersetzen.

Hier habe ich mal die Mappe hinterlegt, es ist einfach als Vorlage für mich gedacht, quasi eine Art "ToDo-Liste":

http://home.arcor.de/woerli/Aufgabenliste.xlsm

Jetzt hat die Arbeitsmappe alle von mir gewünschten Grundfunktionen.
Einzig ungünstig ist noch die Beschriftung der Value-Achse. Diese müsste noch mehr wie ein Kalender erscheinen, die jetzige Achsenbeschriftung durch Monatsangaben ist immernoch "willkürlich" und nicht konsistent, also Datenpunkte sind mit Monat + Jahr beschriftet, teilweise steht nun bei kurzen Vorgängen mehrmals "Okt. 10".
Besser währe ein beschrifteter Bereich und als Hilfsraster die jeweiligen Tage (variiert ja von Monat zu Monat), aber das ist erstmal nicht so dramatisch.

Viele Grüße und ein schönes Wochenende
Holger

 

Hi Holger,

Excel kennt standardmäßig nur eine äquidistante Skalierung der Größenachse und da die Monate eine unterschiedliche Anzahl an Tagen haben, lässt sich das so nicht darstellen. Wenn du eine anders geartete Darstellung haben möchtest, lässt sich das nur mithilfe einer Hilfsdatenreihe realisieren - der Aufwand dafür ist allerdings nicht unerheblich, da diese Hilfsdatenreihe den Diagrammtyp Punkt(XY) haben und deshalb auf die Sekundärachse gezeichnet werden muss.

Bis später,
Karin