Macrorec Code umbasteln macht Probleme
11.11.2003 11:49:56
golem
ich versuche (mal wieder) per MAcrorec ein Diagramm zu erstellen, welches drei Linienchartquellen und eine Balkenchartquelle(quelle=Kurve) beinhaltet.
Diese werden in einer anderen funktinstüchtigen Prozedur für alle Tabellenblätter erstellt. Glücklicherweise ist auf jedem Tabblatt der Quellbereich gleich.
per Macrorec sieht das dann so aus:
XY ist das aktive Tabellenblatt; das activechart wegzumachen bringt nix(Fehler)
(es gibt allerdings mehrere Diagramme auf den Tabellenblättern)
Sub graph(Optional XY As String)
Dim ChrtObjts As ChartObjects, ChrtObj As ChartObject
Dim MyChart As Chart, ws As Variant
Sheets(XY).Select
Set ws = ActiveSheet
Set ChrtObjts = ws.ChartObjects
Set ChrtObj = ChrtObjts.Add(100, 100, 300, 700)
Set MyChart = ChrtObj.Chart
ActiveChart.ChartType = xlLineMarkers 'Fehlermeldung
ActiveChart.SetSourceData Source:=Sheets("XY").Range("T73")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=XY!R53C1:R65C1"
ActiveChart.SeriesCollection(1).Values = "=XY!R53C21:R65C21"
ActiveChart.SeriesCollection(1).Name = "=XY!R50C21:R51C21"
ActiveChart.SeriesCollection(2).XValues = "=XY!R53C1:R65C1"
ActiveChart.SeriesCollection(2).Values = "=XY!R53C20:R65C20"
ActiveChart.SeriesCollection(2).Name = "=XY!R50C20:R51C20"
ActiveChart.SeriesCollection(3).XValues = "=XY!R53C1:R65C1"
ActiveChart.SeriesCollection(3).Values = "=XY!R53C13:R65C13"
ActiveChart.SeriesCollection(3).Name = "=XY!R50C13:R51C13"
ActiveChart.SeriesCollection(4).XValues = "=XY!R53C1:R65C1"
ActiveChart.SeriesCollection(4).Values = "=XY!R53C12:R65C12"
ActiveChart.SeriesCollection(4).Name = "=XY!R50C12:R51C12"
ActiveChart.Location Where:=xlLocationAsObject, Name:="XY"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Sum Inventories"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 6
.MarkerForegroundColorIndex = 45
.MarkerStyle = xlCircle
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.SeriesCollection(4).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 23
.Pattern = xlSolid
End With
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(4).Points(3).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Points(2).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Points(3).Select
ActiveChart.SeriesCollection(3).Points(2).Select
ActiveChart.SeriesCollection(3).Points(3).Select
' ActiveWindow.Visible = False
'Windows("test3.xls").Activate
' Range("H83").Select
ActiveSheet.ChartObjects("Diagramm 26").Activate' --->ist nicht auf jedem Blatt Diagramm 26 (kann man das allgemeiner schreiben?)
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 57
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 44
.MarkerForegroundColorIndex = 3
.MarkerStyle = xlCircle
.Smooth = False
.MarkerSize = 9
.Shadow = False
End With
ActiveChart.SeriesCollection(3).AxisGroup = 2
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 13
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 3
.MarkerForegroundColorIndex = 6
.MarkerStyle = xlCircle
.Smooth = False
.MarkerSize = 9
.Shadow = False
End With
ActiveChart.SeriesCollection(1).AxisGroup = 2
ActiveChart.SeriesCollection(4).Select
With ActiveChart.ChartGroups(1)
.Overlap = 0
.GapWidth = 400
.HasSeriesLines = False
.VaryByCategories = False
End With
'ActiveWindow.Visible = False
'Windows("test3.xls").Activate
'Range("K81").Select
End Sub
Die Macrorecorder-Mitschnitte sind wirklich zum Schreien und total benutzerunfreundlich...
Kann man ohne Macrorec Diagramme erzeugen, die alle möglichen Parameter enthalten? für Links oder Beispiele würde ich mich freuen.
hoffe jemand von Euch blickt da durch.
Wieso gibt es eigentlich 'keine' VBA Bücher zum Thema Diagramme erzeugen/programmieren?
Viele Grüße
Golem